摘要:[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 }
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')))
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 }
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堂課》,為培養台灣未來的領袖而努力。 |