タグ：xlsx

2024/07/23

エクセルファイルの CLI ビューワ

コメント数:
0 コメント
by dotnsf

こんなツールが私以外に需要あるかどうかわからなかったのですが、作った本人である自分は結構使う機会があるので公開することにします。

ツールの用途を一言でいうと「エクセルファイル（xls, xlsx）のテキストビューワ」です。ターミナルやコマンドプロンプトで作業している時に、ふと「このエクセルファイルの中身なんだっけ？」と確認したくなることがあります。多くは業務に必要な情報がエクセルファイルの中に格納されているケースで、「エクセルを起動して編集したいわけじゃないけど、ちょっと内容を確認したい」という場合がほとんどです。あと人によってはエクセルをインストールしているとも限らないので、LibreOffice などの代替製品をインストールしていたりするんだけど、起動に時間がかかって面倒だったり・・・　理由は様々あると思いますが、「ちょっとした理由でエクセルファイルの中身だけ確認したい」けど「ターミナル作業中にアプリケーションを切り替えるのが面倒」といった、自分にはよくあるケースで役立つと考えています。

ツールそのものはこちらからダウンロードできるようにしておきました。自作の Node.js アプリですが、先日このブログでも紹介した pkg を使って単体で実行可能なバイナリ化しました（Linux, Windows, MacOS 用。ただし MacOS 用は未検証です）。リンク先から自分の PC プラットフォームに合うものを選んでダウンロードしてください。ダウンロード後は PATH の通ったフォルダに "xls-viewer" という名前で（Windows の場合は "xls-viewer.exe" という名前で）保存しておくとコマンドプロンプトやターミナルから直接実行できるようになります。この名前で保存しておくことで、どのプラットフォーム向けであっても "xls-viewer" というツール名で実行できるようになり、以下ではこの前提で説明を続けます（ファイル名を変えずに保存した場合は自分のプラットフォーム向けのファイル名に読み替えてください）。

【エクセルファイルのサンプル】
以下でその使い方を紹介しますが、このサンプル・エクセルファイルを使った例を紹介します。こちらも併せてダウンロードしておいてください：
https://github.com/dotnsf/xls-viewer/raw/main/sample01.xlsx

ちなみにこのサンプル・エクセルファイル sample01.xlsx の中身をエクセルそのもので開くと以下のようなものです。２つのシート Sheet1 と Sheet２（２は全角文字）から構成されています。Sheet1 は（実は１箇所だけ式を使ってますが）特に文字装飾要素も使わない、ごくシンプルなシートです：

一方 Sheet２は式も使っていますし、日本語や文字装飾も併用しています。またわざと空の行や空の列も含めています。このエクセルファイルをサンプルとして xls-viewer を使う例を以下で紹介します：

【xls-viewer の使い方】
以下で xls-viewer の使いかたを紹介します。準備として xls-viewer（ファイル名を変えていない場合は元のファイル名に読みかえてください）を PATH の通ったフォルダ（/usr/local/bin など）に配置してください。そしてコマンドプロンプトやターミナルを開き、サンプルエクセルファイル sample01.xlsx のあるフォルダに移動しておきます。

まずは単純に（オプションなしで）実行して見ます。xls-viewer に続いて対象のエクセルファイル名（今回の場合は sample01.xlsx）をフルパスまたは相対パスで指定して実行します。今回は実行時のパスに sample01.xlsx が存在している想定なので、パス指定なしでも実行できるはずです。成功すると以下のような表示になります（見ている人の環境によっては変な所で改行されているように見えるかもしれませんが、実際には必要な半角スペースのパディングなども加えて正しく改行されている、はず）。各シートから表示されているテキスト情報を抜き出して表示しています：

$ xls-viewer sample01.xlsx
Sheet1 :
+----+------+------+---+--+-----+
| 1  | 3    |      |   |  |     |
+----+------+------+---+--+-----+
| 3  | 2    | safa |   |  | saf |
+----+------+------+---+--+-----+
| 2  | sa   |      |   |  |     |
+----+------+------+---+--+-----+
| 43 | safd |      |   |  |     |
+----+------+------+---+--+-----+
|    |      |      | 5 |  |     |
+----+------+------+---+--+-----+

Sheet２ :
+----------------------+--+----------------------+--+------------+
| 少し長めのテキストを |  |                      |  | 太字       |
| 入力                 |  |                      |  |            |
+----------------------+--+----------------------+--+------------+
|                      |  | 更に123を追加したテ  |  | Italic     |
|                      |  | キスト               |  |            |
+----------------------+--+----------------------+--+------------+
|                      |  |                      |  | 色付き     |
+----------------------+--+----------------------+--+------------+
| aaa                  |  | 3                    |  | 下線       |
+----------------------+--+----------------------+--+------------+
|                      |  | 6                    |  |            |
+----------------------+--+----------------------+--+------------+
|                      |  |                      |  |            |
+----------------------+--+----------------------+--+------------+
|                      |  |                      |  |            |
+----------------------+--+----------------------+--+------------+
|                      |  |                      |  | ２行とばし |
+----------------------+--+----------------------+--+------------+
|                      |  |                      |  |            |
+----------------------+--+----------------------+--+------------+
|                      |  |                      |  |            |
+----------------------+--+----------------------+--+------------+
|                      |  |                      |  |            |
+----------------------+--+----------------------+--+------------+
| ３行とばし           |  |                      |  |            |
+----------------------+--+----------------------+--+------------+

指定したファイル（sample01.xlsx）に含まれている２つのシート毎に結果が表示されています。このように特にオプションを指定しない場合は全てのシートを対象に表示します。

特定のシートだけを対象にしたい場合は、"--sheets=（シート名）" というオプションで指定します。チート名は半角カンマ(,)で分けて複数指定することも可能です（Sheet２の "２" は全角なので注意してください）：

$ xls-viewer --sheets=Sheet２ sample01.xlsx
Sheet２ :
+----------------------+--+----------------------+--+------------+
| 少し長めのテキストを |  |                      |  | 太字       |
| 入力                 |  |                      |  |            |
+----------------------+--+----------------------+--+------------+
|                      |  | 更に123を追加したテ  |  | Italic     |
|                      |  | キスト               |  |            |
+----------------------+--+----------------------+--+------------+
|                      |  |                      |  | 色付き     |
+----------------------+--+----------------------+--+------------+
| aaa                  |  | 3                    |  | 下線       |
+----------------------+--+----------------------+--+------------+
|                      |  | 6                    |  |            |
+----------------------+--+----------------------+--+------------+
|                      |  |                      |  |            |
+----------------------+--+----------------------+--+------------+
|                      |  |                      |  |            |
+----------------------+--+----------------------+--+------------+
|                      |  |                      |  | ２行とばし |
+----------------------+--+----------------------+--+------------+
|                      |  |                      |  |            |
+----------------------+--+----------------------+--+------------+
|                      |  |                      |  |            |
+----------------------+--+----------------------+--+------------+
|                      |  |                      |  |            |
+----------------------+--+----------------------+--+------------+
| ３行とばし           |  |                      |  |            |
+----------------------+--+----------------------+--+------------+

デフォルト指定だと列名や行番号は表示されません。表示したい場合は "--label=1" オプションを付けて実行します：

$ xls-viewer --sheets=Sheet２ --label=1 sample01.xlsx
Sheet２ :
+====+======================+===+======================+===+============+
|    | B                    | C | D                    | E | F          |
+====+======================+===+======================+===+============+
| 2  | 少し長めのテキストを |   |                      |   | 太字       |
|    | 入力                 |   |                      |   |            |
+====+----------------------+---+----------------------+---+------------+
| 3  |                      |   | 更に123を追加したテ  |   | Italic     |
|    |                      |   | キスト               |   |            |
+====+----------------------+---+----------------------+---+------------+
| 4  |                      |   |                      |   | 色付き     |
+====+----------------------+---+----------------------+---+------------+
| 5  | aaa                  |   | 3                    |   | 下線       |
+====+----------------------+---+----------------------+---+------------+
| 6  |                      |   | 6                    |   |            |
+====+----------------------+---+----------------------+---+------------+
| 7  |                      |   |                      |   |            |
+====+----------------------+---+----------------------+---+------------+
| 8  |                      |   |                      |   |            |
+====+----------------------+---+----------------------+---+------------+
| 9  |                      |   |                      |   | ２行とばし |
+====+----------------------+---+----------------------+---+------------+
| 10 |                      |   |                      |   |            |
+====+----------------------+---+----------------------+---+------------+
| 11 |                      |   |                      |   |            |
+====+----------------------+---+----------------------+---+------------+
| 12 |                      |   |                      |   |            |
+====+----------------------+---+----------------------+---+------------+
| 13 | ３行とばし           |   |                      |   |            |
+====+----------------------+---+----------------------+---+------------+

上のラベルを付けた結果を見るとわかりますが、対象のシート内の有効なエリアだけが表示されています（A 列目や１行目にはデータがないので表示されていません）。常に A1 セルも含めて表示する場合は "--a1=1" オプションを付けて実行します：

$ xls-viewer --sheets=Sheet２ --label=1 --a1=1 sample01.xlsx
Sheet２ :
+====+===+======================+===+======================+===+============+
|    | A | B                    | C | D                    | E | F          |
+====+===+======================+===+======================+===+============+
| 1  |   |                      |   |                      |   |            |
+====+---+----------------------+---+----------------------+---+------------+
| 2  |   | 少し長めのテキストを |   |                      |   | 太字       |
|    |   | 入力                 |   |                      |   |            |
+====+---+----------------------+---+----------------------+---+------------+
| 3  |   |                      |   | 更に123を追加したテ  |   | Italic     |
|    |   |                      |   | キスト               |   |            |
+====+---+----------------------+---+----------------------+---+------------+
| 4  |   |                      |   |                      |   | 色付き     |
+====+---+----------------------+---+----------------------+---+------------+
| 5  |   | aaa                  |   | 3                    |   | 下線       |
+====+---+----------------------+---+----------------------+---+------------+
| 6  |   |                      |   | 6                    |   |            |
+====+---+----------------------+---+----------------------+---+------------+
| 7  |   |                      |   |                      |   |            |
+====+---+----------------------+---+----------------------+---+------------+
| 8  |   |                      |   |                      |   |            |
+====+---+----------------------+---+----------------------+---+------------+
| 9  |   |                      |   |                      |   | ２行とばし |
+====+---+----------------------+---+----------------------+---+------------+
| 10 |   |                      |   |                      |   |            |
+====+---+----------------------+---+----------------------+---+------------+
| 11 |   |                      |   |                      |   |            |
+====+---+----------------------+---+----------------------+---+------------+
| 12 |   |                      |   |                      |   |            |
+====+---+----------------------+---+----------------------+---+------------+
| 13 |   | ３行とばし           |   |                      |   |            |
+====+---+----------------------+---+----------------------+---+------------+

各列の幅はデフォルトだと半角20文字となっています。この値を変えて表示するには "--row_max_width" オプションを指定して実行します。以下の例では「半角10文字」で表示しています。B2 セルや D3 セルの長めのテキストが半角10文字で折り返している様子が確認できます：

$ xls-viewer --sheets=Sheet２ --label=1 --a1=1 --row_max_width=10 sample01.xlsx
Sheet２ :
+====+===+============+===+============+===+============+
|    | A | B          | C | D          | E | F          |
+====+===+============+===+============+===+============+
| 1  |   |            |   |            |   |            |
+====+---+------------+---+------------+---+------------+
| 2  |   | 少し長めの |   |            |   | 太字       |
|    |   | テキストを |   |            |   |            |
|    |   | 入力       |   |            |   |            |
+====+---+------------+---+------------+---+------------+
| 3  |   |            |   | 更に123を  |   | Italic     |
|    |   |            |   | 追加したテ |   |            |
|    |   |            |   | キスト     |   |            |
+====+---+------------+---+------------+---+------------+
| 4  |   |            |   |            |   | 色付き     |
+====+---+------------+---+------------+---+------------+
| 5  |   | aaa        |   | 3          |   | 下線       |
+====+---+------------+---+------------+---+------------+
| 6  |   |            |   | 6          |   |            |
+====+---+------------+---+------------+---+------------+
| 7  |   |            |   |            |   |            |
+====+---+------------+---+------------+---+------------+
| 8  |   |            |   |            |   |            |
+====+---+------------+---+------------+---+------------+
| 9  |   |            |   |            |   | ２行とばし |
+====+---+------------+---+------------+---+------------+
| 10 |   |            |   |            |   |            |
+====+---+------------+---+------------+---+------------+
| 11 |   |            |   |            |   |            |
+====+---+------------+---+------------+---+------------+
| 12 |   |            |   |            |   |            |
+====+---+------------+---+------------+---+------------+
| 13 |   | ３行とばし |   |            |   |            |
+====+---+------------+---+------------+---+------------+

デフォルトでは各セルの「表示されている内容」をそのまま表示していますが、セルの表示式が定義されている場合に、その式だけを表示するには "--formula=1" オプションを指定します。実行結果にはセルに定義されている式だけが表示されます：

$ xls-viewer --label=1 --a1=1 --formula=1 sample01.xlsx
Sheet1 :
+===+===+=====+===+===+===+===+
|   | A | B   | C | D | E | F |
+===+===+=====+===+===+===+===+
| 1 |   | 1+2 |   |   |   |   |
+===+---+-----+---+---+---+---+
| 2 |   |     |   |   |   |   |
+===+---+-----+---+---+---+---+
| 3 |   |     |   |   |   |   |
+===+---+-----+---+---+---+---+
| 4 |   |     |   |   |   |   |
+===+---+-----+---+---+---+---+
| 5 |   |     |   |   |   |   |
+===+---+-----+---+---+---+---+

Sheet２ :
+====+===+===+===+======+===+===+
|    | A | B | C | D    | E | F |
+====+===+===+===+======+===+===+
| 1  |   |   |   |      |   |   |
+====+---+---+---+------+---+---+
| 2  |   |   |   |      |   |   |
+====+---+---+---+------+---+---+
| 3  |   |   |   |      |   |   |
+====+---+---+---+------+---+---+
| 4  |   |   |   |      |   |   |
+====+---+---+---+------+---+---+
| 5  |   |   |   | 1+2  |   |   |
+====+---+---+---+------+---+---+
| 6  |   |   |   | D5*2 |   |   |
+====+---+---+---+------+---+---+
| 7  |   |   |   |      |   |   |
+====+---+---+---+------+---+---+
| 8  |   |   |   |      |   |   |
+====+---+---+---+------+---+---+
| 9  |   |   |   |      |   |   |
+====+---+---+---+------+---+---+
| 10 |   |   |   |      |   |   |
+====+---+---+---+------+---+---+
| 11 |   |   |   |      |   |   |
+====+---+---+---+------+---+---+
| 12 |   |   |   |      |   |   |
+====+---+---+---+------+---+---+
| 13 |   |   |   |      |   |   |
+====+---+---+---+------+---+---+

なお "--border=0" オプションを付けると、シートの枠が表示されなくなります：

$ xls-viewer --label=1 --a1=1 --border=0 sample01.xlsx
Sheet1 :
    A   B     C     D  E  F
 1  1   3
 2  3   2     safa        saf
 3  2   sa
 4  43  safd
 5                  5

Sheet２ :
     A  B                     C  D                     E  F
 1
 2      少し長めのテキストを                              太字
        入力
 3                               更に123を追加したテ      Italic
                                 キスト
 4                                                        色付き
 5      aaa                      3                        下線
 6                               6
 7
 8
 9                                                        ２行とばし
 10
 11
 12
 13     ３行とばし

これらのオプションは組み合わせて使うことも可能です。結果をファイルにリダイレクトしながら使えば後からファイルとして開くこともできて、個人的には我ながら重宝しているコマンドです。

上述の説明では実行可能ファイルだけを紹介していますが、実際にはソースコード含めて公開しています。作り方に興味ある方はこちらもどうぞ：
https://github.com/dotnsf/xls-viewer

タグ：: #excel; #xls; #xlsx; #viewer; #nodejs; #node.js

2023/12/23

データベース - エクセル　変換ツール

コメント数:
0 コメント
by dotnsf

先日、このようなブログを書きました：
"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 を実行すると、以下のルールでデータベース内容をまるごと１つのエクセルファイルに変換します：
・データベース内の全テーブルと、各テーブル内のレコードデータを取り出す
・各テーブルが１枚のエクセルシート（タブ）になる　テーブル名がシート名になる
・シートの１行目にテーブルの全列が書き出される
・シートの２行目以降にテーブル内の全レコードデータが１行ずつ格納される（ただし BLOB などのバイナリデータは無視する）
・処理が完了すると、（データベース名）.xlsx という名前のエクセルファイルでダウンロードできる

という誰得（？）な処理を行います。　もう１つの「エクセルをデータベースにする」は基本的にこの逆の処理を行います：

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

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

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

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

（１）ターミナルやコマンドプロンプトなどの端末を開く

（２）ソースコードをダウンロード

$ git clone https://github.com/dotnsf/db2xlsx

（３）必要なライブラリをインストール

$ cd db2xlsx

$ npm install

（４）実行（終了時は Ctrl+C）

$ npm start

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

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

Docker で PostgreSQL データベースを１つ用意する場合は以下を実行します：

$ 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" となります。

データベースを２つ作る必要はないので 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" となります。ユーザー名部分やパスワードなどは適宜変更して読み替えてください。

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

データベースが稼働している状態で上述の（４）までを実行して本ツールを起動します（本ツール起動後にデータベース起動でも構いません）：

$ npm start

> db2xlsx@0.0.1 start
> node app.js

server starting on 8080 ...

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

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

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

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

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

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

（items）

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

（brands）

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

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

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

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

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

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

それ以外の部分は必要に応じて指定するものですが、今回指定したワークブックファイルは各シートの２行目にテーブルの列定義が記載されています。このような場合（シートの２行目がレコード情報ではなく、列定義情報の場合）は col_attr パラメータに "1" という値を指定する必要があります（何も指定しない場合は２行目もデータレコードとみなされて 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 処理が実行されます：

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

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

ターミナルやコマンドプロンプトなどの端末をもう１つ開いて、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 | ブランド１ | 説明文１説明文１説明文１説明文１説明文１説明文１説明文
説明文１説明文１説明文１説明文１説明文１説明文１
  | https://www.brand1.com | 2023-12-20 00:00:00
 brand02 | ブランド２ | 説明文２説明文２説明文２説明文２説明文２説明文２説明文
説明文２説明文２
  | https://www.brand2.net | 2023-12-21 00:00:00
 brand03 | ブランド３ | 説明文３説明文３説明文３説明文３説明文３説明文３説明文
説明文３説明文３説明文３説明文３説明文３説明文３説明文３説明文３説明文３説明文
 | 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" のボタンをクリックします：

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

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

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

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

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

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

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

タグ：: #db2xlsx; #xls; #xlsx; #xslx-js-style; #db; #excel; #mysql; #postgres

2023/12/09

"Image to Excel" を REST API 化（xlsx-js-style の使い方）

コメント数:
0 コメント
by dotnsf

このネタを見た時は「やられた！」と思いました：
手持ちの画像を「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 はその１つです。ただ詳しい使い方がウェブ上でもあまり見つけることができず、一部では「事実上、読み取り用のライブラリ」とも書かれていました。実際はそんなことはなくて、現に今回紹介するようなエクセルブックファイルを新規作成する上での最低限の機能を持っていました。一方で情報が少ないことも事実で、今回はソースコードの中身を含めて自分でも色々調べながらの作業となりました。その調べて分かった内容も含めて本ブログエントリにまとめておこうと思います。

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

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

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

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

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

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

（↑hachi.jpg というこの画像ファイルを指定してみました）

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

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

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

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

ダウンロードしたファイルを（ダブルクリックするなどして）エクセルやエクセル互換ツールで開いてみると期待通りのエクセルシートが表示されるはずです（シート名は元の画像ファイル名です）。元祖の Image to Excel と同じような結果になっているはずです：

（↑シートに画像が貼られているのではなく、細かなセルの色を変えることで実現しています）

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

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

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

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

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

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

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

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 パラメータでエクセルファイルのセル１つのサイズを何ピクセル平方にするか
　- width パラメータでエクセルファイルのセルを何列で表現するか
　- height パラメータでエクセルファイルのセルを何行で表現するか
　- file パラメータで画像ファイル本体（ここだけ必須）
・HTTP レスポンスはエクセルファイルのバイナリデータ

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

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

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

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

以下、このフローの実装内容を順に説明していきます。まず（１）ですが、ここは 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;

    :

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

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

この（２）と（３）に相当する部分のコードはこのようになっています：

    :
    //. 画像解析
    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;
    }

    :

（４）では仮想的な 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 ライブラリの出番です。

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

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

    :
    //. ワークブックを作成
    var wb = XLSX.utils.book_new();
    :

（６）ではワークブックに追加することになるワークシート（タブ）を新規に１つ作成します。

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

    :
    //. ワークシートを作成
    var row = [[]];
    var ws = XLSX.utils.aoa_to_sheet([row]);
    :

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

ここでの「セルのサイズ」とは「REST API 実行時に px パラメータとして指定された値（指定されていない場合は default_px の値（＝２）」です。また「セルの列数」とは「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;
    :

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

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

この（８）と（９）はループ内で以下のように実現しています。ピクセルの RGB 値は Canvas のデータ内に４つごとに（R 値、G 値、B 値、輝値）格納されているのでそれぞれを取り出し、この値を "RRGGBB" というフォーマットに変えて、"A1", "A2", ... といったセルの色として指定しています。また cellname という関数を用意して縦位置、横位置からセル名に変換できるようにしています（この後の（１０）でも使っています）：

    :
    //. １ピクセルずつ取り出して色を調べる
    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 );
}

（１０）（７）、（８）、（９）の処理によって変更されたワークシートの内容をワークブックに反映します。これでレスポンスとして返すエクセルファイル（のデータ）が完成しました。

このコードでは実際に使ったセルの範囲を "A1:（最も右下にあるセルの名前）" という形でワークシートの '!ref' 値に指定してから、作成したワークシートをワークブックに追加する、という処理を実行しています：

    :
    //. 値が適用される範囲を指定
    var cname = cellname( imagedata.width - 1, imagedata.height - 1 );
    ws['!ref'] = 'A1:' + cname;

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

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

タグ：: #xlsx-js-style; #image2xlsx; #excel; #xlsx; #rest; #api

2018/11/15

Node.js でエクセルファイルを更新する

コメント数:
3 コメント
by dotnsf

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

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

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

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

同シート内の 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 というファイルができあがります。このファイルをエクセルで開いてみると、このような見た目になりました：

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

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

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

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

結果はこちら：

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

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

タグ：: #node.js; #nodejs; #excel; #xlsx; #populate

2018/08/02

Node.js からエクセルシートを操作する

コメント数:
0 コメント
by dotnsf

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

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

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

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

データとしては "A1:C14" の範囲にまとまっていて、その右に２軸の折れ線グラフが１つあります。この表の 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' } );

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

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

$ node app2.js

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

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

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

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

タグ：: #node; #nodejs; #node.js; #excel; #xls; #xlsx

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

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