Hi, you can try the following
VB.NET code.
Note you need to fill in TODO's: DB connection string and SQL query.
Code:
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;data source=C:\...\TODO.accdb"
Dim results As New DataTable("Report")
' Retrieve Access DB data and fill DataTable.
Using conn As New OleDbConnection(connString)
Dim cmd As New OleDbCommand("SELECT * FROM TODO WHERE TODO", conn)
conn.Open()
Dim adapter As New OleDbDataAdapter(cmd)
adapter.Fill(results)
End Using
' Create new Excel report.
Dim report As New ExcelFile()
Dim reportSheet As ExcelWorksheet = report.Worksheets.Add(results.TableName)
' Insert DataTable to Excel sheet.
Dim options As New InsertDataTableOptions("A1")
options.ColumnHeaders = True
reportSheet.InsertDataTable(results, options)
' Format header row.
Dim headerFont As ExcelFont = reportSheet.Rows(0).Style.Font
headerFont.Weight = ExcelFont.BoldWeight
headerFont.Size = CInt(LengthUnitConverter.Convert(14, LengthUnit.Point, LengthUnit.Twip))
' AutoFit filled columns.
Dim columnCount As Integer = reportSheet.CalculateMaxUsedColumns()
For i As Integer = 0 To columnCount - 1
reportSheet.Columns(i).AutoFit()
Next
' Save Excel file.
report.Save("Report.xls")
I hope this can get anyone to start coding the similar requirements. Also you will notice that I used this
VB.NET Excel component to generate a "Report.xls" file. It has great performances and it does not use
excel automation, so it is quite suited for tasks like generating large reports.