Asp.net將DataGrid匯出到Excel裡的多個worksheet工作表內

2009-09-02

28348
0
.Net隨手記
2009-09-11

將一個DataGrid匯到一個Excel內很簡單,但要如何將多個DataGrid滙到同一個Excel內,並分別放在不同的Worksheet工作表內.

Asp.net匯出一個DataGrid到Excel的方式很簡單,只要執行以下幾行就可以達成,

Response.Clear();

Response.Buffer = true;

Response.Charset = "";

Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");

Response.ContentEncoding = Encoding.GetEncoding( "big5" );

Response.ContentType = "application/vnd.ms-excel";

this.EnableViewState = false;

StringWriter sw=new StringWriter();

HtmlTextWriter htw=new HtmlTextWriter(sw);

DataGrid1.RenderControl(htw);

Response.Write( sw.ToString());

Response.End();

但如果有多個DataGrid,希望能匯到同一個Excel檔,但要分別放到不同的Worksheet工作表內,這樣的方式就無法達成,需要做點加工才可以,就最近的實驗結果,有兩個方法可以達成,一個是可以透過DataSet將它匯出,另一種就是直接用DataGrid.RenderControl去做,這次先以DataGrid.RenderControl的方式來說.

這次所用到的是MIME的方式,匯出來的Excel格式如下:

MIME-Version: 1.0
X-Document-Type: Workbook
Content-Type: multipart/related; boundary="-=BOUNDARY_EXCEL"

---=BOUNDARY_EXCEL
Content-Type: text/html; charset="big5"

<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel">

<head>
<xml>
<o:DocumentProperties>
<o:Author>Jeff_Yeh</o:Author>
<o:LastAuthor>Jeff_Yeh</o:LastAuthor>
<o:Created>2009/9/2 下午 09:00:48</o:Created>
<o:LastSaved>2009/9/2 下午 09:00:48</o:LastSaved>
<o:Company>Jeff隨手記</o:Company>
<o:Version>11.5606</o:Version>
</o:DocumentProperties>
</xml>
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>TestA</x:Name>
<x:WorksheetSource HRef="cid:9d6e5d0b-f362-497b-9ef6-a0e36ae8c4c5"/>
</x:ExcelWorksheet>
<x:ExcelWorksheet>
<x:Name>TestB</x:Name>
<x:WorksheetSource HRef="cid:94c5e9ba-4bec-4bb6-9fe6-8e0c160b6c1b"/>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
</x:ExcelWorkbook>
</xml>
</head>
</html>

---=BOUNDARY_EXCEL
Content-ID: 9d6e5d0b-f362-497b-9ef6-a0e36ae8c4c5
Content-Type: text/html; charset="big5"

<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel">

<head>

只要把上列複制另存為xls檔,就可以開的起來,在裡面就可以看到兩個worksheet,但這樣密密麻麻的東西,還真是看到眼花,而冃Content-ID還必需對應到Worksheep的 HRef的cid,不然Excel就會無法開啟,所以花了一點時間寫了一個Class來呼叫,就不用管那麼多東西.

001

public class ExcelUtil
002

{
003

string _Author;
004

string _Company;
005

006 StringBuilder sbBody=new StringBuilder();
007

StringBuilder sbSheet=new StringBuilder();
008

public ExcelUtil(string Author,string Company)
009

{
010

_Author=Author;
011

_Company=Company;
012

sbBody.AppendFormat(
013

"MIME-Version: 1.0\r\n"+
014

"X-Document-Type: Workbook\r\n"+
015

"Content-Type: multipart/related; boundary=\"-=BOUNDARY_EXCEL\"\r\n\r\n"+
016

"---=BOUNDARY_EXCEL\r\n"+
017

"Content-Type: text/html; charset=\"big5\"\r\n\r\n"+
018

"<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n"+
019

"xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n\r\n"+
020

"<head>\r\n"+
021

"<xml>\r\n"+
022

"<o:DocumentProperties>\r\n"+
023

"<o:Author>{0}</o:Author>\r\n"+
024

"<o:LastAuthor>{0}</o:LastAuthor>\r\n"+
025

"<o:Created>{1}</o:Created>\r\n"+
026

"<o:LastSaved>{1}</o:LastSaved>\r\n"+
027

"<o:Company>{2}</o:Company>\r\n"+
028

"<o:Version>11.5606</o:Version>\r\n"+
029

"</o:DocumentProperties>\r\n"+
030

"</xml>\r\n"+
031

"<xml>\r\n"+
032

"<x:ExcelWorkbook>\r\n"+
033

"<x:ExcelWorksheets>\r\n"
034

,_Author
035

,DateTime.Now.ToString()
036

,_Company);
037

}
038

039 private string ExportExcel()
040

{
041

StringBuilder sb=new StringBuilder(sbBody.ToString());
042

043

sb.Append("</x:ExcelWorksheets>\r\n"+
044

"</x:ExcelWorkbook>\r\n"+
045

"</xml>\r\n"+
046

"</head>\r\n"+
047

"</html>\r\n\r\n");
048

049

sb.Append(sbSheet.ToString());
050

051 sb.Append("---=BOUNDARY_EXCEL--");
052

053

return sb.ToString();
054

}
055

056 public void AddGrid(DataGrid grid,string sheetName)
057

{
058

string gid=Guid.NewGuid().ToString();
059

sbBody.AppendFormat("<x:ExcelWorksheet>\r\n"+
060

"<x:Name>{0}</x:Name>\r\n"+
061

"<x:WorksheetSource HRef=\"cid:{1}\"/>\r\n"+
062

"</x:ExcelWorksheet>\r\n"
063

,sheetName.Replace(":","").Replace("\\","").Replace("/","").Replace("?","").Replace("*","").Replace("[","").Replace("]","").Trim()
064

,gid);
065

066

StringWriter sw=new StringWriter();
067

HtmlTextWriter htw=new HtmlTextWriter(sw);
068

grid.RenderControl(htw);
069

070 sbSheet.AppendFormat("---=BOUNDARY_EXCEL\r\n"+
071

"Content-ID: {0}\r\n"+
072

"Content-Type: text/html; charset=\"big5\"\r\n\r\n"+
073

"<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n"+
074

"xmlns:x=\"urn:schemas-microsoft-com:office:excel\">\r\n\r\n"+
075

"<head>\r\n"+
076

"<xml>\r\n"+
077

"<x:WorksheetOptions>\r\n"+
078

"<x:ProtectContents>False</x:ProtectContents>\r\n"+
079

"<x:ProtectObjects>False</x:ProtectObjects>\r\n"+
080

"<x:ProtectScenarios>False</x:ProtectScenarios>\r\n"+
081

"</x:WorksheetOptions>\r\n"+
082

"</xml>\r\n"+
083

"</head>\r\n"+
084

"<body>\r\n"
085

,gid);
086

sbSheet.Append(sw.ToString());
087

sbSheet.Append("</body>\r\n"+
088

"</html>\r\n\r\n");
089

sw.Close();
090

htw.Close();
091

}
092

093 public void Export(Page page,string FileName)
094

{
095

page.Response.Clear();
096

page.Response.Buffer = true;
097

page.Response.Charset = "";
098

page.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.xls",FileName));
099

page.Response.ContentEncoding = Encoding.GetEncoding( "big5" );
100

page.Response.ContentType = "application/vnd.ms-excel";
101

page.Response.Write(ExportExcel());
102

page.Response.End();
103

}
104

105 public void Clear()
106

{
107

sbBody.Remove(0,sbBody.Length);
108

sbSheet.Remove(0,sbBody.Length);
109

}
110

}

看起來程式碼也不多,但就可以少寫很多Code,比如來說,我要將DataGrid1跟DataGrid2分別滙到不同的worksheet裡去,我只要寫以下的Code就好.

*第63行看我Replace了幾個符號 : \ / ? * [ ] , 因為這幾個符號是保留字,如果使用到,worksheet工作表的名稱就會變為復原_Sheet1,這是Excel本身的限制.我沒判斷長度及空字串~~

ExcelUtil eu=new ExcelUtil("Jeff_Yeh","Jeff隨手記");
2

eu.AddGrid(DataGrid1,"TestA");
3

eu.AddGrid(DataGrid2,"TestB");
4

eu.Export(this,"Jeff");

第一行就是使用上面Sample Class,ExceUtil,傳入兩個參數,第一個是作業,第二個是公司行號,這樣在看Excel摘要資訊時,就可以看到.

第二,三行就是把DataGrid傳給ExcelUtil去處理,第一個參數是DataGrid,第二個參數是Worksheet工作表的名稱.

第四行就是匯出Excel檔,第一個參數是Page,第二個參數是匯出的檔案名稱.

所以每加一個DataGrid,就呼叫一次AddGrid就好,其它的都不用管.

參考資訊 :

MIME Reference

如何在串流 MIME 內容時格式化 Excel 活頁簿

用MIME格式描述多内容Excel工作表

如何將不同的GridView匯出到不同的EXCEL sheet中

原始碼下載 :

WebTest2003.rar

回首頁