Import excel doc to a database in VB.Net2005

[NickJ]

Hay people

I’m very green to the world of VB.Net 2005 so would appreciate some help.

I would like to know how would I import a Excel spread sheet into a Datagrid?

This is what I have so far, but for some reason I get no errors and no data? But nothing happens.

[FONT=&quot]Dim[/FONT][FONT=&quot] DS As System.Data.DataSet[/FONT]
[FONT=&quot] Dim MyCommand As System.Data.OleDb.OleDbDataAdapter[/FONT]
[FONT=&quot] Dim MyConnection As System.Data.OleDb.OleDbConnection[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] MyConnection = New System.Data.OleDb.OleDbConnection( _[/FONT]
[FONT=&quot] "provider=Microsoft.Jet.OLEDB.4.0; " & _[/FONT]
[FONT=&quot] "data source=D:\Stock.XLS; " & _[/FONT]
[FONT=&quot] "Extended Properties=Excel 8.0;")[/FONT]
[FONT=&quot] ' Select the data from Sheet1 of the workbook.[/FONT]
[FONT=&quot] MyCommand = New System.Data.OleDb.OleDbDataAdapter( _[/FONT]
[FONT=&quot] "select * from [Sheet1$]", MyConnection)[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] DS = New System.Data.DataSet()[/FONT]
[FONT=&quot] MyCommand.Fill(DS)[/FONT]
[FONT=&quot] MyConnection.Close()[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Thanks[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Decklan[/FONT]

 

[DavidT_macktool]

Set the Datagrid.datasource

        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            filename = OpenFileDialog1.FileName
            If Not (filename Is Nothing) Then
                ExcelConnection = New System.Data.OleDb.OleDbConnection( _
                      "provider=Microsoft.Jet.OLEDB.4.0; " & _
                      "data source=" & filename & " ; " & _
                      "Extended Properties=Excel 8.0;")
                ExcelCommand = New System.Data.OleDb.OleDbDataAdapter( _
                      "select * from  [Sheet1$]", ExcelConnection)
                DSexcel = New System.Data.DataSet
                ExcelCommand.Fill(DSexcel)
                ExcelConnection.Close()
                DataGrid1.DataSource = DSexcel.Tables(0)
            End If
        End If

You need to set the grid's datasource to the dataset you create with the excel spreadsheet DataGrid1.DataSource = DSexcel.Tables(0).

The code above uses an Open File Dialog on a windows form to find a spreadsheet.

Hope that helps.

 

[DavidT_macktool]

What if there is no Sheet1...

By the way, I have never really got past the instance where there is no Sheet1 in the spreadsheet. Savy Excel users frequently rename the Sheet number tabs to something more meaningful eg. 'Inventory List". Has any one fugured out the code to get data from a different sheet name?

Thanks for reading...

 

[albertkhor]

        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            filename = OpenFileDialog1.FileName
            If Not (filename Is Nothing) Then
                ExcelConnection = New System.Data.OleDb.OleDbConnection( _
                      "provider=Microsoft.Jet.OLEDB.4.0; " & _
                      "data source=" & filename & " ; " & _
                      "Extended Properties=Excel 8.0;")
                ExcelCommand = New System.Data.OleDb.OleDbDataAdapter( _
                      "select * from  [Sheet1$]", ExcelConnection)
                DSexcel = New System.Data.DataSet
                ExcelCommand.Fill(DSexcel)
                ExcelConnection.Close()
                DataGrid1.DataSource = DSexcel.Tables(0)
            End If
        End If

You need to set the grid's datasource to the dataset you create with the excel spreadsheet DataGrid1.DataSource = DSexcel.Tables(0).

The code above uses an Open File Dialog on a windows form to find a spreadsheet.

Hope that helps.

i use your method in my system but when i import excel file below line code
ExcelCommand.Fill(DSexcel)

Message: An unhandled exception of type 'System.Data.OleDbException' occurred in system.data.dll

do you know what is the problem and how to fix it?

 

[albertkhor]

i use your method in my system but when i import excel file below line code
ExcelCommand.Fill(DSexcel)

Message: An unhandled exception of type 'System.Data.OleDbException' occurred in system.data.dll

do you know what is the problem and how to fix it?

i have solve the problem this is because data too long. Mean the system get all the blank field as well, to solve this problem i need to set the [Sheet1$] to [Sheet1$A1:Z10].

i just want to know why i cannot use [Sheet1$], even the problem solve but if my excel data more than Z10 i need to change my code again. Can someone tell me how to solve this problem?

 

[DDEW]

Excel to database grid

Try this example

 

[DavidT_macktool]

Strange behavior

I have had it pick up 1 blank column at the end of the spreadsheet before. All I did was delete the column in Excel and it worked. That doesn't really solve the cause of the problem, though. My first guess is a formatting issue, if the cells have a format associated with them they are read into your program. This could happen if you format rows (ie.. it goes way way across). This is just a guess....clear out the formatting of the unwanted columns and rows.

Keep us posted