How to export data from PostgreSQL database to excel sheet? using vb.net 2019 windows form

Question

I have this button that when the user click all data from postgres will download to excel. i found the reference but the problem is its C# http://www.sqlines.com/postgresql/npgsql_cs_result_sets

Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Dim dt As New DataTable

        Try
            Using MyCon As New Odbc.OdbcConnection("Driver={PostgreSQL ANSI};database=YouthRecord;server=localhost;port=5432;uid=*****;sslmode=disable;readonly=0;protocol=7.4;User ID=*****;password=*****;"),
                    cmd As New Odbc.OdbcCommand("SELECT * FROM ""YouthApp_table_residencedata"" ", MyCon)
                MyCon.Open()
                dt.Load(cmd.ExecuteReader)

                ???????
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

UPDATE did i do it right? can you point out the mistake I made here?

Dim dt As New DataTable
    Dim oExcel As Object
    Dim oBook As Object
    Dim oSheet As Object
    oExcel = CreateObject("Excel.Application")
    oBook = oExcel.Workbooks.Add
    oSheet = oBook.Worksheets(1)

    'Create the QueryTable    
    Dim sNWind As String
    Dim oQryTable As Object
    sNWind = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
    Try
        Using MyCon As New Odbc.OdbcConnection("Driver={PostgreSQL ANSI};database=YouthRecord;server=localhost;port=5432;uid=postgres;sslmode=disable;readonly=0;protocol=7.4;User ID=postgres;password=ncf123;"),
                cmd As New Odbc.OdbcCommand("SELECT * FROM ""YouthApp_table_residencedata"" ", MyCon)
            MyCon.Open()
            dt.Load(cmd.ExecuteReader)
            oQryTable = oSheet.QueryTables.Add(MyCon, oSheet.Range("A1"), dt)

            oQryTable.Refresh



            'Save the Workbook and Quit Excel    
            oBook.SaveAs("C:\Users\Kevin Trinidad\Documents\Residence.xls")
            oExcel.Quit


        End Using
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

this is the error i received

    Exception thrown: 'System.ArgumentException' in Microsoft.VisualBasic.dll
'local.exe' (CLR v4.0.30319: local.exe): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\Accessibility\v4.0_4.0.0.0__b03f5f7f11d50a3a\Accessibility.dll'. 
The program '[39712] local.exe' has exited with code -1 (0xffffffff).

There are some good online conversion tools [C# to VB](https://converter.telerik.com/) — Alex B., Oct 29 '20 at 09:30

score 0 · Answer 1 · answered Oct 31 '20 at 03:48

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    WrapperToGetRidOfExcel()
    MessageBox.Show("Done")
End Sub

The code begins with calling WrapperToGetRidOfExcel where we call the method that contains the Excel code. This clean up code was provided by Govert (15.1k) at The proper way to dispose Excel com object using VB.NET?

The FillExcelFromDataTable begins by calling GetWorksheetData which returns a DataTable.

Private Function GetWorksheetData() As DataTable
    Dim dt As New DataTable

    Try
        Using MyCon As New SqlConnection(My.Settings.CoffeeConnection),
            cmd As New SqlCommand("Select Top 10 * FROM Coffees", MyCon)
            MyCon.Open()
            dt.Load(cmd.ExecuteReader)
        End Using
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
    Return dt
End Function

You can do the appropriate replacements for you Postgres database.

Back to FillExcelFromDataTable.

Private Sub FillExcelFromDataTable()
    Dim dt = GetWorksheetData()
    Dim oExcel As New Excel.Application
    Dim oBook = oExcel.Workbooks.Add
    Dim oSheet = DirectCast(oBook.Worksheets.Add, Excel.Worksheet)
    Dim ColumnIndex = 1
    For Each col As DataColumn In dt.Columns 'This loop adds the header row
        oSheet.Cells(1, ColumnIndex) = col.ColumnName
        ColumnIndex += 1
    Next
    ColumnIndex = 1 'The columns and rows in the spreadsheet
    Dim RowIndex = 2 'The columns and rows in the spreadsheet
    For rowI = 0 To dt.Rows.Count - 1
        For Each col As DataColumn In dt.Columns
            oSheet.Cells(RowIndex, ColumnIndex) = dt(rowI)(col)
            ColumnIndex += 1
        Next
        ColumnIndex = 1 'Reset back to the first column
        RowIndex += 1
    Next
    oBook.Save()
    oBook.SaveAs(Filename:="ExcelDat.xlsx") 'Saved to "C:\Users\xxx\Documents\ExcelDat.xlsx"
    oBook.Close()
    oExcel.Quit()
End Sub

Don't Dim objects As Object. Object has very few properties and methods and nothing useful to you here. Hold your cursor over oExcel, oBook and oSheet. Your will see that they all have a datatype where you can use properties and methods of Excel objects.

score 0 · Accepted Answer · answered Nov 03 '20 at 15:57

Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Try
            Dim xlApp As Microsoft.Office.Interop.Excel.Application
            Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
            Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
            Dim i As Integer
            Dim j As Integer
            xlApp = New Microsoft.Office.Interop.Excel.Application
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("sheet1")
            xlWorkSheet.Columns.AutoFit()
            For i = 0 To DataGridView1.RowCount - 2
                For j = 0 To DataGridView1.ColumnCount - 1
                    For k As Integer = 1 To DataGridView1.Columns.Count
                        xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                        xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
                    Next
                Next
            Next
            Dim fName As String = "DataBuku"
            Using sfd As New SaveFileDialog
                sfd.Title = "Save As"
                sfd.OverwritePrompt = True
                sfd.FileName = fName
                sfd.DefaultExt = ".xlsx"
                sfd.Filter = "Excel Workbook(*.xlsx)|"
                sfd.AddExtension = True
                If sfd.ShowDialog() = DialogResult.OK Then
                    xlWorkSheet.SaveAs(sfd.FileName)
                    xlWorkBook.Close()
                    xlApp.Quit()
                    releaseObject(xlApp)
                    releaseObject(xlWorkBook)
                    releaseObject(xlWorkSheet)
                    MsgBox("Export Successfully !", MsgBoxStyle.Information, "== Notice ==")
                End If
            End Using
        Catch ex As Exception

            MsgBox(ex.Message)
        End Try
    End Sub
    Private Sub ReleaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

here's the link https://www.nosware.com/web/how-to-export-datagridview-items-to-excel-vb-net/463/

How to export data from PostgreSQL database to excel sheet? using vb.net 2019 windows form

2 Answers2