SQL Data to Excel Spreadsheet?

S

Sean

New member
Joined
Dec 24, 2009
Messages
3
Location
Toronto
Programming Experience
1-3
Hey!

So I need to create a windows form that is able to generate reports by querying a SQL Database or SQL Server. There is data in the databases that will be put into an excel file from whatever columns from a specific table I need.

I need it to be a dynamic query that is probably hard coded into the application because I might need to write other queries at some point to generate different reports that need different columns.

What I need is somewhere to start or some code that I can look at because im not very familar when it comes to vb.net/excel. I need code to get all that data from the database and put it neatly in an excel file and I would like to specify what coloumns in the excel file all the data goes under.

Any help would be great! And if a better explanation is need please let me know :)
Thanks!
 
Sort by date Sort by votes
Here is an example that I made generic from one of my apps...
VB.NET: 
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 DumpDataToExcel()
        Dim i As Integer = 0
        Dim sSQL As String = String.Empty
        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.  
        sSQL = "SELECT field1, field2, field3, field4 "
        sSQL = sSQL & "FROM myTable "
        sSQL = sSQL & "ORDER BY field1 "
            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           
            

            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()    
        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
 
Upvote 0 Downvote
Thanks!!

This code is exactly like yours but I get and error:

Executereader: connection property has not been initialized.

I have bolded the error..

VB.NET: 
Dim i As Integer = 0
        Dim sSQL As String = String.Empty
        Dim ds As New DataSet
        Dim myConnection As String = "Data Source=DEVCON011\SQLEXPRESS;Initial Catalog=Tempo;Integrated Security=True"
        'this is used to get the number of records - that number will be used
        'as the max setting for the progress bar.
        Dim objConnection As New SqlConnection(myConnection)
        sSQL = "SELECT Name, Location, BirthDate "
        sSQL = sSQL & "FROM People "
        sSQL = sSQL & "ORDER BY Name "
        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()
        objConnection.Open()
        [B]Dim reader As SqlDataReader = command.ExecuteReader()[/B]
        Me.Cursor = Cursors.WaitCursor
        Me.ProgressBar1.Value = 0
        Me.ProgressBar1.Visible = True
        Me.lblProgress.Visible = True
        Me.ProgressBar1.Maximum = rowcount


        With oPageSetup
            .CenterHeader = "&""Arial,Bold""&16User Table List " & Chr(10)
            .PrintTitleRows = "$1:$5"
            .CenterFooter = "Page &P of &N"
            .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()
        Me.Cursor = Cursors.Default
        objExcel.Visible = True
        Me.ProgressBar1.Value = 0
        Me.ProgressBar1.Visible = False
        Me.lblProgress.Text = vbNullString
        Me.lblProgress.Visible = False
        objConnection.Close()
 
Upvote 0 Downvote
I just read the message about the error and came over to ask a question and offer a suggestion, but it seems that is no longer necessary. :)

How did you fix it?
 
Upvote 0 Downvote

Similar threads

H
  • Question
Question Data Export - advice needed
Replies
1
Views
460
jdelano
J
L
  • Question
Advice needed
Replies
3
Views
352
jdelano
J
J
  • Question
I can see the Excel Workbook, but the WorkSheet methods etc don't work :(
Replies
4
Views
2K
johnhoens
J
M
  • Question
Error open SQL Database Visual Basic 2012
Replies
2
Views
1K
jmcilhinney
jmcilhinney
genesix
  • Question
How can I validate Date.Now?
Replies
2
Views
389
genesix
genesix

Share this page

Latest posts

Back
Top