• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

asp.net将数据导出到excel

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

本次应用datatable导出,若用gridview(假设gridview设为了分页显示)会出现只导出当前页的情况。

    protected void btnPrn_Click(object sender, EventArgs e)
    {
        DataSet ds = new DataSet("gv");//new一个标视为 gv的库
        DataTable dt = new DataTable("logoo");
        string sCmd = "select * from mytable  ";
        MySqlDataReader reader;
        mySqlMod newMySqlMod = new mySqlMod();
        newMySqlMod.RunSQL(sCmd, out reader);
        dt.Load(reader);
        string FileName ="test.xls";//前为固定名称,后为随机名称 Guid.NewGuid().ToString() + ".xls";

//增加服务发布地址 string sNewFullFile = Server.MapPath(FileName); try { //此种方法必须在发布地址预先放置一个format.xls模板 File.Copy(Server.MapPath("format.xls"), sNewFullFile); if (File.Exists(sNewFullFile)) File.Delete(sNewFullFile); } catch (Exception er) { Response.Write(er.Message); return; } String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="+ sNewFullFile + ";" + "Extended Properties=Excel 8.0;"; System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn); OleDbCommand cmd = null; bool bRet = false; try { conn.Open(); cmd = new OleDbCommand("create table [sheet1]([ID] int,[字段一] Text,[字段二] Text,[字段三] Text)", conn); cmd.ExecuteNonQuery(); string strSQL = "INSERT INTO [Sheet1$] ([ID], [字段一],[字段二],[字段三]) VALUES (?, ?, ?, ?)"; cmd = new OleDbCommand(strSQL, conn); for (int i = 0; i < 4; i++) { cmd.Parameters.Add(i.ToString(), OleDbType.VarChar); } DataView dv = dt.DefaultView; foreach (DataRowView row in dv) { cmd.Parameters[0].Value = (int)row["id"]; cmd.Parameters[1].Value = row["col1"].ToString(); cmd.Parameters[2].Value = row["col2"].ToString(); cmd.Parameters[3].Value = row["col3"].ToString(); cmd.ExecuteNonQuery(); } bRet = true; } catch (Exception er) { Response.Write(er.Message); } finally { if (cmd != null) { cmd.Dispose(); } conn.Dispose(); } if (bRet) Response.Redirect(FileName); }

参考文章:一、C#操作Excel(创建、打开、读写、保存)几种方法的总结

下面开始就各种方法逐步说明其具体的Excel操作过程:
1.通过ADO.NET数据库连接方式 Microsoft Jet 提供程序用于连接到 Excel 工作簿。在以下连接字符串中,Extended Properties 关键字设置 Excel 特定的属性。“HDR=Yes;”指示第一行中包含列名,而不是数据,“IMEX=1;”通知驱动程序始终将“互混”数据列作为文本读取。Excel 8.0 针对Excel2000及以上版本,Excel5.0 针对Excel97。

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""

注意,Extended Properties 所需的双引号必须还要加双引号。

使用ADO.NET打开、读取并关闭代码示例如下:

using System.Data.OleDb; using System.Data;
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=c:/test.xls;" + "Extended Properties=Excel 8.0;"; OleDbConnection objConn = new OleDbConnection(sConnectionString); objConn.Open(); OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM [sheet1]", objConn); OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(); objAdapter1.SelectCommand = objCmdSelect; DataSet objDataset1 = new DataSet(); //将Excel中数据填充到数据集 objAdapter1.Fill(objDataset1, "XLData"); objConn.Close();

从上面可以看出,使用ADO.NET可将Excel当作普通数据库,使用SQL语句来操作。
通过ADO.NET获取Excel文件的各Sheet名称,可使用元数据方式:

String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +      "Data Source=c:/test.xls;" +      "Extended Properties=Excel 8.0;"; OleDbConnection cn = new OleDbConnection(sConnectionString); cn.Open(); DataTable tb = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); foreach (DataRow row in tb.Rows) {     //遍历弹出各Sheet的名称     MessageBox.Show(row["TABLE_NAME"]); }

关于使用ADO.NET创建并写入Excel文件与普通数据库操作极为类似,参见以下代码:

String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +      "Data Source=c:/test.xls;" +      "Extended Properties=Excel 8.0;"; OleDbConnection cn = new OleDbConnection(sConnectionString); string sqlCreate = "CREATE TABLE TestSheet ([ID] INTEGER,[Username] VarChar,[UserPwd] VarChar)"; OleDbCommand cmd = new OleDbCommand(sqlCreate, cn); //创建Excel文件:C:/test.xls cn.Open(); //创建TestSheet工作表 cmd.ExecuteNonQuery(); //添加数据 cmd.CommandText = "INSERT INTO TestSheet VALUES(1,'elmer','password')"; cmd.ExecuteNonQuery(); //关闭连接 cn.Close();

关于SQL语句中用到的数据类型,请查看System.Data.OleDb.OleDbType 枚举。
至此,使用ADO.NET打开、创建、读取、写入、保存并退出已全部实现,总结起来,与数据库操作基本无异,很简单。
这种方式的好处就是通用性强,将Excel中内容看作数据表,读取操作简单可靠,适合内容规范的Excel表格的数据读取。缺点是当Excel结构复杂,如含合并单元等时,无法正确读取,甚至出现不可预知的异常。

二、asp.net导出数据到Excel的三种方法

 

第一种是比较常用的方法。是利用控件的RenderControl功能,得到该控件生成的HTML,然后以Excel文件的类型输出到客户端。这种方法生成的其实是个HTML文件,只不过Excel支持HTML格式,所以使用起来似乎和真正的Excel文件没什么两样,但它终究不是Excel格式,它不能当作数据源供其他程序调用。

 

这种实现起来比较简单,就不多述,请看代码。

public  void ExportExcel( System.Web.UI.WebControls.WebControl objControl, string strFileName)
        {
             
            strFileName = System.Web.HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8);

            
           
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.Buffer = true;
            System.Web.HttpContext.Current.Response.Charset = "gb2312";
            System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "online; filename=" + strFileName + ".xls");
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
            
            System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("zh-CN", true);
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
           
            objControl.RenderControl(oHtmlTextWriter);
          
            objControl = null;
            System.Web.HttpContext.Current.Response.Write(oStringWriter.ToString().Replace("<td", "<td STYLE='MSO-NUMBER-FORMAT:\@'"));
            System.Web.HttpContext.Current.Response.Buffer = false;
            System.Web.HttpContext.Current.Response.End();
        }

可能出现的错误:

1、只能在执行 Render() 的过程中调用 RegisterForEventValidation;

解决办法:修改页面选项<%@ Page EnableEventValidation = "false"

还有一种办法是修改web.config的,但影响全局,这里就不推荐了。

2...必须放在具有 runat=server 的窗体标记内。

解决办法:

在页面中重写VerifyRenderingInServerForm方法,不对控件要求form容器进行验证

 public override void VerifyRenderingInServerForm(Control control)  {       //这里没有内容。  }
第二种方法是利用Excel的Com接口库来完成,这种方法的优点是功能强大,可像使用Excel程序一样来操作Excel文件,缺点是需要服务器安装Excel程序,且需要一定的安全配置。

一、准备工作:

1)安装Microsoft Excel

2)Com的安全设置。

   在命令行输入DCOMCNFG ,进入组件服务设置界面,如图:

打到Micorosoft Excel应用程序,如图:

右键点击属性,然后点击“安全”,将“启动和激活权限”、“访问权限”、“配置权限”都选择自定义,然后添加运行ASP.NET的用户(IIS6以上是NETWORK SERVICE以下是ASPNET),给予全部权限,如图所示:

3)配置文件目录权限,因为可能要保存文件到某目录中,所以要将该目录对ASP.NET用户予以写入权限。

 

二、使用

在VS.NET的工程中添加引用,如图:

 

如果添加引用后Excel组件不能正常使用(excel组件的左边显示一个黄色的感叹号),可以使用.Net工具进行组件转换。

转换方法,运行vs.net的命令行工具,进入Office目录,输入TlbImp Excel.exe即可,然后删除有问题的引用重新添加引用,这次使用浏览,然后引用Excel.DLL。

根据我的经验,vs2003可以直接引用Microsoft  Excel x.0 ObjectLibary,而vs2005就需要使用工具转换成DLL才可使用。

然后即可以使用Excel.Application对象来操作它了。

以下就一段简单的将GirdView的数据导出到Excel的代码,功能较简单,权当抛砖引玉了。

 

 

this.GridView1.DataBind();

     Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
   object oMissing  = System.Reflection.Missing.Value;
            
   oExcel.Workbooks.Add(oMissing);
   Excel.Workbook oBook = oExcel.Workbooks[1];
   Excel.Worksheet oSheet = (Excel.Worksheet)oBook.Sheets[1];
   oSheet.Name = this.Title;
           
   Excel.Range rg;

   for (int j = 0; j < this.GridView1.HeaderRow.Cells.Count; j++)
  {
      rg = ((Excel.Range)oSheet.Cells[ 1, j + 1]);
      rg.FormulaR1C1 = this.GridView1.HeaderRow.Cells[j].Text;
    }

    for(int i = 0;i<this.GridView1.Rows.Count;i++)
  {
      for (int j = 0; j < this.GridView1.Rows[0].Cells.Count; j++)
    {
       rg = ((Excel.Range)oSheet.Cells[i + 2, j+1]);
       rg.FormulaR1C1 = this.GridView1.Rows[i].Cells[j].Text;
      }
    }
    rg = null;

    string VirFileName = Guid.NewGuid().ToString() + ".xls";
    oBook.SaveAs(Server.MapPath(VirFileName), Excel.XlFileFormat.xlExcel9795, oMissing, oMissing, oMissing, oMissing, Excel.XlSaveAsAccessMode.xlExclusive,
    oMissing, oMissing, oMissing, oMissing, oMissing);
    oExcel.Workbooks.Close();
    oExcel.Quit();
           
    oSheet = null;
    oBook = null;
    oExcel = null;

     GC.Collect();

     Response.Redirect(VirFileName);

说明一下:为了Excel进程在使用完成后能彻底退出,需要将所有对象在使用完成后清空,像代码中的rg,oSheet,oBook,oExcel等。
第三种方法是利用OELDB数据访问对象操作Excel文件,达到将数据导出到Excel的目的,这种方法可能用的人不太多,缺点是必须有一个存在的Excel文件。优点是服务器不需要安装Excel,且生成的Excel是标准的Excel格式可以当作数据源使用。

准备工作:

1)先用准备一个Excel文件,这个Excel文件可以是空的,或是有一定格式的当做数据模板使用。

2)将操作的目录给ASP.NET用户(ASPNET 或NETWORK SERVICE)写入权限。

以下是示例:

说明一下:如果这个模板文件是个空文件,则需要使用Create Table创建一个工作表,否则只需使用已有的表进操作就可以了。

DataTable dt = new DataTable();
    dt.Columns.Add("name");
    dt.Columns.Add("age", typeof(int));
    dt.Columns.Add("phone");
    dt.Rows.Add("老张", 40,"99213812");
    dt.Rows.Add("小李", 28,"a21313");
    dt.Rows.Add("小王", 22,"2131434");


    string FileName = Guid.NewGuid().ToString()+".xls";

    string sNewFullFile = Server.MapPath(FileName);
    try
    {
        File.Copy(Server.MapPath("format.xls"), sNewFullFile);
     }
     catch (Exception er)
     {
           Response.Write(er.Message);
           return;
     }
     string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + sNewFullFile + ";Extended Properties=Excel 8.0;";
     System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
     OleDbCommand cmd = null;

     bool bRet = false;
     try
     {
        conn.Open();
        cmd  = new OleDbCommand("create table [sheet4]([姓名] Text,[年龄] int,[电话] Text)",conn);
        cmd.ExecuteNonQuery();
        string strSQL = "INSERT INTO [Sheet4$] ([姓名], [年龄],[电话]) VALUES (?, ?, ?)";
        cmd = new OleDbCommand(strSQL, conn);
       for (int i = 0; i < 3; i++)
        {
           cmd.Parameters.Add(i.ToString(), OleDbType.VarChar);
        }
        DataView dv = dt.DefaultView;
        foreach (DataRowView row in dv)
        {
             cmd.Parameters[0].Value = row["name"].ToString();
             cmd.Parameters[1].Value =(int) row["age"];
             cmd.Parameters[2].Value = row["phone"].ToString();
             cmd.ExecuteNonQuery();                     
        }
        bRet = true;
     }
     catch (Exception er)
     {
         Response.Write(er.Message);
     }
      finally
      {
        if (cmd != null)
        {
           cmd.Dispose();
        }
           conn.Dispose();
        }
        if(bRet)
          Response.Redirect(FileName);

 

 

 


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
ASP.NET中Page_Load()与Page_Init()的区别发布时间:2022-07-10
下一篇:
微软公司放出承诺的ASP.NETAJAX1.0服务器端实现源代码发布时间:2022-07-10
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap