お家の中から出たくない

打首獄門同好会は「布団の中から出たくない」ですが、とりあえず布団からはなんとか出られるタイプの人間です。
ただ、家から会社に行くための外出はマジでしたくない、どうも　え～すけさんです。
※犬の散歩・ドッグラン・ペットショップ・カナヘビ取り・磯は喜んで行くけどね！

なんでIT屋に居るのに、風邪やインフルなどの感染症のリスクが有るにも関わらず、わざわざ人がアホみたいにいる時間帯にギューギューの電車に乗って行かなきゃいけないのか。。。
※夏は夏で熱射病や熱中症、汗や香水がキツい臭い放ってる人への遭遇などのリクスしかない

ウチの会社は在宅できます（ただし案件次第）

POIとExcel使いこなせてる？

JavaでExcelになにか操作するときって、だいたいApache POIを使うかと思いますが、いい感じに使いこなせてたりします？

今回はExcelの機能としてある「セルに名前をつける」とPOIの名前付きセルの取得と合わせて、いい感じに何かするアレをご提案します。

まぁとりあえず簡単サンプル

ExcelUtil.java

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.CellUtil;

public class ExcelUtil implements AutoCloseable {
  private Workbook workbook;

  public ExcelUtil(String path) throws EncryptedDocumentException, IOException {
    this.workbook = WorkbookFactory.create(new File(path));
  }

  public void read() throws IOException {
    List<? extends Name> nameCellList = workbook.getAllNames();
    nameCellList.stream().filter(name -> !name.isDeleted()).forEach(name -> {
      AreaReference[] arefs = AreaReference.generateContiguous(workbook.getSpreadsheetVersion(),
          name.getRefersToFormula());
      CellReference cellReference = arefs[0].getAllReferencedCells()[0];
      Cell cell = CellUtil.getCell(
          CellUtil.getRow(cellReference.getRow(), workbook.getSheet(cellReference.getSheetName())),
          cellReference.getCol());
      System.out.println(cellReference.getSheetName() + ":" + name.getNameName() + ":" + name.getRefersToFormula() + ":"
          + cellReference.getRow() + ":" + cellReference.getCol());
      cell.setCellValue("値を入れてみた");
    });
  }

  public void write(String outPath) throws IOException {
    this.workbook.write(new FileOutputStream(new File(outPath)));
  }

  @Override
  public void close() throws Exception {
    if(this.workbook != null) {
      this.workbook.close();
    }
  }
}

Excel操作に特化した感じのUtilの【雛形】だよ。

インスタンス時にアップロードされたExcelファイルや出力テンプレートのExcelファイルのPathを指定してるよ
→引数を別型のコンストラクターとかオーバーロードすればもっと汎用的になるね（とりあえず即席だよ
Workbookをクラスの内部変数として定義しているので、AutoCloseableをimplして上げて勝手に閉じるようにしてるよ
→優しい作りだね！！
保存用のメソッドも用意してるとか優しさの極みだね
→引数を別型のオーバーロードすればもっと汎用的になるね（こっちも即席だよ

とりあえずreadメソッドに何か色々と書いてるからそれを説明するね。

抜粋

ExcelUtil.java（readメソッドを中心的に）


  public void read() throws IOException {
    // まずここでブック内の名前定義済みの情報を取得するよ
    List<? extends Name> nameCellList = workbook.getAllNames();

    // セルが削除されてる場合でも名前が残っている可能性があるため、
    // isDeletedがtrueのやつはフィルターで除外しているよ
    nameCellList.stream().filter(name -> !name.isDeleted()).forEach(name -> {
      // 名前ついているエリアは1セルとは限らない
      AreaReference[] arefs = AreaReference.generateContiguous(workbook.getSpreadsheetVersion(),
          name.getRefersToFormula());
      // 対象範囲内の1番最初（左上のセル）を取得
      CellReference cellReference = arefs[0].getAllReferencedCells()[0];
      // CellUtilを使って、上記情報からシート名・行番号・カラム番号を元にCellを取得
      Cell cell = CellUtil.getCell(
          CellUtil.getRow(cellReference.getRow(), workbook.getSheet(cellReference.getSheetName())),
          cellReference.getCol());
      // セルの情報をとりあえずsysoutしてみる
      // こんな感じで出力（取得）される
      // Sheet1:test1:'Sheet1'!$A$1:0:0　　　　単一セルの場合（名前がtest1
      // Sheet2:test1:'Sheet2'!$A$1:0:0　　　　単一セルの場合（名前がtest1
      // Sheet1:test2:'Sheet1'!$A$3:$D$6:2:0　範囲指定の場合（名前がtest1
      System.out.println(cellReference.getSheetName() + ":" + name.getNameName() + ":" + name.getRefersToFormula() + ":"
          + cellReference.getRow() + ":" + cellReference.getCol());
      // 対象のセルに値をなんか入れてみる
      cell.setCellValue("値を入れてみた");
    });
  }
}

Excelの仕様上、1ブック内に同じ名前を付けたセルを何個も設定することが可能。
→Excelで数式タブ内の名前の管理から新規作成を行うと【範囲】が選択できて、ブック or 各シートの指定が出来る。
今回のは名前付きのやつを全部取ってきてるため、同一名のセル情報が複数取得できる感じです。
あとはコメントにもダラダラと書いてみたけど、処理の内容的にはそんな感じです。

で、それが出来ると何がええんや？

そう、今回はそれが出来ることで何がいい感じになるかというご提案となりますので、
そこら辺の紹介をこれから書いていくってばよ。

固定レイアウトの帳票出力で使うケース

Excelでテンプレ作って、出力値をセルの行・カラム番号指定で出力するのもいいかと思いますが、
固定レイアウトのテンプレであるなら、出力先に名前を定義してあげれば、
出力用メソッドの引数にMapやDTOを指定して名前と同じKeyやプロパティを指定することで、
共通化が出来るんじゃないかという。

Mapを引数にしてみた

  public void output(Map<String, Object> data) {
    List<? extends Name> nameCellList = workbook.getAllNames();
    nameCellList.stream()
        .filter(
            name -> !name.isDeleted() && data.containsKey(name.getNameName()) && data.get(name.getNameName()) != null)
        .forEach(name -> {
          AreaReference[] arefs = AreaReference.generateContiguous(workbook.getSpreadsheetVersion(),
              name.getRefersToFormula());
          CellReference cellReference = arefs[0].getAllReferencedCells()[0];
          Cell cell = CellUtil.getCell(
              CellUtil.getRow(cellReference.getRow(), workbook.getSheet(cellReference.getSheetName())),
              cellReference.getCol());
          // cell.setCellValueにObjectは渡せないため、型に応じてキャストして渡す
          Object value = data.get(name.getNameName());
          if (value instanceof String) {
            cell.setCellValue((String) value);
          } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
          } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
          } else if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
          } else {
            cell.setCellValue(value.toString());
          }
        });
  }

filterで削除されてないセルかつ引数のMapに名前と同じKeyが有り値が入っているという条件を付けて、
ループ処理にて値を設定している感じになります。

もちろん引数をDTOなどのクラスを渡すことも可能となります。

DTOなどのクラス（Objectで受け取る）を指定した版

  public void output(Object data) {
    BeanWrapper beanWrapper = new BeanWrapperImpl(data);
    List<? extends Name> nameCellList = workbook.getAllNames();

    nameCellList.stream()
        .filter(
            name -> !name.isDeleted() && beanWrapper.isReadableProperty(name.getNameName())
                && beanWrapper.getPropertyValue(name.getNameName()) != null)
        .forEach(name -> {
          AreaReference[] arefs = AreaReference.generateContiguous(workbook.getSpreadsheetVersion(),
              name.getRefersToFormula());
          CellReference cellReference = arefs[0].getAllReferencedCells()[0];
          Cell cell = CellUtil.getCell(
              CellUtil.getRow(cellReference.getRow(), workbook.getSheet(cellReference.getSheetName())),
              cellReference.getCol());
          // cell.setCellValueにObjectは渡せないため、型に応じてキャストして渡す
          Object value = beanWrapper.getPropertyValue(name.getNameName());
          if (value instanceof String) {
            cell.setCellValue((String) value);
          } else if (value instanceof Double) {
            cell.setCellValue((Double) value);
          } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
          } else if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
          } else {
            cell.setCellValue(value.toString());
          }
        });
  }

普通にリフレクションでObjectから値を取ってくるもよし、もしSpringを使っているなら上記の例のようにBeanWrapperを使って値を取ってくるもよし。

コレが出来ることで帳票毎にいちいちクラスを作って挿入位置をいちいちテンプレを調べながらやるなんて言う、リソースの無駄遣いも軽減できるのかなという。
もちろん名前付きエリアへ出力後に、別の何か特殊なことをしなきゃいけないってなったら、その帳票用に上記のExcelUtilを継承したクラスを作れば元の機能を保持したまま拡張が出来るというJavaのメリットも活かせるのではないかというご提案。

一覧系の帳票出力で使うケース

例えば以下のようなケースの場合

複数シートがありそれぞれのシートに一覧(シートによって出力開始位置が異なる)を出す
1シート内にデータの異なる一覧が複数ある

要は出力開始位置はわかってるけど位置がバラバラな場合。

これもやりたいことの考え方としては単純で、ある程度のテンプレの制約にはなりますが、

出力開始位置の1つ上に名前（listの名前）をつける
開始位置の下はヘッダー扱いとする（list内のDTOやMapのKeyを指定
メソッドの引数はListを複数保持したMapやDTOとする

テンプレのイメージは以下
→名前がわかるように仮置き、同一シート内に同名は設定できないため、リスト名カラム名としている

一覧出力雛形（行のスタイルコピーとか一旦ガン無視）

  public void outputList(Object data) {
    BeanWrapper beanWrapper = new BeanWrapperImpl(data);
    PropertyDescriptor[] prop = beanWrapper.getPropertyDescriptors();
    // DTO内に定義されているList分ループ
    List<? extends Name> allNameCellList = workbook.getAllNames();
    for (PropertyDescriptor p : prop) {
      if (p.getPropertyType().equals(List.class)) {
        // 開始位置のセル情報を取得
        List<? extends Name> nameCellList = workbook.getNames(p.getName());
        if (nameCellList == null || nameCellList.isEmpty()) {
          continue;
        }
        List list = (List) beanWrapper.getPropertyValue(p.getName());
        if (list == null || list.isEmpty()) {
          continue;
        }

        // 同一の一覧を複数箇所に出す可能性を考慮して取得したList分ぶん回す
        for (Name name : nameCellList) {
          // セルが削除されていないこと
          if (name.isDeleted()) {
            continue;
          }

          AreaReference arefs = AreaReference.generateContiguous(workbook.getSpreadsheetVersion(),
              name.getRefersToFormula())[0];
          String sheetName = arefs.getFirstCell().getSheetName();
          Sheet sheet = workbook.getSheet(arefs.getFirstCell().getSheetName());
          
          // Listがあるシート内で、頭がリスト名と同じ名前定義されているセル情報を取得し
          // セル番号と名前のMapを生成
          Map<Short, String> headerList = allNameCellList.stream().filter(n ->
          // シート番号が同じ かつ 変数名の最初がリスト名になっている
          n.getSheetName().equals(sheetName) && !n.getNameName().equals(name.getNameName())
              && n.getNameName().startsWith(name.getNameName()))
              .collect(Collectors.toMap(n -> {
                AreaReference a = AreaReference.generateContiguous(workbook.getSpreadsheetVersion(),
                    n.getRefersToFormula())[0];
                return a.getFirstCell().getCol();
              }, n -> n.getNameName()));
          // データ出力位置
          int outStartIndex = arefs.getFirstCell().getRow() + 2;
          // 出力前に行を挿入（コレをしないと既存行の上書きになってしまうため
          sheet.shiftRows(outStartIndex + 1, sheet.getLastRowNum(), list.size());

          // TODO セルのスタイルとかをコピーする（めんどいので省略

          // データの出力
          for (Object obj : list) {
            BeanWrapper bw = new BeanWrapperImpl(obj);
            Row dataRow = CellUtil.getRow(outStartIndex, sheet);
            for (Entry<Short, String> e : headerList.entrySet()) {
              Object value = bw.getPropertyValue(e.getValue());
              if (value == null) {
                continue;
              }
              Cell cell = CellUtil.getCell(dataRow, e.getKey());
              if (value instanceof String) {
                cell.setCellValue((String) value);
              } else if (value instanceof Double) {
                cell.setCellValue((Double) value);
              } else if (value instanceof Boolean) {
                cell.setCellValue((Boolean) value);
              } else if (value instanceof Integer) {
                cell.setCellValue((Integer) value);
              } else {
                cell.setCellValue(value.toString());
              }
            }
            outStartIndex++;
          }
        }
      }
    }
  }

ダラダラと長いソースになってますが、やらんとしてることとしては、概ね出来てるんじゃないかなと。

DTO（Object）の中からList型の変数を取得し、取得分ループ
上記のループ中のlistの名前と同じ名前で定義されているExcel上のセル情報を取得
ヘッダー情報をMap<カラム番号, 定義名>として生成
出力リスト分、行シフトする
リスト分ぶん回して、Mapの情報と突き合わせて出力処理を行う

サンプルとして以下のようなテストデータ用のクラスを作って実行

サンプルデータ

  @Data
  public class TestData {
    List<InnerTestData> list1;

    public TestData() {
      list1 = new ArrayList<>();
      for (int i = 0; i < 10; i++) {
        list1.add(new InnerTestData());
      }
    }
  }

  @Data
  public class InnerTestData {
    String list1No = "1";
    String list1Col1 = "2";
    String list1Col2 = "3";
    String list1Col3 = "4";
    String list1Col4 = "5";
    // ～～～　省略　～～～
  }

出力結果は以下となる

ダラダラと書いてみましたが

帳票やテンプレ単位にクラスを作らなくても、案外Excelのテンプレと引数用のクラス作ってデータ生成さえ出来ちゃえば、Excel帳票なんて楽に色々となんとかなりそうに思えるでしょ？（ね、簡単でしょ？

セルに名前を付けているので、後から項目が追加されたとして行の挿入・削除が発生したとしてもExcelはある程度賢いので、名前の参照位置もきちんと更新してくれるはずなので、修正工数も割と少なく出来てしまうのではないかという憶測。

また一覧側の方については、出力先がもし結合してたりした場合やデータ：1に対して複数行で表現しないといけないとかのケースの場合だとしても、Listにアノテーションとか付けたりして1データあたりの行数とかを持たせて対応することとかも実現可能だし、ListDataDtoとかをラッパーとかで作ってそこでデータ持たせるとかでもなんとかなりそうじゃねとか思ったりと、いかようにも対応はできるかなとは思います。
※出力側の処理は結構修正必要そうですが、そこまで難しくはないんじゃないかなという
→ヘッダーの下N行をコピーして挿入していくだけのイメージ

ListDataDto.java　サンプルイメージ

  public class ListDataDto {
    // デフォルトは1データ：1行
    int rowNum = 1;
    // 出力データ
    List<Object> list;

    public ListDataDto(int rowNum, List<Object> list) {
      this.rowNum = rowNum;
      this.list = list;
    }

    public ListDataDto(List<Object> list) {
      this.list = list;
    }
  }

  public void out() {
    Map<String, ListDataDto> outData = new HashMap<>();
    // 1データあたり5行使うサンプル（データは適当
    outData.put("list1", new ListDataDto(5, List.of("", "", "")));
  }

汎用的なロジックはすぐに作れますが、かゆいところに手を届かせるためには、それ相応な実装が必要になるかと思いますので、それの雛形実装みたいな位置づけで本ソースを参考にしてもらえればと思います。
※雛形に不具合があったとしても、苦情は一切受け付けませんが、コメントとかで優しく指摘してください。。。