Fogli multipli OpenXML

Sto aggiungendo più fogli a una cartella di lavoro di Excel. Voglio avere una riga su un foglio e l’altra fila sull’altro foglio. Questo codice inserisce entrambe le righe su entrambi i fogli. Qualche idea su come risolvere questo problema?

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.Worksheet = new Worksheet(new SheetData()); Sheets sheets = ssDoc.WorkbookPart.Workbook.AppendChild(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(); 

Per ogni foglio Excel (che ha dati separati)

  • è necessario un object WorkSheetPart separato
  • è necessario un object WorkSheet separato
  • è necessario un object SheetData separato
  • è necessario un object Sheet separato

Sarebbe come questo:

 SpreadsheetDocument ssDoc = SpreadsheetDocument.Create(saveFile, SpreadsheetDocumentType.Workbook); WorkbookPart workbookPart = ssDoc.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); Sheets sheets = ssDoc.WorkbookPart.Workbook.AppendChild(new Sheets()); // Begin: Code block for Excel sheet 1 WorksheetPart worksheetPart1 = workbookPart.AddNewPart(); 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(); 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(); 

Non so perché debba essere così complicato. L’ho appena trovato sfogliando alcuni post di blog e forum casuali e un sacco di prove ed errori.

Spero che il mio codice qui sotto possa aiutarti.

 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(); foreach (DataTable table in tableSet.Tables) { WorksheetPart wSheetPart = wBookPart.AddNewPart(); 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++; } } } 

Fammi sapere se hai problemi.

Sembra che tu stia tralasciando del codice qui, ma penso che dovresti aggiungere i fogli, non l’altro (i valori non in questo codice)

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

Qualcosa di simile sembra più appropriato.

Questo sembra essere il motivo per cui entrambe le pagine vengono eseguite.

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

È necessario creare un altro sheetData (non creato in questo blocco di codice) per inviarlo all’altro foglio di lavoro o provare un metodo come ho menzionato sopra.