Exporting data from DataGridView as Excel table using VB.NET

0

Recently I have been working on a project that aims to allow the user to send the data completed in the DataGridView as an Excel table, and first of all the table has to be saved in the user's Pc. I am using the code displayed below:

Private Sub Bt2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt2.Click
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value

    Dim i As Int16, j As Int16

    xlApp = New Excel.Application
    xlWorkBook = xlApp.Workbooks.Add(misValue)
    xlWorkSheet = xlWorkBook.Sheets("sheet1")


    For i = 0 To dg.RowCount - 2
        For j = 0 To dg.ColumnCount - 1
            xlWorkSheet.Cells(i + 1, j + 1) = dg(j, i).Value.ToString()
        Next
    Next

    xlWorkBook.SaveAs("C:\Users\Abstract\Desktop\file1.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
         Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
    xlWorkBook.Close(True, misValue, misValue)
    xlApp.Quit()

    releaseObject(xlWorkSheet)
    releaseObject(xlWorkBook)
    releaseObject(xlApp)

    MessageBox.Show("Over")
End Sub
Private Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
        MessageBox.Show("Exception Occured while releasing object " + ex.ToString())
    Finally
        GC.Collect()
    End Try
End Sub

The user clicks the button to save his DataGrid as an Excel table. However, after doing this, I get an exception (at the very first row, where I set xlApp as Excel Application) with the following text:

COM Exception Ocurred
Exception thrown: 'System.Runtime.InteropServices.COMException' in trimitere_excel.exe
Additional information: Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

I have been searching for almost 2 days to get over this. If you have any idea about what I should do, I would be really grateful for any piece of advice that could get me out of this! Have a nice day! :)

excel
vb.net
datagridview
export
runtime-error
asked on Stack Overflow Jul 12, 2017 by Gabriel Stancu • edited Sep 11, 2018 by Gabriel Stancu

1 Answer

1

Maybe this will help:

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports Microsoft.Office.Interop


Private Sub Bt2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt2.Click

Dim pathExcel As String = "C:\Users\Abstract\Desktop\file1.xls"
Dim xlApp As Excel.Application = New Excel.Application
Dim misValue As Object = System.Reflection.Missing.Value
Dim xlWorkBook As Excel.Workbook
xlWorkBook = xlApp.Workbooks.Add(misValue)
Dim xlWorksheet As Excel.Worksheet = xlWorkBook.Sheets("sheet1")

For i = 0 To dg.RowCount - 2
    For j = 0 To dg.ColumnCount - 1
        xlWorkSheet.Cells(i + 1, j + 1) = dg(j, i).Value.ToString()
    Next
Next

xlWorkBookSum.SaveAs(pathExcel)
xlWorkBookSum.Close()
xlApp.Quit()

releaseObject(xlWorkSheet)
releaseObject(xlWorkBook)
releaseObject(xlApp)

MessageBox.Show("Over")

End Sub
answered on Stack Overflow Jul 12, 2017 by Krystian Kozłowski

User contributions licensed under CC BY-SA 3.0