如何优化这个简单而复杂VB.Net看执行？ [英] How to optimize this simple but complex looking implementation in VB.Net?

问题描述

旨在实现： 要转储取表到Excel工作表。

Aim to achieve : Want to dump a fetched table to a excel sheet.

我的实现：

Imports System.Data.OleDb

    Private Sub getRawDataNextMonth()
    Dim sheetName As String = "RawData"
    Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\rawData.xlsx;Extended Properties=""Excel 12.0 XML;""")
    Dim adapter As New Data.OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", con)
    Dim dataSet As New Data.DataSet
    adapter.Fill(dataSet)
    Dim dataTable As Data.DataTable = dataSet.Tables(0)
    Dim rawData(dataTable.Rows.Count, dataTable.Columns.Count - 1) As Object
    Dim range As Excel.Range = WB.Sheets(sheetName).Range("A2:T" + dataTable.Rows.Count.ToString())
    For col = 0 To dataTable.Columns.Count - 1
        For row = 0 To dataTable.Rows.Count - 1
            rawData(row, col) = dataTable.Rows(row).ItemArray(col)
        Next
    Next
    range.Value2 = rawData
    End Sub

我是新来的数据获取和ADO.Net的概念，只是得到它的工作。 但是..

I am new to data fetching and ADO.Net concepts and just got it working. But..

这似乎是非常低效的，瘸腿了我。

This seems to be very inefficient and lame to me.

所以，你能帮助降低复杂性，可能是​​提高性能？​​

一个完全不同的（好）的实现是最欢迎！

请帮我优化这个......你的体验！

Please help me optimize this... with your experience !

推荐答案

这个函数写出到一个文本文件作为CSV格式，然后在Excel中打开该文件并将其保存为XLSX格式。我想这与一个53 x列10,000行的表，并花了约2秒：

This function writes out to a text file as CSV format and then opens the file in Excel and saves it as xlsx format. I tried this with a 53 column x 10,000 row table and it took ~2 seconds:

Private Sub Export3(ByVal filename As String, ByRef dt As DataTable)

        Dim tempfile As String = Path.GetTempPath + Path.GetFileNameWithoutExtension(Path.GetTempFileName) + ".csv"
        Dim sb As StringBuilder

        Using sw As StreamWriter = New StreamWriter(tempfile)
            sb = New StringBuilder("")
            For c As Integer = 0 To dt.Columns.Count - 1
                sb.Append(dt.Columns(c).ColumnName + ",")
            Next
            sw.WriteLine(sb.ToString.TrimEnd(","c))

            For r As Integer = 0 To dt.Rows.Count - 1
                sb = New StringBuilder("")
                For c As Integer = 0 To dt.Columns.Count - 1
                    sb.Append(dt.Rows(r).Item(c).ToString + ",")
                Next
                sw.WriteLine(sb.ToString.TrimEnd(","c))
            Next
        End Using


        Dim xlApp As Excel.Application = Nothing
        Dim xlBook As Excel.Workbook = Nothing

        Try
            xlApp = New Excel.Application
            'xlApp.Visible = True 'for debugging
            xlApp.DisplayAlerts = False
            xlBook = xlApp.Workbooks.Open(tempfile)

            xlBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookDefault)

            My.Computer.FileSystem.DeleteFile(tempfile)

        Finally
            If Not IsNothing(xlBook) Then
                xlBook.Close()
            End If
            If Not IsNothing(xlApp) Then xlApp.Quit()
        End Try
    End Sub

这篇关于如何优化这个简单而复杂VB.Net看执行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！