p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   .NET Framework 2.0 (http://p2p.wrox.com/forumdisplay.php?f=141)
-   -   Excel reports in vb.net (http://p2p.wrox.com/showthread.php?t=70274)

juster21 August 26th, 2008 12:19 PM

Excel reports in vb.net
 
I am in need of pulling data from Access and dumping the data into Excel and formatting into a useable report.
I have a front end where the user would choose certain criteria to generate a query which will be sent to the database.
The returned recordset gets sent to a spreadsheet which I will then format to suit.

I need help properly sending the query and returning the data. Thanks!!!

~Justin

PerttiOO March 3rd, 2014 03:53 AM

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.


All times are GMT -4. The time now is 02:52 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.