Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done
Option Strict On
Option Explicit On
 
Partial Public Class myWebForm
    Inherits System.Web.UI.Page
 
  

 

Dim _objDatasetToExcel As New DatasetToExcel()  Dim _showExcel As Boolean = False
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            '---------------------------------------------------------------------------
            'build showEnlargeButton
            '---------------------------------------------------------------------------
            If Request.QueryString("showExcel") Is Nothing Then
                Dim x As String = "<afont-size: xx-small;"" href=""Javascript:void(0)"" onclick=""window.open('myWebForm.aspx?showExcel=true', 'Show_Excel','width=700,height=500,resizable=1,status=1,toolbar=1,menubar=1,scrollbars=1,location=0');""><font color=blue><u>Export Graph Data to Excel</u></font></a>&nbsp;&nbsp;"
                Response.Write(x)
            End If
 
            '---------------------------------------------------------------------------
            'getSomeData
            '---------------------------------------------------------------------------
            Dim dataDs As DataSet = _
                        objDataAccess.getSomeData( _
                            Now.Year, _
                            "range", _
                            Coupon_Id, _
                            Start_Date.ToString("M/d/yyyy"), _
                            End_Date.ToString("M/d/yyyy"), _
                            sSqlData)
 
 
            Dim dataDr As DataRow = dataDs.Tables(0).Rows(0)
            '-------------------------------------------------------------------------
            'graph.setseries(Prints;54;75;85)
            'graph.setseries(Redemptions;92;63;70)
            '-------------------------------------------------------------------------
            Dim Prints As Integer = Integer.Parse(dataDr("PrintsSum").ToString)
            Dim Redemptions As Integer = Integer.Parse(dataDr("RedemptionsSum").ToString)
 
            Dim arr() As String = { _
                Coupon_Name, _
                    Start_Date.ToString("M/d/yyyy"), _
                    End_Date.ToString("M/d/yyyy"), _
                Prints.ToString, _
                Redemptions.ToString}
            _objDatasetToExcel.AddRow("Coupon Bar Chart", _
                "Coupon;Start Date;End Date;Prints;Redemptions", _
                arr)
 
                Next kvp
DisplayData()
allDone:
        Catch ex As Exception
        End Try
    End Sub
 
    Private Sub DisplayData()
        Try
            If Me._showExcel Then
                Me._objDatasetToExcel.Convert(Response)
            Else
            End If
 
        Catch ex As Exception
 
        End Try
 
    End Sub
 
End Class
 
Imports System
Imports System.IO
Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports Excel
Imports System.Runtime.InteropServices
Public Class DatasetToExcel
    Dim _ds As New DataSet
    Private Sub AddTable(ByVal tableName As String, ByVal inColumnNames As String)
        Try
            If _ds.Tables.IndexOf(tableName) = -1 Then
 
                Dim dataTable As New System.Data.DataTable(tableName)
 
                '---------------------------------------------------------------------------
                '
                '---------------------------------------------------------------------------
                Dim columnNamesArr As String() = Split(inColumnNames, ";")
                Dim i As Integer
                For i = LBound(columnNamesArr) To UBound(columnNamesArr)
                    ' Declare variables for DataColumn and DataRow objects.
                    Dim column As DataColumn
 
                    ' Create new DataColumn, set DataType, ColumnName
                    ' and add to DataTable.   
                    column = New DataColumn()
                    column.DataType = System.Type.GetType("System.String")
                    column.ColumnName = columnNamesArr(i).Trim
                    'column.ReadOnly = True
                    'column.Unique = True
 
                    ' Add the Column to the DataColumnCollection.
                    dataTable.Columns.Add(column)
                Next
                _ds.Tables.Add(dataTable)
            End If
        Catch ex As Exception
            'System.Diagnostics.Debug.WriteLine(theException.Message)
            Throw ex
        Finally
        End Try
    End Sub
    Public Sub AddRow(ByVal tableName As String, ByVal inColumnNames As String, ByVal inDataArray As String())
        Try
            Me.AddTable(tableName, inColumnNames)
            'Dim dataTable As System.Data.DataTable = _ds.Tables(tableName)
            'Dim dataRow = _ds.Tables(tableName).NewRow()
            'Dim i As Integer = 0
            'For i = LBound(inDataArray) To UBound(inDataArray)
            '    Dim x As String = inDataArray(i)
            '    dataRow(i) = x
            'Next i
            _ds.Tables(tableName).Rows.Add(inDataArray)
        Catch ex As Exception
            'System.Diagnostics.Debug.WriteLine(theException.Message)
            Throw ex
        Finally
        End Try
    End Sub
 
    Public Sub WriteDatasetToExcel( _
        ByVal inTemplateFolder As String, _
       ByVal inWriteFolder As String, _
         ByVal inFileName As String)
        '---------------------------------------------------------------
        '
        '---------------------------------------------------------------
        Dim oXL As Application = Nothing
        Dim oWb As _Workbook = Nothing
        Dim oSheet As _Worksheet = Nothing
        Dim oRng As Range = Nothing
        Try
            oXL = New Application()
            oXL.Visible = False
            'Get a new workbook.
            oWb = CType(oXL.Workbooks.Add(inTemplateFolder + "\\template.xls"), _Workbook)
            oSheet = CType(oWb.ActiveSheet, _Worksheet)
            'System.Data.DataTable dtGridData=_ds.Tables(0)
            Dim iRow As Integer = 2
            Dim i As Integer = 0
            '---------------------------------------------------------------
            'loop thru dataset tables
            '---------------------------------------------------------------
            For i = 0 To _ds.Tables.Count - 1
 
                '---------------------------------------------------------------
                'wtite Table Name
                '---------------------------------------------------------------
                'oSheet.Cells(iRow, 1) = _ds.Tables(i).TableName
                Dim xlRange As Excel.Range = CType(oSheet.Cells(iRow, 1), Excel.Range)
                '' Bold the current row.
                'xlRange.EntireRow.Font.Bold = True
 
                '---------------------------------------------------------------
                'any records?
                '---------------------------------------------------------------
                'iRow += 1
                If (_ds.Tables(i).Rows.Count < 1) Then
 
                    '---------------------------------------------------------------
                    '<no records>
                    '---------------------------------------------------------------
                    oSheet.Cells(iRow, 1) = "<no records>"
                    iRow += 1
                Else
 
                    '---------------------------------------------------------------
                    'build column names row
                    '---------------------------------------------------------------
                    Dim j As Integer = 0
                    For j = 0 To _ds.Tables(i).Columns.Count - 1
 
                        oSheet.Cells(iRow, j + 1) = _ds.Tables(i).Columns(j).ColumnName
                        'oSheet.Cells(iRow, j + 1).Font.FontStyle = System.Drawing.FontStyle.Bold
 
                        xlRange = CType(oSheet.Cells(iRow, j + 1), Excel.Range)
                        ' Bold the current row.
                        xlRange.EntireRow.Font.Bold = True
                    Next
 
                    iRow += 1
 
                    '---------------------------------------------------------------
                    'build data row
                    'loop thru dataset table rows
                    '---------------------------------------------------------------
                    Dim rowNo As Integer = 0
                    For rowNo = 0 To _ds.Tables(i).Rows.Count - 1
 
                        '---------------------------------------------------------------
                        'loop thru table columns
                        ' print into cells the values of each column.
                        '---------------------------------------------------------------
                        Dim colNo As Integer = 0
                        For colNo = 0 To _ds.Tables(i).Columns.Count - 1
 
                            oSheet.Cells(iRow, colNo + 1) = _ds.Tables(i).Rows(rowNo)(colNo).ToString()
                        Next
                        iRow += 1
                    Next
                End If
                '---------------------------------------------------------------
                'skip a row for next dataset table
                '---------------------------------------------------------------
                iRow += 1
            Next
 
            '---------------------------------------------------------------
            'style the sheet
            '---------------------------------------------------------------
            oRng = oSheet.Range("A1", "IV1")
            oRng.EntireColumn.AutoFit()
            oXL.Visible = False
            oXL.UserControl = False
            Dim duhSaveFile As String = inWriteFolder + inFileName
 
            '---------------------------------------------------------------
            'delete older file version
            '---------------------------------------------------------------
            Try
                File.Delete(duhSaveFile)
            Catch
            End Try
            '---------------------------------------------------------------
            'save newer file version
            '---------------------------------------------------------------
            oWb.SaveAs(duhSaveFile, XlFileFormat.xlWorkbookNormal, Nothing, Nothing, False, False, XlSaveAsAccessMode.xlShared, False, False, Nothing, Nothing, Nothing)
            ' Need all following code to clean up and remove all references!!!
            oWb.Close(Nothing, Nothing, Nothing)
            oXL.Workbooks.Close()
            oXL.Quit()
 
        Catch ex As Exception
            'System.Diagnostics.Debug.WriteLine(theException.Message)
            Throw ex
        Finally
            '---------------------------------------------------------------
            'ReleaseComObjects
            '---------------------------------------------------------------
            'Marshal.ReleaseComObject(oRng)
            'Marshal.ReleaseComObject(oXL)
            'Marshal.ReleaseComObject(oSheet)
            'Marshal.ReleaseComObject(oWb)
        End Try
 
    End Sub
    Public Sub Convert(ByVal response As HttpResponse)
        Try
            '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
            'instantiate a datagrid
            Dim dg As New DataGrid
            'set the datagrid datasource to the dataset passed in
            Dim i As Integer = 0
            For i = 0 To _ds.Tables.Count - 1
 
 
 
                '---------------------------------------------------------------------------
                'table name
                '---------------------------------------------------------------------------
                Dim littleDs As New DataSet
                Dim dataTable As New System.Data.DataTable(_ds.Tables(i).TableName)
                Dim column As DataColumn
 
                ' Create new DataColumn, set DataType, ColumnName
                ' and add to DataTable.   
                column = New DataColumn()
                column.DataType = System.Type.GetType("System.String")
                column.ColumnName = "Group"
                'column.ReadOnly = True
                'column.Unique = True
 
                ' Add the Column to the DataColumnCollection.
                dataTable.Columns.Add(column)
                littleDs.Tables.Add(dataTable)
                Dim arr() As String = {_ds.Tables(i).TableName}
                littleDs.Tables(0).Rows.Add(arr)
                Dim stringWrite As New System.IO.StringWriter
                'create an htmltextwriter which uses the stringwriter
                Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
                dg.DataSource = littleDs.Tables(0)
                'bind the datagrid
                dg.DataBind()
                'tell the datagrid to render itself to our htmltextwriter
                dg.RenderControl(htmlWrite)
                response.Write(stringWrite.ToString)
 
                '---------------------------------------------------------------------------
                'actual data
                '---------------------------------------------------------------------------
                stringWrite = New System.IO.StringWriter
                'create an htmltextwriter which uses the stringwriter
                htmlWrite = New System.Web.UI.HtmlTextWriter(stringWrite)
                dg.DataSource = _ds.Tables(i)
                '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)
            Next
            response.End()
        Catch ex As Exception
            Dim x As String = ex.ToString
            System.Diagnostics.Debug.WriteLine(ex.Message)
            'Throw ex
        Finally
        End Try
    End Sub
    Public Sub ConvertOneTable(ByVal response As HttpResponse)
        Try
            '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()
        Catch ex As Exception
            Dim x As String = ex.ToString
            System.Diagnostics.Debug.WriteLine(ex.Message)
            'Throw ex
        Finally
        End Try
    End Sub
 
    Private Sub Convert(ByVal TableIndex As Integer, ByVal response As HttpResponse)
        Try
            '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(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()
        Catch ex As Exception
            Dim x As String = ex.ToString
            System.Diagnostics.Debug.WriteLine(ex.Message)
        Finally
        End Try
    End Sub
 
    Private Sub Convert(ByVal TableName As String, ByVal response As HttpResponse)
        Try
            '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(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()
        Catch ex As Exception
            Dim x As String = ex.ToString
            System.Diagnostics.Debug.WriteLine(ex.Message)
        Finally
        End Try
    End Sub
End Class
 
Posted on Friday, January 4, 2008 3:39 AM Excel | Back to top


Comments on this post: WebForm DataSet to Excel

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net