28 | 04 | 2017

Export Datagridview content to Excel

The code below export datagridview content to an Excel file. The save location of the excel file can be selected by the user. 

 Sub ExporttoExcel(ByVal DataGridView1 As DataGridView)
        '***********************************************************
        '***************Code from visiblevisual.com*****************
        '***********************************************************
        'verfying the datagridview having data or not
        If ((DataGridView1.Columns.Count = 0) Or (DataGridView1.Rows.Count = 0)) Then
            Exit Sub
        End If

        'Creating dataset to export
        Dim dset As New DataSet
        'add table to dataset
        dset.Tables.Add()
        'add column to that table
        For i As Integer = 0 To DataGridView1.ColumnCount - 1
            If DataGridView1.Columns(i).Visible = True Then
                dset.Tables(0).Columns.Add(DataGridView1.Columns(i).HeaderText)
            End If
        Next
        Dim celltext As String
        Dim count As Integer = -1
        'add rows to the table
        Dim dr1 As DataRow
        For i As Integer = 0 To DataGridView1.RowCount - 1
            dr1 = dset.Tables(0).NewRow


            For j As Integer = 0 To DataGridView1.Columns.Count - 1
                If DataGridView1.Columns(j).Visible = True Then
                    count = count + 1

                    dr1(count) = DataGridView1.Rows(i).Cells(j).Value
                End If
            Next

            count = -1
            dset.Tables(0).Rows.Add(dr1)
        Next

        Dim excel As New Excel.Application
        Dim wBook As Excel.Workbook
        Dim wSheet As Excel.Worksheet

        wBook = excel.Workbooks.Add()
        wSheet = wBook.ActiveSheet()


        Dim dt As System.Data.DataTable = dset.Tables(0)
        Dim dc As System.Data.DataColumn
        Dim dr As System.Data.DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0

        For Each dc In dt.Columns
            colIndex = colIndex + 1
            excel.Cells(1, colIndex) = dc.ColumnName
        Next

        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                excel.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)

            Next
        Next

        wSheet.Columns.AutoFit()

        

        Dim saveFileDialog1 As New SaveFileDialog()
        saveFileDialog1.Filter = "Excel Workbook|*.xls|Excel Workbook 2011|*.xlsx"
        saveFileDialog1.Title = "Save Excel File"
        saveFileDialog1.FileName = "Export " & Now.ToShortDateString & ".xls"
        saveFileDialog1.ShowDialog()

        saveFileDialog1.InitialDirectory = "C:/"
        If saveFileDialog1.FileName <> "" Then

            Dim fs As System.IO.FileStream = CType(saveFileDialog1.OpenFile(), System.IO.FileStream)
            fs.Close()
        End If


        Dim strFileName As String = saveFileDialog1.FileName
        Dim blnFileOpen As Boolean = False


        Try
            Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
            fileTemp.Close()
        Catch ex As Exception
            blnFileOpen = False
            Exit Sub
        End Try

        If System.IO.File.Exists(strFileName) Then
            System.IO.File.Delete(strFileName)
        End If

        wBook.SaveAs(strFileName)
        excel.Workbooks.Open(strFileName)
        excel.Visible = True
        Exit Sub
errorhandler:
        MsgBox(Err.Description)
    End Sub

 

Login

Sign up now and upload your code to the website.

Help us to continue.....
Statistics
Articles View Hits
2236244
Latest Articles