這篇文章主要介紹了asp.net讀取excel中的數(shù)據(jù)并綁定在gridview上的方法,需要的朋友可以參考下

前臺label，DropDownList，gridview控件

aspx.cs核心代碼：

代碼如下:

using System.Data.OleDb;//需要引入命名

public void Excel_Click(object sender, EventArgs e)

{

if (this.AttachmentFile.Value == "" && this.Label1.Text == "" && DropDownList2.SelectedValue == "")

{

Response.Write("<script>window.alert('請選擇要導(dǎo)入的文件')</script>");

}

if (this.AttachmentFile.Value != "" && this.DropDownList2.SelectedValue == "")

{

HttpFileCollection files = HttpContext.Current.Request.Files;

HttpPostedFile postedFile = files[0];

fileName = System.IO.Path.GetFileName(postedFile.FileName);

if (fileName != "")

{

postedFile.SaveAs("\\localhost\文件夾\" + fileName);

}

string strConn;

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "\\localhost\文件夾\" + fileName + ";Extended Properties=Excel 8.0;";//this.AttachmentFile.Value.ToString()

OleDbConnection conn = new OleDbConnection(strConn);

conn.Open();

DataTable sheetNames = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

foreach (DataRow dr in sheetNames.Rows)

{

DropDownList2.Items.Add(dr[2].ToString());

}

this.Label1.Text = "\\localhost\文件夾\" + fileName;//this.AttachmentFile.Value.ToString();

conn.Close();

}

if (this.Label1.Text.ToString() != "" && this.DropDownList2.SelectedValue != "")// && this.DropDownList1.SelectedValue.ToString() != "全部"

{

//綁定到gridview

GridView1.DataSource = createDataSource(DropDownList2.SelectedValue.ToString(), this.Label1.Text.ToString());//, this.DropDownList1.SelectedValue.ToString()

GridView1.DataBind();

}

}

//以Excel為數(shù)據(jù)源獲取數(shù)據(jù)集

private DataSet createDataSource(string select, string lable)

{

string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + lable + ";Extended Properties=Excel 8.0;";

string strsql = "select 登記號碼,姓名,日期,簽到時間,簽退時間,部門 from [" + select + "] order by 部門,日期,姓名";//excel表格的字段

OleDbConnection conn = new OleDbConnection(strCon);

OleDbDataAdapter da = new OleDbDataAdapter(strsql, conn);

try

{

conn.Open();

DataSet ds = new DataSet();

da.Fill(ds);

conn.Close();

return ds;

}

catch (Exception e)

{

Response.Write("<script>window.alert('沒有數(shù)據(jù),或者" + e.Message + "')</script>");

return null;

}

}

以上是插入07以前版本excel

如果07版本以后只需要做小小修改

代碼如下:

string strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + lable + ";Extended Properties=Excel 12.0;";

更多信息請查看IT技術(shù)專欄