在vb.net中导入不同的excels文件 [英] Import different excels file in vb.net
问题描述
我有点混淆保存不同的名称excel文件.......我想从vb.net中的excel文件导入数据并保存在数据库中....我导入我的excel文件和同样保存数据。但我的问题是我不想修复excel文件名或工作表名。请有人帮助我
我的代码在那里:
私有 Sub BtnSave_Click( ByVal 发件人作为系统。对象, ByVal e As System.EventArgs)句柄 BtnSave.Click
如果 validate_data()那么
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCom As System.Data.OleDb.OleDbDataAdapter
Dim fileBrowse 作为 新 OpenFileDialog
使用 fileBrowse
.Filter = Excel文件(*。xlsx)| * .xlsx |所有文件(*。 *)| *。*
.FilterIndex = 1
。标题= 从Excel文件导入数据
结束 使用
如果 fileBrowse.ShowDialog()= Windows.Forms.DialogResult.OK Ť母鸡
昏暗 fname 作为 字符串
fname = fileBrowse.FileName
DtSet = 新 System.Data.DataSet
MyConnection = 新 System.Data.OleDb.OleDbConnection( Provider = Microsoft.ACE。 OLEDB.12.0;数据源='& fname& '; & 扩展属性= Excel 8.0;)
MyCom = 新 System.Data.OleDb.OleDbDataAdapter( select * from [stulist.xlsx $],MyConnection)
MyCom.TableMappings.Add( 表, Student_Master )
DtSet = 新 System.Data.DataSet
MyCom.Fill(DtSet)
MyConnection.Close()
对于 每个行作为 DataRow 在 DtSet.Tables( 0 )。行
obj.InsertData( INSERT INTO Student_Master(Student_Name,Student_Rollno,Student_Enrollment)VALUES('& row( 0 )。ToString& ','& row( 1 )。ToString& ','& row( 2 )。ToString& '))
下一步
MsgBox( 导入的项目已成功保存)
结束 如果
结束 如果
结束 Sub
在这个程序中我修复了excel文件名,名称为stulist,但实际上我不想修复excel文件名
,MyConnection)
MyCom.TableMappings.Add( 表, Student_Master)
DtSet = 新 System.Data。 DataSet
MyCom.Fill(DtSet)
MyConnection.Close()
对于 每个 row As DataRow 在 DtSet.Tables( 0 )。行
obj.InsertData( INSERT INTO Student_Master(Student_Name,Student_Rollno,Student_Enrollment )价值观('& row( 0 )。ToString& ','& row( 1 )。ToString& ','& row( 2 )。ToString& '))
下一步
MsgBox( 导入的项目已成功保存)
结束 如果
结束 如果
结束 Sub
在这个程序中我修复了excel文件名,名称为stulist但实际上我不想修复excel文件名
< blockquote> 2注释:
1)连接字符串错误:
MyConnection = 新 System.Data.OleDb.OleDbConnection( Provider = Microsoft.ACE .OLEDB.12.0;数据源='& fname& '; & 扩展属性= Excel 8.0;)
请参考: http://www.connectionstrings.com / excel / [ ^ ]
如果您想连接到MS Excel 2007(及更高版本)文件,请使用:
Provider = Microsoft。 ACE 。 OLEDB 。 12 .0;数据源= c:\ myFolder \\ \\ myExcel2007file.xlsx; 扩展属性=Excel 12 。 0 Xml; HDR = YES;
对于MS Excel 97-2003,请使用:
Provider = Microsoft。 Jet 。 OLEDB 。 4 .0;数据源= C:\ MyExcel.xls; 扩展属性=Excel 8 .0; HDR =是;
2)错误命令:
选择 * 来自 [stulist .xlsx
您的命令应参考现有表格:
选择 * 来自 [Sheet1
I am little bit confusing to save the different name excel file .......I want to import data from excel file in vb.net and save in the database ....I am import my excel file and save data too also.But my problem is that i don't want to fix excel file name or sheet name .Please someone help me
my code is there:
Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click
If validate_data() Then
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCom As System.Data.OleDb.OleDbDataAdapter
Dim fileBrowse As New OpenFileDialog
With fileBrowse
.Filter = "Excel files(*.xlsx)|*.xlsx|All files (*.*)|*.*"
.FilterIndex = 1
.Title = "Import data from Excel file"
End With
If fileBrowse.ShowDialog() = Windows.Forms.DialogResult.OK Then
Dim fname As String
fname = fileBrowse.FileName
DtSet = New System.Data.DataSet
MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & fname & " '; " & "Extended Properties=Excel 8.0;")
MyCom = New System.Data.OleDb.OleDbDataAdapter("select * from [stulist.xlsx$]", MyConnection)
MyCom.TableMappings.Add("Table", "Student_Master")
DtSet = New System.Data.DataSet
MyCom.Fill(DtSet)
MyConnection.Close()
For Each row As DataRow In DtSet.Tables(0).Rows
obj.InsertData("INSERT INTO Student_Master(Student_Name, Student_Rollno, Student_Enrollment) VALUES ('" & row(0).ToString & "','" & row(1).ToString & "','" & row(2).ToString & "')")
Next
MsgBox("Imported Item Successfully Saved")
End If
End If
End Sub
and in this program i have fix excel file name in the name of "stulist"but actually i dont want to fix excel file name
解决方案 ", MyConnection)
MyCom.TableMappings.Add("Table", "Student_Master")
DtSet = New System.Data.DataSet
MyCom.Fill(DtSet)
MyConnection.Close()
For Each row As DataRow In DtSet.Tables(0).Rows
obj.InsertData("INSERT INTO Student_Master(Student_Name, Student_Rollno, Student_Enrollment) VALUES ('" & row(0).ToString & "','" & row(1).ToString & "','" & row(2).ToString & "')")
Next
MsgBox("Imported Item Successfully Saved")
End If
End If
End Sub
and in this program i have fix excel file name in the name of "stulist"but actually i dont want to fix excel file name
2 notes:
1) bad connection string:
MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & fname & " '; " & "Extended Properties=Excel 8.0;")
Please, refer this: http://www.connectionstrings.com/excel/[^]
If you would like to connect to MS Excel 2007 (and higher) file, please use:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx; Extended Properties="Excel 12.0 Xml;HDR=YES";
For MS Excel 97-2003, please use:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls; Extended Properties="Excel 8.0;HDR=Yes;
2) bad command:
select * from [stulist.xlsx
Your command should refer to existing sheet:
select * from [Sheet1
这篇关于在vb.net中导入不同的excels文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!