2010年08月13日

POIでセルの値をとるのは大変 数値(通貨)編

一般的に紹介されている取得方法では、(私の)意図したとおりに取得できなかったので、POIでセルの値を見た目どおりに取得する方法について数回に分けて記載します。

どんな場合に問題になるのでしょうか?
セルの値を取得する際に、セルのタイプを把握して適したメソッドを使用しないと例外が発生してしまいます。
このあたりはあちこちのページで紹介されているので困惑することはありません。
しかし、セルが数値タイプや日付タイプで書式が設定されており、表示されているとおりにStringでほしい場合など、問題が顕著になります。
例えばセルに「-12345.00」という値が設定されており、書式が「▲1,234」だった場合に、
  double value = cell.getNumericCellValue();
  String retValue = String.valueOf(value);
とすると、retValueには「-12345.00」が詰められます。
欲しいのは表示どおりの「▲12,345」だったとしても自分で変換しなければなりません。
すべてのセルについてコーディング時に把握できていれば解決できますが、
ユーザが入力するExcelファイルを読み込む場合には、なかなか難しいのが現実ではないでしょうか。

ではどうやって解決すればよいのでしょう?
結論としては DataFormatter#formatCellValue() を使いました。
  DataFormatter formatter = new DataFormatter();
  String retValue = formatter.formatCellValue(cell);
とすると、表示している内容を取得できます。
ところがこれですべてが解決すれば問題はないのですが、
このformatCellValue()が曲者で、場合によってはまともに返してくれません。
通貨「-¥123,456」の書式で設定したセルから帰ってくる値は、
なんと「($123,456)」!!
驚きですが、ステップ実行するとHSSFDataFormat#getFormat()が直接の原因であることがわかります。
(根本原因はフォーマットのインデックス番号にありそう)
BuiltinFormatsクラスから先に検索して、フォーマットのインデックスが存在する値「($123,456)」を返してしまいます。
結局すべてを救うことを断念し、概ね使えているということで上記方法で妥協しました。
もともとフォーマットで負数を赤字にしているフォーマットは救いようがないこともあるので、どこかに線引きが必要です。
あと1点、曲者のformatCellValue()は文字列「_ 」を最後につけて返すことがあります。
これらのことを踏まえて最終的なソースは下記のようにしました。
  DataFormatter formatter = new DataFormatter();
  String retValue = formatter.formatCellValue(cell);
  if (retValue.endsWith("_ ")) {
    retValue = retValue.substring(0, retValue.length() -2);
  }

それでは表示どおりの値で取得できるフォーマットとは?
下記に取得できるフォーマット、できない場合にどのような値になるかを表にしてみました。
フォーマット 表示どおり取得可能 不可能だった際の取得フォーマット
標準(数値)
標準(文字列)
数値((1,234)
数値((1,234))
数値(1,234×-1234
数値(-1,234)
数値(-1,234
数値(△1,234)
数値(▲1,234)
数値((1234.56)
数値((1234.56))
数値(1234.56×-1234.56
数値(-1234.56)
数値(-1234.56
数値(△1234.56)
数値(▲1234.56)
通貨((¥123,456)×(123,456)
通貨((¥123,456))×(123,456)
通貨(¥123,456×-123,456
通貨(-¥123,456)×($123,456)
通貨(-¥123,456×($123,456)
通貨((¥123,456.78)×(123,456.78)
通貨((¥123,456.78))×(123,456.78)
通貨(¥123,456.78×-123,456.78
通貨(-¥123,456.78)×($123,456.78)
通貨(-¥123,456.78×($123,456.78)
会計(¥)×-123456
会計×-123456
会計(¥ 少数2桁)×-123456.78
会計(少数2桁)×-123456.78
パーセンテージ
パーセンテージ(少数2桁)
タグ:java Poi
posted by しん at 12:51| Comment(0) | POI | このブログの読者になる | 更新情報をチェックする

2010年08月14日

POIでセルの値をとるのは大変 日付編(1)

前回はPOIで値を取得することが一筋縄ではいかないこと、数値タイプについての解決策(妥協?)をご紹介しました。
今回は日付についての解決策をご紹介します。

POIで日付タイプであると判定する場合、
 1.セルタイプが数値であること
 2.書式が日付型であること
を確認して値の取得を行います。
一般的には
  switch(cell.getCellType()) {
   ・・・
  case Cell.CELL_TYPE_NUMERIC:
    if (DateUtil.isCellDateFormatted(cell)) {
      // 日付型として値を取得
      return cell.getDateCellValue();
    }
    // それ以外は数値のセルとして値を取得
    ・・・
    break;
  case ・・・
のようになると思います。
前回同様に「表示されているとおりの内容をString型で取得」するには、セルのフォーマットを取得して変換が必要になります。
ここで下記の大きな2つの問題があります。
 1.isCellDateFormatted()が日付として認識できないフォーマットがある
 2.フォーマットの文字列を取得できない場合がある
根本的には一緒のようですが、ひとつずつ問題を見ていきましょう。

isCellDateFormatted()が日付として認識できないフォーマットがある
例えば「[$-411]ggge"年"m"月"d"日"」は日付として認識してくれません。
ユーザ定義も含めて一切合財すべてのケースを救ってほしいとは思いませんが、Excelでセルの書式設定する際に選択肢にあがっている程度のものはなんとかしてほしいと願うのは人情ではないでしょうか。
isCellDateFormatted()は、最初にセルのフォーマットインデックスを調べて、Excelの内部的に「日付フォーマット」と認識しているものである場合にtrueを、
それ以外の場合はフォーマットの文字列をチェックして日付フォーマットの文字列のみで構成されているかで判定します。
上記の例では、内部的な日付フォーマットでもないし、和暦の部分が日付フォーマットの文字列ではないと認識されてしまうわけです。

フォーマットの文字列を取得できない場合がある
どんな場合にフォーマットの文字列を取得できないのでしょうか。
数値編の場合同様、DataFormatter#formatCellValue()を使うと、表示されている内容の文字列で取得できます。
ところが、このメソッドは日付の判定にDateUtil#isCellDateFormatted()を使用しています。
つまり、問題点1と同じ問題を含んでいるわけです。
また、CellStyle#getDataFormatString()でフォーマットを取得しても、Excelでの表現なので、このままではJava側で文字列への変換に使用できません。

ではどうやって解決すればよいのでしょう?
ここまで長々と問題点を記載してきましたが、検索の結果から見ると、POIの開発中にも議論がなされていたようで、
「インデックスによる解決は、常に同じフォーマットになるか保証できないのでやめたほうがよい」とのこと。
また、ExcelとOpenOffice間ですら、日付としての認識に相違がある(らしい)ことから、
またしても妥協をします。
日本のお客様向けの開発をしていて、和暦に適応できないのは困るので、対応したいと思います。
解決方法の方針として、
 1.java.text.DateFormatをもつenum CellDateFormatを定義
 2.CellDateFormatに変換を行いたいフォーマットを登録
 3.日付の判定にDateUtil#isCellDateFormatted()を使用し、
   falseだった場合でも、CellDateFormatに存在する場合にtrueとする
 4.CellDateFormatから返されるjava.text.DateFormatで文字列に変換する
とし、次回に具体的なコーディングをしていきます。
タグ:Poi java
posted by しん at 11:33| Comment(0) | POI | このブログの読者になる | 更新情報をチェックする

2010年08月16日

POIでセルの値をとるのは大変 日付編(2)

前回はExcelのセルが日付タイプの際に表示されている内容で取得することが困難なことを説明しました。
今回は具体的なコーディングを行います。

方針をおさらいすると下記のとおりになります。
 1.java.text.DateFormatをもつenum CellDateFormatを定義
 2.CellDateFormatに変換を行いたいフォーマットを登録
 3.日付の判定にDateUtil#isCellDateFormatted()を使用し、
   falseだった場合でも、CellDateFormatに存在する場合にtrueとする
 4.CellDateFormatから返されるjava.text.DateFormatで文字列に変換する
それでは順番に説明していきます。

1.java.text.DateFormatをもつenum CellDateFormatを定義
2.CellDateFormatに変換を行いたいフォーマットを登録
フォーマットインデックスでの解決はお勧めできないとしておきながら、インデックスからフォーマットのマッピングを行っています。
この部分が最大の妥協です。
そのため必要最小限のフォーマットのみを登録しておき、それ以外はデフォルトフォーマットになるようにしました。(NONE:ソースの2行め)
対応対象のインデックスは、
 @Excelの内部的に「日付フォーマット」と認識しているもの、
 Aセルの書式設定でデフォルトで選択可能であり、
  かつ一般的に使いそうなものからごく一部のみ
としました。
今回はenumを利用しましたが、Mapにしてもよいかもしれません。
enumを利用した理由は、定数のように扱いたかったからです。
ここまでのところのコーディング例を記載します。
public enum CellDateFormat {
  NONE(-1, new SimpleDateFormat("yyyy/MM/dd")),
  FORMAT_14(14, new SimpleDateFormat("yyyy/M/d")),
  ・・・必要なフォーマットを登録しておきます
  FORMAT_58(58, new SimpleDateFormat("GGGGyy年M月d日", new Locale("ja", "JP", "JP")));

  private int formatId;          // Excelのフォーマットインデックス
  private DateFormat dateFormat; // 文字列への変換フォーマット
  private CellDateFormat(int format, DateFormat dateFormat) {
    this.formatId = format;
    this.dateFormat = dateFormat;
  }
  public DateFormat getDateFormat() {
    return this.dateFormat;
  }
  public int getFormatNo() {
    return this.formatId;
  }
  /**
   * フォーマットインデックスから該当するCellDateFormatを返す
   */
  public static CellDateFormat getFormt(int formatId) {
    for (CellDateFormat elm : CellDateFormat.values()) {
      if (elm.getFormatNo() == formatId) {
        return elm;
      }
    }
    return NONE;
  }
  public static boolean contains(int formatId) {
    for (CellDateFormat elm : CellDateFormat.values()) {
      if (elm.getFormatNo() == formatId) {
        return true;
      }
    }
    return false;
  }
}

3.日付の判定にDateUtil#isCellDateFormatted()を使用し、
  falseだった場合でも、CellDateFormatに存在する場合にtrueとする
4.CellDateFormatから返されるjava.text.DateFormatで文字列に変換する
ここまで準備ができれば、あとは変換ロジックを呼び出すだけです。
日付と認識できれば、最低限デフォルトのフォーマットで返すことができます。
先のCellDateFormatで定義しているフォーマットは正しく表示どおりの文字列を返すことができます。
  ・・・
  case Cell.CELL_TYPE_NUMERIC:
    // 日付・整数・少数の判別を行う
    if (DateUtil.isCellDateFormatted(cell)
      || CellDateFormat.contains(cell.getCellStyle().getDataFormat())
    ) {      //日付
      Date theDate = cell.getDateCellValue();
      DateFormat dateFormat 
        = CellDateFormat.getFormt(cell.getCellStyle().getDataFormat()).getDateFormat();
      return dateFormat.format(theDate);
    } else { // 数値
  ・・・

表示どおりの値で取得できるフォーマットとは?
下記に上記のコーディング例での変換可否を記載します。 >
フォーマット 日付として認識 インデックス フォーマットどおりの文字列として取得可能
*yyyy/M/d14
d-mmm-yy○15
d-mmm16
mmm-yy17
h:mm AM/PM18
h:mm:ss AM/PM19
h:mm20
h:mm:ss21
yyyy/m/d h:mm22
m/d/yy30※
yyyy"年"m"月"d"日"31※
h"時"mm"分"32※
h"時"mm"分"ss"秒"33※
mm:ss45
mm:ss.047
yyyy"年"m"月"55※
m"月"d"日"56※
[$-411]ge.m.d57※
[$-411]ggge"年"m"月"d"日"58※
*yyyy年M月d日187※×
[$-F800]dddd, mmmm dd, yyyy187※×
Gyy.m.d×188※×
GGGGyy年m月d日×189※×
yyyy年M月d日×181※×
yyyy年M月×190※×
M月d日×182※×
yyyy/M/d180※×
yyyy/M/d H:m a191※×
yyyy/M/d HH:m183※×
M/d192※×
M/d/yy193※×
MM/dd/yy194※×
dd-MMM195※×
d-MMM-yy196※×
dd-MMM-yy184※×
MMM-yy197※×
MMMM-yy198※×
[$-411]ge.m.d;@×188※×
yyyyMMdd185※×
yyyy/MM/dd186※×
※Excelの内部的に「日付フォーマット」と認識していないもの
 実行環境によって異なる可能性がある
 (表示どおりのフォーマットにならない可能性がある)
タグ:java Poi
posted by しん at 22:02| Comment(2) | POI | このブログの読者になる | 更新情報をチェックする
×

この広告は1年以上新しい記事の投稿がないブログに表示されております。