使用Vb.net将整个Sql server 2005数据库导出到Excel ........... [英] Export whole Sql server 2005 database to Excel using Vb.net...........

问题描述

在我的桌面应用程序中，我想直接从SQL数据库导出数据到带有自定义标题的MS Excel。我想将包含许多表的数据库导出到excel 2007.

当用户点击时在导出按钮上，它将使用VB.Net直接将数据导出到Excel中。我可以实现吗？请帮帮我..

In my desktop application i want to Export data directly from a SQL database to MS Excel with customize heading.I want to export my database which contains many tables to the excel 2007.
When user clicks on Export button, it will directly export data into Excel using VB.Net.How can i achieve that? please help me..

推荐答案

此代码从DataGridView导出到Excel

This code exports from DataGridView to Excel

Private Sub Export_Button_Click(ByVal sender As System.Object, ByVal e As 
System.EventArgs) Handles VIEW_Button.Click
 
    Dim xlApp As Microsoft.Office.Interop.Excel.Application
    Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
    Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value
    Dim i As Integer
    Dim j As Integer
 
    xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass
    xlWorkBook = xlApp.Workbooks.Add(misValue)
    xlWorkSheet = xlWorkBook.Sheets("sheet1")
 

    For i = 0 To DataGridView1.RowCount - 2
        For j = 0 To DataGridView1.ColumnCount - 1
            For k As Integer = 1 To DataGridView1.Columns.Count
                xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
                xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
            Next
        Next
    Next
 
    xlWorkSheet.SaveAs("D:\vbexcel.xlsx")
    xlWorkBook.Close()
    xlApp.Quit()
 
    releaseObject(xlApp)
    releaseObject(xlWorkBook)
    releaseObject(xlWorkSheet)
 
    MsgBox("You can find the file D:\vbexcel.xlsx")
End Sub
 
Private Sub releaseObject(ByVal obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        obj = Nothing
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

这篇关于使用Vb.net将整个Sql server 2005数据库导出到Excel ...........的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！