vs.net2010 操作 Excel2003 与 Excel2007
读取 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();
}
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]);
}
}
}
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]);
}
}
}