Solved

Excel to DataGridView1 vb.net

Posted on 2011-09-22
14
341 Views
Last Modified: 2012-05-12
Hello all,

I would like to know if i can export from Excel to DataGridView1 using an Open file dialog (Mapi)?

I'm searching on google but bi see lots of DataGridView1 to excel (Oleb) but not Excel to DataGridView1.

Thanks again for your help.
0
Comment
Question by:Wilder1626
  • 8
  • 6
14 Comments
 
LVL 11

Author Comment

by:Wilder1626
ID: 36584265
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

Open 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?
0
 
LVL 40
ID: 36587417
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.

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 36587945
Good Friday JamesBurger.

Thanks again for your help.

I have tried the code but i have an issue with :
Using DBConnection As New OLEDBConnection

Open 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$]"

Open 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
0
 
LVL 40
ID: 36588282
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

Open 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.

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 36589429
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

Open 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

Open 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.



0
 
LVL 11

Author Comment

by:Wilder1626
ID: 36589525
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.
0
 
LVL 40
ID: 36589778
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.

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 11

Author Comment

by:Wilder1626
ID: 36589803
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$]"

Open in new window


So this code is good to use then. Right?

0
 
LVL 40
ID: 36589877
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.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 36589988
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$]"

Open in new window

0
 
LVL 40
ID: 36590031
You forgot the New in ds = New DataTable.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 36590049
Oh sorry

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

I was saying New cannot be used on an interface.
0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
ID: 36590205
Probably the same problem as sooner with too many imports. DataTable probably exists in more than one namespace, but the interface takes precedence over the class, so you do not get the same message. You have that bad luck of falling on series of little things one after the other.

Try with New System.Data.DataTable

I have to go, I won't be back till the end of the evening.

A little word of encouragement:

Faut pas lâcher, on va sûrement y arriver. Ce sont tous des petits problèmes qu'on finit par régler par réflexe. Faut dire que .NET et Office sont deux mondes différents, ce qui rend la communication entre les deux difficiles. Office n'a pas été conçu pour .NET. Quand on aura un Office.NET, si un jour ça vient, tout sera beaucoup plus simple. Une feuille Excel est une collection, et toutes les collections .NET peuvent entrer dans la grille par le DataSource. Tu pourrais donc mettre directement une feuille .NET dans le DataSource, sans avoir à passer par des intermédiaires pour créer des trucs que .NET reconnaît nativement.
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 36593285
Merci beaucoup for the encouragement. :-)

Now it's working great.

And also thanks again for your time. Always appreciated.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now