[VB.NET] dataGridView to Excel Class

Description
     In this article i will share excel class which can convert the data in the DataGrid to excel, very usefull better than code from scratch. the limitation is the data appear in datagridview same data in excel later, you cannot costumize the block field.


How To

here's the exportExcel Class :

Imports System.Windows.Forms
Imports System.Windows.Forms
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Threading
Imports System.Globalization
Imports System.Net

Public Class exportExcel
    Dim oexcel As Excel.Application = New Excel.Application()
    Dim obook As Excel.Workbook
    Dim osheet As Object
    Dim customFormatDate As String = "dd/MM/yyyy"

    Public Sub exportExcel(ByVal dataGrid As DataGridView, ByVal savePath As String, ByRef messageOut As Label)
        Thread.CurrentThread.CurrentCulture = New CultureInfo("en-US")
        oexcel = CreateObject("Excel.Application")
        obook = oexcel.Workbooks.Add
        osheet = obook.Worksheets(1)

        'format Header, row index always start at 1
        For columnIndex = 1 To dataGrid.Columns.Count
            osheet.Range(intToAlphabet(columnIndex) & 1).NumberFormat = "@"
            osheet.Range(intToAlphabet(columnIndex) & 1).Value = dataGrid.Columns(columnIndex - 1).HeaderText
            Application.DoEvents()
            messageOut.Text = "Write at : Col:" & intToAlphabet(columnIndex) & " " & "Row:1"
        Next columnIndex

        'format Row cell
        For rowIndex = 0 To dataGrid.Rows.Count - 1 'row start from 2 , because row 1 is header
            For columnIndex = 0 To dataGrid.Columns.Count - 1
                If dataGrid.Columns(columnIndex).ValueType.ToString = GetType(Date).ToString Then
                    'Jika column type = DATE
                    osheet.Range(intToAlphabet(columnIndex + 1) & rowIndex + 2).Value = Format(dataGrid.Rows(rowIndex).Cells(columnIndex).Value(), customFormatDate)
                Else
                    'Jika column type = STRING
                    osheet.Range(intToAlphabet(columnIndex + 1) & rowIndex + 2).NumberFormat = "@"
                    osheet.Range(intToAlphabet(columnIndex + 1) & rowIndex + 2).Value = dataGrid.Rows(rowIndex).Cells(columnIndex).Value()
                End If

                Application.DoEvents()
                messageOut.Text = "Write at : Col:" & intToAlphabet(columnIndex + 1) & " " & "Row:" & rowIndex + 2
            Next columnIndex
        Next rowIndex

        oexcel.DisplayAlerts = False

        Try
            obook.SaveAs(savePath, Excel.XlFileFormat.xlOpenXMLWorkbook)
        Catch e As Exception
            MsgBox(e.Message & vbCrLf & "Process cancelled !!", vbCritical, "Abort")
        End Try

        oexcel.Quit()
        killCurrentExcel(oexcel) ' you can find this function in different article
        oexcel = Nothing
    End Sub

    Private Sub killCurrentExcel(ByVal oexcel As Object)
        Dim proc As System.Diagnostics.Process
        Dim intPID As Integer
        Dim intResult As Integer
        Dim iHandle As IntPtr
        Dim strVer As String

        strVer = oexcel.Version
        iHandle = IntPtr.Zero
        If CInt(strVer) > 9 Then
            iHandle = New IntPtr(CType(oexcel.Parent.Hwnd, Integer))
        Else
            iHandle = FindWindow(Nothing, oexcel.Caption)
        End If
        oexcel.Workbooks.Close()
        oexcel.Quit()
        Marshal.ReleaseComObject(oexcel)
        oexcel = Nothing
        intResult = GetWindowThreadProcessId(iHandle, intPID)
        proc = System.Diagnostics.Process.GetProcessById(intPID)
        proc.Kill()
    End Sub

    Private Function intToAlphabet(ByVal iCol As Integer) As String
        Dim iAlpha As Integer
        Dim iRemainder As Integer
        intToAlphabet = Nothing 'init value 

        iAlpha = Int(iCol / 27)
        iRemainder = iCol - (iAlpha * 26)
        If iAlpha > 0 Then
            intToAlphabet = Chr(iAlpha + 64)
        End If
        If iRemainder > 0 Then
            intToAlphabet = intToAlphabet & Chr(iRemainder + 64)
        End If
    End Function
End Class

To Use this class :

1. Add reference excel Microsoft Excel 12.0 Object Library.
2. Use this code :
     Dim objExportExcel As New exportExcel
     objExportExcel.exportExcel(dataGridView, "D:\pathSave", yourlabelobject)
3. and wait until file excel has been created