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

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

タグ:xlsx

以前にこのブログで「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 関数が再計算されて開くので、そこで値が正しく変わったように見えます)


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



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


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


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

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

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


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


一方、2枚目のシート(Sheet2)には10行3列のこんなデータが入っているものとします。シートごとに列数や行数が全く異なるデータが入っていることを想定しています:
2016082002


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


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

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


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


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


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

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


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


2つ目の Sheet2 に切り替えるとこのようになります。元のシート通り、10行3列の表ができているはずです:
2016082005


3つ目の 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 によるアクセス管理ができるようになります。この辺りは改善できればしたいなあ、と思っていますが、ノーツのビュー内の各エントリーの中身を式の結果にするというのは現時点では困難と思ってます。あと1行追加とか、1列追加とか、できそうで実装してない機能もありますが、その辺りは興味ある人に追加実装していただきたいです(手抜きともいう)。

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

このページのトップヘ