LoginSignup
7

More than 3 years have passed since last update.

XlsMapperで数式ありExcelを出力する

Last updated at Posted at 2019-03-24

きっかけ

「JavaでExcelを出力したいけど、ApachePOIみたいにセル番号で指定するのはやりたくないなあ」、「もう少し高水準なライブラリがほしいなあ」と思っていたら、XlsMapperというライブラリを見つけたので試してみました。誤記や実装のアドバイス等ありましたらご指摘ください。

XlsMapperとは

ExcelをJavaへマッピングするJavaライブラリ。
昔XLSBeansという、書籍化されるような有名なJavaライブラリがあったがその開発がストップしたっぽい。これのver1.1を基に個人の方が別プロジェクトXlsMapperとして少しずつ機能追加しているらしい。(実は昔、XLSBeansをちょっとだけ使ったことがある。)

POIと比べて何が便利か?

POIを使ってエクセルにアクセスする場合、(自分の知っている情報だと)DBからとってきたリストに対して、
レコード1.要素1をエクセルのN番目のレコードのM番目の列にあるセルにセット&書式設定。
レコード1.要素2をエクセルのN番目のレコードのM+1番目の列にあるセルにセット&書式設定。

レコード2.要素1をエクセルのN+1番目のレコードのM番目の列にあるセルにセット&書式設定。

のようなアクセスを実装する必要があったり、列構成が変わったりするとその分N,Mの数値をずらす必要があると思いますが、
このライブラリを使うとまるでORマッパーのように、エクセルの値をPOJOにマッピングしたり、マッピングしたPOJOをエクセルに書き込みしたりできるので、無駄な処理の記述が減ります。
また、書式等も前のレコードをコピーするような設定がアノテーションでできるのでわざわざ実装する必要がありません。

公式サイト

試した環境

  • XlsMapper2.0
  • spring boot
  • Maven
  • lombok
  • java8(8以上必須)
  • Apache POI 4.0.1(3.17以上必須)※
  • Excel2010(2016でも動作確認済み)

※追記
POI 4.0.1にはバグが含まれています。
下記OverOperation.Insertを使用した際にブックが破損するのはこれが原因です。
素直に推奨されているPOI3.17を使用した方がよいです。
(コメントありがとうございます!)

実装

Maven

以下をpomに追加。※エクセル関連以外は省略。

pom.xml
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>com.github.mygreen</groupId>
    <artifactId>xlsmapper</artifactId>
    <version>2.0</version>
</dependency>

ひな形ファイル

以下のような表を持つブックを準備。
表は罫線で囲むのがポイントです。

無題.png

SpringBootApplication

DemoService.java
@SpringBootApplication
public class DemoApplication {

    public static void main(String[] args) {
        // 引数をMapにして返してるだけ
        CommandLineParamsMap params = new CommandLineParamsMap(args);
        try (ConfigurableApplicationContext ctx = SpringApplication.run(DemoApplication.class, args)) {
            DemoService app = ctx.getBean(DemoService.class);
            app.execute(params);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

Service

DemoService.java
@Service
@Slf4j
public class DemoService {
    private static final String TEMPLATE_FILE_PATH = "template\\使用者一覧_%s年%s月%s日作成.xlsx";
    private static final String OUTPUT_FILE_PATH = "C:\\test\\使用者一覧_%s年%s月%s日作成.xlsx";

    public String execute(CommandLineParamsMap params) {
        log.info("DemoService START");
        LocalDate outPutDate = getOutPutDate(params.getValue("date"));
        log.info("date:" + outPutDate);

        String outputPath = makeTargetPath(outPutDate);

        // 表データの取得
        List<UsingListRecord> target = getData();

        // sheetに各情報をセット
        UsingListSheet sheet = new UsingListSheet();
        sheet.setOutPutDate("出力日:" + outPutDate);
        sheet.setRecords(target);
        // 最終行に数式の合計行を追加
        sheet.addSummaryRecord();

        XlsMapper xlsMapper = new XlsMapper();
        try {
            // 書き込み なんとこれだけ(ループ不要)!
            xlsMapper.save(
                    new FileInputStream(TEMPLATE_FILE_PATH), // テンプレートのExcelファイル
                    new FileOutputStream(outputPath), // 書き込むExcelファイル
                    sheet // 作成したデータ
            );
        } catch (XlsMapperException | IOException e) {
            throw new RuntimeException(e);
        }

        // 書き込んだ表を読み込んで出力してみる
        List<UsingListRecord> records = read(outputPath);
        records.forEach(r->log.info(r.toString()));

        log.info("DemoService END");
        return "###########success###########";
    }

    /**
     * yyyymmdd→LocalDateオブジェクト
     * @param value
     * @return
     */
    private LocalDate getOutPutDate(String value) {
        int year = Integer.parseInt(value.substring(0, 4));
        int month = Integer.parseInt(value.substring(4, 6));
        int day = Integer.parseInt(value.substring(6, 8));
        return LocalDate.of(year, month, day);
    }

    // データ取得
    private List<UsingListRecord> getData() {
        // 本当はDBとかから持ってくる
        List<UsingListRecord> list = new ArrayList<>();
        list.add(getSample("アナキン・スカイウォーカー", "タトゥイーン", "人間", 100, "アソーカ・タノ", 1));
        list.add(getSample("パドメ・アミダラ", "ナブー", "人間", 100, null, 2));
        list.add(getSample("ルーク・スカイウォーカー", "ポリス・マサ", "人間", 100, "カイロ・レン", 3));
        list.add(getSample("カイロ・レン", "シャンドリラ", "人間", 100, null, 4));
        list.add(getSample("アソーカ・タノ", "シリ", "トグルータ", 100, null, 5));
        list.add(getSample("ダース・モール", "ダソミア", "ザブラク", null, "サヴァージ・オプレス", 6));
        list.add(getSample("ヨーダ", null, "ヨーダの種族", 800, "ルーク・スカイウォーカー", 7));
        return list;
    }

    // 適当なサンプルレコードを返す
    private UsingListRecord getSample(String name, String homeTown, String species, Integer ageAvg, String apprentice,
            int i) {
        LocalDate d1 = LocalDate.of(2019, 3, 1).plusDays(i);
        Date startDate = Date.from(d1.atStartOfDay(ZoneId.systemDefault()).toInstant());

        UsingListRecord record = new UsingListRecord();
        record.setUserName(name);
        record.setPrice(new BigDecimal(120000 + i));
        record.setTax(0.08d + i);
        record.setUsingStartDate(startDate);
        record.setHomeTown(homeTown);
        record.setSpecies(species);
        record.setAgeAvg(ageAvg);
        record.setApprentice(apprentice);
        return record;
    }

    private List<UsingListRecord> read(String targetPath) {
        XlsMapper xlsMapper = new XlsMapper();
        UsingListSheet sheet = null;
        try {
            sheet = xlsMapper.load(
                    new FileInputStream(targetPath), // 読み込むExcelファイル
                    UsingListSheet.class // アノテーションを付与したクラス。
            );
        } catch (XlsMapperException | IOException e) {
            throw new RuntimeException(e);
        }

        return sheet.getRecords();
    }

    private String makeTargetPath(LocalDate batchDate) {
        return String.format(OUTPUT_FILE_PATH, 
                batchDate.getYear(),
                batchDate.getMonthValue(),
                batchDate.getDayOfMonth()
                );
    }
}

Sheet

1シートを表すクラス。@XlsSheet(name = "シート名")を付与します。

UsingListSheet.java
@Slf4j
@Data
@XlsSheet(name = "使用者一覧")
public class UsingListSheet {

    private String outPutDate;

    @XlsHorizontalRecords(tableLabel = "使用者リスト", bottom = 3)
    @XlsRecordOption(overOperation = OverOperation.Copy)
    private List<UsingListRecord> records;

    public void addSummaryRecord() {
        if (records == null) {
            this.records = new ArrayList<>();
        }

        UsingListRecord record = new UsingListRecord();
        // 自身のインスタンスを渡す
        record.setParent(this);

        record.setUserName("合計");
        records.add(record);
    }

    /**
     * 表書き込み後にPOIで出力日を設定してみる。
     * @param sheet
     */
    @XlsPostSave
    public void aa(final Sheet sheet) {
        Cell cell = POIUtils.getCell(sheet, 7, 0);
        CellStyle style=cell.getCellStyle();
        cell.setCellValue(outPutDate);
        cell.setCellStyle(style);
    }
}

@XlsHorizontalRecords

後述で添付したエクセルのように、見出しが上にあるタイプの表の際に設定します。
tableLabelでテーブルのタイトルを指定します。
bottomでtableLabelから実際の表がどれくらい離れているかを指定します。tableLabelの真下に表の見出しがある場合は指定不要です。

@XlsRecordOption(overOperation = OverOperation.Copy)

テンプレートで指定した行数(罫線で判定)よりデータの行数が多い場合にどうするかを指定します。
OverOperation.Copyの場合は1つ上の行の書式をコピーして行追加してくれます。
OverOperation.Insertは使用するとブックが破損する。(理由は上記の試した環境を参照。)

@XlsPostSave

これを付与したメソッドはファイル書き込み後に自動的に実行されます。
Recordクラスのメソッドにも付与できて、順序としてはSheetの@XlsPostSave→Recordの@XlsPostSaveです。
ほかにも@XlsPreSaveなどいろいろあるので以下を参照。
7. ライフサイクルイベントの管理

Record

エクセルシートに載せる表の1レコードを表すクラス。

UsingListRecord.java
@Data
public class UsingListRecord {
    // マッピングした位置情報
    private Map<String, CellPosition> positions;
    // 親のBean情報
    private UsingListSheet parent;

    @XlsColumn(columnName = "使用者")
    private String userName;
    @XlsColumn(columnName = "料金")
    @XlsFormula(methodName = "getSumFormula", primary = false)
    private BigDecimal price;
    @XlsColumn(columnName = "消費税率")
    private Double tax;
    @XlsColumn(columnName = "使用開始日")
    @XlsDateTimeConverter(excelPattern = "yyyy/m/d")
    private Date usingStartDate;
    @XlsColumn(columnName = "出身地")
    @XlsDefaultValue(value="--", cases=ProcessCase.Save)
    private String homeTown;
    @XlsColumn(columnName = "種族")
    private String species;
    @XlsColumn(columnName = "平均寿命")
    private Integer ageAvg;
    @XlsColumn(columnName = "弟子")
    @XlsDefaultValue(value="--", cases=ProcessCase.Save)
    private String apprentice;

    // 合計の数式を組み立てる
    public String getSumFormula(Point point) {

        // 出身地が合計のときのみ数式を出力する
        if (!userName.equals("合計")) {
            return null;
        }

        // レコードのサイズ(合計用のレコード行を覗いた値)
        final int dataSize = parent.getRecords().size() - 1;

        // 列名
        final String colAlpha = CellReference.convertNumToColString(point.x);

        // 合計値の開始/終了の行番号
        final int startRowNumber = point.y - dataSize + 1;
        final int endRowNumber = point.y;

        return String.format("SUM(%s%d:%s%d)", colAlpha, startRowNumber, colAlpha, endRowNumber);

    }
}

@XlsFormula

primary = falseを指定することで、該当フィールドに値があったときはそれを優先します。
trueの場合は常に数式が優先されます。

@XlsDefaultValue

NULLの場合のデフォルト値をvalueで設定します。cases=ProcessCase.Saveを指定すると書き込みの場合のみデフォルト値を設定します。
※デフォルト値はマッピング先の型と一致していなければいけません。そうでないとTypeBindExceptionが発生する(平均寿命とかに"--"を設定することはできない。)

実行

Springboot appの実行構成で引数に「date=20190312」を入力して適用し実行。

実行結果

出力されたエクセル

数式窓を見るとちゃんと数式も出力されていますね。

無題2.png

ログ

書き込み後の表を読み込んだ際のログ。
取得もちゃんとできている。

ログ(抜粋)
com.example.demo.service.DemoService : date:2019-03-12
com.example.demo.service.DemoService : UsingListRecord(positions={userName=A5, price=B5, tax=C5, usingStartDate=D5, homeTown=E5, species=F5, ageAvg=G5, apprentice=H5}, parent=null, userName=アナキン・スカイウォーカー, price=120001, tax=1.08, usingStartDate=Sat Mar 02 00:00:00 JST 2019, homeTown=タトゥイーン, species=人間, ageAvg=100, apprentice=アソーカ・タノ)
com.example.demo.service.DemoService : UsingListRecord(positions={userName=A6, price=B6, tax=C6, usingStartDate=D6, homeTown=E6, species=F6, ageAvg=G6, apprentice=H6}, parent=null, userName=パドメ・アミダラ, price=120002, tax=2.08, usingStartDate=Sun Mar 03 00:00:00 JST 2019, homeTown=ナブー, species=人間, ageAvg=100, apprentice=--)
com.example.demo.service.DemoService : UsingListRecord(positions={userName=A7, price=B7, tax=C7, usingStartDate=D7, homeTown=E7, species=F7, ageAvg=G7, apprentice=H7}, parent=null, userName=ルーク・スカイウォーカー, price=120003, tax=3.08, usingStartDate=Mon Mar 04 00:00:00 JST 2019, homeTown=ポリス・マサ, species=人間, ageAvg=100, apprentice=カイロ・レン)
com.example.demo.service.DemoService : UsingListRecord(positions={userName=A8, price=B8, tax=C8, usingStartDate=D8, homeTown=E8, species=F8, ageAvg=G8, apprentice=H8}, parent=null, userName=カイロ・レン, price=120004, tax=4.08, usingStartDate=Tue Mar 05 00:00:00 JST 2019, homeTown=シャンドリラ, species=人間, ageAvg=100, apprentice=--)
com.example.demo.service.DemoService : UsingListRecord(positions={userName=A9, price=B9, tax=C9, usingStartDate=D9, homeTown=E9, species=F9, ageAvg=G9, apprentice=H9}, parent=null, userName=アソーカ・タノ, price=120005, tax=5.08, usingStartDate=Wed Mar 06 00:00:00 JST 2019, homeTown=シリ, species=トグルータ, ageAvg=100, apprentice=--)
com.example.demo.service.DemoService : UsingListRecord(positions={userName=A10, price=B10, tax=C10, usingStartDate=D10, homeTown=E10, species=F10, ageAvg=G10, apprentice=H10}, parent=null, userName=ダース・モール, price=120006, tax=6.08, usingStartDate=Thu Mar 07 00:00:00 JST 2019, homeTown=ダソミア, species=ザブラク, ageAvg=null, apprentice=サヴァージ・オプレス)
com.example.demo.service.DemoService : UsingListRecord(positions={userName=A11, price=B11, tax=C11, usingStartDate=D11, homeTown=E11, species=F11, ageAvg=G11, apprentice=H11}, parent=null, userName=ヨーダ, price=120007, tax=7.08, usingStartDate=Fri Mar 08 00:00:00 JST 2019, homeTown=--, species=ヨーダの種族, ageAvg=800, apprentice=ルーク・スカイウォーカー)
com.example.demo.service.DemoService : UsingListRecord(positions={userName=A12, price=B12, tax=C12, usingStartDate=D12, homeTown=E12, species=F12, ageAvg=G12, apprentice=H12}, parent=null, userName=合計, price=840028, tax=null, usingStartDate=null, homeTown=--, species=null, ageAvg=null, apprentice=--)

あとがき

実装してて気づきましたが、クローズ処理が不要なのもいいですね。
今回は最終レコードに数式(合計行)を出力する処理を入れているのでちょっと複雑になりましたが、数式なしの場合はSheet,Recordクラスともにメソッドは不要になり、アノテーションも減ってかなりシンプルになります。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
7