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

score 72 · Answer 1 · edited Nov 17 '17 at 04:32

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.

Very elegant solution and much easier to work with than other solutions. Just a note, anyone looking for the code to CreateTextCell can find it here: https://msdn.microsoft.com/en-us/library/dd452407(v=office.12).aspx#code-snippet-5 — Jacques Koekemoer, Oct 12 '17 at 07:40
Pay attention to this point: a separate WorkSheetPart object is needed a separate WorkSheet object is needed a separate SheetData object is needed a separate Sheet object is needed — IamSierraCharlie, Feb 24 '20 at 06:38
Line with: Worksheet workSheet1 = new WorkSheet(); should be Worksheet workSheet1 = new Worksheet(); Similar for workSheet2. — John Kurtz, Nov 12 '20 at 16:12
Very helpful. Sheets can make use of the same instance of the styles and sharedStrings — KornMuffin, May 07 '22 at 19:59

score 13 · Answer 2 · answered Apr 24 '14 at 03:57

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.

score 0 · Answer 3 · answered Feb 02 '12 at 22:02

0

It seems that you are leaving out some code here, but I think you should be appending the Sheets, not the other (values not in this code)

sheet1.Append(headerRow);
sheet2.Append(newRow);

Something like this seems more appropriate.

This seems to be why both pages are being effected.

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

You either need to make another sheetData (not created in this code block) to send to the other worksheet, or try a method like I mentioned above.

answered Feb 02 '12 at 22:02

corylulu

3,449
1
19
35

I tried to append the rows to the sheet, but this is giving me an error. I have also tried using multiple sheetData's but I cannot figure out how to connect them to individual sheets. – Reed Feb 02 '12 at 22:29
Well you left that stuff out of this code block. We'll need more code to help. – corylulu Feb 02 '12 at 22:35
This is all the code I have. I tried your suggestion, but that doesn't appear to possible. And I don't know what I'm doing with the multiple sheetData's. That's why I came to you guys. – Reed Feb 02 '12 at 22:53
You reference both sheetData and worksheet and both of those variables are not created in this code. – corylulu Feb 02 '12 at 22:57
Oops. Ok you got me there. I have edited my code. Thanks for your patience :) – Reed Feb 03 '12 at 14:46

OpenXML Multiple Sheets

3 Answers3

Linked