Introduction
First of all, I need to give some credit to Marc Brooks for publishing code which made it possible for me to make my code about a hundred times faster than it originally was!
What does this code do: as the title says, this code is capable of extracting all tables and data from any given SQL Server database and exporting it to Excel! Every table gets its own worksheet. I was searching the net for a program like this, but I didn't come across any (free) versions. So I decided to write it myself.
Using the code
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
dsTables = getAllTables(sDatabaseName)
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 = Server.MapPath(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
For iCol As Integer = 0 To dsTables.Tables(i).Columns.Count - 1
xlCells(1, iCol + 1) = dsTables.Tables(i).Columns(iCol).ToString
xlCells(1).EntireRow.Font.Bold = True
Next
If dsTables.Tables(i).Rows.Count > 0 Then
tblSheet.Range("A2").CopyFromRecordset(_
ConvertToRecordset(dsTables.Tables(i)))
End If
xlCells.Columns.AutoFit()
Next
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
xlBook.SaveAs(sFile)
xlBook.Close()
xlExcel.Quit()
ReleaseComObject(xlCells)
ReleaseComObject(tblSheet)
ReleaseComObject(xlBook)
ReleaseComObject(xlBooks)
ReleaseComObject(xlExcel)
xlExcel = Nothing
xlBooks = Nothing
xlBook = Nothing
tblSheet = Nothing
xlCells = Nothing
GC.Collect()
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)
Catch ex As Exception
End Try
End Sub
To convert the DataTable
to a recordset, the following two classes are used:
Private Shared Function ConvertToRecordset(ByVal inTable As DataTable) As ADODB.Recordset
Dim result As New ADODB.Recordset()
result.CursorLocation = ADODB.CursorLocationEnum.adUseClient
Dim resultFields As ADODB.Fields = result.Fields
Dim inColumns As System.Data.DataColumnCollection = inTable.Columns
For Each inColumn As DataColumn In inColumns
resultFields.Append(inColumn.ColumnName, TranslateType(inColumn.DataType), _
inColumn.MaxLength, IIf(inColumn.AllowDBNull, _
ADODB.FieldAttributeEnum.adFldIsNullable, _
ADODB.FieldAttributeEnum.adFldUnspecified), Nothing)
Next
result.Open(System.Reflection.Missing.Value, _
System.Reflection.Missing.Value, ADODB.CursorTypeEnum.adOpenStatic, _
ADODB.LockTypeEnum.adLockOptimistic, 0)
For Each dr As DataRow In inTable.Rows
result.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value)
For columnIndex As Integer = 0 To inColumns.Count - 1
resultFields(columnIndex).Value = dr(columnIndex)
Next
Next
Return result
End Function
Private Shared Function TranslateType(ByVal columnType As Type) As ADODB.DataTypeEnum
Select Case columnType.UnderlyingSystemType.ToString()
Case "System.Boolean"
Return ADODB.DataTypeEnum.adBoolean
Case "System.Byte"
Return ADODB.DataTypeEnum.adUnsignedTinyInt
Case "System.Char"
Return ADODB.DataTypeEnum.adChar
Case "System.DateTime"
Return ADODB.DataTypeEnum.adDate
Case "System.Decimal"
Return ADODB.DataTypeEnum.adCurrency
Case "System.Double"
Return ADODB.DataTypeEnum.adDouble
Case "System.Int16"
Return ADODB.DataTypeEnum.adSmallInt
Case "System.Int32"
Return ADODB.DataTypeEnum.adInteger
Case "System.Int64"
Return ADODB.DataTypeEnum.adBigInt
Case "System.SByte"
Return ADODB.DataTypeEnum.adTinyInt
Case "System.Single"
Return ADODB.DataTypeEnum.adSingle
Case "System.UInt16"
Return ADODB.DataTypeEnum.adUnsignedSmallInt
Case "System.UInt32"
Return ADODB.DataTypeEnum.adUnsignedInt
Case "System.UInt64"
Return ADODB.DataTypeEnum.adUnsignedBigInt
Case "System.String"
Return ADODB.DataTypeEnum.adVarChar
Case Else
Return ADODB.DataTypeEnum.adVarChar
End Select
End Function
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
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)
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
Downloadable example
I've added an example project to this article. The project was created using Visual Studio 2005.
Be sure to check the web.config file, and edit the SQL Server settings when needed. If you have a basic SQL Server running as a (local) server with Integrated Security, and if you have the Northwind database installed, you can just run it and it'll work.
In case you need the code to be in C#, just Google for 'Convert VB.NET to C#'.
Limitations
Unfortunately, there are some limitations to this code. It has everything to do with limitations to Excel. As long as the table structure and data conform to the following list, everything should work OK:
Subject | Limitation |
Worksheet size | Max 65536 rows, 256 columns. |
Cell content (text) | Only 1024 characters in a cell. |
Amount of Worksheets | Limited by available memory. |
SQL TimeStamp field | Project can't handle SQL TimeStamp field (yet). But I'm sure there is a way to get it working. |
That's all there is to it!! Happy coding!
History
- 03/10/2008 - First version.
- 03/12/2008 - Added limitations list.
- 03/14/2008 - Made the code about a hundred times faster by converting the datatable to a recordset and shooting the data right into the Excel sheet, instead of inserting data cell by cell.
- 03/19/2008 - Added a VS2005 VB.NET sample project.