以前にこのブログで「Node.js からエクセルシートを操作する」というタイトル&内容の記事を書いて紹介しました:
Node.js からエクセルシートを操作する
この記事の中では XLSX という npm のライブラリを用いてエクセルファイルからセルの中身を読みとったり、セルに値を代入して保存する、という手法を紹介しました。 XLSX は対応フォーマットも多く、使う機会も多いのではないかと思っていました。
が、その後いろいろ使っていく中で XLSX が苦手とするケースも出てきました。典型的な例としてはセルのフォーマットを保持したままエクセルファイルを更新したい場合です。
具体的にはこんなケースが考えられます。以下のようなシート一枚のエクセルファイル(template.xlsx)が存在していたと仮定します:
同シート内の A1 セルと B1 セルは結合しています。加えて背景色が緑色で、フォントは太字、中央揃えに設定されています。
これらの条件を残しながら A1 セルの内容を別の文字列に更新したい、というのが今回の用件であるとします。もちろん人間がマニュアル操作で更新することはたやすいのですが、これを人手を介さずにプログラマティックに行いたい、というものです。
では、この処理を XLSX ライブラリを使って行ってみます。コード(test01.js)としてはこのような内容になります:
この例では template.xlsx を読み込んだあと、実行時のタイムスタンプ値を取得し、Sheet1 シートの A1 セルの内容を「あいうえお」から「かきくけこ(タイムスタンプ値)」に書き換えた上で xlsx_(タイムスタンプ値).xlsx というファイルで保存するようにしています。
このコードを Node.js で実行してみます:
実行した結果、xlsx_(タイムスタンプ値).xlsx というファイルができあがります。このファイルをエクセルで開いてみると、このような見た目になりました:
A1 セルと B1 セルの結合の情報は保持していたのですが、背景色やフォントの太字の情報は消えていました。また中央揃えは無効になっていました。
このようにセルに定義されたフォーマットを保持したままエクセルファイルを更新しようとすると、XLSX ライブラリでは限界があるようでした。
で、これをどうすればよいか、というのが今ブログエントリのメインテーマです。結論としては XLSX ライブラリではなく、XLSX-populate という npm ライブラリを使うことで解決できそうでした:
XLSX-populate ライブラリを使って上記のコードを書き換えたものがこちら(test02.js)です:
先程の test01.js とほぼ同様の処理を行っています。違いは A1 セルに上書きする内容を「さしすせそ(タイムスタンプ値)」として、出力ファイル名は xlsx_populate_(タイムスタンプ値).xlsx としています。
そしてこのファイルを実行します:
結果はこちら:
ちょっとわかりにくいのですが、A1 セルと B1 セルの結合はもちろん、背景色や太字フォント、中央揃えの情報は全て保持されたまま更新ができています:
もちろん、XLSX populate が万能、というわけではないのですが、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 でファイルの更新処理を行う場合には向いているライブラリだと思います。
コメント
コメント一覧 (3)
私もこのライブラリを使い始めたのですが質問したいことがあります。
目的としてはDBからデータを取得し、指定のセル、シートにに書き込みたいと考えています。
流れとしては①テンプレートとなるファイルを読み込み、指定のセルにDBから取得したデータ(変数data)の配列をsheet1.cell("A2").value(data[0]);のように書き込みます。
ここまでは良いのですが、②もう一度書き込んだファイルを読み込み、別のセルに追記したい場合があり、sheet1.cell("B2").value(data[1])のように記述すると実行すると処理は進んでいるのですが出来上がったファイルを開こうとすると「'ファイル名'の一部の内容に問題が見つかりました。可能な限り復元しますか」のようなポップアップが表示されファイルの中身は①で書き込まれた内容も一部しか残っていない状態になっています。
ただ、疑問なのが確認のためにsheet1.cell("B2").value(1000)のように書き込む値を直接書くと問題なく書き込みが成功します。
そんなに珍しい使用ケースではないと思うのですが原因が分からず悩んでいます。同じようなケースを経験されたことはありますでしょうか?
個人的にはそのようなケースには遭遇したことはないです。ちなみに、以下のコードを試したみたところ、2回目の出力結果(xlsx_populate_2.xlsx)も普通に(メッセージもなく)開くことができました。なおエクセル 2013 を使っています。
var XlsxPopulate = require( 'xlsx-populate' );
var str1 = "さしすせそ";
var str2 = "たちつてと";
//. テンプレート
var templatefile = './template2.xlsx';
//. 出力先
var xlsfile1 = './xlsx_populate_1.xlsx';
var xlsfile2 = './xlsx_populate_2.xlsx';
//. テンプレートから読み込み
XlsxPopulate.fromFileAsync( templatefile ).then( book1 => {
//. 「Sheet1」シート
var sheet1 = book1.sheet(0);
sheet1.range( "C5:C10" ).merged( true );
sheet1.cell( "C5" ).value( str1 );
//. 一回目の保存
book1.toFileAsync( xlsfile1 ).then( result1 => {
console.log( 'saved into ' + xlsfile1 );
XlsxPopulate.fromFileAsync( xlsfile1 ).then( book2 => {
//. 「Sheet1」シート
var sheet2 = book2.sheet(0);
sheet2.cell( "C12" ).value( str2 );
//. 二回目の保存
book2.toFileAsync( xlsfile2 ).then( result2 => {
console.log( 'saved into ' + xlsfile2 );
});
});
});
});
私の実行環境としてNode-Red、PostgreSQL、Excel2016を使用しています。
頂いたコードで言うところの2回目の保存の部分で、1回目に保存したxlsfile1 をテンプレートとして読み込み、保存名もxlsfile1として保存という流れで実行しました。
xlsfile1 を再編集して上書き保存するような感じです。説明下手ですいません。
Node-Redで複数のノードを解しているのでコードをここに書けないのですが、AとBというような2つのボタンがあり、各ボタンを押すとDBに接続→SQL実行→取得したデータを書き込みという流れなのですが、その2つのボタンでExcelのセルの記入箇所を変えようとしています。(AならA列のセル、BならB列のセルなど)
処理が似ているので各ボタンからSQL分作成までのノードはA、B用に個別に用意し、DBに接続、取得結果の処理は同じノードを使い、Excel書き込みはまたA,B用それぞれのノードを用意して処理しました。
この際、DBに接続するノードは「パレット管理→ノードの追加」で取得したpostgresノードを使用しておりここが怪しいのかと思い、同じノードを使って処理し手いた部分をA,B個別に用意したところ今のところ再現しなくなりました。
自分自身の記述方法が悪い気もしてノードのせいとは考えにくいのですが、調べても同様のケースは見つからなく悩んでいたので是非共有させていただけたらと思い書かせていただきました。
長々と申し訳ありません。