Excelに書き込む

http://salvw.miscnotes.com/index.php/archives/2895

 

ソリューションエクスプローラから VB プロジェクトを右クリック → [参照の設定]で「参照の追加」ダイアログを表示させます。
で、[.NET]タブ → 「Microsoft.Office.Interop.Excel(12.0.0.0)」を選択 → [OK]。

 

 

Imports Microsoft.Office.Interop.Excel

 

 Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim startTime = Now

        Dim EX_PATH = "D:/Book1.xls"
        Dim xlApp = New Microsoft.Office.Interop.Excel.Application
        Dim xlBooks As Microsoft.Office.Interop.Excel.Workbooks = xlApp.Workbooks
        Dim xlBook As Microsoft.Office.Interop.Excel.Workbook = xlBooks.Open(EX_PATH)
        Dim xlSheets As Microsoft.Office.Interop.Excel.Sheets = xlBook.Worksheets
        Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet = xlSheets("Sheet1")
        Dim xlRange As Microsoft.Office.Interop.Excel.Range

        '(A)遅い
        'For i = 1 To 10000
        '    xlRange = xlSheet.Range(xlSheet.Cells(i, 1), xlSheet.Cells(i, 10))
        '    xlRange(1, 1).value = i
        '    xlRange(1, 2).value = "1.1"
        '    xlRange(1, 3).value = "1.12"
        '    xlRange(1, 4).value = "1.123"
        '    xlRange(1, 5).value = "1.1234"
        '    xlRange(1, 6).value = "1.12345"
        '    xlRange(1, 7).value = "1.123456"
        '    xlRange(1, 8).value = "1.1234567"
        '    xlRange(1, 9).value = "1.12345678"
        '    xlRange(1, 10).value = "1.123456789"
        'Next
        'xlBook.Save()

        '(A)早い
        Dim xlArray(9999, 9) As Object
        For i = 0 To 9999
            xlArray(i, 0) = New Object : xlArray(i, 0) = i + 1
            xlArray(i, 1) = New Object : xlArray(i, 1) = "1.1"
            xlArray(i, 2) = New Object : xlArray(i, 2) = "1.12"
            xlArray(i, 3) = New Object : xlArray(i, 3) = "1.123"
            xlArray(i, 4) = New Object : xlArray(i, 4) = "1.1234"
            xlArray(i, 5) = New Object : xlArray(i, 5) = "1.12345"
            xlArray(i, 6) = New Object : xlArray(i, 6) = "1.123456"
            xlArray(i, 7) = New Object : xlArray(i, 7) = "1.1234567"
            xlArray(i, 8) = New Object : xlArray(i, 8) = "1.12345678"
            xlArray(i, 9) = New Object : xlArray(i, 9) = "1.123456789"
        Next
        xlRange = xlSheet.Range("A1").Resize(10000, 10)
        xlRange.Value = xlArray
        xlBook.Save()

        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheets)
        xlBook.Close()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)
        xlBooks.Close()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBooks)
        xlApp.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)

        MsgBox((Now - startTime).ToString)

    End Sub