將GridView裡資料匯出成Excel檔

在網頁上展示報表資料，用GridView是很好的方式
有時候報表要做成不同的圖表顯示，利用Chart控制項來製作也不錯

另一種方式，就是把資料匯出成Excel
要做其他的分析、華麗的圖表也是一種方式

這篇文章就來說說
怎麼將GridView裡的欄位資料輸出到Excel檔案
供使用者來做其他使用

我以前從書上看過這樣類似的範例
是vb.net的書籍
用的方法是調用 Microsoft.Office.Tools.Excel 的命名空間(Namespace)
很專業的方式，可以參考MSDN文章
http://msdn.microsoft.com/zh-tw/library/microsoft.office.tools.excel(v=vs.100).aspx

不過從這個命名空間的層級看來
大概也可以知道是不適合用在WEB FORM的一個物件模型
(ASP.NET網頁一開始就直接調用了 System.Web 並使用階層下的物件)
所以要使用這種方法還有一個條件
就是在Server端也要裝相應版本的Excel
在Browser端也要裝相應版本的Excel
因為在Brower端執行時，還是透過Server的 Automation (ActiveX EXE) 操控 Office ，只是Framework幫你包裝好一些 Namespace 讓你比較好運用而已。(講到要操控Server端的Office，應該會因為browser的權限不足所以根本無法使用吧…光create object 就會出問題了，browser 能在伺服器端產生物件…那權力也大的危險了)

這邊說的方式是比較偷龍轉鳳的方式
利用的是Excel本身支持豐富的文件格式(所以本身還帶自動解析識別)

把網頁資料匯出成Excel的方式
dnowba 有鑽研過(k了不少書+爬了不少文)
方法至少有六種

這篇文章是以一個「需求」的角度來寫的
控制excel的方式並不是最好的

對於B/S (Browser/Server ，即WEB應用) 結構來說
我們也許只是想小小的應用一下excel來產生報表
並不需要擁有太多的控制能力

在此打住，直接提供主範例DEMO

◎以上程式範例AspNet40.aspx，如在頁框下不能操作，請開新視窗操作
◎如果有問題歡迎您提出，dnowba很需要有人和我一起討論

頁面佈置

一個GridView控制項(加上分頁、排序、樞紐選取功能，並自動格式化樣式)，用來顯示資料，繫結方式此處省略了。
一個Button控制項，按下後把GridView資料匯出成Excel檔

程式碼

    Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click
        Response.Clear() '從緩衝區資料流清除所有輸出資料，放在最前面
        Response.Buffer = True '開啟緩衝輸出
        Response.Charset = "UTF-8" '設定使用哪個字符集，中文的話也可以是BIG5

        Response.AppendHeader("Content-Disposition", "attachment;filename=" & FormatDateTime(Now, DateFormat.ShortDate) & "_" & FormatDateTime(Now, DateFormat.ShortTime) & "_dnowreport.xls")
        '上面這句是要在<header>標記加參數，參數是匯出成Excel時的檔案名稱，副檔名一定要有，可以寫死(如下)
        '不過如果是有紀錄日期時間性的excel報表，可以用日期時間的方式來設計檔案名
        'Response.AppendHeader("Content-Disposition", "attachment;filename=test.xls")
        '另外時間的寫法也很多種，比方說可以寫成  DateTime.Today.ToShortDateString()
        '  Response.AppendHeader，有人寫Response.AddHeader

        Response.ContentEncoding = Encoding.GetEncoding("UTF-8") 'Excel 的編碼
        Response.ContentType = "application/vnd.ms-excel" '設定HTTP MIME類型

        Me.GridView1.EnableViewState = False

        Dim SW As New System.IO.StringWriter
        '這行在開一個容器(system.text.stringbuilder)，並作好寫成io的準備

        Dim HTW As New System.Web.UI.HtmlTextWriter(SW)
        '上面的HtmlTextWriter是一個動作，將ASP.NET的控制項所含的標記及內容寫到資料流
        ' HtmlTextWriter 提供 TextWriter 物件，此物件將透過內建 HttpResponse 物件來存取。

        Me.GridView1.AllowPaging = False
        Me.GridView1.AllowSorting = False
        Me.GridView1.Columns(0).Visible = False
        Me.GridView1.DataBind()
        ' 如果是有設定「分頁」功能但又希望輸出全部的資料時，可以在這裡先關掉分頁，重新繫結一次資料
        ' 同理，若有設定「排序」、「樞紐選取」功能的話，可以在這裡先處理。

        Me.GridView1.RenderControl(HTW)
        '上面這行用途在傳送GridView控制項內容至HtmlTextWriter物件，這個物件會把內容寫入用戶端裡

        Response.Write(SW.ToString())
        Response.End() '完成response後要手動關閉，不然上面「寫到資料流」的動作不會停止

        'Me.GridView1.AllowPaging = True
        'Me.GridView1.AllowSorting = True
        'Me.GridView1.Columns(0).Visible = True
        'Me.GridView1.DataBind()
        '' 前面為了輸出完整資料，所以手動關閉了分頁功能，這邊可以再開啟，不要忘了還是要重新綁定一次資料
        '' 同理…排序、樞紐選取等功能也可以在此處還原。
        '  後來證明，這些「還原步驟」根本不需要…
        '  因為前面執行到資料輸出到excel的階段後，好像因為程式被中斷所以並沒有把這樣的回傳執行到brower端上。

    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
        '這個事件內沒有程序，但不加上的話程式會出現錯誤，錯誤訊息為：
        '型別 'GridView' 的控制項 'GridView1' 必須置於有 runat=server 的表單標記之中。
        'MSDN裡有寫為什麼：
        'http://msdn.microsoft.com/zh-tw/library/system.web.ui.page.verifyrenderinginserverform.aspx

        '如果不想加這個事件的話，可以有第二種解法
        ' 就是在web.config裡設定
        ' <pages enableEventValidation="false"></pages>

    End Sub

在按下按鈕時，不知道你有沒有產生像我一樣的錯誤訊息：
「您正嘗試開啟xxxx.xls。其檔案格式與副檔名所指定的格式不同，開啟檔案前，請確認檔案未損毀，且來自信任的來源」

如果有的話，按「是」還是可以把檔案打開來，打開後可以發現，excel上顯示的樣式和在GridView上顯示的樣式應該是一個樣。在檔案名稱、工作表名稱被置換成我們設定好的檔案名。

先不管錯誤訊息吧 ! 這邊來說說我們怎麼偷龍轉鳳的
前面提到過
這個方式是應用了excel對文件格式的「支援度」來生成的
這裡應用的就是Excel對「HTML」的支援

我們來實作一次吧!!!

 

◎EXCEL 另存成 HTML ，支援度如何？

先開個新的EXCEL檔案，隨便在欄位裡輸入一些資料
這邊故意設計「欄位有底色」「表格有框線」「超連結」「EXCEL加總函數」「EXCEL 註解工具」…


另存成網頁 (*.htm,*.html)」格式，只儲存選取的工作表

發佈為網頁，其他的功能都不使用

存完後，在瀏覽器上檢視，發現了嗎，畫面上看不到「框線」、「EXCEL加總函數」「EXCEL 註解工具」(註：其實用IE開是看得到「框線」的，但是Chrome上看不到，應該是有不符合W3C的寫法)

再來我們用記事本打開看看，哇哩，就是一個網頁的架構(廢話)
不過很齊全捏，有META標記(我很久沒下這種標記了= =)，連charset這種語系設定都貼心的準備了)

連CSS 樣式表都有，現在是怎樣…

這些欄位說穿了就是TABLE (不過自動生成的TABLE不太乾淨，又有行距又有背景產色，既然上面都寫CSS、設計了STYLE的文件了，怎麼不一股作氣…好吧，算我要求過份了點


底下5887，這個欄位在EXCEL上是用SUM()加總的，輸出後就直接呈現結果了，原來的函數樣子沒有了

從以上得證，EXCEL在另存成HTML的時候，會割捨一些不在W3C規範的內容。
DNOWBA曾經聽人說EXCEL的原生檔案是HTML、WORD也是…如果原生檔案是HTML的話，怎麼我會找不到「註解工具」「加總函數」之類的標記 。我想是非戰之罪，因為EXCEL的支援度太強大…太多的轉譯工具讓人不知本末。

其實上面的都還好，關鍵是如果另存成存成單一網頁檔案(mhtm)，生成的這個檔案會用MIME Type來做為傳遞檔案的根據，還會有MIME-type的宣告語法

◎如果把HTML的檔案，用EXCEL打開呢…

DNOWBA隨便找了一個HTML檔案，先用瀏覽器檢視一下

再來用EXCEL來開這個檔案，哇哩…除了最上方的巡覽列顯示不出來(是用FLASH做的)，圖片、欄位都還保留的不錯，支援度也太強了吧，誰沒事要把HTML檔案用EXCEL開，就算有也是因為HTML的表格要開，連圖片、網頁格式都支援到底，我衷心的建議，乾脆底下的工作表就直接變成「檢視HTML原始檔」，大家用EXCEL來寫網頁程式。

所以我們也可以把原生HTML的格式，改格式名，直接用EXCEL開嗎，注意表頭，我已經把原來的navigation.html，改成navigation.xls了，打開的結果就是…也太神奇了吧，excel真的不打算兼個小差嗎？

不過打開的時候出了錯誤訊息，這錯誤訊息和前面給的程式範例是一樣的…

◎所以…我可以偷龍轉鳳了嗎

就把網頁資料輸出成網頁格式 …什麼都不用轉
然後偷偷的改個格式類型，把html改成xls
看起來就像原生檔案啦
標記都留著也不怕excel讀不出來
因為看起來excel的標記規則好像比html更來得複雜
那就把剛剛另存的html檔拿來參考
現在我們直接用筆記本寫程式碼，依樣畫葫蘆
底下是HTML碼，把css、style等樣式設定先拿掉看起來比較乾淨

<html>
<head>
</head>
<body>
<div>
<table>
	<tr>
		<td>姓名</td>
		<td>部落格</td>
		<td>電話</td>
	</tr>
	<tr>
		<td>豬腦爸</td>
		<td>http://dnowba.blogspot.com</td>
		<td>0933888888</td>
	</tr>
	<tr>
		<td>豬腦媽</td>
		<td>http://dnowba.blogspot.com</td>
		<td>03-321143</td>
	</tr>
</table>
</div>
</body>
</html>

輸出後的結果如下圖…意料之中的是，對於欄位的轉換，excel會自動將看起來是數字的變數字，所以電話欄位中，0933888888把誤認成數字，把前面的0拿掉變成933888888了

所以我們如果想要把資料庫的資料轉到excel上，對於欄位值的型別以及顯示格式絕對是很大的挑戰，我們假設說我們在資料庫設定一個欄位是貨幣格式money，excel上沒有貨幣型別的，那如果我們輸出到GridView上又要用預設的格式$來顯示…那匯出到excel上應該是看到$500之類的值，我想這個值應該…會是字串、不會是數值掛格式…也就是說後面若要計算的話是會出錯的。當然這只是推論…一且都要實作才能確定，不過資料庫上的設定由是顯得重要…像上面這個例子，當然有人有提出解決的方案，我的做法只是很單純的，要求輸入電話時，03321143一定要寫成03-321143，我相信這樣子不管怎麼輸出excel都不會誤認他是數字。

不過我們可以確定的是用HTML語法的<TABLE>標記是可以生成EXCEL報表的

所以這篇文章主範例的作法順序就是：

1、「先產生」繫結好資料的GridView控制項
(因為GridView在瀏覽時的html碼就是table)
2、將GridView的資料含格式原封不動的輸出資料流
(在輸出的時候看是要用Unicode還是ANSI的類別，並提供編碼格式以便識別)
3、將產生的字串包成一個檔案，檔案類型就用xls
(就是偷龍轉鳳的精神，硬是把html格式的檔案類型改名， 讓broser端一開始就用excel打開)

◎主範例程式碼補充說明

1、用html格式來製作excel的缺點

因為是「偽」EXCEL，所以這個檔案若修改後存檔，會出現警告訊息：
「您所指定的檔案格式網頁無法支援這個工作表XXXX.xls中的某些功能，您要保持此活頁簿為這種格式嗎？」
若按「是」那檔案就還是用html來存
若按「否」的話，可以重新存成原生的xls，也只有這種情況下才能使用(保存) 你額外在excel的設計的圖表、函數等excel專有功能…

所以像這種簡易的輸出方式，是利用office能自動開啟(辨識)HTML文件的方便性所設計的，並不能對應軟體本身特有功能。打個比方說，如果想用這匯出的文件，再匯入到其他可匯入excel檔案的系統時，就一定會報錯。因為一般設計可匯入excel檔案，用的元件都是OLE…
這篇範例，你能做的就是輸出「報表」，就只是「報表」，拿來給人看，不是拿來給人應用的。如果你想用word檔來呈現報表，用這個範例改會非常的快。
如果是報表，也有朋友和我建議用csv來做，不過我知道csv是要自已寫字串去連結一堆逗號，然後

2、有關MIME TYPE…

MIME是什麼？這又說來話長了，MIME (Multipurpose Internet Mail Extensions) 原來是用在電子郵件的，信件中不管是純文字，或是包含聲音、影像等檔案，透過轉換編碼成二進制的方式傳送，收件方卻不知道要用什麼方式解碼，所以在MIME簡單的來說，就是Server在告訴Browser，我要傳給你什麼類型(編碼)的文件，透過交涉browser直接用對應的解碼器解碼。後來網際網路發達了，傳送不同類型的檔案不一定是透過mail，MIME 也逐漸成為HTTP協議標準的一部分。
有關MIME的詳細描述可以參考http://zh.wikipedia.org/wiki/MIME

我們可以試著把範例中有關MIME的幾行程式碼拿掉(主範例程式行13、行14)，按下按鈕後，並不會出現下載的對話視窗，畫面只是閃一下，好像只有PostBack而已

像這樣不設定MIME TYPE的話，瀏覽器也會自已辨別檔案的類型，像這個範例render出一堆的標記，瀏覽器就幫你自動識別成text/html，所以直接用瀏覽開畫面…

而MIME的交涉描述，是要放在檔頭，在http和mail通訊上，http的描述比較簡單

Mime-Version: 1.0                                //版本號：1.0  (http通訊不用)
Content-Type: text/plain; charset=Big5  //內容類型=文字";字符集=big5
Content-Transfer-Encoding: ANSI          //編碼方式=ANSI
Content-Description: cc:Mail note part   // 文件描述 (http通訊不用)

如上面，原始碼僅在HTML內容類型加入檔名設定及內容類型(ContentType)設定
php、asp或其他支援此功能的平台都可以使用。在ASP.NET上設定MIME的話，我們是用response的屬性 (這個就是查msdn，沒有為什麼)

MIME 是個協定(規範)，不是ASP.NET的物件，用MIME這種方式來輸出文件，就不限定於應用在ASP.NET平台上，用MIME TYPE的好處，依IE安全性設定，可能內崁於網頁或啟動Office軟體單獨開啟，或另存新檔後，直接透過檔案總管點選開啟。

最後提供輸出時，則依指定的內容類型來設定，這些內容類型字串是有團體組識iana在管理的，但是也可以自已定義，不過我想裡面的格式對一般使用者應該是夠用了。不同文件之內容類型很多，而且會變動的，有興趣的話直接都該組識去看
http://www.iana.org/assignments/media-types/index.html

3、heaer上面加上Response.AppendHeader("Content-Disposition", "attachment;filename=test.xls")的產因

能造成IE能判讀為Office文件的原因為內容類型的正確設定(就是上面的MIME設定)，檔名設定是便於使用者儲存檔案後，得以對應的Office軟體開啟，並預設了一個名稱供儲存，並非絕對必要。

我們把這句刪掉然後執行一次程式(主程式行6)，瀏覽器還是能正確判讀，只是沒有設定header，所以預設檔名變成和網頁名稱一樣(注意檔案名稱)，所以關鍵在於MIME的設定

又或者，我們把haeder裡的檔名改設為test.htm，還是可以用Office軟體開啟。(當然設定好副檔名，就不用讓使用者還要先開軟體再透過軟體讀檔案，直接檔案總管點二下就自動對應)

所以如果今天，我們要輸出的excel檔案， 是excel 2007專門的*.xlsx
你知道要怎麼做了嗎？只是改header有用嗎？…改MIME，找到對應的字串來修改
如果是我，我會說這麼龜毛做什麼，這種偽excel檔既然本來就不是EXCEL原生格式， 還管他是xls還是xlsx…

4、VerifyRenderingInServerForm 的事件的補充解釋

事件裡頭並沒有寫任何程式，不寫這個事件的話會有錯誤訊息：
型別GridView的控制項GridView1必須置於有runat=server 的表單標記之中…


關於這個錯誤產生的原因，歸因於ASP.NET控制項和<form runat=server></form>之間的關係，因為大部份的控制項都是需要透過PostBack機制來與Server進行事件處理的交涉並得到一些返回值的。所以在Server端放了一個驗證方式，看看我們的控制項有沒有run at server，這個驗證方式就是Page.VerifyRenderingInServerForm，它會在控制項進行Render、、AddAttributesToRender等事件時調用，用來驗明正身

        ' Override the Render method to ensure that this control
        ' is nested in an HtmlForm server control, between a <form runat=server>
        ' opening tag and a </form> closing tag.
        Protected Overrides Sub Render(ByVal writer As HtmlTextWriter)

            ' Ensure that the control is nested in a server form.
            If Not (Page Is Nothing) Then
                Page.VerifyRenderingInServerForm(Me)
            End If

            MyBase.Render(writer)

        End Sub

關於VerifyRenderingInServerForm 的事件，是系統自已預設的，詳情見MSDN
http://msdn.microsoft.com/zh-tw/library/system.web.ui.page.verifyrenderinginserverform.aspx

因為我們render的只是GridView控制項開始的標記(從table開始)，所以一render時，系統就發現我們要輸出的東西並沒有用<form runat=server></form>包起來，自然會出現錯誤誤息，所以我們要避開檢查的方式，就是做一個假的VerifyRenderingInServerForm 事件，事件裡就不寫東西，用重載的方式來覆蓋系統設計的檢查函式

另外dnowba有在研究在輸出時實現「同一活頁簿、多個工作表」 的方式，理論上因為是非原生的excel，這種excel專有的多sheet功能應該沒辦法用html來實現才對

◎關於這個煩人的錯誤訊息

錯誤訊息「您正嘗試開啟xxxx.xls。其檔案格式與副檔名所指定的格式不同，開啟檔案前，請確認檔案未損毀，且來自信任的來源」的原因。
經過交叉比對，如果用excle 2003開啟我們製造的xls檔案並不會出現錯誤訊息，而excel 2007就是會，我推論又是2007安全性搞的鬼，只要檔案不是原生出來的，就算檔案類型名稱怎麼改就是會出現這段警告訊息，經過實證把安全性檢驗關掉就不會出現了。怎麼關？

解決方法
1、開始>執行>regedit
2、HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security
3、新增DWORD值，維持數值為0

當然在作 B/S ，我們怎麼可能去控制Brower的電腦，這違反了瀏覽器的安全原則，除非說服他另外手動作以上步驟，或是寫個批次檔*.bat讓使用者下載使用…這種問題，我只會用這個消極的方式處理，個人雖然不大喜歡，搞不好為了一個警告視窗爆出資安問題就划不來了。