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();
}

score 19 · Accepted Answer · answered Jun 16 '17 at 11:24

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();

}

Using open XML to create excel file

1 Answers1

Linked