如何将Excel表中的数据导入数据库

[sexyneco]

做一个网页，客户提供本地Excel表的路径，提交后将Excel表中数据导入服务器数据库中。
谢谢！

[acewang]

string fileExcel, filePath, fileName, strLine, sql;

FileStream objFileStream;

StreamWriter objStreamWriter;

Random nRandom = new Random(DateTime.Now.Millisecond);

SqlConnection cnn = new SqlConnection("data source=(local);initial catalog=chinapackage;user id=sa;password=;");



//Create a random file name.

fileExcel = "t" + nRandom.Next().ToString() + ".xls";



//Set a virtual folder to save the file.

//Make sure to change the application name to match your folder.

filePath = Server.MapPath("\\StartExcel");

fileName = filePath + "\\" + fileExcel;



//Use FileSystemObject to create the .xls file.

objFileStream = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write);

objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.Unicode);



//Use a DataReader object to connect to the Pubs database.

cnn.Open();

//sql = "select au_id,au_lName,au_fname,phone,address,city,state,zip,contract from authors";

sql = "select * from tbdownload";

SqlCommand cmd = new SqlCommand(sql, cnn);

SqlDataReader dr;

dr = cmd.ExecuteReader();



//Initialize the string that is used to build the file.

strLine = "";



//Enumerate the field names and the records that are used to build

//the file.

for (int i = 0; i <= dr.FieldCount-1; i++)

{

strLine = strLine + dr.GetName(i).ToString() + Convert.ToChar(9);

}



//Write the field name information to the file.

objStreamWriter.WriteLine(strLine);



//Reinitialize the string for data.

strLine = "";



//Enumerate the database that is used to populate the file.

while (dr.Read())

{

for (int i = 0; i <= dr.FieldCount-1; i++)

{

strLine = strLine + dr.GetValue(i).ToString() + Convert.ToChar(9);

}

objStreamWriter.WriteLine(strLine);

strLine="";

}



//Clean up.

dr.Close();

cnn.Close();

objStreamWriter.Close();

objFileStream.Close();



//Include a link to the Excel file.

HyperLink1.Text="Open Excel";

HyperLink1.NavigateUrl=fileExcel;

[wolve]

有两个方案。
一个通过oledb数据engine访问excle
一个办法通过调用office的com对象来实现，本人推荐第二种办法。

[sexyneco]

我现在打算先做个窗体程序，窗体上有一个按钮和一个DataGrid，我需要点击按钮后将E:/Test.xls文件中的数据读到DataGrid中，我写的代码如下：

string connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+@"E:/Test.xls";
OleDbConnection odc=new OleDbConnection(connstr);
string selectstr="select * from TestData";
OleDbDataAdapter oda=new OleDbDataAdapter();
odc.Open();
oda.SelectCommand=new OleDbCommand(selectstr);
oda.SelectCommand.Connection=odc;
oda.SelectCommand.ExecuteNonQuery();
DataSet ds=new DataSet();
oda.Fill(ds);
odc.Close();
this.dataGrid1.DataSource=ds;
this.dataGrid1.SetDataBinding(ds,"");

Test.xls中有一个sheet，名为TestData，我想用上面的程序将数据读到DataSet中，然后绑定在DataGrid上，但执行到oda.SelectCommand=new OleDbCommand(selectstr);时系统报错，我想问链接一个excel文件的方法是这样么？是不是我的连接字符串中的Provider不应该写Microsoft.Jet.OLEDB.4.0？按照我的方法能作出来么？谢谢！

[zskyc]

我是先把execl文件传到服务器上，然后用Microsoft.Jet.OLEDB.4.0去读这个文件
string selectstr="select * from `" &文件路径& "`.`TestData` `TestData`";

[wolve]

Private Sub BindData()
Dim myOleDbConnection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _dbFile & _
";Extended Properties=Excel 8.0;")
Dim myOleDbCommand As OleDbCommand = New OleDbCommand("SELECT * FROM [联系人$]", myOleDbConnection)
Dim myData As OledbDataAdapter = New OledbDataAdapter(myOleDbCommand)
myData.Fill(_address)

myOleDbCommand.CommandText = "SELECT * FROM [客户$]"
myData.Fill(_customer)

myOleDbCommand.CommandText = "SELECT * FROM [合作单位$]"
myData.Fill(_collaborator)

dgMyData.DataSource = _address
dgMyData.Update()

myOleDbCommand.Dispose()
myOleDbConnection.Dispose()

End Sub

Private Sub bnSelectFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bnSelectFile.Click
If dlgOpenExcel.ShowDialog() = DialogResult.OK Then
BindData()
Button1.Visible = True
Button2.Visible = True
Button3.Visible = True
End If
End Sub

这个是通过oledb来操作的代码
通过com来操作的到msdn上查一下，我现在没有

[QinMuBiao]

关注