Dim conn As OleDbConnection Dim dtr As OleDbDataReader Dim dta As OleDbDataAdapter Dim cmd As OleDbCommand Dim dts As DataSet Dim excel As String Dim OpenFileDialog As New OpenFileDialog OpenFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments OpenFileDialog.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls" If (OpenFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then Dim fi As New FileInfo(OpenFileDialog.FileName) Dim FileName As String = OpenFileDialog.FileName excel = fi.FullName conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;") dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn) dts = New DataSet dta.Fill(dts, "[Sheet1$]") DataGridView1.DataSource = dts DataGridView1.DataMember = "[Sheet1$]" conn.Close()
MSDN wrote:You do not need to set this property (DataMember) when binding to a data source that contains a single list or table.
Imports System.Data.SqlClient Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim MyConnection As System.Data.OleDb.OleDbConnection Dim DtSet As System.Data.DataSet Dim MyCommand As System.Data.OleDb.OleDbDataAdapter MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\vb.net-informations.xls';Extended Properties=Excel 8.0;") MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection) MyCommand.TableMappings.Add("Table", "Net-informations.com") DtSet = New System.Data.DataSet MyCommand.Fill(DtSet) DataGridView1.DataSource = DtSet.Tables(0) MyConnection.Close() End Sub End Class
' This function open the excel file in excel application and get all the work sheet names and return it as array Private Function getExcelSheetsName(ByVal Excelfilename As String) As ArrayList Dim objExcel As Excel.Application Dim objWorkBook As Excel.Workbook Dim objWorkSheets As Excel.Worksheet Dim SheetList As New ArrayList objExcel = CreateObject("Excel.Application") objWorkBook = objExcel.Workbooks.Open(Excelfilename) For Each objWorkSheets In objWorkBook.Worksheets SheetList.Add(objWorkSheets.Name) ' ListBox1.Items.Add(objWorkSheets.Name) Next End_Excel_App() Return SheetList End Function
'get all excel worksheet names Dim arrList As ArrayList = getExcelSheetsName(excel) 'get the first sheet name and add the sign $ to it Dim sheetName As String = "[" + arrList(0) + "$]"
Dim sheetName As String = "[" + arrList(0) + "$]"
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)