[ASP.NET]DataTable匯出Excel時凍結表頭(Frozen header)

2010-03-21

25759
0
ASP.NET
2010-08-15

[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堂課》，為培養台灣未來的領袖而努力。

回首頁