VB.NET NPOIでExcelファイルを操作

どうも、もももです。

VB.NETでExcelファイルを操作する方法は色々ありますが、今回はNPOIで操作する方法について紹介します。

前提条件
事前準備
サンプルプログラム
まとめ

前提条件

VB.NET(Visual Studio 2019)
Excel(Microsoft Office Personal 2019)

事前準備

NPOIでExcelファイルを操作するには「 NPOI 」をプロジェクトの参照に追加する必要があります。

対象プロジェクトの「参照」を右クリック→「NuGetパッケージの管理」をクリック

NuGetの画面が表示されるので「参照」タブを選択し、検索窓に「Npoi」と入力します。

表示された「NPOI」を選択し「インストール」ボタンをクリックします。

しばらくすると「変更のプレビュー」が表示されるので「OK」をクリックし、インストールが完了するとプロジェクトの参照に「NPOI」及び関連する参照が追加されます。

サンプルプログラム

概要

保存ボタン押下でA1～C1用のテキストボックスに入力した内容がExcel上のA1～C1セルに書き込まれ、上書き保存されるプログラムです。

A1セルは文字列、B1セルは数値、C1セルは日付として保存します。

実行結果

画面の各テキストボックスに「001」、「1」、「1900/1/1」と入力して保存ボタンを押下しました。

Excelファイルを確認するとA1セル：文字列で「001」、B1セル：数値で「１」、C1セル：日付で「1900/01/01」として保存されていました。

コード解説

Imports System.IO
Imports NPOI.SS.UserModel

Public Class Form1
    Private Sub SaveButton_Click(sender As Object, e As EventArgs) _
        Handles SaveButton.Click

        Dim fileName = "Excelファイルのフルパス"

        'ファイルオープン
        Dim ofs As FileStream = File.OpenRead(fileName)
        Dim wb As IWorkbook = WorkbookFactory.Create(ofs)
        Dim ws As ISheet = wb.GetSheetAt(0)
        ofs.Close()

        'データ書込み
        Dim row As IRow = ws.CreateRow(0)

        Dim A1Cell As ICell = row.CreateCell(0)
        A1Cell.SetCellType(CellType.String)
        A1Cell.SetCellValue(A1CellTextBox.Text)

        Dim B1CellStyle As ICellStyle = wb.CreateCellStyle()
        B1CellStyle.DataFormat = wb.CreateDataFormat.GetFormat("0")

        Dim B1Cell As ICell = row.CreateCell(1)
        B1Cell.SetCellType(CellType.Numeric)
        B1Cell.CellStyle = B1CellStyle
        B1Cell.SetCellValue(Double.Parse(B1CellTextBox.Text))

        Dim C1CellStyle As ICellStyle = wb.CreateCellStyle()
        C1CellStyle.DataFormat = wb.CreateDataFormat.GetFormat("yyyy/mm/dd")

        Dim C1Cell As ICell = row.CreateCell(2)
        C1Cell.SetCellType(CellType.Numeric)
        C1Cell.CellStyle = C1CellStyle
        C1Cell.SetCellValue(CDate(C1CellTextBox.Text))

        '保存して終了
        Dim cfs = File.Create(fileName)
        wb.Write(cfs)
        cfs.Close()
        wb.Close()

    End Sub
End Class

11～14行：WorkbookオブジェクトにExcelファイルの内容を読み込み、編集するSheetを取得します。
17行：取得したSheetオブジェクトから編集する行オブジェクトを作成します。(Excel1行目を指定)
19～21行：行オブジェクトからA1セルのオブジェクトを生成し、書式設定(文字列)、データセットを行います。
23～24行：B1セル用のCellStyleオブジェクトを生成し、書式設定(数値)を行います。
26～29行：行オブジェクトからB1セルのオブジェクトを生成し、書式設定(数値)、データセットを行います。
31～32行：C1セル用のCellStyleオブジェクトを生成し、書式設定(日付)を行います。
34～37行：行オブジェクトからC1セルのオブジェクトを生成し、書式設定(日付)、データセットを行います。
40～43行：Excelファイルを読込み、編集したWorkbookオブジェクト(各セル情報の変更を反映したもの)を書込みます。

COM参照ではプログラム上でExcelファイルを開いて直接操作していくのに対して、NPOIはExcelファイルの情報を元にオブジェクト作成⇒作成したオブジェクトを編集⇒編集したオブジェクトをExcelファイルに書込みという流れになります。

COM参照と比べると値のセット方法(SetCellValue関数)に癖があり、慣れるまでは大変かと思いますが、プログラムで直接Excelを操作するわけではないので処理速度が格段に違います。

SetCellValueメソッドの挙動を確認

        Dim stringTestRow = ws.CreateRow(1)

        Dim stringTestCell1 = stringTestRow.CreateCell(0)
        stringTestCell1.SetCellType(CellType.String)
        stringTestCell1.SetCellValue("123")

        Dim stringTestCell2 = stringTestRow.CreateCell(1)
        stringTestCell2.SetCellType(CellType.String)
        stringTestCell2.SetCellValue(123)

        Dim stringTestCell3 = stringTestRow.CreateCell(2)
        stringTestCell3.SetCellType(CellType.String)
        stringTestCell3.SetCellValue(CDate("1900/1/1"))

        Dim numericTestCellStyle As ICellStyle = wb.CreateCellStyle()
        numericTestCellStyle.DataFormat = wb.CreateDataFormat.GetFormat("0")
        Dim numericTestRow = ws.CreateRow(2)

        Dim numericTestCell1 = numericTestRow.CreateCell(0)
        numericTestCell1.SetCellType(CellType.Numeric)
        numericTestCell1.CellStyle = numericTestCellStyle
        numericTestCell1.SetCellValue("123")

        Dim numericTestCell2 = numericTestRow.CreateCell(1)
        numericTestCell2.SetCellType(CellType.Numeric)
        numericTestCell2.CellStyle = numericTestCellStyle
        numericTestCell2.SetCellValue(123)

        Dim numericTestCell3 = numericTestRow.CreateCell(2)
        numericTestCell3.SetCellType(CellType.Numeric)
        numericTestCell3.CellStyle = numericTestCellStyle
        numericTestCell3.SetCellValue(CDate("1900/1/1"))

        Dim dateTestCellStyle As ICellStyle = wb.CreateCellStyle()
        dateTestCellStyle.DataFormat = wb.CreateDataFormat.GetFormat("yyyy/mm/dd")
        Dim dateTestRow = ws.CreateRow(3)

        Dim dateTestCell1 = dateTestRow.CreateCell(0)
        dateTestCell1.SetCellType(CellType.Numeric)
        dateTestCell1.CellStyle = dateTestCellStyle
        dateTestCell1.SetCellValue("123")

        Dim dateTestCell2 = dateTestRow.CreateCell(1)
        dateTestCell2.SetCellType(CellType.Numeric)
        dateTestCell2.CellStyle = dateTestCellStyle
        dateTestCell2.SetCellValue(123)

        Dim dateTestCell3 = dateTestRow.CreateCell(2)
        dateTestCell3.SetCellType(CellType.Numeric)
        dateTestCell3.CellStyle = dateTestCellStyle
        dateTestCell3.SetCellValue(CDate("1900/1/1"))

9通りのテストパターンを用意しました。

上記をサンプルプログラムに追記し実行した結果が下図になります。

・2行目：CellType＝Stringに設定したセルに対して、 A列：文字列型、B列：数値型、 C列：日付型をセット

・3行目：CellType＝Numeric及び書式設定を数値(DataFormat＝”0″)に設定したセルに対して、 A列：文字列型、B列：数値型、 C列：日付型をセット

・4行目：CellType＝Numeric及び書式設定を日付(DataFormat＝”yyyy/mm/dd”)に設定したセルに対して、 A列：文字列型、B列：数値型、 C列：日付型をセット

以上より、「SetCellValueメソッド」の挙動として下記が確認できます。

引数に文字列型を指定した場合、書式設定の内容に関係なく文字列がセットされる(A2セル, A3セル, A4セル)
引数に数値型を指定した場合、DataFormatの設定を行わないと日付としてセットされる(B2セル, B4セル)
引数に日付型を指定した場合、数値設定(DataFormat=”0″)されたセルに対しては数値としてセットされる(C3セル)

まとめ

VB.NETでNPOIを用いてExcelファイルを操作する方法についてまとめました。

COM参照に比べると SetCellValue メソッドや書式設定の方法に注意する必要がありますが、オブジェクトの開放を意識しなくて良いのでプログラムが組みやすいように思います。

COM参照を用いたExcel操作は以下を参照ください。

下記はアプリケーションの作成例になります。

【VB.NET】NPOIでExcelファイルを操作