前回までのあらすじ
Apache POI が共有数式の作成に対応していないっポイ！(渾身のギャグ)(※1)
※1)今回調査したバージョンは3.9だけど、最新の4.0.1でも未対応の模様。
というわけで、Open XML ファイルフォーマットのオブジェクトを直接いじってどうにかしてみる。
まずは適当に数式が共有されそうなデータをExcelでつくって中を覗いてみる。
- A~B列に数値を入力
- C1 に "=A1*B1" を入力
- C2:C3 に C1 をコピー
xl/worksheets/sheet1.xml
<sheetData>
<row r="1" spans="1:3" x14ac:dyDescent="0.4">
<c r="A1">
<v>1</v>
</c>
<c r="B1">
<v>4</v>
</c>
<c r="C1">
<f>A1*B1</f>
<v>4</v>
</c>
</row>
<row r="2" spans="1:3" x14ac:dyDescent="0.4">
<c r="A2">
<v>2</v>
</c>
<c r="B2">
<v>5</v>
</c>
<c r="C2">
<f t="shared" ref="C2:C3" si="0">A2*B2</f>
<v>10</v>
</c>
</row>
<row r="3" spans="1:3" x14ac:dyDescent="0.4">
<c r="A3">
<v>3</v>
</c>
<c r="B3">
<v>6</v>
</c>
<c r="C3">
<f t="shared" si="0" />
<v>18</v>
</c>
</row>
</sheetData>
C2とC3が共有数式になってますね。
この場合、C1は通常セルになっているけど、C1を共有元にすることも可能かな。
Excelの出力結果を参考に値をセット。
- cell は XSSFCell のインスタンス
- 値si は共有数式のインデックスなので式の数に合わせて採番
- 要素v は計算したら設定される（ハズ）なので省略
共有元
CTCellFormula f = CTCellFormula.Factory.newInstance();
f.setT(STCellFormulaType.SHARED);
f.setRef("C1:C3");
f.setSi(0);
f.setStringValue("A1*B1");
CTCell c = cell.getCTCell();
c.setF(f);
共有先
CTCellFormula f = CTCellFormula.Factory.newInstance();
f.setT(STCellFormulaType.SHARED);
f.setSi(0);
CTCell c = cell.getCTCell();
c.setF(f);
実行してみたところ、計算でエラー。
java.lang.IllegalStateException: Master cell of a shared formula with sid=0 was not found
at org.apache.poi.xssf.usermodel.XSSFCell.convertSharedFormula(XSSFCell.java:383)
at org.apache.poi.xssf.usermodel.XSSFCell.getCellFormula(XSSFCell.java:368)
at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getFormulaTokens(XSSFEvaluationWorkbook.java:148)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:286)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:230)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:264)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCell(XSSFFormulaEvaluator.java:151)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:327)
at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:318)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateAllFormulaCells(XSSFFormulaEvaluator.java:238)
XSSFSheet の sharedFormulas に該当がないと怒られている。
org/apache/poi/xssf/usermodel/XSSFSheet.java
/**
* cache of master shared formulas in this sheet.
* Master shared formula is the first formula in a group of shared formulas is saved in the f element.
*/
private Map<Integer, CTCellFormula> sharedFormulas;
private TreeMap<String,XSSFTable> tables;
private List<CellRangeAddress> arrayFormulas;
private XSSFDataValidationHelper dataValidationHelper;
リフレクションでねじ込む。
- sheet は XSSFSheet のインスタンス
Field field = sheet.getClass().getDeclaredField("sharedFormulas");
field.setAccessible(true);
Map<Integer, CTCellFormula> sharedFormulas = (Map<Integer, CTCellFormula>) field.get(sheet);
sharedFormulas.put(0, f);
再度実行してみたところ正常終了。
ファイルを開いてみたところ、計算結果も表示されました。
いけるじゃーん。
CTCell#setF を呼んでいる回数は 行×列 のままなので、パフォーマンス的には大差ないか？と思ったけど、大幅に向上。
CTCellFormula に数式が含まれている場合にコストがかかるんだな。
はまった点とか
- CTCell#setF に渡した CTCellFormula のインスタンスを更新しても、CTCell には反映されない。再セットが必要。
- shiftRows で行をずらすと、セル本体の ref と数式は更新されるが、sharedFormulas 側は更新されない。
いつものラム肉がない！
注文していた電源が到着。
予備電源を使い続けてもよかったのだけど、故障した剛力短2がサイズ小さくてよかったので後継機を購入。
RSS1.0
2019/02/07 23:50:26
注文していた電源が到着。