OpenXML Multiple Sheets

本文关键字：Sheets Multiple OpenXML | 更新日期: 2023-09-27 18:22:23

我正在将多张工作表添加到excel工作簿中。我想在一张纸上放一行，在另一张上放另一行。此代码将两行都放在两张图纸上。关于如何解决这个问题有什么想法吗？

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

OpenXML Multiple Sheets

对于每个Excel工作表（具有单独的数据），

  • 需要一个单独的WorkSheetPart对象
  • 需要一个单独的WorkSheet对象
  • 需要一个单独的SheetData对象
  • 需要一个单独的Sheet对象

它看起来是这样的：

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

我不知道为什么要这么复杂。我只是通过浏览一些随机的博客和论坛帖子以及大量的试错发现了它。

希望我下面的代码能帮到你。

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++;
            }                                
        }
    }

如果你有任何问题，请告诉我。

这里似乎遗漏了一些代码，但我认为应该附加Sheets，而不是其他（值不在该代码中）

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

这样的事情似乎更合适。

这似乎就是为什么这两页都受到影响的原因。

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

您要么需要制作另一个sheetData（不是在这个代码块中创建的）来发送到另一个工作表，要么尝试上面提到的方法。

相关文章：