ASP.NET Excel下载方法一览
方法一 通过GridView(简评:方法比较简单,但是只适合生成格式简单的Excel,且无法保留VBA代码),页面无刷新
aspx.cs部分
using System; using System.Collections; using System.Configuration; using System.Data; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Text; public partial class DataPage_NationDataShow : System.Web.UI.Page { private Data_Link link = new Data_Link(); private string sql; protected void Page_Load(object sender, EventArgs e) { Ajax.Utility.RegisterTypeForAjax(typeof(DataPage_NationDataShow)); } protected void btnExcel_Click(object sender, EventArgs e) { string strExcelName = "MyExcel"; strExcelName = strExcelName.Replace(@"/", ""); Data_Link link = new Data_Link(); string strSQL = this.hidParam.Value; DataSet ds = new DataSet(); ds = link.D_DataSet_Return(strSQL);//获得想要放入Excel的数据 gvExcel.Visible = true; gvExcel.DataSource = null; gvExcel.DataMember = ds.Tables[0].TableName; gvExcel.DataSource = ds.Tables[0]; gvExcel.DataBind(); ExportToExcel(this.Page, gvExcel, strExcelName); } protected void gvExcel_RowDataBound(object sender, GridViewRowEventArgs e) { } public override void VerifyRenderingInServerForm(Control control) { } /// <summary> /// 工具方法,Excel出力(解决乱码问题) /// </summary> /// <param name="page">调用页面</param> /// <param name="excel">Excel数据</param> /// <param name="fileName">文件名</param> public void ExportToExcel(System.Web.UI.Page page, GridView excel, string fileName) { try { 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.Font.Size = 10; excel.AlternatingRowStyle.BackColor = System.Drawing.Color.LightCyan; excel.RowStyle.Height = 25; page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); page.Response.Charset = "utf-8"; page.Response.ContentType = "application/vnd.ms-excel"; page.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>"); excel.Page.EnableViewState = false; excel.Visible = true; excel.HeaderStyle.Reset(); excel.AlternatingRowStyle.Reset(); System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); excel.RenderControl(oHtmlTextWriter); page.Response.Write(oStringWriter.ToString()); page.Response.End(); excel.DataSource = null; excel.Visible = false; } catch (Exception e) { } } }
aspx部分
<head runat="server"> <script type="text/javascript"> //Excel DownLoad function excelExport(){ var hidText = document.getElementById("hidParam"); hidText.value = "some params"; document.getElementById("ExcelOutput").click(); } </script> </head> <body onload="pageInit()"> <form id="form1" runat="server"> <input type="button" value="EXCEL下载" style="width:100px;" onclick="excelExport()" id="excelBut" /> <input id="hidParam" type="text" runat="server" style="display:none;"/> <asp:Button runat="server" ID="ExcelOutput" style="display:none" Text= "EXCEL出力" Width="0px" onclick="btnExcel_Click" UseSubmitBehavior="false"/> <asp:GridView ID="gvExcel" runat="server" Height="95px" OnRowDataBound="gvExcel_RowDataBound" Visible="False"></asp:GridView> </form> </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部分
<head runat="server"> <script type="text/javascript"> //Excel DownLoad function excelExport(){ var hidText = document.getElementById("hidParam"); hidText.value = "some params"; document.getElementById("ExcelOutput").click(); } </script> </head> <body onload="pageInit()"> <form id="form1" runat="server"> <input type="button" value="EXCEL下载" style="width:100px;" onclick="excelExport()" id="excelBut" NAME="excelBut"/> <input id="hidParam" type="text" runat="server" style="display:none;" NAME="hidParam"/> <asp:Button runat="server" ID="ExcelOutput" style="display:none" Text= "EXCEL出力" Width="0px" onclick="btnExcel_Click" UseSubmitBehavior="false"/> <asp:datagrid id="gvExcel" Visible="False" Runat="server" style="Z-INDEX: 107; POSITION: absolute; TOP: 72px; LEFT: 520px" Width="80px" Height="40px"></asp:datagrid> </form> </body>
方法三 以XML形式的Excel方式(可以设置丰富的样式,并可以有多个sheet,但需要模版。速度很快,但生成的文件较大,且无法保留VBA代码)
具体方法:把模版以 XML Document形式另存为A.xml。然后参照A.xml中的内容即可
private void ExcelBut_Click(object sender, System.EventArgs e) { DataSet ds = new DataSet(); string ExcelFileName = ""; DataRow dr=[------列名信息-------]; try { ExcelFileName = Path.Combine(Request.PhysicalApplicationPath + "/ExcelFile", "MyExcel.xls"); //获取Excel需要的数据 ds = [------获得数据-------]; if (ds.Tables.Count == 0) { Response.Write("<script type='text/javascript'>alert('无数据');</script>"); return; } int sheetNum = ds.Tables.Count / 2; StreamWriter writer = new StreamWriter(ExcelFileName, false); //Styles标签前面的信息相当于'头信息',不需要改变 writer.WriteLine("<?xml version=\"1.0\"?>"); writer.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>"); writer.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\""); writer.WriteLine("xmlns:o=\"urn:schemas-microsoft-com:office:office\""); writer.WriteLine("xmlns:x=\"urn:schemas-microsoft-com:office:excel\""); writer.WriteLine("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\""); writer.WriteLine("xmlns:html=\"http://www.w3.org/TR/REC-html40\">"); writer.WriteLine("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">"); writer.WriteLine("<LastAuthor>Automated Report Generator Example</LastAuthor>"); writer.WriteLine(string.Format("<Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS"))); writer.WriteLine(" <Company>51aspx.com</Company>"); writer.WriteLine(" <Version>11.6408</Version>"); writer.WriteLine("</DocumentProperties>"); writer.WriteLine("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">"); writer.WriteLine("<WindowHeight>6195</WindowHeight>"); writer.WriteLine(" <WindowWidth>18495</WindowWidth>"); writer.WriteLine(" <WindowTopX>525</WindowTopX>"); writer.WriteLine(" <WindowTopY>4260</WindowTopY>"); writer.WriteLine(" <AcceptLabelsInFormulas/>"); writer.WriteLine(" <ProtectStructure>True</ProtectStructure>"); writer.WriteLine(" <ProtectWindows>False</ProtectWindows>"); writer.WriteLine("</ExcelWorkbook>"); //通过 ss:ID 和 ss:Name相当于html中style的类 writer.WriteLine(" <Styles> "); writer.WriteLine(" <Style ss:ID='Default' ss:Name='Normal'> "); writer.WriteLine(" <Alignment ss:Vertical='Bottom'/> "); writer.WriteLine(" <Borders/> "); writer.WriteLine(" <Font ss:FontName='MS Pゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior/> "); writer.WriteLine(" <NumberFormat/> "); writer.WriteLine(" <Protection/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s23'> "); writer.WriteLine(" <Font ss:FontName='MS Pゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11' "); writer.WriteLine(" ss:Color='#FF0000'/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s24'> "); writer.WriteLine(" <Interior/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s25'> "); writer.WriteLine(" <Font ss:FontName='MS Pゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s26'> "); writer.WriteLine(" <Font ss:FontName='MS Pゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11' "); writer.WriteLine(" ss:Color='#FF0000'/> "); writer.WriteLine(" <Interior/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s27'> "); writer.WriteLine(" <Borders> "); writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" </Borders> "); writer.WriteLine(" <Font ss:FontName='MS Pゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior ss:Color='#CCFFFF' ss:Pattern='Solid'/> "); writer.WriteLine(" <Protection/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s28'> "); writer.WriteLine(" <Borders> "); writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" </Borders> "); writer.WriteLine(" <Font ss:FontName='MS Pゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior ss:Color='#CCFFFF' ss:Pattern='Solid'/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s29'> "); writer.WriteLine(" <Borders> "); writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" </Borders> "); writer.WriteLine(" <Font ss:FontName='MS Pゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior ss:Color='#CCFFFF' ss:Pattern='Solid'/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s30'> "); writer.WriteLine(" <Borders> "); writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" </Borders> "); writer.WriteLine(" <Font ss:FontName='MS Pゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior ss:Color='#CCFFFF' ss:Pattern='Solid'/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s31'> "); writer.WriteLine(" <Borders> "); writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" </Borders> "); writer.WriteLine(" <Font ss:FontName='MS Pゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior ss:Color='#CCFFFF' ss:Pattern='Solid'/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s32'> "); writer.WriteLine(" <Alignment ss:Vertical='Bottom' ss:WrapText='1'/> "); writer.WriteLine(" <Borders> "); writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" </Borders> "); writer.WriteLine(" <Font ss:FontName='MS Pゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior ss:Color='#CCFFFF' ss:Pattern='Solid'/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s33'> "); writer.WriteLine(" <Alignment ss:Vertical='Bottom' ss:WrapText='1'/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s34'> "); writer.WriteLine(" <Alignment ss:Vertical='Bottom' ss:WrapText='1'/> "); writer.WriteLine(" <Borders> "); writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" </Borders> "); writer.WriteLine(" <Font ss:FontName='MS Pゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s35'> "); writer.WriteLine(" <Borders> "); writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" </Borders> "); writer.WriteLine(" <Font ss:FontName='MS Pゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s36'> "); writer.WriteLine(" <Borders> "); writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" </Borders> "); writer.WriteLine(" <Font ss:FontName='MS Pゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior ss:Color='#FFFF99' ss:Pattern='Solid'/> "); writer.WriteLine(" <Protection ss:Protected='0'/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s37'> "); writer.WriteLine(" <Borders> "); writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" </Borders> "); writer.WriteLine(" <Font ss:FontName='MS Pゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s38'> "); writer.WriteLine(" <Borders> "); writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" </Borders> "); writer.WriteLine(" <Font ss:FontName='MS Pゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" <Style ss:ID='s39'> "); writer.WriteLine(" <Borders> "); writer.WriteLine(" <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/> "); writer.WriteLine(" </Borders> "); writer.WriteLine(" <Font ss:FontName='MS Pゴシック' x:CharSet='128' x:Family='Modern' ss:Size='11'/> "); writer.WriteLine(" <Interior/> "); writer.WriteLine(" </Style> "); writer.WriteLine(" </Styles> "); //Sheet操作 writer.WriteLine("<Worksheet ss:Name='" + dr["Msg030"] + "'>"); 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'>"); writer.WriteLine("<Column ss:StyleID='s34' ss:AutoFitWidth='0' ss:Width='110.25'/>"); writer.WriteLine("<Column ss:StyleID='s34' ss:AutoFitWidth='0' ss:Width='33.75'/>"); writer.WriteLine("<Column ss:StyleID='s34' ss:AutoFitWidth='0' ss:Width='118.5'/>"); writer.WriteLine("<Column ss:StyleID='s34' ss:AutoFitWidth='0' ss:Width='159.75'/>"); writer.WriteLine("<Column ss:StyleID='s34' ss:AutoFitWidth='0' ss:Width='147.75' ss:Span='4'/>"); for (int num = sheetNum - 1; num >= 0; num = num - 1) { // 考課表間有3行的空行 if (num != sheetNum - 1) { writer.WriteLine("<Row>"); writer.WriteLine("</Row>"); writer.WriteLine("<Row>"); writer.WriteLine("</Row>"); writer.WriteLine("<Row>"); writer.WriteLine("</Row>"); } // 列名 writer.WriteLine("<Row ss:Height='40.5'>"); writer.WriteLine("<Cell ss:StyleID='s32'><Data ss:Type='String'>" + dr["Msg002"] + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s32'><Data ss:Type='String'>" + dr["Msg003"] + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s32'><Data ss:Type='String'>" + dr["Msg004"] + "</Data></Cell>"); writer.WriteLine("</Row>"); // 详细数据 for (int i = 0; i < ds.Tables[2 * num].Rows.Count; i++) { writer.WriteLine("<Row ss:Height='27'>"); writer.WriteLine("<Cell><Data ss:Type='String'>" + ds.Tables[2 * num].Rows[i]["EvalLevel1Name"].ToString() + "</Data></Cell>"); writer.WriteLine("<Cell><Data ss:Type='Number'>" + ds.Tables[2 * num].Rows[i]["EvalLevel2Code"].ToString() + "</Data></Cell>"); writer.WriteLine("<Cell><Data ss:Type='String'>" + ds.Tables[2 * num].Rows[i]["EvalLevel2Name"].ToString() + "</Data></Cell>"); writer.WriteLine("</Row>"); } } writer.WriteLine(" </Table>"); writer.WriteLine(" </Worksheet>"); // 其他Sheet操作 for (int num = sheetNum - 1; num >= 0; num--) { writer.WriteLine("<Worksheet ss:Name='" + ds.Tables[2 * num].Rows[0][1].ToString() + "' ss:Protected='1'>"); 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'>"); writer.WriteLine(" <Row>"); writer.WriteLine("<Cell ss:StyleID='s26'><PhoneticText xmlns='urn:schemas-microsoft-com:office:excel'>カキキイロテンスウツ</PhoneticText><Data ss:Type='String'>" + dr["Msg031"] + "</Data></Cell>"); writer.WriteLine("<Cell ss:Index='14' ss:StyleID='Default'/>"); writer.WriteLine("<Cell ss:StyleID='Default'/>"); writer.WriteLine("<Cell ss:StyleID='Default'/>"); writer.WriteLine("<Cell ss:StyleID='Default'/>"); writer.WriteLine("<Cell ss:StyleID='Default'/>"); writer.WriteLine("<Cell ss:StyleID='Default'/>"); writer.WriteLine("<Cell ss:StyleID='Default'/>"); writer.WriteLine("<Cell ss:StyleID='Default'/>"); writer.WriteLine("<Cell ss:StyleID='Default'/>"); writer.WriteLine("</Row>"); writer.WriteLine(" <Row>"); writer.WriteLine("<Cell ss:StyleID='s26'><PhoneticText xmlns='urn:schemas-microsoft-com:office:excel'>カキキイロテンスウツ</PhoneticText><Data ss:Type='String'>" + dr["Msg032"] + "</Data></Cell>"); writer.WriteLine("<Cell ss:Index='14' ss:StyleID='Default'/>"); writer.WriteLine("<Cell ss:StyleID='Default'/>"); writer.WriteLine("<Cell ss:StyleID='Default'/>"); writer.WriteLine("</Row>"); // 列名 writer.WriteLine("<Row>"); writer.WriteLine("<Cell ss:StyleID='s27'><Data ss:Type='String'>" + dr["Msg001"] + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s28'><Data ss:Type='String'>" + dr["Msg002"] + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s28'><Data ss:Type='String'>" + dr["Msg003"] + "</Data></Cell>"); writer.WriteLine("</Row>"); int SheetN = ds.Tables[2 * num].Rows.Count; //详细数据 for (int i = 0; i < SheetN; i++) { writer.WriteLine("<Row>"); writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='Number'>" + ds.Tables[2 * num].Rows[i]["EvalLevel1Code"].ToString() + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'>" + ds.Tables[2 * num].Rows[i]["EvalLevel1Name"].ToString() + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='Number'>" + ds.Tables[2 * num].Rows[i]["EvalLevel2Code"].ToString() + "</Data></Cell>"); writer.WriteLine("<Cell><Data ss:Type='String'> </Data></Cell>"); writer.WriteLine("</Row>"); } writer.WriteLine("<Row>"); writer.WriteLine("</Row>"); writer.WriteLine("<Row>"); writer.WriteLine("</Row>"); //列名 writer.WriteLine("<Row>"); writer.WriteLine("<Cell ss:StyleID='s29'><Data ss:Type='String'>" + dr["Msg011"] + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s30'/>"); writer.WriteLine("<Cell ss:StyleID='s31'/>"); writer.WriteLine("<Cell ss:StyleID='s29'><Data ss:Type='String'>" + dr["Msg012"] + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s31'/>"); writer.WriteLine("<Cell ss:StyleID='s29'><Data ss:Type='String'>" + dr["Msg013"] + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s30'/>"); writer.WriteLine("<Cell ss:StyleID='s30'/>"); writer.WriteLine("<Cell ss:StyleID='s30'/>"); writer.WriteLine("<Cell ss:StyleID='s31'/>"); writer.WriteLine("<Cell ss:StyleID='s29'><Data ss:Type='String'>" + dr["Msg014"] + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s31'/>"); writer.WriteLine("<Cell ss:StyleID='s29'><Data ss:Type='String'>" + dr["Msg015"] + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s31'/>"); writer.WriteLine("</Row>"); writer.WriteLine("<Row>"); writer.WriteLine("<Cell ss:StyleID='s28'><Data ss:Type='String'>" + dr["Msg016"] + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s28'><Data ss:Type='String'>" + dr["Msg017"] + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s28'><Data ss:Type='String'>" + dr["Msg018"] + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s28'><Data ss:Type='String'>" + dr["Msg019"] + "</Data></Cell>"); writer.WriteLine("</Row>"); int DateLen = ds.Tables[2 * num + 1].Rows.Count; string EmployeeManagement = ""; for (int i = 0; i < DateLen; i++) { writer.WriteLine("<Row>"); writer.WriteLine("<Cell ss:StyleID='s37'><Data ss:Type='Number'>" + ds.Tables[2 * num + 1].Rows[i]["EmployeeManagementID"].ToString() + "</Data></Cell>"); if (!EmployeeManagement.Equals(ds.Tables[2 * num + 1].Rows[i]["EmployeeManagementID"].ToString())) { writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='Number'>" + ds.Tables[2 * num + 1].Rows[i]["EmployeeCode"].ToString() + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'>" + ds.Tables[2 * num + 1].Rows[i]["EmployeeName"].ToString() + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'>" + ds.Tables[2 * num + 1].Rows[i]["BefEmployeeName2"].ToString() + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'>" + ds.Tables[2 * num + 1].Rows[i]["EmployeeName2"].ToString() + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s38'><Data ss:Type='Number'>" + ds.Tables[2 * num + 1].Rows[i]["EvalLevel1Code"].ToString() + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'>" + ds.Tables[2 * num + 1].Rows[i]["EvalLevel1Name"].ToString() + "</Data></Cell>"); EmployeeManagement = ds.Tables[2 * num + 1].Rows[i]["EmployeeManagementID"].ToString(); } else { writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'></Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'></Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'></Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'></Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s38'><Data ss:Type='String'></Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'></Data></Cell>"); } writer.WriteLine("<Cell ss:StyleID='s39'><Data ss:Type='Number'>" + ds.Tables[2 * num + 1].Rows[i]["EvalLevel2Code"].ToString() + "</Data></Cell>"); writer.WriteLine("<Cell ss:StyleID='s35'><Data ss:Type='String'>" + ds.Tables[2 * num + 1].Rows[i]["EvalLevel2Name"].ToString() + "</Data></Cell>"); writer.WriteLine("</Row>"); } writer.WriteLine(" </Table>"); writer.WriteLine(" </Worksheet>"); } writer.WriteLine("</Workbook>"); writer.Close(); FileDownload(ExcelFileName); } catch (System.Exception ex) { } finally { Response.End(); } } public void FileDownload(FullFileName) { FileInfo DownloadFile = new FileInfo(FullFileName); Response.Clear(); Response.ClearHeaders(); Response.Buffer = true; Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(MyFile".xls"))); Response.ContentType = "application/ms-excel;charset=UTF-8";//DownloadFile.FullNameoctet-stream Response.ContentEncoding = System.Text.Encoding.GetEncoding("shift-jis"); Response.AppendHeader("Content-Length", DownloadFile.Length.ToString()); Response.WriteFile(FullFileName); Response.Flush(); if (File.Exists(FullFileName)) { File.Delete(FullFileName); } }
方法四 用微软的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));
转载出处:http://www.cnblogs.com/TiestoRay/archive/2013/02/02/2576134.html