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.

Answer 2

Hope my code below can help you.

private void exportDocument(string FilePath, DataTable sourceTable)
    {
        WorkbookPart wBookPart = null;
        DataSet tableSet = getDataSet(sourceTable);//getDataSet is my local function which is used to split a datatable into some datatable based on limited row I've declared.
        using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Create(FilePath, SpreadsheetDocumentType.Workbook))
        {
            wBookPart = spreadsheetDoc.AddWorkbookPart();
            wBookPart.Workbook = new Workbook();
            uint sheetId = 1;
            spreadsheetDoc.WorkbookPart.Workbook.Sheets = new Sheets();
            Sheets sheets = spreadsheetDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>();

            foreach (DataTable table in tableSet.Tables)
            {
                WorksheetPart wSheetPart = wBookPart.AddNewPart<WorksheetPart>();
                Sheet sheet = new Sheet() { Id = spreadsheetDoc.WorkbookPart.GetIdOfPart(wSheetPart), SheetId = sheetId, Name = "mySheet" + sheetId };
                sheets.Append(sheet);

                SheetData sheetData = new SheetData();
                wSheetPart.Worksheet = new Worksheet(sheetData);

                Row headerRow = new Row();
                foreach (DataColumn column in sourceTable.Columns)
                {
                    Cell cell = new Cell();
                    cell.DataType = CellValues.String;
                    cell.CellValue = new CellValue(column.ColumnName);
                    headerRow.AppendChild(cell);
                }
                sheetData.AppendChild(headerRow);

                foreach (DataRow dr in table.Rows)
                {
                    Row row = new Row();
                    foreach (DataColumn column in table.Columns)
                    {
                        Cell cell = new Cell();
                        cell.DataType = CellValues.String;
                        cell.CellValue = new CellValue(dr[column].ToString());
                        row.AppendChild(cell);
                    }
                    sheetData.AppendChild(row);
                }
                sheetId++;
            }                                
        }
    }

Let me know if you get any problems.