node.jsでExcelファイルを読み込み、データを形成してみた際に、
range(Excel内の入力してあるセルの範囲)が正確に取れないことがあったので、
それについて書こうと思います。
今回使用したExcelファイルはこちら(test.xlsx)
xlsxの使い方
xlsxファイル読み込み
const xlsx = require('xlsx');
let workbook = xlsx.readFile('test.xlsx'); //オブジェクトの取得
console.log(workbook);
/*
情報が多いので割愛します。(作成者情報とか)
使用するキーは「sheetNames」「Sheets」くらいですかね
*/
シート名一覧の取得
let sheetNames = workbook.SheetNames; //workbookオブジェクトからシートネームを配列で取得
console.log(sheetNames);
/*
[ 'Sheet1' ]
*/
ワークシートオブジェクトを取得
let worksheet = workbook.Sheets['Sheet1'];
console.log(worksheet);
/*
{ '!ref': 'A1:C2',
A1:
{ t: 's',
v: 'test1',
r: '<t>test1</t><phoneticPr fontId="1"/>',
h: 'test1',
w: 'test1' },
B1:
{ t: 's',
v: 'test2',
r: '<t>test2</t><phoneticPr fontId="1"/>',
h: 'test2',
w: 'test2' },
C1:
{ t: 's',
v: 'test3',
r: '<t>test3</t><phoneticPr fontId="1"/>',
h: 'test3',
w: 'test3' },
A2:
{ t: 's',
v: 'test4',
r: '<t>test4</t><phoneticPr fontId="1"/>',
h: 'test4',
w: 'test4' },
B2:
{ t: 's',
v: 'test5',
r: '<t>test5</t><phoneticPr fontId="1"/>',
h: 'test5',
w: 'test5' },
C2:
{ t: 's',
v: 'test6',
r: '<t>test6</t><phoneticPr fontId="1"/>',
h: 'test6',
w: 'test6' },
'!margins':
{ left: 0.7,
right: 0.7,
top: 0.75,
bottom: 0.75,
header: 0.3,
footer: 0.3 } }
*/
シート内、セル範囲の取得
let range = worksheet['!ref'];
console.log(range);
/*
A1:C2
*/
こちらなんですが、なぜか正確な情報が取得できない場合があります。
Excelファイル作りたての時は正常にrangeがとれたのですが、
プロジェクト等でExcelファイルを更新したりすると再度読み込んだ際にずれる？
最後にまた触れようと思います。
セルオブジェクトの取得
var cell = worksheet['A1']; //セルのアドレスを渡してあげる
console.log(cell);
/*
{ t: 's', //type:この場合はstring
v: 'test1', //value
r: '<t>test1</t><phoneticPr fontId="1"/>',
h: 'test1',
w: 'test1' }
*/
セルの開始位置と終了位置を取得
const xlsx = require('xlsx');
const utils = xlsx.utils;
console.log(utils.decode_cell(range)); //先ほどのrangeをいれてあげる
/*
{ s: { c: 0, r: 0 }, e: { c: 2, r: 1 } } //s:start、e:end、c:column(横列)、r:row(縦列)
*/
for文で値を取得
"use strict";
const xlsx = require('xlsx');
const utils = xlsx.utils;
var workbook = xlsx.readFile('src/cli/luis//test.xlsx')
var worksheet = workbook.Sheets['Sheet1'];
var range = worksheet['!ref'];
var rangeVal = utils.decode_range(range);
for (var r=rangeVal.s.r ; r <= rangeVal.e.r ; r++) { //縦列のループ
for (var c=rangeVal.s.c ; c <= rangeVal.e.c ; c++) { //横列のループ
var adr = utils.encode_cell({c:c, r:r}); //アドレス取得
var cell = worksheet[adr]; //セルのオブジェクトを取得
console.log(cell.v) //セルのvalueを表示
}
}
/*
test1
test2
test3
test4
test5
test6
*/
rangeでうまく値が取れなかった場合
先ほども言ったように原因はわかりませんがrangeがうまくとれない場合があります。。。
今回のケースだと
{ s: { c: 0, r: 0 }, e: { c: 2, r: 1 } }
が正しいのですが、
{ s: { c: 0, r: 0 }, e: { c: 8, r: 2 } }
とかなっていたり。。。
もうrangeは信用できん！for文もやだ！
ということで、下記のように書いてみました。
"use strict";
const xlsx = require('xlsx');
const utils = xlsx.utils;
var workbook = xlsx.readFile('src/cli/luis//test.xlsx')
var worksheet = workbook.Sheets['Sheet1'];
var keys = Object.keys(worksheet) //worksheetオブジェクトのキーをすべて取得
keys = keys.slice(1,-1) //不要なキーを削除（先頭、最後尾）
var index = keys.indexOf("A2") //1行の区切りのインデックスを取得する。この場合は3。
var arr = [];
for (let i = 0; i < keys.length; i += index) {
arr.push(keys.slice(i, i + index))
}
var ret = arr.map(e => e.map(k => worksheet[k].v)) //worksheetオブジェクトから該当するキーのvalueを取得
console.log(ret)
/*
[ [ 'test1', 'test2', 'test3' ], [ 'test4', 'test5', 'test6' ] ]
*/
2次元配列で1行ごとにvalueを取得してみました。
最後に
業務でExcelからデータを読み込むことってあると思いますが、
汎用的なparserを自分で持っておいた方が良いなと感じました。
また、Excelは途中でフォーマット変更されるとparserの修正が大変なので、
後々変更がかからないよう最初にしっかり決めることも大事だと思いました。
以上、ありがとうございました。