Jakarta POI

目次

FrontPage
・Windows11
・Chromebook
・Random
・機械学習
・さくらVPS
・Fedora13
・SuSe10
・Docker
・Ansible
・Java
・Scala
・Python
・Ruby
・Lisp
・Computer
・GIS
・HTML
・Culture
・Link

訪問者

total: 58796
today: 11
yesterday: 9
now: 8

更新

最新の10件

人気の10件

MenuBar

※追記※

• この記事は Excel97-2003 形式しか扱えなかった POI 2.x 時代のもの
• POI 3.9 から Excel 2007 形式にも対応している。
  • Excel97-2003 は HSSFWorkbook クラスにマッピング
  • Excel2007 は XSSFWorkbook クラスにマッピング
• しかし、読み込み時にファイル形式を自動判別して Workbook インタフェース型として返してくれる API があるので、基本的に両者の違いを意識してプログラムを書く必要はない → xUnit Theories / Fixture を参照
• HSSFWorkbook(Excel97-2003) も XSSFWorkbook(Excel2007) も Workbook インタフェースを実装しているので、

  HSSFWorkbook book = new HSSFWorkbook(in);

  の代わりに

  Workbook book = WorkbookFactory.create(in);

  で読み込んで Workbook インタフェース型として扱うようにすれば、HSSFWorkbook とほぼ同じように使える
• maven には、poi-ooxml を指定する http://mvnrepository.com/artifact/org.apache.poi/poi-ooxml
• 読み書きルーチンのスニペット

  import lombok.extern.slf4j.Slf4j;
  import org.apache.poi.ss.usermodel.*;
  
  import java.time.LocalDate;
  import java.time.LocalDateTime;
  import java.util.Calendar;
  import java.util.Date;
  
  @Slf4j
  public class Util {
      private static Object cell2Object(Cell cell, CellType type) {
          switch (type) {
              case BLANK:
                  return "";
  
              case BOOLEAN:
                  return cell.getBooleanCellValue();
  
              case NUMERIC:
                  if(DateUtil.isCellDateFormatted(cell)){
                     return cell.getDateCellValue();
                  }
  
                  double val = cell.getNumericCellValue();
  
                  if (val == Math.ceil(val)) {
                      return (int) val;
                  } else {
                      return val;
                  }
  
              case STRING:
                  return cell.getStringCellValue();
  
              case FORMULA:
                  // I want to get formula value (not formula itself)
                  return cell2Object(cell, cell.getCachedFormulaResultType());
  
              default:
                  // if CellType is ERROR or BLANK or ...
                  return null;
          }
      }
      public static Object cell2Object(Cell cell) {
              return cell2Object(cell, cell.getCellType());
      }
  
      public static String cell2String(Cell cell) {
          Object obj = cell2Object(cell);
  
          return null == obj ? "" : obj.toString();
      }
  
      public static void Object2Cell(Sheet sheet, int rowNo, int colNo, Object value) {
          Row row = sheet.getRow(rowNo);
          if (null == row) {
              log.debug("CREATE ROW ({},{})={}", rowNo, colNo, value);
              row = sheet.createRow(rowNo);
          }
  
          Cell cell = row.getCell(colNo);
          if (null == cell) {
              log.debug("CREATE CELL ({},{})={}", rowNo, colNo, value);
              cell = row.createCell(colNo);
          }
  
          if (value instanceof Double) {
              cell.setCellValue((double)value);
          } else if (value instanceof Date) {
              cell.setCellValue((Date)value);
          } else if (value instanceof LocalDateTime) {
              cell.setCellValue((LocalDateTime) value);
          } else if (value instanceof LocalDate) {
              cell.setCellValue((LocalDate)value);
          } else if (value instanceof Calendar) {
              cell.setCellValue((Calendar)value);
          } else if (value instanceof RichTextString) {
              cell.setCellValue((RichTextString)value);
          } else if (value instanceof String) {
              cell.setCellValue((String)value);
          } else if (value instanceof Boolean) {
              cell.setCellValue((boolean)value);
          } else {
              cell.setCellValue(value.toString());
          }
      }
  }
  

• 次回Excel起動時に再計算させる

  // 全シート再計算フラグON
  wb.setForceFormulaRecalculation(true);
  sheet.setForceFormulaRecalculation(true);
  

POIとは †

1. Jakarta-POI
2. MS-WindowsのOLE(Object Linking and Embedding)オブジェクトをJavaから操作しようというライブラリ
3. POI(Poor Obfuscation Implementation)の構成

   POIFS(POI File System)	OLEドキュメントを読み書きするためのAPI
   HSSF(Horrible Spread Sheet Format)	Excel97形式のOLEドキュメントを読み書きするためのAPI。POIFSを利用
   HFD(Horrible Document Format)	Word97形式のOLEドキュメント読み書きするためのAPI。POIFSを利用
   HPSF(Horrible Property Set Format)	OLEドキュメントのファイル情報(作成者名など)を読み取るためのAPI。今のところ出力する機能はない

HSSFのデータ構造 †

Excelワークシートをいろいろといじることができますが、 ここでは以下の二つの場合を想定して必要なクラスとメソッドを整理します。

• 新規に単純なExcelファイル(Cellに値が入っているだけで罫線や式はない)を作成する
• 単純なExcelファイルを読み取る -- DBUnitの様にExcelを設定ファイルやテスト条件として使いたい --
• テンプレートとなるExcelファイルを読み込んで、値を書き加えて新しいファイルとしてセーブする

HSSFWorkbook(Excel Workbookファイル)
 |
 +---HSSFSheet(Work Sheet)
      |
      +---HSSFRow(行)
            |
            +---HSSFCell(セル)

1. HSSFWorkbook (最初のｼｰﾄ番号は0)

   メソッド	機能
   読み込み
   HSSFWorkbook()	新しいWorkbookを作る
   HSSFWorkbook(InputStream?)	既存のWorkbookを読み込む
   書き込み
   void write(OutputStream?)	Workbookの内容をファイルに書き出す
   byte[] getbytes()	Workbookの内容をbyte[]に書き出す
   Worksheet操作
   HSSFSheet cloneSheet(ｼｰﾄ番号)	ｼｰﾄをｺﾋﾟｰする
   HSSFSheet createSheet()	ｼｰﾄを作成する
   HSSFSheet getSheet(ｼｰﾄ名)	ｼｰﾄを取得する
   HSSFSheet getSheetAt?(ｼｰﾄ番号)	ｼｰﾄを取得する
   int getSheetIndex?(ｼｰﾄ名)	ｼｰﾄ番号を取得する
   String getSheetName?(ｼｰﾄ番号)	ｼｰﾄの名前を取得する
   void removeSheetAt?(ｼｰﾄ番号)	ｼｰﾄの削除
   HSSFSheet setSheetName?(ｼｰﾄ番号,ｼｰﾄ名)	ｼｰﾄに名前を付ける
   HSSFSheet setSheetName?(ｼｰﾄ番号,ｼｰﾄ名,ｴﾝｺｰﾄﾞ)	ｼｰﾄ名に日本語を使う時には、HSSFWorkbook.ENCODING_UTF_16 にする

    
2. HSSFSheet (最初の行番号は0)

   メソッド	機能
   int getFirstRowNum?()	データのある最初の行の行番号を取得する
   int getLastRowNum?()	データのある最後の行の行番号を取得する
   Iterator rowIterator()	データのある行をRowのIteratorで取得する
   HSSFRow getRow(行番号)	行を取得する
   HSSFRow createRow(行番号)	行を作成する
   void removeRow(HSSFRow)	行を削除する

    
3. HSSFRow (最初の列番号は0)

   メソッド	機能
   int getFirstCellNum?()	データのある最初の列の列番号を取得する
   int getLastCellNum?()	データのある最後の列の列番号を取得する
   Iterator CellIterator?()	データのある列をCellのIteratorで取得する
   HSSFCell getCell(列番号)	セルを取得する
   HSSFCell createCell(列番号)	セルを作成する
   void removeCell(HSSFCell)	セルを削除する

    
4. HSSFCell

   メソッド	機能
   int getCellType?()	セルの型を取得する
   Date getDateCellValue?()	セル値を日時として取得する
   double getNumericCellValue?()	セル値を数値として取得する
   String getStringCellValue?()	セル値を文字列として取得する
   void setCellValue?(セル値)	セルに値を書き込む。セル値の型は、double,String,Dateなどが使える
   void setEncoding(encoding)	セルの値に日本語を書き込む場合には HSSFCell.ENCODING_UTF_16 を設定する

    
   1. セルの型(getCellType?()の返値

      値	意味
      HSSFCell.CELL_TYPE_BLANK	空
      HSSFCell.CELL_TYPE_BOOLEAN	論理値
      HSSFCell.CELL_TYPE_ERROR	エラー
      HSSFCell.CELL_TYPE_FORMULA	式
      HSSFCell.CELL_TYPE_NUMERIC	数値
      HSSFCell.CELL_TYPE_STRING	文字列

       
   2. 日付型かどうかの判定
      • 日付型の時、getCellType?() の返値は HSSFCell.CELL_TYPE_NUMERIC
      • 続けて、HSSFDateUtil?.isCellDateFormmated?(cell) を実行し、返値がtrueならば日付型

サンプル †

1. 準備したワークシート
   
    
   
2. ソースコード

    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    
    public class POIExam {
     
     public static Object cell2Object(HSSFCell cell) {
       switch (cell.getCellType()) {
       case HSSFCell.CELL_TYPE_BLANK:
         return "";
    
       case HSSFCell.CELL_TYPE_BOOLEAN:
         return new Boolean(cell.getBooleanCellValue());
    
       case HSSFCell.CELL_TYPE_ERROR:
         return null;
    
       case HSSFCell.CELL_TYPE_FORMULA:
         return "";
    
       case HSSFCell.CELL_TYPE_NUMERIC:
    
         if (HSSFDateUtil.isCellDateFormatted(cell)) {
           return cell.getDateCellValue();
         } else {
           double val = cell.getNumericCellValue();
    
           if (val == Math.ceil(val)) {
             return new Integer((int) val);
           } else {
             return new Double(val);
           }
         }
    
       case HSSFCell.CELL_TYPE_STRING:
         return cell.getStringCellValue();
    
       default:
         return null;
       }
     }
    
     public static void main(String[] args) {
       try {
         // ワークブックの取得
         HSSFWorkbook book = new HSSFWorkbook(new FileInputStream(
             "TestData.xls"));
    
         int sheets = book.getNumberOfSheets();
         System.out.println("シート数\t: " + sheets);
    
         for (int sheetNumber = 0; sheetNumber < sheets; sheetNumber++) {
           System.out.println("----------------------------------------");
           System.out.println(sheetNumber + ":"
               + book.getSheetName(sheetNumber));
    
           HSSFSheet sheet = book.getSheetAt(sheetNumber);
           int firstRowNum = sheet.getFirstRowNum();
           int lastRowNum = sheet.getLastRowNum();
    
           System.out.println("  有効行: " + firstRowNum + "〜" + lastRowNum);
    
           for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
             System.out
                 .println("　　　----------------------------------------");
    
             HSSFRow row = sheet.getRow(rowNum);
    
             if (row == null) {
               continue;
             }
    
             int firstCellNum = row.getFirstCellNum();
             int lastCellNum = row.getLastCellNum();
    
             System.out.print("    " + row.getRowNum() + "行目");
             System.out.print("(有効列: " + firstCellNum + "〜"
                 + lastCellNum + ") = ");
    
             for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
               HSSFCell cell = row.getCell((short) cellNum);
    
               if (cell == null) {
                 continue;
               }
    
               System.out.print(cell2Object(cell) + " | ");
             }
    
             System.out.println("");
           }
         }
    
         // 新しいデータの追加
         HSSFSheet newSheet = book.createSheet("Sheet" + (sheets + 1));
         HSSFRow newRow = newSheet.createRow(0);
         HSSFCell newCell = newRow.createCell((short) 0);
    
         newCell.setEncoding(HSSFCell.ENCODING_UTF_16);
         newCell.setCellValue("新しいワークシート");
    
         book.write(new FileOutputStream("TestData2.xls"));
    
       } catch (Exception e) {
         e.printStackTrace();
       }
     }
    }
   

3. 実行結果 シート数 : 2

   ----------------------------------------
   0:Sheet1
    有効行: 0〜1
      ----------------------------------------
      0行目(有効列: 0〜4) = R1C1 | R1C3 | 
      ----------------------------------------
      1行目(有効列: 0〜4) = R2C2 | R2C3 | R2C4 | 
   ----------------------------------------
   1:Sheet2
    有効行: 0〜2
      ----------------------------------------
      0行目(有効列: 0〜3) = R1C1 | 
      ----------------------------------------
      1行目(有効列: 0〜3) = R2C2 | 
      ----------------------------------------
      2行目(有効列: 0〜3) = R3C2 | R3C3 | 

4. 作成したワークシート
   

はまったところ †

1. HSSFRow.getFirstCellNum?() / HSSFRow.getLastCellNum?() が返す値は、WorkSheet?全体の矩形の左端と右端

   	A	B	C	D	E
   1			O	O
   2		O	O	O	O
   3	O	O	O
   4			O	O	O
   5					O

   • どの行のFirstCellNum?()も0
   • どの行のLastCellNum?()も5
      
2. HSSFRow.getFirstCellNum?() / HSSFRow.getLastCellNum?() が返す値はあまり信用しない方が良い
   • 厳密にFirstCellNum?()列からLastCellNum?()列までデータがあると期待しないで、その範囲内にデータがあると考える。65535列全部見るよりはマシ。
      
3. HSSFRow.CellIterator?() から返ってくる Iterator は Cell の順番が滅茶苦茶
4. cell.getStringCellValue?()には、両端にスペースが混じることがある。

   cell.getStringCellValue().trim()が吉

    
5. 値を書き込む時には、先にエンコーディングを指定する

   ○
   cell.setEncoding(HSSFCell.ENCODING_UTF_16);
   cell.setCellValue(obj.toString()); 
   
   ×
   cell.setCellValue(obj.toString()); 
   cell.setEncoding(HSSFCell.ENCODING_UTF_16);

セルに色をつける †

HSSFFont fontStyle = book.createFont();
fontStyle.setColor(HSSFColor.WHITE.index);

HSSFCellStyle headerStyle = book.createCellStyle();
headerStyle.setFillForegroundColor(HSSFColor.BLACK.index);
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);　
headerStyle.setFont(fontStyle);

cell.setCellStyle(style);

• HSSFCell#setCellStyle?(style) で、セルにスタイルを設定する
• 複数のセルに同じスタイルオブジェクトを適用してよい。（というかOLEのフォーマット上そうすべき）

参考文献 †

1. Jakarta-POI, http://jakarta.terra-intl.com/poi/
2. Java Sticky Note, http://www.salicaceae.net/
3. 青木 淳夫, JakartaPOIでExcel形式の帳票を出力する [中級] POIライブラリによるExcel形式ファイルのダウンロード方法 , Code Zine http://codezine.jp/a/article.aspx?aid=41
4. POIでグラフ, ＠IT会議室 > Java Solution 会議室, http://www.atmarkit.co.jp/bbs/phpBB/viewtopic.php?topic=10824&forum=12&4

Java#Jakarta