gregw1906

Here is the code I'm tring to use:

Dim MyConnection As System.Data.OleDb.OleDbConnection

Dim SheetAdapter As System.Data.OleDb.OleDbDataAdapter

MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & _

"data source='" & "C:\Documents and Settings\gregw\My Documents\Upload Test.xls" & " '; " & "Extended Properties=Excel 8.0;")

' Select the data from Sheet1 of the workbook.

SheetAdapter = New System.Data.OleDb.OleDbDataAdapter("select * from [Purchases$]", MyConnection)

Dim SheetData As New DataTable

MyConnection.Open()

SheetAdapter.Fill(SheetData)

MyConnection.Close()

MsgBox(SheetData.Rows.Item(0).Item(0).ToString)

Me.ReceiptsBindingSource.DataSource = SheetData

Me.ReceiptsDataGridView.DataSource = Me.ReceiptsBindingSource

I've tried me.receiptsdatagridview.datasource = sheetdata

and just about everyother combination I could find in forums and/or the internet in general. Either I get one empty row or all the rows equal to the number excel rows with empty cells. what am I doing wrong.

I've tried just about every combination of code from this forum and the internet in general. What I end up with in my datagridview is the correct number of rows, but all of the cells are empty.

My question is why are the cells empty, but the rows have been imported without any errors

What do I need to do to get my data to show

Now can retrieve the data to an unbound datagridview, but I have load this data into an SQL table.

bindingsource.endedit

tableadapter.fill(dataset.table)

It seems there should be a very simple way to read an excel file into a datagridview. Make whatever changes that need to be made then upload that set of data to a sql table, but I'm just unable to find it.

Does anyone know of a way to:

1. import an excel spreadsheet to a bound datagridview or

2. import an excel spreadsheet to a unbound datagirdview and then import that set to an sql table

Any help would be appreciated.




Re: Visual Basic Express Edition Import Excel spreadsheet into bound datagridview

Glatkoi

I have a similar problem, but I am able to import some of the data.
The spreadsheet I am importing has columns that are sparsely filled (first 15 rows blank, then data then 20 rows blank).
The column name is being imported, but the data is not if the first value occurs after the 9th row.
Should data exist in the first 9 rows of that specific column, then all remaining rows in that column are populated correctly.

Is there any way to change the default behavior so more rows are searched before it gives up on importing any data into the column





Re: Visual Basic Express Edition Import Excel spreadsheet into bound datagridview

js06

With the help of Feng Chen i just got this code to populate a datagridview with excel data.

I'm still working on the save though.

Public Class Form1

Dim connect As System.Data.OleDb.OleDbConnection

Dim adapter As System.Data.OleDb.OleDbDataAdapter

Dim dataset As New System.Data.DataSet()

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim opendlg As New OpenFileDialog

opendlg.Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"

If opendlg.ShowDialog() = Windows.Forms.DialogResult.OK Then

Dim pathname As String = opendlg.FileName

'MsgBox(pathname)

connect = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _

"data source=" & pathname & ";Extended Properties=Excel 8.0;")

'C:\testoledb.xls

adapter = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", connect)

connect.Open()

adapter.Fill(DataSet)

Me.Table1DataGridView.DataSource = DataSet.Tables(0)

adapter.Fill(DataSet.Tables(0))

connect.Close()

MsgBox(CType(DataSet.Tables(0).Rows(0).Item(0), Object).ToString)

End If

End Sub






Re: Visual Basic Express Edition Import Excel spreadsheet into bound datagridview

gregw1906

You are a Godsend. It looks like the key is that datagridview must be filled while the connection is open. I was always closing the connection once I got the data and then setting the data to the datagridview.

Also I noticed that if the header row is not the same name as the datagridview column name it will not import the data. This is an added benefit and a detractor. Benefit because the user's spreadsheet does not have to follow the same column order as the datagridview. Detractor, that I cannot always ensure that they name the columns correctly, but I can work around that. In my documentation, if they see missing information, they can go back and change the columnname in the spreadsheet to match and re-upload.

My database contains two more columns that the spreadsheet will not have which is are a companyID and an upload date. I have set the default value of the upload date to GETDATE() in the SQL Database, so hopefully, that should be ok. the CompanyID I will have a combobox to choose the company and use a for each/next to populate the datagridview column with the id. Hopefully that will work. I think I can work my way through it.

My next hurdle is getting data into the database. I'm sure if a simple:

me.originalbindingsource.endedit()

me.originaltableadapter.fill(dataset.table) will work.

what I'm looking at testing is swapping back the datasource

me.originalbindingsource.datasource = new datasource

me.originaltableadapter.fill(dataset.originaltable)

I will update if that works. If someone already knows that it will not work. Please respond.






Re: Visual Basic Express Edition Import Excel spreadsheet into bound datagridview

js06

I found an issue with the code above. Aparently it is somewhat difficult to import the data and then update the database with it.

I am going to work on some automation code that will maybe make this easier.

Just to give you an idea of how it will work incase you want to give it a try

Here is the basic process:

you have a datagridview and a details view (textboxes) on a form

open the excel file (there are examples to do this here if you search)

once the file is open you can use navigation code that will loop through the row (one cell at a time)

and put the data from each cell into the corresponding textbox (the boxes are bound to the grid so they will update it)

use code to navigate to the next row in the datagridview

move to the next row of cells in excel.

The key to doing this with the excel rows is to use the offset method

Let's say that you start with cell A1

you will use the select method to activate that cell range("A1").select

you then tell it that textbox1.text = activecell.value

then you call offset to move to the next cell in the row

then textbox2.text = activecell.value

you continue this on untill you reach the end of your row then you can call the offset to jump back to the beginning of the row

and then offset down 1 to the next row.

Now you can add a new record to the datagridview and repeat the process

You can wrap this entire code in a do loop

The do loop will be something simple like do until activecell.value = "" a blank cell at the end of your rows in excel

Here is some sample code to get you started

Dim oExcel As Excel.Application

Dim oBook As Excel.Workbook

Dim oSheet As Excel.Worksheet

oExcel = CreateObject("Excel.Application")

oBook = oExcel.Workbooks.open("c:\Book1.xls", [ReadOnly]:=False)

oExcel.Visible = True

oExcel.DisplayAlerts = False '//So It doesnt prompt

oSheet = oBook.Worksheets(1) '.Worksheets("Sheet1") ¡®.worksheets(¡°sheetname¡±)

oSheet.select()

oSheet.Range("A1").Value = "Test worked"

'oSheet.delete()

oBook.SaveAs("C:\Book1.xls")

as you can see the reference that will let you navigate the cells is osheet

offset example: osheet.activecell.offet(1, 0).select

the (1, 0) is the row and column reference ( i always forget which one is row and which is column)

this is not a specific cell reference this is a direction reference (hence the word offset) (move)

you can use postive numbers to move right and down

and negative numbers to move left and up

You can increase the offset by any number

let's say you have 6 columns and you want to offset to the last column you would use (6, 0) or (0, 6)

here is some sample code to add a new row of data

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

Me.BindingContext(DataSet1, DataSet1.Customers.TableName).AddNew()

End Sub

"TableName" Do not change this "customers" is the name of your table






Re: Visual Basic Express Edition Import Excel spreadsheet into bound datagridview

gregw1906

My issue now is not doing any manipulation to the spreadsheet. Once I get it uploaded, I'm done with it. My issue is getting the new data out of the datagridview and into my sql database.

The code you suggested earlier worked great. I'm able to the see the data in the dgv instead of blank rows. I guess this is what you mean by "Aparently it is somewhat difficult to import the data and then update the database with it."

This is what I've tested.

My dgv is bound to the sql server table. If I fill in a row by manually entering it and then run a button click event with

dgv.bindingsource.endedit()

dgv.tableadapter.update(dataset.table)

when I open managment studio I can see the data I just entered.

However, when I upload the spreadsheet to the same dgv and run the click event. The data is not there.

I suspect because the datasource has changed.

What I would like to do transfer the data back to the original bindingsource in something like.

me.originalbindingsource.datasource = dgv.datasource

me.bindingsource.endedit()

me.tableadapter.undate(dataset.table)

But this doesn't work. I can't see the data from management studio.






Re: Visual Basic Express Edition Import Excel spreadsheet into bound datagridview

js06

Ok,

i haven't found the part yet to add the data into the database yet

this is why i suggested the automation process

when you have the textboxes that are bound, when you add it manually it works

the idea would be the same for the automation except instead of you manually putting in the data into the textbox you would do it with code one row or cell at a time.

The automation would be what you would use instead of the oledb connection

if you try the automation you need to leave the oledb code out and use only the automation code

this will do basically the same thing just in a different way

I do have an update on the code to populate the gridview properly though

i'm working on the save part

Public Class Form1

Dim connect As System.Data.OleDb.OleDbConnection

Dim adapter As System.Data.OleDb.OleDbDataAdapter

Dim dataset As New System.Data.DataSet()

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim opendlg As New OpenFileDialog

opendlg.Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"

If opendlg.ShowDialog() = Windows.Forms.DialogResult.OK Then

Dim pathname As String = opendlg.FileName

'MsgBox(pathname)

connect = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _

"data source=" & pathname & ";Extended Properties=Excel 8.0;")

'C:\testoledb.xls

adapter = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", connect)

connect.Open()

adapter.Fill(dataset)

adapter.Fill(Me.Database1DataSet.Table1)

connect.Close()

'MsgBox(CType(DataSet.Tables(0).Rows(0).Item(0), Object).ToString)

End If

End Sub






Re: Visual Basic Express Edition Import Excel spreadsheet into bound datagridview

gregw1906

Well regardless of the using automation or the Oledb. What I was tyring to avoid was iterating through the datagridveiw one row/cell at a time. I can do this through an insert on the original tableadapter. This is what I find so incomplete with vb.net or I'm just too new to understand. The spreadsheet has the exact schema (or should have the exact same schema as the original dataset datatable that exists in my sql database. I guess what I'm really looking for is:

tableadpater.update(me.dataset1.table1 with dataset1.table2) or more exactly.

originaltableadapter.update(me.dataset1.table1 with datagridview.rows)

Unfortunately, it doesn't exist. I have to iterate through each row of datagridview to update the original sql table.

this should be something like:

For Each dgvr As DataGridViewRow In Me.ReceiptsDataGridView.Rows

For Each dgvc As DataGridViewColumn In Me.ReceiptsDataGridView.Columns

Me.ReceiptsTableAdapter.Insert(dgvr.Cells(dgvc.Name).Value) 'This would be really slick but probably won't work, but i'm going to try it anyway.

Next

me.ReceiptsDataGridView.insert(dgvr.Cells(0).Value,dgvr.Cells(1).Value.....)

Next

maybe I can define an array iterate through each column of a row the array and then

For Each dgvr As DataGridViewRow In Me.ReceiptsDataGridView.Rows

me.ReceiptsDataGridView.insert(the array)

Next

or something like I know this one works. This is what I was trying to not to do.

For Each dgvr As DataGridViewRow In Me.ReceiptsDataGridView.Rows

me.ReceiptsDataGridView.insert(dgvr.Cells(0).Value,...dgvr.Cells(n).Value)

Next






Re: Visual Basic Express Edition Import Excel spreadsheet into bound datagridview