vb.net 将excel工作表导入vb.net
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37522364/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Import excel worksheet into vb.net
提问by Kongkon Mahanta
I have got an excel worksheet which actually calculates the loan... If we input the amount and no. of years,it gives us a list of all the EMI's to be made in the coming years.
我有一个实际计算贷款的excel工作表......如果我们输入金额和没有。多年来，它为我们提供了未来几年将要制作的所有 EMI 的清单。
My boss wants to integrate that excel worksheet into vb.net form....How can I do so? Please help me....
我的老板想将那个 excel 工作表集成到 vb.net 表单中......我该怎么做？请帮我....
回答by Beldi Anouar
In your form add a DataGrid "GridControl1"and a button "BtnImport_Click:
在您的表单中添加 aDataGrid "GridControl1"和 a button "BtnImport_Click：
In your button BtnImport_Clickadd this code
在您的按钮中BtnImport_Click添加此代码
Private Sub BtnImport_Click(sender As Object, e As EventArgs) Handles BtnImport.Click
Dim dialog As New OpenFileDialog()
dialog.Filter = "Excel files |*.xls;*.xlsx"
dialog.InitialDirectory = "C:\"
dialog.Title = "Veuillez sélectionner le fichier à importer"
'Encrypt the selected file. I'll do this later. :)
If dialog.ShowDialog() = DialogResult.OK Then
Dim dt As DataTable
dt = ImportExceltoDatatable(dialog.FileName)
GridControl1.DataSource = dt
GridControl1.Visible = True
MsgBox(" done ! ", MsgBoxStyle.Information)
End If
End Sub
And add this functionin your form:
并将其添加function到您的form：
Public Shared Function ImportExceltoDatatable(filepath As String) As DataTable
' string sqlquery= "Select * From [SheetName$] Where YourCondition";
Dim dt As New DataTable
Try
Dim ds As New DataSet()
Dim constring As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filepath & ";Extended Properties=""Excel 12.0;HDR=YES;"""
Dim con As New OleDbConnection(constring & "")
con.Open()
Dim myTableName = con.GetSchema("Tables").Rows(0)("TABLE_NAME")
Dim sqlquery As String = String.Format("SELECT * FROM [{0}]", myTableName) ' "Select * From " & myTableName
Dim da As New OleDbDataAdapter(sqlquery, con)
da.Fill(ds)
dt = ds.Tables(0)
Return dt
Catch ex As Exception
MsgBox(Err.Description, MsgBoxStyle.Critical)
Return dt
End Try
End Function
Hop that help you
跳这对你有帮助
