将dataGridView中显示的列导出到Excel [英] Exporting displayed columns in dataGridView to Excel

问题描述

我可能是盲目的，但这与一般的出口到excel有点不同。我创建了一个解决方案，并想知道这是否是最好的方法。或者即使有另一种方法可以实现。

背景：WinForms，VisualBasic，VS2012，N-Tier（后端是DB2）。我的DTO按照DB2表的顺序进行设置。我的用户希望在DGV内以特定的顺序看到这些字段，并且能够以相同的顺序导出字段。用户还可以重新排列和隐藏列。隐藏的列不应该导出。

我的解决方案从dgv字段到excel中执行正常复制。不同的是，我不得不使用DataGridViewColumnCollection，以便我可以使用DataGridViewElementStates.Visible，以确保我只导出可见的列。

这是代码。

  Private Sub ExportToExcelToolStripMenuItem_Click（sender As Object，e As EventArgs）Handles ExportToExcelToolStripMenuItem.Click 
如果（（dgv.Columns .Count = 0）或（dgv.Rows.Count = 0））然后退出Sub 
 Dim XlApp =新的Excel.Application与{.Visible = True} 
 xlApp.Workbooks.Add（Excel.XlSheetType $ x 
 Dim xlWS = xlApp.ActiveSheet 
 xlWS.Name =导出数据
 
'将可视数据从DGV复制到Excel 
 Dim columnCollection As DataGridViewColumnCollection = dgv.Columns 
 Dim currentVisibleColumn AS DataGridViewColumn = columnCollection.GetFirstColumn（DataGridViewElementStates.Visible）
 Dim lastColumnExported As DataGridViewColumn = currentVisibleColumn 
 Dim visibleColumntCount As In teger = columnCollection.GetColumnCount（DataGridViewElementStates.Visible）
 
'最后导出数据
对于c = 1到VisibleColumnCount 
 xlWS.Cells（1，c）= currentVisibleColumn.HeaderText 
 currentVisibleColumn = columnCollection.GetNextColumn（lastColumnExported，DataGridViewElementStates.Visible，DataGridViewElementStates.None）
 lastColumnExported = currentVisibleColumn 
下一个
 
'仅导出可见单元格
对于r = 0到dgv.Rows.Count  -  1 
'重置值
 currentVisibleColumn = columnCollection.GetFirstColumn（DataGridViewElementStates.Visible）
 lastColumnExported = currentVisibleColumn 
对于c = 1到visibleColumnCount 
 Dim value = dgv.Rows（r）.Cells（currentVisibleColumn.Index）.Value 
如果值<> vbNullString然后
 xlWS.Cells（r + 2，c）= value.ToString（）
结束If 
 currentVisibleColumn = columnCollection.GetNextColumn（lastColumnExported，DataGridViewElementStates.Visible，DataGridViewElementStates.None）
 lastColumnExported = currentVisibleColumn 
下一个
下一个
 
'在Excel中自动调整列
 Dim columns = xlWS.UsedRange.Columns 
 columns.AutoFit（）
 End Sub 
  

感谢您的反馈。
Brian。

解决方案

谢谢你只需要更改两行

如果值为vbNullString然后

  ElseIf值IsNot vbNullString然后
  

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.

解决方案

thank you it works just had to change two lines

If value Is vbNullString Then

          ElseIf value IsNot vbNullString Then

这篇关于将dataGridView中显示的列导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！