まだプログラマーですが何か?

プログラマーネタ中心。たまに作成したウェブサービス関連の話も https://twitter.com/dotnsf

タグ:excel

先日、このようなブログを書きました:
"Image to Excel" を REST API 化(xlsx-js-style の使い方)


xlsx-js-style というライブラリを使って、ネットでプチバズった Image to Excel というツール(を REST API 化したもの)を独自実装した、という紹介エントリでした。

この REST API を作る上で xlsx-js-style というエクセルシートやエクセルブックファイルを Node.js で作成・編集するライブラリを使いました。Image to Excel を作るだけであればセルのサイズや背景色を設定する機能を使えばできるので(セルのテキストを読み書きする必要はなかったので)さほど難しくありませんでした。

というわけで、その勉強の続きという意味も含め、より実践的なセルの読み書きを伴うサービスを作ってみようと思い立ち、苦労しながらブログネタになりそうなものを作ることができたので紹介させていただきます。


【作ったサービスの内容】
一言でいうと「データベースとエクセルの相互変換ツール」です。データベースをエクセルファイルに、エクセルファイルはデータベースに、それぞれ変換して記録(保存)するツール。正確にはツールというよりも REST API として実装しているので、別の外部アプリケーションから使うことを想定しています(Swagger API の UI は標準で用意したので、単体でも動かしてみせることはできます)。

作ったサービスをもう少し詳しく説明します。まず「データベースをエクセルにする」部分から、現時点では PostgreSQL か MySQL だけで動くのですが、こんな感じのデータベース接続文字列を指定して API を実行します:
 (PosgreSQL の場合)postgres://username:password@servername:port/dbname
 (MySQL の場合)  mysql://username:password@servername:port/dbname


PostgreSQL の場合は最初のプロトコル名部分を "postgres" に、MySQL の場合は "mysql" と指定します。"username:password" 部分は接続する際のユーザー名(username)とパスワード(password)を指定します。servername はデータベースサーバー名(または IP アドレス)で、port は接続ポート番号です(PostgreSQL のデフォルト値は 5432 、MySQL のデフォルト値は 3306 です)。そして dbname は接続先のデータベース名を指定します。

例えば localhost の 5432 番ポートで動いている PostgreSQL サーバーの mydb データベースに、ユーザー名 user1 、パスワード pass1 で接続する場合は以下のようなデータベース接続文字列が必要となります:
 postgres://user1:pass1@localhost:5432/mydb


話を戻します。このようなデータベース接続文字列を指定して REST API を実行すると、以下のルールでデータベース内容をまるごと1つのエクセルファイルに変換します:
・データベース内の全テーブルと、各テーブル内のレコードデータを取り出す
・各テーブルが1枚のエクセルシート(タブ)になる テーブル名がシート名になる
・シートの1行目にテーブルの全列が書き出される
・シートの2行目以降にテーブル内の全レコードデータが1行ずつ格納される(ただし BLOB などのバイナリデータは無視する)
・処理が完了すると、(データベース名).xlsx という名前のエクセルファイルでダウンロードできる

という誰得(?)な処理を行います。 もう1つの「エクセルをデータベースにする」は基本的にこの逆の処理を行います:

・実行時のパラメータとして(上述の処理と同様に)データベース文字列を指定する
・併せてエクセルファイルをアップロードする形で REST API を実行する
・エクセルファイル内の全シートを1つのテーブル内容としてデータベースに格納する
 ・テーブル名はシート名
 ・各シートの1行目にテーブルの列名が格納されているとみなす
 ・各シートの2行目以降にテーブルのレコードデータが1行ずつ格納されているとみなし、
  テーブルに1行ずつ追加される(ただし BLOB などのバイナリデータは無視する)
・REST API 実行時のパラメータにより各シートの2行目にある情報はレコード情報ではなく、各列の定義情報とみなすこともできる。その場合、3行目以降にテーブルのレコードデータが格納されているとみなす。
・同様にして REST API 実行時のパラメータによりシート名の既存テーブルを削除(drop table)してから実行するか、新規作成(create table)してから実行するかを選択することができる
・処理が完了するとエクセル内の各シートに定義されていた情報がまるごとデータベース化される


【ソースコード】
作成したサービスのソースコードをこちらで公開しています:
https://github.com/dotnsf/db2xlsx


実際にネット上のどこかでサービスとして公開することも考えたのですが、現実問題としてデータベースがインターネット上に公開されていないと使えないので(普通、公開されていないはずなので)、公開しても使えるものではないと判断しています。 そういうこともあってのソースコードでの公開としました。

サービスとして自分の PC で使ってみたい場合は、Git と Node.js がインストールされた環境で以下の手順を実行してください:

(1)ターミナルやコマンドプロンプトなどの端末を開く

(2)ソースコードをダウンロード
$ git clone https://github.com/dotnsf/db2xlsx


(3)必要なライブラリをインストール
$ cd db2xlsx

$ npm install


(4)実行(終了時は Ctrl+C)
$ npm start


ただ単にサービスとして実行しても対象のデータベースやエクセルファイルが無いと使えないので、より具体的な手順を次のセクションで紹介します。


【Docker と Swagger API で実際に使う手順】
このサービスを実際に使うにはインポートやエクスポートの対象となるデータベースや、エクセルファイルが必要です(そういうサービスです)。エクセルファイルはサンプルを用意できるのですが、データベースはそうはいきません。 実際に使える PostgreSQL や MySQL のデータベースがあればそれをそのまま使っていただいてもいいのですが、そういう人も多くはないと思うので、以下に Docker を使ってデータベースを用意する手順を紹介します。というわけでまずは Docker(個人利用であれば Docker Desktop がお奨めですが、業務利用の場合は有料なので、無料を希望する場合は WSL などを使って Community Edition)をインストールしておいてください。


Docker で PostgreSQL データベースを1つ用意する場合は以下を実行します:
$ docker run -d --name postgres -p 5432:5432 -e POSTGRES_USER=user1 -e POSTGRES_PASSWORD=pass1 -e POSTGRES_DB=db1 postgres

↑このコマンドで作成した場合、ユーザー名 user1 、パスワード pass1 、データベース名 db1、ポート番号 5432 番で postgres という名前の PostgreSQL コンテナが起動します。この例だとデータベース接続文字列は "postgres://user1:pass1@localhost:5432/db1" となります。

データベースを2つ作る必要はないので PostgreSQL で作成した場合はそれを使えばいいのですが、どうしても MySQL を使いたい場合はこちらを実行します:
$ docker run -d --name mysql -e MYSQL_ROOT_PASSWORD=P@ssw0rd -e MYSQL_USER=user1 -e MYSQL_PASSWORD=pass1 -e MYSQL_DATABASE=db1 -p 3306:3306 mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

この場合だとデータベース接続文字列は "mysql://user1:pass1@localhost:3306/db1" となります。ユーザー名部分やパスワードなどは適宜変更して読み替えてください。


この方法を含め、なんらかの方法で接続して読み書き可能なデータベースが用意できているものと仮定して以下の説明を続けます。


データベースが稼働している状態で上述の(4)までを実行して本ツールを起動します(本ツール起動後にデータベース起動でも構いません):
$ npm start

> db2xlsx@0.0.1 start
> node app.js

server starting on 8080 ...

↑の青字のような表示が出力されれば起動に成功しています。ツールは 8080 番ポートで稼働しているので、ウェブブラウザで http://localhost:8080/_doc にアクセスして Swagger API Document を開きます:
2023122201


↑のような画面が表示されます。この画面でも "POST /db2xlsx" と "POST /xlsx2db" の2つの REST API が存在していることが確認できます。名前の通りですが、それぞれ「データベースからエクセル」、「エクセルからデータベース」に変換する API になっています。


これらの API をそれぞれ使ってみましょう。順番としてはデータベースは空の状態であると想定して、
(1)まずエクセルファイルの内容をデータベースにインポート(POST /xlsx2db)して、
(2)そのデータベースの内容をエクセルファイルにエクスポート(POST /db2xlsx)する
という順に実行してみることにします。

まずはデータベースにインポートするエクセルファイルを用意します。独自のものを準備いただいてもいいのですが、すぐ使えるようなサンプルを用意しておきました:
https://dotnsf.blog.jp/items-brands.xlsx


↑このリンクからエクセルファイル items-brands.xlsx をダウンロードしてください。エクセルや互換ツールを使って開ける場合は内容を確認してみてください(自由に変更していただいても構いません):
2023122301

2023122302


この items-brands.xlsx ワークブックには items と brands という2つのシート(タブ)があります。それぞれ以下のようなデータが表形式で格納されています:

(items)
id name brand_id price body updated
varchar(20) primary key varchar(100) not null varchar(20) integer text timestamp
item001 商品1 brand001 100 商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1商品説明1 2023-12-20
item002 商品2 brand002 1200 商品説明2商品説明2商品説明2商品説明2商品説明2商品説明2商品説明2 2023-12-21
item003 商品3 brand003 500 商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3商品説明3 2023-12-22
item004 商品4 brand002 2980 商品説明4商品説明4商品説明4商品説明4商品説明4商品説明4 2023-12-23


(brands)
id name body url updated
varchar(20) primary key varchar(100) not null text varchar(100) timestamp
brand01 ブランド1 説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1説明文1 https://www.brand1.com 2023-12-20
brand02 ブランド2 説明文2説明文2説明文2説明文2説明文2説明文2説明文2説明文2説明文2 https://www.brand2.net 2023-12-21
brand03 ブランド3 説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3 https://www.brand3.jp/ 2023-12-22


エクセルファイルが用意できたらこれをデータベースにインポートしてみましょう。先ほどのブラウザ画面内の "POST /xlsx2db" と書かれた箇所をクリックします:
2023122303


すると下のような隠れていた画面が展開されます。この API を実行する時に指定するパラメータに関する情報が表示されています。内容を確認し、そのまま実行したいので "Try it out" と書かれたボタンをクリックします:
2023122304


するとパラメータ部分が編集可能状態に切り替わります。ここに必要な情報を入力していきます:
2023122305


まず一番上の database_url パラメータは指定が必須になっています。ここにはデータベース接続文字列を入力します。上の方法で PostgreSQL データベースを起動した場合であれば、
 postgres://user1:pass1@localhost:5432/db1
また MySQL データベースを起動した場合であれば、
 mysql://user1:pass1@localhost:3306/db1
を指定してください(別の方法で起動したデータベースの場合はそのデータベースに接続するための接続文字列を指定してください)。


また一番下の「エクセルワークブックファイル」と書かれた file パラメータも必須入力です。ここは「ファイルを選択」ボタンをクリックしてから、先ほど用意したワークブックファイル(ダウンロードしたものを使う場合は items-brands.xlsx )を指定します。

それ以外の部分は必要に応じて指定するものですが、今回指定したワークブックファイルは各シートの2行目にテーブルの列定義が記載されています。このような場合(シートの2行目がレコード情報ではなく、列定義情報の場合)は col_attr パラメータに "1" という値を指定する必要があります(何も指定しない場合は2行目もデータレコードとみなされて insert into table が実行されます。またテーブルの各列は全て text 型として create table が実行されます)。

また今回は初回実行なのでまだデータベース内にテーブルは何も作成されていません。したがって create table を実行してから insert into table を実行する必要があります。その場合は create_table パラメータに "1" を指定してください。今回は指定しませんが drop_table パラメータに "1" をセットして実行すると create table の前に drop table が実行されます。定義の異なる同名のテーブルが作成されている可能性があって、そちらを削除してから実行する場合や、現在登録済みのデータを削除してから再実行したい場合は drop_table の方にも "1" を指定してください。

パラメータの指定が完了したら最後に青い "Execute" ボタンを実行すると xlsx2db 処理が実行されます:
2023122601


実行後、ボタンの下のほうに実行結果が表示されます。またこの REST API を実行した時のコマンド内容も表示されるので、この REST API を外部から利用する機会があれば参考にしてください:
2023122307


とりあえず実行は完了し、成功しているようです。 せっかくなので実際にデータベースにアクセスして成功した結果を確認してみましょう。

ターミナルやコマンドプロンプトなどの端末をもう1つ開いて、CLI からデータベースに接続します。上述のような Docker でデータベースを起動している場合はそのコンテナに直接ログインして調べることができます。

データベースが PostgreSQL の場合は、以下のようなコマンドで確認できます(赤字が入力内容、青字は説明用のコメントです。実際に入力しないでください):
$ docker exec -it postgres bash (データベースが動いているコンテナにログイン)

/# psql "postgres://user1:pass1@localhost:5432/db1" (接続文字列を指定して psql コマンドでログイン)

db1=# \dt (テーブル一覧を表示、brands テーブルと items テーブルが作成されていることを確認)

        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | brands | table | user1
 public | items  | table | user1

db1=# select * from brands; (brands テーブルの内容を確認し、エクセルに入力されていた内容が格納されていることを確認)

   id    |    name    |
           body
   |          url           |       updated
---------+------------+--------------------------------------------------------
-------------------------------------------------------------------------------
---+------------------------+---------------------
 brand01 | ブランド1 | 説明文1説明文1説明文1説明文1説明文1説明文1説明文
説明文1説明文1説明文1説明文1説明文1説明文1
  | https://www.brand1.com | 2023-12-20 00:00:00
 brand02 | ブランド2 | 説明文2説明文2説明文2説明文2説明文2説明文2説明文
説明文2説明文2
  | https://www.brand2.net | 2023-12-21 00:00:00
 brand03 | ブランド3 | 説明文3説明文3説明文3説明文3説明文3説明文3説明文
説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文3説明文
 | https://www.brand3.jp/ | 2023-12-22 00:00:00
(3 rows)

db1=# select * from items; (items テーブルも同様に確認する)

(略)

db1=# \q (psql からログアウトする)

/# exit (コンテナからもログアウトする)

データベースが MySQL の場合も、コマンドが少し異なりますが同様に確認できます:
$ docker exec -it mysql bash (データベースが動いているコンテナにログイン)

# mysql -u user1 -ppass1 db1 --default-character-set=utf8mb4" (接続情報を指定して mysql コマンドでログイン)

mysql> show tables; (テーブル一覧を表示、brands テーブルと items テーブルが作成されていることを確認)

+--------------+
| Tables_in_db |
+--------------+
| brands       |
| items        |
+--------------+
2 rows in set (0.01 sec)

mysql> select * from brands; (brands テーブルの内容を確認し、エクセルに入力されていた内容が格納されていることを確認)

(略)

mysql> select * from items; (items テーブルも同様に確認する)

(略)

mysql> quit (mysql からログアウトする)

# exit (コンテナからもログアウトする)

エクセルファイルをアップロードして、データベース化できることが確認できました。


では次に「データベースからエクセル」も試してみましょう。上の作業でエクセルファイルからデータベースを作成した直後だと、アップロードしたものと同じエクセルファイルが作成されるだけ(苦笑)ではあるのですが、データベースの内容がエクセルになる、という部分については体験できると思います。

Swagger API ドキュメントに戻り、今度は "POST /db2xlsx" のボタンをクリックします:
2023122301


API 実行時のパラメータが展開されます。今回は "POST /xlsx2db" の時よりもシンプルなパラメータになっていることがわかります。"Try it out" ボタンをクリックします:
2023122302


こちらの API ではエクセルにエクスポートしたいデータベースを database_url パラメータにデータベース接続文字列の形で指定するだけです。先ほどと同様のデータベース接続文字列を入力して "Execute" ボタンをクリックします:
2023122303


処理が成功すると "Download file" と書かれたリンクが現れます。ここをクリック:
2023122304


すると、(データベース名).xlsx というエクセルファイルがダウンロードされます。ダウンロード後に開いてみてください:
2023122305


ダウンロードしたエクセルファイルを開いてみると、items と brands という2つのワークシート(タブ)が含まれていて、それぞれのワークシート内に items テーブルと brands テーブルの内容がエクスポートされていることが確認できます。日付列は日付情報として取り出しているので少しフォーマットが変わっていますが中身(日付は)正しいはず:
2023122306

2023122307


データベースからエクセル、の REST API も正しく動くことが確認できるはずです。


【あとがき的なもの】
一部でエクセルがデータベースの代わりに使われていることを知っている身として、その状況はなんとかならんかという思いで作ってみました。REST API 化したので簡単なプログラムを書けば一括処理もできるはずです(笑)。で、どうしてもまたエクセル化する必要が生じた場合にも対応できるような逆方向の REST API も用意しているので、これでなんとかしてください。


 

このネタを見た時は「やられた!」と思いました:
手持ちの画像を「Excel」ファイルにしてしまうWebアプリ「Image to Excel」が爆誕

画像をアップロードして、そのピクセル情報を解析して(よりによって)エクセルファイル化する・・・ 素晴らしい変態アプリだと思いました(褒めてます)。 これをアイデアだけでなく実装して公開する行動力まで含めて素晴らしい姿勢だと感じました。自分もこうでありたい。。

で、その衝撃が強すぎたのか、「これ自分だったらどう作るだろうか??」と考え、特にセルの属性を変更したエクセルファイルとして出力する所は何をどうやれば実現できそうか・・ なども調べた上で、なんとか同じような機能を実装することができたと思っています。自分の場合はアプリケーションというよりは REST API として実装したので、この機能を外部アプリからも使えるような形で仕上げています(Swagger ドキュメントも用意したのでウェブブラウザの UI からも使えるはずです)。実装したものは先日このブログでも紹介した無料の PaaS 環境を使って公開しています:

ソースコード
 https://github.com/dotnsf/image2xlsx
公開した REST API(の Swagger ドキュメント)
 https://image2xlsx.140.238.50.190.sslip.io/ (使い方も後述します)


で、この機能を作る上で xlsx-js-style という npm ライブラリを使っています。このライブラリは Node.js やフロントエンド JavaScript からエクセルファイルを生成する機能を持ったライブラリです。もともとは SheetJS というライブラリ(無料)があったのですが、有料版の提供を境にして無料版のサポートが少なくなってしまったのか、更新が途絶えてしまいました。そこで SheetJS からフォークして様々な後継ライブラリが作られていったのですが、xlsx-js-style はその1つです。ただ詳しい使い方がウェブ上でもあまり見つけることができず、一部では「事実上、読み取り用のライブラリ」とも書かれていました。実際はそんなことはなくて、現に今回紹介するようなエクセルブックファイルを新規作成する上での最低限の機能を持っていました。一方で情報が少ないことも事実で、今回はソースコードの中身を含めて自分でも色々調べながらの作業となりました。その調べて分かった内容も含めて本ブログエントリにまとめておこうと思います。

なお以下で紹介する内容は 2023/12/08 時点で公開しているサービスと、そのソースコードについて紹介しています。今後の更新で多少実装内容が変わる可能性もあることをご了承ください。


【image2xlsx (の Swagger ドキュメント)の使い方】
何はともあれ公開している機能の使い方を紹介します。作ったのは REST API なんですが、Swagger ドキュメントも併せて公開しているので curl などを使わなくてもウェブブラウザで実際の挙動を確認できるように(つまり手持ちの画像をアップロードして画像化したエクセルファイルをダウンロードすることができるように)しています。ウェブブラウザでこの URL にアクセスしてみてください:
https://image2xlsx.140.238.50.190.sslip.io/

2023120701


↑上のような画面が表示されれば成功です。https スキーマを使ってアクセスしているはずなので、実際に使う前に Schemas と書かれた欄(上図の赤枠部分)を "HTTP" から "HTTPS" に変更しておいてください(このサイトのサービスを使う場合、あらかじめ HTTPS に変更しておかないと正しく実行できません)。

この Swagger ドキュメントで提供している REST API は1つだけで、"POST /image" です。緑色の "POST" と書かれた部分をクリックして展開してみましょう:
2023120702


省略されていた部分が展開されて上のような画面になったはずです。この画面で "POST /image" API にはいくつかのパラメータが指定できることがわかります。が、まずは実際に API を実行してみたいので "Try it out" と書かれたボタンをクリックします:
2023120703


パラメータ部分が編集可能状態に切り替わり、一番下には "Execute" と書かれた青いボタンが現れたはずです。これでパラメータを指定して "Execute" ボタンをクリックすると実際に REST API が動く状態になりました。パラメータは4つありますが、入力必須なのは一番下の file フィールドだけです。他はいったん無視して file フィールドのボタンをクリックして適当な画像ファイル(.jpg, .gif, .png など)を指定してください(2023/12/08 時点では 10MB 以下の画像ファイルを受け付けることができます。もっと大きなファイルを扱えるようにしたい場合は是非ソースコードをダウンロードして自分で改良してみてください)。この際に日本語名のファイルを指定するとダウンロードするエクセルファイル名が文字化けしてしまうので、(ファイル名の文字化け程度を許容できる場合はそのままでもいいですが)できれば半角英数字名だけで構成されたファイル名の画像ファイルを指定してください:
hachi
(↑hachi.jpg というこの画像ファイルを指定してみました)


ファイルを選択したら "Execute" ボタンをクリックします:
2023120704


すると指定したファイルがアップロードされ、エクセルファイル化が実行されます(実行中は "LOADING" と表示されます):
2023120705


正しく処理が完了すると "Responses" 欄が現れます。ここには実際に実行した REST API の内容(curl コマンドで実行した時のコマンドの内容)やその実行結果のレスポンスに関する情報が表示されます。処理が成功していると "Download file" と書かれたリンクが表示されていて、ここをクリックすると実行結果のエクセルファイルをダウンロードできます:
2023120706


実際に "Download file" をクリックします。ダウンロードは一瞬のはずです。ちなみにエクセルファイル名は元の画像ファイル名の拡張子部分が "xlsx" になったものです(hachi.jpg → hachi.xlsx):
2023120707


ダウンロードしたファイルを(ダブルクリックするなどして)エクセルやエクセル互換ツールで開いてみると期待通りのエクセルシートが表示されるはずです(シート名は元の画像ファイル名です)。元祖の Image to Excel と同じような結果になっているはずです:
2023120708
(↑シートに画像が貼られているのではなく、細かなセルの色を変えることで実現しています)


なお今回はアップロードする画像以外のパラメータを無視していました。これらのパラメータの意味とデフォルト値について簡単に説明しておきます:
2023120709


px パラメータは「エクセルファイル側で画像の1ピクセルを何ピクセルのセルで表現するか」という数値です。デフォルト値は「2」です。つまり何も指定しなければ画像の各ピクセルは「横 2px 縦 2px」のサイズのセルとして表示されます。この値を変えたい場合は px パラメータになんらかの数値(1とか3あたり)を入力してください。

width パラメータは「エクセルファイル側で画像の横幅を何個ぶんのセルを使って表現するか」の数です。デフォルト値は「100」です。つまり何も指定しなければアップロードされた画像の横幅サイズを 100 に縮小(または拡大)します。

同様に height パラメータは「エクセルファイル側で画像の縦幅を何個ぶんのセルを使って表現するか」の数です。デフォルト値はなく、何も指定されなかった場合は縦横比を保ったまま横サイズ(こちらのデフォルト値は 100)に合わせて縦幅が拡大縮小されます。

なお height パラメータのみ指定されて width パラメータが指定されていなかった場合は、縦横比を保ったまま縦サイズ(=height)に合わせて横幅が拡大縮小されます。

このような仕様となっていました。なので、これらを何も指定せずに実行した場合は、
・画像の1ピクセルは 2x2 px サイズのセルで表示される(そのピクセルの色がセルの色になる)
・横幅は 100px に拡大縮小され、縦幅は同じ比率で拡大縮小される
ということになります。何も指定しなければ横幅は 100 になるので、巨大な画像をアップロードしても、そこまで巨大なエクセルファイルにはならないはずです。よかったら(必要に応じてパラメータも変えたりしながら)使ってみてください。


【image2xlsx REST API の使い方】
ここからは Swagger ドキュメントの使い方というよりは REST API の使い方を説明します。改めて実行結果の Responses 部分の、"Curl" と書かれた部分を見てみます:
2023120701


file 以外のパラメータを指定せずに実行した場合はこんな感じに書かれているはずです:
curl -X POST "https://image2xlsx.140.238.50.190.sslip.io/image" -H "accept: application/vnd.ms-excel" -H "Content-Type: multipart/form-data" -F "file=@hachi.png;type=image/png"

ちなみにパラメータを指定した場合(例えば width パラメータを 200 に指定して実行した場合)はこのようになります(-F "width=200" が追加されています):
curl -X POST "https://image2xlsx.140.238.50.190.sslip.io/image" -H "accept: application/vnd.ms-excel" -H "Content-Type: multipart/form-data" -F "width=200" -F "file=@hachi.png;type=image/png"

これがこの画像ファイルをエクセルファイル化する時に実行された curl コマンドそのものでもあります。REST API としては以下のような仕様です:

・エンドポイント URL は "https://image2xlsx.140.238.50.190.sslip.io/image"
・HTTP メソッドは "POST"
・HTTP リクエストヘッダは "accept: application/vnd.ms-excel" と "Content-Type: multipart/form-data"
・HTTP リクエストボディは以下を FormData で送信
 - px パラメータでエクセルファイルのセル1つのサイズを何ピクセル平方にするか
 - width パラメータでエクセルファイルのセルを何列で表現するか
 - height パラメータでエクセルファイルのセルを何行で表現するか
 - file パラメータで画像ファイル本体(ここだけ必須)

・HTTP レスポンスはエクセルファイルのバイナリデータ

(需要があるとは思えないのですが)ここを理解した上で外部アプリからこのエンドポイントへファイルをアップロードすればエクセルファイル化してダウンロードすることもできるようになります。REST API 化したので外部連携も可能な形になっています(サーバーがそこまで潤沢な環境ではないので、お手柔らかに使ってください)。


【xslx-js-style をどのように使ったか】
ここまでの内容は「本家の Image to Excel をパクってみました」という内容でした。ここからがこのブログエントリの本体ともいえる部分だと思っていて、「これをどうやって作ったのか」という技術的な解説になります。本家のサービスがどのように作られているのかは分からないのですが、自分はこうやってみた、という紹介です。ちなみにプログラミング言語は Node.js で作っています。

改めて、私が作った image2xlsx のソースコードはこちらで公開しています。実際の実装ソースコードはここからも参照できるので興味ある方は是非どうぞ。

この "POST /image" メソッドは概ね以下のようなフローで作られています:
(1)一時フォルダにアップロードされた画像ファイルを受け取る
(2)画像ファイルを開いて、サイズ(横幅、縦幅)を調べる
(3)受け取ったパラメータなどから出力時のサイズ(横幅、縦幅)を求める
(4)Canvas ライブラリを使って仮想的なキャンバスを作り、画像を出力時のサイズで描画する
(5)エクセルワークブック(ファイル)を新規に作成する
(6)エクセルワークシート(タブ)を新規に作成する
(7)(3)で求めたサイズに合わせて画像の各ピクセルぶんのセルをワークシート内に用意する
(8)(4)の画像データを取り出し、1ピクセルごとに RGB 値を取得し、
(9)(8)の結果を各セルの背景色に指定する
(10)セルの色を指定したワークシートをワークブックに追加する
(11)ワークブックのデータを取り出し、HTTP レスポンスとして返す

以下、このフローの実装内容を順に説明していきます。まず(1)ですが、ここは multer というファイルアップロード機能を作る時の定番ライブラリがあるので multer を使って実装しています。

該当コードとしてはこのような感じです:
var express = require( 'express' ),
    bodyParser = require( 'body-parser' ),
    fs = require( 'fs' ),
    multer = require( 'multer' ),
    { createCanvas, Image } = require( '@napi-rs/canvas' ),
    XLSX = require( 'xlsx-js-style' ),
    app = express();

require( 'dotenv' ).config();
var default_px = 'DEFAULT_PX' in process.env ? parseInt( process.env.DEFAULT_PX ) : 2;
var default_width = 'DEFAULT_WIDTH' in process.env ? parseInt( process.env.DEFAULT_WIDTH ) : 100;

app.use( express.static( __dirname + '/public/_doc' ) );
app.use( bodyParser.urlencoded( { extended: true } ) );
app.use( bodyParser.json( { limit: '10mb' } ) );
app.use( express.Router() );
app.use( multer( { dest: './tmp/' } ).single( 'file' ) );

  :

app.post( '/image', function( req, res ){
  var imgpath = req.file.path;
  var outputfilename = '';
  try{
    var imgtype = req.file.mimetype;
    var filename = req.file.originalname;

    :


(2)以降で仮想キャンバス機能を使います。フロントエンド JavaScript では HTML5 の Canvas に相当する機能なのですが、node-Canvas というバックエンド用の互換ライブラリ(のプラットフォーム非互換性を解消した @napi-rs/canvas というライブラリ)を使っています。この canvas でアップロードされた画像ファイルを読み込み、横幅と縦幅を取り出します。

(3)は、画像ファイルと一緒に送られてきたサイズに関するパラメータを使い(送られていない場合はデフォルト設定値を使い)、必要に応じて元画像の縦横比に合わせる形で計算も行って、エクセルファイル内のセルの横幅数と縦幅数を求めておきます。

この(2)と(3)に相当する部分のコードはこのようになっています:
    :
    //. 画像解析
    var data = fs.readFileSync( imgpath );
    var img = new Image;
    img.src = data;

    var width = req.body.width ? parseInt( req.body.width ) : -1;
    var height = req.body.height ? parseInt( req.body.height ) : -1;
    if( width < 0 && height < 0 ){
      width = default_width;  //. サイズの指定が何もない場合、幅100とする
      height = width * img.height / img.width;
    }else if( width < 0 ){
      width = height * img.width / img.height;
    }else if( height < 0 ){
      height = width * img.height / img.width;
    }

    :


(4)では仮想的な Canvas を作って、Canvas 内に元画像を元のサイズから出力時のサイズに拡大・縮小したものを表示(仮想的な Canvas なので実際に表示されるわけではないですが)しています。Canvas に描画されたデータはピクセルごとの情報(今回の場合であれば RGB 値)を取り出して調べることができるようになります。

この部分のコードはこのようにしています:
    :
    //. リサイズして仮想キャンバスに描画
    var canvas = createCanvas( width, height );
    var ctx = canvas.getContext( '2d' );
    ctx.drawImage( img, 0, 0, img.width, img.height, 0, 0, width, height );

    //. 描画した画像のデータを取得
    var imagedata = ctx.getImageData( 0, 0, width, height );
    :

ここまでは(アップロードされた画像を拡大縮小して、各ピクセル毎の情報を取り出すための準備までは) xlsx-js-style ライブラリを使わずに実装しています。ここからが xlsx-js-style ライブラリの出番です。

(5)この後、各ピクセル毎の情報を調べていくのですが、その前にワークブックを新規に作成します。「ワークブック」=「エクセルファイル」と考えると理解しやすいと思いますが、ここでの「新規作成」の意味は「新しいエクセルファイルを作って、まだ保存していない状態にする」と思っていてください。なおこの時点ではワークブック内に1つもタブが存在していない状態で作成されています。

xlsx-js-style でワークブックを新規作成するには utils.book_new() というメソッドを実行します:
    :
    //. ワークブックを作成
    var wb = XLSX.utils.book_new();
    :

(6)ではワークブックに追加することになるワークシート(タブ)を新規に1つ作成します。

xlsx-js-style でワークシートを新規作成するには utils.aoa_to_sheet() というメソッドを実行します。引数にワークシート内の各列の情報を含めることができますが、この時点では特に指定せずに作成しています:
    :
    //. ワークシートを作成
    var row = [[]];
    var ws = XLSX.utils.aoa_to_sheet([row]);
    :


(7)はワークシート内で使うことになるセルの列数と行数(つまり画像の横幅と縦幅)の分だけセルのサイズを調整します。

ここでの「セルのサイズ」とは「REST API 実行時に px パラメータとして指定された値(指定されていない場合は default_px の値(=2)」です。また「セルの列数」とは「Canvas 上に表示されている画像の横幅(=imagedata.width)」で、「セルの行数」とは「Canvas 上に表示されている画像の縦幅(=imagedata.height)」のことです。したがって「セルの列数の分だけセルのサイズを調整する」コードは以下のようになります。具体的には各列の幅を { wpx: (セル幅のピクセル数)} というオブジェクトで定義する配列を用意してワークシートオブジェクトの '!cols' 値として設定し、各行の幅は [ hpx: (セル高さのピクセル数) } というオブジェクトで定義する配列を用意してワークシートオブジェクトの '!rows' 値として設定します。具体的には以下のようなコードになります:
    :
    //. 必要なセルの行数と列数ぶんだけ、幅と高さを指定する
    var px = req.body.px ? parseInt( req.body.px ) : default_px;
    var wscols = [];
    for( var x = 0; x < imagedata.width; x ++ ){
      wscols.push( { wpx: px } );
    }
    var wsrows = [];
    for( var y = 0; y < imagedata.height; y ++ ){
      wsrows.push( { hpx: px } );
    }

    ws['!cols'] = wscols;
    ws['!rows'] = wsrows;
    :

(8)では Canvas 内の各ピクセルにアクセスして RGB 値をそれぞれ取り出し、16進法の "RRGGBB" フォーマットに変換して、

(9)その "RRGGBB" 値をワークシート内の対応するセルの背景色として指定してゆきます。

この(8)と(9)はループ内で以下のように実現しています。ピクセルの RGB 値は Canvas のデータ内に4つごとに(R 値、G 値、B 値、輝値)格納されているのでそれぞれを取り出し、この値を "RRGGBB" というフォーマットに変えて、"A1", "A2", ... といったセルの色として指定しています。また cellname という関数を用意して縦位置、横位置からセル名に変換できるようにしています(この後の(10)でも使っています):
    :
    //. 1ピクセルずつ取り出して色を調べる
    for( var y = 0; y < imagedata.height; y ++ ){
      for( var x = 0; x < imagedata.width; x ++ ){
        var index = ( y * imagedata.width + x ) * 4;
        var rr = imagedata.data[index];    //. 0 <= xx < 256
        var gg = imagedata.data[index+1];
        var bb = imagedata.data[index+2];
        var alpha = imagedata.data[index+3];

        var rrggbb = rgb2hex( rr, gg, bb );
        var cname = cellname( x, y );
        ws[cname] = { v: "", f: undefined, t: 's', s: { fill: { fgColor: { rgb: rrggbb } } } };
      }
    }
    :
    :

function color2hex( c ){
  var x = c.toString( 16 );
  if( x.length == 1 ){ x = '0' + x; }
  return x;
}

function rgb2hex( r, g, b ){
  return color2hex( r ) + color2hex( g ) + color2hex( b );
}

function cellname( x, y ){
  //. String.fromCharCode( 65 ) = 'A';
  var c = '';
  var r = ( y + 1 );

  while( x >= 0 ){
    var m = x % 26;
    x = Math.floor( x / 26 );

    c = String.fromCharCode( 65 + m ) + c;
    if( x == 0 ){
      x = -1;
    }else{
      x --;
    }
  }

  return ( c + r );
}

(10)(7)、(8)、(9)の処理によって変更されたワークシートの内容をワークブックに反映します。これでレスポンスとして返すエクセルファイル(のデータ)が完成しました。

このコードでは実際に使ったセルの範囲を "A1:(最も右下にあるセルの名前)"  という形でワークシートの '!ref' 値に指定してから、作成したワークシートをワークブックに追加する、という処理を実行しています:
    :
    //. 値が適用される範囲を指定
    var cname = cellname( imagedata.width - 1, imagedata.height - 1 );
    ws['!ref'] = 'A1:' + cname;

    //. ワークシートをワークブックに追加
    XLSX.utils.book_append_sheet( wb, ws, filename );
    :


(11)最後に完成したエクセルファイルを HTTP リクエストに対する HTTP レスポンスとして返します。

ここでは、これまでの処理で作成したワークブックのバッファデータを取得して HTTP レスポンスヘッダと合わせる形で返す、ということになるのですが、xlsx-js-style のソースコードを調べてもワークブックのバッファデータを直接取得する関数は用意されていないようでした。というわけで、一旦ワークブックファイルとして書きだし、ワークブックファイルを読み込んだ上で返す(そしてワークブックファイルをアップロード画像と一緒に消す)、というアルゴリズムにしています:
    :
    //. xlsx化
    XLSX.writeFileSync( wb, 'tmp/' + outputfilename );

    res.contentType( 'application/vnd.ms-excel' );
    res.setHeader( 'Content-Disposition', 'attachment; filename="' + outputfilename + '"' );
    var bin = fs.readFileSync( 'tmp/' + outputfilename );
    res.write( bin );
    res.end();
    :

・・と、このような形で xlsx-js-style を使ってエクセルファイルをバックエンドで作成することができました。グラフとかまでは難しそうではありますが、セルのサイズや色を指定したワークシートを作るという程度であれば実現できないことはなさそうで、今後エクセル連携が必要になった場合でも使えそうなライブラリでした。





僕のことを個人的に知ってる人はご存知かもしれませんが、自分は今から25年ちょっと前に「ロータス株式会社」という企業でアプリケーション開発を担当していました。具体的に開発を担当していた製品の中には、昭和を代表する(苦笑)表計算ソフト 1-2-3(「ワンツースリー」)も含まれていました。ここ10年くらいはウェブアプリばかり作っていますが、まだ頭(や性格)がキレキレだった頃はパソコン向けのネイティブアプリも作っていた時代がありました。

自分が開発を直接担当していたのは Microsoft Windows 向けの 32bit 版でしたが、当時はまだ PC-DOS 版を無理やり Windows 向けに拡張した 16bit 版や、PC-DOS 版自体も 1995 年まではバージョンアップ対応をしていました。そんなこともあって当時の担当製品は今でも仮想マシン(VM)の形で残していたりします。今となっては貴重な PC-DOS/V(V7) の稼働環境だと思っています:
2022060400


※ついでにコメントしておくと、Lotus Notes バージョン1などの OS/2 資産も VM 化して残しています。自分が作った製品への愛は高めだと自認しています。


この PCDOS VM の中に懐かしいツール類と一緒に Lotus 1-2-3 R2.5J がインストールされています(赤く囲っているのが当時悪名高かったライセンスファイルです):
2022060401


今回、この環境を持ち出したのは理由があります。先日、スラドにこのようなタレコミがあったのを発見しました:
Lotus 1-2-3、Linux に移植される

(自分も 1-2-3 愛は大きい方だと思ってますが)自分も勝てなさそうな 1-2-3 愛を持っていそうな Tavis 氏がインターネット・アーカイブ転がっていた保存されていた SysV 版の 1-2-3 for UNIX を使って Linux 版(elf 版)を作ることに成功した、というものでした。未だに 1-2-3 への愛を持っているのは自分くらいかと思っていたのですが、世界は広いです。 Tavis さん、Good-job! d(o^


(横道)上記画像でファイラーと呼ばれていた FD を使ったスクリーンショットを掲載していますが、実は FD は Linux に移植済みだったりします。興味ある方はこのコマンド(Ubuntu 向け)を試してみてください:
$ sudo apt install fdclone

$ fd
2022060411



(横道おわり)で、そんな記事に触発されたというわけではないのですが、自分も久しぶりに上記 PC-DOS 版 1-2-3 環境を使ってせっかくなので何か試してみようと思いました。実は以前から気になっていたことがあって、それを検証する機会とさせていただきました。

その気になっていたことというのは、みなさんも一度は耳にしたことがあると思うのですが「ソフトウェア製品に実装された機能の多くは使われない」というものです。製品ベンダーとしては利用者からの要望を形にして様々な機能を実装しているのですが、ほとんどのケースでその多くは使われることがない、というものです。そういう自分も、例えばプログラミングエディタに VSCode を使っていますが、自分に必要な機能は当然のように知ってますが、正直知らない機能が多くあります(良く言えば「困ったときにすぐに調べることができるので、使わない間は知らなくてもよい」わけです)。現在、表計算ソフトといえば「エクセル」ですが、このエクセルも(特に最近のバージョンは)非常に多くの機能が付与されていて、噂では方眼紙としても使うことができるその全てを熟知している人って存在するのだろうか??とすら思ってしまうほどです。

エクセルを「最低限」(この定義が難しい)使えるようになるにはどんなことを知っていればいいんだろう?? という需要もあるようで、YouTube を見ていてもエクセルの基本機能を紹介するものが多く見つかります。

・・・と、そこで閃きました。現在のエクセルで「基本」とされている機能って、Lotus 1-2-3 の頃にはどの程度存在していたのだろうか? という命題です。特に他意はないのですが、この手のエクセル基本機能を調べようとググった結果の上位にあったもので、YouTube でエクセルの基本機能等を紹介しているシリーズを見つけ、そのシリーズ中の「【Excel基本】初心者が最初に覚える関数12選」の中で紹介されている12個の関数を基本機能とみなして、これらの関数が DOS 版 Lotus 1-2-3 の中の関数としてどの程度カバーされていたのか、を検証してみることにしました(勝手に使わせていただきました m(__)m):
https://www.youtube.com/watch?v=cfGKFhSE6uA



なお、この動画で紹介されていた関数12選は以下のような内容でした:
1 SUM関数
2 AVERAGE関数
3 COUNT関数
4 COUNTA関数
5 IF関数
6 COUNTIF関数
7 COUNTIFS関数
8 SUMIF関数
9 SUMIFS関数
10 VLOOKUP関数
11 IFERROR関数
12 XLOOKUP関数


比較対象は Windows 版の Lotus 1-2-3 と DOS 版の Lotus 1-2-3 を・・・と考えていたのですが、今回の12個の関数について検証した結果としてはこの2つに差異はありませんでした(Windows 版に存在している関数は DOS 版にも存在していて、Windows 版に存在していない関数は DOS 版にもありませんでした)。ということもあって、以下では DOS 版での検証結果のみを紹介します。


検証方法としては、上記の YouTube 動画で「エクセルの基本関数」として紹介されている機能と互換性のある関数が DOS 版の Lotus 1-2-3 でも存在しているかどうかを確認する、という形としました。なお動画内で対象としているエクセルは最新の 2021 で、私が検証に使ったのは DOS 版 Lotus 1-2-3 R2.5J というバージョンです。このバージョン自体は 1995 年にリリースされた、DOS の日本語対応版としては最終バージョンですが、基本的な設計は R2 から変わっておらず、1986 年の設計を引き継いでいます(つまり昭和時代のソフトウェアです)。


【検証準備】
(VM 内の)PC-DOS に Lotus 1-2-3 R2.5J をインストールし、適当な表を作った上で関数の検証を行います:
2022060402


【検証1 SUM関数】
指定エリア内の数値を合計する、という(本当の意味で基本的な)関数です。これは Lotus 1-2-3 R2.5J では @SUM 関数として実装されていました(下図の赤枠部分参照)。なお Lotus 1-2-3 ではマクロ関数は全て @XXX という形式になっていて、「アット関数」と呼ばれています:
2022060403


【検証2 AVERAGE関数】
指定エリア内の数値の平均値を求める関数です。これも Lotus 1-2-3 R2.5J では @AVG 関数として実装されていました(下図の赤枠部分参照):
2022060404


【検証3 COUNT関数】
指定エリア内に数値のセルがいくつ存在するかをカウントする関数です。これも Lotus 1-2-3 R2.5J では @PURECOUNT 関数として実装されていました(下図の赤枠部分参照)。1-2-3 にも @COUNT 関数は存在しているのですが少し用途が違っていました:
2022060405



【検証4 COUNTA関数】
指定エリア内に空白ではないセルがいくつ存在するかをカウントする関数です。これも Lotus 1-2-3 R2.5J では @COUNT 関数として実装されていました(下図の赤枠部分参照)。1-2-3 の @COUNT 関数はエクセルの COUNTA 関数で、エクセルの COUNT 関数は 1-2-3 の @PURECOUNT 関数として存在しているのですが少し用途が違っていました:
2022060406


【検証5 IF関数】
条件分岐処理をする関数です。これは Lotus 1-2-3 R2.5J でも @IF 関数として実装されていました(下図の赤枠部分参照):
2022060407


【検証6 COUNTIF関数】
指定エリア内に条件を満たすセルの数かいくつ存在するかをカウントする関数です。これは Lotus 1-2-3 R2.5J には互換関数が存在していません


【検証7 COUNTIFS関数】
指定エリア内に複数の条件を満たすセルの数かいくつ存在するかをカウントする関数です。これは Lotus 1-2-3 R2.5J には互換関数が存在していません


【検証8 SUMIF関数】
指定エリア内で条件を満たすセルだけを対象とした SUM 関数です。これは Lotus 1-2-3 R2.5J には互換関数が存在していません


【検証9 SUMIFS関数】
指定エリア内で複数の条件を満たすセルだけを対象とした SUM 関数です。これは Lotus 1-2-3 R2.5J には互換関数が存在していません


【検証10 VLOOKUP 関数】
指定エリア内で特定の値をもつ列を見つけた場合に、指定された別の列の値を求める関数です。これは Lotus 1-2-3 R2.5J でも @VLOOKUP 関数として実装されていました(下図の赤枠部分参照):
2022060409



【検証11 IFERROR 関数】
エラーが発生した場合の処理を指定する関数です。これは Lotus 1-2-3 R2.5J には互換関数はありませんでしたが @IF 関数と @ISERR 関数を組み合わせる形で同様の機能を実装できました(下図の赤枠部分参照):
2022060408


【検証12 XLOOKUP関数】
VLOOKUP 関数の上位互換となる関数です。エラー発生時の処理も関数内に含めることができます。これはエクセル 2021 で登場した新関数でもあり、Lotus 1-2-3 R2.5J には互換関数が存在していません。


【検証結果まとめ】
というわけで、検証結果をまとめるとこのようになりました:
#エクセルの関数1-2-3 の互換関数
1SUM@SUM
2AVERAGE@AVG
3COUNT@PURECOUNT
4COUNTA@COUNT
5IF@IF
6COUNTIF -
7COUNTIFS -
8SUMIF -
9SUMIFS -
10VLOOKUP@VLOOKUP
11IFERROR@IF と @ISERR
12XLOOKUP -


エクセルの基本関数12個のうち、6個は互換関数が(くどいようですが昭和時代の)1-2-3 にも存在していて、1つは他の関数の組み合わせでなんとかなる、という感じでした。「現在の基本」のうちの半分くらいは当時から使えていた、ということになります。1-2-3 は「条件付きで○○をする」という関数が全般的に弱い感じがしますが、逆に言うとそのような需要があってエクセルで実装されていったのだと思われます。特筆したいのは VLOOKUP 関数で、これは今でも中級者以上が比較的使う機会の多い関数だと思っていますが、その関数が 1-2-3 でも実装されていたんですね。オーパーツのような話で、私も少し驚きました。



なお、今回のブログエントリを書く際の参考文書はこちらでした。今となっては貴重品な、ナツメ社のハンドブックシリーズです:
IMG_20220530_080350_947



以前にこのブログで「Node.js からエクセルシートを操作する」というタイトル&内容の記事を書いて紹介しました:
Node.js からエクセルシートを操作する


この記事の中では XLSX という npm のライブラリを用いてエクセルファイルからセルの中身を読みとったり、セルに値を代入して保存する、という手法を紹介しました。 XLSX は対応フォーマットも多く、使う機会も多いのではないかと思っていました。

が、その後いろいろ使っていく中で XLSX が苦手とするケースも出てきました。典型的な例としてはセルのフォーマットを保持したままエクセルファイルを更新したい場合です。

具体的にはこんなケースが考えられます。以下のようなシート一枚のエクセルファイル(template.xlsx)が存在していたと仮定します:
2018111301


同シート内の A1 セルと B1 セルは結合しています。加えて背景色が緑色で、フォントは太字、中央揃えに設定されています。

これらの条件を残しながら A1 セルの内容を別の文字列に更新したい、というのが今回の用件であるとします。もちろん人間がマニュアル操作で更新することはたやすいのですが、これを人手を介さずにプログラマティックに行いたい、というものです。


では、この処理を XLSX ライブラリを使って行ってみます。コード(test01.js)としてはこのような内容になります:
// test01.js

var XLSX = require( 'xlsx' );

var ts = ( new Date() ).getTime();
var str = "かきくけこ " + ts;


//. テンプレート
var templatefile = './template.xlsx';

//. 出力先
var xlsfile1 = './xlsx_' + ts + '.xlsx';

//. テンプレートから読み込み
var book1 = XLSX.readFile( templatefile );

//. 「Sheet1」シート
var sheet1 = book1.Sheets["Sheet1"];
sheet1["A1"] = { v: str, t:'s', w: str };
book1.Sheets["Sheet1"] = sheet1;

XLSX.writeFile( book1, xlsfile1, { type: 'xlsx' } );
console.log( 'saved into ' + xlsfile1 );

この例では template.xlsx を読み込んだあと、実行時のタイムスタンプ値を取得し、Sheet1 シートの A1 セルの内容を「あいうえお」から「かきくけこ(タイムスタンプ値)」に書き換えた上で xlsx_(タイムスタンプ値).xlsx というファイルで保存するようにしています。

このコードを Node.js で実行してみます:
$ node test01

実行した結果、xlsx_(タイムスタンプ値).xlsx というファイルができあがります。このファイルをエクセルで開いてみると、このような見た目になりました:

2018111302


A1 セルと B1 セルの結合の情報は保持していたのですが、背景色やフォントの太字の情報は消えていました。また中央揃えは無効になっていました。

このようにセルに定義されたフォーマットを保持したままエクセルファイルを更新しようとすると、XLSX ライブラリでは限界があるようでした。


で、これをどうすればよいか、というのが今ブログエントリのメインテーマです。結論としては XLSX ライブラリではなく、XLSX-populate という npm ライブラリを使うことで解決できそうでした:
2018111500


XLSX-populate ライブラリを使って上記のコードを書き換えたものがこちら(test02.js)です:
// test02.js

var fs = require( 'fs' );
var XlsxPopulate = require( 'xlsx-populate' );

var ts = ( new Date() ).getTime();
var str = "さしすせそ " + ts;

//. テンプレート
var templatefile = './template.xlsx';

//. 出力先
var xlsfile2 = './xlsx_populate_' + ts + '.xlsx';

//. テンプレートから読み込み
XlsxPopulate.fromFileAsync( templatefile ).then( book2 => {
  //. 「Sheet1」シート
  var sheet2 = book2.sheet(0);
  sheet2.cell( "A1" ).value( str );

  book2.toFileAsync( xlsfile2 ).then( result => {} );
  console.log( 'saved into ' + xlsfile2 );
});

先程の test01.js とほぼ同様の処理を行っています。違いは A1 セルに上書きする内容を「さしすせそ(タイムスタンプ値)」として、出力ファイル名は xlsx_populate_(タイムスタンプ値).xlsx としています。

そしてこのファイルを実行します:
$ node test02

結果はこちら:

2018111303


ちょっとわかりにくいのですが、A1 セルと B1 セルの結合はもちろん、背景色や太字フォント、中央揃えの情報は全て保持されたまま更新ができています:
2018111304


もちろん、XLSX populate が万能、というわけではないのですが、Node.js でファイルの更新処理を行う場合には向いているライブラリだと思います。


「エクセルファイルを扱えるライブラリ」といえば、Java であれば Apache POI などがありますが、Node.js ではどうだろう?? と思って調べてみました。その名もズバリの xlsx という npm ライブラリを見つけたので使ってみました:

npm - xlsx
https://www.npmjs.com/package/xlsx

2018080100


ライブラリ名は xlsx ですが、対応フォーマットは xls や XML に加えて ODS まで含まれていて、かなり柔軟に使えそうです。


【扱うサンプル】
こんな感じのエクセルファイルを用意して使うことにします:
2018080101


データとしては "A1:C14" の範囲にまとまっていて、その右に2軸の折れ線グラフが1つあります。この表の B14 セルは B2:B13 の合計(SUM)、C14 セルは C2:C13 の平均値(AVERAGE)がマクロで定義されています。まあ「よくあるシート」だと思っていますが、このエクセルファイルを xlsx で扱ってみます。ちなみに同じファイルがこちらからダウンロードできます:


【読み込み例】
まず npm で xlsx ライブラリをインストールします:
$ npm install xlsx

そして xlsx ライブラリを使ってエクセルファイルを読み込む Node.js コードを作成します。この例ではファイル名を指定して読み込み、"Sheet1" シートを取得して console.log() で出力しています:
var XLSX = require( 'xlsx' );

// ファイル読み込み
var book = XLSX.readFile( './SalesSample.xls' );

// シート
var sheet1 = book.Sheets["Sheet1"];
console.log( sheet1 );


実行結果はこんな感じになります。ダンプされたような感じです:
{ '!margins':
   { left: 0.7,
     right: 0.7,
     top: 0.75,
     bottom: 0.75,
     header: 0.3,
     footer: 0.3 },
  B1: { v: '売上', t: 's', w: '売上' },
  C1: { v: '前年比', t: 's', w: '前年比' },
  A2: { v: 1, t: 'n', w: '1' },
  B2: { v: 7370, t: 'n', w: '7370' },
  C2: { v: 0.87, t: 'n', w: '0.87' },
  A3: { v: 2, t: 'n', w: '2' },
   :
   :
  A12: { v: 11, t: 'n', w: '11' },
  B12: { v: 24380, t: 'n', w: '24380' },
  C12: { v: 0.812, t: 'n', w: '0.812' },
  A13: { v: 12, t: 'n', w: '12' },
  B13: { v: 28283, t: 'n', w: '28283' },
  C13: { v: 0.814, t: 'n', w: '0.814' },
  B14: { v: 156518, t: 'n', f: 'SUM(B2:B13)', w: '156518' },
  C14:
   { v: 0.8603333333333333,
     t: 'n',
     f: 'AVERAGE(C2:C13)',
     w: '0.860333333' },
  '!protect': false,
  '!ref': 'A1:C14',
  '!objects': [ , , , , { cmo: [Object], ft: [Object] } ] }

この結果の見方を少し説明します。例えば sheet1["!ref"] の値は 'A1:C14' となっていて、このシートの中で有効なセルとして認識されている範囲は A1:C14 とされています。つまりグラフ部分は完全に無視されていて、このライブラリでは現在は扱えない情報ということになります。xlsx はあくまで表部分の読み書きを対象としています。

次に sheet1["C2"] の値は { v: 0.87, t: 'n', w: '0.87' } となっています。この意味は以下のようになります:
 v: 0.87(数値としての値は 0.87)
 t: 'n'(数値のセルとして認識されている)
 w: '0.87'(表示されているテキストは '0.87')

なお、t の値は以下のいずれかになります:
 b: Boolean
 n: 数値
 d: 日付時刻
 s: 文字列
 z: スタブ
 e: エラー

したがって sheet1["C2"] の { v: 0.87, t: 'n', w: '0.87' } の意味は「値が 0.87 の数値セルで、画面上では '0.87' と表示されている」ということになります。

また sheet1["B14"] の値は { v: 156518, t: 'n', f: 'SUM(B2:B13)', w: '156518' } となっています。この中の f: 'SUM(B2:B13)' は値が数式で定義されていて、その式が SUM(B2:B13) であることを意味しています(sheet1["C14"] も同様です)。表としてはこのレベルで各セルの値を取得できている、ということがわかります。

この読み込み例のサンプル(app1.js)はこちらからダウンロードできます。こちらのファイルは(後述の書き込み機能の結果を確認できるように)上記のコードに少し機能を追加していて、コマンドラインパラメータで読み込むエクセルファイルを指定可能にしています(無指定の場合は SalesSample.xls を読み込みます):
$ node app1.js (xxx.xls)


【書き込み例】
xlsx ライブラリを使ってファイルの書き込みを行うサンプルです。この例では SalesSample.xls を読み込んだあとに "C13" セルの値を上書きして、SalesSample2.xlsx というファイル名で保存しています:
var XLSX = require( 'xlsx' );


// ファイル読み込み
var book = XLSX.readFile( './SalesSample.xls' );

// シート
var sheet1 = book.Sheets["Sheet1"];
console.log( sheet1 );

// セル更新
sheet1["C13"] = { v: 1.01, t: 'n', w: '1.01' };

// シート更新
book.Sheets["Sheet1"] = sheet1;

// ファイル書き込み
XLSX.writeFile( book, './SalesSample2.xlsx', { type: 'xlsx' } );


(注1 最後の XLSX.writeFile() 実行時の最後のオプション { type: 'xlsx' } を指定しないとマクロ関数が無効な状態で保存されてしまいます)

この書き込みのサンプル(app2.js)はこちらからダウンロードできます。実行はそのまま node コマンドで実行します:
$ node app2.js

実行すると SalesSample2.xlsx というファイルが出来ているはずです。試しにこのファイルをエクセルで開いてみるとこのようになります:
2018080201



"C13" セルの値は { v: 1.01, t: 'n', w: '1.01' } に上書きしましたが、たしかに 0.814 から 1.01 に更新されています。同時に "C14" セルの値も(AVERAGE関数が再計算されて)変わっています。一方でグラフが完全に消えてしまいました。まあ読み込みを実行した時点でグラフの情報は消えていたので、その内容で保存するとこのようになってしまうのだと思います。xlsx ライブラリの制限事項になると思いますが、実際に使う際にはご注意ください。

(注2 厳密には SalesSample2.xlsx ファイルが生成された時点では C14 セルの値は変わっていませんが、このファイルをエクセルで開くと AVERAGE 関数が再計算されて開くので、そこで値が正しく変わったように見えます)


以上、詳細は本家のドキュメントを参照いただきたいのですが、少なくともグラフを操作せずにシートの中身を取り出す用途であれば充分につかえて、対応フォーマットも多そうだな、、という印象を持っています。自然言語処理機械学習の学習データとしてエクセル資産を活用する、なんて話になった時に活躍できそうなライブラリですね。



このページのトップヘ