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"
如:
(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;
评论