CODEWITHSUNDEEP
vb.net
Mauro Petrini
Mauro Petrini

Reputation: 351

export datagridview columns to excel

in my datagridview I have the first column visible = false. I need that this column not export to excel

I have the following code to export to excel:

If Sfd.ShowDialog() = DialogResult.OK Then

            Dim App As New Excel.Application
            Dim WB As Excel.Workbook
            Dim WS As New Excel.Worksheet

            WB = App.Workbooks.Add()

            WS = WB.ActiveSheet

            For i As Integer = 1 To DG.Columns.Count
                WS.Cells(1, i) = DG.Columns(i - 1).HeaderText
            Next

            For i As Integer = 0 To DG.Rows.Count - 1
                For j As Integer = 0 To DG.Columns.Count - 1
                    WS.Cells(i + 2, j + 1) = DG.Rows(i).Cells(j).Value.ToString()
                    WS.Cells(i + 2, 1).Font.Color = Color.Blue
                Next
            Next

            With WS
                With .Range(.Cells(1, 1), .Cells(1, DG.ColumnCount)).Font
                    .Color = Color.White
                    .Bold = 1
                    .Size = 12
                End With
                .Range(.Cells(1, 1), .Cells(1, DG.ColumnCount)).Interior.Color = Color.Black
                .Columns.AutoFit()
                .Columns.HorizontalAlignment = 2
            End With

            WB.SaveAs(Sfd.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal)

            WB.Close()
            Process.Start(Sfd.FileName)
        End If

Thanks

Upvotes: 0

Views: 2958

Answers (4)

Jeline Esase
Jeline Esase

Reputation: 84

Hi I had the same problem try the code below it works great in my application this is the link where i copied my code Exporting displayed columns in dataGridView to Excel

' creating Excel Application

        If ((dgSMS.Columns.Count = 0) Or (dgSMS.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 = dgSMS.Columns
        Dim currentVisibleColumn As DataGridViewColumn = columnCollection.GetFirstColumn(DataGridViewElementStates.Visible)
        Dim lastColumnExported As DataGridViewColumn = currentVisibleColumn
        Dim visibleColumnCount 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 dgSMS.Rows.Count - 1
            'Reset values
            currentVisibleColumn = columnCollection.GetFirstColumn(DataGridViewElementStates.Visible)
            lastColumnExported = currentVisibleColumn
            For c = 1 To visibleColumnCount
                Dim value = dgSMS.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()


        ' save the application

        'workbook.SaveAs("c:\output.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _
        '  Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing)



        ' Exit from the application

        ' app.Quit()

Upvotes: 0

Haji
Haji

Reputation: 2077

Change your for loop as below:

  For i As Integer = 1 To DG.Columns.Count-1
                WS.Cells(1, i) = DG.Columns(i).HeaderText
  Next

 For i As Integer = 0 To DG.Rows.Count - 1
                For j As Integer = 1 To DG.Columns.Count - 1
                    WS.Cells(i + 2, j + 1) = DG.Rows(i).Cells(j).Value.ToString()
                    WS.Cells(i + 2, 1).Font.Color = Color.Blue
                Next
 Next

I just changed the inner for loop start from 1. that is from the second column.so it won't export first column

Upvotes: 0

Jadeja RJ
Jadeja RJ

Reputation: 1014

  For i = 0 To DG.Columns.Count - 1
     WS.Cells(1, i + 1) = DG.Columns(i).HeaderText
  Next

Upvotes: 1

mgokhanbakal
mgokhanbakal

Reputation: 1727

Why dont you first generate a datatable that you can build without adding the column which you dont want to export, then you can apply same export procedure to this datatable.

Upvotes: 0

Related Questions