在Excel中插入行时提高OpenXML的性能

Question

我使用OpenXML v2.8.1.0为网站创建excel报告。电子表格有22列。渲染15000行需要30秒，但对于网页来说太长了。我能更有效地做到这一点吗？

代码片段如下(省略了与问题无关的部分(。怀疑我在遍历每个单元格以设置其单元格引用，然后设置每行的rowIndex方面效率低下。

我省略了打开正在填充的电子表格的代码——它包含标题和一个示例详细信息行，该行具有我想要的所有格式，但所有值都为空。

在代码中，我克隆该行，为每个单元格设置值和单元格引用，为该行设置rowIndex，插入该行，然后从数据库中获取下一行数据并重复。

还是说每秒500行的速度与OpenXML的速度差不多？

谢谢。

public class MyReport
{
public byte[] Get(List<dbRow> dbRows)
{
// dbRows is the result of "Select col0, col1, ... col21 from dbTable"
// first 6 rows are the report headers
// row 7 is the blank detail row.
Row templateDetailRow = sheetData.Elements<Row>().Where(r => r.RowIndex == 7).First();
// start inserting data rows at row number 8
uint currentRowPointer = sheetData.Elements<Row>().LastOrDefault().RowIndex + 1; // start adding rows after the current last row
Row currentBottomRow = sheetData.Elements<Row>().LastOrDefault(); // initialize to last row before we start adding rows
foreach (dbRow r in dbRows)
{
Row newRow = (Row)templateDetailRow.Clone();
List<Cell> cells = newRow.Elements<Cell>().OrderBy(c => c.CellReference.Value).ToList();
SetCellsInRow(cells, r, currentRowPointer);
newRow.RowIndex = currentRowPointer;
InsertRow(sheetData, newRow);
currentRowPointer++;
}
}
private void SetCellsInRow(List<Cell> cells, dbRow source, uint rowIndex)
{
SetCell(cells[0], source.string0, rowIndex);
SetCell(cells[1], source.int1, rowIndex);
SetCell(cells[2], source.string2, rowIndex);
SetCell(cells[3], source.string3, rowIndex);
SetCell(cells[4], source.string4, rowIndex);
SetCell(cells[5], String.Format("{0:MM/dd/yyyy}", source.date5), rowIndex);
SetCell(cells[6], source.string6, rowIndex);
SetCell(cells[7], source.int7, rowIndex);
SetCell(cells[8], source.string8, rowIndex);
SetCell(cells[9], source.string9, rowIndex);
SetCell(cells[10], source.date10.ToString("MM/dd/yyyy"), rowIndex);
SetCell(cells[11], String.Format("{0:MM/dd/yyyy}", source.date11), rowIndex);
SetCell(cells[12], String.Format("{0:MM/dd/yyyy}", source.date12), rowIndex);
SetCell(cells[13], source.string13, rowIndex);
SetCell(cells[14], String.Format("{0:MM/dd/yyyy}", source.date14), rowIndex);
SetCell(cells[15], source.int15 ?? 0, rowIndex);
SetCell(cells[16], String.Format("{0:MM/dd/yyyy}", source.date16), rowIndex);
SetCell(cells[17], source.string17, rowIndex);
SetCell(cells[18], source.string18, rowIndex);
SetCell(cells[19], source.int19, rowIndex);
SetCell(cells[20], source.string20, rowIndex);
SetCell(cells[21], source.string21, rowIndex);
}
private void SetCell(Cell theCell, string val, uint rowIndex)
{   // a NON-SHARED string is going in to the cell
theCell.CellValue = new CellValue(val);
theCell.DataType = new EnumValue<CellValues>(CellValues.String);
theCell.CellReference = Regex.Replace(theCell.CellReference, @"d+", rowIndex.ToString());
}
private void SetCell(Cell theCell, int val, uint rowIndex)
{    // an int is going in to the cell
theCell.CellValue = new CellValue(val.ToString());
theCell.DataType = new EnumValue<CellValues>(CellValues.Number);
theCell.CellReference = Regex.Replace(theCell.CellReference, @"d+", rowIndex.ToString());
}
private static void InsertRow(SheetData sheetData, Row row)
{
sheetData.InsertAfter(row, currentBottomRow);
currentBottomRow = row;
}
}

Answer 1

在social.msdn:上收到了同样问题的好答案

忘记设置rowIndex和CellReference要快得多。相反，将它们保留为null，并使用AppendChild。这是我改进的代码：

public class MyReport
{
public byte[] Get(List<dbRow> dbRows)
{
// dbRows is the result of "Select col0, col1, ... col21 from dbTable"

foreach (dbRow r in dbRows)
{
Row newRow = new Row();
SetCell(r.string0, newRow);
SetCell(r.int1, newRow);
SetCell(r.string2, newRow);
SetCell(r.string3, newRow);
SetCell(r.string4, newRow);
SetCell(String.Format("{0:MM/dd/yyyy}", r.date5), newRow);
SetCell(r.string6, rowIndex);
SetCell(r.int7, rowIndex);
SetCell(r.string8, rowIndex);
SetCell(r.string9, rowIndex);
SetCell(r.date10.ToString("MM/dd/yyyy"), rowIndex);
SetCell(String.Format("{0:MM/dd/yyyy}", r.date11), rowIndex);
SetCell(String.Format("{0:MM/dd/yyyy}", r.date12), rowIndex);
SetCell(r.string13, rowIndex);
SetCell(String.Format("{0:MM/dd/yyyy}", r.date14), rowIndex);
SetCell(r.int15 ?? 0, rowIndex);
SetCell(String.Format("{0:MM/dd/yyyy}", r.date16), rowIndex);
SetCell(r.string17, rowIndex);
SetCell(r.string18, rowIndex);
SetCell(r.int19, rowIndex);
SetCell(r.string20, rowIndex);
SetCell(r.string21, rowIndex);
sheetData.AppendChild(newRow);
}
private void SetCell(string val, Row theRow)
{   // a NON-SHARED string is going in to the cell
Cell theCell = new Cell();
theCell.CellValue = new CellValue(val);
theCell.DataType = new EnumValue<CellValues>(CellValues.String);
theRow.AppendChild(theCell);
}
protected void SetCell(int val, Row theRow)
{    // an int is going in to the cell
Cell theCell = new Cell();
theCell.CellValue = new CellValue(val.ToString());
theCell.DataType = new EnumValue<CellValues>(CellValues.Number);
theRow.AppendChild(theCell);
}
}
}