DocumentFormat.Openxml将数据表添加到工作表中,而不循环到行中
本文关键字:循环 工作 Openxml 数据表 添加 DocumentFormat | 更新日期: 2023-09-27 18:09:41
是否有任何方法可以直接将数据表插入工作表而不循环数据行?我有一个非常大的数据表超过200k行。因此,在执行导出excel表时,循环遍历每行需要时间。有人能建议下面的方法是我使用的代码片段
foreach (DataRow dr in dt.Rows)
{
++rowIndex;
writer.WriteStartElement(new Row { RowIndex = rowIndex });
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
cellValue = ReplaceHexadecimalSymbols(cellValue);
cellReference = excelColumnNames[colInx] + rowIndex.ToString();
// Create cell with data
if (IsIntegerColumn[colInx] || IsFloatColumn[colInx])
{
// For numeric cells without any decimal places.
// If this numeric value is NULL, then don't write anything to the Excel file.
cellFloatValue = 0;
bool bIncludeDecimalPlaces = IsFloatColumn[colInx];
if (double.TryParse(cellValue, out cellFloatValue))
{
cellValue = cellFloatValue.ToString(CultureInfo.InvariantCulture);
AppendNumericCell(cellReference, cellValue, bIncludeDecimalPlaces, writer);
}
}
else if (IsDateColumn[colInx])
{
// For date values, we save the value to Excel as a number, but need to set the cell's style to format
// it as either a date or a date-time.
DateTime dateValue;
if (DateTime.TryParse(cellValue, out dateValue))
{
AppendDateCell(cellReference, dateValue, writer);
}
else
{
// This should only happen if we have a DataColumn of type "DateTime", but this particular value is null/blank.
AppendTextCell(cellReference, cellValue, writer);
}
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(cellReference, cellValue, writer);
}
}
writer.WriteEndElement(); // End of Row
}
我不得不做同样的事情。我需要传递数据表并让它创建电子表格。
http://lateral8.com/articles/2010/3/5/openxml-sdk-20-export-a-datatable-to-excel.aspxusing System.Data;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
public void ExportDataTable(
DataTable table,
string exportFile)
{
//create the empty spreadsheet template and save the file
//using the class generated by the Productivity tool
ExcelDocument excelDocument = new ExcelDocument();
excelDocument.CreatePackage(exportFile);
//populate the data into the spreadsheet
using (SpreadsheetDocument spreadsheet =
SpreadsheetDocument.Open(exportFile, true))
{
WorkbookPart workbook = spreadsheet.WorkbookPart;
//create a reference to Sheet1
WorksheetPart worksheet = workbook.WorksheetParts.Last();
SheetData data = worksheet.Worksheet.GetFirstChild<SheetData>();
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
foreach (DataColumn column in table.Columns)
{
Cell headerCell = createTextCell(
table.Columns.IndexOf(column) + 1,
1,
column.ColumnName);
header.AppendChild(headerCell);
}
data.AppendChild(header);
//loop through each data row
DataRow contentRow;
for (int i = 0; i < table.Rows.Count; i++)
{
contentRow = table.Rows[i];
data.AppendChild(createContentRow(contentRow, i + 2));
}
}
}
辅助方法:
#region WorkBook Methods
/// <summary>
/// Gets the Excel column name based on a supplied index number.
/// </summary>
/// <returns>1 = A, 2 = B... 27 = AA, etc.</returns>
private string getColumnName(int columnIndex)
{
int dividend = columnIndex;
string columnName = String.Empty;
int modifier;
while (dividend > 0)
{
modifier = (dividend - 1) % 26;
columnName =
Convert.ToChar(65 + modifier).ToString() + columnName;
dividend = (int)((dividend - modifier) / 26);
}
return columnName;
}
private Cell createTextCell(
int columnIndex,
int rowIndex,
object cellValue)
{
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
cell.CellReference = getColumnName(columnIndex) + rowIndex;
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = cellValue.ToString();
inlineString.AppendChild(t);
cell.AppendChild(inlineString);
return cell;
}
private Row createContentRow(
DataRow dataRow,
int rowIndex)
{
Row row = new Row
{
RowIndex = (UInt32)rowIndex
};
for (int i = 0; i < dataRow.Table.Columns.Count; i++)
{
Cell dataCell = createTextCell(i + 1, rowIndex, dataRow[i]);
row.AppendChild(dataCell);
}
return row;
}
#endregion