Showing posts with label easy way to export datagridview. Show all posts
Showing posts with label easy way to export datagridview. Show all posts

Thursday, 4 April 2019

Export Excel in VB.Net

Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.IO
Imports System.Net.Mime.MediaTypeNames
Imports Microsoft.Office.Interop


Dim idx As Integer
    Private Excel03ConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"
    Private Excel07ConString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim rowsTotal, colsTotal As Short
        Dim I, j, iC As Short
        System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
        Dim xlApp As New Excel.Application
        Try
            Dim btn As Button
            Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
            Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
            xlApp.Visible = True
            Dim chartRange As Excel.Range


            rowsTotal = DataGridView1.RowCount - 1
            colsTotal = DataGridView1.Columns.Count - 1
            chartRange = excelWorksheet.Range("A1", "G1")

            chartRange.HorizontalAlignment = 3
            chartRange.VerticalAlignment = 3
            chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow)
            chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
            chartRange.Font.Size = 20

            chartRange = excelWorksheet.Range("A1", "G1")
            chartRange.Font.Bold = True
            chartRange = excelWorksheet.Range("A1", "G1")
            chartRange.Font.Bold = True


            chartRange = excelWorksheet.Range("A1", "G1")
            With excelWorksheet
                .Cells.Select()
                .Cells.Delete()

                For iC = 0 To colsTotal


                    .Cells(1, iC + 1).Value = DataGridView1.Columns(iC).HeaderText
                    .Cells(1, iC + 1).ColumnWidth = 20
                    .Cells(1, iC + 1).Font.FontStyle = "Bold"
                    .Cells(1, iC + 1).Font.Size = 15

                Next iC
                For I = 0 To rowsTotal
                    For j = 0 To colsTotal
                        .Cells(I + 2, j + 1).value = DataGridView1.Rows(I).Cells(j).Value

                    Next j
                Next I


                '.Cells.ColumnWidth = 15
                .Cells.Select()
                .Cells.EntireColumn.AutoFit()
                .Cells(1, 1).Select()


            End With



        Catch ex As Exception
            'MsgBox("Export Excel Error " & ex.Message)
        Finally
            'RELEASE ALLOACTED RESOURCES
            System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
            xlApp = Nothing
        End Try
End Sub