Rubyでエクセルを出力した際に数式が計算されない問題の解決方法

リンクアンドモチベーション Advent Calendar 2024の17日目を担当します

今回はRubyを使ったエクセル操作で、発生した問題の解決策を共有します。

背景

最近の業務で、Rake Taskを使ってテンプレートのエクセルファイルから150以上のファイルを生成する対応をしました。
スクリプトを実行し、「よし、150ファイル完成！」と思ったのも束の間。確認してみると…

「あれ？数式が再計算されてない！？😱」

手動でVBAを使い再計算はできたものの、150ファイルすべてに手作業で対応するのは非効率かつミスのリスクが高い状況。

「どうにか全てのファイルを手作業せずに再計算させる方法はないか？」と考え、解決策を探りました。

ちなみにVBAは、
Visual Basic Editorを開いて以下を実行すれば、再計算させることができます。

Sub RecalculateAll()
  Application.CalculateFull
End Sub

ただ、1ファイル1ファイルを手動で実行する必要があり、
大量のファイルを再計算する際は不向きです。

問題点: rubyXLでエクセルにデータを流し込むと数式が評価されない

rubyXLでエクセルにデータを突っ込むだけだと、セルに設定されている数式が再計算されないことがあります。

特にプログラムから直接エクセルファイルを編集した場合、この問題が起きるみたいです。

解決策: LibreOfficeで再評価するコマンドを投げる

LibreOfficeを活用することで、数式の再計算を自動化する方法を考えました。
LibreOfficeのCLIを使えば、ファイルを一括で処理するのも簡単です。

ポイント:

• RubyからLibreOfficeを呼び出して、数式を再計算させています。
• ファイルが破損することも考慮し、バックアップも取るようにしています。

以下が作成したコードです。

recalculate_excel.rb

require 'fileutils'

class ExcelRecalculator
  def initialize(folder_path)
    @folder_path = folder_path
  end

  def recalculate_files(pattern = "*.xlsx")
    files = Dir.glob(File.join(@folder_path, pattern))

    if files.empty?
      puts "Excelファイルが #{@folder_path} に見つかりません"
      return
    end

    files.each do |file|
      puts "処理中: #{file}"
      begin
        # バックアップを作成
        backup_file = "#{file}.bak"
        FileUtils.cp(file, backup_file)

        # LibreOfficeのコマンド
        command = "soffice --headless --calc --convert-to xlsx:\"Office Open XML Spreadsheet\" --outdir \"#{File.dirname(file)}\" \"#{file}\""
        result = system(command)

        if result
          FileUtils.rm(backup_file)
          puts "再計算成功: #{file}"
        else
          puts "ファイル処理エラー: #{file}"
          FileUtils.mv(backup_file, file)
        end
      rescue => e
        puts "エラー: #{e.message}"
        FileUtils.mv(backup_file, file) if File.exist?(backup_file)
      end
    end
  end
end

folder_path = "excel"  # Excelファイルがあるフォルダのパス
calculator = ExcelRecalculator.new(folder_path)
calculator.recalculate_files

*ruby 3.1.4, LibreOffice 24.8.3.2のバージョンで動作確認済み

LibreOfficeの導入手順

macOSの場合は、以下のコマンドでインストールしてください。

$ brew install libreoffice

コマンドの説明

コード内で使用しているコマンド：

soffice --headless --calc --convert-to xlsx:"Office Open XML Spreadsheet" --outdir "#{File.dirname(file)}" "#{file}"

LibreOfficeのオプションの意味は以下の通りです。

• --headless: GUIを使わずに非表示モードで実行
• --calc: LibreOffice Calc（スプレッドシート）を指定
• --convert-to xlsx:"Office Open XML Spreadsheet": ファイルをxlsx形式に変換。フォーマットとしてOffice Open XML Spreadsheetを指定。
• --outdir "#{File.dirname(file)}": 処理後のファイルを元のディレクトリに出力。
• "#{file}": 処理対象のファイルを指定。

このコマンドにより、LibreOfficeが指定されたExcelファイルを読み込み、再計算を行い、指定した形式で保存します。

使い方

$ ruby recalculate_excel.rb

を実行することで、
excelディレクトリ配下のすべてのエクセルファイルに対して処理を実行できます。
各ファイルはバックアップが作成されたうえで、再計算後に上書き保存されます。

まとめ

VBAの手作業に頼らず、RubyとLibreOfficeを使うことで大量のエクセルファイルを再計算させることができました。

昨今、いろんな便利ツールが世に溢れていますが、
エクセルファイルで誰かに共有しなければならないという場面はあるかと思います。

そういった時に、この方法は手作業のリスク軽減や時間を削減できるので、ぜひ使ってみてくださいm(_ _)m