writing simple text to excel

From: Roman (pruszkowskir_at_aecl.ca)
Date: 06/22/04 

Date: Tue, 22 Jun 2004 08:59:55 -0400

Hi All,

I have a routine that generates a report and displays it in Excel
spread***. Here is the code (it works, feel free to use it, it's not mine
anyway ;-)))):

'Class to convert a dataset to an html stream which can be used to display
the dataset
'in MS Excel
'The Convert method is overloaded three times as follows
' 1) Default to first table in dataset
' 2) Pass an index to tell us which table in the dataset to use
' 3) Pass a table name to tell us which table in the dataset to use

Public Class DataSetToExcel

    Public Shared Sub Convert(ByVal ds As DataSet, ByVal response As
HttpResponse)
        'first let's clean up the response.object
        response.Clear()
        response.Charset = ""
        'set the response mime type for excel
        response.ContentType = "application/vnd.ms-excel"
        'create a string writer
        Dim stringWrite As New System.IO.StringWriter
        'create an htmltextwriter which uses the stringwriter
        Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
        'instantiate a datagrid
        Dim dg As New DataGrid
        'set the datagrid datasource to the dataset passed in
        dg.DataSource = ds.Tables(0)
        'bind the datagrid
        dg.DataBind()
        'tell the datagrid to render itself to our htmltextwriter
        dg.RenderControl(htmlWrite)
        'all that's left is to output the html
        response.Write(stringWrite.ToString)
        response.End()
    End Sub

    Public Shared Sub Convert(ByVal ds As DataSet, ByVal TableIndex As
Integer, ByVal response As HttpResponse)
        'lets make sure a table actually exists at the passed in value
        'if it is not call the base method
        If TableIndex > ds.Tables.Count - 1 Then
            Convert(ds, response)
        End If
        'we've got a good table so
        'let's clean up the response.object
        response.Clear()
        response.Charset = ""
        'set the response mime type for excel
        response.ContentType = "application/vnd.ms-excel"
        'create a string writer
        Dim stringWrite As New System.IO.StringWriter
        'create an htmltextwriter which uses the stringwriter
        Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
        'instantiate a datagrid
        Dim dg As New DataGrid
        'set the datagrid datasource to the dataset passed in
        dg.DataSource = ds.Tables(TableIndex)
        'bind the datagrid
        dg.DataBind()
        'tell the datagrid to render itself to our htmltextwriter
        dg.RenderControl(htmlWrite)
        'all that's left is to output the html
        response.Write(stringWrite.ToString)
        response.End()
    End Sub

    Public Shared Sub Convert(ByVal ds As DataSet, ByVal TableName As
String, ByVal response As HttpResponse)
        'let's make sure the table name exists
        'if it does not then call the default method
        If ds.Tables(TableName) Is Nothing Then
            Convert(ds, response)
        End If
        'we've got a good table so
        'let's clean up the response.object
        response.Clear()
        response.Charset = ""
        'set the response mime type for excel
        response.ContentType = "application/vnd.ms-excel"
        'create a string writer
        Dim stringWrite As New System.IO.StringWriter
        'create an htmltextwriter which uses the stringwriter
        Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
        'instantiate a datagrid
        Dim dg As New DataGrid
        'set the datagrid datasource to the dataset passed in
        dg.DataSource = ds.Tables(TableName)
        'bind the datagrid
        dg.DataBind()
        'tell the datagrid to render itself to our htmltextwriter
        dg.RenderControl(htmlWrite)
        'all that's left is to output the html
        response.Write(stringWrite.ToString)
        response.End()
    End Sub

End Class

One thing that left is to put simple heading at the top of the spread***
i.e.

COMPANY BLAH
REPORT BLAH
Run on Today()

I tried to add something like this to the SQL statement in OracleDataAdapter
configuration wizard:

Select 'Company BLAH' from dual
UNION ALL
select .... (my query)

but that generates error and creates other problems related to using UNION
i.e. number of columns must be the same, datatypes must be the same, etc.

Has anybody done this?

Thanks

Roman