如何在VB.Net中使用数据库查询创建Excel文件？

I would like to generate an Excel file using a database query in VB.Net. How can I do it?

我想在VB.Net中使用数据库查询生成Excel文件。我该怎么做？

To be more precise: I would like to "bind" a query (much like binding a query to a GridView) to an Excel file such that the rows in the table occupy corresponding cells in a new Excel file, and save the file to my computer. And then, mail that file to someone.

更确切地说：我想将查询“绑定”（很像将查询绑定到GridView）到Excel文件，以便表中的行占用新Excel文件中的相应单元格，并将文件保存到我的文件中。电脑。然后，将该文件邮寄给某人。

While I can handle the mailing part, it's the creation of such a file that I need help with. Anyone know how to achieve what I want to achieve?

虽然我可以处理邮件部分，但是我需要帮助创建这样一个文件。任何人都知道如何实现我想要实现的目标？

PS: I need to do this in VB.Net and I'm using SQL Server 2008.

PS：我需要在VB.Net中这样做，我正在使用SQL Server 2008。

3 个解决方案

#1

OK this isn't perfect but it should get you started. First of all you will want to add a reference to the version of Excel you are using. In my case it is 12.0 (2007) but this code should work with the last two or three version with a minor change or two. At the top of your page add this

好吧这不是完美但它应该让你开始。首先，您需要添加对正在使用的Excel版本的引用。在我的情况下它是12.0（2007）但是这个代码应该适用于最后两个或三个版本，只有一两个小的改动。在页面顶部添加此项

Imports Microsoft.Office.Interop

导入Microsoft.Office.Interop

Next add a function to create a datatable

接下来添加一个函数来创建数据表

Public Function CreateTable() As DataTable
    Dim cn As New SqlConnection(My.Settings.con)
    Dim cmd As New SqlCommand
    Using da As New SqlDataAdapter()
        Dim dt As New DataTable()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "[dbo].[MyStoredProcedure]"
        cmd.CommandTimeout = 0
        cn.Open()
        cmd.Connection = cn
        da.SelectCommand = cmd
        da.Fill(dt)
        cn.Close()
        Return dt
    End Using
End Function

Next the code to take that DataTable and dump it into Excel.

接下来获取DataTable并将其转储到Excel中的代码。

Public Shared Sub PopulateSheet(ByVal dt As DataTable, ByVal File As String)
            Dim oXL As Excel.Application = CType(CreateObject("Excel.Application"), Excel.Application)
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Dim oRng As Excel.Range
        oXL.Visible = True

        oWB = oXL.Workbooks.Add
        oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)

        Dim dc As DataColumn
        Dim dr As DataRow
        Dim colIndex As Integer = 0
        Dim rowIndex As Integer = 0
        For Each dc In dt.Columns
            colIndex = colIndex + 1
            oXL.Cells(1, colIndex) = dc.ColumnName
        Next
        For Each dr In dt.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            For Each dc In dt.Columns
                colIndex = colIndex + 1
                oXL.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
            Next
        Next

        oSheet.Cells.Select()
        oSheet.Columns.AutoFit()
        oSheet.Rows.AutoFit()

        oXL.Visible = True
        oXL.UserControl = True

        oWB.SaveAs(File)
        oRng = Nothing
        oXL.Quit()

        ExcelCleanUp(oXL, oWB, oSheet)
    End Sub

Now you can call it from a button or whatever event you choose with this

现在，您可以通过按钮或您选择的任何事件来调用它

    Dim dt As New DataTable
    Try
        dt = CreateTable()
        PopulateSheet(dt, "c:\test\ExcelFile.xlsx")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        dt.Dispose()
    End Try

Now this is really basic but with a little work you can do cell formatting, page setup and just about anything that can be done inside Excel with the menus/options.

现在这是非常基本的，但通过一些工作，您可以进行单元格格式化，页面设置以及可以使用菜单/选项在Excel中完成的任何事情。

We should also finish this out by adding code to clean things up.

我们还应该通过添加代码来完成清理工作。

Private Shared Sub ExcelCleanUp( _
    ByVal oXL As Excel.Application, _
    ByVal oWB As Excel.Workbook, _
    ByVal oSheet As Excel.Worksheet)

    GC.Collect()
    GC.WaitForPendingFinalizers()

    Marshal.FinalReleaseComObject(oXL)
    Marshal.FinalReleaseComObject(oSheet)
    Marshal.FinalReleaseComObject(oWB)

    oSheet = Nothing
    oWB = Nothing
    oXL = Nothing

End Sub

#2

You can automate Excel in VB.Net and put those values into the spreadsheet via code:

您可以在VB.Net中自动化Excel并通过代码将这些值放入电子表格中：

#3

You can find some excellent articles about this on CodeProject:

你可以在CodeProject上找到一些关于这方面的优秀文章：

Excel Export Component Using XSL: In this article you can find a component to export data to Excel whithout using any office component.

使用XSL导出Excel组件：在本文中，您可以找到一个组件，可以在不使用任何office组件的情况下将数据导出到Excel。

Export to Excel using VB.Net: In this one the author uses Excel automation to do the job.

使用VB.Net导出到Excel：在这一个中，作者使用Excel自动化来完成这项工作。

#1