excel

XLSX-Renderer and Comlink

これの続きです。
備忘録です。

JSONデータをPHPで作成し(PHPじゃなくてもいいんだけど。。。)、クライアント・ブラウザ側のJavaScriptでエクセルファイルを作成するまではできました。
ただ、問題なのは、取得するJSONデータが結構大きいサイズ（例えば10万件とか）になると、それはそれで厄介です。

なんせ、JSONデータをfetchするだけでそれだけで時間がかかりますし、そのデータをエクセルファイルに差し込んでエクセルファイルを作成する時にも結構な時間がかかります。
その間、「データを取得中・・・エクセルファイルを生成中・・・」とかでUIを止めてもいいのですが・・・それはイマドキのトレンドではありませんよね。

せっかくブラウザがワーカースレッドを使えるようにしてくれているので、別スレッドにそういう処理は任してしまいましょう。
そう! 我々にはグーグル様が書いてくれたComlink がある！(笑)

今回は、サンプルとして↓のように、郵便番号のデータベース(SQLite)から、リクエストに応じてJSONデータを返すPHPで書いたウェブAPIを使用し、そのデータをエクセルファイルに変換するまでを書いてみる。

まずは、ウェブAPIですが・・・これはサクッと書いてしまいましょう。
パラメータ”q”に郵便番号の先頭数文字を指定して前方一致で検索してJSONとしてレスポンスを返します。
郵便番号辞書の元になるデータは日本郵便サイトからCSVファイルが公開されているのでそれを使用しSQLiteに変換して使用しました。

次に、エクセルファイルを作成する部分のJavaScriptコードです。
以前にも書きましたが、この部分はnode.jsとnpm, babel.jsとかが必要です。この部分が全く分からなければこの先見てもワケワカンネー状態になるので、とっととブラウザを閉じてこのサイトの事は忘れてくださいm(__)m

作業ディレクトリを一個作って、以下のpackage.jsonとrenderer.jsを作成。

>> tree .
.
├── package.json
└── renderer.js

※ 2022/02/06 rendererをDedicatedWorkerGlobalScopeに追加するようにrenderer.js を若干修正しました。

で、下記を実行して renderer.pack.min.js が生成されるので、このファイルを workerスレッド内からimportScripts関数でロードします。

>> npm i
>> npm run bundle

これで準備完了です。
ここからは、実際に画面(HTML)とメインスレッドのjs、ワーカースレッドのjsを書いていきます。
例によって Web Workerを利用する際には、Comlink を使ってラクします。

HTML等はデモ画面を参照。

まずは、取得したJSONデータをExcelに差し込むための、テンプレートとなるエクセルファイルを作成します。
フツーにエクセルで作成していきます。Microsoft Officeを持っていない場合は、LibreとかオープンソースのOfficeアプリとかでも構いません。
.xlsx形式のファイルを出力できる表計算アプリであれば何でもいいと思います。

こんな感じです。テンプレート XLSXファイル

これらの書式等は、XLSX-Rendererのドキュメント参照。

次にメインスレッド側のJavaScriptコードです。
特筆すべき点はありません。見ればだいたい分かるでせう。
ワーカースレッドのやり取りに Comlink を使ってます。

このファイルをロードする際は、type属性を text/javascript 等ではなく、module にしないとなんかエラーになるみたいです。
そしてボタンのクリックハンドラを設定します。(ここだけjQueryを使用しています)

最後はワーカースレッド側のJavaScriptコードです。
Comlinkを使って、メインスレッド側に関数を公開しています。

※ 2022/02/06 若干修正

このファイル内で、上記で準備した renderer.pack.min.js をロードします。

上記デモページ

たぶんこの程度の処理なら、わざわざワーカースレッドを起こさなくても、いいかとも思います。実際問題、複雑になるだけですし。
メインスレッドはUI専用にして、雑多な処理を全部 workerスレッドに投げるようにすれば、ちょっとハッピーになれる・・・（かもしれない）。

SharedWorkerとかも興味があるんだけど、スマートフォンであまり対応していないんだよね。やっぱバッテリーの電気を食うからなんだろうか・・・。

JSONデータをExcelファイルに展開したい、ブラウザで。

2022年3月8日 一部記述削除

2021年7月31日 修正
babelの設定を書き忘れてた！package.jsonにbabelの設定を追加。

これの続編

サーバーで処理させるよりデータだけをWeb APIで引っ張ってきてブラウザ上のJavaScriptで処理しよう、っていうのがここ最近のトレンドだと思います。そのための環境は整ってきました。

ここ最近、業務系のウェブシステムでExcelをあーだこーだしろ、っていうのが急に増えてきてまして・・・たぶん昔動かしていたAccessアプリケーションを単純にWebへ・・・という流れなんでしょうかね？

で・・・Webサーバー(LAMP)でPHP-Excel（PHP-Spreadsheet）使ってエクセルファイルを処理させると滅茶苦茶メモリ食いませんか？
数年前に少し試してみたんですが、通常業務で利用するぐらいの行数(列数)でもメモリ不足でエラーでるわ、処理が遅くて使いもんにならんわ、で使っていくのを速攻で止めました。
（PHPじゃなくてJavaとかだと速いのかもしれんが、Javaは難易度が高すぎるし、そもそもLAMPを主戦場にしている仕事環境なので。）

さて、ちょっと前に Excelファイルを読み書きする SheetJS っていうのをネタに備忘録として書いたけど、SheetJSは Pro版じゃないと(有料)、スタイルなど文字の大きさとかフォントの指定だとかは扱えません。実質、既存のExcelのデータを読むだけになると思います。まぁPro版買えって話なんですけど。

で、良い感じにブラウザでカンタンにきれいなExcel帳票を作れないかなぁ・・・といろいろ githubとか漁ってたら、XLSX-Rendererっていうのを見つけました。簡単に言うと、WORDでいうところの差し込み印刷のように、テンプレートのExcelファイルにデータを差し込んでExcelファイルを作ってくれる。
XLSX-Renderer自体は ExcelJS というnodeモジュールを使用しているみたい。

XLSX-Renderer
https://github.com/Siemienik/XToolset/tree/master/packages/xlsx-renderer

まぁ、いつもの如く中身はよく分かんねーけど一度試してみました。今回もその備忘録です。
【動作デモはこちらから】

開発環境としてnodejsとnpmが使える環境が大前提です。
また、モダンなJavaScriptの知識( Promise,await/async,thenなど)と、必要であれば、BabelJSなどのカンタンな使い方を知っている人向けです。じゃないとワケワカメになると思います。ご了承のほど。

システムにbrowserify,uglify-jsをインストール

>> sudo npm install -g browserify
>> sudo npm install -g uglify-js

説明するまでもないとは思いますが、browserifyはnodejsモジュールをブラウザで利用できるようにワンパッケージにしてくれるコマンド。uglifyjsはminifyするため。詳しくは知らない。手順だけ知っておけばいい。理屈は後回し。フロントエンド周りに詳しい人は webpack とかいろいろ方法はあるとは思いますが、僕はこれしか知らないので💦

まず、下記のようなテンプレートとなるExcelファイルを作ります。（テンプレートExcelファイル）

このExcelファイルに、データをぶち込みたいところへ、#から始まる命令コマンドをセルに埋め込んでいく、という感じ。自分でセルを埋め込むメソッドをチマチマコーディングするより圧倒的にラクできる感じ。
#! FOR_EACH 命令を使えば、配列をぶち込める。

要は MVCモデルの View の出力先を エクセルファイルにしている感じでしょうか。同様に、DOCX-Renderer とか PDF-Renderer ってのも作ってほしいよ(笑)

次に本題の処理する部分をコーディングします。

扱いやすいように、僕はグローバル関数を(windowオブジェクトのプロパティとして)定義してコールするようしました。このあたりはどういう処理を行わせるかによって実装方法は変わると思います。とりあえず関数として実装しました。
このままではブラウザでは動かないので、上記のコードをブラウザで利用できるようにするため、この xlsx-template.jsをbabelなどのツールを使ってビルドします。そのため、ディレクトリを一個作って以下のpackage.jsonを置き、

>> tree .
.
├── package.json
└── xlsx-template.js

そのディレクトリで以下を実行

>> npm install
>> npm run bundle

そうすると、bundle.min.jsが生成されるので、これをHTMLファイルのscriptタグからロードします。
HTMLファイルはこんな感じでしょうか。実際にはJSONデータはサーバーから取得することになりますが・・・。データ構造は簡単で、キーと値がそのままテンプレートのExcelファイルのセルに記述した “## キー名” に対応してます。このあたりは XLSX-Renderer のgithubページに記述の仕方が書かれているので参考に。比較的簡単です。

そうすると、下記のようにテンプレートとなるExcelファイルにデータを埋めてくれます。

【動作デモ】

エクセルのレイアウトが変わっても、テンプレートのExcelファイルを変更すればいいだけので、変更も簡単で、実行コードを修正する必要もない。

まだ試していませんが・・・データベースサーバーからウェブサーバー経由で1万件ぐらいのJSONデータを取ってきて、処理させてテストしてみて、処理速度が業務に耐えられるようであれば、本格的に利用していきたい、と思ってます。

・・・しかし・・・みんなExcel好きだよねぇ。。。

編集可能な簡易グリッドビュー画面をイチから作ってみる

直接関係はありませんが、これ の続きです。

ここにきて、カンタンなグリッドを表示する仕事があって、それならセルの内容を書き換えるだけの簡易的なセル編集機能もあればなぁ、と家に帰ってから唐突に思いつき、テレビを見ながら数時間ぐらいで突貫工事で作った。

仕様的には・・・
（１）Excelファイル(もしくはUTF-8なCSVファイル)をブラウザに読込み、
（２）簡単なグリッドビュー表示を行い、
（３）セルをクリックしたら編集し、
（４）リターンキー押下で編集確定、
（５）エスケープキーで編集取消
という感じ。

これならHTMLとCSS、JavaScriptで百数十行ぐらいでいけそうです。
実際業務で使うには、変更内容を追跡し、サーバーへ送信してデータベースに反映して・・・というような事が考えられますが・・・そこまでやっとれん！ｗ

まずは、見てくれ、画面デザインですが、あくまでサンプル的なものなので、ほとんど素のHTMLです。
CSSのGridなんて初めて書いてみました。普段はBootstrapのレイアウトグリッドを利用して画面を作っているんですが・・・CSSのグリッドって、なんか難解ですね・・・ドキュメントを一回読んだだけではオッサンには理解できません(笑) エクセルの読込には、みんな大好き SheetJS ライブラリを利用しました。

ただ、セルを結合しまくったりしてるような複雑なエクセルファイルとか読み込むとたぶん滅茶苦茶になります。そういう複雑なエクセルファイルを持っていないので試していないんだけど・・・

デモ画面はこちら

CSS自体はそんなに凝ったことしてないので、読めば大体やろうとしていることが分かるでしょう。

続いて、肝心のJavaScript ですが、ほとんど各種イベントの処理ぐらいで、これといって特筆するべき事はありません。
ところどころ jQuery 使って見苦しいのですが・・・jQueryだと明らかにタイプ量が減るので僕は多用しています。世間の流れとしてはjQueryは排除されつつあるのですが・・・やっぱりjQueryに慣れきってしまうと document.querySelector なんてタイプするのがすんげぇ面倒。jQueryだと $ の一文字ですよ？イベント登録も on の二文字ですよ？ addEventListener とかタイプするのがアホらしくなりますけどね、オッサンは。

いつも利用させてもらってる疑似テストデータを利用してエクセルデータを流し込んでやると、こんな感じです。「こんな感じです」って言われてもね。。。

いちおう、エクセルファイルから簡易グリッドビューにデータを流し込み最低限の編集はできるようになりました。
・・・が、グリッドのHTML的な構造は見直す必要がありますよねぇ。

<div class="row body">
  <span class="cell"></span> ....延々とセルが続く
</div>

一つの要素(div.row.body)にすべてのセル(span.cell)を全部詰め込んで無理くりCSSでグリッド表示させてるだけなんで、<table>要素みたいに、行で分かれていないので、任意の行を選択したいときは、いちいち計算しないといけません。

ま、ちょっとだけグリッド表示したい、って時に使う用、と割り切る。

ウェブブラウザ上のJavaScriptでExcelファイルをゴニョゴニョしたい

2022年6月13日 修正。追記を削除。FileReaderを使用したコードを削除。代わりに Blob.arrayBuffer()メソッドを使用したコートに変更しました。

2021年10月21日 追記 FileReaderを使わず、Blob.arrayBufer()を使用したコードを追記しました。

特に需要はないと思いますが、極たま～～～にウェブでエクセルファイルを扱うことがあります。
なんつっても、世の中の文書フォーマットは、マイクロソフトのエクセル(Excel)がデファクトスタンダードです💦
これはもうどうにもなりません。CSVで、つっても、エクセルファイルをよこしやがります。

まぁ、やっぱり業務システムにはエクセルは欠かせません、というか、これなしには、日本は動きません。日本の企業とか国、地方公共団体は、エクセルで動いているんです。これはもう動かしようのない事実であり当分の間は変わりませんし、変化の兆しも見えません。

しょうがない。

と、開発者の方が思ったかどうかわかりませんが、ブラウザのJavaScriptでエクセルファイルをパースして編集して、出力してくれる ライブラリがあるんですね。サーバーサイドのNode.jsでも使えます、というかそっちがメインのライブラリなのかも。

一般的にMS-Officeをインストールしたパソコンにはおまけ？として、EXCELのオートメーションが使えるようになってます。あくまでOfficeを買えば！の話ですが。
Windows Scripting HostなどからVBScriptやJScriptを使ってカンタンにエクセルファイルをアーダコーダできます。

・・・が、今回はこういう噺ではありません。
一般的なブラウザ上で、エクセルファイルをアーダコーダできるライブラリがあったんです。需要がなかったので今まで知らなかった！

SheetJS Spreadsheets simplified

で、ググったりしていろいろ調べると、意外にカンタンに使えちゃいますね。ただし、javascriptの blobとかFileReaderとかArrayBufferとかUint8Arrayとか・・・そういうちょっとややこしめの知識が必要です。

昔と違ってブラウザでローカルファイルとか普通に扱えます。ですが、その時は必ず、上記のFile/Blob/FileReader/ArrayBuffer/TypedArrayとかが絡んできます。
FileとBlobの関係、ArrayBufferとTypedArray(Uint8Arrayとか)の関係、さらにはFileReaderとFile/Blobの関係。このあたりは鬼門です。なんでこんなめんどくさいんだよ！っていつも思います。

MDNとかのリファレンスを読むのが手っ取り早いのですが・・・とりあえず、MDNのサイトでは、こういう場合は、こうする、という「お約束」の手順が書かれているので、まずそれを丸覚えするのがいいと思います。公文式です(笑) 理屈は後から学べばいいんです。ただ、ググってブログ記事を参考にするのは結局は理解するのが遅くなってしまうので、リファレンスとサンプルを読んで、書いて、試してみたほうがいいと思ってます。

※ たぶんIEでは動かないと思う。試してないけど。IEは既にMSも認めたオワコンなんで、どーでもいい。

下のデモ(テストコード)

さて、HTMLファイルをサクッと書きます。（Ryzenの価格表コピペしました。）

JSライブラリは適当にCDNから引っ張ってきましょう。僕は jQuery好き好き人間なので、jQueryを使用します。すみません。
INPUT[type=file]タグでローカルファイルの口としましょう。ドラッグ＆ドロップを仕込んでもいいのですが、コードをカンタンにするため、普通のファイル選択にしました。ここでローカルファイルを選んでjavascriptコードに放り込みます。

で、次に実際の処理を書いていきます。まず、基本。INPUT[type=file]のonchangeイベントハンドラを起点にしています。(1311行目付近)

ローカルファイルを読み込んでゴニョゴニョするときは、必ずFileReaderのインスタンスを作って、onloadイベントで処理を行います。(18行目付近)
下記例では、FileReader.readAsArrayBuffer() していますが、単純に Data URIが必要であれば FileReader.readAsDataUrl() を使用します。
ローカルの画像ファイルを読み込んで表示するときは、readAsDataUrlメソッドを使いますよねぇ。
ちなみにreadAsArrayBufferメソッドを使うと、onloadイベントハンドラ内で ev.target.result によってArrayBufferオブジェクトを得ることができますが、
Internet Explorerが今月中(2022年6月現在)に抹殺される。そして、FileReaderを使うやり方は、もう時代遅れなので、Blob.arrayBuffer()メソッドとasync/await構文を使ったものにしましょう。

直接このArrayBufferオブジェクトにアクセスすることができません。必ず、TypedArray・・・たとえば、Uint8Arrayなどのオブジェクトのインスタンスからアクセスします。めんどくさいですねぇ。

FileReader.onload内で、ev.target.result を そのまま Uint8Arrayコンストラクタに渡して、ArrayBufferへアクセスするための Uint8Arrayオブジェクトを作り(21行目付近)、それを XLSX.readメソッドに渡します。ただそれだけで、エクセルファイルをパースしてくれます。あとは、XLSX.utils オブジェクトのメソッドをコールしていけば、だいたいのことはできると思います。
CSVデータが欲しければ、XLSX.utils.sheet_to_csv, JSONデータが欲しければ XLSX.utils.sheet_to_json、シートを増やしたければ、XLSX.utils.append_sheet、他にも javascript配列をシートに、DOM TABLE要素をシートに・・・とか対応するメソッドがあるようです。この辺のドキュメントはgithubをたよりに試行錯誤するしかないのかな。。。

エクセルファイルにシートを追加して、そのエクセルファイルをダウンロードする、これだけのことをクライアント内(ブラウザ内)で完結することができてしまいます。
ローカル内でサーバーを立てる必要もありません。上記二つのHTMLとJSを任意のフォルダに適当な名前で保存して、file://スキームで試すことも可能です。

(1) HTMLファイル ローカルで開いたところ。

(2) エクセルを選択すると、ダウンロードリンクが追加されます。

(3) 元のエクセルはいつもダミーで使わせてもらってる疑似会社情報のエクセルファイル
( http://hogehoge.tk/personal/ )

(4) HTMLのテーブルデータをエクセルシートにぶっこんでくれます。
ただしセル書式は全部吹っ飛びます。

今回は、エクセルシートを追加してみましたけど、セルの属性とかは全部ぶっとんでしまいました💦
が、単にJSONやCSVが取れればいい、というのが大半の需要かと思いますので問題はないでしょう。

僕は エクセルファイルをサーバー側で変換するのではなく、クライアントで一旦CSVに変換してから、サーバーにアップロードして、処理を行う用途に使用しました。サーバーでエクセルファイルを処理すると重くなるんで・・・。
エクセルを読むだけなら、カンタンにできるので、本当にありがたいライブラリです。

ただ一点、ちゃんとしたリファレンスドキュメントがありません。。。これはPro版を買えってことなのかなぁ。。。Community版はApache License 2.0なんで、「おまえら、ソース読んで、自分でなんとかしろよ」ってことなんでしょうね。

MySQL for Excel

知らない間に、MySQL for Excel という素敵なツールがあるのに気づき(遅すぎ)、会社のPCにダウンロードしてインストールして、テストサーバー(CentOS6 / MySQL 5.1)に接続してみました。。。

家のパソコンにはエクセルなんて無用の長物なのでインストールしていないし、持ってもいない。

結果、撃沈。

Authentication with old password no longer suppored. Use 4.1+ style….

とかいう、エラーダイアログが出て止まる。んー、ユーザーの設定は全部phpMyAdmin経由で使用しているから、古いパスワードが埋め込まれてんのかな？と思い、sshで接続してmysqlコマンドで、下記確認。

select user,host,password from mysql.user;

新しいパスワードは、アスタリスク(*)から始まるけど、全部16文字の古い形式やった。。。どおりで通らんはず。

全部パスワードを設定しなおしたった。

mysql

これで、MySQL for Excel で無事に接続でけた。

テーブルのインポートから・・・mysqlexcelでもデータのインポートだけなら、Connector/ODBCをインストールしてODBC 経由で普通にできるやん！という無粋なことは言っちゃいけない。

でもって、テーブルのレコード追加・編集まで・・・使い慣れた表計算グリッドでデータをぶち込めたり、上書きできたり、という。素敵！

mysqlexcel2

しかし・・・結局のところ、エクセル使いの方は、アクセスを使うと思うし・・・Windowsで飯を食ってる人はそもそもSQLServer使うし・・・、MySQL for Excelは誰が何のために使うのだろうか・・・？ そもそもMySQLを日常的に使用する開発者がわざわざExcelなんて使うだろうか？ ふつうはOpen Officeでそ？僕なんて、いまだにIBMのLotus Symphony 3だし。

ってわけで、HDDの肥やしになる。