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

2021年5月21日 修正
細々間違い、タイプミス等を加筆修正。


これの続編

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

また、InternetExplorerがやっとWindowsから取り除かれることがアナウンスされていました。なくなりはしませんが、完全にオプション扱いになるみたい? 日本のWeb環境の進歩を実質止めてきた(邪魔してきた)レガシーが無くなる予定。
Internet Explorer は Microsoft Edge へ – Windows 10 の Internet Explorer 11 デスクトップアプリは 2022 年 6 月 15 日にサポート終了

ここ最近、業務系のウェブシステムで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好きだよねぇ。。。

ブラウザで画像ファイルのリサイズ処理

2021年3月15日 修正
blobオブジェクトを返すのではなく、new File([blob]……)として Fileオブジェクトのインスタンスを返すように修正


最近のお話。あくまでフィクションです💦 進行中案件の担当さんにある日相談を受ける。


画像をウェブサーバーにアップロードしたいけど、大きいサイズはリサイズしてサーバーに保存したいんだよねー
でも、外注先の人が・・・
『サーバーで画像縮小するんで負荷が大きいし、画像処理にGD使うからPHPのmemory_limitを上げてくれないと・・・』
って言われてるんです!サーバーは共用のレンタルサーバーでmemory_limitなんて変更してくれそうもないし・・・。
『memory_limitを仮に上げてもアクセスが重なるとサーバー落ちてしまうかもしれないけど、責任持てない』
とも言われます!

何か方法ない?


んー、アップロードする前に JavaScript でリサイズすればいいんじゃね?って単純に思ったわけですけど・・・
そんなことも分かんねー外注先使ってんのか・・・っていうのが一つ目。
つーか、重くなるのが分かってんならサーバーで処理させるな! っていうのが2つ目

ってなわけで、サンプルコードを渡して「後は良きに計らえ」コース。
サンプルを作ってやれば、あとはなんとかしてくれるんじゃねーか? つか、なんでこっちがサンプル作んなきゃいけないんだよ!!!
本来外注先のシステム制作会社がやるべき仕事だろーよ!
まぁ、怒ってもしょうがない。そのレベルの外注先しかやってくれるところないんだからしょうがない。
こうやってこのブログのネタにできるんなら、怪我の功名というやつです。(ん?使い方間違ってないか?)

本題です。要は ブラウザで画像をリサイズしてBlobオブジェクトとして取得できれば、あとは FormDataを作ってappend するなり、してやれば万事解決です。

ググればJavaScriptとCanvas APIを使ったコードがゴロゴロ転がってるので参考にしてこちらの用途に合うように手を加えていく。
画像リサイズ処理自体は Canvas API を使えば簡単にできるので、後は File オブジェクトを FileReader で読み込んで、リサイズ後に canvasオブジェクトの toBlobメソッドで Blobオブジェクトを取得してやればいい。

ややこしいのが、それらすべてが非同期で処理しなければならない事。Promiseを使って一連の画像処理をしてやれば、await構文で同期処理のように待つことができる。

再利用できるように モジュールとして書いた。
簡単に説明すると、リサイズが必要な画像のFileオブジェクトと短辺の最大サイズを引数にして、コールするとリサイズされた画像がblobオブジェクトとして resolve される。

当然ブラウザのバージョンに依存してしまうが・・・ゴチャゴチャ言われたら ターゲットのブラウザ用に Babel で変換しちまえばいいし💦

これを下記コードのように input[type=file]のonChangeイベントハンドラでゴニョゴニョしてサーバーにアップロードする。
僕は jQuery が大好きなので jQuery を使う。何度も言うけど、やっぱり document.querySelector(‘p’) とかタイプするより、$(‘p’) の方がラクなんだよね。

当然ながら Internet Explorer は全バージョンエラーになる。
chrome/firefox や safari でもバージョンによっては動かない。。。これじゃダメだ!って言われたら、最終手段 Babel のご登場(⌒∇⌒)

僕は下記のような .babelrc を書いて・・・

 >> browserify ./index.js --transform babelify | uglifyjs -c -m --output ./es5/bundle.min.js 

とかやると、とりあえず IE11でもシンタックスエラーにはならないようになる。だけど、結局 FormData.appendメソッドでエラーになるけどね😫

まぁ、今更なんですが、Canvas APIではリサイズ処理だけじゃなく図形を描画したり、ピクセル単位で演算処理を行うことでいろんなことが可能なので、サーバーサイドで画像処理を行わず、余力のあるクライアント(フロントエンド)で前処理させてから・・・というのがイマドキのやり方なんだろうな、と思います。

簡易ISOファイル作成コマンド

これの続きです。

自分用のツールの置き場、備忘録として記録しています😅

CDやDVDのメディアからISOファイルを作成するのは結構カンタンにできました。
これで殆どやりたいことはできたのですが、やっぱりディレクトリからISOファイルを作るにはどうするんだろう??? という素朴な考えがでてきまして・・・
ググるとこれもWindowsで標準機能でできるようで・・・具体的にはイメージマスタリングAPIとしてCD/DVD等のファイルシステム作成からメディアへの書き込みまでの機能がCOMサーバーとして提供されているようです。COMサーバーで実装ってことは、WSHやC#から簡単に使える・・・ってことです。

サンプルのコードはもうマイクロソフトのコミュニティーサイトに載ってましたので、これを適当にいじって、任意のディレクトリをISOファイルにビルドできるコードを書いてみました。一つ前に書いたDVDメディアからISOファイルを作るコードも統合してみました。

イメージマスタリングAPIは.NET Frameworkのクラスライブラリで提供されておらず、C#から使用するには、TLBIMPコマンドを使用してタイプライブラリからCLRアセンブリに変換し、コンパイルするときにこのアセンブリを参照しないといけません。Visual Studio のIDE環境を使用する場合はプロジェクトにIMAPI2FS.dllの参照を追加するだけでいいかもしれません。
僕みたいにコンソール画面でカチャカチャする場合は・・・

>> tlbimp c:\WINDOWS\System32\imapi2fs.dll

のようにすると、同名のアセンブリ(dllファイル)ができるので、ソースコードをコンパイルするときに、このDLLファイルを /r オプションで追加します。

殆どサンプルコードをコピペしただけなので、特に特筆するところはありません・・・githubにリポジトリ作ろうと思ったけど、ファイル1個だけなんでgistにした。

一応、タイプライブラリのインポートもあるので nmake用の makefileも書く。

DVDメディアのダンプ

Windows7の環境が必要になり開発用のプロダクトキー?を発行してもらい、後はHyperV環境で仮想マシンを作ってインストールするだけ。
って思ってたら、罠が・・・HyperVで稼働しているマシンにDVDを読めるドライブがねぇよ。。。
DVDメディアからISOファイルを作れば・・・と思い、Windows10のアクセサリの中にISOファイルを作るアプリを一応探してみたけど当然なく・・・、WindowsってISOファイルからメディアに焼く機能はエクスプローラから呼び出せるのに、その反対ができない・・・。

むむむ、linuxとかだと、ddコマンドで一発なのに・・・WSLからddでDVDドライブのデバイスを指定して・・・ってできないのかなぁ・・・。

仕方ないので窓の杜でフリーソフトを漁るか・・・とも思いましたが・・・、単にドライブをオープンしてダンプすればエエだけやろ? ってことで、C#で書いた。C#からWindows APIをコールするところはマイクロソフトのサイトからコピペして、適当に。

要点は、Windows APIの CreateFile から返る SafeFileHandleをFileStreamに渡してバッファを介してコピーするだけ。
FileStream.CopyToAsyncメソッドを使えば一行で済むんだけど、やっぱり途中経過(進捗状況)は必要かなー、と思って無理やり FileStream.ReadAsync/WriteAsyncメソッドを使ったけど・・・。

CMDとBashと変数展開と・・・

備忘録。

CUIではほとんど WSL1/ubuntu を使っています。WSL2の方が実行パフォーマンスはいいんでしょうけど・・・。

WSL1メインとはいいつつ、コマンドプロンプト(cmd.exe)を全く使わない・・・ということはない。たとえばタスクスケジューラに仕事をしてもらいたい処理は バッチファイル(CMDファイル)に書いて渡す方が何かとトラブルは少なくなりますし。
bitlockerで暗号化しているVHDファイルのマウント処理とかをWindowsのスタートアップスクリプトに登録したりとか、ログオン/ログアウトスクリプトに、後始末するスクリプトとか・・・Windowsのサービスを制御したりとか、コンピュータ名とIPアドレスの対応を調べたりとか、IPのルーティングを変えたりとか、やっぱりcmdファイル(バッチファイル)じゃないと不便なこともあります。

Powershellもありますが・・・ps1ファイルの実行がデフォルトでブロックされているので他所のPCで手軽に動かせない・・・とか、なんかイマイチです。

bashでのシェルスクリプトもウェブ開発では必須なので、いろんな処理の自動化スクリプトをちょくちょく書きます。
・・・で、bashとcmdのスクリプトをいったりきたり、色々書く時いつも躓くのは、変数展開の文法・・・要は書き方をよく忘れてしますこと。頭は悪い上、加齢でどんどん記憶力が落ちていく・・・。
あれ、bashのシェルスクリプトのこういう書き方って、バッチファイルではどうやるんだっけ???ということが度々あるので、カンタンな対応・比較表があれば便利だなと思い、メモついでに書いておく。

最低限こんだけ覚えてればなんとかなる・・・かもしれない。

  bash cmd
1行目 #!/bin/bash @echo off
変数代入 hoge=”This is a sample” SET hoge=This is a sample
変数参照 echo $hoge or echo ${hoge} echo %hoge%
入力 echo -n “please input: ”
read hoge
echo $hoge
SET /P hoge=please input:
echo %hoge%
文字列置換 hoge=”this is my appple pen”
echo ${hoge//this/that}
SET hoge=this is my appple pen
echo %hoge:this=that%
部分文字列 hoge=”this is my appple pen”
echo ${hoge:8:2}
echo ${hoge:8}
SET hoge=this is my appple pen
echo %hoge:~8,2%
echo %hoge:~8%
パス分解
パス
ベース名
拡張子
ファイル名
echo $0
echo ${0%/*}
filename=${0##*/}; echo ${filename%.*}
echo ${0##*.}
echo ${0##*/}
echo %0
echo %~dp0
echo %~n0
echo %~x0
echo %~nx0
日付時刻
乱数(簡易)
echo $(date)
echo $RANDOM
echo %DATE% %TIME%
echo %RANDOM%
IF-ELSE文 if [ expression ] ; then
 …
else
 …
fi
if expression (
 command1
 command2
  ….
) else (
 command3
 command4
 …
)
ループ(while) while [ expression ]
do
 …
done
loop:

if expresssion goto loop
※gotoで代替

間違いは随時修正中。思いついたら随時追加中。