Java Poiを使ってExcelのGrepツールを作ってみる

始めに

JavaとPoiを使ってExcelファイルの文字列を検索するツールを作ってみた

このツールを作った目的

VBAにも似たようなツールはあるが、これらのツールには以下の欠点がある

• ExcelファイルをオープンするたびにOSのプロセスを起動するため、マシンに負荷がかかる
• 大量のファイル（体感的には20～30）を一括で処理するとツールが頻繁にフリーズする
• 何より致命的なのはVBAのツールの実行中は他のExcelのワークブックが使えなくなる

Java + PoiでExcelファイルの処理を行うとVBAツールにあった欠点はほぼ解消される

• JVM上で処理が完結するためExcelファイルをオープンするたびにOSのプロセスを上げることはなくなる
• 大量のファイルを短時間で処理できる
  • 具体的には3000ファイルを20分弱で処理できる
• ツール実行中もExcelのワークブックは普通に操作できる

このツールでやりたいこと

箇条書きにするとこんな感じ

• 特定のサブディレクトリ以下のエクセルファイルをすべて処理の対象にする
• 出力結果を指定したディレクトリに保存する
• Excelに含まれているセルとオートシェイプを検索の対象にする
• 検索文字列には正規表現を使えるようにする

ツールの説明

UI

JavaFxで作ってみた
JavaFx Scene builderを使えばVBの感覚で簡単に画面作成ができる

使い方

検索先に検索対象のexcelが保存されているディレクトリを指定し、出力先に実行結果の格納先を指定し、検索文字列に正規表現で検索文字列を指定し、実行ボタンを押すだけです
（UIの見た目通り）
なお、検索対象のファイルは実行状況に表示されます

ツールの実装

ソース

GitHub https://github.com/triple4649/poisampleにアップしたよ

依存するJar

PoiでExcelファイル（xls形式、xlsx形式）を読み込むために必要なJarをGradleに定義したものから引っ張ってきた

    compile group: 'org.apache.poi', name: 'poi-excelant', version: '3.17'
    compile group: 'dom4j', name: 'dom4j', version: '1.6.1'
    compile group: 'javax.xml.bind', name: 'jsr173_api', version: '1.0'
    compile group: 'org.apache.poi', name: 'poi-ooxml-schemas', version: '4.0.0'
    compile group: 'org.apache.poi', name: 'openxml4j', version: '1.0-beta'
    compile group: 'org.apache.xmlbeans', name: 'xmlbeans', version: '2.6.0'

依存するJarは意外と多いので地道にJarをダウンロードするよりは、Gradleで依存関係を解決するほうが楽になる
Gradleの全文はここを参照のこと

ディレクトリの走査

JDK1.7から導入されたJava.nioのwalkFileTreeを使えばディレクトリの走査は簡単に行うことができる
詳しくはJava:ディレクトリを渡り歩くを参照のこと

エクセルファイルの処理

データモデルについて

ExcelとPoiのデータモデルの対応は

Excel	Poi
WorkBook	org.apache.poi.ss.usermodel.Workbook
Sheet	org.apache.poi.ss.usermodel.Sheet
Row	org.apache.poi.ss.usermodel.Row
Cell	org.apache.poi.ss.usermodel.Cell

となる
なお、org.apache.poi.ss.usermodel.Workbookはxls形式、xlsx形式、いずれにも対応している

シート、列、行の処理

基本的に、Poiのデータモデルのforeachを使って処理する
各データモデルのforeachには"Consumer<? extends Sheet > action"を渡す

＜図　Poiのデータモデルとforeachの引数の対応＞

Poiのデータモデル	foreachの引数
org.apache.poi.ss.usermodel.Workbook	Consumer<? extends Sheet > action
org.apache.poi.ss.usermodel.Sheet	Consumer<? extends Row> action
org.apache.poi.ss.usermodel.Row	Consumer<? extends Cell> action

Java8のFunctionインターフェースを見慣れていない人はConsumerクラスに戸惑うかもしれないが、Consumerとは、「引数一つをとり、戻り値を返さない処理をする」ためのクラスである。
もっと端的に描くと
引数を一つ取り、戻り値のない処理を書いた

• ラムダ式
• メソッド

のことである。
今回のworkbookに含まれるすべてのsheetを処理するロジックではWorkbookインターフェイスのforeachメソッドに対し

    private void searchWorkBook(Workbook  wk) {
        wk.forEach(s -> searchSheet(s));
    }

とシート処理のメソッドを指定し、searchSheetは

    private void searchSheet(Sheet s) {
        //オートシェイプを処理します
        s.createDrawingPatriarch().forEach(o->handleShape(o));
        //セルを処理します
        s.forEach(r->searchRow(r));
    }

のような、引数sheetを指定した戻り値のない処理を実装している

ワークブックの処理

各シートに対する列挙処理を書くだけで、特筆することはない

シートの処理

ここでやるべきことは

• シートに含まれるオートシェープの処理
• シートに含まれる列の処理

である

シートに含まれるオートシェープの処理

まず、createDrawingPatriarch()メソッドを使いDrawing<?>を取得する
Drawing<?>はシートに含まれるオートシェイプを管理するインターフェイスである。
シートに含まれるオートシェープを処理するには、Drawing<?>#foreachで列挙されるオートシェイプを処理しすればよい
ただし、Drawing<?>#foreachの引数は

Consumer<? extends Object > action

となっている。
つまり、各々のオートシェープを処理するときは

createDrawingPatriarch()#foreach(s->(キャスト)s・・・・)

のようにObjectを自前で適切な適切な型にダウンキャストする必要がある。
今回、処理したいオートシェープは

• テキストが含まれているオートシェイプ
• グループ化されているオートシェイプ（に含まれる文字列）

であるため、これらのオートシェイプが処理できるよう、適切にダウンキャストする必要がある。

テキストが含まれているオートシェイプの処理

Drawing<?>で列挙されたオブジェクトをXSSFSimpleShapeインターフェイス、またはHSSFSimpleShapeインターフェイスにキャストする必要がある
HSSFSimpleShape、XSSFSimpleShapeは＜図 HSSFSimpleShape、XSSFSimpleShapeの使い分け＞のようにxls形式とxlsx形式化で使い分ける。

エクセルファイルの形式	オートシェイプ(テキストボックス)の型
xls	org.apache.poi.xssf.usermodel.XSSFSimpleShape
xlsx	org.apache.poi.hssf.usermodel.HSSFSimpleShape

＜図 HSSFSimpleShape、XSSFSimpleShapeの使い分け＞

グループ化されているオートシェイプ

Drawing<?>で列挙されたオブジェクトをXSSFShapeGroupクラス、またはHSSFShapeGroupクラスのいずれかにキャストする。
グループオブジェクトもxls形式とxlsx形式で使うクラスが違うので、それぞれのファイル形式に応じたクラスにキャストする必要がある

エクセルファイルの形式	オートシェイプ(グループ)の型
xls	org.apache.poi.hssf.usermodel.HSSFShapeGroup
xlsx	org.apache.poi.xssf.usermodel.XSSFShapeGroup

＜図 HSSFShapeGroup、HSSFShapeGroupの使い分け＞

XSSFShapeGroup、HSSFShapeGroupともにforeachメソッドでグループ化されているオートシェイプを取得することができる

まとめ

長々とオートシェイプの処理について書いてきたが、実装するソースは極めて単純なものです

    //オートシェイプを処理するメソッド
    private void handleShape(Object d) {
        String s="";
        //shapeの処理(XLSX形式)
        if(d instanceof XSSFSimpleShape) {
            s =((XSSFSimpleShape) d).getText();
        }
        //shapeの処理(XLS形式)
        if(d instanceof HSSFSimpleShape) {
            s =((HSSFSimpleShape) d).getString().getString();
        }
        //グループ化されたshapeの処理(XLSX形式)
        if(d instanceof XSSFShapeGroup) {
            ((XSSFShapeGroup)d).forEach(gs->handleShape(gs));
        }
        //グループ化されたshapeの処理(XLS形式)
        if(d instanceof HSSFShapeGroup) {
            ((HSSFShapeGroup)d).forEach(gs->handleShape(gs));
        }
        result.add(func.apply(s));
    }

＜図 オートシェープ処理実装例＞
このソースの肝は、グループ化したオブジェクトを処理するときに自分自身を呼び出す、再帰処理を行っていることだ。
親→子の入れ子構造になっているデータ構造は再帰処理を使うと、ロジックがすっきり書くことができる。

セルの処理

Cell#getCellTypeEnum()で返されるEum値でテキスト値を取得する方法が変わる
セルの書式、EUM値、テキスト値の取得の対応付けは＜図　セルの書式、EUM値、テキスト値の取得の対応付け＞を参照のこと。

セルの書式	CellType	テキスト値の取得
真偽値	CellType.BOOLEAN	c.getBooleanCellValue()
文字列	CellType.STRING	c.getStringCellValue()
日付型	CellType.NUMERICかつDateUtil.isCellDateFormattedが真	c.getDateCellValue()
数値型	CellType.NUMERICかつDateUtil.isCellDateFormattedが偽	c.getNumericCellValue()
関数	CellType.FORMULA	c.getNumericCellValue()を実行し、例外が発生したらc.getStringCellValue()
エラー	CCellType.ERROR	c.getErrorCellValue()

＜図　セルの書式、EUM値、テキスト値の取得の対応付け＞

JavaFxからツールの呼び出しについて

例えば

のようなチェックボックスを追加して、チェックの状態に応じて処理を切り替える場合は
「チェックの組み合わせのフラグ」
に応じて処理を分岐するよりも
「チェックしたときにやりたいことFunctionインターフェイスで実装し、それを切り替える」
ほうがフラグ管理をしない分、すっきりしたロジックをかけそうな気がする

具体的には

    //正規表現を使うcheckボックスの押下状況に応じて
    //Grep処理を切り替える
    @FXML 
    protected void onRegCheck(ActionEvent evt) {
        String target =funcOnUpLowStr.apply(searchStr.getText());
        if(this.checkReg.isSelected()) {
            funcSearchStr=s->{
                if(funcOnUpLowStr.apply(s).matches(target))return s;
                else return "";
            };
        }else {
            funcSearchStr=s->{
                if(funcOnUpLowStr.apply(s).indexOf(target)>=0)return s;
                else return "";
            };
        }
    }
    @FXML 
    //大文字小文字を使うcheckボックスの押下状況に応じて
    //大文字小文字変換を切り替える
    protected void onUpLowerStrcheck(ActionEvent evt) {
        if(this.checkUpLowerStr.isSelected()) {
            funcOnUpLowStr=s->s.toLowerCase();
        }else {
            funcOnUpLowStr=s->s;
        }
    }
    @FXML
    protected void onSearch(ActionEvent evt) {
        try {
            new FindingStr(
                //ディレクトリ操作処理実行中にやりたいことをラムダ式で指定する
                s->resultField.setText(s + "\n" +resultField.getText() ),
                //セルの値を取得したときにやりたいことを関数型インターフェイスで指定する
                funcSearchStr
            ).search(inputpath.getText(), 
                    outputpath.getText()+"\\result.txt");
        }catch(Exception e) {
            e.printStackTrace();
        }
    }

みたいに実装することができそう