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

はじめに

Excel帳票の出力には、Excelテンプレートエンジンを利用するのが便利です。
Excelテンプレートエンジンである「JETT」を利用し、
ピボットテーブルで以下のようなグラフを表示させる方法を紹介します。

サンプルコードは以下よりダウンロードできます。
jett-example
実際にダウンロードし動かしながら、ご確認ください。

pom.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メソッドを呼んでいます、とても簡単ですね。

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に置き換わります。

※タグの詳細ついては、ここ

実際の出力のイメージは、以下のようになります。

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

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

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

動作させるテストコード

以下を実行することで、サンプルコードの確認ができます。

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);
    }
}