摘要:[ASP.NET] 把 DataTable 匯出 EXCEL (DataTableToExcel / 凍結表頭 / 文字靠左右 / 背景色)
今天<天秤>颱風天高雄應當放假一天
但是身為電子公司根本沒再休假
一切以公司公佈為主
颱風天也沒啥人想來
<鹹鹹>沒事
剛好參考gipi大大的文章http://www.dotblogs.com.tw/jimmyyu/archive/2010/03/21/asp-net-export-excel-frozen-header.aspx
小弟參考一下...自行修改自己所需
public void ExportDataTableToExcel(DataTable s_DataTable)
{
int tRowCount = s_DataTable.Rows.Count;
int tColumnCount = s_DataTable.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";
//設定檔名可為中文_#1
Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode("Y2J中文檔名", System.Text.Encoding.UTF8) + ".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>");
//設定此Worksheet名稱_#2
Response.Write("\n <x:Name>Y2J測試</x:Name>");
//以下針對此工作表進行屬性設定
Response.Write("\n <x:WorksheetOptions>");
Response.Write("\n <x:FrozenNoSplit/>");
//設定凍結行號_#3
Response.Write("\n <x:SplitHorizontal>1</x:SplitHorizontal>");
//設定起始行號(TopRowBottomPane)_#4
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>");
//塞入head
for (int i = 0; i < tColumnCount; i++)
{
//設定head的背景色_#5
Response.Write("\n <TD bgcolor = #fff8dc>");
Response.Write(s_DataTable.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++)
{
if (j % 2 == 0)
{
//設定單號資料靠左且背景為綠色_#6
Response.Write("\n <TD align=\"left\" x:num bgcolor = ADFF2F>");
Response.Write(s_DataTable.Rows[j][k].ToString());
Response.Write("\n </TD>");
}
else
{
//設定雙號資料靠右_#7
Response.Write("\n <TD align=\"right\" x:num>");
Response.Write(s_DataTable.Rows[j][k].ToString());
Response.Write("\n </TD>");
}
}
Response.Write("\n </TR>");
}
Response.Write("</Table>");
Response.Write("</body>");
Response.Write("</html>");
Response.End();
}
Y2J's Life:http://kimenyeh.blogspot.tw/