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:
- Imports System.Runtime.InteropServices.Marshal
Now add the following class to your project:
- Private Sub create(ByVal sDatabaseName As String)
-
Dim dsTables As DataSet = New DataSet
-
-
'Get all Tables from database
-
dsTables = getAllTables(sDatabaseName)
-
'Create Excel Application, Workbook, and WorkSheets
-
Dim xlExcel As New Excel.Application
-
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.DisplayAlerts = False
-
xlBooks = xlExcel.Workbooks
-
xlBook = xlBooks.Add
-
For i As Integer = 0 To dsTables.Tables.Count - 1
-
tblSheet = xlBook.Worksheets.Add
-
tblSheet.Name = dsTables.Tables(i).TableName
-
xlCells = tblSheet.Cells
-
'Fill all cells with data
-
GenerateExcelFile(dsTables.Tables(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.Sheets(SheetCount - 0), Excel.Worksheet).Delete()
-
CType(xlExcel.Sheets(SheetCount - 1), Excel.Worksheet).Delete()
-
CType(xlExcel.Sheets(SheetCount - 2), Excel.Worksheet).Delete()
-
Catch ex As Exception
-
End Try
-
'Save the excel file
-
xlBook.SaveAs(sFile)
-
'Make sure all objects are disposed
-
xlBook.Close()
-
xlExcel.Quit()
-
ReleaseComObject(xlCells)
-
ReleaseComObject(tblSheet)
-
ReleaseComObject(xlBook)
-
ReleaseComObject(xlBooks)
-
ReleaseComObject(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.Current.Response.ContentType = "application/octet-stream"
-
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.IO.Path.GetFileName(sFile))
-
HttpContext.Current.Response.Clear()
-
HttpContext.Current.Response.WriteFile(sFile)
-
HttpContext.Current.Response.End()
-
Catch ex As Exception
-
'An exception will be thrown, but can just be ignored
-
End Try
-
End Sub
To generate the individual sheets, the following Sub is used:
- Private Sub GenerateExcelFile(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.Count - 1
-
xlCells(1, iCol + 1) = table.Columns(iCol).ToString
-
xlCells(1).EntireRow.Font.Bold = True
-
Next
-
'Output Data
-
For iRow = 0 To table.Rows.Count - 1
-
dr = table.Rows.Item(iRow)
-
ary = dr.ItemArray
-
For iCol = 0 To UBound(ary)
-
xlCells(iRow + 2, iCol + 1) = ary(iCol).ToString
-
Response.Write(ary(iCol).ToString & vbTab)
-
Next
-
Next
-
xlCells.Columns.AutoFit()
-
End Sub
And now the trick to getting all tables and data from a database:
- Public database as String
-
Public ReadOnly Property getAllTables(ByVal sDB As String) As DataSet
-
Get
-
database = sDB
-
Dim m_dshelp As DataSet = New DataSet
-
getRequestedAllTables(m_dshelp)
-
Return m_dshelp
-
End Get
-
End Property
-
-
Private Function getRequestedAllTables(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.name) 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).Item(0)
-
getData(sSQL, dsTables.Tables(0).Rows(i).Item(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_sql, getConnection)
-
objDataAdapter = New SqlDataAdapter(objcommand)
-
objDataAdapter.Fill(pdataset, p_table)
-
End Function
-
-
Private Function getConnection() As SqlConnection
-
If (ConfigurationManager.AppSettings("SQLPW") <> "") Then
-
getConnection = New SqlConnection("Server=" & _
-
ConfigurationManager.AppSettings("SQLserver") & ";password=" & _
-
ConfigurationManager.AppSettings("SQLPW") & "; user=" & _
-
ConfigurationManager.AppSettings("SQLUser") & ";database=" & database)
-
Else
-
getConnection = New SqlConnection("Data Source=" & _
-
ConfigurationManager.AppSettings("SQLserver") & ";Initial Catalog=" & _
-
database & ";Integrated Security=True")
-
End If
-
End Function
That's all there is to it!! Happy Coding!
Export complete database to excel file 
