Excelのセルに高速に値を出力する方法

Imports Microsoft.Office.Interop

Public Class ExcelForm

    ''' <summary>
    ''' 速い例
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        'Excelに貼り付けるデータを準備（実際はデータベース等から取得する）
        Dim dt As New DataTable

        dt.Columns.Add("A")
        dt.Columns.Add("B")

        For i As Integer = 0 To 10000
            Dim row As DataRow = dt.NewRow
            row("a") = i
            row("b") = i Mod 2

            dt.Rows.Add(row)
        Next

        'DataTableを二次元配列に格納する
        Dim data(dt.Rows.Count - 1, dt.Columns.Count - 1) As String
        For y As Integer = 0 To dt.Rows.Count - 1
            For x As Integer = 0 To dt.Columns.Count - 1
                data(y, x) = dt.Rows(y)(x).ToString
            Next
        Next
        
        'Excelを起動する
        Dim excelApp As New Excel.Application()
        'Excelブックを追加する
        Dim bk As Excel.Workbook = excelApp.Workbooks.Add()
        'Excelシートを取得する
        Dim sheet As Excel.Worksheet = bk.Worksheets(1)

        'データを貼り付ける
        Dim range As Excel.Range = Nothing
        Dim range1 As Excel.Range = Nothing
        Dim range2 As Excel.Range = Nothing

        Try
            Dim startX As Integer = 3
            Dim startY As Integer = 2
            '始点
            range1 = DirectCast(sheet.Cells(startY, startX), Excel.Range)
            '終点
            range2 = DirectCast(sheet.Cells(startY + UBound(data), startX + UBound(data, 2)), Excel.Range)
            'セル範囲
            range = sheet.Range(range1, range2)
            '貼り付け
            range.Value = data

            'Excelを表示する
            excelApp.Visible = True

        Catch
            Throw
        Finally
            '解放
            If Not range Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(range)
                range = Nothing
            End If

            If Not range1 Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(range1)
                range1 = Nothing
            End If

            If Not range2 Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(range2)
                range2 = Nothing
            End If

            If Not sheet Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet)
                sheet = Nothing
            End If

            If Not bk Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(bk)
                bk = Nothing
            End If

            If Not excelApp Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
                excelApp = Nothing
            End If

            GC.Collect()
        End Try
    End Sub

    ''' <summary>
    ''' 遅い例
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        'Excelに貼り付けるデータを準備（実際はデータベース等から取得する）
        Dim dt As New DataTable

        dt.Columns.Add("A")
        dt.Columns.Add("B")

        For i As Integer = 0 To 10000
            Dim row As DataRow = dt.NewRow
            row("a") = i
            row("b") = i Mod 2

            dt.Rows.Add(row)
        Next

        'Excelを起動する
        Dim excelApp As New Excel.Application()
        'Excelブックを追加する
        Dim bk As Excel.Workbook = excelApp.Workbooks.Add()
        'Excelシートを取得する
        Dim sheet As Excel.Worksheet = bk.Worksheets(1)

        'データを貼り付ける
        Dim range As Excel.Range = Nothing

        Try

            Dim startX As Integer = 3
            Dim startY As Integer = 2

            For y As Integer = startY To startY + dt.Rows.Count - 1
                For x As Integer = startX To startX + dt.Columns.Count - 1
                    range = DirectCast(sheet.Cells(y, x), Excel.Range)
                    range.Value = dt.Rows(y - startY)(x - startX).ToString
                Next
            Next

            'Excelを表示する
            excelApp.Visible = True

        Catch
            Throw
        Finally
            '解放
            If Not range Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(range)
                range = Nothing
            End If

            If Not sheet Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet)
                sheet = Nothing
            End If

            If Not bk Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(bk)
                bk = Nothing
            End If

            If Not excelApp Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
                excelApp = Nothing
            End If

            GC.Collect()
        End Try
    End Sub
End Class

ツイート