将数据库mysql中的数据导出为ex​​cel [英] Export data from database mysql to excel

问题描述

当我尝试将数据从表mysql导出到excel时，速度有问题。要创建的文件非常大，等待创建文件的时间很长。我正在一台配有4个处理器和8 gb ram的计算机上创建这个应用程序。我认为我的问题在于代码。

i have a problem with speed when i try to export data from table mysql to excel. the file to create is very large and the time is very long to wait the creation of the file. i am creating this application on a coputer with 4 processors and 8 gb of ram. I think my problem is in the code.

 Private Sub ExportExcel() '(ByVal sender As System.Object, ByVal e As System.EventArgs) ' Handles BtnExportExcel.Click
    'verfying the datagridview having data or not
    If ((tabella_excel.GetUpperBound(0) = 0) Or (tabella_excel.GetUpperBound(1) = 0)) Then
        Exit Sub
    End If



    Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
    Dim wBook As Microsoft.Office.Interop.Excel.Workbook
    Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet

    wBook = excel.Workbooks.Add()
    wSheet = wBook.ActiveSheet()


    Dim righe As Integer = tabella_excel.GetUpperBound(1)
    Dim colonne As Integer = tabella_excel.GetUpperBound(0)

    For i = 0 To colonne
        'excel.Rows(i + 1) = tabella_excel(i, 0)
        For j = 0 To righe
            excel.Cells(i + 1, j + 1) = tabella_excel(i, j)
        Next
    Next


    wSheet.Columns.AutoFit()
    'seleziono il percorso in cui salvare il file
    Dim saveFileDialog1 As New SaveFileDialog()
    saveFileDialog1.Filter = "File di testo (*.xls)|*.xls"
    saveFileDialog1.Title = "Save an Image File"
    saveFileDialog1.ShowDialog()


    'End If
    Dim strFileName As String = saveFileDialog1.FileName
    Dim blnFileOpen As Boolean = False
    Try
        Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
        fileTemp.Close()
    Catch ex As Exception
        blnFileOpen = False
    End Try

    If System.IO.File.Exists(strFileName) Then
        System.IO.File.Delete(strFileName)
    End If

    wBook.SaveAs(strFileName)
    'excel.Workbooks.Open(strFileName)
    'excel.Visible = True
End Sub

推荐答案

如果您没有任何相关要求，那么您可以使用c＃

执行sql连接查询

if you do not have any perticular requirement then you can execute query with sql connection from c#

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=D:\testing.xls;',
    'SELECT * FROM [SheetName

'）选择 * 来自 SQLServerTable

') select * from SQLServerTable

这篇关于将数据库mysql中的数据导出为ex​​cel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！