Question Data Grid View results to. . . excel or

[Styles]

All,

I tried searching both these Forums and Google and have not been able to find an answer to this question. If I missed something in the forums I apologize. I have a database loaded into Visual Studio 2008 (VB.NET). My Form has a datagrid listbox of table "A" and the 2nd is a datagrid view of the results of table "A" and table "B" by city. To explain that better the first table posted to the form in a listbox has cities and town codes. The 2nd table is based on company names, city, phone number and a number order. The number order I included to sort the companies into an order after it is organized by city. The results of the 2nd datagridview show the companies in each town after you click on the listbox. Is there a way to export these individual results by town to Excel or Word? I have tables already setup for each. Or can I create a Cystal Report? Companies are constantly taken on or off the list. The output needs to just take the company name and phone numbers and put them into column a and the rest of the form is hand written. Any help or direction would be appreciated thanks.

 

[Budius]

I've done a bunch of DB -> Excel this past month and couldn't find any automatic way to do it (or if is what you were looking for)
You have to use a FOR go through the grid or through the datatable(s) and write one by one.

I'm sure that if you google or check here in the forums, there are tons of results on how to interact with Excel from vb.net.
here you can see some examples: http://www.vbdotnetforums.com/third-party-products/44497-reading-data-excel-file.html

 

[Styles]

Budius,

Thank you for the quick reply. I should have added some more content before posting as it would of explained better. I don't care if it is Excel, Word or 3rd party program as long as I can reach my desired result. The 2 tables I have on my form are the "Active" or current vendors we use for a given city. The city name is the primary key for the city table, and is a foreign key for the vendors table. If I check the yes/no box in the main database then it will not be in the results of our current vendor form. This changes a great deal as vendors licenses are not valid or are on suspension. I just need to be able to export to a printable form. Say there are 6 active vendors in Mooseneck based on my data grid I would need to be able to take those 6 vendors and put their name, phone #'s into a table based on the city order column to the first column so I can print it. There are 7 other columns in the table with a title for each but need to be blank so it can be hand written on what date, time etc we called them for service.

 

[Budius]

and I re-confirm my answer, although not the only way, the way I know how to do is through Excel, therefore is the example I provided you, and what data you're exporting makes no difference.

the 2nd post on the link I gave you http://www.vbdotnetforums.com/third-party-products/44497-reading-data-excel-file.html was written by me, it shows how to create and initiate the excel, workbook and worksheets objects, and how to write in any cell you need, and finalise the object, from there you just have to write a "For each row" and write the data you need.
If you read JohnHs post on that topic, he points out how using early binding will make your programming much easier.

In case you don't know how to write this "For each row" I suggest you to check this topic http://www.vbdotnetforums.com/ado-net/44272-what-correct-way-iterate-through-rows-datatable.html which have a nice accurate and lengthy discussion on how to do it and what is the best ways to do it.

 

[Styles]

Thanks for the quick response. I read the links you suggested and have come up with the following as an example of what I think I gathered from them. In case you can't tell I am new to all this and am doing this to make my job easier and hey with layoffs seeable through the haze it can't hurt (yea I know reality is they don't care).


Ok so hopefully you can see the attachment as well. I know there are a few kinks but am I at least headed in the right direction? Also since the datagridview changes based on the datagridlist the user selects will it still change the output correctly?

 

[Budius]

no probs, and sorry for not replying earlier, I was out for the weekend.
If you want to post code use the tag [ CODE ] ** your code here ** [/ CODE] but without the spaces, the forum system will format it correctly, just like you see on this post.

Now the actual problem:
- the correct way to get data from a DataGridView is:

TableName.Rows(rowNumber).Cells(ColumnNumber).ToString

- as view on the other topic I sent you before, this kind of notation

        For Each MyRow As DataGridViewRow In DG.Rows
            For Each MyCell As DataGridCell In MyRow.Cells
               ' Process your data using the following
                _something_ = MyCell.ToString()
            Next
        Next

is much better as you don't have to count the number or rows.

- the excel initialisation is quite different, you need 3 objects: 1) the Excel application; 2) the Excel workbook; 3) the Excel worksheets

        Dim oExcel As New Microsoft.Office.Interop.Excel.Application
        Dim oWB As New Microsoft.Office.Interop.Excel.Workbook
        Dim oWS As New Microsoft.Office.Interop.Excel.Worksheet
        oWB = oExcel.Workbooks.Add()
        oWS = oWB.ActiveSheet

See how the oWB is set as a new Workbook in the app and the oWS is the active sheet in the book? ; )
And do not forget to CLOSE the excel after or else you will be doing a big mess on the user computer.

to write onto the Excel cells you have to use:

oWS.Cells(RowNumber, ColNumber) = value

you comment about the "next row would have to be in row 4, 5, 6 etc"
if you're using "FOR EACH" you can get the row number from

MyRow.Index + 1

the +1 leaves space for a title that you'll like want to put on the Excel sheet
And if you use "FOR i_Row " you should use this i_Row to index the Excel rows.

Hope it helps...

 

[Styles]

Budius,

Thank you again for the quick response. It was the weekend and I honestly didn't expect a response (we all have to enjoy what little free time we get). I will copy this into my notes and give it a try. Hopefully I will have no more questions and can resolve this. Again thanks!

 

[Styles]

Budius/All,

I used the last response as a guide to try and complete my project here. Well I am definitely not a VB.Net genius and was not able to use it. I kept getting build errors. So I looked on the forum again back at the suggested links as well as online and came up with this code instead. I get a lot farther in the process but still am not at my goal. Slowly sinking can anyone offer me a life vest? Here is what I have so far is this good, bad ugly?

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim oExcel As New Microsoft.Office.Interop.Excel.Application
oExcel.Workbooks.Open("C:\Test Excel.xlsx")
Dim count As Integer = 9
For Each MyRow As DataGridViewRow In Light_Duty_by_CityDataGridView.Rows
    For Each MyCell As DataGridViewCell In MyRow.Cells
        oExcel.ActiveSheet.Cells(count, 1).Value = MyCell.ToString
        count = count + 1
    Next
 Next
End Sub
End Class

 

[Styles]

Sorry I forgot to add that I got rid of the error with the +1 and 4,5,6 count in the previous post. I edited the datagrid to put the results in order, and also hid the columns I didn't need. So now I only have showing the name of company and their main and alternate phone number. This changes as you click on the listbox datagrid. Once I click on the town I want and it displays the results in the datagridview I click on "button1" and it opens the excel file and loads the results.

 

[Styles]

I have figured out what I was doing wrong and have working code now.

Private Sub Load_Vendors()
        Dim oExcel As New Microsoft.Office.Interop.Excel.Application
        oExcel.Workbooks.Open("C:\Test Excel.xlsx")
        oExcel.Worksheets("Sheet1").activate()
        oExcel.Visible = True
        Dim count As Integer = 9
        Dim i As Integer
        Dim j As Integer
        Dim misValue As Object = System.Reflection.Missing.Value
        oExcel.Cells(6, 2) = CityListBox.SelectedValue
        oExcel.Cells(6, 9) = Date.Now
        'do while condition works in simple form but doesn't not work for each invididual city
        'Do While count < 38
        For i = 0 To (CityDataGridView.RowCount - 2)
            For j = 0 To (CityDataGridView.ColumnCount - 1)
                If count = 39 Then count = 9 And oExcel.Worksheets("Sheet2").activate()
                oExcel.Cells(count, 1) = CityDataGridView(j, i).Value.ToString
                count = count + 1
            Next
        Next
    End Sub

My new problem now is how to repeat the 2 for loops if necessary? For example in one city we only have 3 vendors so I would need to repeat the loop 2 more times for a total of 3 to fill a full sheet of paper. Now another city has 14 vendors so that is why I added the count with second sheet to fit them on the excel template. I have tried using an IF/Then statement and a Do and Do/While statement but have not been able to achieve my results. I know I can use the CityListBox.SelectedValue as part of my code. Any help would be appreciated.

 

[Budius]

hi,

1st of all, instead of using the 'count' you can use:

LastRow = oExcel.Worksheets("Sheet1").UsedRange.Rows.Count + 1

I'm not sure I understood your question, cause I don't know your database, but that's something that you should be able to crack it up yourself with another FOR_NEXT loop.
something like:

Dim sheet as integer
Dim j as integer
Dim i as integer
For sheet = 1 to NumberOfVendors
     oExcel.Worksheets.Add()         ' << Add a new worksheet to make sure you have them
     oExcel.Worksheets("Sheet" & sheet.ToString).activate()   ' << activate it

     For i = 0 To (CityDataGridView.RowCount - 2)
          For j = 0 To (CityDataGridView.ColumnCount - 1)
               oExcel.Cells(LastRow, 1) = CityDataGridView(j, i).Value.ToString  ' fill it up
          Next
     Next
Next

All you have to do is crack it up what NumberOfVendors is.
and from my previous reply, try using

        For Each MyRow As DataGridViewRow In DG.Rows
            For Each MyCell As DataGridCell In MyRow.Cells
                oExcel.Cells(MyRow.Index + 2, MyCell .Index + 4) = CityDataGridView(MyRow.Index, MyCell .Index).Value.ToString  ' fill it up
            Next
        Next

 

[Styles]

First let me apologize for not being more specific (though it was 4am ish and wasn't thinking clearly). My database consists of different vendors we use for towing services. We have to use approved vendors based on the town the incident occurs in. For example in Bridgeport I have 14 vendors, and in Branford only 3. My Excel spreadsheet has a header and then 9 columns in it. The first column shows the vendor name and their 2 phone numbers. The other 8 columns are left blank for the person calling to fill out. The code so far loads all the information I need onto the spreadsheet. My issue is to save paper and get the 3 vendors for Branford to repeat 2 more times so that I can fill the sheet of paper. Then for Bridgeport the "count" code was added since there are more vendors than would fit on a single page, the 38 row is the last printable row on the sheet. So I thought it would be easier to create another sheet with the template so that it could just add those in as well. I would assume that I would need a separate statement for each vendor selected from the datagridlistview, since each town has different amounts and would either not need to be repeated as in Bridgeport or repeated several times. I tried something similar to your last post but still couldn't achieve my end result. I can iron out the details just a little "nudge" in the right direction would be appreciated.

 

[Budius]

hhhmmmmmmm.....

I would do it the other way around, try to fiddle the Excel template layout in a way that pages can break automatically, check the excel options on page configurations, etc...
that way all you'd had to do is fill the data one after the other on excel.

could you post a print screen of the DataGridview so I can see how those vendors are filled in the form ?

 

[Styles]

Here is a screen shot of both the datagridview as well as the excel template.



The way I have it set up as I stated is dependent on which town you click in the listview it will export those results to the excel sheet. The database and datasource itself have several other columns that I need for other things, but do not need for this spreadsheet. The datagridview is also sorted by a hidden column called “city order”. I used this column so that I can keep the order that we currently have so no one gets skipped.


This is the excel sheet after the information is exported from VB.NET. In this example based on font and column height I can fit the 3 vendors in order 2 more times to fill up a full page. Hope the images help I tried to shrink them down as much as possible but still be readable. Thanks again!

 

[Budius]

oh om... got it....
it's just a matter to use the 2 loops 'walk through' the datagridview in the order you want it to appear on Excel, and then make Excel go one after the other.
Shouldn't be difficult.... but sometimes an outside view always help.

I think that is the best way, and just fiddle with the template to change page and repeat header at all the pages....

        For Each MyRow As DataGridViewRow In DG.Rows
            For Each MyCell As DataGridCell In MyRow.Cells

                oExcel.Cells((MyRow.Index*3) + MyCell.Index + 5, 1) = CityDataGridView(MyRow.Index, MyCell .Index).Value.ToString  ' fill it up
                     ' (MyRow.Index*3) <<< so for each row the loop will jump another 3 lines on Excel
                     ' + MyCell.Index  <<< for each column, it will put one after the other
                     ' + 5             <<< jump an extra amount for your headers (I'm not sure if it's 5, change it to your hearts content)
            Next
        Next

but if you insist using i and j (which I don't recommend) you can easily do the same with:

oExcel.Cells((i*3) + j + 5, 1) = CityDataGridView(i, j).Value.ToString  ' fill it up

or if you still want to carry on using the count

oExcel.Cells(count + 5, 1) = CityDataGridView(i, j).Value.ToString  ' fill it up
count = count + 1
if count = 39 then count = 0

happy coding.