ASP.Net Dynamic Excel Creation from SQL Datasource

[Brian Sivel]

I have a reguirement to enable the creation of excel workbooks from sql stored procedure. To do this I want to create a vb class to generate and save the excel files. Then and ASP.net page will enable opening the excel file via the browser or enable the user to download the file.

The class should be able to be called from a vareity of pages within the web app and have the ability to handle 8 to 10 separate stored procedures that will return various data sets.

[PsychoCoder]

Why not start by showing us what you've tried thus far and we will do our best to help

[Brian Sivel]

@Brian Sivel
Thanks for the reply. I stated creating my vb class but I'm stuck on how to dimension an excel application and pass the dataset results to it....

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
 
Public Class ExcelWriter
    Public Sub MakeMyExcelFile(ByVal sp As String, ByVal fn As String, ByVal dt1 As Date, ByVal dt2 As Date)
 
 
        Dim cnxnstr As String = ""
        cnxnstr = ConfigurationManager.ConnectionStrings("PRTrackerDEVConnectionString").ToString
        Using cnxn As New Data.SqlClient.SqlConnection(cnxnstr)
 
            Dim cmd As New Data.SqlClient.SqlCommand("bss_set_userid_locks", cnxn)
            cmd.CommandType = Data.CommandType.StoredProcedure
            cnxn.Open() : cmd.Connection = cnxn
 
            'Dimension all the parameters the stored procedure requires...
            Dim pLoginDT1 As New Data.SqlClient.SqlParameter
            Dim pLoginDT2 As New Data.SqlClient.SqlParameter
 
            'Configure each parameter...
            With pLoginDT1
                .ParameterName = "@logindt1" : .DbType = Data.DbType.DateTime
                .Direction = Data.ParameterDirection.Input
                .Value = dt1
                cmd.Parameters.Add(pLoginDT1)
            End With
 
            With pLoginDT2
                .ParameterName = "@logindt2" : .DbType = Data.DbType.DateTime
                .Direction = Data.ParameterDirection.Input
                .Value = dt2
                cmd.Parameters.Add(pLoginDT2)
            End With
 
 
            ' Create data adapter
 
            Dim da As New SqlDataAdapter(cmd)
 
            ' Fill dataset from adapter
 
            Dim ds As New System.Data.DataSet
 
            da.Fill(ds)
 
        End Using
 
 
        Dim xlApp As 
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
 
        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
 
 
    End Sub
 
End Class