1 protected void CreateExcel(DataTable dt, string fileName) 2 { 3 System.Text.StringBuilder strb = new System.Text.StringBuilder(); 4 strb.Append(" <html xmlns:o=\"urn:schemas-microsoft-com:office:office\""); 5 strb.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\""); 6 strb.Append("xmlns=\"http://www.w3.org/TR/REC-html40\">"); 7 strb.Append(" <head> <meta http-equiv='Content-Type' content='text/html; charset=UTF-8'>"); 8 strb.Append(" <style>"); 9 strb.Append(".xl26"); 10 strb.Append(" {mso-style-parent:style0;"); 11 strb.Append(" font-family:\"Times New Roman\", serif;"); 12 strb.Append(" mso-font-charset:0;"); 13 strb.Append(" mso-number-format:\"@\";}"); 14 strb.Append(" </style>"); 15 strb.Append(" <xml>"); 16 strb.Append(" <x:ExcelWorkbook>"); 17 strb.Append(" <x:ExcelWorksheets>"); 18 strb.Append(" <x:ExcelWorksheet>"); 19 strb.Append(" <x:Name>ResourceDetails</x:Name>"); 20 strb.Append(" <x:WorksheetOptions>"); 21 strb.Append(" <x:DefaultRowHeight>285</x:DefaultRowHeight>"); 22 strb.Append(" <x:Selected/>"); 23 strb.Append(" <x:Panes>"); 24 strb.Append(" <x:Pane>"); 25 strb.Append(" <x:Number>3</x:Number>"); 26 strb.Append(" <x:ActiveCol>1</x:ActiveCol>"); 27 strb.Append(" </x:Pane>"); 28 strb.Append(" </x:Panes>"); 29 strb.Append(" <x:ProtectContents>False</x:ProtectContents>"); 30 strb.Append(" <x:ProtectObjects>False</x:ProtectObjects>"); 31 strb.Append(" <x:ProtectScenarios>False</x:ProtectScenarios>"); 32 strb.Append(" </x:WorksheetOptions>"); 33 strb.Append(" </x:ExcelWorksheet>"); 34 strb.Append(" <x:WindowHeight>6750</x:WindowHeight>"); 35 strb.Append(" <x:WindowWidth>10620</x:WindowWidth>"); 36 strb.Append(" <x:WindowTopX>480</x:WindowTopX>"); 37 strb.Append(" <x:WindowTopY>75</x:WindowTopY>"); 38 strb.Append(" <x:ProtectStructure>False</x:ProtectStructure>"); 39 strb.Append(" <x:ProtectWindows>False</x:ProtectWindows>"); 40 strb.Append(" </x:ExcelWorkbook>"); 41 strb.Append(" </xml>"); 42 strb.Append(""); 43 strb.Append(" </head> <body> <table align=\"center\" style='border-collapse:collapse;table-layout:fixed'> <tr>"); 44 if (dt.Rows.Count > 0) 45 { 46 //写列标题 47 int columncount = dt.Columns.Count; 48 for (int columi = 0; columi < columncount; columi++) 49 { 50 strb.Append(" <td style='text-align:center;'><b>" + dt.Columns[columi] + "</b></td>"); 51 } 52 strb.Append(" </tr>"); 53 //写数据 54 for (int i = 0; i < dt.Rows.Count; i++) 55 { 56 strb.Append(" <tr>"); 57 for (int j = 0; j < dt.Columns.Count; j++) 58 { 59 if (dt.Columns[j].DataType == Type.GetType("System.Decimal")) 60 { 61 strb.Append(" <td class='xl26' style='text-align:right;'>" + dt.Rows[i][j].ToString() + "</td>"); 62 } 63 else 64 { 65 strb.Append(" <td class='xl26'>" + dt.Rows[i][j].ToString() + "</td>"); 66 } 67 } 68 strb.Append(" </tr>"); 69 } 70 } 71 strb.Append("</table> </body> </html>"); 72 Response.Clear(); 73 Response.Buffer = true; 74 Response.Charset = "UTF-8"; 75 Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xls"); 76 Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");//设置输出流 77 Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 78 this.EnableViewState = false; 79 Response.Write(strb); 80 Response.End(); 81 }
DataTable数据表导出Excel (传统方法)
转载请标明出处,谢谢.