[原创]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;
int f_iRow, f_iCol, f_iDtColumn;
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++;
f_iCol = p_iCol;
}
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++;
}
f_iRowCount++;
f_iTotalRow++;
if (f_iTotalRow >= f_iMaxRow)
break;
if (p_iLimited > 0)
{
if (f_iRowCount >= p_iLimited)
break;
}
f_iRow++;
f_iCol = p_iCol;
}
return;
}
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; int f_iRow, f_iCol, f_iDtColumn; 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++; f_iCol = p_iCol; } 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++; } f_iRowCount++; f_iTotalRow++; if (f_iTotalRow >= f_iMaxRow) break; if (p_iLimited > 0) { if (f_iRowCount >= p_iLimited) break; } f_iRow++; f_iCol = p_iCol; } return;} 优化后的代码, 从DataTable导入60000(注意,是六万)笔数据, 只要不到两分钟.
1public 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
10 int f_iRow, f_iCol, f_iDtColumn;
11
12 f_iRow = p_iRow;
13 f_iCol = p_iCol;
14
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
25 f_iCol = p_iCol;
26 }
27
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
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
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
74 f_iRowCount++;
75 f_iTotalRow++;
76
77 if (f_iTotalRow >= f_iMaxRow)
78 break;
79
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
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
10
int f_iRow, f_iCol, f_iDtColumn;11
12
f_iRow = p_iRow;13
f_iCol = p_iCol;14
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
25
f_iCol = p_iCol;26
}27
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
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
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
else60
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
74
f_iRowCount++;75
f_iTotalRow++;76
77
if (f_iTotalRow >= f_iMaxRow)78
break;79
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
上面一些代码是有其他用的, 大家只要看注释那块的就行了. 人太懒, 不好意思. 呵呵.
这个方法只是我在项目中使用的代码, 时间比较紧, 肯定还有更快的方法, 如果各位有更好的方法, 请赐教.
