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;
}

優化後的代碼, 從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

上面一些代碼是有其他用的, 大家只要看注釋那塊的就行了. 人太懶, 不好意思. 呵呵.

這個方法只是我在項目中使用的代碼, 時間比較緊, 肯定還有更快的方法, 如果各位有更好的方法, 請賜教.