C＃でExcelを読み書きするライブリとして、NetOffice、CLosedOffice、NPOIが有名ですが、この記事ではNPOIの基本的な使い方をサンプルを交えて紹介したいと思います。
他のサイトでは、いきなりソースコードの説明があったり、フォントの指定方法やセルの指定方法などが個別の記事として解説されているケースが多いようですが、ここでは最初にクラスの構成（概要）を理解して頂き、それを踏まえてよく使うであろうメソッドやプロパティについて、短いサンプルを付けて解説していきます。
NPOIとは
Javaで使われていたExcelファイル読み書き用ライブラリである「POI」を .NET に移植したものです。
特長として、OfficeがインストールされていなくてもExcelファイルが作成できるという点が挙げられます。
POIの由来は、"Poor Obfuscation Implementation" (質の悪い難読な実装)の頭文字から来ているとの事で、Officeのファイル形式を調べた結果、非常に分かり難くなっていたことから、皮肉を込めて命名したのだとか。
インストール方法
Nugetでインストールが可能です。
Nugetの使い方が知りたい方は、こちらの記事をご覧ください。
Visual Studio のヘッダメニュー「ツール」⇒「Nuget パッケージマネージャ」⇒「ソリューションのNuGetパッケージの管理」で下記を表示して、npoi で検索すると表示されます。
後は、インストールしたいプロジェクトをチェックし、「インストール」ボタンをクリックすればOKです。
NPOIのクラス構成（概要）
Excelには、ワークブック（いわゆる拡張子が xlsx のEXCELファイル）があって、その中に複数のシートが含まれています。
シートには行と列があり、行と列で示されるセルがあります。
NOPIにも、それに対応するクラスがあり次の様な関係になっています。
では、それぞれのクラスにどのようなメソッドがあるかについて見ていきましょう。
NPOIの場合、IWorkBook⇒ISheet⇒IRow⇒ICell というクラスの紐づけになるのですが、それぞれにCreate～メソッドが用意されており、このメソッドを通して各クラスを生成するようになっています。
このような入れ子の構造の場合、Collection形式で子要素を管理し、インデクサで子要素にアクセスする場合が多いのですが、NPOIの場合はインデクサではなく、Get～メソッドを使って子要素にアクセスします。
機能別解説
では、個々の機能について解説していきます。
あくまでも実用本位であり、NPOIを使ってExcelを読み書きするのに必要なものに限定していますので、全ての機能を網羅しているわけではないことをあらかじめご了承下さい。
また、NPOIを使う場合は、ソースのヘッダ部に下記の参照設定を記述しておいて下さい。
|
1 2 3 4 |
using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using NPOI.HSSF.Util; using NPOI.SS.Util; |
それから、サンプルソースコードに登場する _workBook は、既に new された IWorkbook のインスタンスが格納されているという前提になっています。
多くの場合、IWorkbook のインスタンスを生成し、シートやセルを追加（又は変更）した後で、最後にファイルに保存することになりますので、そのインスタンスが既に代入されているものと解釈して下さい。
EXCELファイルの読み込み
EXCELブックをゼロから作りたい場合は、new XSSFWorkbook() と記述し、インスタンスを生成します。
新規作成してもシートは自動で作成されないので、必ずCreateSheetメソッドでシートを最低1枚は追加しておいて下さい。
一方、既存のEXCELブックを開きたい場合は、 WorkbookFactory.Create(ファイル名) と記述します。
|
1 2 3 4 5 6 |
//新規のExcelブックを作成する IWorkbook book = new XSSFWorkbook(); book.CreateSheet("Sheet1"); //既存のExcelブックを読み込む IWorkbook book = WorkbookFactory.Create(@"book1.xlsx"); |
CreateSheet の引数にシート名を指定していますが、省略することも可能です。
省略すると、追加した分だけ "Sheet0"、"Sheet1"、・・・・・ の名前でシートが作成されます。
EXCELファイルの保存
保存は IWorkbook クラスの Write メソッドを使います。
このWriteメソッドにはファイル名ではなくファイルストリームを渡す必要がありますので、次の様な記述になります。
|
1 2 3 4 |
using (var fs = new FileStream(@"book1.xlsx", FileMode.Create)) { _workBook.Write(fs); } |
FileStream の第2の引数にオープンモードを指定していますが、 FileMode.Create を指定すると、既存のExcelブックが無ければ新規作成し、あれば無条件に上書きします。
シートの追加
シートは ISheetの追加は、IWorkBook の CreateSheet メソッドを使います。
引数にシート名を付ける事も可能ですし、省略すれば Sheet+連番 でシート名が作成されます。
ちなみに、連番は０から始まりますので、シートが無い状態でCreateSheet() を実行すると、"Sheet0" というシートが作成されます。
本家のExcelで新しいExcelBookを作成した場合は自動で"Sheet1" が作られますので、それに合わせたい場合は明示的に名前を指定してください。
|
1 2 3 4 5 |
//自動でシート名を付ける場合 _workBook.CreateSheet(); //明示的にシート名を指定する場合 _workBook.CreateSheet("Sheet1"); |
シートの取得
シートを取得する場合、シート名とシート番号のどちらでも指定が可能です。
シート名の場合は GetSheet メソッドを、シート番号の場合は GetSheetAt を使います。
|
1 2 3 4 5 |
//シート名を指摘して取得 _workBook.GetSheet("MySheet"); //シート番号を指定して取得 _workBook.GetSheetAt(3); |
シートの削除
シートを削除するには、IWorkBook の RemoveSheetAtメソッドにシート番号を指定します。
シート名で削除する場合は、GetSheetIndex メソッドでシート名からシート番号を取得し、RemoveSheetAtメソッドにその番号を指定します。
|
1 2 3 4 5 6 |
//指定したシート番号のシートを削除する _workBook.RemoveSheetAt(3); //指定したシート名のシートを削除する var index = _workBook.GetSheetIndex("MySheet"); _workBook.RemoveSheetAt(index); |
シートのアクティブ化
シートをアクティブにする場合、IWorkBook の SetActiveSheet メソッドにシート番号を指定します。
指定したシート名のシートをアクティブにしたい場合、GetSheetIndex メソッドでシート名からシート番号を取得し、SetActiveSheetメソッドにその番号を指定します。
|
1 2 3 4 5 6 |
//指定したシート番号のシートをActiveにする _workBook.SetActiveSheet(3); //指定したシート名のシートをActiveにする var index = _workBook.GetSheetIndex("MySheet"); _workBook.SetActiveSheet(index); |
現在アクティブになっているシート番号を取得するには、 ActiveSheetIndex プロパティを参照します。
|
1 |
var index = _workBook.ActiveSheetIndex |
現在アクティブになっているシート名を取得したい場合は、GetSheetName メソッドを使ってシート番号からシート名を取得します。
|
1 2 |
var index = _workBook.ActiveSheetIndex var name = _workBook.GetSheetName(index); |
アクティブなセルは、あくまでもExcel上で選択されている状態にするだけのことなので、NPOI上はあまり意味を持ちません。
指定した位置のセルを取得（R1C1による指定）
ISheetクラスのGetRowメソッドに行番号を指定し、そこから取得したIRow クラスのインスタンスに対して GetCellメソッドを実行します。
指定する行番号、列番号は０から始まりますのでご注意ください。
Excelのシート状で1行1列名（A1)は 行番号=0,列番号=0を指定する必要があります。
|
1 2 3 |
//シート番号2のシートに対して、5行3列の位置にあるセルを取得 IRow row = _workBook.GetSheetAt(2).GetRow(4) ?? _sheet.CreateRow(4); ICell cell = row.GetCell(2) ?? row.CreateCell(2); |
ここでのポイントは、指定した行もしくは列が存在しない場合、GetRowやGetCell が null になるため、null が返ってきた際は行又は列を生成しているという点です。
シート番号ではなく、シート番号で指定する場合は次の様になります。
|
1 2 3 |
//MySheetという名前のシートに対して、5行3列の位置にあるセルを取得 IRow row = _workBook.GetSheet("MySheet").GetRow(4) ?? _sheet.CreateRow(4); ICell cell = row.GetCell(2) ?? row.CreateCell(2); |
指定した位置のセルを取得（A1形式による指定）
A1形式で直接セルを指定することは出来ないので、A1形式から行番号と列番号を取得する必要があります。
CellReference クラスの引数にA1型式のアドレスをセットし、new により生成したインスタンスから、行番号と列番号が取得できます。
あとはR1C1型式の時と同じ方法でセルを指定します。
|
1 2 3 4 |
//シート番号2のシートに対して、C5の位置にあるセルを取得 CellReference reference = new CellReference("C5"); IRow row = _workBook.GetSheetAt(2).GetRow(reference.Row) ?? _sheet.CreateRow(reference.Row); ICell cell = row.GetCell(reference.Col) ?? row.CreateCell(reference.Col); |
セルから値を取得
セルから値を取得するには、ICellクラスのプロパティを使います。
|
1 2 3 4 5 |
//シート番号=1 の5行3列のセルから文字列を取得する例 IRow row = _workBook.GetSheetAt(1).GetRow(5) ?? _sheet.CreateRow(5); ICell cell = row.GetCell(3) ?? row.CreateCell(3); var str = cell.StringCellValue; |
少々面倒なのは、セルに代入されている値の型によって、参照するプロパティが変わる点です。
セルの型と異なるプロパティを参照すると例外エラーが発生してしまいます。
| セルの型 | 意味 | 値を取得するプロパティ |
|---|---|---|
| CellType.String | 文字列 | StringCellValue |
| CellType.Numeric | 数値（整数、実数） | DateUtil.IsCellDateFormatted(セル)を使って判定 trueの場合：DateCellValue falseの場合：NumericCellValue |
| CellType.Boolean | true/false | BooleanCellValue |
| CellType.Formula | 数式 | CellFormula |
| CellType.Error | エラー | ErrorCellValue |
| CellType.Blank | ブランク（中身が空） | なし |
下記はシート名と行番号、列番号を指定することで、セルの型に応じたプロパティを参照し、結果を object 型で返すサンプルです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
public object GetValue(string sheetName,int rowIndex,int columnIndex) { IRow row = _workBook.GetSheet(sheetName).GetRow(rowIndex) ?? _sheet.CreateRow(rowIndex); ICell cell = row.GetCell(columnIndex) ?? row.CreateCell(columnIndex); //セルの型に応じたプロパティを参照する switch (cell.CellType) { case CellType.String: return cell.StringCellValue; case CellType.Numeric: if (DateUtil.IsCellDateFormatted(cell)) return cell.DateCellValue； else return cell.NumericCellValue; case CellType.Boolean: return cell.BooleanCellValue; case CellType.Formula: return cell.CellFormula; case CellType.Error: return cell.ErrorCellValue; case CellType.Blank: return null; default: return null; } } |
セルに値を代入
ICell クラスには SetCellValue というメソッドが用意されていて、これを使って値をセルに代入する（書き込む）ことが可能です。
|
1 2 3 4 5 |
//シート番号=1 の5行3列のセルに、実数の12345.6を代入する例 IRow row = _workBook.GetSheetAt(1).GetRow(5) ?? _sheet.CreateRow(5); ICell cell = row.GetCell(3) ?? row.CreateCell(3); cell.SetCellValue(12345.6); |
値を読み込む時は、セルの型に応じて参照するプロパティが異なっていましたが、SetCellValue メソッドは1種類で複数の型に対応しています。
| SetCellValueの引数で渡せる型 | 意味 |
|---|---|
| string | 文字列 |
| double | 実数 |
| DateTime | 日付 |
| bool | true/false |
| XSSFRichTextString | リッチテキスト |
次のソースコードは指定した行と列の位置に、任意の値を書き込むサンプルです。
対象となるセルを取得した後で、引数の型を取得し、if 文でそれに応じた型にキャストして SetCellValue メソッドに渡しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
public void SetValue(int rowIndex,int columnIndex,object value) { IRow row = _workBook.GetSheet(sheetName).GetRow(rowIndex) ?? _sheet.CreateRow(rowIndex); ICell cell = row.GetCell(columnIndex) ?? row.CreateCell(columnIndex); //引数で渡された型を判定 Type type = value.GetType(); //渡された value の型に応じてSetCellValueの引数の型をCASTする if (type == typeof(string)) cell.SetCellValue((string)value); else if (type == typeof(double) || type == typeof(float)) cell.SetCellValue((double)value); else if (type == typeof(int) || type == typeof(long)) cell.SetCellValue((int)value); else if (type == typeof(bool)) cell.SetCellValue((bool)value); else if (type == typeof(DateTime)) cell.SetCellValue((DateTime)value); else if (type == typeof(NPOI.XSSF.UserModel.XSSFRichTextString)) cell.SetCellValue((NPOI.XSSF.UserModel.XSSFRichTextString)value); else if (type == typeof(DBNull)) cell.SetCellValue(""); else cell.SetCellValue((string)value); } |
ちなみに、下から3行目の else if (type == typeof(DBNull)) cell.SetCellValue("") という記述は、DataTableとの連携（DataTableのセルから値を取り出してSetValueメソッドに渡した場合）を想定したものです。
DataTableのセルが空(null)の場合、DBNull の型を返してくるので、それを受けると空の文字列を返すようにしています。
セルのスタイル指定
セルのスタイルは、 IWorkBook クラスの CreateCellStyle メソッドを使ってICellStyle のオブジェクトを生成し、これに必要なスタイルを設定した後で、ICell の CellStyleプロパティに代入します。
スタイルには罫線も指定が可能で、その他にも様々なプロパティが用意されていますので、必要に応じて設定して下さい。
注意点は、色の指定は short 型の色番号を使って指定することです。
Color.Red とかで指定出来たらよいのですが、残念ながらそうなっていません。
しかし、HSSFColor クラスで色番号が定数化されており、Indexプロパティで参照可能です。
例えば赤色の場合、 HSSFColor.Red.Index という記述で色番号が取得できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
//セルの取得 IRow row = _workBook.GetSheetAt(1).GetRow(5) ?? _sheet.CreateRow(5); ICell cell = row.GetCell(3) ?? row.CreateCell(3); //スタイルオブジェクトの生成とスタイルの設定 ICellStyle style = _workBook.CreateCellStyle(); style.FillPattern = FillPattern.SolidForeground; //塗りつぶしパターン style.FillForegroundColor = HSSFColor.Red.Index; //塗りつぶす色の番号 style.BorderBottom = NPOI.SS.UserModel.BorderStyle.MediumDashDot; //下部のボーダーを太い破線で引く style.BottomBorderColor = HSSFColor.Blue.Index; //下部のボーダーの色を青にする style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //セルの文字をセンタリングする //セルにスタイルを設定 cell.CellStyle = style; |
セルのフォント設定
フォントは、IWorkBook クラスの CreateFont メソッドを使ってIFontのオブジェクトを生成し、フォント名やサイズ、色などを設定した後で、ICell のSetFontメソッドを呼び出して設定します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
//セルの取得 IRow row = _workBook.GetSheetAt(1).GetRow(5) ?? _sheet.CreateRow(5); ICell cell = row.GetCell(3) ?? row.CreateCell(3); //スタイルを設定 IFont font = _workBook.CreateFont(); font.FontHeightInPoints = 12; //フォントサイズを12pointにする font.FontName = ”メイリオ”; //フォントファミリーをメイリオにする font.Color = HSSFColor.Yellow.Index; //文字を黄色にする font.IsBold = true; //太字にする //セルにフォントを設定 cell.CellStyle.SetFont(font) |
文字列の一部のみ色やフォントを変える
EXCELでは、セルに入力されている文字列の一部分に対してフォントや色を変えることが可能ですが、NPOIでも同じことができます。
NPOI.XSSF.UserModel.XSSFRichTextString メソッドに任意の文字列を渡してあげると、NPOIで扱えるリッチテキスト型の文字列に変換できます。
NPOI.XSSF.UserModel.XSSFRichTextString rtext = new NPOI.XSSF.UserModel.XSSFRichTextString(value);
設定したいフォントと色は、 CreateFont メソッドを使います。
CreateFont メソッドの第１引数にはフォントサイズ、第２引数には太字の有無、第３引数にはフォントの色、第４引数にはフォント名が指定できます。
IFont font = CreateFont(int size, bool isBold, int color, string fontName)
リッチテキスト型には ApplyFont メソッドが用意されてるので、色やフォントを変えたい文字列上の場所（開始位置、終了位置）とフォントを指定してあげます。
rtext.ApplyFont(int start, int end, IFont font)
こうすることによって、文字列の一部に対して色やフォントを変えることができます。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
//シート１の5行３列のセルを取得 IRow row = _workBook.GetSheetAt(1).GetRow(5) ?? _sheet.CreateRow(5); ICell cell = row.GetCell(3) ?? row.CreateCell(3); //リッチテキストのオブジェクトを作成し、３文字目から５文字目までの３文字に対して //フォントサイズ=12、太字=true、色=2、フォント=メイリオ を設定する。 NPOI.XSSF.UserModel.XSSFRichTextString rtext = new NPOI.XSSF.UserModel.XSSFRichTextString(value); var font = CreateFont(12, true, 2, "メイリオ"); rtext.ApplyFont(3,5, font); //セルに代入 cell.SetCellValue(rtext); |
まとめ
今回はNPOI の基本的な使い方について解説しました。
ExcelをインストールしなくてもExcelブックが作成できるところが便利です。
さすがにグラフとかを挿入することは難しいですが、あらかじめExcelに数式やグラフを設定しておき、参照元のデータをNPOIで書き換えるという使い方が一番修正が楽で手っ取り早い方法ではないかと思います。
もしExcelファイルを読み書きする必要が生じたら、是非この記事を参考にして下さい。
