[ASP.NET]簡易自訂Excel匯出格式

摘要:[ASP.NET]簡易自訂Excel匯出格式

以下function可以傳入DataTable後輕易的做到Excel下載的功能,主要是透過<Table>的方式來配置每個欄位,因此只要Html Table可以做到的設定,這個function可以做的出來喔:

01     /// <summary>
02     /// 傳入DataTable後,透過Response.Write將資料寫到client端,並指定為Excel格式
03     /// </summary>
04     /// <param name="pDataTable">資料來源</param>

05     public void ExportDataTableToExcel(DataTable pDataTable)
06     {
07         int tRowCount = pDataTable.Rows.Count;
08         int tColumnCount = pDataTable.Columns.Count;
09
10         Response.Expires = 0;
11         Response.Clear();
12         Response.Buffer = true;
13         Response.Charset = "utf-8";
14         Response.ContentEncoding = System.Text.Encoding.UTF8;
15         Response.ContentType = "application/vnd.ms-excel";
16         Response.AddHeader("Content-Disposition", "attachment; filename=Excel.xls");
17         Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");
18
19         Response.Write("<Table borderColor=black border=1>");
20         Response.Write("\n <TR>");
21         for (int i = 0; i < tColumnCount; i++)
22         {
23             Response.Write("\n <TD  bgcolor = #fff8dc>");
24             Response.Write(pDataTable.Columns[i].ColumnName);
25             Response.Write("\n </TD>");
26         }

27         Response.Write("\n </TR>");
28         for (int j = 0; j < tRowCount; j++)
29         {
30             Response.Write("\n <TR>");
31             for (int k = 0; k < tColumnCount; k++)
32             {
33                 Response.Write("\n <TD align=\"right\" x:num>");
34                 Response.Write(pDataTable.Rows[j][k].ToString());
35                 Response.Write("\n </TD>");
36             }

37             Response.Write("\n </TR>");
38         }

39
40         Response.Write("</Table>");
41
42         Response.End();
43     }

拿Northwind的Products資料表簡單的測試一下,發現果然如我們所預期,將Products整個Table的資料都匯出來囉:

也可以做欄位加總:

但我們發現UnitPrice這個欄位所存的值跟資料庫的值有所差異,資料庫中設定是小數位數4位,但匯出後小數點後的數值都不見了,如果我們要保留UnitPrice的小數點位數該怎麼做呢?

Step1.先取得欄位的schema小數位數,透過以下的SQL指令可取得Products欄位的schema

SELECT xprec,xscale,name AS ColumnName, type_name(xusertype) AS ColumnType  
FROM syscolumns WHERE (id = (SELECT objid=id FROM sysobjects WHERE id=object_id('Products')))

 

看到UnitPrice這個欄位是小數點四位數,接著小改一下前面的code:

01     /// <summary>
02     /// 傳入DataTable後,透過Response.Write將資料寫到client端,並指定為Excel格式
03     /// </summary>
04     /// <param name="pDataTable">資料來源</param>

05     public void ExportDataTableToExcel(DataTable pDataTable)
06     {
07         int tRowCount = pDataTable.Rows.Count;
08         int tColumnCount = pDataTable.Columns.Count;
09
10         Response.Expires = 0;
11         Response.Clear();
12         Response.Buffer = true;
13         Response.Charset = "utf-8";
14         Response.ContentEncoding = System.Text.Encoding.UTF8;
15         Response.ContentType = "application/vnd.ms-excel";
16         Response.AddHeader("Content-Disposition", "attachment; filename=Excel.xls");
17         Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");
18
19         Response.Write("<Table borderColor=black border=1>");
20         Response.Write("\n <TR>");
21         for (int i = 0; i < tColumnCount; i++)
22         {
23             Response.Write("\n <TD  bgcolor = #fff8dc>");
24             Response.Write(pDataTable.Columns[i].ColumnName);
25             Response.Write("\n </TD>");
26         }

27         Response.Write("\n </TR>");
28         for (int j = 0; j < tRowCount; j++)
29         {
30             Response.Write("\n <TR>");
31             for (int k = 0; k < tColumnCount; k++)
32             {
33                 //如果是UnitPrice的話就補滿小數位數,小數位數部分可透過取得欄位的xscale來取得
34                 if (pDataTable.Columns[k].ColumnName == "UnitPrice")
35                 {
36                     Response.Write("\n<TD style='mso-number-format:\"0\\." + "0".PadLeft(4, '0') + "\";'>");
37                 }

38                 else
39                 {
40                     Response.Write("\n <TD align=\"right\" x:num>");
41                 }

42                 Response.Write(pDataTable.Rows[j][k].ToString());
43                 Response.Write("\n </TD>");
44             }

45             Response.Write("\n </TR>");
46         }

47
48         Response.Write("</Table>");
49
50         Response.End();
51     }

再匯出一次,我們發現UnitPrice欄位的小數位數出來囉,

這個方法或許不是最快的,但用到現在我覺得他彈性算是最大的,因為格式、顏色都可以自行定義。

游舒帆 (gipi)

探索原力Co-founder,曾任TutorABC協理與鼎新電腦總監,並曾獲選兩屆微軟最有價值專家 ( MVP ),離開職場後創辦探索原力,致力於協助青少年培養面對未來的能力。認為教育與組織育才其實息息相關,都是在為未來儲備能量,2018年起成立為期一年的專題課程《職涯躍升的關鍵24堂課》,為培養台灣未來的領袖而努力。