数据向Excel中写入的加速的办法.

 一个单元格一个单元格的写入Excel虽然不错,但是速度太慢,要重复的操作Excel对象. 在网上找到了个办法,是写入数组,然后由数组直写入Excel中,速度要快许多.

1. 生成Object数组的方法

1.         public object[,] GetObjectArray(System.Data.DataTable dt)
2.         {
3.             object[,] objData = new object[dt.Rows.Count, dt.Columns.Count];
4.             for (int i = 0; i < dt.Rows.Count; i++)
5.             {
6.                 //  string col ="A" + (i+2).ToString();
7.                 for (int j = 0; j < dt.Columns.Count; j++)
8.                 {
9.                     objData[i, j] = dt.Rows[i][j].ToString();
10.                 }
11.             }
12.             return objData;
13.         }

2. 数据操作的代码

1. Worksheet sheetUnknown;
2. sheetUnknown.Name = "NYBB";
5. Range rgeData = this.GetHeaderRange(dt, sheetUnknown);
6. object[] objHeader = 
7. {
8.     "CompanyClaimID","FullCOmpanyClaimID","ValueDate","ClaimantName","MEVStatus",
9.     "ClosedDate","Reserve","Deductible","TotalPaid","ExpensePaid","TotalIncurred",
10.     "Description","StatusID","Effective","NamedInsured","LossTypeID","CoverageID",
11.     "DateOfLoss","DateReported","LocationAddress","LocationCity","LocationState",
12.     "LocationZip","CauseID","Recovery"
13. };
14. rgeData.Value2 = objHeader;
16. //因为我的Excel表中有表头,所以Cell的起始行从2开始,到总行数+1... 表头的生成方法如下.
17. sheetUnknown.get_Range(sheetUnknown.Cells[2, 1], sheetUnknown.Cells[dt.Rows.Count + 1, dt.Columns.Count]).Value2 = GetObjectArray(dt);

3.生成表头的代码

1.         public Range GetHeaderRange(System.Data.DataTable dt,Worksheet worksheet)
2.         {
3.             Range rgeData = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, dt.Columns.Count]);
4.             rgeData.Font.Bold = true;
5.             rgeData.Font.Size = 9;
6.             rgeData.Interior.ColorIndex = 15;
7.             rgeData.HorizontalAlignment = XlHAlign.xlHAlignCenter;
8.             rgeData.EntireColumn.AutoFit();
9.             return rgeData;
10.         }

其中dt是DataTable对象,是由select查询结果返回的.   Worksheet是Office的Excel对象,我使用的是Office2003, 但是强烈使用Office2000,兼容性可能会好一些,Office中在引用了Excel.dll之后,可以用 Excel.对象名来操作对象.