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).
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/
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.
User contributions licensed under CC BY-SA 3.0