表に罫線を引くのはもう古い!? 若者に笑われないためのExcelテーブル入門

Apache POI,Excel,Java

こんにちは、ソリューション開発部の柴崎です。

Excelで表を作ろうとしている最中、最近の若者は表に罫線を引かないと聞きました。なぜ若者は表に罫線を引かないのでしょうか。その謎を解明するため、われわれ調査隊はOOXMLの奥地へと向かいました――。

まとめ

  • 罫線を引いている時間がもったいないので、テーブルとして書式設定を使うのがおすすめです。
  • テーブルを使うと構造化参照できるようになり、数式が読みやすくなります。
  • Apache POIでも構造化参照するテーブルを作れます。

表を作る

罫線を使う方法・使わない方法で表を作ってみましょう。例えば以下のようなデータがあったとします。

表を作る

罫線を使う

何年もExcel使ってるんだ、表くらいすぐに作れるわ!?

範囲選択して
範囲選択して
罫線を引いて表の完成
罫線を引いて表の完成
フィルターもつけたりして
フィルターもつけたりして

流れるような見事な操作としか言いようがありません。表といえばこれですよ。

テーブルとして書式設定を使う

若者は罫線を引かずにテーブルとして書式設定を使うそうです。しかし、こちとら何年も表に罫線を引き続けてきたので、そう簡単になびくつもりはありません。試しに「テーブルとして書式設定」とやらを使ってやろうではありませんか。

「ホーム」リボンの「テーブルとして書式設定」からスタイルを選ぶ
「ホーム」リボンの「テーブルとして書式設定」からスタイルを選ぶ
テーブルのデータの範囲を確認して
テーブルのデータの範囲を確認して
テーブルの完成
テーブルの完成

はい、ものすごく簡単ですね。?

テーブルとして書式設定のここがすごい!

テーブルとして書式設定を使う場合、以下の利点があります。

  • 範囲選択しなくても、テーブルのデータの範囲が推定される。
  • 1回の操作でフィルターを追加できる。
  • 罫線を引きたければ、罫線のあるスタイルを選択すれば良い。

加えてテーブルを使う場合、テーブルのデータの範囲に隣接する行や列を追加すると自動的に範囲が拡張され、拡張された範囲にも自動的にスタイルが適用されます。わざわざ追加された範囲に罫線を引くような手間はありません。スタイルとして罫線を引くかどうかはさておき、テーブル機能を使った方が保守する上でも便利です。

テーブルを使うと参照しやすい

本記事の例に出した「単価」(C列)と「購入個数」(D列)を掛け合わせた「購入額」(E列)のような導出項目について考えてみましょう。名前をつけていない場合は、E2セルを「= C2 * D2」のようなセル参照を使った数式とすることで導出することを考えるかと思います。

テーブルを使う場合には、E2セルを「= [@単価] * [@購入個数]」のような構造化参照を使った数式とすることで導出できます。列の名前を変更した場合は、構造化参照した数式も併せて更新されますのでご安心ください。明示的にセル参照するよりも計算内容の確認が簡単になりますし、列と列の間に新しい列を挿入した場合に、数式が壊れていないかの不安が軽減されるのではないでしょうか。

列の名前で構造化参照

テーブルに名前をつけてさらに便利に参照する

テーブル自体に名前をつけることで、テーブルを参照しやすくなります。また、ピボットテーブルなどデータの範囲を指定する際にも使えます。

テーブル名は、テーブル範囲のいずれかを選択した状態で「テーブル デザイン」リボンの左端にテーブル名の入力欄から変更できます。

テーブルに名前をつける

上記のような「製品購入」テーブルの「購入額」列の合計値を別のシートで計算したい場合は、「=SUM(製品購入[購入額])」のような数式にできます。

他にも、例えば「製品番号」列と「製品名」列を持つ「製品」テーブルがあれば、XLOOKUP 関数を使って「=XLOOKUP([@製品番号], 製品[製品番号], 製品[製品名])」のような数式で「製品名」を参照するなど応用できると思います。

POIでテーブルを作る

Excelの使い方だけで終わってしまうと味気ないので、Apache POIを使ったテーブルを作る方法も紹介します。

動作環境

Java 17とPOI 5系を使いました。

  • Java 17
  • Gradle 7.2
  • Apache POI 5.0.0
plugins {
    id 'myproject.java-conventions' // よくある Java 設定
}

dependencies {
    implementation 'org.apache.poi:poi-ooxml:5.0.0'
}

余談ですが、POIは5系になって依存するライブラリが増えました。イメージサイズを減らそうとしている人にとってはつらいかなと思います。今回であれば、明らかに使っていない暗号系・画像系のライブラリを除外できますが、個別にexcludeすることを考えると気が重くなりますね。

POIを使ったテーブルを作成するサンプルコード

POIを使ったテーブルを作成するサンプルコードを書いてみました。(解説はサンプルコードの後にあります。)

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTableStyleInfo;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.time.LocalDate;
import java.util.Arrays;
import java.util.function.BiConsumer;

public class PoiTables {

    public static void main(final String... args) throws IOException {
        record TableRowValues(String productId, LocalDate datePurchased, int unitPrice, int numOfUnits) {
        }

        record TableColumn(String columnName, String styleDataFormat, BiConsumer<Cell, TableRowValues> setter) {
            void setRowValueToCell(final TableRowValues rowValues, final Cell cell) {
                setter.accept(cell, rowValues);
            }
        }

        final var tableName = "製品購入";
        final TableColumn[] tableColumns = {
                new TableColumn(
                        "製品番号",
                        "text",
                        (cell, rowValues) -> cell.setCellValue(rowValues.productId())
                ),
                new TableColumn(
                        "購入日",
                        "yyyy\\-mm\\-dd;@",
                        (cell, rowValues) -> cell.setCellValue(rowValues.datePurchased())
                ),
                new TableColumn(
                        "単価",
                        "\"¥\"#,##0;\"¥\"\\-#,##0",
                        (cell, rowValues) -> cell.setCellValue(rowValues.unitPrice())
                ),
                new TableColumn(
                        "購入個数",
                        "#,##0",
                        (cell, rowValues) -> cell.setCellValue(rowValues.numOfUnits())
                ),
                new TableColumn(
                        "購入額",
                        "\"¥\"#,##0;\"¥\"\\-#,##0",
                        (cell, rowValues) -> cell.setCellFormula("製品購入[[#This Row],[単価]] * 製品購入[[#This Row],[購入個数]]")
                )
        };
        final TableRowValues[] tableRows = {
                new TableRowValues("ARK-H", LocalDate.of(2020, 9, 29), 137, 2),
                new TableRowValues("ARK-O", LocalDate.of(2020, 7, 22), 140, 5),
                new TableRowValues("ARK-G", LocalDate.of(2020, 11, 6), 149, 3),
                new TableRowValues("ARK-E", LocalDate.of(2020, 8, 1), 145, 2),
                // ……
        };

        final var colLength = tableColumns.length;
        final var rowLength = tableRows.length;

        final var top = 0;
        final var left = 0;
        final var bottom = top + 1 + rowLength - 1;
        final var right = left + colLength - 1;

        try (var book = new XSSFWorkbook()) {
            final var sheet = (XSSFSheet) book.createSheet();
            final var creationHelper = book.getCreationHelper();
            final var dataFormat = book.createDataFormat();

            /* テーブルの見出し行 */
            {
                final var row = sheet.createRow(top);
                for (var c = 0; c < colLength; c++) {
                    final var columnIndex = left + c;
                    final var cell = row.createCell(columnIndex);
                    cell.setCellValue(tableColumns[c].columnName());

                    // sheet.autoSizeColumn(columnIndex);
                    sheet.setColumnWidth(columnIndex, 12 * 0x100);
                }
            }

            /* テーブルとして書式設定 */
            final var tableArea = creationHelper.createAreaReference(
                    new CellReference(top, left),
                    new CellReference(bottom, right)
            );
            final var table = sheet.createTable(tableArea);
            table.setName(tableName);
            table.setDisplayName(tableName); // テーブル名
            table.setStyleName("TableStyleLight6"); // スタイル: TableStyleLight## | TableStyleMedium## | TableStyleDark##
            final var style = (XSSFTableStyleInfo) table.getStyle();
            style.setShowRowStripes(true); // 縞模様 (行)
            final var ctAutoFilter = table.getCTTable().addNewAutoFilter();
            ctAutoFilter.setRef(tableArea.formatAsString()); // フィルター ボタン

            /* テーブルのデータ行 */
            final var cellStyles = Arrays.stream(tableColumns)
                    .map(TableColumn::styleDataFormat)
                    .mapToInt(dataFormat::getFormat)
                    .mapToObj(formatIndex -> {
                        final var cellStyle = book.createCellStyle();
                        cellStyle.setDataFormat(formatIndex);
                        return cellStyle;
                    })
                    .toArray(XSSFCellStyle[]::new);
            for (var r = 0; r < rowLength; r++) {
                final var row = sheet.createRow(top + 1 + r);
                final var rowValues = tableRows[r];

                for (var c = 0; c < colLength; c++) {
                    final var cell = row.createCell(left + c);
                    cell.setCellStyle(cellStyles[c]);
                    tableColumns[c].setRowValueToCell(rowValues, cell);
                }
            }

            /* 保存 */
            try (var stream = Files.newOutputStream(Path.of("table.xlsx"))) {
                book.write(stream);
            }
        }
    }

}

数式を設定する前にテーブルの見出し行と範囲を指定する

構造化参照の数式をセルに設定する前に、見出し行とテーブル範囲の指定が重要です。テーブルでないセルに構造化参照の数式を設定するとExcelファイルを開いたときに「値のエラー」になります。また、見出し行にない名前を構造化参照の数式に使うとorg.apache.poi.ss.formula.FormulaParseException例外が発生します。

テーブルとして書式設定する

まずテーブルの範囲を決めテーブルを作成します。

final var tableArea = creationHelper.createAreaReference(
        new CellReference(top, left),
        new CellReference(bottom, right)
);
final var table = sheet.createTable(tableArea);

「テーブル名」を指定する場合は以下のように設定します。

table.setName(tableName);
table.setDisplayName(tableName); // テーブル名

setDisplayNameだけの設定だけで事足りるはずですが、ExcelではsetNameで設定される値にも同じ値を設定していますので合わせておいた方が安心と思います。

Excelの「テーブルとして書式設定」を選んだ直後に選択する淡色、中間、濃色のスタイルは以下のように設定します。

table.setStyleName("TableStyleLight6"); // スタイル: TableStyleLight## | TableStyleMedium## | TableStyleDark##

淡色はTableStyleLight1からTableStyleLight21まで、中間はTableStyleMedium1からTableStyleMedium28まで、濃色はTableStyleDark1からTableStyleDark11まで指定できます。

「縞模様(行)」を指定する場合は以下のように設定します。

final var style = (XSSFTableStyleInfo) table.getStyle();
style.setShowRowStripes(true); // 縞模様 (行)

サンプルコードには書いていませんが、「最初の列」を指定したい場合はsetFirstColumn、「最後の列」を指定したい場合はsetLastColumn、「縞模様(列)」を指定したい場合はsetShowRowStripesで設定できます。

「フィルター ボタン」を指定する場合は以下のように設定します。

final var ctAutoFilter = table.getCTTable().addNewAutoFilter();
ctAutoFilter.setRef(tableArea.formatAsString()); // フィルター ボタン

POIでテーブルの範囲を変更(XSSFTable#setArea)すると、併せてフィルターの範囲も変更してくれますので、フィルターの範囲を再設定する必要はありません。

構造化参照の数式にはテーブル名と特殊項目指定子を明記する

構造化参照の数式を設定する場合は、以下のようにテーブル名と特殊項目指定子を明記する必要があります。

cell.setCellFormula("製品購入[[#This Row],[単価]] * 製品購入[[#This Row],[購入個数]]")

Excel の画面上では「[@単価] * [@購入個数]」のようにテーブル名を省略したり省略した特殊項目指定子で数式を設定できますが、OOXMLの内部では「製品購入[[#This Row],[単価]] * 製品購入[[#This Row],[購入個数]]」のように設定されています。POIで省略した数式を設定してもエラーになりませんが、Excelファイルを開くと「セルの範囲が無効のエラー」になりますのでご注意ください。

最後に

プログラミングしていると名前付けの大切さは身に染みているかと思います。Excelでも分かりやすく数式を書く工夫のひとつとして、テーブルを使ってみてはいかがでしょうか。

  • Zabbix Enterprise Appliance
  • 低コスト・短納期で提供するまるごとおまかせZabbix