对不起,我的英语。我是开放xml SDK中的新手。我的程序能够将一些数据和DataTable导出到Excel文件(模板),在模板中,我将数据插入到一些占位符。它的工作原理非常好,但是我也需要插入一个数据表…我的示例代码:
using (Stream OutStream = new MemoryStream()) { // read teamplate using (var fileStream = File.OpenRead(templatePath)) fileStream.CopyTo(OutStream); // exporting Exporting(OutStream); // to start OutStream.Seek(0L, SeekOrigin.Begin); // out using (var resultFile = File.Create(resultPath)) OutStream.CopyTo(resultFile);
导出的下一种方法
private void Exporting(Stream template) { using (var workbook = SpreadsheetDocument.Open(template, true, new OpenSettings { AutoSave = true })) { // Replace shared strings SharedStringTablePart sharedStringsPart = workbook.WorkbookPart.SharedStringTablePart; IEnumerable<Text> sharedStringTextElements = sharedStringsPart.SharedStringTable.Descendants<Text>(); DoReplace(sharedStringTextElements); // Replace inline strings IEnumerable<WorksheetPart> worksheetParts = workbook.GetPartsOfType<WorksheetPart>(); foreach (var worksheet in worksheetParts) { DoReplace(worksheet.Worksheet.Descendants<Text>()); } int z = 40; foreach (System.Data.DataRow row in ExcelWorkXLSX.ToOut.Rows) { for (int i = 0; i < row.ItemArray.Count(); i++) { ExcelWorkXLSX.InsertText(workbook, row.ItemArray.ElementAt(i).ToString(), getColumnName(i), Convert.ToUInt32(z)); } z++; } } } }
但是这个片段要输出DataTable slooooooooooooooooooooooooowwwwwww …
如何快速,真实地将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); } } } }