技術的な話

【Python】openpyxlでExcelを出力した際に数式の計算結果が取得できない問題 - 原因編

ExcelをPythonから弄ることはたまにあります。無理矢理これまでのExcel業務を自動化しようとした時とかね…。
PythonでExcelを操作するにはいくつか代表的なライブラリがあります。

いずれのライブラリも現在（2022/02時点）でもメンテナンスされている様子です。

なお、xlwingsはWindows or Macのみ対応しており、Linuxは非対応です。opensource版は無償で使用できますが、xwings PROなるものもあり、別途有償のライセンスが必要です。諸々のサポートが付いていたり、高級な機能があったりするのでどちらかというとエンタープライズ向けなのかなといった印象です。

この辺は試してみて別途纏めようかと思います。

話を本題に戻しますが私はDocker（Ubuntuベース）で開発していたので、openpyxlを使用していました。その時に遭遇した問題を記事にしていこうかと思います。

原因の解説等をしていたら随分と長めの文章になりました。

手っ取り早く解決方法を知りたい方は目次から飛んでください。

環境

今回はJupyterのイメージを使っていました。

  • Docker 20.10.11
  • イメージ jupyter/datascience-notebook
  • Python 3.8
  • openpyxl 3.0.8

問題の現象

Excelの数値が読み取れない

これが今回の問題になります。

なーんだカンタンじゃん。Excelファイルを読み込む時にあのオプションが足りてないんでしょ。

import openpyxl as px

# これを
wb = px.load_workbook('<path/to/excel>')
↓
# こうする
wb = px.load_workbook('<path/to/excel>', data_only=True)

これで解消出来れば良かったのですが、そうもいきませんでした。

ちょっと今回やろうとしていたことが特殊であったので別の問題がありました。

今回やろうとしていたこと

説明すると下記になります。

  1. Excelのsheet1にpandas等でCSVデータを出力する。
  2. Excelのsheet2に予め埋め込まれた計算式でsheet1の値を拾って計算する。
  3. sheet2の値を読み取ってPythonで処理を行う。

みたいな流れです。3.の箇所で値が拾えない問題が発生しています。

なんか周りくどいことをやっているなぁと思った方！その通りです。

ただ、2.の計算式をPythonで実装しようと思ったらかなり時間と労力が発生しそうであったので極力Excelの力に頼りたかったんです（言い訳）。

言葉だとちょっと分かりずらいので一応図を含めて改めて説明すると以下のような感じです。

こんな感じでsheet1にデータを出力します（pandas等で出力）。

sheet2で数式を使用して計算します。

sheet1の値を参照しつつ、この例では適当な数式を入れています。

値が入れ替わっているのはわざとです。

このsheet2の値をopenpyxlで取得しようとすると。

値が全てNoneで返ってきます。はて。
ちなみに以下のようなコードで値を取得しました。

# Excelファイル読み込み
wb = px.load_workbook('<path/to/excel>', data_only=True)

# Sheet2を取得
ws = wb['Sheet2']

rows = []

# Sheet2の対象セルを走査
for row in ws["B3:E7"] :
   for col in row :
       # something to do 
       print(f'value is {col.value}')

作成したExcelを開いてみる

おかしいなと思いながら今回処理結果が反映されたExcelを開くと問題無くsheet1に値が表示されます。なのでそもそも値が設定されていないとか、自身の勘違いという訳でも無さそうです。

なんでだろう。

原因と対策

原因について

今回の現象の原因はExcelの仕様にあります。

openpyxlのload_workbookを使用していると分かる通り、セルの値は２つ（数式、値）取得可能です。

# このdata_onlyを
# Trueに設定：セルの値が取得できる
# Falseに設定：セルの数式が取得できる
wb = px.load_workbook('<path/to/excel>', data_only=True)

今回取得したいのは値なのですが、この値はファイルをExcelで開かないと再評価されない様子です。openpyxlで開いて保存しても一生ここの値が埋まらないのです。

これはExcelファイルの中身を見てみると理解が深まると思います。次はそこを解説していきます。

Excelの定義ファイルを見てみる

事前に7zip等の解凍ソフトが必要なります。

Excelを7zip経由で開いてみると、定義ファイル等が参照できます。

最初に見えるのがこんなファイルです。

xl\worksheets以下に進むと各シートの定義ファイルが参照出来ます。

sheet2.xmlが数式が入力されているシートの定義ファイルになります。

次に数式が定義されているsheet2.xmlのExcelで開く前とExcelで開いた後の変化を見ていきます。

まずはExcelで開く前の定義ファイルです。

中段に各セルの定義された箇所を抜粋しています。見ると分かる通り<f></f>で数式は定義されていますが、<v></v>に値が入っていません。

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
   xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
   xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac xr xr2 xr3"
   xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"
   xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision"
   xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2"
   xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xr:uid="{EF948152-3B65-4277-83C4-3DF2E0939C4C}">
   <dimension ref="B2:E7"/>
   (中略)
   <sheetFormatPr defaultRowHeight="18.75" x14ac:dyDescent="0.4"/>
   <sheetData>
       (中略)
       <row r="3" spans="2:5" x14ac:dyDescent="0.4">
           <c r="B3" s="1">
               <f>IF(Sheet1!A1<>"",Sheet1!A1,"")</f>
               </v>
           </c>
           <c r="C3" s="1">
               <f>IF(Sheet1!C1<>"",Sheet1!C1,"")</f>
               </v>
           </c>
           <c r="D3" s="1">
               <f>IF(Sheet1!D1<>"",Sheet1!D1,"")</f>
               </v>
           </c>
           <c r="E3" s="1">
               <f>IF(Sheet1!B1<>"",Sheet1!B1,"")</f>
               </v>
           </c>
       </row>
       (中略)
   </sheetData>
   <mergeCells count="1">
       <mergeCell ref="B2:E2"/>
   </mergeCells>
   <phoneticPr fontId="1"/>
   <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>

次はExcelで開いた後に保存処理をしたsheet2.xmlです。

今度は<v></v>に値が入っていることが分かります。

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
   xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
   xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac xr xr2 xr3"
   xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"
   xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision"
   xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2"
   xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xr:uid="{EF948152-3B65-4277-83C4-3DF2E0939C4C}">
   <dimension ref="B2:E7"/>
   (中略)
   <sheetFormatPr defaultRowHeight="18.75" x14ac:dyDescent="0.4"/>
   <sheetData>
       (中略)
       <row r="3" spans="2:5" x14ac:dyDescent="0.4">
           <c r="B3" s="1">
               <f>IF(Sheet1!A1<>"",Sheet1!A1,"")</f>
               <v>1</v>
           </c>
           <c r="C3" s="1">
               <f>IF(Sheet1!C1<>"",Sheet1!C1,"")</f>
               <v>3</v>
           </c>
           <c r="D3" s="1">
               <f>IF(Sheet1!D1<>"",Sheet1!D1,"")</f>
               <v>4</v>
           </c>
           <c r="E3" s="1">
               <f>IF(Sheet1!B1<>"",Sheet1!B1,"")</f>
               <v>2</v>
           </c>
       </row>
       (中略)
   </sheetData>
   <mergeCells count="1">
       <mergeCell ref="B2:E2"/>
   </mergeCells>
   <phoneticPr fontId="1"/>
   <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>

このことから分かることは下記2点かと思います。

  1. ExcelファイルはExcelで開いた時に評価されて値が保存される。
  2. openpyxlのload_workbookはオプションに応じて下記の値を取得する。
    data_only=True：定義ファイルの<v></v>の値
    data_only=False：定義ファイルの<f></f>の値

1.は普段意識していませんが当然と言えば当然ですね。

以上のExcelの仕様を踏まえて対策を講じていきます。

対策について

考えられる対策は下記になるかと思います。

  1. 数式は読み取れるのでPythonで読み取ってそれを元に計算する。
    簡単な数式だけであれば実現可能そうではあるが複雑な計算式だと非現実的。
  2. そもそもの設計を見直す。
    漢にはどうしてもこれでやらねばならぬ時があるのです。
  3. Python or シェルスクリプト経由でExcelを起動し、対象ファイルを開いて保存し、そのファイルを読み取る。
    比較的実現出来そう。

ということでDocker上でExcel起動しちゃえばｲｲｼﾞｬﾝ！と思って取り掛かるも致命的なことに気付きます。

あれ？Microsoft Officeのライセンスもう無いぞ？というかこんなちょっとした機能のためにDocker上にOfficeをインストールするのも正直気が引ける。

そんな時に僕らの味方LibreOfficeが役に立ちます。

具体的な対策

LibreOfficeを使用してExcelファイルを開いて無理矢理値を反映させようと思います。

ちょっと長くなりそうなので別記事でまとめます。

https://buzz-server.com/tech/python-openpyxl-no-value-measures/

-技術的な話
-, , , ,