通过OleDbDataAdapter将指定路径的.xls文件填充到DataSet中。
using
System.Data.OleDb;
using System.Data;
...
... {
protected DataSet ExcelToDS(string FilePath)
...{
DataSet ds=new DataSet();
new OleDbDataAdapter("SELECT * FROM [Sheet1$]", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0;").Fill(ds,"Sheet1");
return ds;
}
}
using System.Data;
...
... {
protected DataSet ExcelToDS(string FilePath)
...{
DataSet ds=new DataSet();
new OleDbDataAdapter("SELECT * FROM [Sheet1$]", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0;").Fill(ds,"Sheet1");
return ds;
}
}
然后将DataSet按照格式写入数据库中:
using
System.Data;
using System.Data.SqlClient;
....
... {
.....
protected void LoadButton_Click(object sender, EventArgs e)
...{
if (FileUpload1.PostedFile.FileName.ToString() != null
&& FileUpload1.PostedFile.FileName.ToString() != ""
&& FileUpload1.PostedFile.FileName.EndsWith(".xls")) //FileUpload1为页面中控件
...{
DataSet ds = null;
try
...{
string path = FileUpload1.FileName;
string strFilePath = MapPath("excel") + "/" + path;
FileUpload1.PostedFile.SaveAs(strFilePath);
ds = ExcelToDS(strFilePath);
for (int i = 0; i < ds.Tables[0].Rows.Count-1; i++)
...{
for (int j = 0; j < 6; j++)
...{
string colvalue = ds.Tables[0].Rows[i].ItemArray[j].ToString();
if (j < 2 && colvalue == "")
...{
Response.Write("<script > window.alert( '数据导入发生错误!') </script > ");
}
}
//得到各列的值
string user_id = ds.Tables[0].Rows[i].ItemArray[0].ToString();
......
string union_name = ds.Tables[0].Rows[i].ItemArray[10].ToString();
SqlConnection MyConnection = new SqlConnection();
MyConnection.ConnectionString = MyConnectionString;//MyConnectionString 为你的数据库连接字符串
SqlCommand MyCommand = new SqlCommand();
MyCommand.CommandText="INSERT INTO [XXX] ( [USER_ID],......,[STATUS] ) VALUES ( @user_id,...,@status)";
MyCommand.Connection = MyConnection;
MyCommand.Parameters.Add(new SqlParameter("@user_id",user_id));
.......
MyCommand.Parameters.Add(new SqlParameter("@status",status));
MyConnection.Open();
MyCommand.ExecuteNonQuery();
MyConnection.Close();
}
}
catch (SqlException exc)
...{
Response.Write("<script >window.alert('导入数据发生错误:"+ exc.Message+"') </script > ");
return;
}
Response.Write("<script>window.alert('已成功导入数据!')</script>");
}
else Response.Write("<script>window.alert('请至少选择一个Excel文件导入!')</script>");
}
.....
}
using System.Data.SqlClient;
....
... {
.....
protected void LoadButton_Click(object sender, EventArgs e)
...{
if (FileUpload1.PostedFile.FileName.ToString() != null
&& FileUpload1.PostedFile.FileName.ToString() != ""
&& FileUpload1.PostedFile.FileName.EndsWith(".xls")) //FileUpload1为页面中控件
...{
DataSet ds = null;
try
...{
string path = FileUpload1.FileName;
string strFilePath = MapPath("excel") + "/" + path;
FileUpload1.PostedFile.SaveAs(strFilePath);
ds = ExcelToDS(strFilePath);
for (int i = 0; i < ds.Tables[0].Rows.Count-1; i++)
...{
for (int j = 0; j < 6; j++)
...{
string colvalue = ds.Tables[0].Rows[i].ItemArray[j].ToString();
if (j < 2 && colvalue == "")
...{
Response.Write("<script > window.alert( '数据导入发生错误!') </script > ");
}
}
//得到各列的值
string user_id = ds.Tables[0].Rows[i].ItemArray[0].ToString();
......
string union_name = ds.Tables[0].Rows[i].ItemArray[10].ToString();
SqlConnection MyConnection = new SqlConnection();
MyConnection.ConnectionString = MyConnectionString;//MyConnectionString 为你的数据库连接字符串
SqlCommand MyCommand = new SqlCommand();
MyCommand.CommandText="INSERT INTO [XXX] ( [USER_ID],......,[STATUS] ) VALUES ( @user_id,...,@status)";
MyCommand.Connection = MyConnection;
MyCommand.Parameters.Add(new SqlParameter("@user_id",user_id));
.......
MyCommand.Parameters.Add(new SqlParameter("@status",status));
MyConnection.Open();
MyCommand.ExecuteNonQuery();
MyConnection.Close();
}
}
catch (SqlException exc)
...{
Response.Write("<script >window.alert('导入数据发生错误:"+ exc.Message+"') </script > ");
return;
}
Response.Write("<script>window.alert('已成功导入数据!')</script>");
}
else Response.Write("<script>window.alert('请至少选择一个Excel文件导入!')</script>");
}
.....
}