Exporting to Excel (OpenXML) results in Unreadable Content

Question

I've been struggeling with creating worksheets for my xlsx file. Adding the first worksheet isn't a problem, but when I want a second sheet, the exported xlsx seems to be corrupt. Who can point out to me what I'm doing wrong? Note: I already tried to also call 'workbookpart,Workbook.Save();' right after creating the first Workbook, but without the required result.

protected void export_Click(object sender, EventArgs e)
{
    ExportToExcel(@"D:\dev\Dotnet4\Excel\test.xlsx");
}

private void ExportToExcel(string filepath)
{
    SpreadsheetDocument spreadsheetDocument;
    WorkbookPart workbookpart;
    CreateSpreadsheet(filepath, out spreadsheetDocument, out workbookpart);

    CreateWorksheet(spreadsheetDocument, workbookpart, "My sheet 1");
    CreateWorksheet(spreadsheetDocument, workbookpart, "My sheet 2");

    workbookpart.Workbook.Save();

    // Close the document.
    spreadsheetDocument.Close();
}

private static void CreateWorksheet(SpreadsheetDocument spreadsheetDocument, WorkbookPart workbookpart, string worksheetName)
{
    // 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 = worksheetName
    };
    sheets.Append(sheet);
}

private static void CreateSpreadsheet(string filepath, out SpreadsheetDocument spreadsheetDocument, out WorkbookPart workbookpart)
{
    // Create a spreadsheet document by supplying the filepath.
    // By default, AutoSave = true, Editable = true, and Type = xlsx.
    spreadsheetDocument = SpreadsheetDocument.
        Create(filepath, SpreadsheetDocumentType.Workbook);

    // Add a WorkbookPart to the document.
    workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();
}

Answer 1

I think you have 2 small issues:

1. The SheetId is the same for both sheets you are adding which is invalid.
2. You are adding a new Sheets element each time you call CreateWorksheet but there should only be one such element in the XML (there are many Sheet's but not many Sheets!).

To solve 1 you could use the count of the sheets.ChildElements. As this is 0 first time round and one the second you'll need to add 1 to it. If you prefer you could take it in to CreateWorksheet as a parameter; it doesn't really matter as long as they start at 1 and are different.

To solve 2 you can perform a null check on the Sheets property of the Workbook and only create it if it doesn't already exist.

The below should do what you're after.

private static void CreateWorksheet(SpreadsheetDocument spreadsheetDocument, WorkbookPart workbookpart, string worksheetName)
{
    // Add a WorksheetPart to the WorkbookPart.
    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet(new SheetData());

    // Add Sheets to the Workbook.
    if (spreadsheetDocument.WorkbookPart.Workbook.Sheets == null)
    {
        //spreadsheetDocument.WorkbookPart.Workbook.Sheets = new Sheets();
        spreadsheetDocument.WorkbookPart.Workbook
                                        .AppendChild<Sheets>(new Sheets());
    }

    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;

    // Append a new worksheet and associate it with the workbook.
    Sheet sheet = new Sheet()
    {
        Id = spreadsheetDocument.WorkbookPart.
            GetIdOfPart(worksheetPart),
        SheetId = (UInt32)sheets.ChildElements.Count + 1,
        Name = worksheetName
    };
    sheets.Append(sheet);
}

One final point is that SpreadsheetDocument implements IDisposable so it should be disposed after use.