Excel to DataGridView1 vb.net

This is my first part of the code where i select a file and put the full file path in a textbox

Dim openfiledialog1 As New OpenFileDialog
        openfiledialog1.ShowDialog()
        openfiledialog1.Filter = "allfiles|*.xls"
        file_name.Text = openfiledialog1.FileName

Select allOpen in new window

Now, i just need to be able to enter the excel file data into my DataGridView1.

Can i also make it start the transfer from row 5 of the excel row into the grid?

Now that you have the name selected by the user, you want to fill the DataGridView, isn't it?

For the maximum control, if you ever need to change a few things during the transfer, the best method is through Ole, where you launch Excel, open the selected file, grab the information from Excel, and store it into de de DataGridView. But that is quite involved, you have to fill the grid cell by cell.

So most will prefer to do it by treating Excel as a database, loading the sheet into a DataTable, and binding that DataTable to the grid. The code is a lot simpler that way. You have a good example here.

Good Friday JamesBurger.

Thanks again for your help.

I have tried the code but i have an issue with :

Using DBConnection As New OLEDBConnection

Select allOpen in new window

See the screen shot bellow of the error.

 OLEDBConnection, is that the right way?

Thanks again.

Just to let you know that i also tested that one and it work. But i can make it start at row 5 from the excel sheet for example:

 pintu(file_name.Text)
        Try
            con.Open()
            str = "select * from [Feuil1$]"
            com = New OleDbCommand(str, con)
            ds = New DataSet
            oledbda = New OleDbDataAdapter(com)
            oledbda.Fill(ds, "[Feuil1$]")
            con.Close()
            DataGridView1.DataSource = ds
            DataGridView1.DataMember = "[Feuil1$]"

Select allOpen in new window

And also, is takes a specific sheet. But if i change the sheet name, it will not work.

Is there a way to select by default the sheet selected?

Thanks again


oledb-issue.jpg

What the message says, in clearer words, is that the class OLEDBConnection can be found in 2 different namespaces.

You probably have set an Imports instruction, either in the project's properties References tab, or at the top of the file.  Imports are there make your code smaller, preventing you from having to type the namespace every time you need a class.
But when there are too many Imports, you can end up with conflicts of that type.

The solution is to tell the system which of the 2 namespaces you want to use. In this case, I do not think it would make a big difference, but I would chose System.Data.OldBd since it is a .NET namespace, and not something that comes through Office. So:

Using DBConnection As New System.Data.OleDB.OLEDBConnection.

----

I was not aware that the user was working in the Excel sheet at the same time you wanted to work with it in code. That brings a lot of new problems, and will force alternative ways to do the stuff that are not as easy to implement.

You cannot use the above method with the selected sheet, because the database connection does not see the opened copy of Excel. It goes straight at the file, so it has no way to know which sheet is active. That means, "entre autres", that if the user has made changes in the page and has not saved them, your .NET code won't see them.

That also means, if I understand correctly, that the user has to start Excel on one hand, and your application on another one? Not a very good way of working.
-----

Here is what I see as the simplest alternative, in its most basic form.

Start Excel and load the .xls from your application. This way, your application has a better idea of what is happening in the copy of Excel that the user is working with. It could for instance, know which sheet is active and save the file before calling the database access. Button1 starts Excel, Button2 fills the grid.

Public Class Form1

	Dim app As New Microsoft.Office.Interop.Excel.Application	'This starts Excel

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

		'Here : The code for your OpenFileDialog
		app.Workbooks.Open(openfiledialog1.FileName)
		app.Visible = True	'Excel starts invisible when started through code

	End Sub

	Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click

		Dim sheet As Microsoft.Office.Interop.Excel.Worksheet
		sheet = CType(app.Workbooks(1).ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)
		'Here: The code to fill the grid, with sheet.Name giving you the name of the currently active sheet

	End Sub

	Private Sub Form1_FormClosed(sender As Object, e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed

		'***** Very important *****
		'To make sure that Excel is closed
		'Even if the user has closed it, you are holding a reference, so Excel did not really closed
		'It disappears from the list of applications, but if you go in the Windows Task List, it stills run as a Process
		'You need to close it on your side to let go of the reference

		app.Quit()

	End Sub

End Class

Select allOpen in new window

-----

One question however.

If the user is working in Excel, what is the reason for displaying the information in a data grid? The DataGridView has very limited editing capabilities by default. You will have a lot of work to simulate in the data grid some of the most basic functions that are readily available in Excel.

Hello,

Q: I was not aware that the user was working in the Excel sheet at the same time you wanted to work with it in code. That brings a lot of new problems, and will force alternative ways to do the stuff that are not as easy to implement.

A: Well actually, no users will work in the sheet at the same time has me. Once they send it to me, i have the final excel copy.

Q:That also means, if I understand correctly, that the user has to start Excel on one hand, and your application on another one? Not a very good way of working.

A: Actually, that is something that i did not like in the code i had. I wouls like better to just import the excel file in the grid without openning the excel file.

For the button one, it's working like this:

  Dim openfiledialog1 As New OpenFileDialog
        openfiledialog1.ShowDialog()
        'Here : The code for your OpenFileDialog
        app.Workbooks.Open(OpenFileDialog1.FileName)
        app.Visible = True  'Excel starts invisible when started through code

Select allOpen in new window

But the problem is with button 2:

 Dim sheet As Microsoft.Office.Interop.Excel.Worksheet
        sheet = CType(app.Workbooks(1).ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)
        'Here: The code to fill the grid, with sheet.Name giving you the name of the currently active sheet

Select allOpen in new window

It does not put the data in the grid.

I think something is missing when i tell to put the information in the DataGridView1.

For the last question

Q:If the user is working in Excel, what is the reason for displaying the information in a data grid? The DataGridView has very limited editing capabilities by default. You will have a lot of work to simulate in the data grid some of the most basic functions that are readily available in Excel.

A:The excel file i sent by email to me. But since this is the first modual of my app. cause there will be arround 13 diffrent, but they will all be imported in this program but with diffrend criteria, i wanted to work on 1 program instead of multiple macro excel file.

If i give you more information, after i import the data in the grid, i will be able to fix some data column with a button and also create that last function where it will transfer the grid data into a .text file.

Each column will be placed in a specific position in the dat file.

I understood that the user had the sheet opened when you said. Is there a way to select by default the sheet selected

When a sheet is selected, it is usually because a user has selected that sheet in Excel. Wrong choice of word. I do it all the time :-).

I understand that it won't be the case, you will receive the sheets through email.

But what do you mean by the "sheet selected".

In order to go straight to the document, without opening it and without any intervention from a user, only through your code, you need to have a convention on how to name the sheet that contains the data. You will then be able to fill the grid with the OleDB routine simply by using that name instead of Feuil1$ in str = "select * from [Feuil1$]".

By the way, I had not seen that previously, and by searching I see that it comes from the example in the page I sent you to, but in French, the name of the sheet is Feuil1, without the $ at the end. I think that the $ in the original was a typo by the guy who wrote the code. That might be the reason why the code for the OleDb does not work.

That code was working.

pintu(file_name.Text)
        Try
            con.Open()
            str = "select * from [Feuil1$]"
            com = New OleDbCommand(str, con)
            ds = New DataSet
            oledbda = New OleDbDataAdapter(com)
            oledbda.Fill(ds, "[Feuil1$]")
            con.Close()
            DataGridView1.DataSource = ds
            DataGridView1.DataMember = "[Feuil1$]"

Select allOpen in new window

So this code is good to use then. Right?

If you tell me that the code was working, then good. But did I catch it right, was it working? Variable con is not defined in what I see. Is it defined somewhere else? If not, do something like this before con.Open.

Dim con As New OleDBConnection (<Insert the string that you had in the piece of code you sent with an error on the namespace>)

Although it should work, as a pro, I would change a few things so that the code is cleaner. This code makes a common mistake of creating a DataSet (a container for tables) when there is only one table, you sheet. I would thus change it to work with a DataTable instead of working with a DataSet

Try
            con.Open()
            str = "select * from [Feuil1$]"
            com = New OleDbCommand(str, con)
            'Replace this: ds = New DataSet
            ds = New DataTable
            oledbda = New OleDbDataAdapter(com)
            oledbda.Fill(ds, "[Feuil1$]")
            con.Close()
            DataGridView1.DataSource = ds
            'This is not needed anymore: DataGridView1.DataMember = "[Feuil1$]"

You can also rename ds by dt so that things are clearer.

It look's like it does not want to take the DataTable. Only the DataSet.

It says that DataTable is a type and cannot be used as an expression.

Try
        con.Open()
        str = "select * from [Feuil1$]"
        com = New OleDbCommand(str, con)
        ds = DataTable
        oledbda = New OleDbDataAdapter(com)
        oledbda.Fill(ds, "[Feuil1$]")
        con.Close()
        DataGridView1.DataSource = ds
        DataGridView1.DataMember = "[Feuil1$]"

Select allOpen in new window

You forgot the New in ds = New DataTable.

Oh sorry

I did put the New but i was getting the error also.

I was saying New cannot be used on an interface.

Merci beaucoup for the encouragement. :-)

Now it's working great.

And also thanks again for your time. Always appreciated.