6 Grid导出Ecxel (1) 直接导出Excel Response.Clear(); Response.Buffer = true; Response.Charset = "GB2312"; Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls"); // 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码！！！ Response.ContentEncoding = System.Text.Encoding.UTF7; Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); GridID.RenderControl(oHtmlTextWriter); Response.Output.Write(oStringWriter.ToString()); Response.Flush(); Response.End(); 这个方法简单,适合只导出数据,不需要特殊格式. 在.CS文件里要加上: public override void VerifyRenderingInServerForm(Control control) { } 在页面代码中的第一行加上:EnableEventValidation="false" 如: <%@ Page Language="C#" MasterPageFile="AccountMaster.master" AutoEventWireup="true" CodeFile="SchoolGapStatistic.aspx.cs" Inherits="AccountStatistics_SchoolGapStatistic" Title="Untitled Page" EnableEventValidation="false" %> (2) 用模板导出Excel 先用Excel画出要导出的样式,然后填充数据. 代码: using System.IO; 在类的方法前面加上: Excel.ApplicationClass myApp; Excel.Workbook myBook; Excel.Worksheet mySheet; 导出方法: protected void ToExcel() { string strStyleFile = Server.MapPath("xx\\aa.xls");//样式文件 if (!File.Exists(strStyleFile)) Response.Write("<script language='javascript'>alert('样式不存在！')</script>"); string strDateFile = Server.MapPath("yy\\aa.xls"); //保存报表文件 //try //{ if (File.Exists(strDateFile)) File.Delete(strDateFile); File.Copy(strStyleFile, strDateFile); #region 写文件 myApp = null; myBook = null; mySheet = null; object oMissiong = System.Reflection.Missing.Value; myApp = new Excel.ApplicationClass(); myApp.Visible = false; myApp.Workbooks.Open(strDateFile, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong); myBook = myApp.Workbooks[1]; //mySheet = (Excel.Worksheet)myBook.ActiveSheet; myBook.Worksheets.Add(oMissiong, oMissiong, 1, oMissiong); mySheet = (Excel.Worksheet)myBook.Sheets[1]; mySheet.Name = "验收单明细"; #endregion #region 读数据写单元格 mySheet.Cells[2, 6] = DateTime.Now.ToString("yyyy-MM-dd");//制表日期 mySheet.Cells[2, 3] =lblTJDW.Text ;//制表单位 int i=0; long kcsl = 0; for (i = 0; i < GridId.Rows.Count; i++) { mySheet.Cells[4 + i, 1] = i+1;//序号 if (GridId.Rows[i].Cells[0].Text.Trim() == " ") { mySheet.Cells[4 + i, 2] = ""; } else { mySheet.Cells[4 + i, 2] = GridId.Rows[i].Cells[0].Text.Trim(); } if (GridId.Rows[i].Cells[1].Text.Trim() == " ") { mySheet.Cells[4 + i,3] = ""; } else { mySheet.Cells[4 + i, 3] = GridId.Rows[i].Cells[1].Text.Trim(); } if (GridId.Rows[i].Cells[2].Text.Trim() == " ") { mySheet.Cells[4 + i, 4] = ""; } else { mySheet.Cells[4 + i, 4] = GridId.Rows[i].Cells[2].Text.Trim(); } if (GridId.Rows[i].Cells[3].Text.Trim() == " ") { mySheet.Cells[4 + i, 5] = ""; } else { mySheet.Cells[4 + i, 5] = GridId.Rows[i].Cells[3].Text.Trim(); } if (GridId.Rows[i].Cells[4].Text.Trim() == " ") { mySheet.Cells[4 + i, 6] = ""; } else { mySheet.Cells[4 + i, 6] = GridId.Rows[i].Cells[4].Text.Trim(); } if (GridId.Rows[i].Cells[5].Text.Trim() == " ") { mySheet.Cells[4 + i, 7] = ""; } else { mySheet.Cells[4 + i, 7] = GridId.Rows[i].Cells[5].Text.Trim(); } } mySheet.Cells[4 + i, 1] = "合计"; mySheet.get_Range(mySheet.Cells[4 + i, 1], mySheet.Cells[4 + i, 5]).Merge(false); mySheet.Cells[4 + i, 6] = lblQKSL.Text; mySheet.Cells[4 + i, 7] = lblQKJE.Text; #endregion myBook.Save(); myBook.Close(true, strDateFile, true); System.Runtime.InteropServices.Marshal.ReleaseComObject(mySheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(myBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(myApp); GC.Collect(); FileInfo file = new System.IO.FileInfo(strDateFile); Response.Clear(); Response.Buffer = true; Response.ContentEncoding = System.Text.Encoding.UTF8; // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name)); // 添加头信息，指定文件大小，让浏览器能够显示下载进度 Response.AddHeader("Content-Length", file.Length.ToString()); // 指定返回的是一个不能被客户端读取的流，必须被下载 Response.ContentType = "application/ms-excel"; // 把文件流发送到客户端 Response.WriteFile(file.FullName); // 停止页面的执行 Response.End(); } 存在问题:第二个方法对于一些特殊字符,如:×,会出现乱码,方法一不会. 7 把GridView数据源转换成DataTable DataTable dt=((DataTable)gvJF.DataSource); 8,代码设置EXCEL格式: mySheet.Cells[1, 1] = txtBT1.Value; mySheet.get_Range(mySheet.Cells[1, 1], mySheet.Cells[1, 6]).Merge(false); mySheet.get_Range(mySheet.Cells[1, 1], mySheet.Cells[1, 6]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin; mySheet.get_Range(mySheet.Cells[1, 1], mySheet.Cells[1, 6]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin; mySheet.get_Range(mySheet.Cells[1, 1], mySheet.Cells[1, 6]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin; mySheet.get_Range(mySheet.Cells[1, 1], mySheet.Cells[1, 6]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin; mySheet.get_Range(mySheet.Cells[1, 1], mySheet.Cells[1, 6]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; mySheet.get_Range(mySheet.Cells[1, 1], mySheet.Cells[1, 6]).Font.Bold = true;
评论