読者です 読者をやめる 読者になる 読者になる

山下寛人オフィシャルブログ

オイシックス株式会社 執行役員 システム本部長 山下寛人の公式ブログです。

エクセル自動化検証

すごい作りこんだエクセルがありましてそれをシステム化しようとしています。共有フォルダ上にいろいろなファイルがあり複雑にファイル参照したりしています。
実装方法としてはエクセルを全部やめて作り直すという方法がまっとうなやり方ですがコストが膨大になってしまいます。今あるエクセルを生かしつつ手作業になっている部分を自動化する方針で検討しています。
エクセルを自動的にする方法はいろいろあります。エクセルのVBAJavaのPOI、PowerShellなど。どれでもできるかなと思っていましたが他のファイルの参照や式はちゃんと動くのか気になって検証してみました。

まずApache POI。ググってみるとやはりそのままでは値を書き換えても計算式は再計算されないもよう。FormulaEvaluaterというものがあり、それを使えば計算できるらしい。このようなコードになります。
CreationHelper helper = workbook.getCreationHelper();
FormulaEvaluator eval = helper.createFormulaEvaluator();
CellValue cellValue = eval.evaluate(cell);
System.out.println(cellValue.getNumberValue());
もしくは全部再計算というのもありました。
XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) wb);
しかしこれらはセル参照に対応していませんでした。こんな例外が出ます。
Exception in thread "main" java.lang.RuntimeException: not implemented yet
at org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook.getExternalSheetIndex(XSSFEvaluationWorkbook.java:127)
2012年の情報ですが、対応していないとのことでした。
poi-user mailing list archives

POIはエクセル自体は動かずファイルの中身だけを操作するものです。そこでエクセル自体をPowershellで動かしてみます。こんなようなコードでエクセルを操作できます。
$xls = New-Object -ComObject Excel.Application
$book = $xls.Workbooks.Open("poi-test1.xlsm")
$sheet = $book.Worksheets.Item("Sheet1")
Write-Output $sheet.Cells.Range("B5").Text
$book.Save()
$xls.Quit()
$xls = $null
[GC]::Collect()
B5には他のファイルを参照する式が入っています。これでも他のシートを参照している部分は他のシートを変更しても反映されません。

エクセルを画面に出してみます。このようにします。
$xls.Visible = $True
シートを開いたときに、他のシートを参照していますが更新しますか、というダイアログが出るようになりました。このダイアログが出てしまうと自動処理がストップしてしまいます。しかしこれがクリアできればできそうです。このダイアログは参照元のファイルを開いている状態だと出ずに値が更新されます。なので先に開いてみます。見事、できました。画面を表示しているかどうかは関係ないかもしれないのでvisibleをtrueにしないで試してみます。これでもできました。つまり参照元のファイルを先に開いておくというのがポイントのようです。

もう少し調べてみます。要するに外部参照の値を更新するかどうかのダイアログが邪魔していたのが更新されなかった原因では?このダイアログを出さないようにするには先に参照元のファイルを開いておくのが1つですが、エクセルやファイルの設定で出さないようにする方法もありそうです。調べてみるとありました。エクセル2010の場合。
ファイル → オプション → 詳細設定 → 全般の中の「リンクの自動更新前にメッセージを表示する」
このチェックボックスをオフにします。これで無事もともとのプログラムで外部参照の値を反映させることができました。