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

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

タグ:excel

僕のことを個人的に知ってる人はご存知かもしれませんが、自分は今から25年ちょっと前に「ロータス株式会社」という企業でアプリケーション開発を担当していました。具体的に開発を担当していた製品の中には、昭和を代表する(苦笑)表計算ソフト 1-2-3(「ワンツースリー」)も含まれていました。ここ10年くらいはウェブアプリばかり作っていますが、まだ頭(や性格)がキレキレだった頃はパソコン向けのネイティブアプリも作っていた時代がありました。

自分が開発を直接担当していたのは Microsoft Windows 向けの 32bit 版でしたが、当時はまだ PC-DOS 版を無理やり Windows 向けに拡張した 16bit 版や、PC-DOS 版自体も 1995 年まではバージョンアップ対応をしていました。そんなこともあって当時の担当製品は今でも仮想マシン(VM)の形で残していたりします。今となっては貴重な PC-DOS/V(V7) の稼働環境だと思っています:
2022060400


※ついでにコメントしておくと、Lotus Notes バージョン1などの OS/2 資産も VM 化して残しています。自分が作った製品への愛は高めだと自認しています。


この PCDOS VM の中に懐かしいツール類と一緒に Lotus 1-2-3 R2.5J がインストールされています(赤く囲っているのが当時悪名高かったライセンスファイルです):
2022060401


今回、この環境を持ち出したのは理由があります。先日、スラドにこのようなタレコミがあったのを発見しました:
Lotus 1-2-3、Linux に移植される

(自分も 1-2-3 愛は大きい方だと思ってますが)自分も勝てなさそうな 1-2-3 愛を持っていそうな Tavis 氏がインターネット・アーカイブ転がっていた保存されていた SysV 版の 1-2-3 for UNIX を使って Linux 版(elf 版)を作ることに成功した、というものでした。未だに 1-2-3 への愛を持っているのは自分くらいかと思っていたのですが、世界は広いです。 Tavis さん、Good-job! d(o^


(横道)上記画像でファイラーと呼ばれていた FD を使ったスクリーンショットを掲載していますが、実は FD は Linux に移植済みだったりします。興味ある方はこのコマンド(Ubuntu 向け)を試してみてください:
$ sudo apt install fdclone

$ fd
2022060411



(横道おわり)で、そんな記事に触発されたというわけではないのですが、自分も久しぶりに上記 PC-DOS 版 1-2-3 環境を使ってせっかくなので何か試してみようと思いました。実は以前から気になっていたことがあって、それを検証する機会とさせていただきました。

その気になっていたことというのは、みなさんも一度は耳にしたことがあると思うのですが「ソフトウェア製品に実装された機能の多くは使われない」というものです。製品ベンダーとしては利用者からの要望を形にして様々な機能を実装しているのですが、ほとんどのケースでその多くは使われることがない、というものです。そういう自分も、例えばプログラミングエディタに VSCode を使っていますが、自分に必要な機能は当然のように知ってますが、正直知らない機能が多くあります(良く言えば「困ったときにすぐに調べることができるので、使わない間は知らなくてもよい」わけです)。現在、表計算ソフトといえば「エクセル」ですが、このエクセルも(特に最近のバージョンは)非常に多くの機能が付与されていて、噂では方眼紙としても使うことができるその全てを熟知している人って存在するのだろうか??とすら思ってしまうほどです。

エクセルを「最低限」(この定義が難しい)使えるようになるにはどんなことを知っていればいいんだろう?? という需要もあるようで、YouTube を見ていてもエクセルの基本機能を紹介するものが多く見つかります。

・・・と、そこで閃きました。現在のエクセルで「基本」とされている機能って、Lotus 1-2-3 の頃にはどの程度存在していたのだろうか? という命題です。特に他意はないのですが、この手のエクセル基本機能を調べようとググった結果の上位にあったもので、YouTube でエクセルの基本機能等を紹介しているシリーズを見つけ、そのシリーズ中の「【Excel基本】初心者が最初に覚える関数12選」の中で紹介されている12個の関数を基本機能とみなして、これらの関数が DOS 版 Lotus 1-2-3 の中の関数としてどの程度カバーされていたのか、を検証してみることにしました(勝手に使わせていただきました m(__)m):
https://www.youtube.com/watch?v=cfGKFhSE6uA



なお、この動画で紹介されていた関数12選は以下のような内容でした:
1 SUM関数
2 AVERAGE関数
3 COUNT関数
4 COUNTA関数
5 IF関数
6 COUNTIF関数
7 COUNTIFS関数
8 SUMIF関数
9 SUMIFS関数
10 VLOOKUP関数
11 IFERROR関数
12 XLOOKUP関数


比較対象は Windows 版の Lotus 1-2-3 と DOS 版の Lotus 1-2-3 を・・・と考えていたのですが、今回の12個の関数について検証した結果としてはこの2つに差異はありませんでした(Windows 版に存在している関数は DOS 版にも存在していて、Windows 版に存在していない関数は DOS 版にもありませんでした)。ということもあって、以下では DOS 版での検証結果のみを紹介します。


検証方法としては、上記の YouTube 動画で「エクセルの基本関数」として紹介されている機能と互換性のある関数が DOS 版の Lotus 1-2-3 でも存在しているかどうかを確認する、という形としました。なお動画内で対象としているエクセルは最新の 2021 で、私が検証に使ったのは DOS 版 Lotus 1-2-3 R2.5J というバージョンです。このバージョン自体は 1995 年にリリースされた、DOS の日本語対応版としては最終バージョンですが、基本的な設計は R2 から変わっておらず、1986 年の設計を引き継いでいます(つまり昭和時代のソフトウェアです)。


【検証準備】
(VM 内の)PC-DOS に Lotus 1-2-3 R2.5J をインストールし、適当な表を作った上で関数の検証を行います:
2022060402


【検証1 SUM関数】
指定エリア内の数値を合計する、という(本当の意味で基本的な)関数です。これは Lotus 1-2-3 R2.5J では @SUM 関数として実装されていました(下図の赤枠部分参照)。なお Lotus 1-2-3 ではマクロ関数は全て @XXX という形式になっていて、「アット関数」と呼ばれています:
2022060403


【検証2 AVERAGE関数】
指定エリア内の数値の平均値を求める関数です。これも Lotus 1-2-3 R2.5J では @AVG 関数として実装されていました(下図の赤枠部分参照):
2022060404


【検証3 COUNT関数】
指定エリア内に数値のセルがいくつ存在するかをカウントする関数です。これも Lotus 1-2-3 R2.5J では @PURECOUNT 関数として実装されていました(下図の赤枠部分参照)。1-2-3 にも @COUNT 関数は存在しているのですが少し用途が違っていました:
2022060405



【検証4 COUNTA関数】
指定エリア内に空白ではないセルがいくつ存在するかをカウントする関数です。これも Lotus 1-2-3 R2.5J では @COUNT 関数として実装されていました(下図の赤枠部分参照)。1-2-3 の @COUNT 関数はエクセルの COUNTA 関数で、エクセルの COUNT 関数は 1-2-3 の @PURECOUNT 関数として存在しているのですが少し用途が違っていました:
2022060406


【検証5 IF関数】
条件分岐処理をする関数です。これは Lotus 1-2-3 R2.5J でも @IF 関数として実装されていました(下図の赤枠部分参照):
2022060407


【検証6 COUNTIF関数】
指定エリア内に条件を満たすセルの数かいくつ存在するかをカウントする関数です。これは Lotus 1-2-3 R2.5J には互換関数が存在していません


【検証7 COUNTIFS関数】
指定エリア内に複数の条件を満たすセルの数かいくつ存在するかをカウントする関数です。これは Lotus 1-2-3 R2.5J には互換関数が存在していません


【検証8 SUMIF関数】
指定エリア内で条件を満たすセルだけを対象とした SUM 関数です。これは Lotus 1-2-3 R2.5J には互換関数が存在していません


【検証9 SUMIFS関数】
指定エリア内で複数の条件を満たすセルだけを対象とした SUM 関数です。これは Lotus 1-2-3 R2.5J には互換関数が存在していません


【検証10 VLOOKUP 関数】
指定エリア内で特定の値をもつ列を見つけた場合に、指定された別の列の値を求める関数です。これは Lotus 1-2-3 R2.5J でも @VLOOKUP 関数として実装されていました(下図の赤枠部分参照):
2022060409



【検証11 IFERROR 関数】
エラーが発生した場合の処理を指定する関数です。これは Lotus 1-2-3 R2.5J には互換関数はありませんでしたが @IF 関数と @ISERR 関数を組み合わせる形で同様の機能を実装できました(下図の赤枠部分参照):
2022060408


【検証12 XLOOKUP関数】
VLOOKUP 関数の上位互換となる関数です。エラー発生時の処理も関数内に含めることができます。これはエクセル 2021 で登場した新関数でもあり、Lotus 1-2-3 R2.5J には互換関数が存在していません。


【検証結果まとめ】
というわけで、検証結果をまとめるとこのようになりました:
#エクセルの関数1-2-3 の互換関数
1SUM@SUM
2AVERAGE@AVG
3COUNT@PURECOUNT
4COUNTA@COUNT
5IF@IF
6COUNTIF -
7COUNTIFS -
8SUMIF -
9SUMIFS -
10VLOOKUP@VLOOKUP
11IFERROR@IF と @ISERR
12XLOOKUP -


エクセルの基本関数12個のうち、6個は互換関数が(くどいようですが昭和時代の)1-2-3 にも存在していて、1つは他の関数の組み合わせでなんとかなる、という感じでした。「現在の基本」のうちの半分くらいは当時から使えていた、ということになります。1-2-3 は「条件付きで○○をする」という関数が全般的に弱い感じがしますが、逆に言うとそのような需要があってエクセルで実装されていったのだと思われます。特筆したいのは VLOOKUP 関数で、これは今でも中級者以上が比較的使う機会の多い関数だと思っていますが、その関数が 1-2-3 でも実装されていたんですね。オーパーツのような話で、私も少し驚きました。



なお、今回のブログエントリを書く際の参考文書はこちらでした。今となっては貴重品な、ナツメ社のハンドブックシリーズです:
IMG_20220530_080350_947



以前にこのブログで「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 こういうのは我ながら嫌いじゃない。

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 ではない)エクセル連携をする際に便利に使えそうなライブラリです。


このページのトップヘ