Report Generate to Excel

[01hanstu]

Hi, does anyone know of a workng example of a vb.net application generating a report to excel, where the database is access?

Thanks
Stuart

[Hack]

I do it with SQL Server, not Access, but the principle should remain the same.

Option Strict On
 
Imports Excel
Imports System.Data.SqlClient
 
Public Class Form1
    Private objExcel As Excel.Application = DirectCast(CreateObject("Excel.Application"), Excel.Application)
    Private bkWorkBook As Excel.Workbook = DirectCast(objExcel.Workbooks.Add(), Excel.Workbook)
    Private shWorkSheet As Excel.Worksheet = DirectCast(bkWorkBook.Sheets("Sheet1"), Excel.Worksheet)
    Private rngRowStart As Excel.Range = DirectCast(shWorkSheet.Cells(1, 1), Excel.Range)
    Private oPageSetup As Excel.PageSetup = DirectCast(shWorkSheet.PageSetup, Excel.PageSetup)
    Private oRange As Excel.Range = DirectCast(shWorkSheet.Columns("A:BZ"), Excel.Range) 
 
Private Sub PrintUserList(Optional ByVal pstrStatus As String = vbNullString)
    Dim i As Long
        Dim sSQL As String
        Dim ds As New DataSet
        'this is used to get the number of records - that number will be used
        'as the max setting for the progress bar.  for the count, we need to use
        'a data set
        sSQL = "SELECT user_last_nm, user_first_nm, cost_ctr, machine_nm, active "
        sSQL = sSQL & "FROM mytablename "
        Select Case pstrStatus
            Case "Active"
                sSQL = sSQL & "WHERE active = 'T' "
            Case "Inactive"
                sSQL = sSQL & "WHERE active = 'F' "
        End Select
        sSQL = sSQL & "ORDER BY user_last_nm "
        Try
            Dim da As New System.Data.SqlClient.SqlDataAdapter(sSQL, myConnection)
            da.Fill(ds)
            Dim rowcount As Integer = ds.Tables.Item(0).Rows.Count
            'now, for the display, we need to use the datareader
            Dim command As New SqlCommand(sSQL, myConnection)
            Dim reader As SqlDataReader = command.ExecuteReader()
            Me.Cursor = Cursors.WaitCursor
            Me.ProgressBar1.Value = 0
            Me.ProgressBar1.Visible = True
            Me.lblProgress.Visible = True
            Me.ProgressBar1.Maximum = rowcount
            System.Windows.Forms.Application.DoEvents()
            shWorkSheet.Name = "Report Output"
 
            With oPageSetup
                .CenterHeader = "&""Arial,Bold""&16User Table List " & Chr(10)
                .PrintTitleRows = "$1:$5"
                .CenterFooter = "Page &P of &N"
                .LeftFooter = "&8Prepared By:  " & UserInfo.RealName & vbCr & "Report #AUP1 "
                .RightFooter = "&8" & Format(Now, "Long Date") & vbCr & "AUP.NET Ver. " & My.Application.Info.Version.Major & "." & My.Application.Info.Version.Minor
                .FooterMargin = objExcel.InchesToPoints(0.35)
                .HeaderMargin = objExcel.InchesToPoints(0.35)
                .LeftMargin = objExcel.InchesToPoints(0.75)
                .RightMargin = objExcel.InchesToPoints(0.75)
                .TopMargin = objExcel.InchesToPoints(1.0#)
                .BottomMargin = objExcel.InchesToPoints(0.75)
                .FitToPagesWide = 1
                .FitToPagesTall = 10
                .Zoom = False
            End With
 
            shWorkSheet.Range("C4").Value = "Last"
            shWorkSheet.Range("C5").Value = "Name"
            shWorkSheet.Range("D4").Value = "First "
            shWorkSheet.Range("D5").Value = "Name"
            shWorkSheet.Range("E4").Value = "Cost"
            shWorkSheet.Range("E5").Value = "Center"
            shWorkSheet.Range("F4").Value = "Machine"
            shWorkSheet.Range("F5").Value = "Name"
            shWorkSheet.Range("G4").Value = "Active"
            shWorkSheet.Range("G5").Value = "Status"
            shWorkSheet.Range("C1:G5").Font.Bold = True
            shWorkSheet.Range("B1:B2").Font.Italic = True
            shWorkSheet.Range("B1:B2").Font.Size = 12
            oRange.VerticalAlignment = Excel.Constants.xlTop
            shWorkSheet.Range("E:E").HorizontalAlignment = Excel.Constants.xlCenter
            rngRowStart = shWorkSheet.Range("C7")
            While reader.Read()
                For i = 0 To 4
                    rngRowStart.Offset(0, 0).Value = reader.GetString(0)
                    rngRowStart.Offset(0, 1).Value = reader.GetString(1)
                    rngRowStart.Offset(0, 2).Value = reader.GetString(2)
                    If Not reader.IsDBNull(3) Then 'test for nulls
                        rngRowStart.Offset(0, 3).Value = reader.GetString(3)
                    Else
                        rngRowStart.Offset(0, 3).Value = "None Logged"
                    End If
                    If reader.GetString(4) = "T" Then
                        rngRowStart.Offset(0, 4).Value = "Active" 'active status
                    Else
                        rngRowStart.Offset(0, 4).Value = "Inactive"
                    End If
                Next
                ProgressBar1.Value = ProgressBar1.Value + 1
                System.Windows.Forms.Application.DoEvents()
                lblProgress.Text = Int((ProgressBar1.Value / ProgressBar1.Maximum) * 100) & "% Complete"
                rngRowStart = rngRowStart.Offset(1, 0)
            End While
            oRange = DirectCast(shWorkSheet.Columns("C:C"), Excel.Range)
            oRange.ColumnWidth = 15
            oRange = DirectCast(shWorkSheet.Columns("F:F"), Excel.Range)
 
            oRange.ColumnWidth = 15
            reader.Close()
            da.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message & " " & ex.Source, "PrintUserList")
        End Try
        Me.Cursor = Cursors.Default
        objExcel.Visible = True
        Me.ProgressBar1.Value = 0
        Me.ProgressBar1.Visible = False
        Me.lblProgress.Text = vbNullString
        Me.lblProgress.Visible = False
    End Sub
End Class