更快地创建 Excel(互操作)

Question

我确定了减慢进程的代码是这样的(我正在填充单元格(：

我在这里所做的基本上是使用数据集从数据库中加载一些数据。

Microsoft.Office.Interop.Excel.Range range1 = null;
Microsoft.Office.Interop.Excel.Range cell1 = null;
Microsoft.Office.Interop.Excel.Borders border1 = null;
for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
int s = i + 1;
for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
xlWorkSheet.Cells[s + 1, j + 1] = data;
range1 = xlWorkSheet.UsedRange;
cell1 = range1.Cells[s + 1, j + 1];
border1 = cell1.Borders;

if (((IList)terms).Contains(xlWorkSheet.Cells[1, j + 1].Value.ToString()))
{
cell1.Interior.Color = System.Drawing.Color.Red;
}
range1.Columns.AutoFit();
range1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
border1.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border1.Weight = 2d;
}
}

加载整个东西有时需要 1 分钟以上。有没有优化它？

Answer 1

逐个单元格是使用互操作与 Excel 交互的最慢方式 - 查找如何在一次操作中将数据从数组添加到工作表。

例如

将数组写入 Excel 范围

显示了这种方法。

Answer 2

互操作库非常慢，并且花费了大量的系统资源。

无需使用互操作库来创建 Excel 文件，只需使用它 OpenXML 库即可。 我在生产中使用它。超过 100 万行，将数据集导出到 excel 文件只需大约 10 秒。

下面是引用自的示例代码：

使用 c# 中的 Open Xml SDK 将数据表导出到 Excel

private void ExportDSToExcel(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();
uint sheetId = 1;
foreach (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);
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 (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 (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);
}
}
}
}