NET导出Excel文件
最近做ASP.NET Excel导出功能，顺便整理了一下可用的一些导出Excel方法
一般导出方式
1，客户提出要将统计的结果导出到excel文件，首先利用如下方式：
增加 <%@page contentType="application/vnd.ms-excel;charset=GBK" %>
头部说明，然后放一个table在该页面中即可了。
2，客户看过后提出了改进意见，要求保存文件的时候自动给文件一个名称，这样可以直接保存。
改进如下：
增加 response.setHeader("Content-Disposition", "attachment; filename="+fileName);
但是却又遇到了乱码的问题：
response.setHeader("Content-Disposition",
"attachment; filename="+new String(fileName.getBytes("GBK"),"iso-8859-1"));
将文件名称的编码由GBK转换为ISO-8859-1就正常了。
GridView导出Excel
尝试了一下Gridview导出为Excel，原本以为很简单，可是真正应用起来还是不太好弄的，呵呵，所想非所得。总结了一下应该注意下面几点：
1.由于gridview的内容可能是分页显示的，因此，这里在每次导出excel时，先将gridview的allowpaging属性设置为false，然后databind()一下，确保搂到所有数据；
2.不用单独设置导出的路径，导出时会弹出对话框让你确认保存位置；
3.要写一个空的VerifyRenderingInServerForm方法（必须写），以确认在运行时为指定的ASP.NET 服务器控件呈现HtmlForm 控件；
4.导出后别忘记再重新设置其allowpaging属性；
当我把这些都设置好以后，点击[导出]，出现了 只能在执行 Render() 的过程中调用 RegisterForEventValidation(RegisterForEventValidation can only be called during Render(); ) 的错误，又检查代码，没发现问题啊，搞了一会弄不出来，然后搜索了一下，发现了解决办法：
修改你的aspx文件中的：
<%@ Page Language="C#" EnableEventValidation = "false" AutoEventWireup="true" CodeFile="SysUser.aspx.cs" Inherits="Autho_SysUser2" %>
增加红色的部分就ok了。
下面是代码和截图：
#region 导出为Excel
public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for
}
private void ToExcel(Control ctl, string FileName)
{
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + FileName);
ctl.Page.EnableViewState = false;
System.IO.StringWriter tw = new System.IO.StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
private void toExcelClk()
{
gvSysUser.AllowPaging = false;
gvSysUser.AllowSorting = false;
gvSysUser.DataBind();
ToExcel(gvSysUser, "OFS_Data.xls");
gvSysUser.AllowPaging = true;
gvSysUser.AllowSorting = true;
gvSysUser.DataBind();
}
#endregion
最新ASP.NET导出EXCEL类
说明：可以导出ASP.NET页面和DATAGRID（WebControl）数据，可以导出表单头
using System;
using System.Data;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Diagnostics;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;
namespace bookstore
{
///
/// Excel 的摘要说明。
///
public class Excel
{
public Excel()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public void SaveToExcel(Page myPage, DataTable dt,DataGrid DG,string myExcelHeader,HtmlTable Tab,string myFileName)
{
HttpResponse resp;
resp=myPage.Response;
resp.ContentEncoding=Encoding.GetEncoding("GB18030");
resp.AppendHeader("Content-Disposition","attachment;filename="+myFileName+".xls");
resp.ContentType="application/ms-excel";
string colHeaders = "\t\t\t\t"+ myExcelHeader +"\n\n\n";
colHeaders+=tableHeader(Tab)+"\n";
StringBuilder sb=new StringBuilder();
int mycol=DG.Columns.Count;
ArrayList myAL=new ArrayList();
for(int i=0;i {
colHeaders +=DG.Columns[i].HeaderText+"\t";
myAL.Add(((System.Web.UI.WebControls.BoundColumn)(DG.Columns[i])).DataField);
}
colHeaders += "\n";
sb.Append(colHeaders);
int myrow=dt.Rows.Count;
for(int k=0;k {
foreach(string field in myAL)
{
sb.Append(dt.Rows[k][field]);
sb.Append("\t");
}
sb.Append("\n");
}
colHeaders=sb.ToString();
colHeaders=colHeaders+"\n";
resp.Write(colHeaders);
resp.End();
resp.Clear();
resp.Close();
}
/*得到表单头子*/
/*表单头子有TABLE组成，偶次项排列，TABLE在HTML中加 RUNAT=SERVER*/
public string tableHeader(HtmlTable Tab)
{
int iCols=Tab.Rows[0].Cells.Count;
int iRows=Tab.Rows.Count;
string str="";
for(int row=0;row {
for(int col=0;col {
if(col%2==1)//取偶次项的控件数据（目前只有TextBox和DropDownList，没有包含LABEL）
{
try
{
if(Tab.Rows[row].Cells[col].Controls[0].ToString()=="System.Web.UI.LiteralControl")
{
if(Tab.Rows[row].Cells[col].Controls[1].ToString()=="System.Web.UI.WebControls.TextBox")
{
str+=((System.Web.UI.WebControls.TextBox)(Tab.Rows[row].Cells[col].Controls[1])).Text+"\t";
}
if(Tab.Rows[row].Cells[col].Controls[1].ToString()=="System.Web.UI.WebControls.DropDownList")
{
str+=((System.Web.UI.WebControls.ListControl)(((System.Web.UI.WebControls.DropDownList)((Tab.Rows[row].Cells[col].Controls[1]))))).SelectedValue+"\t";
}
}
else
{
if(Tab.Rows[row].Cells[col].Controls[0].ToString()=="System.Web.UI.WebControls.TextBox")
{
str+=((System.Web.UI.WebControls.TextBox)(Tab.Rows[row].Cells[col].Controls[0])).Text+"\t";
}
if(Tab.Rows[row].Cells[col].Controls[0].ToString()=="System.Web.UI.WebControls.DropDownList")
{
str+=((System.Web.UI.WebControls.ListControl)(((System.Web.UI.WebControls.DropDownList)((Tab.Rows[row].Cells[col].Controls[0]))))).SelectedValue+"\t";
}
}
}
catch
{
str+=Tab.Rows[row].Cells[col].InnerHtml+"\t";
}
if((col+1)%iCols==0)
{
str+="\n";
}
}
else
{
str+="\t"+Tab.Rows[row].Cells[col].InnerHtml+"\t";
}
}
}
return(str);
}
}
}
ASP.NET导出Excel、World文档
对于大量的数据，有时候导出到Excel中将更加方便进行数据统计分析，而对于排版打印则导出到World文档中更加方便。在ASP.NET可以通过少量代码实现这两种导出。
新建一页面，该页面布局Html源码如下：
<form id="form1" runat="server"><div>
<asp:Button ID="cmdOpen" runat="server" Text="在线打开" CommandName="open" OnCommand="Button_Click" /> <asp:Button ID="cmdSave"
runat="server" Text="本地保存" CommandName="save" OnCommand="Button_Click" />
<asp:DropDownList ID="listType" runat="server">
<asp:ListItem Value="excel">Excel</asp:ListItem>
<asp:ListItem Value="word">Word</asp:ListItem>
</asp:DropDownList><br />
<br />
数据源：<br />
<br />
<asp:GridView ID="myGW" runat="server">
</asp:GridView>
</div></form>
这里没有什么特别的，只需要注意在线打开与本地保存两个按钮通过CommandName进行检验并触发相应的动作，DropDownList控件存放了可以导出的格式，可以自己添加如txt、html等格式。准备工作就绪以后，可以进行代码的书写，以完成我们需要的功能：
protected void Page_Load(object sender, EventArgs e)
{
FillGridView();
}
private void OutPut(string fileType, string strType)
{
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", fileType);
Response.ContentType = strType;
this.EnableViewState = false;
System.IO.StringWriter swOut = new System.IO.StringWriter();
HtmlTextWriter hTw = new HtmlTextWriter(swOut);
myGW.RenderControl(hTw);
Response.Write(swOut.ToString());
Response.End();
}
protected void Button_Click(object sender, CommandEventArgs e)
{
switch (e.CommandName)
{
case "save":
switch (listType.SelectedValue)
{
case "excel":
OutPut("attachment;filename=out.xls", "application/ms-excel");
break;
case "word":
OutPut("attachment;filename=out.doc", "application/ms-word");
break;
}
break;
case "open":
switch (listType.SelectedValue)
{
case "excel":
OutPut("online;filename=out.xls", "application/ms-excel");
break;
case "word":
OutPut("online;filename=out.doc", "application/ms-word");
break;
}
break;
}
}
public override void VerifyRenderingInServerForm(Control control)
{
}
private void FillGridView()
{
string strConn = "server=localhost\\sqlexpress;database=Northwind;user id=sa;password=sa123";
SqlConnection conn = new SqlConnection(strConn);
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
string strSql = "select * from Categories";
comm.CommandText = strSql;
conn.Open();
this.myGW.DataSource = comm.ExecuteReader();
this.myGW.DataBind();
conn.Close();
}
在以上的程序中，最主要的就是函数OutPut(string fileType, string strType)，它完成导出或在线打开的动作，这里接受两个参数其中fileType指定是保存或在线打开，并且指名了文件的名字如：attachment;filename=out.xls（作为附件保存，文件名为out.xls）、online;filename=out.xls（在线打开）；strType指定了带出文档的格式如application/ms-excel。
注意如果想要导出GridView中的数据并且要我们的程序顺利执行，则必须重写VerifyRenderingInServerForm(Control control)，否则会报错的。
如果我们想要将整个页面导出到Excel中，则不需要重写VerifyRenderingInServerForm，只需要将OutPut函数中的myGW.RenderControl(hTw)改为this.RenderControl(hTw)就可以了。
完成以上工作，在浏览器中测试，我们想要的导出功能就可以很好的工作了。
利用Office中导出的Excel.dll（DCOM方式）
这个类可以很好的控制Excel。读出内容想怎么导就怎么导
/// <summary>
/// Excel处理类
/// </summary>
public class ExcelHander
{
public ExcelHander()
{
//
// TODO: Add constructor logic here
//
}
private string AList="ABCDEFGHIJKLMNOPQRSTUVWXYZ";
public string GetAix(int x,int y)
{
char [] AChars=AList.ToCharArray();
if(x>=26){return "";}
string s="";
s=s+AChars[x-1].ToString();
s=s+y.ToString();
return s;
}
public void setValue(int x,int y,string align,string text)
{
Excel.Range range=sheet.get_Range(this.GetAix(x,y),miss);
range.set_Value(miss,text);
if(align.ToUpper()=="CENTER")
{
range.HorizontalAlignment=Excel.XlHAlign.xlHAlignCenter;
}
if(align.ToUpper()=="LEFT")
{
range.HorizontalAlignment=Excel.XlHAlign.xlHAlignLeft;
}
if(align.ToUpper()=="RIGHT")
{
range.HorizontalAlignment=Excel.XlHAlign.xlHAlignRight;
}
}
public void setValue(int x,int y,string text)
{
Excel.Range range=sheet.get_Range(this.GetAix(x,y),miss);
range.set_Value(miss,text);
}
public void setValue(int x,int y,string text,System.Drawing.Font font,System.Drawing.Color color)
{
this.setValue(x,y,text);
Excel.Range range=sheet.get_Range(this.GetAix(x,y),miss);
range.Font.Size=font.Size;
range.Font.Bold=font.Bold;
range.Font.Color=color;
range.Font.Name=font.Name;
range.Font.Italic=font.Italic;
range.Font.Underline=font.Underline;
}
public void insertRow(int y)
{
Excel.Range range=sheet.get_Range(GetAix(1,y),GetAix(25,y));
range.Copy(miss);
range.Insert(Excel.XlDirection.xlDown,miss);
range.get_Range(GetAix(1,y),GetAix(25,y));
range.Select();
sheet.Paste(miss,miss);
}
public void past()
{
string s="a,b,c,d,e,f,g";
sheet.Paste(sheet.get_Range(this.GetAix(10,10),miss),s);
}
public void setBorder(int x1,int y1,int x2,int y2,int Width)
{
Excel.Range range=sheet.get_Range(this.GetAix(x1,y1),this.GetAix(x2,y2));
range.Borders.Weight=Width;
}
public void mergeCell(int x1,int y1,int x2,int y2)
{
Excel.Range range=sheet.get_Range(this.GetAix(x1,y1),this.GetAix(x2,y2));
range.Merge(true);
}
public Excel.Range getRange(int x1,int y1,int x2,int y2)
{
Excel.Range range=sheet.get_Range(this.GetAix(x1,y1),this.GetAix(x2,y2));
return range;
}
private Missing miss=Missing.Value; //忽略的参数OLENULL
public static Missing MissValue=Missing.Value;
private Excel.Application m_objExcel;//Excel应用程序实例
private Excel.Workbooks m_objBooks;//工作表集合
private Excel.Workbook m_objBook;//当前操作的工作表
private Excel.Worksheet sheet;//当前操作的表格
public Excel.Worksheet CurrentSheet
{
get
{
return sheet;
}
set
{
this.sheet=value;
}
}
public Excel.Workbooks CurrentWorkBooks
{
get
{
return this.m_objBooks;
}
set
{
this.m_objBooks=value;
}
}
public Excel.Workbook CurrentWorkBook
{
get
{
return this.m_objBook;
}
set
{
this.m_objBook=value;
}
}
public void OpenExcelFile(string filename)
{
m_objExcel = new Excel.Application();
UserControl(false);
m_objExcel.Workbooks.Open(
filename,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss,
miss);
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = m_objExcel.ActiveWorkbook;
sheet = (Excel.Worksheet)m_objBook.ActiveSheet;
}
public void UserControl(bool usercontrol)
{
if(m_objExcel==null){return ;}
m_objExcel.UserControl=usercontrol;
m_objExcel.DisplayAlerts=usercontrol;
m_objExcel.Visible = usercontrol;
}
public void CreateExceFile()
{
m_objExcel = new Excel.Application();
UserControl(false);
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel.Workbook)(m_objBooks.Add(miss));
sheet = (Excel.Worksheet)m_objBook.ActiveSheet;
}
public void SaveAs(string FileName)
{
m_objBook.SaveAs(FileName, miss, miss, miss, miss,
miss, Excel.XlSaveAsAccessMode.xlNoChange,
Excel.XlSaveConflictResolution.xlLocalSessionChanges,
miss,miss, miss, miss);
//m_objBook.Close(false, miss, miss);
}
public void ReleaseExcel()
{
m_objExcel.Quit();
Marshal.ReleaseComObject(m_objExcel);
Marshal.ReleaseComObject(m_objBooks);
Marshal.ReleaseComObject(m_objBook);
Marshal.ReleaseComObject(sheet);
GC.Collect();
}
}
浙公网安备 33010602011771号