100分求教Excel导入的问题
思路是：不同客户端打开Excel导入后台数据库一个动态临时表中。想通过先把Excel数据导入DataSet中，再通过DataSet把数据导入到临时表中。
Dim InExStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & strExcelName & ";Extended ProPerties= " "Excel 8.0;HDR=Yes;IMEX=1 " " "
Dim InExOleDbCon As New OleDb.OleDbConnection(InExStr)
Dim InExOleAd As New OleDb.OleDbDataAdapter( "Select * From [SortData$] ", InExOleDbCon)
Dim InExDs As New DataSet
InExOleAd.Fill(InExDs, strTempTable)
Dim InExDataAd As DbDataAdapter
InExDataAd.SelectCommand.CommandText = "UpdateSql "
Dim InExCom As SqlCommandBuilder = New SqlCommandBuilder(InExDataAd)
InExDataAd.UpdateCommand = InExCom.GetDeleteCommand
InExDataAd.Update(InExDs)
[解决办法]
如果数据库用的是sql server ,有更简单的有效的办法
SELECT *
into 临时表
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0 ',
'Data Source= "c:\book1.xls ";User ID=Admin;Password=;Extended properties=Excel 5.0 ')...[Sheet1$]
[解决办法]
以前做过的一个小例子 希望对你有启发!
Try
'************************************************************************************************************
'获得客户打开文件的路径赋给filename,满足客户自定义数据源
Dim FileName As String = " "
Dim OpenFileDialog As New OpenFileDialog
OpenFileDialog.Title = "打開 "
OpenFileDialog.Filter = ".xls|*.xls "
OpenFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
If (OpenFileDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK) Then
FileName = OpenFileDialog.FileName
End If
If FileName = " " Then
Exit Sub
End If
Dim cn As System.Data.OleDb.OleDbConnection
Dim cmd As System.Data.OleDb.OleDbDataAdapter
Dim ds As New System.Data.DataSet()
'MsgBox(FileName)
'cn = New System.Data.OleDb.OleDbConnection( "provider=Microsoft.Jet.OLEDB.4.0; " & _
' "data source=FileName;Extended Properties=Excel 8.0; ")
cn = New System.Data.OleDb.OleDbConnection( "provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source=D:\departments.xls;Extended Properties=Excel 8.0; ")
'绝对路径是为data source=D:\departments.xls时可行
'************************************************************************************************************
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
Dim xSheet As Microsoft.Office.Interop.Excel.Worksheet
xlBook = xlApp.Workbooks.Open(FileName)
xSheet = xlBook.Worksheets(1)
xlApp.Visible = False
'MsgBox(xSheet.Name)
'Debug.Print(xSheet.Name)
'获取客户导入的Excel子表的名称 eg.sheet1,满足自定义这个名字
'************************************************************************************************************
' Select the data from Sheet1 of the workbook.
'cmd = New System.Data.OleDb.OleDbDataAdapter( "select * from [xSheet.Name & $] ", cn)
cmd = New System.Data.OleDb.OleDbDataAdapter( "select * from [sheet1$] ", cn)
'绝对名为select * from [Sheet1$]时可行
cn.Open()
cmd.Fill(ds)
cn.Close()
Dim str As String = "Data Source=IBM-leep2007\sqlexpress;Initial Catalog=db_pro;Integrated Security=True "
Dim conn As New SqlClient.SqlConnection(str)
'conn.Open()
Dim commandstring As String = "select * from departments "
Dim myadapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(commandstring, conn)
Dim mySqlCommandBuilder As SqlClient.SqlCommandBuilder = New SqlClient.SqlCommandBuilder(myadapter)
Dim dt As New DataSet
myadapter.Fill(dt)
'MsgBox( "mkk ")
Dim rownum_excel As Integer
Dim colnum_excel As Integer
'Dim rownum_sql As Integer
'Dim colnum_sql As Integer
For rownum_excel = 0 To ds.Tables(0).Rows.Count - 1
For colnum_excel = 0 To ds.Tables(0).Columns.Count - 1
dt.Tables(0).Rows(CInt(rownum_excel)).Item(CInt(colnum_excel)) = ds.Tables(0).Rows(CInt(rownum_excel)).Item(CInt(colnum_excel))
Next
Next
myadapter.Update(dt)
dt.AcceptChanges()
'MsgBox( "mkk ")
GridControl1.DataSource = Nothing
GridControl1.DataSource = dt.Tables(0)
Catch ex As Exception
MsgBox(ex.Message)
End Try
[解决办法]
Dim excelapp As Excel.Application '声明一个application对象
Dim excelworkbook As Excel.Workbook '声明一个工作簿对象
Dim excelsheet As Excel.Worksheet '声明一个工作表对象
' Dim excelrange As Excel.Range '声明一个范围对象
Dim strTmp, strPath, strIlines As String
Dim i As Int16
dlgOpen.Title = "请选择发货数据表 "
dlgOpen.FileName = " "
dlgOpen.Filter = "*.xls|*.xls "
If dlgOpen.ShowDialog = Windows.Forms.DialogResult.OK Then
strTmp = dlgOpen.FileName
strPath = System.IO.Path.GetDirectoryName(dlgOpen.FileName)
Else
MessageBox.Show( "没有选择文件! ", "提示 ") : Exit Sub
End If
excelapp = New Excel.Application
excelworkbook = excelapp.Workbooks.Open(strTmp) '访问到工作簿：这个strfile 是文件的路径，我从打开文件对话框中得到的。
excelsheet = excelworkbook.Sheets.Item(1) '访问到工作表：item使用索引值来得到sheet对象的引用
excelsheet.Activate()
i = 3
Grd.AutoRedraw = False
Do Until Trim(excelsheet.Cells(i, 1).value) = " "
strIlines = SHJFExist(Trim(excelsheet.Cells(i, 4).value))
If Val(strIlines) > 0 Then
strTmp = MessageBox.Show(Trim(excelsheet.Cells(i, 4).value) & "此运单号已存在！是否继续？ ", "提示 ", MessageBoxButtons.OKCancel)
If Val(strTmp) = 1 Then
i = i + 1
Else
GoTo gtCon
End If
Else
'后面用.trim格式化字符串 用trim()后产生多余？号
Dim x1 As String = ((excelsheet.Cells(i, 1).value & " ").ToString).Trim
Dim x2 As String = CStr(excelsheet.Cells(i, 2).value & " ").ToString.Trim
Dim x3 As String = ((excelsheet.Cells(i, 3).value & " ").ToString).Trim
Dim x4 As String = ((excelsheet.Cells(i, 4).value & " ").ToString).Trim
Dim x5 As String = ((excelsheet.Cells(i, 5).value & " ").ToString).Trim
Dim x6 As String = ((excelsheet.Cells(i, 6).value & " ").ToString).Trim
Dim x7 As String = ((excelsheet.Cells(i, 7).value & " ").ToString).Trim
Dim x8 As String = ((excelsheet.Cells(i, 8).value & " ").ToString).Trim
Dim x9 As String = ((excelsheet.Cells(i, 9).value & " ").ToString).Trim
' Dim x10 As String = ((excelsheet.Cells(i, 10).value & " ").ToString).Trim
Dim x13 As String = ((excelsheet.Cells(i, 13).value & " ").ToString).Trim
strTmp = WS.ExecuteSQL( "JRWL ", "Insert Into shjf (cNo,cMid,cEnd,cBillNo,cUnit,cRUnit,cFoodsName,cFoodsStyle,cPackaging,iNumber,iWeight,mWeight,cDemo,ddate) Values ( ' " & _
x1 & " ', ' " & x2 & " ', ' " & x3 & " ', ' " & x4 & " ', ' " & x5 & " ', ' " & x6 & " ', ' " & x7 & " ', ' " & x8 & " ', ' " & x9 & " ', ' " & CDec(Val(Trim(excelsheet.Cells(i, 10).value))) & " ', ' " & CDec(Val(Trim(excelsheet.Cells(i, 11).value))) & " ', " & Val(Trim(excelsheet.Cells(i, 12).value)) & ", ' " & _
x13 & " ', ' " & Format(DSDate, "yyyy-MM-dd ") & " ') ")
i = i + 1
End If
Loop
If strTmp = "1 " Then
MessageBox.Show( "引入完成！ ")
Else
MessageBox.Show(strTmp)
End If
GoTo gtCon
'金额表引入
gtCon: excelapp.Workbooks.Close()
excelapp.Quit()
LoadGrd()
System.GC.Collect(0)
