6. 数式の指定方法

6.1. @XlsFormula

書き込み時にセルの数式を指定するためのアノテーションです。 [ver.1.5+]

注釈

数式指定用のアノテーションは、セルをマッピングするアノテーション @XlsCell 、 @XlsLabelledCell、 @XlsArrayCells、 @XlsLabelledArrayCells、 @XlsColumn、 @XlsMapColumns、 @XlsArrayColumns を付与しているプロパティに対して有効になります。

6.1.1. 数式を直接指定する場合（属性value）

数式を直接指定する場合は、属性 value で指定します。 Javaのアノテーションの仕様上、属性valueのみを指定する時には、属性名の省略可能がです。

数式を指定する際に、メッセージファイルと同様に、変数やEL式が利用可能です。

  • 変数は {変数名} で定義します。

  • EL式は ${EL式} で定義します。

変数、EL式中では、予め次の変数が登録されており、セルの値ごとに変わります。

表 - 6.1.1 予め登録されている変数

変数名

説明

rowIndex

処理対象のセルの行のインデックス。0から始まります。

columnIndex

処理対象のセルの列のインデックス。0から始まります。

rowNumber

処理対象のセルの行番号。1から始まります。

columnNumber

処理対象のセルの列番号。1から始まります。

columnAlpha

処理対象のセルの列の名前。Aから始まります。

address

処理対象のセルのアドレス。 A1 の形式です。

targetBean

処理対象のプロパティが定義されているJavaBeanのオブジェクトです。

cell

処理対象のセルのオブジェクトです。POIのクラス org.apache.poi.ss.usermodel.Cell のオブジェクトです。

さらに、よく使う関数が登録されており、呼び出すことができます。 関数の実態は、 com.gh.mygreen.xlsmapper.expression.CustomFunctions です。

表 - 6.1.2 予め登録されている関数

関数の形式

説明

f:colToAlpha(<列番号>)
1から始まる列番号を英字名に変換します。
v2.2以前では、関数の接頭語は x: でした。v2.3から f: に変更されました。

自身のJavaBeanも変数 targetBean として登録されているため、任意のメソッドを呼び出すこともできます。

 1@XlsSheet(name="サンプル")
 2public class SampleSheet {
 3
 4    // 数式の指定
 5    @XlsOrder(1)
 6    @XlsLabelledCell(label="更新日付", type=LabelledCellType.Right)
 7    @XlsFormula("TODAY()")
 8    private Date date;
 9
10    @XlsOrder(2)
11    @XlsHorizontalRecords(tableLabel="レコード", terminal=RecordTerminal.Border)
12    @XlsRecordOption(overOperation=OverOperation.Insert)
13    private List<SampleRecord> records;
14}
15
16public class SampleRecord {
17
18    // マッピングした位置情報
19    private Map<String, CellPosition> positions;
20
21    @XlsColumn(columnName="名前")
22    private String name;
23
24    @XlsColumn(columnName="国語")
25    private int kokugo;
26
27    @XlsColumn(columnName="算数")
28    private int sansu;
29
30    // 数式の指定（変数、EL式を使用して指定）
31    @XlsColumn(columnName="合計")
32    @XlsFormula(value="SUM(${f:colToAlpha(targetBean.kokugoColNum)}{rowNumber}:${f:colToAlpha(targetBean.sansuColNum)}{rowNumber})", primary=true)
33    private int sum;
34
35    // プロパティ「kokugo」の列番号を返す。
36    public String getKokugoColNum() {
37        CellPosition position = positions.get("kokugo");
38        return position.addRow(1);
39
40    }
41
42    // プロパティ「sansu」の列番号を返す。
43    public String getSansuColNum() {
44        CellPosition position = positions.get("sansu");
45        return position.addRow(1);
46    }
47
48}

6.1.2. 数式を組み立てるメソッドを指定する場合（属性methodName）

条件により数式を変更するような場合や、複雑な数式を組み立てる場合、数式を組み立てるメソッドを指定できます。

メソッドの条件は次のようになります。

  • 定義位置は、プロパティが定義してあるJavaBeanのクラスと同じ箇所。

  • 修飾子は、public/private/protected などなんでもよい。

  • 引数は、指定しないか、または次の値が指定可能。順番は任意。

    • セルのオブジェクト org.apache.poi.ss.usermodel.Cell 。

    • シートのオブジェクト org.apache.poi.ss.usermodel.Sheet 。

    • セルの座標 com.gh.mygreen.xlsmapper.util.CellAddress 。

      • 0から始まります。

      • 同じ座標を示すクラスとして、 java.awt.Point 、org.apache.poi.ss.util.CellAddress が使用可能です。

    • システム設定 com.gh.mygreen.xlsmapper.Configuration 。

  • 戻り値は、String型。

    • nullまたは空文字を返すと、ブランクセルとして出力されます。

 1@XlsSheet(name="サンプル")
 2public class SampleSheet {
 3
 4    // 数式のメソッドの指定
 5    @XlsOrder(1)
 6    @XlsLabelledCell(label="更新日付", type=LabelledCellType.Right)
 7    @XlsFormula(methodName="getDateFormula")
 8    private Date date;
 9
10    @XlsOrder(2)
11    @XlsHorizontalRecords(tableLabel="レコード", terminal=RecordTerminal.Border)
12    @XlsRecordOption(overOperation=OverOperation.Insert)
13    private List<SampleRecord> records;
14
15    // 数式を組み立てるメソッド
16    public String getDateFormula() {
17        return "TODAY()"
18    }
19}
20
21public class SampleRecord {
22
23    // マッピングした位置情報
24    private Map<String, CellPosition> positions;
25
26    @XlsColumn(columnName="名前")
27    private String name;
28
29    @XlsColumn(columnName="国語")
30    private int kokugo;
31
32    @XlsColumn(columnName="算数")
33    private int sansu;
34
35    // 数式の指定（メソッドを指定）
36    @XlsColumn(columnName="合計")
37    @XlsFormula(methodName="getSumFormula", primary=true)
38    private int sum;
39
40    // 数式を組み立てるメソッド
41    private String getSumFormula(CellPosition position) {
42
43        int rowNumber = position.addRow(1);
44        String colKokugo = CellReference.convertNumToColString(positions.get("kokugo").y);
45        String colSansu = CellReference.convertNumToColString(positions.get("sansu").y);
46
47        return String.format("SUM(%s%d:%s%d)", colKokugo, rowNumber, colSansu, rowNumber);
48    }
49
50}

6.1.3. 数式を優先する場合（属性primary）

出力するオブジェクトのプロパティに値が設定されている場合、アノテーション @XlsFormula を指定していても、デフォルトでは値が出力されます。

数式を優先して出力する場合、 属性 primary=true を指定すると数式が優先されます。 特に、プリミティブ型など初期値が入っている場合や、 アノテーション @XlsConverter(defaultValue="<初期値>") で初期値を指定している場合には、注意が必要です。

 1public class SampleRecord {
 2
 3    // マッピングした位置情報
 4    private Map<String, CellAddress> positions;
 5
 6    @XlsColumn(columnName="名前")
 7    private String name;
 8
 9    @XlsColumn(columnName="国語")
10    private int kokugo;
11
12    @XlsColumn(columnName="算数")
13    private int sansu;
14
15    // 数式の指定（数式を優先する場合）
16    @XlsColumn(columnName="合計")
17    @XlsFormula(value="SUM(B{rowNumber}:C{rowNumber})", primary=true)
18    private int sum;
19
20}

6.1.4. 式言語処理のカスタマイズ

数式を直接指定する場合は、EL式の1つの実装である JEXL が利用できますが、実装を切り替えたり、デフォルトの関数を登録したりとカスタマイズができます。

設定を変更したい場合は、 Configuration#formulaFormatter() の値を変更します。

1// 数式をフォーマットする際のEL関数を登録する。
2ExpressionLanguageJEXLImpl formulaEL = new ExpressionLanguageJEXLImpl();
3Map<String, Object> funcs = new HashMap<>();
4funcs.put("x", CustomFunctions.class);
5formulaEL.getJexlEngine().setFunctions(funcs);
6
7// 数式をフォーマットするEL式の実装を変更する
8XlsMapper mapper = new XlsMapper();
9mapper.getConiguration().getFormulaFormatter().setExpressionLanguage(formulaEL);

6.1.5. 数式を設定する際のポイント

6.1.5.1. 処理順序を一定にすることによる数式中の座標のずれを防ぐ

数式にセルの座標を含む場合、セルを書き込んだ後に行を追加すると、セルの位置がずれる場合があります。 これは、内部で使用しているExcelのライブラリ「Apaceh POI」は、行を追加しても数式中の座標は不変であるためです。

このような場合、 アノテーション @XlsOrder を使い、処理順序を指定することで回避できます。

6.1.5.2. EL式中でプロパティを参照する場合

どのようなEL式の処理系もプロパティの値を参照する場合、基本的にはJavaBeanの規約に基づくpublicなgetterメソッド経由でアクセスすることになります。 ただし、JEXLは、publicフィールドも参照できます。

getter/stterのアクセッサメソッドの定義が面倒な場合は、動的に生成する Lombok を利用することをお薦めします。

6.1.6. 複雑な表を作成する

最後のレコードの値が数式でデザインが異なるような表を作成する場合を例に説明します。

_images/Formula_sample.png

図 - 6.1.1 Formula(sample)

6.1.6.1. JavaBeanの定義

  • 平均値(AVERAGE関数)の数式を組み立てるには、レコードの件数が取得が必要です。

    • このようなときは、レコードのインスタンスを作成するときに、親のインスタンスを設定し、たどれるようにします。

  • プロパティの値が設定されている場合はプロパティの値を出力し、値がnullのときには数式を出力するようにするように、属性 primary=false を設定します。

    • 数値などの場合、プリミティブ型だと初期値が設定されてしまうため、ラッパー型を使います。

  • レコードの色を変えたい場合は、ライフサイクル・コールバック用 のアノテーションを使います。

    • @XlsPostSave で書き込んだ後に実行されるメソッドに付与し、その実装を行います。

  1@XlsSheet(name="成績表")
  2public class SampleSheet {
  3
  4    // マッピングした位置情報
  5    private Map<String, Point> positions;
  6
  7    @XlsOrder(2)
  8    @XlsHorizontalRecords(tableLabel="成績一覧", bottom=2, terminal=RecordTerminal.Border)
  9    @XlsRecordOption(overOperation=OverOperation.Insert)
 10    private List<SampleRecord> records;
 11
 12    // レコードを追加する
 13    public void add(SampleRecord target) {
 14        if(records == null) {
 15            this.records = new ArrayList<>();
 16        }
 17
 18        // 自身のインスタンスを渡す
 19        target.setParent(this);
 20
 21        // No.を自動的に振る
 22        target.setNo(records.size()+1);
 23
 24        this.records.add(target);
 25    }
 26
 27    public List<SampleRecord> getRecords() {
 28        return records;
 29    }
 30}
 31
 32public class SampleRecord {
 33
 34    // マッピングした位置情報
 35    private Map<String, CellPosition> positions;
 36
 37    // 親のBean情報
 38    private SampleSheet parent;
 39
 40    @XlsColumn(columnName="名前")
 41    private String name;
 42
 43    @XlsColumn(columnName="国語")
 44    @XlsFormula(methodName="getKyokaAvgFormula", primary=false)
 45    private Integer kokugo;
 46
 47    @XlsColumn(columnName="算数")
 48    @XlsFormula(methodName="getKyokaAvgFormula", primary=false)
 49    private Integer sansu;
 50
 51    @XlsColumn(columnName="合計")
 52    @XlsFormula(value="SUM(C{rowNumber}:D{rowNumber})", primary=true)
 53    private Integer sum;
 54
 55    // 各教科の平均の数式を組み立てる
 56    public String getKyokaAvgFormula(Point point) {
 57
 58        // レコード名が平均のときのみ数式を出力する
 59        if(!name.equals("平均")) {
 60            return null;
 61        }
 62
 63        // レコードのサイズ（平均用のレコード行を覗いた値）
 64        final int dataSize = parent.getRecords().size() -1;
 65
 66        // 列名
 67        final String colAlpha = CellReference.convertNumToColString(point.x);
 68
 69        // 平均値の開始/終了の行番号
 70        final int startRowNumber = point.y - dataSize +1;
 71        final int endRowNumber = point.y;
 72
 73        return String.format("AVERAGE(%s%d:%s%d)", colAlpha, startRowNumber, colAlpha, endRowNumber);
 74
 75    }
 76
 77    // 最後のレコードのときにセルの色を変更
 78    @XlsPostSave
 79    public void handlePostSave(final Sheet sheet) {
 80
 81        if(!name.equals("平均")) {
 82            return;
 83        }
 84
 85        final Workbook book = sheet.getWorkbook();
 86
 87        for(Point address : positions.values()) {
 88            Cell cell = POIUtils.getCell(sheet, address);
 89
 90            CellStyle style = book.createCellStyle();
 91            style.cloneStyleFrom(cell.getCellStyle());
 92
 93            // 塗りつぶし
 94            style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
 95            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
 96
 97            // 罫線の上部を変更
 98            style.setBorderTop(CellStyle.BORDER_DOUBLE);
 99
100            cell.setCellStyle(style);
101        }
102
103    }
104
105    public void setParent(SampleSheet parent) {
106        this.parent = parent;
107    }
108
109    public void setNo(int no) {
110        this.no = no;
111    }
112
113    public SampleRecord name(final String name) {
114        this.name = name;
115        return this;
116    }
117
118    public SampleRecord kokugo(final Integer kokugo) {
119        this.kokugo = kokugo;
120        return this;
121    }
122
123    public SampleRecord sansu(final Integer sansu) {
124        this.sansu = sansu;
125        return this;
126    }
127}

6.1.6.2. データの作成とファイルの出力

  • 計算式を出力するプロパティの値は、省略します。

  • 特に、平均値を設定するレコードは、名前以外の値は省略します。

 1// データの作成
 2final SampleSheet outSheet = new SampleSheet();
 3
 4// 各人のレコードの作成（合計値の設定は行わない。）
 5outSheet.add(new SampleRecord().name("山田太郎").kokugo(90).sansu(85));
 6outSheet.add(new SampleRecord().name("鈴木一郎").kokugo(85).sansu(80));
 7outSheet.add(new SampleRecord().name("林三郎").kokugo(80).sansu(60));
 8
 9// 平均値用のレコード(点数などのデータ部分はなし)
10outSheet.add(new SampleRecord().name("平均"));
11
12// ファイルへの書き込み
13XlsMapper mapper = new XlsMapper();
14mapper.getConiguration().setContinueTypeBindFailure(true);
15
16File outFile = new File("seiseki.xlsx");
17try(InputStream template = new FileInputStream("template.xlsx");
18        OutputStream out = new FileOutputStream(outFile)) {
19
20    mapper.save(template, out, outSheet);
21}