Click here to Skip to main content
11,813,557 members (46,891 online)
Click here to Skip to main content

Export a complete database to an Excel file

, 19 Mar 2008 CPOL 72.4K 1.6K 84
Rate this:
Please Sign up or sign in to vote.
Export all tables and data to an Excel file.


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

    '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 files
    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
        If dsTables.Tables(i).Rows.Count > 0 Then
            'With ConvertToRecordset the datatable is converted to a recordset
            'Then with CopyFromRecordset the entire recordset can be inserted at once 
        End If

    'Remove initial excel sheets. Within a try catch because the database 
    'could be empty (a workbook without worksheets is not allowed)
        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

    'Make sure all objects are disposed
    xlExcel = Nothing
    xlBooks = Nothing
    xlBook = Nothing
    tblSheet = Nothing
    xlCells = Nothing

    'Let the Garbage Collector know it can get to work

    'Export Excel for download
        HttpContext.Current.Response.ContentType = "application/octet-stream"
        HttpContext.Current.Response.AddHeader("Content-Disposition", _
                    "attachment; filename=" + _
    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)

    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)
    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 
        database = sDB 
        Dim m_dshelp As DataSet = New DataSet
        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] =, [RowCount] = MAX(si.rows) " & _
    "FROM sysobjects so, sysindexes si " & _
    "WHERE so.xtype = 'U' AND = OBJECT_ID( AND si.rows > 0 " & _
    "GROUP BY " & _
    "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)
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)
        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#'.


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!


  • 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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Dennis Betten
Software Developer (Senior) Centric Netherlands
Netherlands Netherlands
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralNeed C# Pin
ravanth5-May-10 0:49
memberravanth5-May-10 0:49 
GeneralRe: Need C# Pin
FernandoUY30-Jul-13 6:52
memberFernandoUY30-Jul-13 6:52 
GeneralRecompile MS Interop Pin
pokiswork29-Dec-09 8:49
memberpokiswork29-Dec-09 8:49 
QuestionExport to Excel file ... Pin
Ganesan 200928-Jul-09 3:08
memberGanesan 200928-Jul-09 3:08 
AnswerRe: Export to Excel file ... Pin
Dennis Betten2-Aug-09 21:18
memberDennis Betten2-Aug-09 21:18 
AnswerRe: Export to Excel file ... Pin
gg423713-Nov-09 0:33
membergg423713-Nov-09 0:33 
QuestionIndexing xlCells Pin
Phellipe Schelotag29-Jul-08 2:17
memberPhellipe Schelotag29-Jul-08 2:17 
QuestionStill need HELP Pin
Phellipe Schelotag24-Jul-08 9:40
memberPhellipe Schelotag24-Jul-08 9:40 
AnswerRe: Still need HELP Pin
Dennis Betten24-Jul-08 20:51
memberDennis Betten24-Jul-08 20:51 
Hello Phellipe,

I guess the problem is that my code has been written for VS2005. I suppose some objects and instances are unknown in VS2003. Unfortunately I don't have a VS2003 installation on any of my PC, so I'm unable to recreate your problem.
I will try to get my hands on a VS2003 CD, but in the mean time I'm afraid I can't help you.

To be continued...

GeneralRe: Still need HELP Pin
Phellipe Schelotag25-Jul-08 2:14
memberPhellipe Schelotag25-Jul-08 2:14 
GeneralExcel Automation Pin
BarbaMariolino15-May-08 0:39
memberBarbaMariolino15-May-08 0:39 
GeneralRe: Excel Automation Pin
Dennis Betten15-May-08 2:46
memberDennis Betten15-May-08 2:46 
GeneralUnspecified error (Exception from HRESULT: 0x80004005 (E_FAIL)) on 5th table Pin
jmoschak25-Mar-08 11:49
memberjmoschak25-Mar-08 11:49 
GeneralRe: Unspecified error (Exception from HRESULT: 0x80004005 (E_FAIL)) on 5th table Pin
Dennis Betten25-Mar-08 14:17
memberDennis Betten25-Mar-08 14:17 
Generalfaster conversion DataSet.CreateDataReader Pin
Thanks for all the fish20-Mar-08 4:25
memberThanks for all the fish20-Mar-08 4:25 
GeneralRe: faster conversion DataSet.CreateDataReader Pin
Dennis Betten21-Mar-08 1:14
memberDennis Betten21-Mar-08 1:14 
GeneralVery nice! Example project works great. Pin
KevinStein19-Mar-08 14:29
memberKevinStein19-Mar-08 14:29 
QuestionNice work! How about some demo code... Pin
oneshoe18-Mar-08 4:31
memberoneshoe18-Mar-08 4:31 
GeneralRe: Nice work! How about some demo code... Pin
Dennis Betten18-Mar-08 4:40
memberDennis Betten18-Mar-08 4:40 
GeneralRe: Nice work! How about some demo code... Pin
oneshoe18-Mar-08 5:53
memberoneshoe18-Mar-08 5:53 
GeneralRe: Nice work! How about some demo code... Pin
Dennis Betten19-Mar-08 12:30
memberDennis Betten19-Mar-08 12:30 
GeneralRe: Nice work! How about some demo code... Pin
oneshoe20-Mar-08 3:27
memberoneshoe20-Mar-08 3:27 
GeneralGreat!! Pin
Gerrit de Vries15-Mar-08 0:53
memberGerrit de Vries15-Mar-08 0:53 
GeneralRe: Great!! Pin
Dennis Betten15-Mar-08 1:10
memberDennis Betten15-Mar-08 1:10 
GeneralRe: Great!! Pin
Evan Nickels2-Apr-08 1:17
memberEvan Nickels2-Apr-08 1:17 
GeneralNot for very big dbs though Pin
Captain Scarlet10-Mar-08 5:29
memberCaptain Scarlet10-Mar-08 5:29 
GeneralRe: Not for very big dbs though [modified] Pin
Dennis Betten10-Mar-08 5:51
memberDennis Betten10-Mar-08 5:51 
GeneralRe: Not for very big dbs though Pin
Member 164393918-Mar-08 1:37
memberMember 164393918-Mar-08 1:37 
GeneralThis would have been very usefull on my previous project Pin
David B. Taylor10-Mar-08 5:05
memberDavid B. Taylor10-Mar-08 5:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.151002.1 | Last Updated 19 Mar 2008
Article Copyright 2008 by Dennis Betten
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid