[ASP.NET]DataTable匯出Excel時凍結表頭(Frozen header)
好久以前寫過一篇DataTable匯出Excel的文章:[ASP.NET] 簡易自訂Excel匯出格式
最近看到論壇上有人問到這個匯出的功能能否在匯出時直接將header進行凍結(Frozen),上網查了一下,看到這個:
Microsoft Office XML formats,然後找到一篇別人的討論文章:
How to freeze the header row in an Excel spreadsheet exported from ASP.NET
好久以前寫過一篇DataTable匯出Excel的文章:[ASP.NET] 簡易自訂Excel匯出格式
最近看到論壇上有人問到這個匯出的功能能否在匯出時直接將header進行凍結(Frozen),上網查了一下,看到這個:
Microsoft Office XML formats,然後找到一篇別人的討論文章:
How to freeze the header row in an Excel spreadsheet exported from ASP.NET
我就簡單的修改了我前一篇文章的內容,Code的部分重點如下:
//引用這三個xmlns
Response.Write("<html xmlns:o='urn:schemas-microsoft-com:office:office'");
Response.Write("xmlns:x='urn:schemas-microsoft-com:office:excel'");
Response.Write("xmlns='http://www.w3.org/TR/REC-html40'>");
Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");
//在head中加入xml定義
Response.Write("\n <head>");
Response.Write("\n <xml>");
Response.Write("\n <x:ExcelWorkbook>");
Response.Write("\n <x:ExcelWorksheets>");
Response.Write("\n <x:ExcelWorksheet>");
Response.Write("\n <x:Name>Test</x:Name>");
//以下針對此工作表進行屬性設定
Response.Write("\n <x:WorksheetOptions>");
Response.Write("\n <x:FrozenNoSplit/>");
//設定凍結行號
Response.Write("\n <x:SplitHorizontal>1</x:SplitHorizontal>");
//設定卷軸起始行號
Response.Write("\n <x:TopRowBottomPane>1</x:TopRowBottomPane>");
//設定捲軸捲動
Response.Write("\n <x:ActivePane>2</x:ActivePane>");
Response.Write("\n </x:WorksheetOptions>");
Response.Write("\n </xml>");
Response.Write("\n </head>");
引用office的XML,在Header中加入ExcelWorkbook的屬性設定,最重要的重點在WorksheetOption的Tag,不過這邊我還沒找到對於Office XML每個Element的詳細說明,如果有人找到的話可以提供一下。
下面針對重點屬性<x:SplitHorizontal>與<x:TopRowBottomPane>做個示意:
//設定凍結行號
Response.Write("\n <x:SplitHorizontal>1</x:SplitHorizontal>");
//設定卷軸起始行號
Response.Write("\n <x:TopRowBottomPane>1</x:TopRowBottomPane>");
起始畫面如下,游標在第二筆上:
捲動後,凍結了第一行:
將兩個屬性都調整為2:
//設定凍結行號
Response.Write("\n <x:SplitHorizontal>2</x:SplitHorizontal>");
//設定卷軸起始行號
Response.Write("\n <x:TopRowBottomPane>2</x:TopRowBottomPane>");
起始畫面,游標一開始在第三筆上:
捲動後,凍結了前兩行:
Office XML Formats的細節我這邊沒有講得很清楚,有興趣的人可以上官網看看:新副檔名與 Office XML Formats 簡介
而修改完後完整的Code如下:
public void ExportDataTableToExcel(DataTable pDataTable)
{
int tRowCount = pDataTable.Rows.Count;
int tColumnCount = pDataTable.Columns.Count;
Response.Expires = 0;
Response.Clear();
Response.Buffer = true;
Response.Charset = "utf-8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=Excel.xls");
//引用這三個xmlns
Response.Write("<html xmlns:o='urn:schemas-microsoft-com:office:office'");
Response.Write("xmlns:x='urn:schemas-microsoft-com:office:excel'");
Response.Write("xmlns='http://www.w3.org/TR/REC-html40'>");
Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");
//在head中加入xml定義
Response.Write("\n <head>");
Response.Write("\n <xml>");
Response.Write("\n <x:ExcelWorkbook>");
Response.Write("\n <x:ExcelWorksheets>");
Response.Write("\n <x:ExcelWorksheet>");
Response.Write("\n <x:Name>Test</x:Name>");
//以下針對此工作表進行屬性設定
Response.Write("\n <x:WorksheetOptions>");
Response.Write("\n <x:FrozenNoSplit/>");
//設定凍結行號
Response.Write("\n <x:SplitHorizontal>2</x:SplitHorizontal>");
//設定卷軸起始行號
Response.Write("\n <x:TopRowBottomPane>2</x:TopRowBottomPane>");
Response.Write("\n <x:ActivePane>2</x:ActivePane>");
Response.Write("\n </x:WorksheetOptions>");
Response.Write("\n </xml>");
Response.Write("\n </head>");
Response.Write("\n <body>");
Response.Write("<Table borderColor=black border=1>");
Response.Write("\n <TR>");
for (int i = 0; i < tColumnCount; i++)
{
Response.Write("\n <TD bgcolor = #fff8dc>");
Response.Write(pDataTable.Columns[i].ColumnName);
Response.Write("\n </TD>");
}
Response.Write("\n </TR>");
for (int j = 0; j < tRowCount; j++)
{
Response.Write("\n <TR>");
for (int k = 0; k < tColumnCount; k++)
{
Response.Write("\n <TD align=\"right\" x:num>");
Response.Write(pDataTable.Rows[j][k].ToString());
Response.Write("\n </TD>");
}
Response.Write("\n </TR>");
}
Response.Write("</Table>");
Response.Write("</body>");
Response.Write("</html>");
Response.End();
}
游舒帆 (gipi) 探索原力Co-founder,曾任TutorABC協理與鼎新電腦總監,並曾獲選兩屆微軟最有價值專家 ( MVP ),離開職場後創辦探索原力,致力於協助青少年培養面對未來的能力。認為教育與組織育才其實息息相關,都是在為未來儲備能量,2018年起成立為期一年的專題課程《職涯躍升的關鍵24堂課》,為培養台灣未來的領袖而努力。 |