Exporting displayed columns in dataGridView to Excel

Question

I may be blind, but this is a little different than the normal export-to-excel. I've created a solution and would like to know if this is the best way. Or even if there is another way to do it.

Background: WinForms, VisualBasic, VS2012, N-Tier (backend is DB2). My DTOs are set up in order of the DB2 tables. My users want to see the fields in a certain order within the DGV AND be able to export the fields in the same order. Users can also rearrange and hide columns. Hidden columns should not be exported.

My solution does do the normal copy from dgv field by field to excel. The difference is that I had to use DataGridViewColumnCollection so that I could utilize DataGridViewElementStates.Visible in order to make sure I'm only exporting visible columns.

Here is the code.

Private Sub ExportToExcelToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExportToExcelToolStripMenuItem.Click
  If((dgv.Columns.Count = 0) Or (dgv.Rows.Count = 0)) Then Exit Sub
  Dim XlApp = New Excel.Application With {.Visible = True}
  xlApp.Workbooks.Add(Excel.XlSheetType.xlWorksheet)
  Dim xlWS = xlApp.ActiveSheet
  xlWS.Name = "Exported Data"

  'Copy visible data from DGV to Excel
  Dim columnCollection As DataGridViewColumnCollection = dgv.Columns
  Dim currentVisibleColumn AS DataGridViewColumn = columnCollection.GetFirstColumn(DataGridViewElementStates.Visible)
  Dim lastColumnExported As DataGridViewColumn = currentVisibleColumn
  Dim visibleColumntCount As Integer = columnCollection.GetColumnCount(DataGridViewElementStates.Visible)

  'Finally export the data
  For c = 1 to VisibleColumnCount
     xlWS.Cells(1,c) = currentVisibleColumn.HeaderText
     currentVisibleColumn = columnCollection.GetNextColumn(lastColumnExported, DataGridViewElementStates.Visible, DataGridViewElementStates.None)
     lastColumnExported = currentVisibleColumn
  Next

  'Only export visible cells
  For r = 0 To dgv.Rows.Count - 1
     'Reset values
     currentVisibleColumn = columnCollection.GetFirstColumn(DataGridViewElementStates.Visible)
     lastColumnExported = currentVisibleColumn
     For c = 1 to visibleColumnCount
         Dim value = dgv.Rows(r).Cells(currentVisibleColumn.Index).Value
         If value <> vbNullString Then
            xlWS.Cells(r + 2, c) = value.ToString()
         End If
         currentVisibleColumn = columnCollection.GetNextColumn(lastColumnExported, DataGridViewElementStates.Visible, DataGridViewElementStates.None)
         lastColumnExported = currentVisibleColumn
     Next
  Next

  'Autosize columns in excel
  Dim columns = xlWS.UsedRange.Columns
  columns.AutoFit()
End Sub

Thank you for your feedback. Brian.

What exactly is your question? Is it just "was this code written well"? — davidsbro
– davidsbro, Commented Jan 29, 2014 at 18:01
The question is, "Is there another way to do this that works better? Or is this the way to do it?" My first time encountering this situation, so I want to make sure I'm doing it right. — PHBeagle
– PHBeagle, Commented Jan 29, 2014 at 22:55

Esteban · Accepted Answer · 2014-10-27 17:41:49Z

0

Theres is more simple way to do that and it is to perform the if statmente at each iteration of the cells copy process. You ask if that column at that index is visible, if it is visible you copy else you skeep that column and take next.

answered Oct 27, 2014 at 17:41

Esteban

312 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Exporting displayed columns in dataGridView to Excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related