(cache)SpringMVCでテンプレートファイルを利用したExcel帳票を作る方法

1. はじめに

今回はSpringMVC＝TERASOLUNA5.xでテンプレートファイルを利用したExcel帳票を作る方法について説明します。TERASOLUNA5.xのガイドライン「4.10.2.2. Excelファイルのダウンロード」にExcel帳票の説明がありますが、これはプログラムでゼロからExcelファイルを作る方法です。
Excel帳票を作成する場合、事前にテンプレートファイルを用意して、これにデータを流し込む方法が一般的かと思います。今回は以下のテンプレートファイルにデータを流し込んでExcel帳票を出力したいと思います。

なお、参考としているシステムは「terasolunaorg/terasoluna-tourreservation-mybatis3」です。

2. ソースコード

ExcelReportController.java

package com.example.reportdemo.app.report;

import java.io.File;
import java.io.IOException;
import javax.inject.Inject;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.example.reportdemo.domain.model.Reserve;
import com.fasterxml.jackson.databind.ObjectMapper;

@Controller
public class ExcelReportController {

    /**
     * [dummy] 帳票データを読み込むためのobjectMapper
     */
    @Inject
    ObjectMapper objectMapper;

    /**
     * [dummy] 帳票データを格納したjsonファイル
     */
    @Value("${app.sample.jsonFile:C:/temp/excel/jsonData.json}")
    private File jsonDataFile;

    @RequestMapping(value = "report/excel", method = RequestMethod.GET)
    public String excelReport(Model model) {

        // 1. [dummy] get report data
        Reserve reserve = this.findReserve();
        // 2. set report data to model
        model.addAttribute(reserve);
        model.addAttribute("fileName", "予約明細.xlsx");
        // 3. return excel view bean's name
        return "reservationExcelView";
    }

    /**
     * [dummy] 帳票に出力するためのダミーの予約情報を作成する
     * @return 予約
     */
    private Reserve findReserve() {
        Reserve reserve = null;
        try {
            reserve = objectMapper.readValue(jsonDataFile, Reserve.class);
            System.out.println(objectMapper.writeValueAsString(reserve));
        } catch (IOException e) {
            e.printStackTrace();
        }
        return reserve;
    }
}

通常、帳票を出力するには対象のデータをデータベースから取得したりしますが、今回のサンプルでは手抜きでjsonファイルからデータを取得することにしました。他は通常のControllerの実装と特に変わりません。ポイントを示すとしたら以下の２点です。

連携したいデータはJSPの画面表示と同様にModelに格納して連携する
ハンドラメソッドの戻り値は、このあと実装するViewのBean名を返すこと

ReservationExcelView.java

package com.example.reportdemo.app.report;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.web.servlet.view.document.AbstractXlsxView;

import com.example.reportdemo.domain.model.Reserve;
import com.example.reportdemo.domain.model.TourInfo;

/**
 * ★ポイント1
 */
@Component
public class ReservationExcelView extends AbstractXlsxView {

    private static final Logger LOGGER = LoggerFactory
            .getLogger(ReservationExcelView.class);

    /**
     * ★ポイント2
     * ツアー予約登録のExcelテンプレート
     */
    @Value("${app.report.resevation.template:C:/temp/excel/reservation.xlsx}")
    private File excelTemplateFile;

    /**
     * ★ポイント2
     * Excelファイルの読み取りパスワード
     */
    @Value("${app.report.resevation.pass:locked}")
    private String excelOpenPass;

    /**
     * ★ポイント3
     * <p>
     * excelTemplateFile で指定したExcelテンプレートを利用してWorkbookを作成する
     * <p>
     * このメソッドで返却したWorkbookのオブジェクトが、buildExcelDocumentメソッドの引数として渡される
     * @see org.springframework.web.servlet.view.document.AbstractXlsxView#createWorkbook(java.util.Map,
     *      javax.servlet.http.HttpServletRequest)
     */
    @Override
    protected Workbook createWorkbook(Map<String, Object> model,
            HttpServletRequest request) {
        Workbook workbook = null;
        try (InputStream is = new ByteArrayInputStream(
                Files.readAllBytes(excelTemplateFile.toPath()));) {
            workbook = WorkbookFactory.create(is);
            // workbook = WorkbookFactory.create(is, excelOpenPass);
            // ファイルにパスワードロックが掛かっている場合、jce_policy-8.zip の適用が必要
        } catch (IOException | EncryptedDocumentException
                | InvalidFormatException e) {
            LOGGER.error("create workbook error", e);
        }
        return workbook;
    }

    /**
     * ★ポイント4
     * @see org.springframework.web.servlet.view.document.AbstractXlsView#buildExcelDocument(java.util.Map,
     *      org.apache.poi.ss.usermodel.Workbook, javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
     */
    @Override
    protected void buildExcelDocument(Map<String, Object> model,
            Workbook workbook, HttpServletRequest request,
            HttpServletResponse response) throws Exception {
        // ★ポイント5
        // 1. Modelに格納されている帳票データを取得
        Reserve reserve = (Reserve) model.get("reserve");
        TourInfo tourInfo = reserve.getTourInfo();

        // ★ポイント6
        // 2. シートの選択
        Sheet sheet = workbook.getSheet("予約");

        // ★ポイント7
        // 3. セルにデータを設定
        // 5行F列に「予約番号」の値を設定
        getCell(sheet, 4, 5).setCellValue(reserve.getReserveNo());

        // ★ポイント8        
        // 4. responseヘッダにファイル名を設定
        String fileName = (String) model.get("fileName");
        String encodedFilename = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-Disposition","attachment; filename*=UTF-8''" + encodedFilename);

        // ★ポイント7
        // 3. セルにデータを設定
        // 5行AA列に「予約日」の値を設定
        getCell(sheet, 4, 26).setCellValue(reserve.getReservedDay());
        // ツアー名
        getCell(sheet, 5, 5).setCellValue(tourInfo.getTourName());
        // 出発日
        getCell(sheet, 6, 5).setCellValue(tourInfo.getDepDay());
        // 日数
        getCell(sheet, 6, 26).setCellValue(tourInfo.getTourDays());
        // 出発地
        getCell(sheet, 7, 5).setCellValue(tourInfo.getDeparture().getDepName());
        // 目的地
        getCell(sheet, 7, 26).setCellValue(tourInfo.getArrival().getArrName());
        // 添乗員
        getCell(sheet, 8, 5).setCellValue(tourInfo.getConductor());
        // 宿泊施設
        getCell(sheet, 9, 5)
                .setCellValue(tourInfo.getAccommodation().getAccomName());
        // 連絡先
        getCell(sheet, 9, 26)
                .setCellValue(tourInfo.getAccommodation().getAccomTel());
        // 概要
        getCell(sheet, 10, 5).setCellValue(tourInfo.getTourAbs());
        // omitted
    }

    /**
     * <p>
     * 引数で指定されたシートの、行番号、列番号で指定したセルを取得して返却する
     * <p>
     * 行番号、列番号は0から開始する
     * <p>
     * Excelテンプレートで該当のセルを操作していない場合、NullPointerExceptionになる
     * @param sheet シート
     * @param rowIndex 行番号
     * @param colIndex 列番号
     * @return セル
     */
    private Cell getCell(Sheet sheet, int rowIndex, int colIndex) {
        Row row = sheet.getRow(rowIndex);
        return row.getCell(colIndex);
    }

}

★ポイント1
org.springframework.web.servlet.view.document.AbstractXlsxViewを拡張して独自のViewクラスを定義します。実装したViewはBean定義が必要なため@Componentクラスアノテーションを付与しておきます。

★ポイント2
テンプレートとして利用するExcelファイルをリソースとして定義します。Fileとして定義していますがSpringでは@Valueで読み込むことが可能です。
もし、Excelファイルにパスワードを設定している場合は、こちらも同様に定義します。（オプション）

★ポイント3
今回の記事のポイントです。createWorkbookメソッドをオーバーライドし、ExcelテンプレートからWorkbookのオブジェクトを生成します。ここは単純にPOIの操作になります。
このメソッドの戻り値のWorkbookオブジェクトがbuildExcelDocumentメソッドの引数として渡されます。

（参考）
パスワードの掛ったExcelファイルを利用することもできます。
その場合WorkbookFactory.create(is, "パスワード")でWorkbookオブジェクトを生成します。パスワードを解除するには暗号化ライブラリの問題（暗号強度の高いものは輸出規制の制限対象となる）でjce_policy-8.zipの適用が必要です。

★ポイント4
buildExcelDocumentメソッドをオーバーライドし、Excel帳票を作成する処理を実装します。

第１引数のMap<String, Object> model　：　Controllerで設定したModelのデータ
第２引数のWorkbook workbook　：　createWorkbookメソッドで生成したWorkbookオブジェクト
第３引数のHttpServletRequest request　：　HTTPリクエスト
第４引数のHttpServletResponse response)　：　HTTPレスポンス。レスポンスヘッダを設定する場合に操作する

★ポイント5
帳票に流し込むデータをModelから取得します。Controllerとのデータ連携はModelを介して行います。

★ポイント6
POIの操作になります。まずは対象となるシートを選択します。

★ポイント7
データを設定するにはセルを選択する必要があります。サンプルではgetCellメソッドを用意しました。
対象のセルにPOIのsetCellValueメソッドでデータを設定します。

（注意）
POIの仕様でテンプレートのExcelファイルで何も操作していない行、列はnullになるので注意してください。ポジティブに考えればNullPointerExceptionが発生した場合、設定すべきセルではなく他のセルを選択してしまったバグが分かったということです。

★ポイント8
HTTPレスポンスヘッダにダウンロードファイルのファイル名を設定します。
日本語をファイル名を設定する場合はutf-8でエンコードします。
ファイル名を設定しない場合、リクエストパスに拡張子の.xlsxを付与した名前になります。サンプルだとパスがreport/excelなのでexcel.xlsxになります。

Bean定義ファイル（TERASOLUNA5.xの場合：spring-mvc.xml）

    <!-- Settings View Resolver. -->
    <mvc:view-resolvers>
        <!-- ★ポイント9 -->
        <mvc:bean-name />
        <mvc:jsp prefix="/WEB-INF/views/" />
    </mvc:view-resolvers>

★ポイント9
Bean定義ファイルの<mvc:view-resolvers>の定義に<mvc:bean-name />を追加します。記述する順番も重要です。必ず<mvc:jsp>より前に定義してください。

jsonData.json

{
  "reserveNo" : "12345678",
  "reservedDay" : "2018-01-10T07:02:15.919+0000",
  "adultCount" : 2,
  "childCount" : 1,
  "transfer" : 0,
  "sumPrice" : 75000,
  "remarks" : "めも",
  "tourInfo" : {
    "tourCode" : "00000001",
    "plannedDay" : "2017-01-10T07:02:15.919+0000",
    "planNo" : "0101",
    "tourName" : "テラソルナ極楽ツアー",
    "tourDays" : 0,
    "depDay" : "2018-01-30",
    "avaRecMax" : 0,
    "basePrice" : 30000,
    "conductor" : 1,
    "tourAbs" : "そこは別天地、静寂と湯けむりの待つ宿へ…\r\n詳しい情報はお取り合わせをお願い致します。",
    "departure" : {
      "depCode" : "01",
      "depName" : "北海道"
    },
    "arrival" : {
      "arrCode" : "02",
      "arrName" : "青森"
    },
    "accommodation" : {
      "accomCode" : "0001",
      "accomName" : "TERASOLUNAホテル第一荘",
      "accomTel" : "018-123-4567"
    },
    "paymentLimit" : "2018-01-23"
  },
  "customer" : {
    "customerCode" : "00001234",
    "customerName" : "山田 太郎",
    "customerKana" : "ヤマダ タロウ",
    "customerPass" : null,
    "customerBirth" : "2000-07-07",
    "customerJob" : "営業",
    "customerMail" : "test@eample.com",
    "customerTel" : "01-2345-6789",
    "customerPost" : "123-4567",
    "customerAdd" : "東京都江東区豊洲"
  }
}

Excel帳票に流し込むデータです。サンプルのためですが、JSONからデータを読み込ませるようにするとデータ変更も楽ですね。

3. さいごに

今回はSpringMVC＝TERASOLUNA5.xでテンプレートファイルを利用したExcel帳票を作る方法について説明しました。
ポイントはcreateWorkbookメソッドをオーバライドしてExcelのテンプレートファイルからWorkbookオブジェクトを生成することです。