VB.NET で Excel ファイルを DataGridView に読み込む — Office も OLEDB も使わずに

· unvell team
VB.NET で Excel ファイルを DataGridView に読み込む — Office も OLEDB も使わずに

前回の記事では、DataGridView の内容を Excel に出力しました。今回はその逆です。「お客様から届いた Excel を画面に取り込みたい」という要望に、VB.NET でよく使われる答えは 2 つあります。Microsoft.ACE.OLEDB.12.0 で SQL のように読む方法と、Microsoft.Office.Interop.Excel でセルを 1 つずつ読む方法です。

どちらも開発機では動きます。ところが OLEDB は、Access Database Engine の入っていない PC で「'Microsoft.ACE.OLEDB.12.0' プロバイダーはローカルのコンピューターに登録されていません。」と言って止まります。しかも列の型を先頭の 8 行から推測するので、先頭がすべて数値の列では、その下にある「未定」のような文字列が空で届きます。Interop は、そもそも Excel の入った PC でしか動きません。

この記事では、ReoGrid で .xlsx ファイルを読み、型付きの DataTable にして DataGridView に表示します。コードは貼り付けて使える 1 つのモジュールです。実際の業務ファイルにありがちなタイトル行、結合セル、下の合計行も処理します。V5 のコードは Option Strict On でビルドし、Excel が保存するのと同じ形の .xlsx を読み込ませて、届いた値と型を確かめました。

Excel の受注一覧のシート(タイトル行、空行、見出し、結合した支店、6 件の受注、空行、合計行、注記)から、見出しと 6 件の受注だけを読み、受注日は Date、郵便番号は String、金額は Decimal、出荷済は Boolean の DataTable にする流れを示した図


プロジェクトに合う ReoGrid を選ぶ

プロジェクト使うものパッケージ
.NET 10ReoGrid V5unvell.ReoGrid.One
.NET Framework 4.8、または .NET 8(Windows)ReoGrid V4unvell.ReoGrid4

VB.NET の WinForms アプリは今も .NET Framework が多く、その場合は V4 を使います。V4 のモジュールはこちら。どちらも UNVELL のパッケージフィードからインストールします(V5 の手順、V4 の手順)。30 日間のトライアルキーで、どちらも試せます。


Excel の読み込みで押さえるべき点

UsedRange を 2 重ループで回すだけなら 10 行で書けそうに見えます。実際のファイルでは、次の点に気を配る必要があります。

落とし穴起きることこのモジュールの対応
見出しの上のタイトル行タイトルが列名になり、本当の見出しがデータ行に混ざる見出しの行を headerRow で指定する
日付xlsx の日付は「日付の表示形式が付いた数値」なので、値だけ読むと 46328 が届く表示形式が日付のセルを Date にする
列の型の推測OLEDB は先頭 8 行で型を決めるので、型の合わない値が空になる列のすべての行を見て型を決める
縦に結合したセル値は先頭のセルにしかないので、2 行目以降の「支店」が空になる結合範囲の左上の値を各行に入れる
表示形式で付けた先頭のゼロ数値+表示形式 0000000 の郵便番号が 600001 で届くtextColumns の列は Excel の表示どおりの文字列で読む
下の合計行と注記合計がデータとして取り込まれ、集計が 2 倍になる最初の空行で読み終える
エラー値#VALUE! が数値の列に混ざる空(DBNull)にする
空・重複した見出しDataTable が DuplicateNameException を投げる「列3」「備考 (2)」のように名前を付ける
Excel で開いたままのファイル読み込みで IOException が発生するほかのプログラムの書き込みを許す共有モードで開く

読み込みモジュール — ReoGrid V5

.vbproj のプロジェクトにパッケージを追加します。

dotnet add package unvell.ReoGrid.One

アプリケーションの起動時に、ライセンスキーを 1 回だけ適用します。キーが無いと、読み込みのときに ReoGridLicenseException が発生します。

Imports unvell.ReoGrid.Core.License

ReoGridLicense.SetLicense(myLicenseKey)

続いて、次のモジュールを追加します。

Imports System.Data
Imports System.IO
Imports unvell.ReoGrid.Core
Imports unvell.ReoGrid.Core.Data
Imports unvell.ReoGrid.Core.Style
Imports unvell.ReoGrid.IO.Excel

Public Module ExcelImport

    ''' <summary>
    ''' xlsx のシートを読み、headerRow 行目(0 始まり)を見出しにした DataTable を返す。
    ''' textColumns に挙げた列は、Excel の表示どおりの文字列で読む。
    ''' </summary>
    Public Function LoadDataTable(path As String,
                                  Optional sheetName As String = Nothing,
                                  Optional headerRow As Integer = 0,
                                  Optional textColumns As String() = Nothing) As DataTable

        ' Excel で開いたままのファイルも読めるように、ほかのプログラムの書き込みを許して開く
        Dim book As Workbook
        Using stream As New FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
            book = XlsxReader.Read(stream)
        End Using

        Dim ws = If(sheetName Is Nothing, book(0), book(sheetName))
        If ws Is Nothing Then Throw New ArgumentException($"シート「{sheetName}」がありません。", NameOf(sheetName))

        Dim table As New DataTable(ws.Name)
        Dim used As RangePosition
        If Not ws.TryGetUsedRange(used) OrElse used.EndRow <= headerRow Then Return table

        ' 列は、見出しのある最後の列まで
        Dim lastCol = used.EndCol
        Do While lastCol >= 0 AndAlso ws.GetValue(headerRow, lastCol).IsEmpty
            lastCol -= 1
        Loop

        ' 行は、見出しの次から最初の空行の手前まで。下にある合計や注記は読まない
        Dim lastRow = headerRow
        Do While lastRow < used.EndRow AndAlso Not IsBlankRow(ws, lastRow + 1, lastCol)
            lastRow += 1
        Loop

        ' 列の型は、先頭の数行ではなくすべての行を見て決める
        For c = 0 To lastCol
            Dim name = UniqueName(table, ws.GetDisplayText(headerRow, c).Trim(), c)
            Dim format As String = Nothing
            Dim type = ColumnType(ws, headerRow + 1, lastRow, c, format)
            If textColumns IsNot Nothing AndAlso textColumns.Contains(name) Then type = GetType(String)
            table.Columns.Add(name, type).ExtendedProperties("NumberFormat") = format
        Next

        For r = headerRow + 1 To lastRow
            Dim row = table.NewRow()
            For c = 0 To lastCol
                row(c) = ToValue(ws, r, c, table.Columns(c).DataType)
            Next
            table.Rows.Add(row)
        Next
        Return table
    End Function

    ' 列のすべての値が同じ種類ならその型、混ざっていれば String
    Private Function ColumnType(ws As Worksheet, firstRow As Integer, lastRow As Integer,
                                col As Integer, ByRef format As String) As Type
        Dim found As Type = Nothing
        For r = firstRow To lastRow
            Dim cell = Anchor(ws, r, col)
            Dim t = KindOf(ws, cell.Row, cell.Col)
            If t Is Nothing Then Continue For           ' 空のセルとエラー値は判断に使わない
            If found Is Nothing Then
                found = t
                format = ws.GetNumberFormat(cell.Row, cell.Col)
            ElseIf t IsNot found Then
                Return GetType(String)
            End If
        Next
        Return If(found, GetType(String))
    End Function

    Private Function KindOf(ws As Worksheet, row As Integer, col As Integer) As Type
        Select Case ws.GetValue(row, col).Kind
            Case CellValueKind.Number
                ' xlsx の日付は「日付の表示形式が付いた数値」
                Return If(NumberFormatter.IsDateFormat(ws.GetNumberFormat(row, col)), GetType(Date), GetType(Decimal))
            Case CellValueKind.DateTime : Return GetType(Date)
            Case CellValueKind.Boolean : Return GetType(Boolean)
            Case CellValueKind.Text : Return GetType(String)
            Case Else : Return Nothing
        End Select
    End Function

    Private Function ToValue(ws As Worksheet, row As Integer, col As Integer, type As Type) As Object
        Dim cell = Anchor(ws, row, col)
        Dim v = ws.GetValue(cell.Row, cell.Col)
        If v.IsEmpty OrElse v.Kind = CellValueKind.Error Then Return DBNull.Value
        If type Is GetType(String) Then Return ws.GetDisplayText(cell.Row, cell.Col)
        If type Is GetType(Date) Then Return DateTime.FromOADate(v.AsNumber)
        If type Is GetType(Boolean) Then Return v.AsBoolean
        Return CDec(v.AsNumber)
    End Function

    ' 結合したセルは、値を持つ左上のセルを指す
    Private Function Anchor(ws As Worksheet, row As Integer, col As Integer) As CellPosition
        Dim merged As RangePosition
        If ws.Merges.TryGetAt(row, col, merged) Then Return merged.TopLeft
        Return New CellPosition(row, col)
    End Function

    Private Function IsBlankRow(ws As Worksheet, row As Integer, lastCol As Integer) As Boolean
        For c = 0 To lastCol
            If Not ws.GetValue(row, c).IsEmpty Then Return False
        Next
        Return True
    End Function

    ' 空の見出しは「列3」、重複した見出しは「備考 (2)」にする
    Private Function UniqueName(table As DataTable, header As String, col As Integer) As String
        Dim name = If(header = "", $"列{col + 1}", header)
        Dim candidate = name
        Dim n = 2
        Do While table.Columns.Contains(candidate)
            candidate = $"{name} ({n})"
            n += 1
        Loop
        Return candidate
    End Function

End Module

headerRow は 0 始まりです。上の図のように見出しが Excel の 3 行目にあるなら 2 を渡します。sheetName を省くと先頭のシートを読みます。

ボタンから呼び出す

Imports System.Data
Imports System.IO

Private Sub OpenButton_Click(sender As Object, e As EventArgs) Handles OpenButton.Click
    Using dlg As New OpenFileDialog With {.Filter = "Excel ブック|*.xlsx"}
        If dlg.ShowDialog(Me) <> DialogResult.OK Then Return

        Dim table As DataTable
        Try
            table = LoadDataTable(dlg.FileName, headerRow:=2, textColumns:={"郵便番号"})
        Catch ex As InvalidDataException
            MessageBox.Show("xlsx 形式のファイルではありません。.xls のファイルは Excel で .xlsx として保存し直してください。")
            Return
        End Try

        DataGridView1.DataSource = table
        For Each col As DataGridViewColumn In DataGridView1.Columns
            col.DefaultCellStyle.Format = DotNetFormatOf(table.Columns(col.DataPropertyName))
        Next
    End Using
End Sub

DataSource に DataTable を渡すと、DataGridView は列を自動で作ります。Boolean の列はチェックボックスの列になります。列の型が付いているので、列見出しをクリックしたときの並べ替えも、金額は金額の大きさの順、日付は日付の順です。文字列のまま取り込むと、金額は 13,500・196,000・45,600 のような文字の順に並びます。

古い .xls 形式(Excel 97-2003)や、拡張子だけ .xlsx に変えた CSV を選ぶと、InvalidDataException が発生します。上の Catch は、そのときに保存し直しを促すためのものです。


列の型の決め方

モジュールは、見出しの下の行をすべて見て列の型を決めます。

Excel のセルDataTable の列の型
数値Decimal
日付の表示形式が付いた数値Date
TRUE / FALSEBoolean
文字列String
種類が混ざった列String(Excel の表示どおりの文字)
空のセル・エラー値型の判断には使わず、値は DBNull

数式のセルは、Excel が保存したときの計算結果を値として読みます。金額の列が =F4*G4 のような数式でも、DataTable には 45600 のような数値が入ります。

数値を Decimal にするのは、金額の計算に向くうえに、値の精度が Excel とそろうからです。Excel は数値を 15 桁の精度で扱います。セルの値をそのまま Double で受け取ると 0.30000000000000004 のような値が届くことがありますが、CDec は 15 桁に丸めるので Excel の表示と同じ 0.3 になります。

型が付いているので、DataTable のままで集計もできます。

Dim total = CDec(table.Compute("SUM(金額)", ""))   ' 425900

型の混ざった列を見つける

1 つの列に数値と文字列が混ざっていると、その列は String になります。たとえば「見積」シートで、Excel の 5 行目の単価が「未定」だとします。単価の列は "3,800" と "未定" を持つ文字列の列になります。その行の金額(=F5*G5)は Excel 上で #VALUE! なので、DBNull で届きます。

取り込んだ値を保存する前に、数値であるはずの列を確かめておくと安心です。データ行は見出しのすぐ下から空行なしで並ぶので、DataTable の行番号から Excel の行番号がわかります。

Imports System.Globalization

Dim headerRow = 2
Dim table = LoadDataTable(path, "見積", headerRow:=headerRow)

' 数値であるはずの列に数値でない値があれば、Excel の行番号を添えて知らせる
Dim errors As New List(Of String)
If table.Columns("単価").DataType Is GetType(String) Then
    For i = 0 To table.Rows.Count - 1
        Dim text = table.Rows(i)("単価").ToString()
        Dim value As Decimal
        If text <> "" AndAlso Not Decimal.TryParse(text, NumberStyles.Number, CultureInfo.CurrentCulture, value) Then
            errors.Add($"{i + headerRow + 2} 行目: 単価「{text}」は数値ではありません")
        End If
    Next
End If

この例では「5 行目: 単価「未定」は数値ではありません」が返ります。


縦に結合したセル

日本の業務の Excel では、同じ支店や同じ得意先の行を縦に結合してまとめる書き方がよくあります。結合したセルの値は、結合範囲の左上のセルにしか入っていません。そのまま読むと、2 行目以降の支店は空です。

モジュールの Anchor は、結合範囲の中のセルを左上のセルに読み替えます。札幌の 3 行には 3 行とも「札幌」が入るので、DataTable の行を絞り込んだり並べ替えたりしても支店がわかります。


表示形式で付けた先頭のゼロ

郵便番号や社員番号の先頭のゼロは、Excel では 2 通りの方法で残せます。

  • 文字列として入力する('0600001 や、セルの書式を「文字列」にしてから入力)。この場合、モジュールは何もしなくても String の列として 0600001 を読みます。
  • 数値として入力し、表示形式 0000000 を付ける。セルの値は 600001 で、ゼロは表示だけです。この列を数値のまま読むと 600001 が届きます。

後者のファイルでは、列の名前を textColumns に渡します。その列は Excel の表示どおりの文字列で読むので、0600001 が届きます。

LoadDataTable(path, headerRow:=2, textColumns:={"郵便番号", "社員番号"})

表示形式のしくみは Excel の表示形式コードを読み解く で詳しく扱っています。


書式を画面に引き継ぐ

モジュールは、列の最初の値の表示形式を DataColumn.ExtendedProperties("NumberFormat") に残しています。DataGridView の DefaultCellStyle.Format には .NET の書式文字列を指定するので、前回の記事の対応表を逆向きに使います。

' Excel の書式コードを、DataGridView が使う .NET の書式文字列に変換する
Private Function DotNetFormatOf(column As DataColumn) As String
    If column.DataType Is GetType(Date) Then Return "yyyy/MM/dd"
    Select Case TryCast(column.ExtendedProperties("NumberFormat"), String)
        Case "#,##0" : Return "N0"
        Case "#,##0.00" : Return "N2"
        Case "0%" : Return "P0"
        Case "0.0%" : Return "P1"
    End Select
    Return ""
End Function
Excel の書式コードDefaultCellStyle.Format
#,##0N0
#,##0.00N2
0%P0
0.0%P1
日付の書式yyyy/MM/dd

日付は一律に yyyy/MM/dd にしています。Excel の標準の日付の書式は PC の地域設定によって表示が変わるので、画面では形をそろえたほうが読みやすいからです。


Excel で開いたままのファイル

XlsxReader.Read にはファイルのパスも渡せます。ただ、ユーザーが同じファイルを Excel で開いていると、その読み込みは IOException で止まります。Excel が書き込みのためにファイルを開いているところへ、ほかのプログラムの書き込みを許さない共有モードで開こうとするためです。

モジュールは FileShare.ReadWrite でストリームを開いてから XlsxReader.Read に渡すので、Excel で開いたままのファイルも読めます。読めるのは最後に保存した内容です。Excel 上で編集中の、まだ保存していない変更は入りません。


大きなファイル

10 万行 × 9 列(約 5 MB)の xlsx も、そのまま DataTable にできます。計測では、初回が約 1.7 秒、2 回目以降が 0.6〜0.8 秒でした(Apple Silicon の Mac、.NET 10)。


.NET Framework 4.8 では V4 で同じことをする

V4 のパッケージは unvell.ReoGrid4 で、.NET Framework 4.8 と .NET 8(Windows)で動きます。ライセンスの呼び出しは同じで、Imports unvell.ReoGrid.License を使います。モジュールの形は V5 とほぼ同じで、セルの読み方だけが変わります。フォームのコードはそのまま使えます。

Imports System.Data
Imports System.IO
Imports unvell.ReoGrid
Imports unvell.ReoGrid.IO

Public Module ExcelImport

    Public Function LoadDataTable(path As String,
                                  Optional sheetName As String = Nothing,
                                  Optional headerRow As Integer = 0,
                                  Optional textColumns As String() = Nothing) As DataTable

        Dim book = ReoGridControl.CreateMemoryWorkbook()
        Using stream As New FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)
            book.Load(stream, FileFormat.Excel2007)
        End Using

        Dim ws = If(sheetName Is Nothing, book.Worksheets(0), book.Worksheets(sheetName))
        If ws Is Nothing Then Throw New ArgumentException($"シート「{sheetName}」がありません。", NameOf(sheetName))

        Dim table As New DataTable(ws.Name)
        Dim used = ws.UsedRange
        If used.EndRow <= headerRow Then Return table

        Dim lastCol = used.EndCol
        Do While lastCol >= 0 AndAlso ws.GetCellData(headerRow, lastCol) Is Nothing
            lastCol -= 1
        Loop

        Dim lastRow = headerRow
        Do While lastRow < used.EndRow AndAlso Not IsBlankRow(ws, lastRow + 1, lastCol)
            lastRow += 1
        Loop

        For c = 0 To lastCol
            Dim name = UniqueName(table, ws.GetCellText(headerRow, c).Trim(), c)
            Dim type = ColumnType(ws, headerRow + 1, lastRow, c)
            If textColumns IsNot Nothing AndAlso textColumns.Contains(name) Then type = GetType(String)
            table.Columns.Add(name, type)
        Next

        For r = headerRow + 1 To lastRow
            Dim row = table.NewRow()
            For c = 0 To lastCol
                row(c) = ToValue(ws, r, c, table.Columns(c).DataType)
            Next
            table.Rows.Add(row)
        Next
        Return table
    End Function

    Private Function ColumnType(ws As Worksheet, firstRow As Integer, lastRow As Integer, col As Integer) As Type
        Dim found As Type = Nothing
        For r = firstRow To lastRow
            Dim t = KindOf(ws.GetCellData(Anchor(ws, r, col)))
            If t Is Nothing Then Continue For
            If found Is Nothing Then
                found = t
            ElseIf t IsNot found Then
                Return GetType(String)
            End If
        Next
        Return If(found, GetType(String))
    End Function

    ' V4 は、日付の表示形式のセルを DateTime として読む
    Private Function KindOf(data As Object) As Type
        If TypeOf data Is Double Then Return GetType(Decimal)
        If TypeOf data Is Date Then Return GetType(Date)
        If TypeOf data Is Boolean Then Return GetType(Boolean)
        If TypeOf data Is String Then Return GetType(String)
        Return Nothing
    End Function

    Private Function ToValue(ws As Worksheet, row As Integer, col As Integer, type As Type) As Object
        Dim cell = Anchor(ws, row, col)
        Dim data = ws.GetCellData(cell)
        If KindOf(data) Is Nothing Then Return DBNull.Value
        If type Is GetType(String) Then Return ws.GetCellText(cell)
        If type Is GetType(Decimal) Then Return CDec(data)
        Return data
    End Function

    Private Function Anchor(ws As Worksheet, row As Integer, col As Integer) As CellPosition
        Return ws.GetRangeIfMergedCell(New CellPosition(row, col)).StartPos
    End Function

    Private Function IsBlankRow(ws As Worksheet, row As Integer, lastCol As Integer) As Boolean
        For c = 0 To lastCol
            If ws.GetCellData(row, c) IsNot Nothing Then Return False
        Next
        Return True
    End Function

    Private Function UniqueName(table As DataTable, header As String, col As Integer) As String
        Dim name = If(header = "", $"列{col + 1}", header)
        Dim candidate = name
        Dim n = 2
        Do While table.Columns.Contains(candidate)
            candidate = $"{name} ({n})"
            n += 1
        Loop
        Return candidate
    End Function

End Module

V5 との違いは 3 つです。

  • V4 は、日付の表示形式のセルを最初から DateTime として読みます。そのため、書式から日付を見分ける処理を省いています。
  • V4 は、読み込んだ数式を V4 自身で計算し直します。計算がエラーになったセルの値は Nothing なので、空のセルと同じく DBNull になります。
  • V4 版は書式を DataTable に引き継ぎません。フォームの DotNetFormatOf は日付の列にだけ書式を付けるので、数値の列に 3 桁区切りが要るなら列ごとに Format を指定してください。

OLEDB・Interop との違い

OLEDB(Microsoft.ACE.OLEDB.12.0)Office InteropReoGrid
ユーザーの PC に必要なものアプリと同じビット数の Access Database EngineExcelなし(アプリに組み込んだパッケージで完結)
列の型先頭の 8 行から推測し、合わない値は空になるセルごとに Object で受け取るすべての行を見て決める
サービスやタスクスケジューラ上ビット数の組み合わせに気を配る必要があるMicrosoft のサポート対象外画面なしで動作

取り込んだデータをそのまま編集させたいなら、DataGridView の代わりに ReoGridControl を置く方法もあります。Excel のシートを罫線や書式ごと表示し、数式やフィルターもそのまま使えます(VB.NET で Excel ファイルを読み込み・書き出し・表示する)。


まとめ

  • 見出しの上にタイトル行があるファイルは、headerRow で見出しの行を指定する
  • xlsx の日付は表示形式の付いた数値なので、NumberFormatter.IsDateFormat で見分けて DateTime.FromOADate で変換する
  • 列の型はすべての行を見て決める。混ざっていれば String にし、保存する前に検査する
  • 縦に結合したセルは、結合範囲の左上の値を各行に入れる
  • 表示形式で付けた先頭のゼロは、GetDisplayText で表示どおりの文字列として読む
  • 最初の空行で読み終えると、下の合計行と注記を取り込まずに済む
  • FileShare.ReadWrite で開けば、Excel で開いたままのファイルも読める
  • .NET 10 なら V5、.NET Framework 4.8 なら V4。モジュールはほぼ同じ形で書ける

V5 の新機能を見る / 30 日間のトライアルを始める


関連情報

ご自身のプロジェクトで ReoGrid を試す

.NET WinForms / WPF / Avalonia 向けの Excel 互換スプレッドシートコンポーネント。30 日間の無料トライアルをご利用いただけます。

ニュースレター

最新リリースをメールでお届け

ReoGrid の新バージョン・新機能・技術記事のお知らせをお送りします。配信停止はいつでも可能です。

関連する記事

VB.NET で DataGridView の内容を Excel(.xlsx)に出力する — Office Interop を使わずに

定番の Interop のループは、Office の入っていない PC で動かず、郵便番号 0600001 を 600001 に変えてしまいます。この記事の VB.NET モジュールは DataGridView の内容を本物の .xlsx に書き出します。見えている列を画面の順に、数値は数値、日付は日付、先頭のゼロもそのままです。.NET 10 向けの ReoGrid V5 と .NET Framework 4.8 向けの V4、両方の完全なコードを載せています。

Excel の「テーブル」を C# で作る — 「テーブルとして書式設定」が実際に保存しているもの

Excel の「テーブルとして書式設定」は、見出しに色が付き 1 行おきに縞が入るので書式に見えます。ところが保存したファイルを開いてみると、塗りを持つセルは 1 つもありません。Excel が保存しているのは「範囲・見出し行・スタイル名」を持つオブジェクトです。xlsx の中のテーブルの正体、組込 60 種のスタイルの中身、そして Excel がファイルを開けるかどうかを分ける 3 つの決まりを整理し、ReoGrid V5.1 の C# コードでフィルター▽と集計行付きのテーブルを作ります。

VB.NET で Excel ファイルを読み込み・書き出し・表示する

VB.NET で `.xlsx` ファイルを扱うための実践ガイド — データの読み込み、レポートの生成、WinForms / WPF デスクトップアプリへのライブスプレッドシートビューアの埋め込み。いずれも Office をインストールせずに実現できます。