vs.net2010 操作 Excel2003 与 Excel2007

最新推荐文章于 2019-03-29 00:39:13 发布

转载最新推荐文章于 2019-03-29 00:39:13 发布 · 61 阅读

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/tiger8000/archive/2011/10/08/2201743.html

本文介绍如何使用C#代码读取Excel文件并显示在GridView控件中，以及如何将数据导出为Excel文件。重点包括使用不同连接字符串以支持不同版本的Excel文件，并解决在操作Excel2007文件时可能出现的错误。同时提供了导出数据到Excel的具体步骤，支持两种Excel版本格式。

读取 Excel 数据

using System.Data.OleDb;
using System.Data;

public void ReadExcelFiless()
        {
             // string strConn  = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + Server.MapPath("ExcelFiles/MyExcelFile.xls") + ";Extended Properties=Excel 8.0"; // 此连接只能操作Excel2007 前的.xls 文件
             // string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + Server.MapPath("ExcelFiles/MyExcelFile.xls") + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'"; // 此连接只能操作Excel2007之前(.xls)文件
             string strConn = " Provider=Microsoft.Ace.OleDb.12.0; " + " data source= " + Server.MapPath( " ExcelFiles/Mydata2007.xlsx ") + " ;Extended Properties='Excel 12.0; HDR=NO; IMEX=1' "; // 此连接可以操作.xls与.xlsx文件

            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter adp = new OleDbDataAdapter( " Select * from [Sheet1$] ", conn);
            DataSet ds = new DataSet();
            adp.Fill(ds, " Book1 ");
             this.GridView1.DataSource = ds.Tables[ " Book1 "].DefaultView;
             this.GridView1.DataBind();
        }

操作Excel 2007 文件时，如出现

“未在本地计算机上注册“Microsoft.Ace.OleDb.12.0”提供程序” 错误，则需

http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe 下载。然后安装就行了。

导出数据到 Excel

using System.IO;

protected void Button1_Click( object sender, EventArgs e)
        {
            DisableControls(GridView1);

            Response.ClearContent();

            Response.AddHeader( " content-disposition ", " attachment; filename=MyExcelFile2011.xls "); // 导出Excel200， Excel2003格式
             // Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile2011.xlsx"); // 导出 Excel2007格式

            Response.ContentType = " application/excel ";

            StringWriter sw = new StringWriter();

            HtmlTextWriter htw = new HtmlTextWriter(sw);

             this.GridView1.RenderControl(htw);

            Response.Write(sw.ToString());

            Response.End();
        }
         public override void VerifyRenderingInServerForm(Control control)
        {

        }
         private void DisableControls(Control gv)
        {

            LinkButton lb = new LinkButton();

            Literal l = new Literal();

             string name = String.Empty;

             for ( int i = 0; i < gv.Controls.Count; i++)
            {

                 if (gv.Controls[i].GetType() == typeof(LinkButton))
                {

                    l.Text = (gv.Controls[i] as LinkButton).Text;

                    gv.Controls.Remove(gv.Controls[i]);

                    gv.Controls.AddAt(i, l);

                }

                 else if (gv.Controls[i].GetType() == typeof(DropDownList))
                {

                    l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;

                    gv.Controls.Remove(gv.Controls[i]);

                    gv.Controls.AddAt(i, l);

                }

                 if (gv.Controls[i].HasControls())
                {

                    DisableControls(gv.Controls[i]);

                }

            }

        }

转载于:https://www.cnblogs.com/tiger8000/archive/2011/10/08/2201743.html