ASP.NET Excel下载方法一览
方法一 通过GridView(简评：方法比较简单，但是只适合生成格式简单的Excel，且无法保留VBA代码),页面无刷新
aspx.cs部分
1 using System; 2 using System.Collections; 3 using System.Configuration; 4 using System.Data; 5 using System.Web; 6 using System.Web.Security; 7 using System.Web.UI; 8 using System.Web.UI.HtmlControls; 9 using System.Web.UI.WebControls; 10 using System.Web.UI.WebControls.WebParts; 11 using System.Text; 12 13 public partial class DataPage_NationDataShow : System.Web.UI.Page 14 { 15 private Data_Link link = new Data_Link(); 16 private string sql; 17 18 protected void Page_Load(object sender, EventArgs e) 19 { 20 Ajax.Utility.RegisterTypeForAjax(typeof(DataPage_NationDataShow)); 21 } 22 23 protected void btnExcel_Click(object sender, EventArgs e) 24 { 25 string strExcelName = "MyExcel"; 26 strExcelName = strExcelName.Replace(@"/", ""); 27 28 Data_Link link = new Data_Link(); 29 string strSQL = this.hidParam.Value; 30 DataSet ds = new DataSet(); 31 ds = link.D_DataSet_Return(strSQL);//获得想要放入Excel的数据 32 33 gvExcel.Visible = true; 34 gvExcel.DataSource = null; 35 gvExcel.DataMember = ds.Tables[0].TableName; 36 gvExcel.DataSource = ds.Tables[0]; 37 gvExcel.DataBind(); 38 40 ExportToExcel(this.Page, gvExcel, strExcelName); 41 } 42 43 protected void gvExcel_RowDataBound(object sender, GridViewRowEventArgs e) 44 { } 45 public override void VerifyRenderingInServerForm(Control control) 46 { } 47 48 /// <summary> 49 /// 工具方法，Excel出力(解决乱码问题) 50 /// </summary> 51 /// <param name="page">调用页面</param> 52 /// <param name="excel">Excel数据</param> 53 /// <param name="fileName">文件名</param> 54 public void ExportToExcel(System.Web.UI.Page page, GridView excel, string fileName) 55 { 56 try 57 { 58 foreach (GridViewRow row in excel.Rows) 59 { 60 for (int i = 0; i < row.Cells.Count; i++) 61 { 62 excel.HeaderRow.Cells[i].BackColor = System.Drawing.Color.Yellow; 63 } 64 } 65 excel.Font.Size = 10; 66 excel.AlternatingRowStyle.BackColor = System.Drawing.Color.LightCyan; 67 excel.RowStyle.Height = 25; 68 69 page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); 70 page.Response.Charset = "utf-8"; 71 page.Response.ContentType = "application/vnd.ms-excel"; 72 page.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>"); 73 excel.Page.EnableViewState = false; 74 excel.Visible = true; 75 excel.HeaderStyle.Reset(); 76 excel.AlternatingRowStyle.Reset(); 77 78 System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); 79 System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); 80 excel.RenderControl(oHtmlTextWriter); 81 page.Response.Write(oStringWriter.ToString()); 82 page.Response.End(); 83 84 excel.DataSource = null; 85 excel.Visible = false; 86 } 87 catch (Exception e) 88 { 89 90 } 91 } 92 }
aspx部分
1 <head runat="server"> 2 <script type="text/javascript"> 3 //Excel DownLoad 4 function excelExport(){ 5 var hidText = document.getElementById("hidParam"); 6 hidText.value = "some params"; 7 document.getElementById("ExcelOutput").click(); 8 } 9 </script> 10 </head> 11 <body onload="pageInit()"> 12 <form id="form1" runat="server"> 13 <input type="button" value="EXCEL下载" style="width:100px;" onclick="excelExport()" id="excelBut" /> 14 <input id="hidParam" type="text" runat="server" style="display:none;"/> 15 <asp:Button runat="server" ID="ExcelOutput" style="display:none" Text= "EXCEL出力" Width="0px" onclick="btnExcel_Click" UseSubmitBehavior="false"/> 16 <asp:GridView ID="gvExcel" runat="server" Height="95px" OnRowDataBound="gvExcel_RowDataBound" Visible="False"></asp:GridView> 17 </form> 18 </body>
在刚才的aspx.cs代码中
foreach (GridViewRow row in excel.Rows) { for (int i = 0; i < row.Cells.Count; i++) { excel.HeaderRow.Cells[i].BackColor = System.Drawing.Color.Yellow; } }
这部分是给表头添加样式。
有时候为了便于浏览，需要给交叉行添加样式，简单点的可以用下面这种：
excel.AlternatingRowStyle.BackColor = System.Drawing.Color.LightCyan;
但是细看一下会发现它把一整行的样式都改变了，包括后面那些没有用到的列。
解决办法是有，不过比较繁琐，就是修改每个单元格的样式。
int rowCount = excel.Rows.Count; int colCount = excel.HeaderRow.Cells.Count; for (int i = 0; i < rowCount; i++) { for(int j=0;j<colCount; j++) { excel.Rows[i].Cells[j].BackColor = System.Drawing.Color.LightCyan; } }
方法二 通过DataGrid(与方法一基本相同),页面无刷新
aspx.cs部分
public override void VerifyRenderingInServerForm(Control control) {} /// <summary> /// エクセル出力イベント /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void ExcelBut_Click(object sender, System.EventArgs e) { DataGrid dgExcel = new DataGrid(); try { DataSet ds = getExcelData(this.PageParams.Value);//出力データを取得する if(ds.Tables[0].Rows.Count>0) { //エクセルへデータを投入する string execlName= "MyExcel"; Encoding encodingType=System.Text.Encoding.UTF8; dgExcel.DataMember=ds.Tables[0].TableName; dgExcel.DataSource=ds.Tables[0]; Response.Buffer = true; Response.Charset = "utf-8"; Response.AppendHeader("Content-Disposition", "attachment;filename=" + execlName+ ".xls"); Response.ContentEncoding = encodingType; Response.ContentType = "application/ms-excel"; StringWriter oStringWriter = new StringWriter(); HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter); dgExcel.DataBind(); dgExcel.Visible = true; dgExcel.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.Flush(); Response.Close(); dgExcel.DataSource = null; dgExcel.Visible = false; } else { Response.Write("<script>alert('xxxxxx')</script>"); } } catch(Exception ex) { Response.Write("<script>alert('oooooo')</script>"); } }
aspx部分
1 <head runat="server"> 2 <script type="text/javascript"> 3 //Excel DownLoad 4 function excelExport(){ 5 var hidText = document.getElementById("hidParam"); 6 hidText.value = "some params"; 7 document.getElementById("ExcelOutput").click(); 8 } 9 </script> 10 </head> 11 <body onload="pageInit()"> 12 <form id="form1" runat="server"> 13 <input type="button" value="EXCEL下载" style="width:100px;" onclick="excelExport()" id="excelBut" NAME="excelBut"/> 14 <input id="hidParam" type="text" runat="server" style="display:none;" NAME="hidParam"/> 15 <asp:Button runat="server" ID="ExcelOutput" style="display:none" Text= "EXCEL出力" Width="0px" onclick="btnExcel_Click" UseSubmitBehavior="false"/> 16 <asp:datagrid id="gvExcel" Visible="False" Runat="server" style="Z-INDEX: 107; POSITION: absolute; TOP: 72px; LEFT: 520px" 17 Width="80px" Height="40px"></asp:datagrid> 18 </form> 19 </body>
方法三 以XML形式的Excel方式(可以设置丰富的样式，并可以有多个sheet，但需要模版。速度很快，但生成的文件较大，且无法保留VBA代码)
具体方法：把模版以 XML Document形式另存为A.xml。然后参照A.xml中的内容即可
View Code
1 private void ExcelBut_Click(object sender, System.EventArgs e) 2 { 3 DataSet ds = new DataSet(); 4 string ExcelFileName = ""; 5 DataRow dr=[------列名信息-------]; 6 try 7 { 8 ExcelFileName = Path.Combine(Request.PhysicalApplicationPath + "/ExcelFile", "MyExcel.xls"); 9 10 //获取Excel需要的数据 11 ds = [------获得数据-------]; 12 if (ds.Tables.Count == 0) 13 { 14 Response.Write("<script type='text/javascript'>alert('无数据');</script>"); 15 return; 16 } 17 int sheetNum = ds.Tables.Count / 2; 18 StreamWriter writer = new StreamWriter(ExcelFileName, false); 19 20 //Styles标签前面的信息相当于'头信息'，不需要改变 21 writer.WriteLine("<?xml version=\"1.0\"?>"); 22 writer.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>"); 23 writer.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\""); 24 writer.WriteLine("xmlns:o=\"urn:schemas-microsoft-com:office:office\""); 25 writer.WriteLine("xmlns:x=\"urn:schemas-microsoft-com:office:excel\""); 26 writer.WriteLine("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\""); 27 writer.WriteLine("xmlns:html=\"http://www.w3.org/TR/REC-html40\">"); 28 writer.WriteLine("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">"); 29 writer.WriteLine("<LastAuthor>Automated Report Generator Example</LastAuthor>"); 30 writer.WriteLine(string.Format("<Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS"))); 31 writer.WriteLine(" <Company>51aspx.com</Company>"); 32 writer.WriteLine(" <Version>11.6408</Version>"); 33 writer.WriteLine("</DocumentProperties>"); 34 writer.WriteLine("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">"); 35 writer.WriteLine("<WindowHeight>6195</WindowHeight>"); 36 writer.WriteLine(" <WindowWidth>18495</WindowWidth>"); 37 writer.WriteLine(" <WindowTopX>525</WindowTopX>"); 38 writer.WriteLine(" <WindowTopY>4260</WindowTopY>"); 39 writer.WriteLine(" <AcceptLabelsInFormulas/>"); 40 writer.WriteLine(" <ProtectStructure>True</ProtectStructure>"); 41 writer.WriteLine(" <ProtectWindows>False</ProtectWindows>"); 42 writer.WriteLine("</ExcelWorkbook>"); 43 44 //通过 ss:ID 和 ss:Name相当于html中style的类 45 writer.WriteLine(" <Styles> "); 46 writer.WriteLine(" <Style ss:ID='Default' ss:Name='Normal'> "); 47 writer.WriteLine(" <Alignment ss:Vertical='Bottom'/> "); 48 writer.WriteLine(" <Borders/> "); 49 writer.WriteLine(" <Font ss:FontName='ＭＳ Ｐゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 50 writer.WriteLine(" <Interior/> "); 51 writer.WriteLine(" <NumberFormat/> "); 52 writer.WriteLine(" <Protection/> "); 53 writer.WriteLine(" </Style> "); 54 writer.WriteLine(" <Style ss:ID='s23'> "); 55 writer.WriteLine(" <Font ss:FontName='ＭＳ Ｐゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11' "); 56 writer.WriteLine(" ss:Color='#FF0000'/> "); 57 writer.WriteLine(" </Style> "); 58 writer.WriteLine(" <Style ss:ID='s24'> "); 59 writer.WriteLine(" <Interior/> "); 60 writer.WriteLine(" </Style> "); 61 writer.WriteLine(" <Style ss:ID='s25'> "); 62 writer.WriteLine(" <Font ss:FontName='ＭＳ Ｐゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 63 writer.WriteLine(" <Interior/> "); 64 writer.WriteLine(" </Style> "); 65 writer.WriteLine(" <Style ss:ID='s26'> "); 66 writer.WriteLine(" <Font ss:FontName='ＭＳ Ｐゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11' "); 67 writer.WriteLine(" ss:Color='#FF0000'/> "); 68 writer.WriteLine(" <Interior/> "); 69 writer.WriteLine(" </Style> "); 70 writer.WriteLine(" <Style ss:ID='s27'> "); 71 writer.WriteLine(" <Borders> "); 72 writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); 73 writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); 74 writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); 75 writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); 76 writer.WriteLine(" </Borders> "); 77 writer.WriteLine(" <Font ss:FontName='ＭＳ Ｐゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 78 writer.WriteLine(" <Interior ss:Color='#CCFFFF' ss:Pattern='Solid'/> "); 79 writer.WriteLine(" <Protection/> "); 80 writer.WriteLine(" </Style> "); 81 writer.WriteLine(" <Style ss:ID='s28'> "); 82 writer.WriteLine(" <Borders> "); 83 writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); 84 writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); 85 writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); 86 writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); 87 writer.WriteLine(" </Borders> "); 88 writer.WriteLine(" <Font ss:FontName='ＭＳ Ｐゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 89 writer.WriteLine(" <Interior ss:Color='#CCFFFF' ss:Pattern='Solid'/> "); 90 writer.WriteLine(" </Style> "); 91 writer.WriteLine(" <Style ss:ID='s29'> "); 92 writer.WriteLine(" <Borders> "); 93 writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); 94 writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); 95 writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); 96 writer.WriteLine(" </Borders> "); 97 writer.WriteLine(" <Font ss:FontName='ＭＳ Ｐゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 98 writer.WriteLine(" <Interior ss:Color='#CCFFFF' ss:Pattern='Solid'/> "); 99 writer.WriteLine(" </Style> "); 100 writer.WriteLine(" <Style ss:ID='s30'> "); 101 writer.WriteLine(" <Borders> "); 102 writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); 103 writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); 104 writer.WriteLine(" </Borders> "); 105 writer.WriteLine(" <Font ss:FontName='ＭＳ Ｐゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 106 writer.WriteLine(" <Interior ss:Color='#CCFFFF' ss:Pattern='Solid'/> "); 107 writer.WriteLine(" </Style> "); 108 writer.WriteLine(" <Style ss:ID='s31'> "); 109 writer.WriteLine(" <Borders> "); 110 writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); 111 writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); 112 writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); 113 writer.WriteLine(" </Borders> "); 114 writer.WriteLine(" <Font ss:FontName='ＭＳ Ｐゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 115 writer.WriteLine(" <Interior ss:Color='#CCFFFF' ss:Pattern='Solid'/> "); 116 writer.WriteLine(" </Style> "); 117 writer.WriteLine(" <Style ss:ID='s32'> "); 118 writer.WriteLine(" <Alignment ss:Vertical='Bottom' ss:WrapText='1'/> "); 119 writer.WriteLine(" <Borders> "); 120 writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); 121 writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); 122 writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); 123 writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); 124 writer.WriteLine(" </Borders> "); 125 writer.WriteLine(" <Font ss:FontName='ＭＳ Ｐゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 126 writer.WriteLine(" <Interior ss:Color='#CCFFFF' ss:Pattern='Solid'/> "); 127 writer.WriteLine(" </Style> "); 128 writer.WriteLine(" <Style ss:ID='s33'> "); 129 writer.WriteLine(" <Alignment ss:Vertical='Bottom' ss:WrapText='1'/> "); 130 writer.WriteLine(" </Style> "); 131 writer.WriteLine(" <Style ss:ID='s34'> "); 132 writer.WriteLine(" <Alignment ss:Vertical='Bottom' ss:WrapText='1'/> "); 133 writer.WriteLine(" <Borders> "); 134 writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); 135 writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); 136 writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); 137 writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); 138 writer.WriteLine(" </Borders> "); 139 writer.WriteLine(" <Font ss:FontName='ＭＳ Ｐゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 140 writer.WriteLine(" <Interior/> "); 141 writer.WriteLine(" </Style> "); 142 writer.WriteLine(" <Style ss:ID='s35'> "); 143 writer.WriteLine(" <Borders> "); 144 writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); 145 writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); 146 writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); 147 writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); 148 writer.WriteLine(" </Borders> "); 149 writer.WriteLine(" <Font ss:FontName='ＭＳ Ｐゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 150 writer.WriteLine(" <Interior/> "); 151 writer.WriteLine(" </Style> "); 152 writer.WriteLine(" <Style ss:ID='s36'> "); 153 writer.WriteLine(" <Borders> "); 154 writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); 155 writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); 156 writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); 157 writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); 158 writer.WriteLine(" </Borders> "); 159 writer.WriteLine(" <Font ss:FontName='ＭＳ Ｐゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 160 writer.WriteLine(" <Interior ss:Color='#FFFF99' ss:Pattern='Solid'/> "); 161 writer.WriteLine(" <Protection ss:Protected='0'/> "); 162 writer.WriteLine(" </Style> "); 163 writer.WriteLine(" <Style ss:ID='s37'> "); 164 writer.WriteLine(" <Borders> "); 165 writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); 166 writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); 167 writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); 168 writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); 169 writer.WriteLine(" </Borders> "); 170 writer.WriteLine(" <Font ss:FontName='ＭＳ Ｐゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 171 writer.WriteLine(" <Interior/> "); 172 writer.WriteLine(" </Style> "); 173 writer.WriteLine(" <Style ss:ID='s38'> "); 174 writer.WriteLine(" <Borders> "); 175 writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); 176 writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); 177 writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); 178 writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); 179 writer.WriteLine(" </Borders> "); 180 writer.WriteLine(" <Font ss:FontName='ＭＳ Ｐゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 181 writer.WriteLine(" <Interior/> "); 182 writer.WriteLine(" </Style> "); 183 writer.WriteLine(" <Style ss:ID='s39'> "); 184 writer.WriteLine(" <Borders> "); 185 writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); 186 writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); 187 writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); 188 writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); 189 writer.WriteLine(" </Borders> "); 190 writer.WriteLine(" <Font ss:FontName='ＭＳ Ｐゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); 191 writer.WriteLine(" <Interior/> "); 192 writer.WriteLine(" </Style> "); 193 writer.WriteLine(" </Styles> "); 194 195 //Sheet操作 196 writer.WriteLine("<Worksheet ss:Name='" + dr["Msg030"] + "'>"); 197 writer.WriteLine(" <Table ss:ExpandedColumnCount='9' ss:ExpandedRowCount='3000' x:FullColumns='1' x:FullRows='1' ss:StyleID='s33' ss:DefaultColumnWidth='54' ss:DefaultRowHeight='13.5'>"); 198 writer.WriteLine("<Column ss:StyleID='s34' ss:AutoFitWidth='0' ss:Width='110.25'/>"); 199 writer.WriteLine("<Column ss:StyleID='s34' ss:AutoFitWidth='0' ss:Width='33.75'/>"); 200 writer.WriteLine("<Column ss:StyleID='s34' ss:AutoFitWidth='0' ss:Width='118.5'/>"); 201 writer.WriteLine("<Column ss:StyleID='s34' ss:AutoFitWidth='0' ss:Width='159.75'/>"); 202 writer.WriteLine("<Column ss:StyleID='s34' ss:AutoFitWidth='0' ss:Width='147.75' ss:Span='4'/>"); 203 204 for (int num = sheetNum - 1; num >= 0; num = num - 1) 205 { 206 // 考課表間有3行的空行 207 if (num != sheetNum - 1) 208 { 209 writer.WriteLine("<Row>"); 210 writer.WriteLine("</Row>"); 211 writer.WriteLine("<Row>"); 212 writer.WriteLine("</Row>"); 213 writer.WriteLine("<Row>"); 214 writer.WriteLine("</Row>"); 215 } 216 217 // 列名 218 writer.WriteLine("<Row ss:Height='40.5'>"); 219 writer.WriteLine("<Cell ss:StyleID='s32'><Data ss:Type='String'>" + dr["Msg002"] + "</Data></Cell>"); 220 writer.WriteLine("<Cell ss:StyleID='s32'><Data ss:Type='String'>" + dr["Msg003"] + "</Data></Cell>"); 221 writer.WriteLine("<Cell ss:StyleID='s32'><Data ss:Type='String'>" + dr["Msg004"] + "</Data></Cell>"); 222 writer.WriteLine("</Row>"); 223 224 // 详细数据 225 for (int i = 0; i < ds.Tables[2 * num].Rows.Count; i++) 226 { 227 writer.WriteLine("<Row ss:Height='27'>"); 228 writer.WriteLine("<Cell><Data ss:Type='String'>" + ds.Tables[2 * num].Rows[i]["EvalLevel1Name"].ToString() + "</Data></Cell>"); 229 writer.WriteLine("<Cell><Data ss:Type='Number'>" + ds.Tables[2 * num].Rows[i]["EvalLevel2Code"].ToString() + "</Data></Cell>"); 230 writer.WriteLine("<Cell><Data ss:Type='String'>" + ds.Tables[2 * num].Rows[i]["EvalLevel2Name"].ToString() + "</Data></Cell>"); 231 writer.WriteLine("</Row>"); 232 } 233 } 234 writer.WriteLine(" </Table>"); 235 writer.WriteLine(" </Worksheet>"); 236 237 // 其他Sheet操作 238 for (int num = sheetNum - 1; num >= 0; num--) 239 { 240 writer.WriteLine("<Worksheet ss:Name='" + ds.Tables[2 * num].Rows[0][1].ToString() + "' ss:Protected='1'>"); 241 writer.WriteLine(" <Table ss:ExpandedColumnCount='31' ss:ExpandedRowCount='30000' x:FullColumns='1' x:FullRows='1' ss:StyleID='s25' ss:DefaultColumnWidth='54' ss:DefaultRowHeight='13.5'>"); 242 writer.WriteLine(" <Row>"); 243 writer.WriteLine("<Cell ss:StyleID='s26'><PhoneticText xmlns='urn:schemas-microsoft-com:office:excel'>カキキイロテンスウツ</PhoneticText><Data ss:Type='String'>" + dr["Msg031"] + "</Data></Cell>"); 244 writer.WriteLine("<Cell ss:Index='14' ss:StyleID='Default'/>"); 245 writer.WriteLine("<Cell ss:StyleID='Default'/>"); 246 writer.WriteLine("<Cell ss:StyleID='Default'/>"); 247 writer.WriteLine("<Cell ss:StyleID='Default'/>"); 248 writer.WriteLine("<Cell ss:StyleID='Default'/>"); 249 writer.WriteLine("<Cell ss:StyleID='Default'/>"); 250 writer.WriteLine("<Cell ss:StyleID='Default'/>"); 251 writer.WriteLine("<Cell ss:StyleID='Default'/>"); 252 writer.WriteLine("<Cell ss:StyleID='Default'/>"); 253 writer.WriteLine("</Row>"); 254 writer.WriteLine(" <Row>"); 255 writer.WriteLine("<Cell ss:StyleID='s26'><PhoneticText xmlns='urn:schemas-microsoft-com:office:excel'>カキキイロテンスウツ</PhoneticText><Data ss:Type='String'>" + dr["Msg032"] + "</Data></Cell>"); 256 writer.WriteLine("<Cell ss:Index='14' ss:StyleID='Default'/>"); 257 writer.WriteLine("<Cell ss:StyleID='Default'/>"); 258 writer.WriteLine("<Cell ss:StyleID='Default'/>"); 259 260 writer.WriteLine("</Row>"); 261 262 // 列名 263 writer.WriteLine("<Row>"); 264 writer.WriteLine("<Cell ss:StyleID='s27'><Data ss:Type='String'>" + dr["Msg001"] + "</Data></Cell>"); 265 writer.WriteLine("<Cell ss:StyleID='s28'><Data ss:Type='String'>" + dr["Msg002"] + "</Data></Cell>"); 266 writer.WriteLine("<Cell ss:StyleID='s28'><Data ss:Type='String'>" + dr["Msg003"] + "</Data></Cell>"); 267 writer.WriteLine("</Row>"); 268 int SheetN = ds.Tables[2 * num].Rows.Count; 269 270 //详细数据 271 for (int i = 0; i < SheetN; i++) 272 { 273 writer.WriteLine("<Row>"); 274 writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='Number'>" + ds.Tables[2 * num].Rows[i]["EvalLevel1Code"].ToString() + "</Data></Cell>"); 275 writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'>" + ds.Tables[2 * num].Rows[i]["EvalLevel1Name"].ToString() + "</Data></Cell>"); 276 writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='Number'>" + ds.Tables[2 * num].Rows[i]["EvalLevel2Code"].ToString() + "</Data></Cell>"); 277 writer.WriteLine("<Cell><Data ss:Type='String'> </Data></Cell>"); 278 writer.WriteLine("</Row>"); 279 } 280 writer.WriteLine("<Row>"); 281 writer.WriteLine("</Row>"); 282 writer.WriteLine("<Row>"); 283 writer.WriteLine("</Row>"); 284 285 //列名 286 writer.WriteLine("<Row>"); 287 writer.WriteLine("<Cell ss:StyleID='s29'><Data ss:Type='String'>" + dr["Msg011"] + "</Data></Cell>"); 288 writer.WriteLine("<Cell ss:StyleID='s30'/>"); 289 writer.WriteLine("<Cell ss:StyleID='s31'/>"); 290 writer.WriteLine("<Cell ss:StyleID='s29'><Data ss:Type='String'>" + dr["Msg012"] + "</Data></Cell>"); 291 writer.WriteLine("<Cell ss:StyleID='s31'/>"); 292 writer.WriteLine("<Cell ss:StyleID='s29'><Data ss:Type='String'>" + dr["Msg013"] + "</Data></Cell>"); 293 writer.WriteLine("<Cell ss:StyleID='s30'/>"); 294 writer.WriteLine("<Cell ss:StyleID='s30'/>"); 295 writer.WriteLine("<Cell ss:StyleID='s30'/>"); 296 writer.WriteLine("<Cell ss:StyleID='s31'/>"); 297 writer.WriteLine("<Cell ss:StyleID='s29'><Data ss:Type='String'>" + dr["Msg014"] + "</Data></Cell>"); 298 writer.WriteLine("<Cell ss:StyleID='s31'/>"); 299 writer.WriteLine("<Cell ss:StyleID='s29'><Data ss:Type='String'>" + dr["Msg015"] + "</Data></Cell>"); 300 writer.WriteLine("<Cell ss:StyleID='s31'/>"); 301 writer.WriteLine("</Row>"); 302 writer.WriteLine("<Row>"); 303 writer.WriteLine("<Cell ss:StyleID='s28'><Data ss:Type='String'>" + dr["Msg016"] + "</Data></Cell>"); 304 writer.WriteLine("<Cell ss:StyleID='s28'><Data ss:Type='String'>" + dr["Msg017"] + "</Data></Cell>"); 305 writer.WriteLine("<Cell ss:StyleID='s28'><Data ss:Type='String'>" + dr["Msg018"] + "</Data></Cell>"); 306 writer.WriteLine("<Cell ss:StyleID='s28'><Data ss:Type='String'>" + dr["Msg019"] + "</Data></Cell>"); 307 308 writer.WriteLine("</Row>"); 309 int DateLen = ds.Tables[2 * num + 1].Rows.Count; 310 string EmployeeManagement = ""; 311 312 for (int i = 0; i < DateLen; i++) 313 { 314 writer.WriteLine("<Row>"); 315 writer.WriteLine("<Cell ss:StyleID='s37'><Data ss:Type='Number'>" + ds.Tables[2 * num + 1].Rows[i]["EmployeeManagementID"].ToString() + "</Data></Cell>"); 316 if (!EmployeeManagement.Equals(ds.Tables[2 * num + 1].Rows[i]["EmployeeManagementID"].ToString())) 317 { 318 writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='Number'>" + ds.Tables[2 * num + 1].Rows[i]["EmployeeCode"].ToString() + "</Data></Cell>"); 319 writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'>" + ds.Tables[2 * num + 1].Rows[i]["EmployeeName"].ToString() + "</Data></Cell>"); 320 writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'>" + ds.Tables[2 * num + 1].Rows[i]["BefEmployeeName2"].ToString() + "</Data></Cell>"); 321 writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'>" + ds.Tables[2 * num + 1].Rows[i]["EmployeeName2"].ToString() + "</Data></Cell>"); 322 writer.WriteLine("<Cell ss:StyleID='s38'><Data ss:Type='Number'>" + ds.Tables[2 * num + 1].Rows[i]["EvalLevel1Code"].ToString() + "</Data></Cell>"); 323 writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'>" + ds.Tables[2 * num + 1].Rows[i]["EvalLevel1Name"].ToString() + "</Data></Cell>"); 324 EmployeeManagement = ds.Tables[2 * num + 1].Rows[i]["EmployeeManagementID"].ToString(); 325 } 326 else 327 { 328 writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'></Data></Cell>"); 329 writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'></Data></Cell>"); 330 writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'></Data></Cell>"); 331 writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'></Data></Cell>"); 332 writer.WriteLine("<Cell ss:StyleID='s38'><Data ss:Type='String'></Data></Cell>"); 333 writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'></Data></Cell>"); 334 } 335 writer.WriteLine("<Cell ss:StyleID='s39'><Data ss:Type='Number'>" + ds.Tables[2 * num + 1].Rows[i]["EvalLevel2Code"].ToString() + "</Data></Cell>"); 336 writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'>" + ds.Tables[2 * num + 1].Rows[i]["EvalLevel2Name"].ToString() + "</Data></Cell>"); 337 writer.WriteLine("</Row>"); 338 } 339 writer.WriteLine(" </Table>"); 340 writer.WriteLine(" </Worksheet>"); 341 } 342 343 writer.WriteLine("</Workbook>"); 344 writer.Close(); 345 346 FileDownload(ExcelFileName); 347 } 348 catch (System.Exception ex) 349 { 350 351 } 352 finally 353 { 354 Response.End(); 355 } 356 } 357 public void FileDownload(FullFileName) 358 { 359 FileInfo DownloadFile = new FileInfo(FullFileName); 360 Response.Clear(); 361 Response.ClearHeaders(); 362 Response.Buffer = true; 363 Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(MyFile".xls"))); 364 Response.ContentType = "application/ms-excel;charset=UTF-8";//DownloadFile.FullNameoctet-stream 365 Response.ContentEncoding = System.Text.Encoding.GetEncoding("shift-jis"); 366 Response.AppendHeader("Content-Length", DownloadFile.Length.ToString()); 367 Response.WriteFile(FullFileName); 368 Response.Flush(); 369 370 if (File.Exists(FullFileName)) 371 { 372 File.Delete(FullFileName); 373 } 374 }
方法四 用微软的COM组件操作Excel。虽然可以很方便的操作单元格，并且能保留VBA代码，它相当于直接打开一个EXCEL进程。
当初这个COM组件式给WinForm准备的，但在Web端有很多东西的支持不尽人意，例如无法用组件中的方法关闭Excel对象，
只能通过强制关闭Excel进程的方式(这其中还有很多问题，例如只能关闭当前打开的这个Excel的进程，否则会把其他用户的Excel进程关闭)。在这里不推荐
方法五 用流的方式，把内容以HTML的格式向Excel中输出数据 好处是 可以生成格式丰富复杂的Excel，页面无刷新
aspx部分
<asp:Button ID="hidExport" onClick="hidExport_Click()" Runat="server"></asp:Button>
aspx.cs部分
//内容很好理解，只需当成Table来拼字符串即可 private string getExcelContent() { StringBuilder sb = new StringBuilder(); sb.Append("<table borderColor='black' border='1' >"); sb.Append("<thead><tr><th colSpan='2' bgColor='#ccfefe'>标题</th></tr>"); sb.Append("<tr><th bgColor='#ccfefe'>号码</th><th bgColor='#ccfefe'>名字</th></tr></thead>"); sb.Append("<tbody>"); sb.Append("<tr class='firstTR'><td bgcolor='#FF99CC'></td><td></td></tr>"); sb.Append("<tr class='secondTR'><td></td><td bgcolor='lightskyblue'></td></tr>"); sb.Append("</tbody></table>"); return sb.ToString(); } private void hidExport_Click(object sender, System.EventArgs e) { string content = getExcelContent(); string css = ".firstTR td{color:blue;width:100px;}.secondTR td{color:blue;width:100px;}"; string filename = "Test.xls"; CommonTool.ExportToExcel(filename, content ,css); }
工具类CommonTool
public class CommonTool { /// <summary> /// 以流的形式，可以设置很丰富复杂的样式 /// </summary> /// <param name="content">Excel中内容(Table格式)</param> /// <param name="filename">文件名</param> /// <param name="cssText">样式内容</param> public static void ExportToExcel(string filename, string content,string cssText) { var res = HttpContext.Current.Response; content = String.Format("<style type='text/css'>{0}</style>{1}",cssText,content); res.Clear(); res.Buffer = true; res.Charset = "UTF-8"; res.AddHeader("Content-Disposition", "attachment; filename=" + filename); res.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8"); res.ContentType = "application/ms-excel;charset=UTF-8"; res.Write(content); res.Flush(); res.End(); } }
这种方法比较灵活，而且可以通过选择器来添加样式，相当不错。缺点就是需要将数据转换成字符串。
上面的方式是，通过WebForm控件调用后台方法，在后台方法中从数据源获取并整合成要导出的内容，然后输出；
对这种方式稍加升级，就可以处理导出页面内容到Excel。大体思路：
- 在前端页面通过js获取要导出到Excel的html内容
- 将内容传输到后端
- 后端接收数据（例如ashx、Controller、aspx.cs）
- 后端对数据进行处理，并输出到前端页面（还是使用上面的工具类方法ExportToExcel）
麻烦的地方在第2步，要面对的问题有两个：
- 因为这里需要将数据传到后端，同时还要下载文件，所以使用ajax是不行了，只能使用form post表单数据
- 上传数据、下载文件的时候不能刷新页面
由这两点出发，解决方式如下：
var excelInfo = document.getElementById("excelArea").innerHTML;//获取要导出到Excel的内容 var actionTarget = "/ExcelExportController/Export"; //接收Excel数据的后端接口 var excelStr = ''/ +'<div'/ +' <form id="excelForm" action="' + actionTarget + '" method="post" enctype="application/x-www-form-urlencoded" target="exportFormFrame">'/ +' <input name="excelInfo" type="hidden" value="' + excelInfo + '" />'/ +' </form>'/ +' <iframe name="exportFormFrame" id="exportFormFrame" style="width: 0px;height: 0px;display:none;"></iframe>'/ +'</div>'; $(document.body).append(excelStr); $('#excelForm').submit();
注：直接向Excel输出html有几个注意点：
- 单元格内换行 <br style='mso-data-placement:same-cell;'/>
- 设置表格的边框样式 borderColor='black' border='1'
- 设置行高列宽要使用pt，px无效（也许有其他原因，我测试的是这样）
- excel空余部分边框的问题。用此方法输出的excel，空余部分边框都不显示，如果要做成想普通excel的样子，需在输出流的head部分加上excel的设置
<!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name></x:Name><x:WorksheetOptions>
<x:Selected/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
附 ：
1.动态的让单元格中的内容换行
例 原内容是 博客园是面向开发者的知识分享社区，不允许发布任何推广、广告、政治方面的内容
目标状态是
博客园是面向开发者的知识分享社区
不允许发布任何推广、广告、政治方面的内容
则只需将程序中要填入该单元格的内容改成如下格式即可【即在换行处加 &CHAR(10)& 即可】
="博客园是面向开发者的知识分享社区"&CHAR(10)&"不允许发布任何推广、广告、政治方面的内容"
2.通过IE生成文件的时候，经常会出现文件名乱码的情况，只需要如下方式对文件名编码即可
string excelName="数据导出.xls"; excelName = HttpUtility.UrlEncode(Encoding.UTF8.GetBytes(excelName));
