asked on

Writing only displayed columns of datagridview to excel in vb.net

Hello,

I'm using below code to extracting my datagridviews to Excel sheet via ClosedXML. Some of my columns are invisible and i dont want to extract it.

How can i apply this to my code? Any help would be grateful.

Thank you.

        Public Shared Function WriteCheckSheetDgvToExcel(ByVal objSheet As Excel.IXLWorksheet, ByVal dgv As DataGridView) As Boolean
            Try
                For colHeader As Integer = 1 To dgv.ColumnCount Step 1
                    If dgv.Columns(colHeader - 1).Visible = True Then
                        objSheet.Cell(1, colHeader).Value = dgv.Columns(colHeader - 1).HeaderText
                    End If

                Next
                For col As Integer = 0 To dgv.ColumnCount - 1 Step 1
                    For row = 0 To dgv.Rows.Count - 1
                        objSheet.Cell(row + 2, col + 1).SetValue(Convert.ToString(dgv.Rows(row).Cells(col).Value)).SetDataType(Excel.XLDataType.Text)
                        If dgv.Rows(row).Cells(col).GetType = GetType(DataGridViewComboBoxCell) Then
                            Dim cboCell As New DataGridViewComboBoxCell
                            cboCell = CType(dgv.Rows(row).Cells(col), DataGridViewComboBoxCell)
                            If cboCell.Items.Count > 1 Then
                                objSheet.Cell(row + 2, col + 1).DataValidation.List(ComboboxItemsToList(cboCell), True)
                            End If
                        End If
                    Next
                Next
                FormatSheetAsTable(objSheet)
                Return True
            Catch ex As Exception
                Return False
                Tools.OutputLog.Print(Tools.OutputLog.Type.ERR, ex.Message)
            End Try
        End Function

Open in new window

Norie

Hakan

As you are looping through the columns you can check for there visibility.

If a column is hidden then skip it and move on to the next one.

The code might look something like this, sorry can't test anything right now.

                For col As Integer = 0 To dgv.ColumnCount - 1 Step 1

                    If dgv.Columns(col).Visible Then 

                        For row = 0 To dgv.Rows.Count - 1
                            objSheet.Cell(row + 2, col + 1).SetValue(Convert.ToString(dgv.Rows(row).Cells(col).Value)).SetDataType(Excel.XLDataType.Text)
                            If dgv.Rows(row).Cells(col).GetType = GetType(DataGridViewComboBoxCell) Then
                                Dim cboCell As New DataGridViewComboBoxCell
                                cboCell = CType(dgv.Rows(row).Cells(col), DataGridViewComboBoxCell)
                                If cboCell.Items.Count > 1 Then
                                    objSheet.Cell(row + 2, col + 1).DataValidation.List(ComboboxItemsToList(cboCell), True)
                                End If
                            End If
                        Next

                    End If
                Next

Open in new window

PS If you do take this approach you wouldn't be able to use the variable col for the column in Excel, you would need to introduce another variable and increment it appropriately.

Skale

ASKER

I didint succeeded with the your note :

PS If you do take this approach you wouldn't be able to use the variable col for the column in Excel, you would need to introduce another variable and increment it appropriately.

It makes really difficult to find a solution to increment columns and finding correct value from datagridview. I didnt make a correct way to map DGV cell and Excel cell:(

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.