读取 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”提供程序” 错误，则需

下载。然后安装就行了。

导出数据到 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]);

                }

            }

        }