Imports System.IO Imports System.Data Imports System.Data.OleDb Public Class Form1 Private Excel03ConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'" Private Excel07ConString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'" Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'Database1DataSet.Table1' table. You can move, or remove it, as needed. Me.Table1TableAdapter.Fill(Me.Database1DataSet.Table1) End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Try OpenFileDialog1.ShowDialog() Catch ex As Exception MsgBox("error", vbCritical, "my project") End Try End Sub Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk Try Dim filePath As String = OpenFileDialog1.FileName Dim extension As String = Path.GetExtension(filePath) Dim header As String = If(checkbox1.Checked, "YES", "NO") Dim conStr As String, sheetName As String conStr = String.Empty Select Case extension Case ".xls" 'Excel 97-03 conStr = String.Format(Excel03ConString, filePath, header) Exit Select Case ".xlsx" 'Excel 07 conStr = String.Format(Excel07ConString, filePath, header) Exit Select End Select 'Get the name of the First Sheet. Using con As New OleDbConnection(conStr) Using cmd As New OleDbCommand() cmd.Connection = con con.Open() Dim dtExcelSchema As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) sheetName = dtExcelSchema.Rows(0)("TABLE_NAME").ToString() con.Close() End Using End Using 'Read Data from the First Sheet. Using con As New OleDbConnection(conStr) Using cmd As New OleDbCommand() Using oda As New OleDbDataAdapter() Dim dt As New DataTable() cmd.CommandText = (Convert.ToString("SELECT * From [") & sheetName) + "]" cmd.Connection = con con.Open() oda.SelectCommand = cmd oda.Fill(dt) con.Close() 'Populate DataGridView. DataGridView1.DataSource = dt Table1BindingSource.EndEdit() Table1TableAdapter.Update(Database1DataSet.Table1) MsgBox("OK") End Using End Using End Using Catch ex As Exception MsgBox("error", vbCritical, "My project") End Try End Sub End Class
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)