« ◇地図認識に於ける鉄道表示の重要性 | トップページ | ◆順列組合せの公式はΠ（総乗)を使うべき »

Javaでexcelファイル(.xlsx)を読む：POIサンプル

メモです。

・POIを使うサンプルコード
・excelデータと実行結果
・build.xmlの例
・POIライブラリのダウンロード(2012/9/27現在)
・参考:出力する例(行ごとに色を変える、カラム幅自動調整)
・サンプルのダウンロード
・Graphics2Dでセルに図形を描く

 JavaでEXCEL(.xlsx)ファイルを読む。POIを使うサンプルコード

POIはEXCELアクセスのためのライブラリで ワークブック / シート / 行 / セル を 表すクラス XSSFWorkbook / XSSFSheet / XSSFRow / XSSFCell からなります。
これらはインターフェース Workbook / Sheet / Row / Cell の実装になっています。

POIは依存ライブラリのダウンロードが途轍もなく難しいのですが、ダウンロードさえ できれば後は単純です。

次のようなプログラムで、ワークブックの内容を読取り表示することができます。 

// PoiTest.java
import java.io.*;
import org.apache.poi.hssf.usermodel.*; // 旧excelの場合
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
public class PoiTest{
   public static void main(String[]args){
      try{
         FileInputStream fi=new FileInputStream(args[0]);
         Workbook book =new XSSFWorkbook(fi);
         // Workbook book =new HSSFWorkbook(fi);
         fi.close();
         //for(Sheet sheet:book){ // XSSFWorkbookの場合
         for(int s=0;s<book.getNumberOfSheets();++s){ // 全シートをなめる(※)
            Sheet sheet= book.getSheetAt(s);
            //sheet.setForceFormulaRecalculation(true); // 数式解決(※２)
            System.out.println("--- "+sheet.getSheetName()+" ---");
            for(Row row:sheet){ // 全行をなめる
               for(Cell cell:row){ // 全セルをなめる
                  System.out.print(getStr(cell)+" ");
                  }
               System.out.println("");
               }
            }
         }
      catch(Exception e){
         e.printStackTrace(System.err);
         System.exit(1);
         }
      System.exit(0);
      }
   public static String getStr(Cell cell){ //データ型毎の読み取り
      switch(cell.getCellType()){
      case Cell.CELL_TYPE_BOOLEAN:
         return Boolean.toString(cell.getBooleanCellValue());
      case Cell.CELL_TYPE_FORMULA:
         return cell.getCellFormula();
         //return cell.getStringCellValue();(※）
      case Cell.CELL_TYPE_NUMERIC:
         return Double.toString(cell.getNumericCellValue());
      case Cell.CELL_TYPE_STRING:
         return cell.getStringCellValue();
         }
      return "";// CELL_TYPE_BLANK,CELL_TYPE_ERROR
      }
   }
//※:残念ながらWorkBookおよびHSSWorkbookではforeach形式は使えない
//※2:数式を解決して参照したい場合
この例では全データをなめていますが、個別位置を指定してデータを取得することも できます。
例えばシート名を指定してシートを得るには 
   XSSFSheet sheet=book.getSheet("シートX");
といった形で得ることができます。n番目の行を得るといったことも可能です。

このサンプルは単純な読み取りですが、POIでワークブックを生成することなどもできます。

POIのAPIドキュメントは次のアドレスで見ることができます。
http://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFWorkbook.html

XSSF???は.xlsx用のもので、旧型式の取り扱いのためのHSSF???もあります。 同じインターフェースの実装ですので、最初のWorkbookインスタンスの生成 以外では.xlsxも旧型式も同じプログラムで動かすことができます。 と思ったところXSSFWorkbookはforeach形式でSheetを得ることができますが、 XSSFWorkbookではできません。

 excelデータと実行結果

次のように２つのシートからなるエクセルファイルを上記プログラムで読んでみました。

次の結果が得られました。 

--- あいう ---
あ い う
a b c
1.0 2.0 3.0
--- XXX ---
AAA BBB CCC DDD
abc def ghi jkl
101 102 103 104
1.0～3.0は数値として読まれ、101～104は文字列として読まれています。

 build.xmlの例

このサンプルをビルドしたANTのbuild.xmlを載せます。 

<?xml version="1.0" encoding="UTF-8"?>
<!-- build.xml -->
<project default="main">
   <property name="classpath" value="../libs/poi-ooxml-3.8.jar
                                    ;../libs/poi-3.8.jar
                                    ;../libs/poi-ooxml-schemas-3.8.jar
                                    ;../libs/dom4j-1.6.1.jar
                                    ;../libs/xml-apis-1.0.b2.jar
                                    ;../libs/openjdk-6-b14.jar
                                    ;../libs/xmlbeans-2.3.0.jar
                                    "/>
   <property name="MAIN"      value="PoiTest" />
   <target name="main" depends="clean,compile,run">
      <echo>=== CLEAN COMPLIE and RUN done ===</echo>
   </target>
   <target name="compile">
      <javac srcdir           ="."
             includes         ="*.java"
             includeAntRuntime="false"
             encoding         ="UTF-8"
             debug="yes" optimize="no"
             classpath=".;${classpath}" />
   </target>
   <target name="run">
      <java classname  ="${MAIN}"
            classpath=".;${classpath}"
            fork="true" failonerror="true">
         <arg line="POItest.xlsx"/>
      </java>
   </target>
   <target name="clean">
      <delete dir="." includes="*.class,*.BAK"/>
   </target>
</project>
POIとその依存ライブラリは全て../libsの下に置いた形です。
excelファイルは起動引数で与えています。

 POIライブラリのダウンロード

POIを動かすには複雑なライブラリ依存を解決する必要があります。
実際にこの例のANTのbuild.xmlで指定しているライブラリは次のものです。 

   <property name="classpath" value="../libs/poi-ooxml-3.8.jar
                                    ;../libs/poi-3.8.jar
                                    ;../libs/poi-ooxml-schemas-3.7.jar
                                    ;../libs/dom4j-1.6.1.jar
                                    ;../libs/xml-apis-1.0.b2.jar
                                    ;../libs/openjdk-6-b14.jar
                                    ;../libs/xmlbeans-2.3.0.jar
                                    "/>

これらをダウンロードしたサイトは次の所です。（2012/10/13現在） 

poi-ooxml-3.8.jar
poi-3.8.jarのダウンロード
http://grepcode.com/snapshot/repo1.maven.org/maven2/org.apache.poi/poi-ooxml/3.8

poi-ooxml-schemas-3.8.jarのダウンロード
poi-bin-3.8-beta5-20111217.zipを
を
http://www.apache.org/dyn/closer.cgi/poi/dev/
のミラーサイトのbinフォルダからダウンロード。展開すると
複数のjarが入っている。その中のpoi-ooxml-schemas-3.8-beta5-20111217.jar
をリネームして使用

dom4j-1.6.1.jarのダウンロード
http://grepcode.com/snapshot/repo1.maven.org/maven2/dom4j/dom4j/1.6.1

xml-apis-1.0.b2.jar
http://grepcode.com/snapshot/repo1.maven.org/maven2/xml-apis/xml-apis/1.0.b2

openjdk-6-b14.jar
http://grepcode.com/snapshot/repository.grepcode.com/java/root/jdk/openjdk/6-b14

xmlbeans-2.3.0.jarのダウンロード
http://www.jarfinder.com/index.php/jars/versionInfo/14388
schemaとxmlbeans以外は最初のpoi-ooxml/3.8のページのDepends On:からたどれます。

poi-bin-3.8-beta5-20111217.zipの中に複数のjarが入っていますが それらだけでは動作させることができませんでした。

当初poi-ooxml-schemas-3.8.jarは3.7を使用したのですが、3.7では sheet.setForceFormulaRecalculation(true);を実行すると参照エラー がでるので、置き換えました。
poi-ooxml-schemas-3.8.20120326.jarがあるはずなのですが、 到達できませんでした。

poiの難しさは何といってもライブラリの依存を解決することに あります。運がいい人は一発で行く可能性もありますが、 下手すると、ダウンロード迷路をさまよい続けることになります。

 参考:出力する例(行ごとに色を変える、カラム幅自動調整)

参考までに、ワークブックを作成し、出力する例を載せます。灰色の部分は無くても動きます。 行ごとに色を変えるというのがデフォルト でないという恐るべきエクセルの仕様のためわざわざコードにしています。また、 色を付けると枠が消えるというとんでもないエクセルの仕様のため、わざわざ枠も 指定しなおしています。内容がカラムに収まるようにカラム幅の調整しています。 

// PoiCreTest.java
import java.io.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import java.awt.Color;
public class PoiCreTest{
   public static void main(String[]args){
      try{
         XSSFWorkbook  book      = new XSSFWorkbook();
         XSSFCellStyle style_odd = book.createCellStyle();
         setCellColorStyle(style_odd,0xFAFFFF);
         XSSFCellStyle style_evn = book.createCellStyle();
         setCellColorStyle(style_evn,0xE5F5FF);
         for(int s=0;s<3;++s){
            XSSFSheet sheet= book.createSheet("Sheet."+s);
            for(int r=0;r<4;++r){
               XSSFRow row= sheet.createRow(r);
               XSSFCellStyle style= row.getRowStyle();
               for(int c=0;c<5;++c){
                  XSSFCell cell= row.createCell(c,Cell.CELL_TYPE_STRING);
                  cell.setCellValue("Cell."+s+"."+r+"."+c);
                  cell.setCellStyle(r%2==0?style_odd:style_evn);
                  }
               }
            for(int c=0;c<5;++c){
               sheet.autoSizeColumn(c); // カラム幅を内容に揃える
               }
            }
         FileOutputStream os =new FileOutputStream("test_out.xlsx");
         book.write(os);
         os.close();
         }
      catch(Exception e){
         e.printStackTrace(System.err);
         System.exit(1);
         }
      System.exit(0);
      }
   static void setCellColorStyle(XSSFCellStyle style,int rgb){
      byte[] color={(byte)((rgb>>16)&0xFF) // 色は固定色または
                   ,(byte)((rgb>>8)&0xFF)  // byte配列から作る
                   ,(byte)((rgb)&0xFF)};   // XSSFColor
      style.setFillForegroundColor(new XSSFColor(color));//Backgroundではない
      style.setFillPattern(CellStyle.SOLID_FOREGROUND);  //無いと塗られない
      style.setBorderTop(CellStyle.BORDER_THIN);         //無いと枠が消える
      style.setBorderBottom(CellStyle.BORDER_THIN);      //無いと枠が消える
      style.setBorderLeft(CellStyle.BORDER_THIN);        //無いと枠が消える
      style.setBorderRight(CellStyle.BORDER_THIN);       //無いと枠が消える
      }
   }
こんな出力が得られます。

 サンプルのダウンロード

ソースとビルドキットの入ったpoiTst.zipを置きます。
「右クリック-対象をファイルに保存」でダウンロードできます

ライブラリは含まれていません。

次のファイルからなっています。

poiTst.java: サンプルプログラム
build.xml: ANTのビルドファイル
POItest.xlsx: 試験Excelデータ
A10_test.bat: 試験実行バッチ(antを呼ぶだけ）

 セルにJava/Graphics2Dで図形を書く:メモ

Graphics2Dでセルに図形を書く（正確には書いた図形をセルに置く）例を載せます。

残念ながら多くのAPIの意味が不明のままです。意味が分かった時点で 別記事にまとめます。

サイズに関する値は全く意味不明のまま、適当に調整しました。

行っているのは、一旦セルを並べた後、
・[1,1]セルの幅を変え
・イメージをBufferedImageにGraphics2Dでイメージを作り
・イメージをバイト配列に変え
・セル[1,1]にアンカーを打ち、イメージを書き込む
作業です 

// PoiCreTest.java
import java.io.*;
import java.awt.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import java.awt.Color;
import java.awt.image.BufferedImage;
import javax.imageio.ImageIO;
public class PoiCreTest{
   public static void main(String[]args){
      try{
         XSSFWorkbook  book      = new XSSFWorkbook();
         XSSFCellStyle style_odd = book.createCellStyle();
         setCellColorStyle(style_odd,0xFAFFFF);//F5FFFF
         XSSFCellStyle style_evn = book.createCellStyle();
         setCellColorStyle(style_evn,0xE5F5FF);//DDEEFF
         XSSFSheet sheet=book.createSheet("Sheet0");
         for(int r=0;r<4;++r){
            XSSFRow row=sheet.createRow(r);
            XSSFCellStyle style= row.getRowStyle();
            for(int c=0;c<5;++c){
               XSSFCell cell= row.createCell(c,Cell.CELL_TYPE_STRING);
               cell.setCellValue("Cell."+r+"."+c);
               cell.setCellStyle(r%2==0?style_odd:style_evn);
               }
            }
         // セルの幅と高さ設定
         XSSFRow row=sheet.getRow(1);
         row.setHeightInPoints(100);// point
         sheet.setColumnWidth(1,112*256/6);// 100pointくらい?
         // イメージを作る(Graphics2D on BufferedImage)
         BufferedImage img= new BufferedImage(100,100
                                             ,BufferedImage.TYPE_INT_RGB);
         Graphics2D    g2d= img.createGraphics();
         g2d.setBackground(Color.WHITE);
         g2d.clearRect(0,0,100,100);
         g2d.setColor(Color.BLACK);
         BasicStroke stroke = new BasicStroke(4.0f);
         g2d.setStroke(stroke);
         g2d.drawLine(10,10,50,50);
         // イメージデータをバイト配列で得る
         ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
         ImageIO.write(img, "png", byteArrayOut);
         // 指定cellにアンカーを打ち、イメージを出力する
         Drawing drawing     = sheet.createDrawingPatriarch();
         int dx1=0;  // 謎（セル内の開始位置ではなさそう
         int dy1=0;  // 謎
         int dx2=0;  // 謎
         int dy2=0;  // 謎
         int col1=1; // 開始セルのカラム位置
         int row1=1; // 開始セルの行位置
         int col2=0; // 謎（終了セルのカラム位置？）
         int row2=0; // 謎（終了セルの行位置？）
         ClientAnchor anchor = drawing.createAnchor(dx1,dy1,dx2,dx2
                                                   ,col1,row1,col2,row2);
         anchor.setAnchorType(0);
         int picIndex = book.addPicture(byteArrayOut.toByteArray()
                                       ,Workbook.PICTURE_TYPE_PNG);
         Picture pic = drawing.createPicture(anchor, picIndex);
         pic.resize(1.33);// 意味不明
         // 出力
         FileOutputStream os =new FileOutputStream("test_out.xlsx");
         book.write(os);
         os.close();
         }
      catch(Exception e){
         e.printStackTrace(System.err);
         System.exit(1);
         }
      System.exit(0);
      }
   static void setCellColorStyle(XSSFCellStyle style,int rgb){
      byte[] color={(byte)((rgb>>16)&0xFF) // 色は固定色または
                   ,(byte)((rgb>>8)&0xFF)  // byte配列から作る
                   ,(byte)((rgb)&0xFF)};   // XSSFColor
      style.setFillForegroundColor(new XSSFColor(color));//Backgroundではない
      style.setFillPattern(CellStyle.SOLID_FOREGROUND);  //無いと塗られない
      style.setBorderTop(CellStyle.BORDER_THIN);         //無いと枠が消える
      style.setBorderBottom(CellStyle.BORDER_THIN);      //無いと枠が消える
      style.setBorderLeft(CellStyle.BORDER_THIN);        //無いと枠が消える
      style.setBorderRight(CellStyle.BORDER_THIN);       //無いと枠が消える
      }
   }

次の様な出力が得られます。

2012年9月27日 (木) パソコン・インターネット |

« ◇地図認識に於ける鉄道表示の重要性 | トップページ | ◆順列組合せの公式はΠ（総乗)を使うべき »

トラックバック


この記事へのトラックバック一覧です： Javaでexcelファイル(.xlsx)を読む：POIサンプル:

« ◇地図認識に於ける鉄道表示の重要性 | トップページ | ◆順列組合せの公式はΠ（総乗)を使うべき »