Excel 2019 for MacにWebクエリ経由でデータを渡してみる

Excel 2019 for Mac には外部データを取り込む手段のひとつに Web クエリがあります。

少なくとも Excel 2000 の頃には既に存在していた機能で、大まかに言えば Web ベージにある <table> 要素を抽出して Excel シート上に転記するものです。

流行り廃りが激しい Web 系の機能なのに 20 年以上も存在してるなんて！

1. 思ったこと

現存してるのは良いのですが、Excel 2019 for Mac ではどうなのでしょう。

Excel にデータ提供する側としては、Power Query (Get & Transform) 以外の手段にも興味があります。そもそも手段は TPO に合わせるので、いくつあっても困りません。

当然と言えば当然なのですが、Excel for Mac の場合、Windows 版で問題ない VBA が動作しない場合があります。特に ActiveX が絡む CreateObject() は絶望的です。
また、WEBSERVICE(), FILTERXML()等のWeb 系関数は値を返しません。

とにかく Windows 版での当たり前が Mac では通用しない事は多々あります。
なので、過去の経験も踏まえて検証してみました。

....

例えば、このようなテーブルが Web サイトの表示ページ内にあるとします。

番号	文字列	日付	数値
1	りんご🍎	2018-01-03	100
22	みかん🍊	2021-11-21	1,200
333	キウイ🥝	2022-04-10	12,3450.25

Web クエリを利用すれば、この内容をそのまま Excel シート上に展開できます。

基本的にインポート系のデータ取り込みは初回限りですが、Web クエリで取り込んだデータは再読み込みが可能です。常に最新のデータを表示できるので、日々変化するデータを照会する手段としては便利だと思います。

Web クエリの実行時にテーブルと見なすのは <table> 要素だけです。
今時の HTML ドキュメントでは CSS で <div>, <lu>, <li> タグ等のスタイルを操作してテーブルを表現する構造もありますが、その場合は Excel に読み込む事ができません。

....

一方、Excel にデータを提供する側の視点で考えると「 http レスポンスに <table> 要素を返せば、後は Excel が良いようにしてくれる」機能にも見えます。

つまりは、独自にデータ照会用の http サーバを用意すれば、Excel には Web クエリを経由してデータ提供が可能になると。

2. 環境を整える

実際に検証用の環境を構築してみます。
※使用マシン: M1 Mac mini (macOS Big Sur 11.6.5)

今回はローカルサーバを Node.js で構築します。

$ node -v
v17.8.0

検証のため http レスポンスを返すだけの簡易的な http サーバを用意します。

任意のディレクトリに次のファイルを作成します。

・ファイル名: main.js

const http = require("http");
const port = process.env.PORT || 3000;

const server = http.createServer((_request, response) => {
    response.writeHead(200, {
        "Content-Type": "text/html; charset=utf-8"
    });
    const msg = `
<table>
<thead>
<tr>
<th style="background-color: #EAD9FF; text-align: right">番号</th>
<th>文字列</th>
<th>日付</th>
<th>数値</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>りんご🍎</td>
<td>2018-01-03</td>
<td>100</td>
</tr>
<tr>
<td>22</td>
<td>みかん🍊</td>
<td>2021-11-21</td>
<td>1,200</td>
</tr>
<tr>
<td>333</td>
<td>キウイ🥝</td>
<td>2022-04-10</td>
<td>12,3450.25</td>
</tr>
</tbody>
</table>
`;
    response.end(msg);
    console.log("送ったよ");
});

server.listen(port);
console.log(`待機中 (PORT: ${port})`);

ここでは Excel 側で適切に処理されるであろう <table> 要素だけを生文字列リテラルとして記述しています。

敢えて <table> 要素に絞ってますが、正規の HTML ドキュメントを貼り付けても、もちろん検証可能です。

....

ローカルサーバを起動します。

$ node main.js
待機中 (PORT: 3000)

この状態で curl コマンド等からhttp://localhost:3000/にアクセスすると、前述した <table> 要素を構成する部分のテキストだけを返却します。

正規の HTML ドキュメントではありませんが、一応 Safari, Chrome, Firefox でアクセスしてみたところ、それなりに表示されました。

3. Web クエリの実行

前述のローカルサーバが返す内容を Excel 側に取り込んでみます。

まずはデータ取得用の iqy ファイルを準備します。

・ファイル名: my_web_query.iqy

WEB
1
http://localhost:3000/

Selection=1
Formatting=HTML
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=True
DisableDateRecognition=False
DisableRedirections=False

データの照会先はhttp://localhost:3000/です。

取得するデータを HTML 形式として解釈し、最初の <table> 要素を対象とします。
その他、各セルの値についてはパラメータに応じて処理します。

....

この iqy ファイルを Excel 2019 for Mac のメニューバーにある「データ」-「外部データ取り込み」-「Web クエリの実行...」から表示される画面で選択し、そのまま Excel に取り込みます。

※この操作については、こちらのサイトが詳しいです。

www.wanichan.com

そして、取り込んだ結果がこちらです。

f:id:infinity_volts:20220401201805p:plain

UTF-8 エンコードの <table> 要素を文字化けせずに取り込む事ができました。

日付と数値は Excel で扱える書式設定に変換されるので、フォーマットから好みの書式に変更できます。

ちなみに、<table> 要素の書式については、いくつか制限があります。

CSS, JavaScript の効果は反映されない。
<style> 要素の内容は処理されない。
<table>, <tr>, <th>, <td> タグ内の style 属性は処理される。
非推奨になった align, bgcolor 等の属性も処理される。

また、データを再読み込みする際にも注意すべき点があります。

<th>, <td> 要素内のテキストで改行 <br /> は縦方向にセル分割される。
<br /> の副作用で、同一 <tr> 要素の他項目は縦方向にセル統合される。

過度な期待はせず、Excel に取り込んでから、Excel の範疇で対処するのが無難ですね。

....

ここで、一旦ローカルサーバを停止し、main.js 中の <table> 要素の内容を変更します。

再度ローカルサーバを起動して、Excel のリボンの「すべて更新」をクリックするとデータが最新に置き換わる様子を確認できます。

4. あとがき

技術の変革が止まない Web 系の機能ですが、Excel 2019 for Mac でも十全に使えました。これなら、Excel が直接接続できないデータへのコネクタを作成できます。

少し心配なのは、最近 iqy ファイルが添付されているメールはウイルス扱いなので、それなりの配慮が求められます。風評被害で Web クエリそのものを拒絶されると辛いです。

....

そもそも Power Query (Get & Transform) はどこに行ったのよ。

docs.microsoft.com

現在、Excel 2019 for Mac 16.59 ですが、肝心の Power Query が見当たりません。

私の記憶違いでなければ、2022.1 にリリースされた Excel 2019 for Mac 16.57 で、手ずから Power Query を試したはずなのです。
その際、SQLServer への接続を確認して安心してたのに！

見当たらないのは致し方ないので、大人しく次のリリースを待つ事にします。

刹那（せつな）の瞬き

Willkömmen! Ich heiße Setsuna. Haben Sie etwas Zeit für mich?

Excel 2019 for MacにWebクエリ経由でデータを渡してみる

1. 思ったこと

2. 環境を整える

3. Web クエリの実行

4. あとがき