【OutSystems】Advanced Excel 使用時に入力規則が解除されてしまう

OutSystems の [Advanced Excel](https://www.outsystems.com/forge/component-overview/355/advanced-excel-o11) で Excel ファイルを出力したところ、一部セルの入力規則が解除されてしまう事象が発生した。
原因と回避方法について記載する。

# 対象
- OutSystems 11 で forge「Advanced Excel」を使用している方

# 事象
- Advanced Excel にて Excel ファイル出力時、一部セル(列)の入力規則が解除されてしまう

# 発生条件
1. 同じ条件での入力規則が複数のセル範囲にある
例) A列及びC列が同じ入力規則になっている
2. Row_Insert アクションで行の追加を行っている。
なお、追加した行だけでなく、対象列すべての行に対する入力規則が解除されてしまう。
:::note warn
※ Row_Insert 以外でも発生する可能性あり。
:::

# 原因
Excel ファイル(xlsx)を解凍した際、入力規則(formula1)の適用セル(sqref)が複数の範囲になっていると事象が発生する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/cd0bf367-f868-6651-2a66-b4e5ade76327.png)

# 回避方法
入力規則(formula1)の適用セル(sqref)が単一の範囲になるようにする。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/7e90c150-99f2-3784-223b-9f84c62dfc96.png)

# 再現手順と原因

## 使用するツールやアカウントなど
- OutSystems 11
- Advanced Excel (記載時のバージョンは 2.2.17)
- Excel for the web (Microsoft 365 無料版) ※通常のExcelでも可

## 再現手順
### テンプレート Excel の作成
A列とB列に入力規則を設定
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/b7eb52d0-e3c4-4e15-dbb0-397738f500a3.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/55ed8f4a-127d-71cb-4dc2-a0ccb790b516.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/20e61759-ac7c-26f9-3634-ca21405228fc.png)

### Action の作成
Resouces に、テンプレートファイルをアップロード。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/cd865a0e-c914-ca1e-f3f8-1d6cf0f181da.png)

テンプレートファイルを読み込み、 Row_Insert で 10 行目に行を追加。
このバイナリデータをダウンロードする処理を作る（記載省略）。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/4d3ccfbf-e0b6-cf6d-b5ce-4f2fb4dbb5dd.png)

Action を実行し、事象が再現しないことを確認（10行目に行が追加され、A列とB列の入力規則がある）。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/b4b9ee40-a5a0-8185-040c-f1d64521a4f7.png)

テンプレートファイルのA列をC～E列へコピー＆ペーストし、 Resouces 上のファイルも更新。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/a5795c10-475b-9006-e16c-2b90ed2d1e2b.png)

再度 Action を実行し、事象が再現することを確認。
（10行目に行が追加され、A列とB列の入力規則があるが、C～E列の入力規則がない。）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/7f035771-1108-981e-8a9b-da3106243c1e.png)

## 原因（仮説と検証）
### 仮説
問題を切り分けていった結果、 Excel ファイルの内容（ワークシートの XML）によって事象が発生すると推測した。
xlsx ファイルは zip ファイルなので、解凍することでワークシートの XML ファイルを確認することができる。
上記、再現手順の xlsx を解凍すると入力規則の適用セルが「A2:A20 C2:E20」と指定されていることが確認できる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/39ec9dba-2f62-b469-3db3-9ae9bdd1e536.png)
『セル範囲 (sqref 属性) において、半角ブランクで区切られた範囲は無視され、最初の範囲しか認識されていないのでは？』という仮説を立て検証を行う。

### 検証
A列をコピー＆ペーストせずに、C～E列のそれぞれに対しA列と同様の入力規則を設定する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/83c4318b-7396-c1ce-eef4-e53013e42dcf.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/c8880065-17fb-cf1e-e6aa-2c919f0fdd0a.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/40f0aa4a-b040-5764-bf05-af3e25c280b5.png)

Resouces のファイルを更新し、再度 Action を実行すると、C列に入力規則があるがD～E列は入力規則がなくなっている。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/5edcbb53-1d15-2ea1-d271-65975eb45b4f.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/4cbebad5-10cd-101d-9fe6-56a0afee57c3.png)

ワークシートの XML は以下のようになっている。
A列をコピー＆ペーストしなかったことにより、A列の入力規則とC～E列の入力規則は別々の定義となっているが、C～E列は１つの sqref 属性内で定義されていることがわかる。
A～C列は入力規則があり、D～E列は入力規則が解除されていたことから、この時点で仮説が正しいと考えられる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/44ef27a6-eeda-758c-60d5-e9a6fa149357.png)

### 回避方法
各列に対し、他と重複しない入力規則を設定した後、正しい入力規則に設定する。
例) C列に「1」、D列に「2」、E列に「3」のような他と重複しない入力規則を一度設定した後、C～E列にA列と同じ入力規則(A,B,C)を設定する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/f4e34a3c-8926-ca5e-c829-e46b43b57525.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/fae715b6-9074-0b7f-eb6c-385d6a5b473e.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/d647ce2b-9290-309f-43c0-ba73fb507789.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/9a413cbb-b9f2-eaa6-71ad-46b6b7579c58.png)

Resouces のファイルを更新し、再度 Action を実行すると、A～E列の入力規則が残っていることが確認できる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/f8272b3b-7fab-c33f-3c38-48b1d430416c.png)

ワークシートの XML を確認すると、各 sqref 属性には１つの範囲しか指定されていないことも確認できる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3804213/8551dd4f-aba3-c2f5-3526-a65a876d9db2.png)

:::note warn
XML 出力内容が原因であることから、１度回避しても再度テンプレートファイルを更新した際に事象が再現してしまう危険性あり。
Excel のバージョン等によっても結果が変わる可能性あり。
このことから、 Advanced Excel のアクションを使って入力規則を設定する等を検討するのもアリ！
:::

# あとがき
原因が原因なだけに、入力規則以外にも『ここのセルは正しいのに、こっちのセルはおかしい』なんて事象が発生したら XML の内容を確認してみるとヒントがあるかも！

# さいごに
株式会社メディアテックでは絶賛 [開発メンバを募集中](https://www.mediatech.jp/career) です。
ローコード開発だけではなく、RPAやBIエンジニア諸々多数の募集をしておりますので、みなさまのご応募をお待ちしております。