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

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

タグ：xls

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

ツールの用途を一言でいうと「エクセルファイル（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 は（実は１箇所だけ式を使ってますが）特に文字装飾要素も使わない、ごくシンプルなシートです：
2024072301



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


【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

2024072300


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

先日、このようなブログを書きました：
"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 を開きます：
2023122201


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


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

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


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

2023122302


この 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" と書かれた箇所をクリックします：
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 ）を指定します。

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


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


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

ターミナルやコマンドプロンプトなどの端末をもう１つ開いて、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" のボタンをクリックします：
2023122301


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


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


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


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


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

2023122307


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


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


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

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

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

2018080100


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


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


データとしては "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 というファイルが出来ているはずです。試しにこのファイルをエクセルで開いてみるとこのようになります：
2018080201



"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

自分はエンジニアのキャリアの中でエクセルではない某表計算ソフトの開発に携わる機会がありました。そんなわけで、どちらかというとアンチ・エクセル派だったりします：
2016082100


また一部の人には公にしていますが、その後ノーツ／ドミノのエンジニアも担当させていただく機会もありました。ノーツは大好きです、.NSF 万歳！


だからというわけではないのですが、そんなバックグラウンドを持つ自分がエクセルデータファイル（*.xls や *.xlsx）をノーツデータベース（*.nsf）化するツールを作ってみました。これで幸せになれる人がいたら使ってほしいです（笑）。

ちなみに OpenNTF.org を探してみると、この逆（ノーツをエクセル化）を実現するようなものは（自分が動かしてみたわけではありませんが）見つかります。が「エクセルをノーツ化」という変態的のはさすがになさそうだったので、技術的挑戦も兼ねて作ってみました。正直かなり手間取りましたが、当初考えていた最低限の挙動はできるようになったので、一旦公開して、紹介します。実体はノーツの NTF および NSF ですが、ACL は誰でも開いてみれるようになっているはずなので、興味ある人は中身を確認したり、もう少し便利に作りなおしたりしてみてください。

まず仕様のおさらいの意味で、どんなインプットに対して、どんなアウトプットができるようになるのか、という点を紹介します。用意する必要があるのはエクセルシートファイルです：
2016081901


このエクセルファイルには複数のシートが存在していることを想定します。そして各シート毎になんらかのデータが格納されているものとします。この例ではシートは３つあり、１枚目（Sheet1）にはこんな感じで、７行５列のデータが入ってます（ちなみに E 列の値は同じ行の C 列の値を２倍したものになるような計算結果として定義されています）：
2016082001


一方、２枚目のシート（Sheet2）には１０行３列のこんなデータが入っているものとします。シートごとに列数や行数が全く異なるデータが入っていることを想定しています：
2016082002


３枚目のシート（Sheet3）には変換対象範囲を理解する上で役立つシートにしました。シート内のセルが虫食い状態のようにところどころに空白のセルがあるものです。ちなみにこの例では５行３列（A1 セルから C5 セルまでの矩形部分）を変換対象にするような仕様にしています（E2 セルにデータは存在していますが、D 列に全くデータがないので対象外と判定する仕様です）：
2016082003


このデータをノーツ化するにあたり、以下の様なルールで変換（というかインポート）します：
  • １つのエクセルシートの内容を１つのビューとして変換する
  • なので、３つのシートを持つエクセルファイルは３つのビューをもつノーツデータベースに変換される
  • ノーツを使いつつ、見栄えはなるべく表計算っぽくする

実際の作業は以下のようになります。まず今回用意したノーツデータベース "XLS2NSF" （リンクは後述）をノーツクライアントから開きます：
2016081902


いわゆる「ポリシー文書」が開き、使い方などが確認できます。内容は確認いただくとして、すぐに試すには「エクセルファイル指定」と書かれたボタンをクリックします：
2016082101


ファイルダイアログが開くので、エクセルファイル（*.xls / *.xlsx）を指定します。ここでは上記で紹介した MyBook1.xls という３つのシートを持つファイルを指定しています：
2016081904


後は全自動でノーツDB化が実行されます。事実上エクセルファイルを指定するだけ、です。

全ての変換処理が完了すると、指定したファイル名と同じタイトルのノーツDB（ファイルパスはノーツデータディレクトリ直下、ファイル名はタイトルの最後に ".nsf" を加えたもの）がワークスペース上に追加されているはずです。これをクリックすると変換結果が参照できます：
2016081903


この作成されたデータベースをノーツで開いた様子がこちらです。元ファイルには３つのシートが存在していましたが、それぞれのシートがノーツのビューとしてナビゲータから切り替えて見ることができます。こちらは Sheet1 のもの。７行５列の表が中身も含めて再現されています。なお元ファイルでは E 列の中身は同じ行の C 列の２倍になるような計算式でしたが、変換後は固定値になっています：
2016082004


２つ目の Sheet2 に切り替えるとこのようになります。元のシート通り、１０行３列の表ができているはずです：
2016082005


３つ目の Sheet3 はこちら。セルの虫食い状態も再現されています。また上記で紹介したように元のシートの A1 セルから C5 セルの部分が変換されているはずです：
2016082006



この変換後のノーツデータベース（のビュー）は ViewEdit モードを実装しているので、特定のセルをクリックすると、そのセルの値を書き換えることができます。以下の例では Sheet2 の C6 列（もともと "4" と入力されていた箇所）を編集して "123" にしている所です：
2016082007


Enter を押すと変更が確定して、元のビューに戻ります：
2016082008


と、まあこんな需要があるのかないのか分からないものを作ってみました。折角なので GitHub で公開しています：
https://github.com/dotnsf/xls2nsf

（注　.nsf や .ntf のノーツデータベースを Git で管理すべきではありません。良い子はマネしちゃだめ）


実際に試してみたい！　という奇特な方は、上記 GitHub から "Clone or Download" - "Download ZIP" を選択してダウンロードし、展開して使ってください：
2016081902


また、このブログの中で紹介に使っているエクセルのサンプルファイル（MyBook1.xls）も併せて公開します。ぜひ手持ちのファイルで試していただきたいのですが、良さ気なのがない場合は使ってください：
http://dotnsf.blog.jp/MyBook1.xls


以下、2016/Aug/21 時点での制約です：
  • LotusScript で実装しています。エクセル読み取りは OLE を使っています。そのためノーツクライアントとエクセルがインストールされた Windows 環境でないと動かないと思います。
  • 変換の対象となる部分の計算方法が特殊です。現時点では各シートの A1 セルには必ず値が入っている必要があります。
  • また列は行、列ともに空セルしかない最初の行や列を探して、その内側を対象とみなしています。下の霊であれば、D列と 6 行目には空セルしかないので、その内側の A1 セルから C5 セルまでが対象です（E2 セルにデータがありますが、現在の仕様ではデータ変換の対象にはしません）。
2016082001


ノーツ化することで式による計算機能を失ってしまうわけですが（汗）、代わりに強力な ACL によるアクセス管理ができるようになります。この辺りは改善できればしたいなあ、と思っていますが、ノーツのビュー内の各エントリーの中身を式の結果にするというのは現時点では困難と思ってます。あと１行追加とか、１列追加とか、できそうで実装してない機能もありますが、その辺りは興味ある人に追加実装していただきたいです（手抜きともいう）。

ま、自分で作っておいてなんですが、「そこそこ動く MAD なツール」だと思いますｗ　こういうのは我ながら嫌いじゃない。

タグ ：
#excel
#xls
#xlsx
#nsf
#notes

このページのトップヘ