Unable to Save Exported data from Gridview to Excel

Question

I'm having some issues trying to Save this into a Excel file, i'm trying using the Invokemember ("Save As"... but isn't working, I don't know if I'm missing something. If someone can point me in the right direction, I will appreciate it.

This is de Code:

Imports System.Reflection Imports System.Globalization Public Class Excel

Public Shared Sub Export2Excel(datagridview As GridView, IsShowIsCaptions As Boolean) 
    Dim objMainApp As Object
    Dim objWorkBook As Object
    Dim objWorkBooks As Object
    Dim objWorkSheets As Object
    Dim objWorkSheet As Object
    Dim objCellRange As Object
    Dim Parameters As Object()
    Dim Sheet As Object
    Dim headers As String() = New String(datagridview.Columns.Count - 1) {}
    Dim columns As String() = New String(datagridview.Columns.Count - 1) {}
    Dim ci As System.Globalization.CultureInfo = New System.Globalization.CultureInfo("en-US")

    Dim i As Integer = 0
    Dim c As Integer = 0
    For c = 0 To datagridview.Columns.Count - 1
        headers(c) = datagridview.Columns(c).HeaderText 'OwningColumn.Name.ToString()
        columns(c) = Convert.ToString(ChrW(i))
        If (c < 26) Then
            i = c + 65
            columns(c) = Convert.ToChar(i) 'columns(c) = Convert.ToString((char)i)
        End If
        If (c = 26) Then columns(c) = "AA"
        If (c = 27) Then columns(c) = "AB"
        If (c = 28) Then columns(c) = "AC"
        If (c = 29) Then columns(c) = "AD"
        If (c = 30) Then columns(c) = "AE"
        If (c = 31) Then columns(c) = "AF"
        If (c = 32) Then columns(c) = "AG"
        If (c = 33) Then columns(c) = "AH"
        If (c = 34) Then columns(c) = "AI"
        If (c = 35) Then columns(c) = "AJ"
        If (c = 36) Then columns(c) = "AK"
        If (c = 37) Then columns(c) = "AL"
        If (c = 38) Then columns(c) = "AM"
        If (c = 39) Then columns(c) = "AN"
        If (c = 40) Then columns(c) = "AO"
        If (c = 41) Then columns(c) = "AP"
        If (c = 42) Then columns(c) = "AQ"
        If (c = 43) Then columns(c) = "AR"
        If (c = 44) Then columns(c) = "AS"
        If (c = 45) Then columns(c) = "AT"
        If (c = 46) Then columns(c) = "AU"
        If (c = 47) Then columns(c) = "AV"
        If (c = 48) Then columns(c) = "AW"
        If (c = 49) Then columns(c) = "AX"
        If (c = 50) Then columns(c) = "AY"
        If (c = 51) Then columns(c) = "AZ"
        If (c = 52) Then columns(c) = "BA"
        If (c = 53) Then columns(c) = "BB"
        If (c = 54) Then columns(c) = "BC"
        If (c = 55) Then columns(c) = "BD"
        If (c = 56) Then columns(c) = "BE"
        If (c = 57) Then columns(c) = "BF"
        If (c = 58) Then columns(c) = "BG"
        If (c = 59) Then columns(c) = "BH"
        If (c = 60) Then columns(c) = "BI"
        If (c = 61) Then columns(c) = "BJ"
        If (c = 62) Then columns(c) = "BK"
        If (c = 63) Then columns(c) = "BL"
        If (c = 64) Then columns(c) = "BM"
        If (c = 65) Then columns(c) = "BN"
        If (c = 66) Then columns(c) = "BO"
        If (c = 67) Then columns(c) = "BP"
        If (c = 68) Then columns(c) = "BQ"
        If (c = 69) Then columns(c) = "BR"
        If (c = 70) Then columns(c) = "BS"
        If (c = 71) Then columns(c) = "BT"
        If (c = 72) Then columns(c) = "BU"
        If (c = 73) Then columns(c) = "BV"
        If (c = 74) Then columns(c) = "BW"
        If (c = 75) Then columns(c) = "BX"
        If (c = 76) Then columns(c) = "BY"
        If (c = 77) Then columns(c) = "BZ"
        If (c = 78) Then columns(c) = "CA"
        If (c = 79) Then columns(c) = "CB"
        If (c = 80) Then columns(c) = "CC"
        If (c = 81) Then columns(c) = "CD"
        If (c = 82) Then columns(c) = "CE"
        If (c = 83) Then columns(c) = "CF"
        If (c = 84) Then columns(c) = "CG"
        If (c = 85) Then columns(c) = "CH"
        If (c = 86) Then columns(c) = "CI"
        If (c = 87) Then columns(c) = "CJ"
        If (c = 88) Then columns(c) = "CK"
        If (c = 89) Then columns(c) = "CL"
        If (c = 90) Then columns(c) = "CM"
        If (c = 91) Then columns(c) = "CN"
        If (c = 92) Then columns(c) = "CO"
        If (c = 93) Then columns(c) = "CP"
        If (c = 94) Then columns(c) = "CQ"
        If (c = 95) Then columns(c) = "CR"
        If (c = 96) Then columns(c) = "CS"
        If (c = 97) Then columns(c) = "CT"
        If (c = 98) Then columns(c) = "CU"
        If (c = 99) Then columns(c) = "CV"
        If (c = 100) Then columns(c) = "CW"
        If (c = 101) Then columns(c) = "CX"
        If (c = 102) Then columns(c) = "CY"
        If (c = 103) Then columns(c) = "CZ"
        If (c = 104) Then columns(c) = "DA"
        If (c = 105) Then columns(c) = "DB"
        If (c = 106) Then columns(c) = "DC"
        If (c = 107) Then columns(c) = "DD"
        If (c = 108) Then columns(c) = "DE"
        If (c = 109) Then columns(c) = "DF"
        If (c = 110) Then columns(c) = "DG"
        If (c = 111) Then columns(c) = "DH"
        If (c = 112) Then columns(c) = "DI"
        If (c = 113) Then columns(c) = "DJ"
        If (c = 114) Then columns(c) = "DK"
        If (c = 115) Then columns(c) = "DL"
        If (c = 116) Then columns(c) = "DM"
        If (c = 117) Then columns(c) = "DN"
        If (c = 118) Then columns(c) = "DO"
        If (c = 119) Then columns(c) = "DP"
        If (c = 120) Then columns(c) = "DQ"
        If (c = 121) Then columns(c) = "DR"
        If (c = 122) Then columns(c) = "DS"
        If (c = 123) Then columns(c) = "DT"
        If (c = 124) Then columns(c) = "DU"
        If (c = 125) Then columns(c) = "DV"
        If (c = 126) Then columns(c) = "DW"
        If (c = 127) Then columns(c) = "DX"
        If (c = 128) Then columns(c) = "DY"
        If (c = 129) Then columns(c) = "DZ"
        If (c = 130) Then columns(c) = "EA"
        If (c = 131) Then columns(c) = "EB"
        If (c = 132) Then columns(c) = "EC"
        If (c = 133) Then columns(c) = "ED"
        If (c = 134) Then columns(c) = "EE"
        If (c = 135) Then columns(c) = "EF"
        If (c = 136) Then columns(c) = "EG"
        If (c = 137) Then columns(c) = "EH"
        If (c = 138) Then columns(c) = "EI"
        If (c = 139) Then columns(c) = "EJ"
        If (c = 140) Then columns(c) = "EK"
        If (c = 141) Then columns(c) = "EL"
        If (c = 142) Then columns(c) = "EM"
        If (c = 143) Then columns(c) = "EN"
        If (c = 144) Then columns(c) = "EO"
        If (c = 145) Then columns(c) = "EP"
        If (c = 146) Then columns(c) = "EQ"
        If (c = 147) Then columns(c) = "ER"
        If (c = 148) Then columns(c) = "ES"
        If (c = 149) Then columns(c) = "ET"
        If (c = 150) Then columns(c) = "EU"
        If (c = 151) Then columns(c) = "EV"
        If (c = 152) Then columns(c) = "EW"
        If (c = 153) Then columns(c) = "EX"
        If (c = 154) Then columns(c) = "EY"
        If (c = 155) Then columns(c) = "EZ"
        If (c = 156) Then columns(c) = "FA"
        If (c = 157) Then columns(c) = "FB"
        If (c = 158) Then columns(c) = "FC"
        If (c = 159) Then columns(c) = "FD"
        If (c = 160) Then columns(c) = "FE"
        If (c = 161) Then columns(c) = "FF"
        If (c = 162) Then columns(c) = "FG"
        If (c = 163) Then columns(c) = "FH"
        If (c = 164) Then columns(c) = "FI"
        If (c = 165) Then columns(c) = "FJ"
        If (c = 166) Then columns(c) = "FK"
        If (c = 167) Then columns(c) = "FL"
        If (c = 168) Then columns(c) = "FM"
        If (c = 169) Then columns(c) = "FN"
        If (c = 170) Then columns(c) = "FO"
        If (c = 171) Then columns(c) = "FP"
        If (c = 172) Then columns(c) = "FQ"
        If (c = 173) Then columns(c) = "FR"
        If (c = 174) Then columns(c) = "FS"
        If (c = 175) Then columns(c) = "FT"

    Next
    Try
        Dim objClassType As Type
        objClassType = Type.GetTypeFromProgID("Excel.Application")
        objMainApp = Activator.CreateInstance(objClassType)

        objWorkBooks = objMainApp.[GetType]().InvokeMember("Workbooks", BindingFlags.GetProperty, Nothing, objMainApp, Nothing)
        objWorkBook = objWorkBooks.[GetType]().InvokeMember("Add", BindingFlags.InvokeMethod, Nothing, objWorkBooks, Nothing)

        objWorkSheets = objWorkBook.[GetType]().InvokeMember("Worksheets", BindingFlags.GetProperty, Nothing, objWorkBook, Nothing)

        Parameters = New [Object](0) {}
        Parameters(0) = 1
        objWorkSheet = objWorkSheets.[GetType]().InvokeMember("Item", BindingFlags.GetProperty, Nothing, objWorkSheets, Parameters, ci)

        If IsShowIsCaptions Then
            For c = 0 To datagridview.Columns.Count - 1
                Parameters = New [Object](1) {}
                Parameters(0) = columns(c) & "1"
                Parameters(1) = Missing.Value
                objCellRange = objWorkSheet.[GetType]().InvokeMember("Range", BindingFlags.GetProperty, Nothing, objWorkSheet, Parameters)

                Parameters = New [Object](0) {}
                Parameters(0) = headers(c)
                objCellRange.[GetType]().InvokeMember("Value", BindingFlags.SetProperty, Nothing, objCellRange, Parameters)
            Next
        End If
        For i = 0 To datagridview.Rows.Count - 1
            For c = 0 To datagridview.Columns.Count - 1
                Parameters = New [Object](1) {}
                Parameters(0) = columns(c) & Convert.ToString(i + 2)
                Parameters(1) = Missing.Value
                objCellRange = objWorkSheet.[GetType]().InvokeMember("Range", BindingFlags.GetProperty, Nothing, objWorkSheet, Parameters)

                Parameters = New [Object](0) {}
                If String.IsNullOrEmpty(datagridview.Rows(i).Cells(c).Text) Then
                    Parameters(0) = ""
                Else
                    Parameters(0) = datagridview.Rows(i).Cells(c).Text '.Text was as .Value
                End If
                objCellRange.[GetType]().InvokeMember("Value", BindingFlags.SetProperty, Nothing, objCellRange, Parameters)
            Next
        Next
        Parameters = New [Object](0) {}
        Parameters(0) = True
        objMainApp.[GetType]().InvokeMember("Visible", BindingFlags.SetProperty, Nothing, objMainApp, Parameters)
        'This line gives me an error: objWorkBook.[GetType]().InvokeMember("SaveAs", BindingFlags.InvokeMethod, Nothing, objWorkBook, New [Object](0) {"C:\\Test.xls"})
        objMainApp.[GetType]().InvokeMember("UserControl", BindingFlags.SetProperty, Nothing, objMainApp, Parameters)

    Catch ex As Exception
        MsgBox("Error exporting data to Excel: " + ex.Message)
    End Try
End Sub

End Clas

"isn't working" is not a useful problem description in programming. — Tim Williams, May 19 '15 at 20:09
Sorry what I meant is that there is not an error, I just don't know how to save the information that I'm reading from the gridview into the Excel File. Everything is working fine, all the data from the Gridview is been read and placed into the objects, and I just need to save that info, I have been trying to find a solution but I can find any. I Believe that I have to use ...invokemember("SaveAs",... at some point but I don't know how to implement that. The line that you see commented is an attempt to save the information but it did just not saved anything. — Ramon, May 19 '15 at 20:42
Your code looks overly complex - eg this is much cleaner: http://www.visual-basic-tutorials.com/export-datagridview-to-excel-in-visual-basic.html — Tim Williams, May 19 '15 at 23:02

Unable to Save Exported data from Gridview to Excel

0 Answers0