Home About
JavaScript , Node.js , Excel

Node.js での エクセルデータ読み込み データの入っている範囲を計算する

Node.js の xlsx モジュールを使って、データの入っている範囲を計算します。 その他、特定の行のデータを取得したり、列データを取得する方法について覚え書き。

potate

このエクセルデータのダウンロード: potate.xlsx

プロジェクトディレクトリをつくり初期化:

$ mkdir potate
$ cd potate
$ npm init --yes

必要なモジュールを入れる:

$ npm install xlsx
$ npm install underscore

index.js の作成:

$ touch index.js

まずは A1 のセルの内容を出力するコードを書きます。

const xlsx = require('xlsx')
const _ = require('underscore')

const xlsxFilename = 'potate.xlsx';

const workbook = xlsx.readFile(xlsxFilename);
const sheetNames = workbook.SheetNames;
const worksheet = workbook.Sheets[sheetNames[0]];

const rowIndex = 0;
const columnIndex = 0;

const cellAddress = xlsx.utils.encode_cell( {c:columnIndex, r:rowIndex} );
const cell = worksheet[cellAddress];

console.log(cell.w);

実行:

$ node index.js
id

id と出力されました。A1 には id という文字列が入っているのでこれでOKです。

では、1行目(rowIndex=0)はどの列までデータが入っているのでしょうか? それを調べてみます。

main.js を書き換えます。

/*
const rowIndex = 0;
const columnIndex = 0;

const cellAddress = xlsx.utils.encode_cell( {c:columnIndex, r:rowIndex} );
const cell = worksheet[cellAddress];
*/

const rowIndex = 0;

_.each( _.range(10), (columnIndex)=>{
    const cellAddress = xlsx.utils.encode_cell( {c:columnIndex, r:rowIndex} );
    const cell = worksheet[cellAddress];
    if( cell!=null ){
        console.log(`${columnIndex} : ${cell.w}`);
    } else {
        console.log(`${columnIndex} : ${cell}`);
    }
});

実行して確認:

$ node index.js
0 : id
1 : 品名
2 : スペック
3 : 価格
4 : undefined
5 : undefined
6 : undefined
7 : undefined
8 : undefined
9 : undefined

4列目 ( columnIndex=3 ) までデータが入っています。

しかし、今のコードでは 10列目まで調べていますが、10列以上あった場合に困ります。 undefined なセルになるまで繰り返し調べ、それを見つけたら、そのときの columnIndex を返す再帰関数 lastColumnIndex を書きます。

const lastColumnIndex = (rowIndex, columnIndex)=>{
    const cellAddress = xlsx.utils.encode_cell( {c:columnIndex, r:rowIndex} );
    const cell = worksheet[cellAddress];
    if( cell==null ){
        return (columnIndex-1);
    } else {
        return lastColumnIndex(rowIndex, (columnIndex+1));
    }
};

これを使うコードは:

const rowIndex = 0;
const columnIndex = lastColumnIndex(rowIndex, 0);
console.log(columnIndex);

これを実行すると 3 が出力されます。 先頭行の末尾の列インデックスは 3 なので、これでOKです。

今度は、行について調べます。 1列目の最大行はどこか、さきほどと同じように lastRowIndex という再帰関数を書きます。

const lastRowIndex = (rowIndex, columnIndex)=>{
    const cellAddress = xlsx.utils.encode_cell( {c:columnIndex, r:rowIndex} );
    const cell = worksheet[cellAddress];
    if( cell==null ){
        return (rowIndex-1);
    } else {
        return lastRowIndex((rowIndex+1), columnIndex);
    }
};

これを使うコードは:

const rowIndex    = lastRowIndex(0, 0);
const columnIndex = lastColumnIndex(0, 0);
console.log(`(${rowIndex}, ${columnIndex})`);

実行すると (3, 3) が出力されます。

それでは、この関数を使って、このエクセルシートを行ごとに上から下へ向かって処理するコードを書いてみます。

行を指定して、その行の値を配列にして返す関数:

const rowCellValues = (rowIndex)=>{
    return _.map( _.range(lastColumnIndex(rowIndex, 0)+1), (columnIndex)=> {
        const cellAddress = xlsx.utils.encode_cell( {c:columnIndex, r:rowIndex} );
        const cell = worksheet[cellAddress];
        return cell.w
    });
};

この関数を使って表全体を出力:

_.each( _.range(lastRowIndex(0, 0)+1), (rowIndex)=> {
    const values = rowCellValues(rowIndex);
    console.log(values);
});

実行:

$ node index.js
[ 'id', '品名', 'スペック', '価格' ]
[ 'a100', 'ポテトチップス うすしお味', '60g x12', '1241' ]
[ 'a101', 'ポテトチップス コンソメパンチ', '60g ×12', '1322' ]
[ 'a102', 'ポテトチップス のり塩', '60g ×12', '1179' ]

できました。

まとめ

完成したコード:

const xlsx = require('xlsx')
const _ = require('underscore')

const lastColumnIndex = (rowIndex, columnIndex)=>{
    const cellAddress = xlsx.utils.encode_cell( {c:columnIndex, r:rowIndex} );
    const cell = worksheet[cellAddress];
    if( cell==null ){
        return (columnIndex-1);
    } else {
        return lastColumnIndex(rowIndex, (columnIndex+1));
    }
};

const lastRowIndex = (rowIndex, columnIndex)=>{
    const cellAddress = xlsx.utils.encode_cell( {c:columnIndex, r:rowIndex} );
    const cell = worksheet[cellAddress];
    if( cell==null ){
        return (rowIndex-1);
    } else {
        return lastRowIndex((rowIndex+1), columnIndex);
    }
};

const rowCellValues = (rowIndex)=>{
    return _.map( _.range(lastColumnIndex(rowIndex, 0)+1), (columnIndex)=> {
        const cellAddress = xlsx.utils.encode_cell( {c:columnIndex, r:rowIndex} );
        const cell = worksheet[cellAddress];
        return cell.w
    });
};

const columnCellValues = (columnIndex)=>{
    return _.map( _.range(lastRowIndex(columnIndex, 0)+1), (rowIndex)=> {
        const cellAddress = xlsx.utils.encode_cell( {c:columnIndex, r:rowIndex} );
        const cell = worksheet[cellAddress];
        return cell.w
    });
};



const xlsxFilename = 'potate.xlsx';

const workbook = xlsx.readFile(xlsxFilename);
const sheetNames = workbook.SheetNames;
const worksheet = workbook.Sheets[sheetNames[0]];

_.each( _.range(lastRowIndex(0, 0)+1), (rowIndex)=> {
    const values = rowCellValues(rowIndex);
    console.log(values);
});

これでセルが存在している部分だけに限定してそのセルの値を取得する方法がわかりました。

追伸 特定の列のデータをまとめて取得したい場合

たとえば、2列目の品名だけを全部取り出したい場合を考えます。

列を指定してその列にある値を配列にして返す関数:

const columnCellValues = (columnIndex)=>{
    return _.map( _.range(lastRowIndex(columnIndex, 0)+1), (rowIndex)=> {
        const cellAddress = xlsx.utils.encode_cell( {c:columnIndex, r:rowIndex} );
        const cell = worksheet[cellAddress];
        return cell.w
    });
};

これを使うコード:

const productNames = columnCellValues(1);
console.log(productNames);

実行結果:

[ '品名', 'ポテトチップス うすしお味', 'ポテトチップス コンソメパンチ', 'ポテトチップス のり塩' ]