将数据存储在Excel中到SQL Server数据库

本文介绍了将数据存储在Excel中到SQL Server数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我创建了一个项目. 我在其中使用了标签，文件上传，gridview和按钮. 这是按钮单击事件中的代码:

I am created a project. I used a label, fileupload, gridview and button in it. This is the code in button click event :

SqlConnection con = new SqlConnection(/*connection string*/); FileUpload1.SaveAs(Server.MapPath("~//App_Data//") + FileUpload1.FileName); string path = Server.MapPath("~//App_Data//") + FileUpload1.FileName; try { //for .xls file System.Data.OleDb.OleDbConnection MyConnection; DataSet DtSet; System.Data.OleDb.OleDbDataAdapter MyCommand; MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties=Excel 8.0;"); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet, "[Sheet1$]"); MyConnection.Close(); GridView1.DataSource = DtSet.Tables[0]; GridView1.DataBind(); SqlDataAdapter adpter = new SqlDataAdapter("Select * from StudentList", con); adpter.Update(DtSet.Tables[0]); Response.Write("successfully Imported values to SQL Server"); } catch (Exception ex) { Response.Write(ex.Message); }

上面的代码成功执行.它从excel检索数据并显示在GridView中，但DataSet表行未保存在Sql Server中创建的表中. 我想使用断开连接的模式. 将MapPath行中的反斜杠更改为斜杠，以便代码着色器可以应对. [/Edit]

The above code executes successfully. It retrieves data from excel and displays in GridView but DataSet table row does not get saved in table created in Sql Server. I want to use disconnected mode. Changed backslashes to slashes in the MapPath lines so that the code colourizer could cope. [/Edit]

推荐答案

"，MyConnection)； DtSet = 新 System.Data.DataSet(); MyCommand.Fill(DtSet，" [Sheet1 ", MyConnection); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet, "[Sheet1

" ) ; MyConnection.Close(); GridView1.DataSource = DtSet.Tables [ 0 ]; GridView1.DataBind(); SqlDataAdapter adpter = 新建 SqlDataAdapter(" 从StudentList中选择*" ，骗局)； adpter.Update(DtSet.Tables [ 0 ]); Response.Write(" 已成功将值导入到SQL Server" )； } 捕获(例外) { Response.Write(ex.Message); } "); MyConnection.Close(); GridView1.DataSource = DtSet.Tables[0]; GridView1.DataBind(); SqlDataAdapter adpter = new SqlDataAdapter("Select * from StudentList", con); adpter.Update(DtSet.Tables[0]); Response.Write("successfully Imported values to SQL Server"); } catch (Exception ex) { Response.Write(ex.Message); }

上面的代码成功执行.它从excel检索数据并显示在GridView中，但DataSet表行未保存在Sql Server中创建的表中. 我想使用断开连接的模式. 将MapPath行中的反斜杠更改为斜杠，以便代码着色器可以应对. [/Edit]

The above code executes successfully. It retrieves data from excel and displays in GridView but DataSet table row does not get saved in table created in Sql Server. I want to use disconnected mode. Changed backslashes to slashes in the MapPath lines so that the code colourizer could cope. [/Edit]

我认为如果您先将其存储到数据库中并从中选择它会更好.请在此处查看更多信息，以及如何使用SqlBulkCopy的示例. stackoverflow/questions/3664067/import-data-from- excel-into-multiple-tables [ ^ ] 附带说明:在您的代码示例中，最好尽可能晚地创建连接(con)，这样您就不会使用不必要的资源并保持连接占用，即使直到以后. 祝你好运！ I think it would work better if you would store it into the database first and select it from there. Have a look here for more more info and an example how to use SqlBulkCopy for that. stackoverflow/questions/3664067/import-data-from-excel-into-multiple-tables[^] As a side note: In your code example it would be somewhat better to create the connection (con) as late as possible so you don''t use unnecessary resources and keep a connection occupied even if it isn''t used until later. Good luck!