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

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

タグ:excel

「エクセルファイルを扱えるライブラリ」といえば、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 こういうのは我ながら嫌いじゃない。

PHP からエクセルフォーマットのファイルを扱えるようにするための PHPExcel を使ってみました。備忘録としてその手順を記録しておきます。

まず、PHPExcel の最新版をダウンロードサイトから選択してダウンロードします。2016/Jan/10 時点での最新版のバージョンは 1.8.0 でした。以下、このバージョンを使う前提で紹介します:
2016011001


ダウンロードしたファイル(PHPExcel_1.8.0_doc.zip)を適当なフォルダに展開します。ファイル内にはドキュメントなども含まれていますが、実際に PHP から利用する際に必要なファイルは全て Classes フォルダ内にあるので、実環境ではこのフォルダ以下のファイルだけがあれば一応動きます:
2016011002


次に実際にアクセスする先のエクセルファイルを用意します。今回は Excel97-2003 フォーマット(拡張子 .xls)の、このようなブックファイル(Book1.xls)を用意しました。同じもので良ければこちらこちらからダウンロードしてください。なお PHPExcel 自体は Excel 2007 フォーマット(拡張子 .xlsx)を扱うことも可能です。今回はどちらのファイルにも対応させてみます:
2016011003


なお、今回用意したブックファイルは上記のようにシートが3枚あり、そのうちの1枚目のシートに表が作られています。また表内では日本語が使われていたり、セルの色属性や計算式などの機能も使われています。

ではこの Book1.xls を PHPExcel で読み込んでみましょう。以下の様な PHP ファイルを用意します:
<?php
//. PHPExcel ライブラリの読み込み
include_once( dirname( __FILE__ ) . '/Classes/PHPExcel.php' );
include_once( dirname( __FILE__ ) . '/Classes/PHPExcel/IOFactory.php' );

//. endsWith 関数の定義
function endsWith( $str, $suffix ){
  $len = strlen( $suffix );
  return $len == 0 || substr( $str, strlen( $str ) - $len, $len ) === $suffix;
}

//. 対象ファイル名
$excelfilename = 'Book1.xls';
$excelfilepath = dirname( __FILE__ ) . '/' . $excelfilename;

//. ファイル拡張子によってリーダーインスタンスを変える
$reader = null;
if( endsWith( $excelfilename, 'xls' ) ){
  $reader = PHPExcel_IOFactory::createReader( 'Excel5' );
}else if( endsWith( $excelfilename, 'xlsx' ) ){
  $reader = PHPExcel_IOFactory::createReader( 'Excel2007' );
}

if( $reader ){
  //. エクセルファイルを読み込む
  $excel = $reader->load( $excelfilepath );
  echo $excel->getSheetCount(); //. シート数を取得
  echo "<br/>";

  //. Formulaを計算する指定でアクティブシートの内容を取得する
  $obj1 = $excel->getActiveSheet()->toArray( null, true, true, true );
  var_dump($obj1);
  echo "<br/>";

  //. Formulaを計算しない(式を式のままにする)指定でアクティブシートの内容を取得する
  $obj2 = $excel->getActiveSheet()->toArray( null, false, true, true );
  var_dump($obj2);
  echo "<br/>";
}else{
  echo "No reader.";
}
?>

そして、この PHP ファイルを HTTP のドキュメントルート以下に用意し、更に同じフォルダにエクセルファイル(Book1.xls)と PHPExcel ライブラリの Classes フォルダを用意した上で、この PHP ファイルにブラウザからアクセスするとこんな結果になりました:
2016011004


この結果を見やすく整形するとこんな感じ↓です:
3

array(6) {
 [1]=> array(4) {
  ["A"]=> string(1) "A"
  ["B"]=> string(1) "B"
  ["C"]=> string(3) "計"
  ["D"]=> string(6) "平均"
 }
 [2]=> array(4) {
  ["A"]=> float(1)
  ["B"]=> float(20)
  ["C"]=> float(21)
  ["D"]=> float(10.5)
 }
 [3]=> array(4) {
  ["A"]=> float(3)
  ["B"]=> float(31)
  ["C"]=> float(34)
  ["D"]=> float(17)
 }
 [4]=> array(4) {
  ["A"]=> float(2)
  ["B"]=> float(44)
  ["C"]=> float(46)
  ["D"]=> float(23)
 }
 [5]=> array(4) {
  ["A"]=> float(5)
  ["B"]=> float(2)
  ["C"]=> float(7)
  ["D"]=> float(3.5)
 }
 [6]=> array(4) {
  ["A"]=> float(11)
  ["B"]=> float(97)
  ["C"]=> float(108)
  ["D"]=> float(54)
 } 
} 

array(6) {
 [1]=> array(4) {
  ["A"]=> string(1) "A"
  ["B"]=> string(1) "B"
  ["C"]=> string(3) "計"
  ["D"]=> string(6) "平均"
 }
 [2]=> array(4) {
  ["A"]=> float(1)
  ["B"]=> float(20)
  ["C"]=> string(6) "=A2+B2"
  ["D"]=> string(5) "=C2/2"
 }
 [3]=> array(4) {
  ["A"]=> float(3)
  ["B"]=> float(31)
  ["C"]=> string(6) "=A3+B3"
  ["D"]=> string(5) "=C3/2"
 }
 [4]=> array(4) {
  ["A"]=> float(2)
  ["B"]=> float(44)
  ["C"]=> string(6) "=A4+B4"
  ["D"]=> string(5) "=C4/2"
 }
 [5]=> array(4) {
  ["A"]=> float(5)
  ["B"]=> float(2)
  ["C"]=> string(6) "=A5+B5"
  ["D"]=> string(5) "=C5/2"
 }
 [6]=> array(4) {
  ["A"]=> string(11) "=SUM(A2:A5)"
  ["B"]=> string(11) "=SUM(B2:B5)"
  ["C"]=> string(11) "=SUM(C2:C5)"
  ["D"]=> string(5) "=C6/2"
 } 
} 

1行目の「3」はシート数なので、これは正しく取得できていることがわかります。

2行目から始まるブロックは1枚目のシートの中身を「Formula を計算して」取得したものです。つまり計算式が定義されていた場合はその計算結果が取得できていることになるのですが、例えば C2 セルは A2 と B2 の和(=A2+B2)が定義されていましたが、正しく計算結果が取得できていることがわかります。

またその下には1枚目のシートの中身を「Formula をそのまま」取得したものが出力されています。C2 セルの内容が "=A2+B2" となっていることが確認でき、これも期待通りに動いていたことが確認できました。


PHPExcel はファイルの読み込みだけでなく、Excel ファイルとして出力することも出来て、(CSV や TSV ではない)エクセル連携をする際に便利に使えそうなライブラリです。


ある意味でよくある話ですが、「データベースサーバーの情報をエクセルで見たい」という要望をいただきます。

この「データベース」が何なのかにもよりますが、原則的にはそのデータベースサーバーにあった ODBC ドライバをそのパソコンに入れて設定した上で、エクセルから外部データ呼び出しを行うことになります。

仮に「データベース=MySQL」という前提で、この手順を紹介します。

まずはデータベースサーバーに合った ODBC ドライバが必要です。MySQL であればこちらからダウンロードします:
MySQL :: Download Connector/ODBC

Zip 版と Msi 版があります。どちらでもいいのですが Msi 版であればダウンロードしたものをダブルクリックすればインストーラーが起動して簡単なのでこちらがおススメです。

また 32bit 版と 64bit 版があります。使っている Windows にあった方を選んでダウンロードするのですが、よく分からない場合はとりあえず 64bit 版を選んでみて、インストール時に「環境があってない」といった旨のメッセージが出る場合は改めて 32bit 版を選ぶ、という方法もあります。

インストーラが起動したら基本的には全て Next や「はい」を選んで標準インストールします。

これで ODBC ドライバのインストールは完了です。MySQL 以外のデータベースを使っている場合は若干異なる所もありますが、正しい ODBC ドライバを入手してインストール、という点では全て共通です。


次に導入した ODBC ドライバを使ってデータソースを定義します。簡単に言うと、目的のデータベースサーバー上のデータに接続するための設定を行います。

コントロールパネルを起動して、 管理ツール > データソース(ODBC) を選びます:
2014111101


「ODBC データソース」管理のダイアログが開きます。「ユーザーDSN」タブの「追加」ボタンをクリックします:
2014111102


新規に追加するデータソースで使うドライバ(接続先の種類)を選びます。先程インストールした ODBC ドライバから選択します。MySQL の場合は "ANSI Driver" と "Unicode Driver" の2つが導入されているはずですが、ここでは "ANSI Driver" を選択(データに UTF-8 の日本語が含まれている場合はこちら)して「完了」ボタン:
2014111103


MySQL の接続先に関する情報を入力するダイアログが表示されます。Data Source Name には接続先を識別するための名前を入力します。Description はその説明ですが空欄でも構いません。MySQL サーバーがリモートの場合は TCP/IP Server: でその IP アドレスやホスト名を指定します。ポート番号は特に変更していない限りは 3306 のままで大丈夫です。そしてデータベースに接続するためのユーザー名とパスワードを入力。ここまでの指定内容が正しければ、その下の Database セレクションボックスの中に、この MySQL サーバーにこのユーザーからアクセスできるデータベースの一覧が入っているので、そこから実際に接続するデータベースを選びます:
2014111104


ここまで指定できたら「Test」ボタンをクリックします。全ての情報が正しければ "Connection Successful" というメッセージが表示され、データベースに正しく接続できたことが分かります。「OK」ボタンをクリックして1つ前の画面に戻ります:
2014111105


先程の画面に、いま作成したデータソースが追加されたことが確認できるようになったはずです。これでデータソースの追加もできました:
2014111106


ここまでの設定ガできていれば、後はエクセル上での作業になります。エクセルを起動し、データを表示したいワークシートを開いた状態で、メニューの データ > 外部データの取り込み > その他のデータソース取り込み > データ接続ウィザード を選択します(エクセルのバージョンによっては データ > 外部データの取り込み > データの取り込み > 新しいデータソースへの接続):
2014111108


データ接続ウィザードが表示されます。まずデータソースの種類として "ODBC DSN" を選択します:
2014111109


次に ODBC データソースを指定します。先ほど作成した "MySQL" (作成したデータソースの名前)を指定します:
2014111110


データソースの情報に基づいて情報を取得し、データベースとその中のテーブルの選択画面に移ります。エクセル内での表示に使いたいデータベースとテーブルを選択します:
2014111111


テーブル内のデータをキーワード検索で絞り込みたい場合などはここでキーワードの指定などを行うこともできます。ここまでの指定ができたら「完了」ボタンをクリックします:
2014111112


最後にどのような方法で指定データをインポートするかを指示します。普通にテーブルとしてインポートするのか、ピボットテーブルとしてインポートするのか、既存のワークシートの特定位置に入れるのか、新規にワークシートを作成するのか、といった指定を行います:
2014111113


すると指定内容に従った形で MySQL サーバーのデータがエクセルにインポートされます。インポート後はエクセルデータとして利用できるので、例えばグラフ化などもエクセルでの作業として簡単に行えます:
2014111114


なお、この設定を行ったエクセルシートを保存して再度開いた場合にセキュリティ警告が表示されることがあります。この警告が表示された場合、保存した際のシートの情報は残っていますが、データベースサーバーとの接続は切れています。保存後にデータベースに追加したり変更された情報はエクセル上では反映されていません:
2014111112


データベースサーバーの最新情報を取り込みたい場合はメニューの データ > すべて更新 を選択します。これで該当シート内のデータはデータベース内の最新情報に更新されます:
2014111113


このページのトップヘ