用NPOI從DataBase到Excel

NPOI的C# Helper代碼

 1         public static void WriteExcel(DataTable dt, string filePath)
 2         {
 3             if (!string.IsNullOrEmpty(filePath) && dt.Rows.Count > 0)
 4             {
 5                 HSSFWorkbook wk = new HSSFWorkbook();
 6                 ISheet sheet = wk.CreateSheet(dt.TableName);
 7 
 8                 //列頭
 9                 IRow headerRow = sheet.CreateRow(0);
10                 for (int i = 0; i < dt.Columns.Count; i++)
11                 {
12                     headerRow.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
13                 }
14 
15                 //填充內容
16                 for (int i = 0; i < dt.Rows.Count; i++) //註意條件dt.Rows.Count
17                 {
18                     IRow row = sheet.CreateRow(i+1);
19                     for (int j = 0; j < dt.Columns.Count; j++)//註意條件dt.Columns.Count
20                     {
21                         row.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j])); //註意這裡寫法
22                     }
23                 }
24                 //寫入到客戶端
25                 using (MemoryStream ms = new MemoryStream())
26                 {
27                     wk.Write(ms);
28                     using (FileStream file = new FileStream(filePath, FileMode.Create, FileAccess.Write))
29                     {
30                         byte[] data = ms.ToArray();
31                         file.Write(data,0,data.Length);
32                         file.Flush();
33                     }
34                     wk = null;
35                 }
36 
37             }
38         }