Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

グロースエクスパートナーズAdvent Calendar 2024

Day 18
@dori334inグロースエクスパートナーズ株式会社

[Excel差分比較] セルの値以外の要素の差分を比較したい

Last updated at Posted at 2024-12-17

はじめに

こんにちは！株式会社GxPに今年新卒として入社した香取です。
この記事はグロースエクスパートナーズAdvent Calender 2024
の18日目の記事です
配属一か月目に業務システムで使用しているExcelファイルをうっかりデグレさせ、障害を発生させてしまいました。今回はその後の再発防止策として開発した「Excel差分比較ツール」について書いていこうと思います。

背景

開発中のシステムにはPDFを発行する機能があり、Excelで作成したテンプレートにJavaで値を入力し、PDFとして出力する仕組みを採用しています。値の入力は、テンプレート内のセルを名前付き範囲として設定し、Java側で名前を指定することで実現しています。

今回の障害は、私がテンプレートを修正する際に、誤って古いバージョンのExcelファイルを編集してしまったことが原因です。その結果、セルの値や名前付き範囲が変更され、PDFが正しく出力されなくなりました。

このような問題を防ぐため、Excelファイルの差分を確認できる仕組みを作る必要があると考え、「Excel差分比較ツール」を開発しました。

問題

  • Excelのようなバイナリファイルはプルリクエストやgit diffなどで差分を確認することができないため、誰も私がテンプレートを破壊していることに気付けなかった
  • 修正箇所が1セルだけだったことも油断につながってしまった

やりたいこと(ツールの目的)

  • Excelファイルの差分を確認したい
  • 値の差分だけでなく、値以外の要素(今回は名前付き範囲)の差分も見たい
  • コンソールで結果を表示し、CIに組み込めるようにしたい
  • Windows,Mac両方で差分を確認できるようにしたい

検討したツール

方眼Diff
変更点が視覚的にわかりやすかったが、値の差分しか見られなかったのとコンソール出力できなかったので使わなかった

Excelアドイン
Excelにアドインを追加することでExcelの差分を見ることができる。
値の差分だけでなく、名前付き範囲やフォントなどの値以外のものを比較することもできる。
コンソール出力ができないのでCI出力できないのと、Windowsでしか使えないという理由で落選(チームにmacユーザーがいた)

結局、既存のツールだけではやりたいことが実現できるものを見つけることができませんでした…

実際に作ったもの

Excel差分比較をするためにApach TikaとApach POIを使用して、git diffで差分を出力できるようにしました。

Apach TikaはPDFやWordファイルからテキストを取得することができるjarファイルで、Excelファイル(.xlsx)のセルの値を取得することにも対応しています。
しかし、セルの値しか取得できないので値以外を取得するためにApach POIというjavaのライブラリを使用してjarファイルを作っていきます。(全部POIで実装してもよかったが手間だったので値はTikaで取得するようにしました)

比較するもの 使うもの
セルの値 Apach Tika
値以外 (名前付き範囲,セルの色,フォントなど) Apach POI

Apach Tikaの準備

tika-app.X.X.X.jar (X.X.Xはバージョン)を公式サイトからダウンロードしたら準備OK！

Apach POIの準備

POIはjavaのライブラリなのでpom.xmlやbuild.gradleに追記していくと使えるようになります。

今回はmavenでexceldiffという名前の新しいプロジェクトを作成し、pom.xmlのdependenciesに以下の記述を追加しました。

pom.xml
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>5.2.3</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>5.2.3</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml-schemas</artifactId>
  <version>4.1.2</version>
</dependency>
<dependency>
  <groupId>org.apache.commons</groupId>
  <artifactId>commons-collections4</artifactId>
  <version>4.4</version>
</dependency>

jarファイルの作成

以下のコードをメインクラスとして実装し、JARファイルを作成します。

exceldiff.java
package com.example;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.List;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class exceldiff {

  public static void main(String[] args) throws IOException {
    if (args.length != 1) {
      System.out.println("Usage: java CustomExcelDiff <file-path>");
      return;
    }

    FileInputStream file = new FileInputStream(new File(args[0]));
    Workbook workbook = new XSSFWorkbook(file);

    // ワークブック内のすべてのシートをループ
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
      Sheet sheet = workbook.getSheetAt(i);
      System.out.println("Sheet: " + sheet.getSheetName());

      // 名前付き範囲をすべて取得
      List<? extends Name> name = workbook.getAllNames();
      name.forEach(
          (Name n) -> {
            String reference = n.getRefersToFormula(); //名前付き範囲の範囲を取得
            System.out.println("(" + reference + ") " + n.getNameName()); //名前付き範囲を出力

          }
      );

      // ここの内容を書き換えるとフォントや色などの他要素も取得することができる
      
    }
    workbook.close();
    file.close();
  }
}

次に以下のリンクを参考にjarファイル出力する(exceldiff.jarを作成)

git diff で差分を出力できるようにする

git diffをした際に比較対象がExcelファイル(.xlsx)であれば、tikaと今回作ったexceldiffを起動するようなshファイルを呼び出すように設定します。

まずgit管理しているリポジトリ内に.gitattributesファイルを作成し、次のように記述します。

.gitattribute
*.xlsx diff=openxlsx
*.XLSX diff=openxlsx

これでファイルの拡張子が.xlsxのものをgit diffで比較するときにgit config内のopenxlsxという設定を元にgit diffしてくれるようになります（設定は後述)

次にshファイルを作成します(exceldiff.sh)

exceldiff.sh
#!/bin/bash
cd `dirname $0`
java -Dfile.encoding=UTF-8 -cp './exceldiff.jar' com.example.exceldiff "$1"
java -Dfile.encoding=UTF-8 -jar './tika-app-2.9.2.jar' -t "$1"

'./exceldiff-1.0.jar'と'./tika-app-2.9.2.jar'に関してはjarファイルのある場所によって変えてください。

次に差分出力したいリポジトリでgit configの設定をします。

git config --local diff.openxlsx exceldiff.shのパス

ここまででgit diffでexcelファイルの差分比較する準備が整いました

実際に差分出力してみる

今回はコミットしているExcelファイルと現在のExcelファイルをgit diffで比較してみます

更新前(コミットしているファイル)は画像のようになっている
image.png

また、C1セルにはテスト1という名前付き範囲が設定されており、カーソルを合わせるとこんな感じにセル番号(C1)ではなくテスト1という表示になっている
image.png

更新後の方は次の画像の通りでA1とB1の値が書き換得ている
image.png

またC1についていた名前付き範囲も更新されており、テスト2としている
image.png

これをgit diffしてみる

diff --git a/demo.xlsx b/demo.xlsx
index f80dd05..f2d9db5 100644
--- a/demo.xlsx
+++ b/demo.xlsx
@@ -1,6 +1,6 @@
 Sheet: Sheet1
-(Sheet1!$C$1) テスト1 
+(Sheet1!$C$1) テスト2 
 Sheet1
-       更新前Excel コウシンマエ セルの値AAAA アタイ 
+       更新後Excel コウシン ゴ セルの値BBBB アタイ

このように出力される
中段辺りにある↓はPOIで作ったexceldiffによる出力です

 Sheet: Sheet1
-(Sheet1!$C$1) テスト1 
+(Sheet1!$C$1) テスト2

変更した名前付き範囲も差分確認できています。

下の方にある↓はTikaによる出力になります。(漢字にフリガナを付けてくれている)

 Sheet1
-       更新前Excel コウシンマエ セルの値AAAA アタイ 
+       更新後Excel コウシン ゴ セルの値BBBB アタイ

値の差分もちゃんと比較してくれてます

おわりに

最後まで読んでいただきありがとうございました！
Excelの名前付き範囲の差分を見たい…!というかなりニッチな内容の記事でしたがいかがでしたでしょうか?
実際に私たちのチームでは今回の差分比較ツールをCIに組み込んで、自動で差分確認を行うようにしています。

Excelの値の差分比較している記事は何個か見かけるのですが、名前付き範囲の差分比較をしている記事を見つけることができず😢
結局自分で作ることになってしまいましたが、良い方法を知っているという方がいれば教えていただきたいです...!

参考

今回の記事を書くにあたって調べなおしたら出てきた記事

CIに組み込んでプルリクにコメントしてくれるという機能を作っている記事

0
1
0

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
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?