Question

C＃在网页中，从excel中读入数据，然后插入到数据库

我要具体代码，文件从fileupload中打开．谢谢各位！

Answer 1

if (FileUpload1.PostedFile != null)
{
string str = FileUpload1.PostedFile.FileName;
int i = str.LastIndexOf("\\");
String filename=str.Substring(i+1);
FileUpload1.PostedFile.SaveAs(@Server.MapPath("xls") + filename);

Label1.Text = "文件名为" + filename;
string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Server.MapPath("xls") + "\\" + filename + ";Extended Properties=Excel 8.0";
OleDbConnection thisconnection = new OleDbConnection(conn);
thisconnection.Open();
string Sql = "select * from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
DataSet ds = new DataSet();
mycommand.Fill(ds, "[Sheet1$]");
thisconnection.Close();

string conn1 = System.Configuration.ConfigurationManager.ConnectionStrings["ZOPGConn"].ToString();
SqlConnection thisconnection1 = new SqlConnection(conn1);
thisconnection1.Open();
int count = ds.Tables["[Sheet1$]"].Rows.Count;

for (int j = 0; j < count; j++)
{
string id_1, id_2, id_3;
id_1 = ds.Tables["[Sheet1$]"].Rows[j]["id1"].ToString();
id_2 = ds.Tables["[Sheet1$]"].Rows[j]["id2"].ToString();
id_3 = ds.Tables["[Sheet1$]"].Rows[j]["id3"].ToString();
string excelsql = "insert into test(id1,id2,id3) values ('" + id_1 + "','" + id_2 + "','" + id_3 + "') ";
SqlCommand mycommand1 = new SqlCommand(excelsql, thisconnection1);
mycommand1.ExecuteNonQuery();
}
Response.Write("更新成功");
thisconnection1.Close();
}

Answer 2

复制下来运行一下就明白了，别忘了导入 System.Data.OleDb 命名空间

using System.Data.OleDb;

// 程序如下：

String path = Server.MapPath("/ExcelFile.xls");
String conString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
OleDbConnection conn = new OleDbConnection(conString);
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", conn);
DataSet ds = new DataSet();
adapter.Fill(ds);

剩下的事情就是操作 ds 对象了，比如可以遍历这个存入 DataSet 的 Excel 数据：

for (int i=0;i<ds.Tables[0].Columns.Count;i++)
{
// 遍历所有的列
}

for (int j=0;j<ds.Tables[0].Rows.Count;j++)
{
// 遍历所有的行
}

Answer 3

1: 保存文件到服务器:FileUpload1.PostedFile.SaveAs
(FileUpload1.PostedFile.FileName.ToString());
2: 从这个Excel中读数据并存到一个DATASET里去:
string mystring = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '" + Filename + "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";

OleDbConnection cnnxls = new OleDbConnection(mystring);
OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [sheet1]", cnnxls);
DataSet myDs = new DataSet();
myDa.Fill(myDs.Tables["exceldata"]);
3:这个myDs的数据就是上传的那个EXCEL中的数据了,有了这个DATASET,
你就可以插入数据库了,

Answer 4

正确，不过文件太大了效率就不准们好了啊