vb.net expoerting datagrid values to excel

Question

0.00/5 (No votes)

See more:

i am trying to export values in datagrid control to excel .the problem is after debugging ,when i click the button,the application just gets stuck. and i cannot close the form either. i have to click the debug button. the excel file also is not created. please suggest solutions. thanks.

VB

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)    Handles Button1.Click
           Dim xlApp As Microsoft.Office.Interop.Excel.Application
           Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
           Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
           Dim misValue As Object = System.Reflection.Missing.Value
            Dim i As Integer
           Dim j As Integer

           xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass
           xlWorkBook = xlApp.Workbooks.Add(misValue)
           xlWorkSheet = CType(xlWorkBook.Worksheets.Item("sheet1"), Microsoft.Office.Interop.Excel.Worksheet)
           For i = 0 To DataGridView1.RowCount - 2
             For j = 0 To DataGridView1.ColumnCount - 1
               For k As Integer = 1 To DataGridView1.Columns.Count
                   xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                   xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
               Next
             Next
           Next



          xlWorkSheet.SaveAs("D:\vbexcel.xlsx")
          xlWorkBook.Close()
          xlApp.Quit()

          xlApp = Nothing
          xlWorkBook = Nothing
          xlWorkSheet = Nothing

         MsgBox("You can find the file D:\vbexcel.xlsx")

      End Sub

Posted 26-Jul-13 5:36am

hlsc1983

Updated 26-Jul-13 5:48am

v2

Add a Solution

Comments

woopsydoozy 26-Jul-13 11:44am

When you stop the debugger, where is the code? Are you in your loop? Populating the sheet this way is slow, so if you have a lot of data, it will take awhile.

hlsc1983 26-Jul-13 11:55am

sorry i did not get your question about the loop?please tell me the changes i need to make in order to make the code execute faster..

hlsc1983 26-Jul-13 11:57am

sorry i dont get your question about the loop...what changes do i need to make for the code to execute faster...???

woopsydoozy 26-Jul-13 12:06pm

A loop will work fine, but is slow. My question was whether your code is really stuck (likely some interop issue with Excel) or whether you're just not waiting long enough for it to complete. If it's in your loop, wait longer. If you want to speed up the processing, look into loading your sheet via an array; another question from today should help guide you: http://www.codeproject.com/Answers/626941/vb-net-copy-array-to-excel-spreadsheet

hlsc1983 26-Jul-13 12:16pm

you are right ... the code works but it is slow... how can i use an array?

ZurdoDev 26-Jul-13 12:07pm

You need to debug it and find out why it is hanging. As woopsy said, it may not be hanging but busy processing.

hlsc1983 26-Jul-13 12:17pm

yes you are right... it is just busy.. how to speed it up?

ZurdoDev 26-Jul-13 12:20pm

Do less data. Perhaps pull from the db instead of your datagrid. Then the UI does not need to be involved. The UI is usually what is the slowest.

hlsc1983 26-Jul-13 12:46pm

i need to use the datgrid beacause it is populated depending on a search operation performed by the user and result of the search is used to fill up the datagrid.,, of course it is not shown in the code but that is the idea,,,

ZurdoDev 26-Jul-13 12:55pm

You could still store off what the search filters were and then pull straight from the db.

hlsc1983 26-Jul-13 15:20pm

store off in the database? how can I do it?

ZurdoDev 26-Jul-13 15:25pm

Create a table in the db, when the user searches store the search criteria in the db. Then when you want to go to excel rerun the query and go directly to excel.

2 solutions

Solution 2

Check these..
http://www.nullskull.com/faq/1504/export-datagridview-data-in-excel-using-vbnet.aspx[^]
http://stackoverflow.com/questions/680199/how-to-export-datagridview-to-excel-using-vb-net[^]
Export datagridview to excel vb.net[^]

Posted 26-Jul-13 6:51am

ridoy

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

woopsydoozy · Accepted Answer · 2013-07-26T06:38:00

Solution 1

Since we've established that you're really just looking to speed up your processing, look here: http://support.microsoft.com/kb/306022[^]
Look for "Use Automation to transfer an array of data to a range on a worksheet" on that page--that gives you an example of populating a range with an array. Basically, you load your data into a multi-dimensional array, and then in one line of code load that array to your sheet--works MUCH faster.

Posted 26-Jul-13 6:38am

woopsydoozy

Comments

hlsc1983 26-Jul-13 15:18pm

thanks....
the way i understand is I need to fill the array first using the data grid values..so how do I achieve it? if u can kindly give some links or code

woopsydoozy 26-Jul-13 16:34pm

Think of the dimensions of your array as row/column for your grid, so something like ArrayName(grid.Row.Count - 1, grid.Columns.Count - 1). Then walk your grid with nested for loops, loading each item to the appropriate spot in the array, like: For r = 0 to grid.Rows.Count - 1 : For c = 0 to grid.Columns.Count - 1: ArrayName(r,c) = grid.Rows(r)(c): Next c: Next r

hlsc1983 27-Jul-13 15:15pm

thanks.. i still gett an error "Comexception was unhandled" associated with the line
-" xlWorkSheet.Range("A1").Resize(DataGridView1.Rows.Count, DataGridView1.Columns.Count).Value = myArray"

The code is--

Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim r As Integer
Dim c As Integer

xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = CType(xlWorkBook.Worksheets.Item("sheet1"), Microsoft.Office.Interop.Excel.Worksheet)

Dim myArray(DataGridView1.Rows.Count - 1, DataGridView1.Columns.Count - 1) As Object
For r = 0 To DataGridView1.Rows.Count - 1
For c = 0 To DataGridView1.Columns.Count - 1
myArray(r, c) = DataGridView1.Rows(r).Cells(c)
Next c
Next r

xlWorkSheet.Range("A1").Resize(DataGridView1.Rows.Count, DataGridView1.Columns.Count).Value = myArray
xlWorkSheet.SaveAs("D:\vbexcel.xlsx")
xlWorkBook.Close()
xlApp.Quit()

xlApp = Nothing
xlWorkBook = Nothing
xlWorkSheet = Nothing

MsgBox("You can find the file D:\vbexcel.xlsx")

woopsydoozy 29-Jul-13 13:11pm

That's some error coming from your Excel interop. It's probably this line, which attempts to put the cell, rather than the cell value, in the array: myArray(r, c) = DataGridView1.Rows(r).Cells(c). Instead, try myArray(r, c) = DataGridView1.Rows(r).Cells(c).Value. Also check to make sure you're populating all your array elements--I don't know that the interop deals well with Nothings.