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

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で代替

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

JavaScript Map オレオレ拡張

今、かつてないほどJavaScriptと関わっている。
※JavaScriptという名称は正式にはOracleの登録商標で一般的には ECMA Script って事になるんだろうけど、めんどくさいので JavaScript と明記する。あらかじめご了承のほど。

僕のJavaScriptの文法、その他の知識は正直2010年(ES5)ぐらいで止まってる。というのも今持っている知識だけで要求されるほとんどのケースが実現可能だからだ。
もちろんパフォーマンス的な、効率的な・・・というのは棚に上げまくっているんだけど。その当時も ES6 も意識していましたが、まだブラウザでの実装状況が混沌としててInternetExplorerがまだまだ全盛時代だったので、どうしてもすべてのケースで動くように ES5 を強制していた。

今の JavaScript って5~6年前によく見たコーディングスタイルとは全然違いますよね。
ブラウザでホスティングされているJavaScriptで普通にラムダ式が使えるし、変数宣言で var ではなく let を使うことでブロックスコープを意識したコーディングができる。
まぁ、今頃こんなこと書いても、今更感が半端ないんですけどね。

去年から現在進行形で携わっている仕事では、徐々に(少しずつ) ES6 を意識したコーディングを心がけるようになってきました。ほんとに少しずつですけどね💦

余談:(余談ばっかりだが・・・)——————
理由はマイクロソフトが正式に Internet Explorer 及び edge-HTMLをベースにしたEdgeブラウザのサポートを止める、もしくは新規開発しない、事を宣言したから。
残念なことだけど、ITリテラシーの低い人たちからすれば、未だにInternet Explorer がインターネット業界(笑)の標準だと思っている人が結構多い。
マイクロソフトがレガシーブラウザと決別宣言してくれたおかげで、ChromeやFireFox,Chromium版Edgeを強制することが可能になったことが非常に大きい。
———————-

一番意識しているのは(オブジェクト初期化子{}でインスタンスを作る)オブジェクトを連想配列的に使用するのを止めてMap や Set を使うことにしたこと。
MapやSetもかなり以前から実装されているけど、やっぱり InternetExplorer11 では限定的な実装なので使うのも躊躇してた。

Map,Set は非常に便利ですよね。オブジェクトを使用した連想配列では、キー(プロパティー)に文字列ぐらいしか使えない。Mapだとキーに何でも入る。
MDNでの説明だと、頻繁に削除・挿入を繰り返すケースでパフォーマンスが上がるんだそうで。

たいがいの場合、キーには文字列を使うのが大半の用途だと思います。DOM要素やオブジェクトもキーにできると思うけど、正直僕には使いどころが分からない。まぁ、思いつくのは コールバック(関数)なんかをためておく用途にするぐらい。それでも、そういう時は Map よりSetを使うし。。。

でも特にブラウザ上でDOMとか扱っていると、やっぱりプレーンなオブジェクトの方がコーディングが楽な時もある。ので、Map.prototype空間にオレオレMap拡張メソッドを追加して使うことになってくる。

※2020/04/02 ちょっと追記した。

Map.prototype.toPlainObject

たぶん誰もが書いてる・・・と思う、文字通り、文字列をキーにしたMapをオブジェクトに変換する。
最新のブラウザとかだと、Object.fromEntries とかいう Object.entriesの逆動作を行う関数が実装されているので↓は不要なのかも。

Map.prototype.toPlainObject = function()
{
  var rv = {};
  this.forEach(function(v,k) { 
    if(typeof k === 'string')
      rv[k] = v;
  });
  return rv;
};
// もしくはもっと簡単に・・・
Map.prototype.toPlainObject = function()
{
  return Object.fromEntries(this);
};

Map.from

オブジェクトからMapへの変換は・・・↓でいいのかな? Object.entries は比較的新しめのブラウザしか対応してなくない?

Map.from = function(o)
{
  return new Map(Object.entries(o));
};

Map.prototype.stringify

JSON.stringify のもろパクリですね。toString をオーバーライドすればいいんでしょうけど、そこまでするのは止めましょう。
主に localStorage/SessionStorage へ格納するときに使う。

Map.prototype.stringify = function()
{
  return JSON.stringify(this.toPlainObject());
};

Map.parse

これも JSON.parse のパクリ。JSONのパクリというか、文字列を直接parseするのではなく、一旦オブジェクトにJSON.parseで変換して Map に変換します💦

Map.parse = function(str)
{
  return new Map(Object.entries(JSON.parse(str)));
};

Map.prototype.numIncr/Map.prototype.numDecr

オブジェクトだと、普通にobj.counter++ とか、obj.counter += 10 とかできますよね。。。。
Mapだと一旦 getで取得してsetで更新しないといけません。JavaScriptは演算子を定義もしくはオーバーロードできませんよねぇ。。。。
演算子のオーバーロードは混乱の元になるので極力やっちゃいけない、ってC++の時言われてたな・・・。今もそうなのかなぁ。。。

Map.prototype.numIncr = function(key,delta)
{
  if(typeof delta !== 'number' || delta == 0)
    delta = 1;

  var value = this.get(key);
  if(typeof value === 'number')
  {
    value += delta;
    this.set(key,value);
  } 
  return this;
};
Map.prototype.numDecr = function(key,delta)
{
  if(typeof delta !== 'number' || delta == 0)
    delta = 1;

  return this.numIncr(key,-1 * delta);
};

なんか、Mapの良さ(キーはなんでもOK)を殺してしまうようなものばかりだな。。。キーをstringに限定するようなものあればな~・・・とは思いますが、型が強制されない言語だと難しい。

ざっとよく使うものを列挙しました。自分がコピペできるように💦
ここまで書いてて、気づいたのは・・・var使いすぎ問題! ついつい癖で var って書いてしまうんす。
ラムダ式もイマイチ好きになれない記法だったりします。 C#だと当たり前のよう書くんですけどね。。。好き嫌いというより単に癖なのかも。

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

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

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

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

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

まずは、見てくれ、画面デザインですが、あくまでサンプル的なものなので、ほとんど素の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>要素みたいに、行で分かれていないので、任意の行を選択したいときは、いちいち計算しないといけません。

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