Categories: JavaSpringBootアプリ開発

【Apache POI】JavaでExcelのGrepツールを作った

今まで、ExcelをgrepするときはVBAで作成したマクロでgrepしていましたが、このやり方だとVBAでExcelをgrepしている間はマクロが終わるまで他のExcelを開けません。

grep中に他のExcelファイルを触りたいので、JavaでExcelのgrepツールが作れないかと思ってライブラリを探したところApache POIというのを見つけたので、これでExcelのgrepツールを作ってみました。

目次

目次へ

特徴

今回作成したツールの特徴は以下のとおりです。

  • ブラウザで動くスタンドアローンなWebアプリ
  • grepした結果をCSVファイルに出力
  • 指定したフォルダを再帰的に検索してすべてのExcelファイルをgrepする
  • 「.xls」と「.xlsx」の両方に対応
  • 図形の中の文字も検索可能

技術要素は以下のとおりです。

  • Spring Boot
  • Apache POI
  • Maven

ここからはコード解説していきます。

Mavenプロジェクトを作成する

今回は、SpringBootでスタンドアローンに使えるようにアプリ化したので、以下の記事を参考にMavenプロジェクトを作成します。

【Windows版】VSCodeで始めるSpringBoot(Mavenプロジェクト編)

pom.xmlにApache POIのライブラリの設定を追記します。

pom.xml

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>5.1.0</version>
</dependency>

コード解説

すべての処理を解説したいところではありますが、それを記すには余白が小さすぎるので重要な部分のみピックアップして解説していきます。

index.html

アプリを起動したときに一番最初に表示される画面です。

index.html

<!DOCTYPE html>
<html lang="ja" xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>ExcelGrepツール</title>
<script type="text/javascript"></script>
</head>
<body>
<h2>ExcelGrepツール</h2>
<p>プロセスID：<span th:text="${pid}"></span></p>
<form th:action="@{/grep}" method="post">
    <label>
        Excelが格納されたフォルダを指定してください。：<input type="text" name="msg1"><br>
        検索対象文字列を入力してください。：<input type="text" name="msg2"><br>
    </label>
    <button>ExcelをGrepする</button>
</form>
</body>
</html>
</html>

以下の部分の処理では初期表示時にアプリのプロセスIDを画面に表示します。このプロセスIDはタスクマネージャでアプリをキルする場合などにアプリのプロセスを特定するために使います。

<p>プロセスID：<span th:text="${pid}"></span></p>

以下のformでExcelが格納されたフォルダと検索対象文字列を入力してボタンを押すと、サーバ側に処理が移り、Apache POIのライブラリを使ってgrep処理を行います。

<form th:action="@{/grep}" method="post">
    <label>
        Excelが格納されたフォルダを指定してください。：<input type="text" name="msg1"><br>
        検索対象文字列を入力してください。：<input type="text" name="msg2"><br>
    </label>
    <button>ExcelをGrepする</button>
</form>

Sample.java

Sample.javaというクラス名ですが、ツールの大きな流れを実装しているクラスです。

Sample.java

package com.example.greptool;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Path;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.StringJoiner;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class Sample {

    public static void main(String param1, String param2)  {

        //ルートディレクトリ
        String rootPath = param1;

        //検索対象文字列
        String targetStr = param2;

        //全ブックの情報を格納するオブジェクト。
        List<ExcelInfo> allBookList = new ArrayList<>();

        Path[] pathss;
        try {
            //フルパスを取得する。
            pathss = FilePathUtil.getFullPaths(rootPath);

            //取得したファイル数分繰り返す。
            for (Path p : pathss) {

                //ユーザがファイルを開いたときに生成される「~$test1.xlsx 」のような一時ファイルは読み飛ばす。
                if(p.getFileName().toString().startsWith("~")){
                    continue;
                };

                //「.xls 」「.xlsx」以外のファイルを読み飛ばす。
                if(! p.getFileName().toString().endsWith(StringUtil.EXTENTISON_XLS) &&
                    ! p.getFileName().toString().endsWith(StringUtil.EXTENTISON_XLSX)){
                    continue;
                };

                //パスを文字列に変換。
                String wbFilePath = p.toString();

                InputStream in = new FileInputStream(wbFilePath);

                //EXCELブックを開く。
                Workbook wb = WorkbookFactory.create(in);
            
                //1ブック分の情報を格納するオブジェクトを生成。
                List<ExcelInfo> oneBookList = new ArrayList<>();

                //Excelの情報を保持するオブジェクトを生成
                ExcelInfo excelInfo = new ExcelInfo();
                excelInfo.setWb(wb);                    //処理中のExcelブック
                excelInfo.setWbFilePath(wbFilePath);    //処理中のExcelブックのフルパス（ファイル名含む。）
                excelInfo.setTargetStr(targetStr);      //検索対象の文字列
    
                //1ブック分を検索。
                new PoiUtil().searchWorkBook(excelInfo,oneBookList);
    
                //1ブック分を検索結果を格納。
                allBookList.addAll(oneBookList);
            }

            //検索結果を出力。
            for(ExcelInfo d: allBookList){
                System.out.println("[結果]" + d.getWbFilePath() + "    " + d.getSheetName() + "    " + d.getCellAddress() + "    " + d.getHittedStr());
            }

            String part2 = targetStr != "" ? targetStr : "検索文字列なし";
            String name = "result_" + part2 + "_";
            String dateStr = StringUtil.MillisecondsFormatter.format(LocalDateTime.now()).toString();
                        
            CsvUtil.createCSV(rootPath, name + dateStr + ".csv", new Sample().generateOutput(allBookList));

        } catch (FileNotFoundException e1) {
            e1.printStackTrace();
        }catch (IOException e){
            e.printStackTrace();
            System.out.println(e.getMessage());
        }
    }

    //CSV出力用の内容を生成する。
    List<String> generateOutput(List<ExcelInfo> allBookList){
        List<String> list = new ArrayList<>();

        //ヘッダ情報を格納
        String header = String.join(StringUtil.TAB_STR,   
                                    "ファイルパス",
                                    "シート名",
                                    "セル番地",
                                    "ヒットした文字列");
        list.add(header);

        //明細情報を格納
        for(ExcelInfo data : allBookList){
            StringJoiner joiner = new StringJoiner(StringUtil.TAB_STR);
            joiner.add(data.getWbFilePath());
            joiner.add(data.getSheetName());
            joiner.add(data.getCellAddress());
            joiner.add(data.getHittedStr());
            list.add(joiner.toString());
        }

        return list;
    }
}

処理の大まかな流れは以下のとおりです。

  1. 指定したフォルダの中からExcelファイルを1つ取り出す
  2. grep処理を行う
  3. grep結果をallBookListに格納する
  4. １～３をExcelファイル数だけ繰り返す
  5. すべてのExcelファイルのgrepが完了したら結果をCSVに出力する

以下の処理ではファイルの読み飛ばし処理を行っています。指定したフォルダの中にはExcelファイル以外にもテキストファイルやPDFファイルなど様々なものが格納されていることが想定されます。これらのファイルが存在していると処理がうまく走らないため、拡張子を見て、「.xls」「.xlsx」以外のファイルは読み飛ばすようにしています。

また、ユーザがExcelファイルを開いていると「~$test1.xlsx」といった一時ファイルが生成されていることが稀にあります。こういった一時ファイルも処理がうまく走らなくなる原因になるため、読み飛ばすようにしています。

//ユーザがファイルを開いたときに生成される「~$test1.xlsx 」のような一時ファイルは読み飛ばす。
if(p.getFileName().toString().startsWith("~")){
    continue;
};

//「.xls 」「.xlsx」以外のファイルを読み飛ばす。
if(! p.getFileName().toString().endsWith(StringUtil.EXTENTISON_XLS) &&
    ! p.getFileName().toString().endsWith(StringUtil.EXTENTISON_XLSX)){
    continue;
};

grep処理のメイン処理は以下からスタートします。Excelファイルを1ブックずつ順に処理します。

//1ブック分を検索。
new PoiUtil().searchWorkBook(excelInfo,oneBookList);

以下の処理では、grep結果をコンソールに出力するとともに、CSVファイルへの書き出しを行います。CSVファイルに出力する内容は以下のとおりです。

  • ファイルパス：Excelファイルが格納されているパスをフルパスで出力します。（ルートからのパス＋ファイル名）
  • シート名
  • セル番地
  • ヒットした文字列
//検索結果を出力。
for(ExcelInfo d: allBookList){
    System.out.println("[結果]" + d.getWbFilePath() + "    " + d.getSheetName() + "    " + d.getCellAddress() + "    " + d.getHittedStr());
}

String part2 = targetStr != "" ? targetStr : "検索文字列なし";
String name = "result_" + part2 + "_";
String dateStr = StringUtil.MillisecondsFormatter.format(LocalDateTime.now()).toString();
            
CsvUtil.createCSV(rootPath, name + dateStr + ".csv", new Sample().generateOutput(allBookList));

以下の処理では、CSVファイルへ書き出すための編集処理を行っています。

//CSV出力用の内容を生成する。
List<String> generateOutput(List<ExcelInfo> allBookList){
    List<String> list = new ArrayList<>();

    //ヘッダ情報を格納
    String header = String.join(StringUtil.TAB_STR,   
                                "ファイルパス",
                                "シート名",
                                "セル番地",
                                "ヒットした文字列");
    list.add(header);

    //明細情報を格納
    for(ExcelInfo data : allBookList){
        StringJoiner joiner = new StringJoiner(StringUtil.TAB_STR);
        joiner.add(data.getWbFilePath());
        joiner.add(data.getSheetName());
        joiner.add(data.getCellAddress());
        joiner.add(data.getHittedStr());
        list.add(joiner.toString());
    }

    return list;
}

PoiUitl.java

Apache POIのライブラリを使って色々実装しているクラスになります。

PoiUitl.java

package com.example.greptool;

import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFShapeGroup;
import org.apache.poi.hssf.usermodel.HSSFSimpleShape;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Shape;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFShapeGroup;
import org.apache.poi.xssf.usermodel.XSSFSimpleShape;

public class PoiUtil {

    DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy/MM/dd");
    DataFormat df;
    LocalDateTime localDateTime;
    DataFormatter formatter;
    FormulaEvaluator evaluator;

    public void searchWorkBook(ExcelInfo excelInfo, List<ExcelInfo> oneBookList) {
        // Excelブックを取り出す。
        Workbook wb = excelInfo.getWb();

        // EXCEL数式の計算結果を格納するためのobjを生成する。
        evaluator = wb.getCreationHelper().createFormulaEvaluator();

        // 1ブック内の全シートを検索する処理を呼び出す。
        wb.forEach(sheet -> searchSheet(sheet, excelInfo, oneBookList));
    }

    private void searchSheet(
            Sheet sheet,
            ExcelInfo excelInfo,
            List<ExcelInfo> oneBookList) {
        // シート名を取得する。
        String sheetName = sheet.getSheetName();
        excelInfo.setSheetName(sheetName);

        // 1シート内のすべてのオートシェイプを検索する処理を呼び出す。
        sheet
                .createDrawingPatriarch()
                .forEach(objct -> searchShape(objct, excelInfo, oneBookList));

        // 1シート内のすべての行を検索する処理を呼び出す。
        sheet.forEach(row -> searchRow(row, excelInfo, oneBookList));
    }

    private void searchRow(
            Row row,
            ExcelInfo excelInfo,
            List<ExcelInfo> oneBookList) {
        // 1行内のすべてのセルを検索する処理を呼び出す。
        row.forEach(cell -> searchCell(cell, excelInfo, oneBookList));
    }

    private void searchCell(
            Cell cell,
            ExcelInfo excelInfo,
            List<ExcelInfo> oneBookList) {
        // セル番地を取得する。
        excelInfo.setCellAddress(cell.getAddress().toString());

        // セルの型を判定する。（型ごとに値を取得する処理を振り分ける必要があるため。）
        CellType cellType = cell.getCellType();
        switch (cellType) {
            // 文字型の場合
            case STRING:
                String nomarlStr = cell.getStringCellValue();

                // 検索対象文字が含まれていたらリストに追加する。
                if (nomarlStr.contains(excelInfo.getTargetStr())) {
                    excelInfo.setHittedStr(nomarlStr);
                    resultAdd(excelInfo, oneBookList);
                }
                break;
            // 数値型 or 日付型の場合
            case NUMERIC:
                // NUMERICの場合は、数値型か日付型かを判定して処理を振り分ける。
                // 日付型の場合
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    localDateTime = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
                    String formatted = dtf.format(localDateTime);

                    // 検索対象文字が含まれていたらリストに追加する。
                    if (formatted.contains(excelInfo.getTargetStr())) {
                        excelInfo.setHittedStr(formatted);
                        resultAdd(excelInfo, oneBookList);
                    }
                    // 数値型の場合
                } else {
                    String numlicStr = String.valueOf(cell.getNumericCellValue());

                    // 検索対象文字が含まれていたらリストに追加する。
                    if (numlicStr.contains(excelInfo.getTargetStr())) {
                        excelInfo.setHittedStr(numlicStr);
                        resultAdd(excelInfo, oneBookList);
                    }
                }
                break;
            // EXCEL関数型の場合 ※sum()など。
            case FORMULA:
                // 「FORMULA」→「String」に変換（計算後の値を文字列で取得。）
                formatter = new DataFormatter();
                String resultStr = formatter.formatCellValue(cell, evaluator);

                // 検索対象文字が含まれていたらリストに追加する。
                if (resultStr.contains(excelInfo.getTargetStr())) {
                    excelInfo.setHittedStr(resultStr);
                    resultAdd(excelInfo, oneBookList);
                }
                break;
            // 真偽値の場合
            case BOOLEAN:
                String booleanStr = String.valueOf(cell.getBooleanCellValue());

                // 検索対象文字が含まれていたらリストに追加する。
                if (booleanStr.contains(excelInfo.getTargetStr())) {
                    excelInfo.setHittedStr(booleanStr);
                    resultAdd(excelInfo, oneBookList);
                }
                break;
            // その他
            case BLANK: // セルに値が入っていない場合
            case ERROR:
            case _NONE:
                break;
        }
    }

    private void searchShape(
            Shape shape,
            ExcelInfo excelInfo,
            List<ExcelInfo> oneBookList) {
        String hittedTxt = "";

        // shapeの処理(XLSX形式)
        if (shape instanceof XSSFSimpleShape) {
            hittedTxt = ((XSSFSimpleShape) shape).getText();
        }
        // shapeの処理(XLS形式)
        if (shape instanceof HSSFSimpleShape) {
            hittedTxt = ((HSSFSimpleShape) shape).getString().getString();
        }
        // グループ化されたshapeの処理(XLSX形式)
        if (shape instanceof XSSFShapeGroup) {
            ((XSSFShapeGroup) shape).forEach(
                    gs -> searchShape(shape, excelInfo, oneBookList));
        }
        // グループ化されたshapeの処理(XLS形式)
        if (shape instanceof HSSFShapeGroup) {
            ((HSSFShapeGroup) shape).forEach(
                    gs -> searchShape(shape, excelInfo, oneBookList));
        }

        // 検索対象文字が含まれていたらリストに追加する。
        if (hittedTxt.contains(excelInfo.getTargetStr())) {
            excelInfo.setHittedStr(hittedTxt);
            resultAdd(excelInfo, oneBookList, true);
        }
    }

    private void resultAdd(
            ExcelInfo excelInfo,
            List<ExcelInfo> oneBookList,
            boolean... zukeiFlg) {
        ExcelInfo data = new ExcelInfo();

        data.setWbFilePath(excelInfo.getWbFilePath()); // フルパス（Excelファイル名含む）
        data.setSheetName(excelInfo.getSheetName()); // シート名

        // セル番地
        // zukeiFlgが渡された場合は、セル番地に[図形]を代入する。zukeiFlgが渡されなかった場合はそのままセル番地を代入。
        if (zukeiFlg != null && zukeiFlg.length > 0 && zukeiFlg[0]) {
            data.setCellAddress("[図形]");
        } else {
            data.setCellAddress(excelInfo.getCellAddress());
        }

        data.setHittedStr(excelInfo.getHittedStr()); // ヒットした文字列

        oneBookList.add(data);
    }
}

searchWorkBookメソッド

Sample.javaから呼び出されるメソッドです。PoiUtilクラスの中で唯一のPublicメソッドになっています。

1つのExcelファイルの中には複数のシートが存在していることが想定されるので、1シートずつ順にsearchSheetメソッドを呼び出します。

searchSheetメソッド

このメソッドでは、1シート分の処理を行います。主に以下の3つの処理を行っています。

  1. シート名の取得
  2. シート内の図形（オートシェイプ）内の文字を対象にしたgrep処理（searchShapeメソッドの呼び出し）
  3. シート内のすべての行を対象にしたgrep処理（searchRowメソッドの呼び出し）

1シート内には数万のセルが存在していますが、セルを1つずつ順次処理していくためにまずは1行ずつ順次処理していくイメージです。

searchRowメソッド

このメソッドでは、1行分を対象にした処理を行います。1行の中に全セルに対して1セルずつgrep処理を行うためにsearchCellメソッドを呼び出します。

searchCellメソッド

このメソッドでは、1セル分を対象にした処理を行います。このメソッドを呼び出すことでようやくgrep処理が行えるわけです。このメソッドでは以下の流れでgrep処理を行っています。

  1. セル番地を取得
  2. セルの型を判定
  3. セル内の文字と検索対象文字列を比較
  4. セル内に検索対象文字列が含まれていたら、セル内の文字列を結果リストに格納

セルの値を取得するためには、セルの型を判定する必要があります。なぜかというと、セルの型ごとに値を取得するためのメソッドが異なるからです。

セルの型はorg.apache.poi.ss.usermodel.CellTypeに定義されていて、以下の7種類があります。

セルの型対象
CellType.STRING文字列型セル
CellType.NUMERIC数値型セル または 日付型セル
CellType.FORMULAExcel関数が設定されたセル=SUM()など
CellType.BOOLEAN真偽値型のセル（true,false）
CellType.BLANK何も入力されていないセル
CellType.ERROR
CellType._NONE

セルの型が判定できたら、値を取得するためのメソッドを呼び出します。

値を取得するためのメソッドにはorg.apache.poi.ss.usermodel.Cellに定義されていて、以下の種類があります。

セルの型値を取得するためのメソッド
CellType.STRINGgetStringCellValue()
CellType.NUMERIC数値型の場合はgetNumericCellValue()
日付型の場合はgetDateCellValue()
CellType.FORMULAformatCellValue(cell, evaluator)
CellType.BOOLEANgetBooleanCellValue()

セルの型が数値型と日付型の場合は両方ともCellType.NUMERICとして判定されてしまうため、値を取得するときは、数値型と日付型のどちらかなのかを区別してメソッドを呼び出す必要があります。

区別するにはorg.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(Cell cell)を使って以下の判定を行います。trueであれば日付型、falseであれば数値型になります。

// NUMERICの場合は、数値型か日付型かを判定して処理を振り分ける。
// 日付型の場合
if (DateUtil.isCellDateFormatted(cell)) {
	//日付型だった場合の処理

//数値型の場合
else {
	//数値型だった場合の処理
}

また、セルの型がExcel数式型（CellType.FORMULA）の場合、数式自体がgrepの対象となってしまうので、数式の計算結果に対してgrep処理を行いたい場合は、org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(Cell cell, FormulaEvaluator evaluator)を使って以下の処理で計算結果を文字列として取得します。

// EXCEL関数型の場合 ※sum()など。
case FORMULA:
    // 「FORMULA」→「String」に変換（計算後の値を文字列で取得。）
    formatter = new DataFormatter();
    String resultStr = formatter.formatCellValue(cell, evaluator);

ダウンロード

作成したツールを公開しておきます。

注意点

大体の文字列はgrepできるようになっていますが、見落としはあると思います。

このツールを使用した際に生じた損害については責任を負いませんので、ご自身の責任で使用してください。

Excelをgrepするツールダウンロード

※ダウンロードしたファイルの展開には7zipが必要です。

アプリの起動方法と終了方法については以下の記事を参考にしてください。

【Spring Boot】アプリをローカルPC上で起動する方法

以上で記事の解説はお終い！

もっとJavaやSpringを勉強したい方にはUdemyがオススメ！同僚に差をつけよう！

【Udemy厳選】2023年Javaで人気のオススメ動画コース6選
issiki_wp

Leave a Comment
Share
Published by
issiki_wp
3年 ago

Recent Posts

【Udemy厳選】2023年Gitで人気のオススメ動画コース3選

ここからはUdemyでベストセ…

1年 ago

【Java入門】HttpURLConnectionでGET送信して結果をJacksonでJSONに変換して受け取る

App.java public…

2年 ago

【Spring DI】@Qualifier、@Primary、@Profileの使い方

本記事は、Spring Fra…

2年 ago

【Spring DI】インターフェースを実装した2つのクラスが2つとも@Componentを付与されていた場合どうなるのか

本記事は、Spring Fra…

2年 ago

【Spring DI】component-scanの研究1

本記事は、Spring Fra…

2年 ago

【Udemy厳選】2023年HTML、CSS、JavaScriptで人気のオススメ動画コース13選

ここからはUdemyでオススメ…

2年 ago