怎样将excel文件上传到服务器的内存,然后用OleDbConnection进行连接,将文件读出来.

[201201201]

怎样将excel文件上传到服务器的内存,然后用OleDbConnection进行连接,将文件读出来.

[dwh0722]

http://dotnet.aspx.cc/ShowDetail.aspx?id=C673E2CD-3F1E-4919-8CE0-D69B894A0599

[sunny6281]

Private Sub btn_Up_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Up.Click
Dim strFullName As String = Me.btn_GetFileName.PostedFile.FileName
Dim strFileName As String = strFullName.Substring(strFullName.LastIndexOf("\") + 1)
If strFullName = "" Then
RegisterStartupScript("提示", "<script>alert('请选择文件！');</script>")
Exit Sub
End If
Dim dir As New System.IO.DirectoryInfo(ViewState("folder").ToString)
Dim fileinfo As System.IO.FileInfo() = dir.GetFiles(strFileName)
If fileinfo.Length Then
RegisterStartupScript("提示", "<script>alert('存在同名文件，请更名存储！');</script>")
Exit Sub
End If

ViewState.Item("FileName") = ViewState("folder").ToString + "\" + strFileName
btn_GetFileName.PostedFile.SaveAs(ViewState.Item("FileName").ToString)
dgRight.DataSource = GetXLS(ViewState.Item("FileName").ToString)
dgRight.DataBind()
btn_DB.Visible = True
End Sub

Function GetXLS(ByVal strFileName As String) As DataTable
If strFileName = "" Then
Return Nothing
End If
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + ";Extended Properties=Excel 8.0;"
Dim sql As String = "Select 电脑序号,社会保障号,工号,姓名,所属年度,月平均工资 from [sheet1$]"
Dim adp As New OleDb.OleDbDataAdapter(sql, strConn)
Dim dt As New DataTable()
Try
adp.Fill(dt)
Catch
RegisterStartupScript("提示", "<script>alert('文件打开失败，该文件将被删除，请修改文件内容后再上传！');</script>")
DelFile()
End Try
Return dt
End Function

[sunny6281]

Private Sub btn_DB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_DB.Click
Dim dt As DataTable = GetXLS(ViewState.Item("FileName").ToString)
If dt Is Nothing Then
Exit Sub
End If

Dim dr As DataRow
Dim comm As New OleDb.OleDbCommand("", New OleDb.OleDbConnection(Application.Item("ConnStr").ToString))
comm.Connection.Open()
Dim dtError As New DataTable()
Dim col As DataColumn
For Each col In dt.Columns
dtError.Columns.Add(col.ColumnName)
Next

For Each dr In dt.Rows
Try
comm.CommandText = " insert into base (id,insureid,wagetime,avgwage) values(" + dr("电脑序号").ToString + ",'" + dr("社会保障号").ToString + "','" + dr("所属年度").ToString + "'," + dr("月平均工资").ToString + ")"
If comm.Connection.State = ConnectionState.Closed Then
comm.Connection.Open()
End If
comm.ExecuteNonQuery()
Catch
dtError.ImportRow(dr)
End Try
Next
comm.Connection.Close()
comm.Dispose()
dt.Dispose()
RegisterStartupScript("提示", "<script>alert('数据已成功导入数据库！');</script>")
dgRight.DataBind()
End Sub

[wlhtake]

使用GetXLS需要引用什么呢？？？？！？！？！？！