将整个数据库导出到 Excel 文件

• 下载示例项目 - 363 KB

引言

首先，我需要感谢 Marc Brooks 发布的代码，这使得我的代码比最初快了大约一百倍！

这段代码的作用：正如标题所述，这段代码能够从任何给定的 SQL Server 数据库中提取所有表和数据，并将其导出到 Excel！每个表都有自己的工作表。我一直在网上搜索这样的程序，但没有找到任何（免费）版本。所以我决定自己编写它。

使用代码

要使这段代码工作，您需要通过“项目”中的“添加引用”并从 COM 选项卡中选择 Microsoft Excel 9.0（或 10.0）对象库来添加对 Excel.dll 的引用。

然后，导入以下命名空间

Imports System.Runtime.InteropServices.Marshal

现在将以下类添加到您的项目中

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
        Next
        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 
            tblSheet.Range("A2").CopyFromRecordset(_
               ConvertToRecordset(dsTables.Tables(i)))
        End If
        xlCells.Columns.AutoFit()
    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)
    Catch ex As Exception
    End Try 
End Sub

为了将 DataTable 转换为记录集，使用了以下两个类

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

现在，从数据库中获取所有表和数据的技巧

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

可下载示例

我已将示例项目添加到本文中。该项目是使用 Visual Studio 2005 创建的。

请务必检查 web.config 文件，并在需要时编辑 SQL Server 设置。如果您有一个基本的 SQL Server 作为（本地）服务器运行，并使用集成安全性，并且您安装了 Northwind 数据库，您可以直接运行它，它就可以工作了。

如果您需要代码以 C# 编写，只需在 Google 上搜索“将 VB.NET 转换为 C#”。

限制

不幸的是，这段代码有一些限制。这与 Excel 的限制有关。只要表结构和数据符合以下列表，一切都应该正常工作

主题	局限性
工作表大小	最大 65536 行，256 列。
单元格内容（文本）	一个单元格中最多 1024 个字符。
工作表数量	受可用内存限制。
SQL 时间戳字段	项目目前无法处理 SQL 时间戳字段（尚未）。但我相信一定有办法使其工作。

就这样了！！祝您编码愉快！

历史

• 2008/03/10 - 第一个版本。
• 2008/03/12 - 添加了限制列表。
• 2008/03/14 - 通过将数据表转换为记录集并将数据直接射入 Excel 表格，而不是逐个单元格插入数据，使代码快了大约一百倍。
• 2008/03/19 - 添加了 VS2005 VB.NET 示例项目。