[原创]OWC生成Excel的效能优化
优化前的代码, 从DataTable导入1000笔数据要差不多10分钟,有时候还会超时或出错:
public void WriteDataToSpreadsheet2(SpreadsheetClass p_spreadsheet, DataTable p_dt,
int p_iRow, int p_iCol, bool p_bWithCaption, int p_iStaredRecord,
int p_iLimited, int[] p_iTextColumns)
{
Worksheet f_sheet = p_spreadsheet.ActiveSheet;
int f_iRowCount = 0;
int f_iTotalRow = 0;
int f_iMaxRow = 65535;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
int f_iRow, f_iCol, f_iDtColumn;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
f_iRow = p_iRow;
f_iCol = p_iCol;
//使用标题
if (p_bWithCaption == true)
{
foreach (DataColumn f_col in p_dt.Columns)
{
f_sheet.Cells[f_iRow, f_iCol] = f_col.Caption;
f_iCol++;
}
f_iRow++;
f_iTotalRow++;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
f_iCol = p_iCol;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
foreach (DataRow f_row in p_dt.Rows)
{
f_iDtColumn = 0;
foreach (DataColumn f_col in p_dt.Columns)
{
if (f_row[f_col] != DBNull.Value)
{
//注意,这里是一列一列的赋值
if (Array.IndexOf(p_iTextColumns, f_iDtColumn) > -1)
f_sheet.Cells[f_iRow, f_iCol] = "'" + f_row[f_col].ToString(); //强制转换成字符串
else
f_sheet.Cells[f_iRow, f_iCol] = f_row[f_col];
}
f_iDtColumn++;
f_iCol++;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
f_iRowCount++;
f_iTotalRow++;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
if (f_iTotalRow >= f_iMaxRow)
break;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
if (p_iLimited > 0)
{
if (f_iRowCount >= p_iLimited)
break;
}
f_iRow++;
f_iCol = p_iCol;
}
return;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
优化后的代码, 从DataTable导入60000(注意,是六万)笔数据, 只要不到两分钟.
1
public void WriteDataToSpreadsheet(SpreadsheetClass p_spreadsheet, DataTable p_dt,
2
int p_iRow, int p_iCol, bool p_bWithCaption, int p_iStaredRecord,
3
int p_iLimited, int[] p_iTextColumns)
4
{
5
Worksheet f_sheet = p_spreadsheet.ActiveSheet;
6
int f_iRowCount = 0;
7
int f_iTotalRow = 0;
8
int f_iMaxRow = 65535;
9![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
10
int f_iRow, f_iCol, f_iDtColumn;
11![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
12
f_iRow = p_iRow;
13
f_iCol = p_iCol;
14![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
15
if (p_bWithCaption == true)
16
{
17
foreach (DataColumn f_col in p_dt.Columns)
18
{
19
f_sheet.Cells[f_iRow, f_iCol] = f_col.Caption;
20
f_iCol++;
21
}
22
f_iRow++;
23
f_iTotalRow++;
24![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
25
f_iCol = p_iCol;
26
}
27![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
28
string[] A2Z = new string[26] { "A", "B", "C", "D", "E", "F", "G",
29
"H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R",
30
"S", "T", "U", "V", "W", "X", "Y", "Z" };
31
List<string> A2ZZ = new List<string>();
32
A2ZZ.AddRange(A2Z);
33
foreach (string en in A2Z)
34
{
35
foreach (string en2 in A2Z)
36
{
37
A2ZZ.Add(en + en2);
38
}
39
}
40
string lastColumnName = A2ZZ[p_dt.Columns.Count - 1];
41![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
42
List<object[,]> list = new List<object[,]>();
43
Range r = f_sheet.get_Range("A" + f_iRow.ToString(), lastColumnName + f_iRow.ToString());
44
int row = p_dt.Rows.Count;
45
int col = p_dt.Columns.Count;
46![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
47
if (row > 0)
48
{
49
//先将每一行资料放入一个数组,然后再放入清单
50
for (int i = 0; i < row; i++)
51
{
52
object[,] objData = new Object[1, col];
53
for (int j = 0; j < col; j++)
54
{
55
if (p_dt.Rows[i][j] != DBNull.Value)
56
{
57
if (Array.IndexOf(p_iTextColumns, j) > -1)
58
objData[0, j] = "'" + p_dt.Rows[i][j].ToString();
59
else
60
objData[0, j] = p_dt.Rows[i][j];
61
}
62
else objData[0, j] = "";
63
}
64
list.Add(objData);
65
}
66
}
67
object m_objOpt = System.Reflection.Missing.Value;
68
for (int i = 0; i < row; i++)
69
{
70
//直接到一个区间,然后对区间直接赋数组作为值, 这是真正提速的地方< span style="color: #008000;">
71
Range r2 = r.get_Range("A" + (f_iRowCount+1).ToString(), lastColumnName + (f_iRowCount+1).ToString());
72
r2.set_Value(m_objOpt, list[i]);
73![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
74
f_iRowCount++;
75
f_iTotalRow++;
76![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
77
if (f_iTotalRow >= f_iMaxRow)
78
break;
79![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
80
if (p_iLimited > 0)
81
{
82
if (f_iRowCount >= p_iLimited)
83
break;
84
}
85
f_iRow++;
86
f_iCol = p_iCol;
87
}
88
return;
89
}
90![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
58
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
59
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
60
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
61
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
62
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
63
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
64
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
65
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
66
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
67
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
68
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
69
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
70
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
71
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
72
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
73
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
74
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
75
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
76
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
77
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
78
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
79
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
80
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
81
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
82
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
83
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
84
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
85
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
86
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
87
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
88
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
89
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
90
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
上面一些代码是有其他用的, 大家只要看注释那块的就行了. 人太懒, 不好意思. 呵呵.
这个方法只是我在项目中使用的代码, 时间比较紧, 肯定还有更快的方法, 如果各位有更好的方法, 请赐教.