ExcelテンプレートエンジンJETTを利用してピボットテーブルでグラフを表示しよう

# はじめに
Excel帳票の出力には、Excelテンプレートエンジンを利用するのが便利です。
Excelテンプレートエンジンである[「JETT」](http://jett.sourceforge.net/)を利用し、
ピボットテーブルで以下のようなグラフを表示させる方法を紹介します。

![IMAGE1.png](https://qiita-image-store.s3.amazonaws.com/0/71427/602a6182-c097-e7a3-00a9-7de5d84b98ca.png)
サンプルコードは以下よりダウンロードできます。
[jett-example](https://github.com/k3286/jett-example.git)
実際にダウンロードし動かしながら、ご確認ください。

# pom.xmlの設定
```xml:pom.xml
<!-- JETT -->
<dependency>
    <groupId>net.sf.jett</groupId>
    <artifactId>jett-core</artifactId>
    <version>0.10.0</version>
</dependency>
```
## 依存関係
- commons-codec
- commons-jexl
- coomons-logging
- jagg-core
- jett-core
- poi
- poi-ooxml
- poi-ooxml-schemas
- stax-api
- xmlbeans

依存関係からわかるように、このエンジンは「POI」をベースにしています。

# 帳票変換処理のコード
変換処理で使用するJETTのクラスは、この「ExcelTransformerクラス」だけであり、
transformメソッドを呼んでいます、とても簡単ですね。

```java:ReportMaker.java
package com.github.k3286.report;

import java.io.IOException;
import java.io.InputStream;
import java.util.Map;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;

import net.sf.jett.transform.ExcelTransformer;

public class ReportMaker {

    /**
     * パラメータと、テンプレートファイル名を指定し、帳票変換を行う
     * @param params パラメータ
     * @param templateName テンプレートファイル名
     * @return 変換したWorkbook
     */
    public static Workbook toReport(Map<String, Object> params, String templateName) {
        Workbook workbook = null;
        InputStream is = null;
        try {
            is = ReportMaker.class.getResourceAsStream("/" + templateName);
            ExcelTransformer transformer = new ExcelTransformer();
            workbook = transformer.transform(is, params);
        } catch (InvalidFormatException | IOException e) {
            e.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return workbook;
    }
}
```
# 帳票テンプレートのポイント
サンプルコードには、帳票のテンプレートとなるファイル「template.xlsx」があります。シート構成は、データリストを出力するシートと、このデータリストを読み込み、グラフを表示するシートに分かれています。

##「DATA」シートの記述
\<jt:forEach>タグを使うことで、データの繰り返し出力ができます。動作させるテストコードには、パラメータを渡すために、Mapクラスを使用しています。テンプレートファイルには、keyを記述し、変換の際にvalueに置き換わります。

![IMAGE3.png](https://qiita-image-store.s3.amazonaws.com/0/71427/35c6c8cc-26ff-71de-5d9f-9dc33c460ea7.png)
※[タグの詳細ついては、ここ](http://jett.sourceforge.net/tags/basics.html)

実際の出力のイメージは、以下のようになります。
![IMAGE2.png](https://qiita-image-store.s3.amazonaws.com/0/71427/f67cb74d-cfa5-d93d-4aa6-c90f2a5725d3.png)

他にもタグは用意されていますので、試してみてください。

##「売上高%（顧客別）」及び、「粗利（計上月別）」シートの記述
「DATA」シートがあれば、ピボットテーブルと、それに対応したグラフなどをテンプレートに用意しておきます。アイディア次第で色々と表現ができると思います。
あとは以下の２つのポイントを押さえて設定してください。
・[ピボットテーブル【データ範囲】を自動更新するには](http://office-qa.com/Excel/ex203.htm)
・ピボットテーブルのオプション「ブックを開くときにデータを自動的に更新する」に☑を入れる。

こうしておくことでブックを開いたとき、出力したデータに応じたグラフが表示されます。（手動でデータを更新する必要はなくなり、よりダイナミックになります）

# 動作させるテストコード
以下を実行することで、サンプルコードの確認ができます。

```java:ReportMakerTest.java
package com.github.k3286.report;

import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;

import com.github.k3286.dto.AnalysisData;

public class ReportMakerTest {

    @Test
    public void report_test() throws Exception {

        // データ準備
        List<AnalysisData> datas = new ArrayList<AnalysisData>();
        datas.add(new AnalysisData(toDate_yyyyMM("201504"), "㈱AVAX", BigDecimal.valueOf(1000000), BigDecimal.valueOf(800000)));
        datas.add(new AnalysisData(toDate_yyyyMM("201505"), "㈱AVAX", BigDecimal.valueOf(1100000), BigDecimal.valueOf(600000)));
        datas.add(new AnalysisData(toDate_yyyyMM("201506"), "㈱AVAX", BigDecimal.valueOf(1300000), BigDecimal.valueOf(600000)));
        datas.add(new AnalysisData(toDate_yyyyMM("201507"), "㈱AVAX", BigDecimal.valueOf(1200000), BigDecimal.valueOf(700000)));

        datas.add(new AnalysisData(toDate_yyyyMM("201504"), "㈱松上電気", BigDecimal.valueOf(4000000), BigDecimal.valueOf(1800000)));
        datas.add(new AnalysisData(toDate_yyyyMM("201505"), "㈱松上電気", BigDecimal.valueOf(3100000), BigDecimal.valueOf(1600000)));
        datas.add(new AnalysisData(toDate_yyyyMM("201506"), "㈱松上電気", BigDecimal.valueOf(2300000), BigDecimal.valueOf(1600000)));
        datas.add(new AnalysisData(toDate_yyyyMM("201507"), "㈱松上電気", BigDecimal.valueOf(3200000), BigDecimal.valueOf(1700000)));

        datas.add(new AnalysisData(toDate_yyyyMM("201504"), "㈱グミシステム", BigDecimal.valueOf(4000000), BigDecimal.valueOf(1800000)));
        datas.add(new AnalysisData(toDate_yyyyMM("201505"), "㈱グミシステム", BigDecimal.valueOf(3100000), BigDecimal.valueOf(1600000)));
        datas.add(new AnalysisData(toDate_yyyyMM("201506"), "㈱グミシステム", BigDecimal.valueOf(2300000), BigDecimal.valueOf(1600000)));
        datas.add(new AnalysisData(toDate_yyyyMM("201507"), "㈱グミシステム", BigDecimal.valueOf(3200000), BigDecimal.valueOf(1700000)));

        datas.add(new AnalysisData(toDate_yyyyMM("201504"), "㈱カフェラテシステム", BigDecimal.valueOf(1000000), BigDecimal.valueOf(600000)));
        datas.add(new AnalysisData(toDate_yyyyMM("201505"), "㈱カフェラテシステム", BigDecimal.valueOf(1000000), BigDecimal.valueOf(600000)));
        datas.add(new AnalysisData(toDate_yyyyMM("201506"), "㈱カフェラテシステム", BigDecimal.valueOf(1000000), BigDecimal.valueOf(600000)));
        datas.add(new AnalysisData(toDate_yyyyMM("201507"), "㈱カフェラテシステム", BigDecimal.valueOf(1000000), BigDecimal.valueOf(600000)));

        // 帳票変換
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("datas", datas);
        Workbook workbook = ReportMaker.toReport(map, "template.xlsx");

        // ファイル出力
        final String outPath = "output.xlsx";
        try (FileOutputStream fileOut = new FileOutputStream(outPath)) {
            workbook.write(fileOut);
            fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public Date toDate_yyyyMM(String dateStr) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        return sdf.parse(dateStr);
    }
}
```