OpenXML Multiple Sheets

Question

I am adding multiple sheets to an excel workbook. I want to have one row on one sheet and the other row on the other sheet. This code puts both rows on both sheets. Any ideas on how to fix this?

        SpreadsheetDocument ssDoc = SpreadsheetDocument.Create(saveFile, SpreadsheetDocumentType.Workbook);

        // Add a WorkbookPart to the document
        WorkbookPart workbookPart = ssDoc.AddWorkbookPart();
        workbookPart.Workbook = new Workbook();
        // Add a WorksheetPart to theWorkbookPart
        WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet(new SheetData());

        Sheets sheets = ssDoc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

        Sheet sheet1 = new Sheet()
        {   Id = ssDoc.WorkbookPart.GetIdOfPart(worksheetPart),
            SheetId = 1, Name = "Sheet1"
        };

        Sheet sheet2 = new Sheet()
        {
            Id = ssDoc.WorkbookPart.GetIdOfPart(worksheetPart),
            SheetId = 2, Name = "Sheet2"
        };

        sheets.Append(sheet1);
        sheets.Append(sheet2);
        Worksheet worksheet = new Worksheet();
        SheetData sheetData = new SheetData();

        Row headerRow = new Row();
        Cell emptyCell = CreateTextCell(cellHeader, index, "");
        headerRow.Append(emptyCell);

        Row newRow = new Row();
        Cell mycell = CreateTextCell(cellHeader, index, "data");
        newRow.Append(mycell);

        sheetData.Append(headerRow);
        sheetData.Append(newRow);

        worksheet.Append(sheetData);            
        worksheetPart.Worksheet = worksheet;

        ssDoc.Close();

Answer 1

For each Excel sheet (that has separate data)

• a separate WorkSheetPart object is needed
• a separate WorkSheet object is needed
• a separate SheetData object is needed
• a separate Sheet object is needed

It would look like this:

SpreadsheetDocument ssDoc = SpreadsheetDocument.Create(saveFile,
    SpreadsheetDocumentType.Workbook);

WorkbookPart workbookPart = ssDoc.AddWorkbookPart();
workbookPart.Workbook = new Workbook();

Sheets sheets = ssDoc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

// Begin: Code block for Excel sheet 1
WorksheetPart worksheetPart1 = workbookPart.AddNewPart<WorksheetPart>();
Worksheet workSheet1 = new WorkSheet();
SheetData sheetData1 = new SheetData();

// the data for sheet 1
Row rowInSheet1 = new Row();
Cell emptyCell = CreateTextCell(cellHeader, index, "");
rowInSheet1.Append(emptyCell);

sheetData1.Append(rowInSheet1);

worksheet1.AppendChild(sheetData1);
worksheetPart1.Worksheet = workSheet1;

Sheet sheet1 = new Sheet()
{
    Id = ssDoc.WorkbookPart.GetIdOfPart(worksheetPart1),
    SheetId = 1,
    Name = "Sheet1"
};
sheets.Append(sheet1);
// End: Code block for Excel sheet 1

// Begin: Code block for Excel sheet 2
WorksheetPart worksheetPart2 = workbookPart.AddNewPart<WorksheetPart>();
Worksheet workSheet2 = new WorkSheet();
SheetData sheetData2 = new SheetData();

// the data for sheet 2
Row rowInSheet2 = new Row();
Cell mycell = CreateTextCell(cellHeader, index, "data");
rowInSheet2.Append(mycell);

sheetData2.Append(rowInSheet2);

worksheet2.AppendChild(sheetData2);
worksheetPart2.Worksheet = workSheet2;

Sheet sheet2 = new Sheet()
{
    Id = ssDoc.WorkbookPart.GetIdOfPart(worksheetPart2),
    SheetId = 2,
    Name = "Sheet2"
};
sheets.Append(sheet2);
// End: Code block for Excel sheet 2

ssDoc.Close();

I don't know why it has to be so complicated. I just found it by skimming through a few random blog and forum posts and a lot trial and error.