Exporting displayed columns in dataGridView to Exc

问题:

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.

回答1:

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.

回答2:

thank you it works just had to change two lines

If value Is vbNullString Then

          ElseIf value IsNot vbNullString Then