Node.jsのfetch()でデータを取得して、Excel 2019 for Macに渡してみる
前回、外部データ取り込みに Web クエリを利用しました。
この記事では、http リクエストに対して、ソースコード内に記述した <table> 要素をデータとして返します。しかし、いつも同じ内容では物足りないです。
実際の用途を考慮すると、Web API というか RESTful API みたいなサービスにアクセスして JSON データを得て、その結果を加工して Excel に渡したいものです。
....
その準備として、改めて Node.js で Fetch 関連のパッケージを確認していたところ、Node.js v17.5 から fetch() が試験的実装として利用できるとの情報を得ました。
元々 node-fetch で対処するつもりでしたが、早速これを試したくなりました。
試してみた
実在する Web API を前提にすると関係各位に迷惑をかけそうなので、ここでは私が用意したサーバ環境で話を進めます。
(1) JSON 配列を返すサーバ
仮にhttp://example.jp/api/v1/membersにアクセスすると、データベースから名簿を抽出して JSON 配列で返却するサービスがあるとします。
実際に返される JSON 配列はこんな感じです。
[ { "番号": 304, "氏名": "長田 隆次", "誕生日": "1991/05/25" }, { "番号": 110, "氏名": "岸本 龍也", "誕生日": "1989/11/06" }, { "番号": 307, "氏名": "中井 雄樹", "誕生日": "1984/02/29" }, { "番号": 105, "氏名": "江口 美奈", "誕生日": "1979/06/23" } ]
この JSON 配列をテーブル形式に戻したレイアウトがこちらです。
| 番号 | 氏名 | 誕生日 |
|---|---|---|
| 304 | 長田 隆次 | 1991/05/25 |
| 110 | 岸本 龍也 | 1989/11/06 |
| 307 | 中井 雄樹 | 1984/02/29 |
| 105 | 江口 美奈 | 1979/06/23 |
あくまで試用なので、シンプルなものにしてみました。
このデータを Excel に渡す事が目標になります。
(2) fetch()で取得したデータを <table> 要素に加工するローカルサーバ
検証用の Node.js 環境は前回と同様です。
※使用マシン: M1 Mac mini (macOS Big Sur 11.6.5)
$ node -v v17.8.0
- http リクエストを受ける。
- Node.js に実装された fetch() で前述のサーバにアクセス。
- fetch() で取得した JSON 配列を <table> 要素に加工。
- その結果を http レスポンスで返す。
そんなローカルサーバを用意します。
....
任意のディレクトリに次のファイルを作成します。
・ファイル名: main.js
const http = require('http'); const port = process.env.PORT || 3000; const server = http.createServer(async (_request, response) => { try { // JSON配列を取得 const res = await fetch("http://example.jp/api/v1/members"); const members = await res.json(); console.log(members); // ExcelのWebクエリで処理できる形式にして送信 response.writeHead(200, { "Content-Type": "text/html; charset=utf-8" }); const msg = await toHtmlTable(members); response.end(msg); console.log(`送ったよ: ${Date(Date.now())}`); } catch (error) { console.error(error); } }); server.listen(port); console.log(`待機中 (PORT: ${port})`); async function toHtmlTable(json_array) { if (json_array.length > 0) { // カラム名 const columns = Object.keys(json_array[0]); const thead = []; thead.push("<thead>"); thead.push("<tr>"); for (const col_name of columns) { thead.push(`<th>${col_name}</th>`); } thead.push("</tr>"); thead.push("</thead>"); // ロー const tbody = []; tbody.push("<tbody>"); for (const row of json_array) { const td = []; for (const col_name of columns) { td.push(`<td>${row[col_name]}</td>`); } tbody.push("<tr>"); tbody.push(td.join("\n")); tbody.push("</tr>"); } tbody.push("</tbody>"); const table = []; table.push("<table>"); table.push(thead.join("\n")); table.push(tbody.join("\n")); table.push("</table>"); return table.join("\n"); } return ""; }
効率が悪いコードですが、私が確認しやすい構成にしてます。
....
試験的な機能を有効にする為、パラメータ付きでローカルサーバを起動します。
$ node --experimental-fetch main.js 待機中 (PORT: 3000) (node:6515) ExperimentalWarning: Fetch is an experimental feature. This feature could change at any time (Use `node --trace-warnings ...` to show where the warning was created)
警告メッセージが表示されますが気にしません。
(3) ローカルサーバが返す内容を 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
この iqy ファイルを Excel 2019 for Mac のメニューバーにある「データ」-「外部データ取り込み」-「Web クエリの実行...」から表示される画面で選択し、そのまま Excel に取り込みます。
これで目標は達成できました。
データベースが変更された場合、Excel 側でリボンの「すべて更新」をクリックすると、最新データに置き換わります。
JSON データの構造が複雑な場合もあるよ
いつもデータ構造が単一のテーブル形式とは限りません。
この例は、気象庁の天気予報サイトのレイアウトを真似しつつ、JSON データを表の枠組みに落とし込んだ結果です。
※この画像内のデータは修正を加えたものです。
JSON データの構造に従い、colspan, rowspan 属性で表組みをして、style 属性で体裁を整えると Excel でもそれなりに表示してくれます。
当初 <table> 要素の復習として、気象庁が公開してる JSON データを fetch() で処理するコードを書いたのですが、諸々を考慮して記事に載せるのは控えました。
あとがき
今のところ fetch() に関しては、特に問題なく動作してます。
私は最近、とりあえず node-fetch、用途によって SuperAgent, axios な感じでした。
パッケージとは異なり npm install 無しで使えるのが嬉しいです。
今後 fetch() の採用が中止になるとは思ってませんが、正式採用のアナウンスが来ないうちは現状維持ですね。
....
なお、この記事で試した環境であれば、わざわざ fetch() を利用せずとも、直接データベースにアクセスして後処理すれば同じ結果が得られます。
しかし、いつもそれが可能とは限りません。
外部はもちろん、 LAN 上のサービスでも、諸事情により提供されるデータ形式が選択できない場面があります。
そのため、ここでは JSON データ取得→加工→Excel の流れを実現する手段の一つとして検証してみました。
そして、Excel 2019 for Mac への Power Query の提供が待ち遠しいです。
来週くらいに更新が来るかもしれませんが、今回はどうなんでしょう。
