14194

Using open XML to create excel file

Question:

I am trying to use Open XML to create a file but when trying to add just the first row of headers the file is being corrupted and I am unable to open, can anyone tell me what I am doing wrong here?

using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create("C:\\testpdfs\\mytest.xlsx", SpreadsheetDocumentType.Workbook)) { // Add a WorkbookPart to the document. WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); // Add Sheets to the Workbook. Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook. AppendChild<Sheets>(new Sheets()); // Append a new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart. GetIdOfPart(worksheetPart), SheetId = 1, Name = ViewBag.Title }; Row row = new Row() { RowIndex = 1 }; Cell header1 = new Cell() { CellReference = "A1", CellValue = new CellValue("Interval Period Timestamp") }; row.Append(header1); Cell header2 = new Cell() { CellReference = "A2", CellValue = new CellValue("Settlement Interval") }; row.Append(header2); Cell header3 = new Cell() { CellReference = "A3", CellValue = new CellValue("Aggregated Consumption Factor") }; row.Append(header3); Cell header4 = new Cell() { CellReference = "A4", CellValue = new CellValue("Loss Adjusted Aggregated Consumption") }; row.Append(header4); sheet.Append(row); sheets.Append(sheet); //sheet.Append(row); workbookpart.Workbook.Save(); // Close the document. spreadsheetDocument.Close(); return View(); }

Answer1:

You have a few issues here.

Firstly, you're adding row to the Sheet but it needs to be added to the SheetData. The easiest way to do this is to keep a reference to the SheetData object so we can use it later:

SheetData sheetData = new SheetData(); worksheetPart.Worksheet = new Worksheet(sheetData); ... sheetData.Append(row);

Secondly, you need to explicitly give a data type to each cell because the default if no data type is given is number:

Cell header1 = new Cell() { CellReference = "A1", CellValue = new CellValue("Interval Period Timestamp"), DataType = CellValues.String };

Finally, your Cell References are not quite right. You are adding everything to one row but adding references for rows 1 to 4 (A1-A4). Given the cells are referred to as "headers" in your code I'm guessing you actually want the values in cells A1-D1 in which case you need to just update the CellReference values. If you actually want values in A1-A4 then you'll need to add each cell to a new row.

The full code listing (assuming you want cells A1-D1) is:

using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create("C:\\testpdfs\\mytest.xlsx", SpreadsheetDocumentType.Workbook)) { // Add a WorkbookPart to the document. WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>(); SheetData sheetData = new SheetData(); worksheetPart.Worksheet = new Worksheet(sheetData); // Add Sheets to the Workbook. Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook. AppendChild<Sheets>(new Sheets()); // Append a new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart. GetIdOfPart(worksheetPart), SheetId = 1, Name = ViewBag.Title }; Row row = new Row() { RowIndex = 1 }; Cell header1 = new Cell() { CellReference = "A1", CellValue = new CellValue("Interval Period Timestamp"), DataType = CellValues.String }; row.Append(header1); Cell header2 = new Cell() { CellReference = "B1", CellValue = new CellValue("Settlement Interval"), DataType = CellValues.String }; row.Append(header2); Cell header3 = new Cell() { CellReference = "C1", CellValue = new CellValue("Aggregated Consumption Factor"), DataType = CellValues.String }; row.Append(header3); Cell header4 = new Cell() { CellReference = "D1", CellValue = new CellValue("Loss Adjusted Aggregated Consumption"), DataType = CellValues.String }; row.Append(header4); sheetData.Append(row); sheets.Append(sheet); workbookpart.Workbook.Save(); // Close the document. spreadsheetDocument.Close(); return View(); }

Recommend

  • Relative position in DOM of elements in jQuery
  • Team City and Power Shell
  • Task manager shows memory leak, but Heap snapshot doesn't
  • Using PDFs for icon images in Xcode 7.2
  • A “regex for words” (semantic replacement) - any example syntax and libraries?
  • counting only truthy values in a collection [duplicate]
  • Target iPhone application by model (e.g. 3G vs 3GS)
  • Stored procedure OUTPUT VARCHAR2 value truncated using 12c client
  • TreatControlCAsInput issue. Is this a bug?
  • Getting the base url of my server with JAX-RS
  • what is the advantage of using Alamofire over NSURLSession/NSURLConnection for networking?
  • Order by does not work with Concat() in LINQ
  • Assigning variable and using it in range
  • Remove alt-codes from string
  • Error trying to download using filnename format
  • javascript window.parent issue
  • Accessing the username of all the followers of a user in Instagram using Scrapy or Instagram Python
  • Azure ARM DSC scale set deployment - cannot locate script
  • How to integrate Struts2 with Thymeleaf?
  • CMake with regarding generated files
  • Condition on a timestamp column to select data for a year
  • How does flex-shrink factor in padding and border-box?
  • Updating price of in app purchase
  • Algorithm for sorting a list of objects in c#
  • Wordpress plugins it asks for FTP Details
  • Memory leak in Ionic(or angular)?
  • Can't access web service when connected to the network :: HTTP 407
  • Faces Servlet not parsing .xhtml pages in jsf 2. running on tomcat 7
  • SQL Worksheet is not displaying in SQL Developer
  • VS2010 RDLC C#. How can I set a LocalReport object to a ReportViewer?
  • Calculate time from document
  • Update cell query for Excel ADO from Delphi
  • How to get rgb from transparent pixel in js
  • Angular 4: Responsive Grid List
  • How to use FirstOrDefault inside Include
  • PHP Permalinks.. how to change?
  • media foundation H264 decoder not working properly
  • Running R's aov() mixed effects model from Python using rpy2
  • Access to a Matlab gui from the web
  • ReferenceError: TextEncoder is not defined