下面的代码是网上找来的,目的是把Excel中数据导入到数据库,但是有一个问题,就是Excel的第一行总是不行读取到,int count = ds.Tables["[Sheet1$]"].Rows.Count;总是比实际少一行,大家看下是怎么回事,谢谢!
代码如下:
string filename = this.img.PostedFile.FileName;
string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="+filename + ";Extended Properties=Excel 8.0";
OleDbConnection thisconnection = new OleDbConnection(conn);
thisconnection.Open();
string Sql = "select * from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
System.Data.DataSet ds = new System.Data.DataSet();
mycommand.Fill(ds, "[Sheet1$]");
thisconnection.Close(); string conn1 = "User ID=sa;Data Source=127.0.0.1;Password=;Initial Catalog=Lecture;Provider=SQLOLEDB.1;";
OleDbConnection thisconnection1 = new OleDbConnection(conn1);
thisconnection1.Open();
int count = ds.Tables["[Sheet1$]"].Rows.Count;
for (int i = 0; i < count; i++)
{
string id, id_1, id_2, id_3;
id = ds.Tables["[Sheet1$]"].Rows[i][0].ToString();
id_1 = ds.Tables["[Sheet1$]"].Rows[i][1].ToString();
id_2 = ds.Tables["[Sheet1$]"].Rows[i][2].ToString();
id_3 = ds.Tables["[Sheet1$]"].Rows[i][3].ToString();
string excelsql = "insert into Stu(SNo, SName, SSex, SClass, SPwd) values ('" + id + "','" + id_1 + "','" + id_2 + "','" + id_3 + "', '123456') ";
OleDbCommand mycommand1 = new OleDbCommand(excelsql, thisconnection1);
mycommand1.ExecuteNonQuery();
}
Response.Write("更新成功");
thisconnection1.Close();
代码如下:
string filename = this.img.PostedFile.FileName;
string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="+filename + ";Extended Properties=Excel 8.0";
OleDbConnection thisconnection = new OleDbConnection(conn);
thisconnection.Open();
string Sql = "select * from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
System.Data.DataSet ds = new System.Data.DataSet();
mycommand.Fill(ds, "[Sheet1$]");
thisconnection.Close(); string conn1 = "User ID=sa;Data Source=127.0.0.1;Password=;Initial Catalog=Lecture;Provider=SQLOLEDB.1;";
OleDbConnection thisconnection1 = new OleDbConnection(conn1);
thisconnection1.Open();
int count = ds.Tables["[Sheet1$]"].Rows.Count;
for (int i = 0; i < count; i++)
{
string id, id_1, id_2, id_3;
id = ds.Tables["[Sheet1$]"].Rows[i][0].ToString();
id_1 = ds.Tables["[Sheet1$]"].Rows[i][1].ToString();
id_2 = ds.Tables["[Sheet1$]"].Rows[i][2].ToString();
id_3 = ds.Tables["[Sheet1$]"].Rows[i][3].ToString();
string excelsql = "insert into Stu(SNo, SName, SSex, SClass, SPwd) values ('" + id + "','" + id_1 + "','" + id_2 + "','" + id_3 + "', '123456') ";
OleDbCommand mycommand1 = new OleDbCommand(excelsql, thisconnection1);
mycommand1.ExecuteNonQuery();
}
Response.Write("更新成功");
thisconnection1.Close();
for(int i=0;i<dr.length;i++)