Visual Studio2010でExcelの大量のデータの操作（Excelから取得編)

Visual Studio2010でExcelの大量のデータの操作（Excelから取得編)

前回はデータをExcelに表示（設定）する方法を掲載しましたが、今回は逆のExcelから取得です。

セルから1つずつ転送する方法は、転送するデータが少量の場合は問題ありませんが、大量のデータをExcelブックから転送する場合は、この方法はお勧めできません。
大量のデータの操作にはオートメーションを使用してデータの配列にワークシートの特定のセル範囲を転送します。

【環境】
.NET VB (Microsoft Visual Studio 2010(sp1) .NET Framework 4.5.2)
Microsoft Excel 2010

【検証結果】
項目数（Col)40程度のRowを約10,000件操作した場合、
セルを1つずつ転送する方法では処理時間に約3.5分要しました。
データの配列をワークシートに転送する方法では、30秒で終了しました。
(データをDBに登録する処理も含めています。)


セルから1つずつ転送する方法

Dim celval1 As String
Dim celval2 As String
For i As Integer = 2 To ews.UsedRange.Rows.Count + 1
    If Not CType(ews.Cells(i, 0), _
        Global.Microsoft.Office.Interop.Excel.Range).Value2.ToString().Equals( _
        String.Empty) Then

        '1列目の項目を取出し
        celval1 = CType(ews.Cells(i, 0), _
            Global.Microsoft.Office.Interop.Excel.Range).Value2.ToString()
        '2列目の項目を取出し
        celval2 = CType(ews.Cells(i, 1), _
            Global.Microsoft.Office.Interop.Excel.Range).Value2.ToString()
    End If
Next

オートメーションを使用してワークシートからデータの配列に転送する方法

Dim celval1 As String
Dim celval2 As String
'Excelシートの値データ配列に転送
Dim DataArray(,) As Object = _
    CType(ews.Range("A2").Resize( _
        ews.UsedRange.Rows.Count, _
        ews.UsedRange.Columns.Count).Value2, Object(,))

For i As Integer = 1 To ews.UsedRange.Rows.Count
    If Not DataArray(i, 0).ToString().Equals(String.Empty) Then
        '1列目の項目を取出し
        celval1 =  DataArray(i, 0).ToString().Equals(String.Empty)
        '2列目の項目を取出し
        celval2 =  DataArray(i, 1).ToString().Equals(String.Empty)
    End If
Next

【参考】 https://support.microsoft.com/ja-jp/kb/306022

Visual Studio2010でExcelの大量のデータの操作（Excelに表示編)

Visual Studio2010でExcelの大量のデータの操作（Excelに表示編)

セルを1つずつ転送する方法は、転送するデータが少量の場合は問題ありませんが、大量のデータをExcelブックに転送する場合は、この方法はお勧めできません。
大量のデータの操作にはオートメーションを使用してデータの配列をワークシートの特定のセル範囲に転送します。

【環境】
.NET VB (Microsoft Visual Studio 2010(sp1) .NET Framework 4.5.2)
Microsoft Excel 2010

【検証結果】
項目数（Col)40程度のRowを約10,000件操作した場合、
セルを1つずつ転送する方法では処理時間に約10分要しました。
データの配列をワークシートに転送する方法では、数秒で終了しました。

セルを1つずつ転送する方法

Dim dt As DataTable = 【データテーブル取得】
'Excel WorkSheet
Dim ews As Excel.Worksheet = CType(ExcelWorkbook.ActiveSheet, Global.Microsoft.Office.Interop.Excel.Worksheet)
Dim er As Excel.Range

For i As Integer = 0 To dt.Rows.Count - 1
    For j As Integer = 0 To dt.Columns.Count - 1
        er = CType(ews.Cells(i + 2, j + 1), Global.Microsoft.Office.Interop.Excel.Range)
        '書式を設定
        Select Case dt.Columns(j).DataType.Name
            Case "Decimal"
                'er.NumberFormatLocal = "#,##0"
                er.NumberFormatLocal = "G/標準"
            Case "String"
                er.NumberFormatLocal = "@"
            Case Else
                er.NumberFormatLocal = "G/標準"
        End Select
        '値を設定
        er.Value2 = dt.Rows.Item(i).Item(j).ToString()

    Next
    '更新フラグを初期化
    er = CType(ews.Cells(i + 2, CInt(COL.UpdFlg)), Global.Microsoft.Office.Interop.Excel.Range)
    er.NumberFormatLocal = "@"
    er.Value2 = String.Empty
Next

オートメーションを使用してデータの配列をワークシートに転送する方法

Dim dt As DataTable = 【データテーブル取得】
'Excel WorkSheet
Dim ews As Excel.Worksheet = CType(ExcelWorkbook.ActiveSheet, Global.Microsoft.Office.Interop.Excel.Worksheet)
Dim er As Excel.Range
Dim DataArray(dt.Rows.Count - 1, dt.Columns.Count - 1) As Object

For i As Integer = 0 To dt.Rows.Count - 1
    For j As Integer = 0 To dt.Columns.Count - 1
        DataArray(i, j) = dt.Rows.Item(i).Item(j).ToString()
    Next
Next

'属性設定（列単位）
For j As Integer = 0 To dt.Columns.Count - 1
    er = ews.Range(ews.Cells(2, j + 1), ews.Cells(dt.Rows.Count + 1, j + 1))
    Select Case dt.Columns(j).DataType.Name
        Case "Decimal"
            'er.NumberFormatLocal = "#,##0"
            er.NumberFormatLocal = "G/標準"
        Case "String"
            er.NumberFormatLocal = "@"
        Case Else
            er.NumberFormatLocal = "G/標準"
    End Select
Next

'ワークシートの全セルにデータを転送
ews.Range("A2").Resize(dt.Rows.Count, dt.Columns.Count).Value = DataArray

【参考】 https://support.microsoft.com/ja-jp/kb/306022