JavaからExcelの起動なしでExcelファイルを読み書きする
Jakarta POIと、OLE構造化ストレージ(OLE構造化ファイル)
MS-OfficeのWORD/EXCELなどのファイル形式はOLE構造化ドキュメントで、簡単にいえば、ファイルシステム上のファイルの中に、ファイルシステムがあるようなものである。そのため、OLE構造化ドキュメントの中には、更に別のドキュメントや画像などのリソースを入れることが可能である。いまでこそ(あたりまえすぎて)重要とは思われていない感もあるが、まだ16ビットアプリケーション全盛期のアプリケーション間に深い断絶があった時代において、インプレースOLEとしてドキュメントの中に埋め込んだ別のドキュメントのアプリケーションが、その場で起動できるような仕組みを実現するために考えられた、相当複雑なファイル形式である。
Windowsには当然、この機構はAPIとして用意されているのだが、この複雑さが災いして、Windows APIを使わずに、このファイルを利用することは非常に困難であった。当然、Pure JAVAで実装しようとすれば、その複雑さを自前で実装しなければならないことになる。
Excelファイルを扱うにしても、Excelの表データのフォーマット自身はBIFF形式と呼ばれるもので比較的簡単に扱えるものらしいが*1、このBIFF形式のデータがOLE構造化ファイルの中に格納されていることがネックなのである。
そして、その困難を解決したのがPOI(Poor Obfuscation Implementation(貧しく曖昧な実装) )である。(よくまあ、こんな蔑称を授けていただいたものだと思う。*2 )
POIは名前はどうであれ、非常にいい仕事をしてくれるようだ。
POIを入手する
POIはApache Jakartaプロジェクトの1つである。
http://jakarta.apache.org/poi/
この中には構造化ドキュメント形式を扱うためのサブプロジェクトがいくつかあるが、その中のHSSFがExcelの読み書きを行うためのサブプロジェクトである。
といっても、HSSF個別で入手するわけではなくPOI一式になっている。
projectの下にあるdownloadのリンクからバイナリパッケージをダウンロードする。
たとえば、poi-bin-2.5.1-final-20040804.zipを取得すると、中に
- poi-2.5.1-final-20040804.jar
- poi-contrib-2.5.1-final-20040804.jar
- poi-scratchpad-2.5.1-final-20040804.jar
といったjarファイルがある。
Excelを読み書きする上で必要なのはpoi-2.5.1-final-20040804.jarである。
これをプロジェクトのクラスパスに通しておけばよい。
Excelファイルを作成してみる。
ExcelファイルをExcelを使わずにJAVAから作成してみるテスト。
package jp.seraphyware.hssftest; import java.io.BufferedOutputStream; import java.io.FileOutputStream; import java.io.OutputStream; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; public class ExcelWriteTest { public static void main(final String[] args) throws Exception { // 新規にワークブックをメモリ上に作成 final HSSFWorkbook workbook = new HSSFWorkbook(); int cnt = 0; // シートの作成 for (int sheetNo = 0; sheetNo < 10; sheetNo++) { final HSSFSheet worksheet = workbook.createSheet(); // シート名に日本語を使う場合は明示的にUTF-16を指定する必要あり。 workbook.setSheetName(sheetNo, "シート" + sheetNo, HSSFWorkbook.ENCODING_UTF_16); // 行 x 列で埋める for (int rowIdx = 0; rowIdx < 10; rowIdx++) { final HSSFRow row = worksheet.createRow(rowIdx); for (short colIdx = 0; colIdx < 20; colIdx++) { final HSSFCell cell = row.createCell(colIdx); // 日本語をセットするためにはUTF-16を指定する必要あり cell.setEncoding(HSSFCell.ENCODING_UTF_16); final String val = String.format( "%1$d表%2$d行%3$d列", sheetNo, rowIdx, colIdx); // 引数の型を認識してセルに値をセットする。 cell.setCellValue(val); cnt += 1; // スタイルの設定 final HSSFCellStyle style = workbook.createCellStyle(); final HSSFDataFormat format = workbook.createDataFormat(); style.setDataFormat(format.getFormat("@")); // セルの書式を文字列にする // 罫線の作成 if (cnt % 3 == 0) { style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); } // セルのフォントの色、サイズの指定 if (cnt % 7 == 0) { final HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.RED.index); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontHeight((short)(16 * 20)); // 16pt (1/20pt単位) style.setFont(font); } else { // セルの水平・垂直揃えを中央にする。 style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); } // スタイルのセット cell.setCellStyle(style); } } // カラム幅の設定 for (short colIdx = 0; colIdx < 20; colIdx++) { // 15文字幅。デフォルトフォントの「0」に対する1/256を単位とする。 final short width = 256 * 15; worksheet.setColumnWidth(colIdx, width); } } // ファイルへ保存 final OutputStream os = new BufferedOutputStream( new FileOutputStream("テスト1.xls")); try { workbook.write(os); } finally { os.close(); } } }
とくに難しい点はなかったと思う。
カラム幅の設定やフォントサイズの設定方法で多少困惑した。
日本語を使う場合にはUTF-16を指定しないと文字化けしてしまうので注意が必要である。
Excelファイルを読み込んでみる
ExcelファイルをExcelを使わずにJAVAから読み込んでみるテスト。
package jp.seraphyware.hssftest; import java.io.FileInputStream; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; public class ExcelReadTest { public static void main(final String[] args) throws Exception { // ファイルからOLE構造化ドキュメントにバインド final POIFSFileSystem fs = new POIFSFileSystem( new FileInputStream("テスト1.xls")); // ワークブックの読み込み final HSSFWorkbook workbook = new HSSFWorkbook(fs); // シート数の取得 final int sheetMax = workbook.getNumberOfSheets(); for (int sheetNo = 0; sheetNo < sheetMax; sheetNo++) { final HSSFSheet sheet = workbook.getSheetAt(sheetNo); final String sheetName = workbook.getSheetName(sheetNo); System.out.println("シート名: " + sheetName); // 定義されている最後の行番号を取得する final int rowMax = sheet.getLastRowNum(); for (int rowIdx = 0; rowIdx <= rowMax; rowIdx++) { final HSSFRow row = sheet.getRow(rowIdx); if (row == null) { // 空行はスキップ System.out.println("*空行"); continue; } // 定義されている最後の列番号を取得する final short colMax = row.getLastCellNum(); for (short colIdx = 0; colIdx <= colMax; colIdx++) { final HSSFCell cell = row.getCell(colIdx); if (cell == null) { // 空セルはスキップ System.out.println("空セル"); continue; } // セルの値を文字列として取得 final String value = cell.getStringCellValue(); System.out.println(String.format( "%1$d行%2$d列=%3$s", rowIdx, colIdx, value)); } } } } }
こちらはエンコードの指定とかも必要なく、普通に読んでくれた。
セルの位置を指定して読み込む形になるので、どこが終わりであるのか判断するためにsheet.getLastRowNum()、row.getLastCellNum()を使っている。
また、セルが飛び地になっている場合にはデータが存在せず行やセルがnullの場所もありえる。
結論
一般的な「表」の入出力という用途であれば、Excelで書き込む上でも読み込む上でも十分な能力があると思う。
さすがに図形の張り込み、グラフもろもろの見た目にこだわり始めると大変そうな気がするが、私にとって、そんな機能は必要ない。
これは非常に良いと思った。
Python2.5でExcelを使わずにExcelファイルを読み書きする。
動機
上記でJAVAを試したから、というわけではなくて、むしろ、こちらが目的。
Pythonのような軽量言語ではゴリゴリ作りこむよりも、利用できるものは利用してしまったほうがよいと思う。そこでExcelを使って入力データを作ってからPythonで読み込ませたり、あるいはPythonで集計した結果をExcelで出力できたら良さそうだな、というところが動機。
ファイル形式としてはCSVファイルでも良かったりするのだが、これは案外難しいし、Excelの複数シートの扱いや、出力するとしても色づけ、罫線づけは魅力的だと思う。
準備
PythonでExcelファイルを扱うためのライブラリとして、pyExceleratorというものがあるらしい。これを使うと、Excelの読み書きが簡単にできる。
http://sourceforge.net/projects/pyexcelerator/
ライセンスはBSD Lisenceである。
- ダウンロードページから、pyExcelerator-xxx.zipを取得する。
- これを展開し、setupを実行する。
python setup.py install
これでpyExceleratorがインストールされる。
Excelファイルを作成してみる。
ExcelファイルをExcelを使わずにPythonから作成してみるテスト。
#!/usr/bin/env python # -*- coding: utf-8 -*- import pyExcelerator # スタイルのキャッシュ styles = {}; # パターン番号からスタイルを生成しキャッシュする. # 同じスタイルとなる場合は生成済みのものを使用する. # (Excel97-2003形式で使えるスタイル数は4000個までのため # 同じスタイルの場合は新しいスタイルを作らないことで節約する.) # http://office.microsoft.com/ja-jp/excel-help/HA010077823.aspx def findOrCreateStyle(cnt): # パターン番号から、カラーと罫線の組み合わせキーを作成する. # (見た目が段違いになるように割り切れないようにしておく.) color = cnt % 9 border = cnt % 3 key = (color, border) # キーに対するスタイルが未作成の場合のみスタイルを作成する. if key not in styles: style = pyExcelerator.XFStyle() style.num_format_str = "@" # 文字列フォーマット # セルの水平・垂直揃えを中央にする alignment = pyExcelerator.Formatting.Alignment() alignment.horz = pyExcelerator.Formatting.Alignment.HORZ_CENTER alignment.vert = pyExcelerator.Formatting.Alignment.VERT_CENTER style.alignment = alignment # 罫線をつける if border in (1, 2): borders = pyExcelerator.Formatting.Borders() if border == 1: borders.bottom = pyExcelerator.Formatting.Borders.MEDIUM if border == 2: borders.left = pyExcelerator.Formatting.Borders.THIN style.borders = borders font = pyExcelerator.Formatting.Font() # 定義色: 0黒 1白 2赤 3緑 4青 5黄 6マゼンダ 7シアン # 詳細はBIFFRecords.py 参照 font.colour_index = color % 8 font.bold = True # 太字 font.height = 16 * 20 # 16pt (1/20pt単位で指定する) style.font = font # スタイルの保存 styles[key] = style # 作成済みスタイルを返す. return styles[key] def main(): # 新しいブックをメモリ上に作成する workbook = pyExcelerator.Workbook() cnt = 0 # 新しいシートを作成する。 for sheetNo in range(0, 10): # UNICODEでシートタイトル指定 worksheet = workbook.add_sheet(u"シート%d" % sheetNo) # 行 x 列 でセルを埋める for row in range(0, 10): # 1行目は0 for col in range(0, 20): # A列は0 # スタイルを設定 style = findOrCreateStyle(cnt) cnt += 1 # セルに書き込み worksheet.write(row, col, label = u"%d表%d行%d列" % (sheetNo, row, col), style = style) # 列幅を設定する for col in range(0, 20): # 15文字幅 (デフォルトのフォントで文字「0」の1/256を単位とする) worksheet.col(col).width = 256 * 15 # ブックをファイルに保存する。(暗黙で上書き保存) workbook.save(u"テスト1.xls") # 実行 main()
驚くことでもないのかもしれないが、Unicode文字列として指定さえすれば文字化けもすることなく日本語が普通に扱える。
しかも、JavaのPOIで書くよりもシンプルで簡単なようである。
このpyExceleratorのプロジェクトには、まともなドキュメントのようなものがないようなのだが(さがしてもみつからない)、サンプルを見れば、使い方はだいたい分かるだろう。
それでも分からなければソースを見れば良い。丁寧なコメントがついていて、ついでにExcelのBIFFフォーマットにもちょっと詳しくなれるかもしれない特典付きである。
とりあえず、Excelファイルを書くのは、とても簡単だという印象。
Excelファイルを読み込んでみる
ExcelファイルをExcelを使わずにPythonから読み込んでみるテスト。
#!/usr/bin/env python # -*- coding: utf-8 -*- import pyExcelerator def main(): sheets = pyExcelerator.parse_xls(u"テスト1.xls") for (sheetName, sheet) in sheets: print u"シート名: %s" % sheetName # シートは有効なカラムをあらわす(行・列)のタプルをキーとするマップとして扱われる。 # よって、まず、有効なカラム位置を取得し、行列順に並び替える。 keys = sorted(sheet.keys()) for (row, col) in keys: value = sheet[(row, col)] print u"%2d行%2d列: %s" % (row, col, value) # 実行 main()
これでExcelの全シート、全セルの値を標準出力に送り出せる。
サンプルコードではcp???という具合にキャラクターセットを指定していたが、省略しても問題なく日本語が読み込めた。(UTF-16扱いなことが幸いしたのであろうか。)
すごい短いことになっているが、だが、喜ぶのは早計である。
見てのとおり、Excelの書き方と読み方はぜんぜん違う。
書き方では、一応、シートがあって行があってセルがあって…、というオブジェクトの体裁をもっているのだが、読み込みのほうはシート名、セル位置を表すタプルとセルデータのスパース配列*1のペアとして「解析済み」の形で取得される。とりあえず、セルのデータを読む上では不自由ない。(だから、私的には問題ないのだが。)
だが、Excelファイルを開いて編集して書き戻す、といったRoundtripな使い方は無理っぽいのである。
とはいえ、解析済みで「スパース配列」として戻ってくるところはうれしいかもしれない。むしろ、余計な手間もなく簡単に目的のコードがかけそうだからである。
