使用C＃中的Open Xml SDK将DataTable导出到Excel

使用C＃中的Open Xml SDK将DataTable导出到Excel

我写了这个简单的例子。这个对我有用。我只用一个数据集和一个表进行了测试，但是我想这对您来说足够了。

考虑到我将所有单元格都视为String（甚至不是SharedStrings）。如果要使用SharedStrings，则可能需要稍微调整一下示例。

编辑：若要进行此工作，必须将Windowsbase和documentFormat.OpenXml引用添加到项目。

请享用，

private void ExportDataSet(DataSet ds, string destination)        { using (var workbook = Spreadsheetdocument.Create(destination, documentFormat.OpenXml.SpreadsheetdocumentType.Workbook)) {     var workbookPart = workbook.AddWorkbookPart();     workbook.WorkbookPart.Workbook = new documentFormat.OpenXml.Spreadsheet.Workbook();     workbook.WorkbookPart.Workbook.Sheets = new documentFormat.OpenXml.Spreadsheet.Sheets();     foreach (System.Data.DataTable table in ds.Tables) {         var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();         var sheetData = new documentFormat.OpenXml.Spreadsheet.SheetData();         sheetPart.Worksheet = new documentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);         documentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<documentFormat.OpenXml.Spreadsheet.Sheets>();         string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);         uint sheetId = 1;         if (sheets.Elements<documentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)         {  sheetId =      sheets.Elements<documentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;         }         documentFormat.OpenXml.Spreadsheet.Sheet sheet = new documentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };         sheets.Append(sheet);         documentFormat.OpenXml.Spreadsheet.Row headerRow = new documentFormat.OpenXml.Spreadsheet.Row();         List<String> columns = new List<string>();         foreach (System.Data.DataColumn column in table.Columns) {  columns.Add(column.ColumnName);  documentFormat.OpenXml.Spreadsheet.Cell cell = new documentFormat.OpenXml.Spreadsheet.Cell();  cell.DataType = documentFormat.OpenXml.Spreadsheet.CellValues.String;  cell.CellValue = new documentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);  headerRow.AppendChild(cell);         }         sheetData.AppendChild(headerRow);         foreach (System.Data.DataRow dsrow in table.Rows)         {  documentFormat.OpenXml.Spreadsheet.Row newRow = new documentFormat.OpenXml.Spreadsheet.Row();  foreach (String col in columns)  {      documentFormat.OpenXml.Spreadsheet.Cell cell = new documentFormat.OpenXml.Spreadsheet.Cell();      cell.DataType = documentFormat.OpenXml.Spreadsheet.CellValues.String;      cell.CellValue = new documentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //      newRow.AppendChild(cell);  }  sheetData.AppendChild(newRow);         }     } }        }