First of all, I need to give some credit to Mahesh Chand for providing me with an excellent basis to export data to excel.
What does this code do:
As the title says, this code is capable of extracting all tables and it's data from any given database! I was searching the net for a program like this, but I didn't come accross any (free) versions. So I decided to write it myself.
To get this code to work, you need to add a reference to Excel.dll by using Add Reference on the project and selecting Microsoft Excel 9.0 (or 10.0) Object Library from the COM tab on the Add Reference dialog.
And then import the following namespace:
[CODE=vbnet]Imports System.Runtime. InteropServices .Marshal[/CODE]
Now add the following class to your project:
[CODE=vbnet]Private Sub create(ByVal sDatabaseName As String)
Dim dsTables As DataSet = New DataSet
'Get all Tables from database
dsTables = getAllTables(sD atabaseName)
'Create Excel Application, Workbook, and WorkSheets
Dim xlExcel As New Excel.Applicati on
Dim xlBooks As Excel.Workbooks
Dim xlBook As Excel.Workbook
Dim tblSheet As Excel.Worksheet
Dim xlCells As Excel.Range
Dim sFile As String
'File name for the excel file
sFile = Server.MapPath( "~\Sheets\" & sDatabaseName & "_data.xls" )
xlExcel.Visible = False : xlExcel.Display Alerts = False
xlBooks = xlExcel.Workboo ks
xlBook = xlBooks.Add
For i As Integer = 0 To dsTables.Tables .Count - 1
tblSheet = xlBook.Workshee ts.Add
tblSheet.Name = dsTables.Tables (i).TableName
xlCells = tblSheet.Cells
'Fill all cells with data
GenerateExcelFi le(dsTables.Tab les(i), xlCells)
Next
'Remove initial excel sheets. Within a try catch because the database
'could be empty (a workbook without worksheets is not allowed)
Try
Dim SheetCount As Integer = xlExcel.Sheets. Count
CType(xlExcel.S heets(SheetCoun t - 0), Excel.Worksheet ).Delete()
CType(xlExcel.S heets(SheetCoun t - 1), Excel.Worksheet ).Delete()
CType(xlExcel.S heets(SheetCoun t - 2), Excel.Worksheet ).Delete()
Catch ex As Exception
End Try
'Save the excel file
xlBook.SaveAs(s File)
'Make sure all objects are disposed
xlBook.Close()
xlExcel.Quit()
ReleaseComObjec t(xlCells)
ReleaseComObjec t(tblSheet)
ReleaseComObjec t(xlBook)
ReleaseComObjec t(xlBooks)
ReleaseComObjec t(xlExcel)
xlExcel = Nothing
xlBooks = Nothing
xlBook = Nothing
tblSheet = Nothing
xlCells = Nothing
'Let the Garbage Collector know it can get to work
GC.Collect()
'Export Excel for download
Try
HttpContext.Cur rent.Response.C ontentType = "applicatio n/octet-stream"
HttpContext.Cur rent.Response.A ddHeader("Conte nt-Disposition", "attachment ; filename=" + System.IO.Path. GetFileName(sFi le))
HttpContext.Cur rent.Response.C lear()
HttpContext.Cur rent.Response.W riteFile(sFile)
HttpContext.Cur rent.Response.E nd()
Catch ex As Exception
'An exception will be thrown, but can just be ignored
End Try
End Sub[/CODE]
To generate the individual sheets, the following Sub is used:
[CODE=vbnet]Private Sub GenerateExcelFi le(ByRef table As DataTable, ByVal xlCells As Excel.Range)
Dim dr As DataRow, ary() As Object
Dim iRow As Integer, iCol As Integer
'Output Column Headers
For iCol = 0 To table.Columns.C ount - 1
xlCells(1, iCol + 1) = table.Columns(i Col).ToString
xlCells(1).Enti reRow.Font.Bold = True
Next
'Output Data
For iRow = 0 To table.Rows.Coun t - 1
dr = table.Rows.Item (iRow)
ary = dr.ItemArray
For iCol = 0 To UBound(ary)
xlCells(iRow + 2, iCol + 1) = ary(iCol).ToStr ing
Response.Write( ary(iCol).ToStr ing & vbTab)
Next
Next
xlCells.Columns .AutoFit()
End Sub[/CODE]
And now the trick to getting all tables and data from a database:
[CODE=vbnet]Public database as String
Public ReadOnly Property getAllTables(By Val sDB As String) As DataSet
Get
database = sDB
Dim m_dshelp As DataSet = New DataSet
getRequestedAll Tables(m_dshelp )
Return m_dshelp
End Get
End Property
Private Function getRequestedAll Tables(ByRef p_dataset As DataSet) As Boolean
'Retrieve all tablenames from the database:
Dim sSQL As String
Dim dsTables As DataSet = New DataSet
sSQL = "SELECT [TableName] = so.name, [RowCount] = MAX(si.rows) " & _
"FROM sysobjects so, sysindexes si " & _
"WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.na me) AND si.rows > 0 " & _
"GROUP BY so.name " & _
"ORDER BY 2 DESC"
getData(sSQL, "Tables", dsTables)
'Loop thrue all tables and do a SELECT *. Then add them to the dataset
For i As Integer = 0 To dsTables.Tables (0).Rows.Count - 1
sSQL = "SELECT * FROM " & dsTables.Tables (0).Rows(i).Ite m(0)
getData(sSQL, dsTables.Tables (0).Rows(i).Ite m(0), p_dataset)
Next
End Function
Private Function getData(ByVal p_sql As String, ByVal p_table As String, ByRef pdataset As DataSet) As Boolean
Dim objDataAdapter As SqlDataAdapter
Dim objcommand As SqlCommand
objcommand = New SqlCommand(p_sq l, getConnection)
objDataAdapter = New SqlDataAdapter( objcommand)
objDataAdapter. Fill(pdataset, p_table)
End Function
Private Function getConnection() As SqlConnection
If (ConfigurationM anager.AppSetti ngs("SQLPW") <> "") Then
getConnection = New SqlConnection(" Server=" & _
ConfigurationMa nager.AppSettin gs("SQLserver" ) & ";password= " & _
ConfigurationMa nager.AppSettin gs("SQLPW") & "; user=" & _
ConfigurationMa nager.AppSettin gs("SQLUser") & ";database= " & database)
Else
getConnection = New SqlConnection(" Data Source=" & _
ConfigurationMa nager.AppSettin gs("SQLserver" ) & ";Initial Catalog=" & _
database & ";Integrate d Security=True")
End If
End Function[/CODE]
That's all there is to it!! Happy Coding!
What does this code do:
As the title says, this code is capable of extracting all tables and it's data from any given database! I was searching the net for a program like this, but I didn't come accross any (free) versions. So I decided to write it myself.
To get this code to work, you need to add a reference to Excel.dll by using Add Reference on the project and selecting Microsoft Excel 9.0 (or 10.0) Object Library from the COM tab on the Add Reference dialog.
And then import the following namespace:
[CODE=vbnet]Imports System.Runtime. InteropServices .Marshal[/CODE]
Now add the following class to your project:
[CODE=vbnet]Private Sub create(ByVal sDatabaseName As String)
Dim dsTables As DataSet = New DataSet
'Get all Tables from database
dsTables = getAllTables(sD atabaseName)
'Create Excel Application, Workbook, and WorkSheets
Dim xlExcel As New Excel.Applicati on
Dim xlBooks As Excel.Workbooks
Dim xlBook As Excel.Workbook
Dim tblSheet As Excel.Worksheet
Dim xlCells As Excel.Range
Dim sFile As String
'File name for the excel file
sFile = Server.MapPath( "~\Sheets\" & sDatabaseName & "_data.xls" )
xlExcel.Visible = False : xlExcel.Display Alerts = False
xlBooks = xlExcel.Workboo ks
xlBook = xlBooks.Add
For i As Integer = 0 To dsTables.Tables .Count - 1
tblSheet = xlBook.Workshee ts.Add
tblSheet.Name = dsTables.Tables (i).TableName
xlCells = tblSheet.Cells
'Fill all cells with data
GenerateExcelFi le(dsTables.Tab les(i), xlCells)
Next
'Remove initial excel sheets. Within a try catch because the database
'could be empty (a workbook without worksheets is not allowed)
Try
Dim SheetCount As Integer = xlExcel.Sheets. Count
CType(xlExcel.S heets(SheetCoun t - 0), Excel.Worksheet ).Delete()
CType(xlExcel.S heets(SheetCoun t - 1), Excel.Worksheet ).Delete()
CType(xlExcel.S heets(SheetCoun t - 2), Excel.Worksheet ).Delete()
Catch ex As Exception
End Try
'Save the excel file
xlBook.SaveAs(s File)
'Make sure all objects are disposed
xlBook.Close()
xlExcel.Quit()
ReleaseComObjec t(xlCells)
ReleaseComObjec t(tblSheet)
ReleaseComObjec t(xlBook)
ReleaseComObjec t(xlBooks)
ReleaseComObjec t(xlExcel)
xlExcel = Nothing
xlBooks = Nothing
xlBook = Nothing
tblSheet = Nothing
xlCells = Nothing
'Let the Garbage Collector know it can get to work
GC.Collect()
'Export Excel for download
Try
HttpContext.Cur rent.Response.C ontentType = "applicatio n/octet-stream"
HttpContext.Cur rent.Response.A ddHeader("Conte nt-Disposition", "attachment ; filename=" + System.IO.Path. GetFileName(sFi le))
HttpContext.Cur rent.Response.C lear()
HttpContext.Cur rent.Response.W riteFile(sFile)
HttpContext.Cur rent.Response.E nd()
Catch ex As Exception
'An exception will be thrown, but can just be ignored
End Try
End Sub[/CODE]
To generate the individual sheets, the following Sub is used:
[CODE=vbnet]Private Sub GenerateExcelFi le(ByRef table As DataTable, ByVal xlCells As Excel.Range)
Dim dr As DataRow, ary() As Object
Dim iRow As Integer, iCol As Integer
'Output Column Headers
For iCol = 0 To table.Columns.C ount - 1
xlCells(1, iCol + 1) = table.Columns(i Col).ToString
xlCells(1).Enti reRow.Font.Bold = True
Next
'Output Data
For iRow = 0 To table.Rows.Coun t - 1
dr = table.Rows.Item (iRow)
ary = dr.ItemArray
For iCol = 0 To UBound(ary)
xlCells(iRow + 2, iCol + 1) = ary(iCol).ToStr ing
Response.Write( ary(iCol).ToStr ing & vbTab)
Next
Next
xlCells.Columns .AutoFit()
End Sub[/CODE]
And now the trick to getting all tables and data from a database:
[CODE=vbnet]Public database as String
Public ReadOnly Property getAllTables(By Val sDB As String) As DataSet
Get
database = sDB
Dim m_dshelp As DataSet = New DataSet
getRequestedAll Tables(m_dshelp )
Return m_dshelp
End Get
End Property
Private Function getRequestedAll Tables(ByRef p_dataset As DataSet) As Boolean
'Retrieve all tablenames from the database:
Dim sSQL As String
Dim dsTables As DataSet = New DataSet
sSQL = "SELECT [TableName] = so.name, [RowCount] = MAX(si.rows) " & _
"FROM sysobjects so, sysindexes si " & _
"WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.na me) AND si.rows > 0 " & _
"GROUP BY so.name " & _
"ORDER BY 2 DESC"
getData(sSQL, "Tables", dsTables)
'Loop thrue all tables and do a SELECT *. Then add them to the dataset
For i As Integer = 0 To dsTables.Tables (0).Rows.Count - 1
sSQL = "SELECT * FROM " & dsTables.Tables (0).Rows(i).Ite m(0)
getData(sSQL, dsTables.Tables (0).Rows(i).Ite m(0), p_dataset)
Next
End Function
Private Function getData(ByVal p_sql As String, ByVal p_table As String, ByRef pdataset As DataSet) As Boolean
Dim objDataAdapter As SqlDataAdapter
Dim objcommand As SqlCommand
objcommand = New SqlCommand(p_sq l, getConnection)
objDataAdapter = New SqlDataAdapter( objcommand)
objDataAdapter. Fill(pdataset, p_table)
End Function
Private Function getConnection() As SqlConnection
If (ConfigurationM anager.AppSetti ngs("SQLPW") <> "") Then
getConnection = New SqlConnection(" Server=" & _
ConfigurationMa nager.AppSettin gs("SQLserver" ) & ";password= " & _
ConfigurationMa nager.AppSettin gs("SQLPW") & "; user=" & _
ConfigurationMa nager.AppSettin gs("SQLUser") & ";database= " & database)
Else
getConnection = New SqlConnection(" Data Source=" & _
ConfigurationMa nager.AppSettin gs("SQLserver" ) & ";Initial Catalog=" & _
database & ";Integrate d Security=True")
End If
End Function[/CODE]
That's all there is to it!! Happy Coding!
Comment