如何加快将DataTable转储到Excel工作表中？ [英] How to speed up dumping a DataTable into an Excel worksheet?
问题描述
我有以下例程将DataTable转储到Excel工作表中。
I have the following routine that dumps a DataTable into an Excel worksheet.
private void RenderDataTableOnXlSheet(DataTable dt, Excel.Worksheet xlWk,
string [] columnNames, string [] fieldNames)
{
// render the column names (e.g. headers)
for (int i = 0; i < columnNames.Length; i++)
xlWk.Cells[1, i + 1] = columnNames[i];
// render the data
for (int i = 0; i < fieldNames.Length; i++)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
xlWk.Cells[j + 2, i + 1] = dt.Rows[j][fieldNames[i]].ToString();
}
}
}
无论什么原因，转储DataTable 25列和400行在我相对现代的电脑上大约需要10-15秒。使用更长的测试仪器。
For whatever reason, dumping DataTable of 25 columns and 400 rows takes about 10-15 seconds on my relatively modern PC. Takes even longer testers' machines.
有没有什么可以加快这个代码？或者是互操作本来就很慢？
Is there anything I can do to speed up this code? Or is interop just inherently slow?
解决方案：根据Helen Toomik的建议，我修改了该方法，现在应该为几种常见的数据类型（int32， double，datetime，string）。随意扩展它。处理我的数据集的速度从15秒变为1以下。
SOLUTION: Based on suggestions from Helen Toomik, I've modified the method and it should now work for several common data types (int32, double, datetime, string). Feel free to extend it. The speed for processing my dataset went from 15 seconds to under 1.
private void RenderDataTableOnXlSheet(DataTable dt, Excel.Worksheet xlWk, string [] columnNames, string [] fieldNames)
{
Excel.Range rngExcel = null;
Excel.Range headerRange = null;
try
{
// render the column names (e.g. headers)
for (int i = 0; i < columnNames.Length; i++)
xlWk.Cells[1, i + 1] = columnNames[i];
// for each column, create an array and set the array
// to the excel range for that column.
for (int i = 0; i < fieldNames.Length; i++)
{
string[,] clnDataString = new string[dt.Rows.Count, 1];
int[,] clnDataInt = new int[dt.Rows.Count, 1];
double[,] clnDataDouble = new double[dt.Rows.Count, 1];
string columnLetter = char.ConvertFromUtf32("A".ToCharArray()[0] + i);
rngExcel = xlWk.get_Range(columnLetter + "2", Missing.Value);
rngExcel = rngExcel.get_Resize(dt.Rows.Count, 1);
string dataTypeName = dt.Columns[fieldNames[i]].DataType.Name;
for (int j = 0; j < dt.Rows.Count; j++)
{
if (fieldNames[i].Length > 0)
{
switch (dataTypeName)
{
case "Int32":
clnDataInt[j, 0] = Convert.ToInt32(dt.Rows[j][fieldNames[i]]);
break;
case "Double":
clnDataDouble[j, 0] = Convert.ToDouble(dt.Rows[j][fieldNames[i]]);
break;
case "DateTime":
if (fieldNames[i].ToLower().Contains("time"))
clnDataString[j, 0] = Convert.ToDateTime(dt.Rows[j][fieldNames[i]]).ToShortTimeString();
else if (fieldNames[i].ToLower().Contains("date"))
clnDataString[j, 0] = Convert.ToDateTime(dt.Rows[j][fieldNames[i]]).ToShortDateString();
else
clnDataString[j, 0] = Convert.ToDateTime(dt.Rows[j][fieldNames[i]]).ToString();
break;
default:
clnDataString[j, 0] = dt.Rows[j][fieldNames[i]].ToString();
break;
}
}
else
clnDataString[j, 0] = string.Empty;
}
// set values in the sheet wholesale.
if (dataTypeName == "Int32")
rngExcel.set_Value(Missing.Value, clnDataInt);
else if (dataTypeName == "Double")
rngExcel.set_Value(Missing.Value, clnDataDouble);
else
rngExcel.set_Value(Missing.Value, clnDataString);
}
// figure out the letter of the last column (supports 1 letter column names)
string lastColumn = char.ConvertFromUtf32("A".ToCharArray()[0] + columnNames.Length - 1);
// make the header range bold
headerRange = xlWk.get_Range("A1", lastColumn + "1");
headerRange.Font.Bold = true;
// autofit for better view
xlWk.Columns.AutoFit();
}
finally
{
ReleaseObject(headerRange);
ReleaseObject(rngExcel);
}
}
private void ReleaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch
{
obj = null;
}
finally
{
GC.Collect();
}
}
推荐答案
一个一个地设置单元格值，一个批处理。
Instead of setting cell values one by one, do it in a batch.
步骤1.将数据从DataTable传输到具有相同维度的数组。
Step 1. Transfer the data from your DataTable into an array with the same dimensions.
步骤2.定义跨越适当范围的Excel范围对象。
Step 2. Define an Excel Range object that spans the appropriate range.
步骤3.将Range.Value设置为数组。
Step 3. Set the Range.Value to the array.
这将更快，因为您将跨Interop边界共有两个调用（一个获取Range对象，一个设置其值）而不是每个单元格的两个（获取单元格，设置值）。
This will be a lot faster because you will have a total two calls across the Interop boundary (one to get the Range object, one to set its value), instead of two per cell (get cell, set value).
在 MSDN知识库文章302096 。
这篇关于如何加快将DataTable转储到Excel工作表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
