程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 編程語言 >> .NET網頁編程 >> C# >> 關於C# >> OWC生成Excel的效能優化

OWC生成Excel的效能優化

編輯:關於C#

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

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

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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved