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> "
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