export datagridview columns to excel

Question

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

Jadeja RJ · Accepted Answer · 2013-11-20 18:39:57Z

1

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

answered Nov 20, 2013 at 18:39

Jadeja RJ

1,0143 gold badges14 silver badges27 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 10:10:10Z

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()

mgokhanbakal · Accepted Answer · 2013-11-20 17:04:48Z

0

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.

answered Nov 20, 2013 at 17:04

mgokhanbakal

1,7471 gold badge20 silver badges26 bronze badges

1 Comment

Mauro Petrini Over a year ago

How change this line with a datatable? DtExport.Rows(i).Cells(j).Value.ToString()

Haji · Accepted Answer · 2013-11-21 04:22:30Z

0

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

edited Nov 21, 2013 at 4:22

answered Nov 20, 2013 at 18:30

Haji

2,0971 gold badge16 silver badges22 bronze badges

4 Comments

Mauro Petrini Over a year ago

yes the rows of column(0) don´t show but the headertext of the column yes

Haji Over a year ago

@WFgo now I have edited my answer for header text also..check with that

Haji Over a year ago

@WFgo Did you check with my coding?

Mauro Petrini Over a year ago

it not works, the first column disappear completely but also the rows of second column also dissapear

Collectives™ on Stack Overflow

export datagridview columns to excel

4 Answers 4

Comments

Comments

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related