Wednesday, September 14, 2022

Excel に配置されたチェックボックスの値をスクリプトなどから取得したい（備忘録）

まずそもそもの話、 Excel のチェックボックスのチェック状況を特定のセルにリンクして記録する機能(Cell link)があらかじめ備わっているので、それを使うべき。そうすれば、このエントリーで書いているようなことを行なわなくても普通にPOIとか node.js の xlsx 系ライブラリで難なくその値を読むことができます。

the excel sheet with iphone14 buy or not checkboxes

さて今回は、そのようなセルリンク機能を使わないで、チェックボックスを配置したエクセルデータをスクリプトで直接読むことはできるのか？という話。 Cell link 機能により特定のセルにチェックボックスの状況（TRUE/FALSE）を書きだしていれば、スクリプトでそのセルを読みとるだけの話。しかし、そうでない場合に方法があるのか？

結論としては、このスタックオーバーフローの回答です。これを読んだだけでわかるひとは、以後の説明は読む必要ないです。

今回は冒頭の画像のエクセルデータを使って具体的に説明します。

Step1 展開

iphone14-buy-or-note.xlsx を展開します。 xlsx ファイルは単なる zip ファイルなので、unzip します。

$ unzip iphone14-buy-or-note.xlsx

カレントディレクトリに iphone14-buy-or-note.xlsx の中身が展開されます。展開されて出てきたファイルは以下の内容になります。

Archive:  ./iphone14-buy-or-not.xlsx
  inflating: [Content_Types].xml     
  inflating: _rels/.rels             
  inflating: xl/_rels/workbook.xml.rels  
  inflating: xl/workbook.xml         
  inflating: xl/sharedStrings.xml    
  inflating: xl/drawings/drawing1.xml  
  inflating: xl/drawings/vmlDrawing1.vml  
  inflating: xl/styles.xml           
  inflating: xl/worksheets/_rels/sheet1.xml.rels  
  inflating: xl/worksheets/sheet1.xml  
  inflating: xl/theme/theme1.xml     
  inflating: docProps/app.xml        
  inflating: docProps/core.xml       
  inflating: xl/ctrlProps/ctrlProp3.xml  
  inflating: xl/ctrlProps/ctrlProp2.xml  
  inflating: xl/ctrlProps/ctrlProp1.xml

Step2 drawing1.xml を見る

「レビューを見てから考える」という文字列を設定したチェックボックスがどこに記述されているか探します。

$ find . -name "*.xml" | xargs grep "レビューを見てから考える" -l
./xl/drawings/drawing1.xml

この drawing1.xml の該当箇所 mc:Choice 要素を調べると以下のようになっています。

<mc:Choice xmlns:a14="http://schemas.microsoft.com/office/drawing/2010/main" Requires="a14">
  <xdr:twoCellAnchor editAs="oneCell">
    <xdr:from>...</xdr:from>
    <xdr:to>...</xdr:to>
    <xdr:sp macro="" textlink="">
      <xdr:nvSpPr>
        <xdr:cNvPr id="1026" name="Check Box 2" hidden="1">...</xdr:cNvPr>
        <xdr:cNvSpPr/>
      </xdr:nvSpPr>
      <xdr:spPr bwMode="auto">...</xdr:spPr>
      <xdr:txBody>
        <a:bodyPr vertOverflow="clip" wrap="square" lIns="27432" tIns="22860" rIns="0" bIns="22860" anchor="ctr" upright="1"/>
        <a:lstStyle/>
        <a:p>
          <a:pPr algn="l" rtl="0"><a:defRPr sz="1000"/></a:pPr>
          <a:r>
            <a:rPr lang="en-US" sz="1200" b="0" i="0" u="none" strike="noStrike" baseline="0">
              <a:solidFill><a:srgbClr val="000000"/></a:solidFill>
              <a:latin typeface="Calibri" charset="0"/>
              <a:cs typeface="Calibri" charset="0"/>
            </a:rPr>
            <a:t>レビューを見てから考える</a:t>
          </a:r>
        </a:p>
      </xdr:txBody>
    </xdr:sp>
    <xdr:clientData/>
  </xdr:twoCellAnchor>
</mc:Choice>

ここでポイントは:

        <xdr:cNvPr id="1026" name="Check Box 2" hidden="1">...</xdr:cNvPr>

です。「レビューを見てから考える」に関連付けされた id は 1026 です。

Step3 sheet1.xml を見る

次に 1026 を含む xml ファイルを検索:

$ find . -name "*.xml" | xargs grep 1026 -l
./xl/worksheets/sheet1.xml
./xl/drawings/drawing1.xml

./xl/worksheets/sheet1.xml に含まれています。このファイルを開いて、1026 で検索すると、以下の要素になります。

<control shapeId="1026" r:id="rId4" name="Check Box 2">

ここでのポイントは rId4 です。

Step4 sheet1.xml.rels を見る

この rId4 を使って、rels ファイルを検索します。

$ find . -name "*.rels" | xargs grep rId4 -l
./sheet1.xml.rels
./xl/worksheets/_rels/sheet1.xml.rels
./xl/_rels/workbook.xml.rels

./xl/worksheets/_rels/sheet1.xml.rels を開き rId4 で検索します。

<Relationship
    Id="rId4"
    Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/ctrlProp"
    Target="../ctrlProps/ctrlProp2.xml"/>

これで rId4 は ../ctrlProps/ctrlProp2.xml と関連付けされていることがわかります。

Step4 ctrlProp2.xml を見る

../ctrlProps/ctrlProp2.xml の中身は:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<formControlPr
    xmlns="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"
    objectType="CheckBox"
    checked="Checked"
    lockText="1" noThreeD="1"/>

checked="Checked" になっています。つまりチェックされていると。

まとめ

以上のステップを経て、「レビューを見てから考える」がチェックされていることを調べることができました。同様に「買う」「買わない」を調べると、チェックされていないことも把握できます。

これらの処理が POI などの xlsx ライブラリに入っていれば助かるのですが、ちょっと調べた限りではわかりませんでした。

こんな大変なことをしなくても Cell link 機能でチェックボックスのチェック状況をどこかのセルに反映しておけば済む話。これをスクリプトで実装した上で、間違いは許されない！みたいな世界は一体どんな拷問なんだという感想しかない。

もっとも、こんなデータを手作業で転記する作業が1000件とかあるならば、この流れをスクリプトで組む価値もなくはないでしょうけれど。だったら最初からエクセルのエントリーシートまともに設計しとけよ。