Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

export only visible columns in datagridview to excel

Posted on 2009-02-14
17
Medium Priority
?
4,149 Views
Last Modified: 2013-11-26
hi!

i have a datagrdiview which can go from 3 to 5 columns depending on what checkbox the user checked to populate it with. the first 2 columns are always there, but from 3 to 5 it depends. i am trying to export from the datagridview in which i have set columns invisible if user did not check the checkbox. this is the datagridview to export to excel:


how can i only export the visible columns from the datagridview?

thanks a lot!

Tammy
'verify if the datagridview has data or not
        If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
            Exit Sub
        End If
 
        'Create dataset to export
        Dim dset As New DataSet
        ' add table to dataset
        dset.Tables.Add()
        'add column to that table
        For i As Integer = 0 To DataGridView1.ColumnCount - 1
            If DataGridView1.Columns(i).Visible Then
                dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
            End If
        Next
 
        'add rows to the table
        Dim dr1 As DataRow
        For i As Integer = 0 To DataGridView1.RowCount - 1
            dr1 = dset.Tables(0).NewRow
            For j As Integer = 0 To DataGridView1.Columns.Count - 1
                dr1(j) = DataGridView1.Rows(i).Cells(j).Value
            Next
            dset.Tables(0).Rows.Add(dr1)
        Next
 
 
 
        Dim xlapp As New Excel.Application
        Dim xlbooks As Excel.Workbooks = xlapp.Workbooks
        Dim xlbook As Excel.Workbook = xlapp.Workbooks.Add(5)
 
 
 
        xlapp.Visible = False
 
 
        Dim xlsheet As Excel.Worksheet = xlapp.ActiveSheet
 
        Dim dt As System.Data.DataTable = dset.Tables(0)
        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 8
 
        For Each dc In dt.Columns
            colIndex = colIndex + 1
            xlapp.Cells(8, colIndex) = dc.ColumnName
        Next
 
        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                xlapp.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
 
            Next
        Next
 
 
 
 
 
 
        xlsheet.Rows(8).Font.Bold = True ' <------ bold for titles

Open in new window

0
Comment
Question by:jtammyg
  • 8
  • 8
17 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 23645157
On the surface, I would think that you need to check to see if the column's Visible property is True, and set the values from those columns in a DataRow.
0
 

Author Comment

by:jtammyg
ID: 23646691
can u show me in what part of the code to do that please?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 23647925
Change this section of the code
        For Each dc In dt.Columns
            colIndex = colIndex + 1
            xlapp.Cells(8, colIndex) = dc.ColumnName
        Next
 
        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                xlapp.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
 
            Next
        Next
to the one shown below
        For i as integer=0 to datagridview1.Columns.count - 1
            if datagridview1.columns(i).visible = true then
               colIndex = colIndex + 1
               xlapp.Cells(8, colIndex) = datagridview1.columns(i).name
            End if
        Next
 
        For j as integer=0 to dt.Rows.count - 1
            rowIndex = rowIndex + 1
            colIndex = 0
            For k as integer = 0 to dt.Columns.count - 1
                if datagridview1.columns(k).visible = true then
                   colIndex = colIndex + 1
                   xlapp.Cells(rowIndex + 1, colIndex) = dr(k)
                end if
 
            Next
        Next

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:jtammyg
ID: 23649747
codecruiser:

i am getting the following error message  in this line

dr1(j) = DataGridView1.Rows(i).Cells(j).Value

when using your code:

System.IndexOutOfRangeException was unhandled
  Message="Cannot find column 3."
  Source="System.Data"
  StackTrace:
       at System.Data.DataColumnCollection.get_Item(Int32 index)
       at System.Data.DataRow.set_Item(Int32 columnIndex, Object value)
       at WindowsApplication1.Form1.button4_Click(Object sender, EventArgs e) in C:\PrivETags Generator\PrivETags Generator\WindowsApplication1\Form1.vb:line 742
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at WindowsApplication1.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:



no idea what this means.

thanks a lot for your help on this!!
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 23651807
I can not see that line in my code!!! All it means is that you are refering to column 3 when there are less than 3 columns.
0
 

Author Comment

by:jtammyg
ID: 23651837
sorry it was in my code....how do i overcome that error? it stops the app from running.

thanks a lot Codecruiser!!
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 23651930
The reason is that in this code
           If DataGridView1.Columns(i).Visible Then
                dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
            End If
You have put a check on visible for creating columns in the datatable but here
           For j As Integer = 0 To DataGridView1.Columns.Count - 1
                dr1(j) = DataGridView1.Rows(i).Cells(j).Value
            Next
You are adding rows to the table with more columns than the table has because you did not put the visible check.
By the way, why are you first copying to datatable and then excel? Why not write directly to Excel from the gridview?
0
 

Author Comment

by:jtammyg
ID: 23652306
because I do not know how to do it directly.....i am kinda new to this..!

how would you copy directly?

thanks a lot!! I really appreciate it!
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 600 total points
ID: 23653973
Here is the same code rewritten. You can delete all the code related to datatable
        For i as integer=0 to datagridview1.Columns.count - 1
            if datagridview1.columns(i).visible = true then
               colIndex = colIndex + 1
               xlapp.Cells(8, colIndex) = datagridview1.columns(i).name
            End if
        Next
 
        For j as integer=0 to datagridview1.rows.count - 1
            rowIndex = rowIndex + 1
            colIndex = 0
            For k as integer = 0 to datagridview1.columns.count - 1
                if datagridview1.columns(k).visible = true then
                   colIndex = colIndex + 1
                   xlapp.Cells(rowIndex + 1, colIndex) = datagridview1.rows(j).cells(k).value
                end if
 
            Next
        Next

Open in new window

0
 

Author Comment

by:jtammyg
ID: 23654091
codecruiser,

i used this code and now i selected to show only the last column of the datagridview......so there should be 3 columns exported ( the last column would be column 5 if i had all columns visible)

it is not showing in the excel report. so it shows columns 1 and 2 but not 3.

any ideas why?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 23654386
Set the visible property properly and it should work fine. Try to keep the invisible columns at the end.
0
 

Author Comment

by:jtammyg
ID: 23659302
I cannot keep the invisible columns at the end because the 3 invisible columns are coming from checkboxes that the user selects.

so lets say user wants to get label1 but not label2 or label3....he will check label1 checkbox, this will only show the 2 default columns and the label1 column, but the user might want to see label2 instead next time he runs the application or label3...or he may want to see label1 and label3 at same time.

does this makes sense?

so how should the code that you sent me look for those examples?

sorry to keep asking but it is not entirely clear.

thanks so much for ur time!!!!
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 23659812
When you said it shows column 1 and 2 but not 3, do you mean when all three are visible? Or when you select only column3? Does it add the column name on top in excel or not?
0
 

Author Comment

by:jtammyg
ID: 23661776
if i select checkbox to show label3 nowwith the code from above it does not show anything.....only the first 2 columns that are fixed, but not the last one...not even the title
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 23662584
Use step through in debug mode to go through the code and see which if conditions are not becoming true such as the visible condition.
0
 

Author Comment

by:jtammyg
ID: 23697994
I type my comment but on the wrong box....i had forgotten to delete a checkbox checking if columns existed....i did not need that with ur solution.....

thanks so much for your time...it worked like a charm!!!!
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 23709354
Glad to be helpful.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month20 days, 19 hours left to enroll

810 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