前回の記事では、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 を読み込ませて、届いた値と型を確かめました。
プロジェクトに合う ReoGrid を選ぶ
| プロジェクト | 使うもの | パッケージ |
|---|---|---|
| .NET 10 | ReoGrid V5 | unvell.ReoGrid.One |
| .NET Framework 4.8、または .NET 8(Windows) | ReoGrid V4 | unvell.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 / FALSE | Boolean |
| 文字列 | 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 |
|---|---|
#,##0 | N0 |
#,##0.00 | N2 |
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 Interop | ReoGrid | |
|---|---|---|---|
| ユーザーの PC に必要なもの | アプリと同じビット数の Access Database Engine | Excel | なし(アプリに組み込んだパッケージで完結) |
| 列の型 | 先頭の 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。モジュールはほぼ同じ形で書ける
関連情報
- VB.NET で DataGridView の内容を Excel(.xlsx)に出力する — この記事の逆向き
- VB.NET で使う — プロジェクトの準備と、C# サンプルを VB に読み替えるポイント
- XLSX 入出力 — 読み込みの対応範囲
- VB.NET で Excel ファイルを読み込み・書き出し・表示する
- Excel の表示形式コードを読み解く
- 「CSV を Excel で開いたら郵便番号の 0 が消えた」を自社アプリで防ぐ