View Single Post
  #2 (permalink)  
Old March 3rd, 2014, 03:53 AM
PerttiOO PerttiOO is offline
Registered User
Points: 21, Level: 1
Points: 21, Level: 1 Points: 21, Level: 1 Points: 21, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2014
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default RE: Excel reports in vb.net

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.
Reply With Quote