sheetjs/test.mjs

2872 lines
113 KiB
JavaScript
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

/* xlsx.js (C) 2013-present SheetJS -- http://sheetjs.com */
/* vim: set ts=2: */
/*jshint mocha:true */
/* eslint-env mocha */
/*global process, document, require */
/*global ArrayBuffer, Uint8Array */
/*::
declare type EmptyFunc = (() => void) | null;
declare type DescribeIt = { (desc:string, test:EmptyFunc):void; skip(desc:string, test:EmptyFunc):void; };
declare var describe : DescribeIt;
declare var it: DescribeIt;
declare var before:(test:EmptyFunc)=>void;
declare var afterEach:(test:EmptyFunc)=>void;
declare var cptable: any;
*/
import * as fs from 'fs';
import * as assert from 'assert';
import * as X from './xlsx.mjs';
X.set_fs(fs);
import * as cptable from './dist/cpexcel.full.mjs';
X.set_cptable(cptable);
import XLSX_ZAHL from './dist/xlsx.zahl.mjs';
var DIF_XL = true;
var browser = typeof document !== 'undefined';
var Buffer_from = /*::(*/function(){}/*:: :any)*/;
if(typeof Buffer !== 'undefined') {
var nbfs = !Buffer.from;
if(!nbfs) try { Buffer.from("foo", "utf8"); } catch(e) { nbfs = true; }
Buffer_from = nbfs ? function(buf, enc) { return (enc) ? new Buffer(buf, enc) : new Buffer(buf); } : Buffer.from.bind(Buffer);
// $FlowIgnore
if(!Buffer.alloc) Buffer.alloc = function(n) { return new Buffer(n); };
}
var opts = ({cellNF: true}/*:any*/);
var TYPE = browser ? "binary" : "buffer";
opts.type = TYPE;
var fullex = [".xlsb", /*".xlsm",*/ ".xlsx"/*, ".xlml", ".xls"*/];
var ofmt = ["xlsb", "xlsm", "xlsx", "ods", "biff2", "biff5", "biff8", "xlml", "sylk", "dif", "dbf", "eth", "fods", "csv", "txt", "html"];
var ex = fullex.slice(); ex = ex.concat([".ods", ".xls", ".xml", ".fods"]);
if(typeof process != 'undefined' && ((process||{}).env)) {
opts.WTF = true;
opts.cellStyles = true;
if(process.env.FMTS === "full") process.env.FMTS = ex.join(":");
if(process.env.FMTS) ex=process.env.FMTS.split(":").map(function(x){return x[0]==="."?x:"."+x;});
}
var exp = ex.map(function(x){ return x + ".pending"; });
function test_file(x){ return ex.indexOf(x.slice(-5))>=0||exp.indexOf(x.slice(-13))>=0 || ex.indexOf(x.slice(-4))>=0||exp.indexOf(x.slice(-12))>=0; }
var files = [], fileA = [];
if(!browser) {
var _files = fs.existsSync('tests.lst') ? fs.readFileSync('tests.lst', 'utf-8').split("\n").map(function(x) { return x.trim(); }) : fs.readdirSync('test_files');
for(var _filesi = 0; _filesi < _files.length; ++_filesi) if(test_file(_files[_filesi])) files.push(_files[_filesi]);
var _fileA = fs.existsSync('tests/testA.lst') ? fs.readFileSync('tests/testA.lst', 'utf-8').split("\n").map(function(x) { return x.trim(); }) : [];
for(var _fileAi = 0; _fileAi < _fileA.length; ++_fileAi) if(test_file(_fileA[_fileAi])) fileA.push(_fileA[_fileAi]);
}
/* Excel enforces 31 character sheet limit, although technical file limit is 255 */
function fixsheetname(x/*:string*/)/*:string*/ { return x.substr(0,31); }
function stripbom(x/*:string*/)/*:string*/ { return x.replace(/^\ufeff/,""); }
function fixcsv(x/*:string*/)/*:string*/ { return stripbom(x).replace(/\t/g,",").replace(/#{255}/g,"").replace(/"/g,"").replace(/[\n\r]+/g,"\n").replace(/\n*$/,""); }
function fixjson(x/*:string*/)/*:string*/ { return x.replace(/[\r\n]+$/,""); }
var dir = "./test_files/";
var dirwp = dir + "artifacts/wps/", dirqp = dir + "artifacts/quattro/";
var paths = {
aadbf: dirwp + 'write.dbf',
aadif: dirwp + 'write.dif',
aaxls: dirwp + 'write.xls',
aaxlsx: dirwp + 'write.xlsx',
aaxml: dirwp + 'write.xml',
abcsv: dirqp + 'write_.csv',
abdif: dirqp + 'write_.dif',
abslk: dirqp + 'write_.slk',
abx57: dirqp + 'write_57.xls',
abwb2: dirqp + 'write_6.wb2',
abwb2b: dirqp + 'write_6b.wb2',
abwb3: dirqp + 'write_8.wb3',
abqpw: dirqp + 'write_9.qpw',
abx97: dirqp + 'write_97.xls',
abwks: dirqp + 'write_L1.wks',
abwk1: dirqp + 'write_L2.wk1',
abwk3: dirqp + 'write_L3.wk3',
abwk4: dirqp + 'write_L45.wk4',
ab123: dirqp + 'write_L9.123',
ab124: dirqp + 'write_L97.123',
abwke2: dirqp + 'write_Led.wke',
abwq1: dirqp + 'write_qpdos.wq1',
abwb1: dirqp + 'write_qpw.wb1',
afxls: dir + 'AutoFilter.xls',
afxml: dir + 'AutoFilter.xml',
afods: dir + 'AutoFilter.ods',
afxlsx: dir + 'AutoFilter.xlsx',
afxlsb: dir + 'AutoFilter.xlsb',
asxls: dir + 'author_snowman.xls',
asxls5: dir + 'author_snowman.xls5',
asxml: dir + 'author_snowman.xml',
asods: dir + 'author_snowman.ods',
asxlsx: dir + 'author_snowman.xlsx',
asxlsb: dir + 'author_snowman.xlsb',
cpxls: dir + 'custom_properties.xls',
cpxml: dir + 'custom_properties.xls.xml',
cpxlsx: dir + 'custom_properties.xlsx',
cpxlsb: dir + 'custom_properties.xlsb',
cssxls: dir + 'cell_style_simple.xls',
cssxml: dir + 'cell_style_simple.xml',
cssxlsx: dir + 'cell_style_simple.xlsx',
cssxlsb: dir + 'cell_style_simple.xlsb',
cstxls: dir + 'comments_stress_test.xls',
cstxml: dir + 'comments_stress_test.xls.xml',
cstxlsx: dir + 'comments_stress_test.xlsx',
cstxlsb: dir + 'comments_stress_test.xlsb',
cstods: dir + 'comments_stress_test.ods',
cwxls: dir + 'column_width.xls',
cwxls5: dir + 'column_width.biff5',
cwxml: dir + 'column_width.xml',
cwxlsx: dir + 'column_width.xlsx',
cwxlsb: dir + 'column_width.xlsb',
cwslk: dir + 'column_width.slk',
dnsxls: dir + 'defined_names_simple.xls',
dnsxml: dir + 'defined_names_simple.xml',
dnsxlsx: dir + 'defined_names_simple.xlsx',
dnsxlsb: dir + 'defined_names_simple.xlsb',
dnsslk: dir + 'defined_names_simple.slk',
dnuxls: dir + 'defined_names_unicode.xls',
dnuxml: dir + 'defined_names_unicode.xml',
dnuods: dir + 'defined_names_unicode.ods',
dnuxlsx: dir + 'defined_names_unicode.xlsx',
dnuxlsb: dir + 'defined_names_unicode.xlsb',
dtxls: dir + 'xlsx-stream-d-date-cell.xls',
dtxml: dir + 'xlsx-stream-d-date-cell.xls.xml',
dtxlsx: dir + 'xlsx-stream-d-date-cell.xlsx',
dtxlsb: dir + 'xlsx-stream-d-date-cell.xlsb',
dtfxlsx: dir + 'DataTypesFormats.xlsx',
fstxls: dir + 'formula_stress_test.xls',
fstxml: dir + 'formula_stress_test.xls.xml',
fstxlsx: dir + 'formula_stress_test.xlsx',
fstxlsb: dir + 'formula_stress_test.xlsb',
fstods: dir + 'formula_stress_test.ods',
hlxls: dir + 'hyperlink_stress_test_2011.xls',
hlxml: dir + 'hyperlink_stress_test_2011.xml',
hlxlsx: dir + 'hyperlink_stress_test_2011.xlsx',
hlxlsb: dir + 'hyperlink_stress_test_2011.xlsb',
ilxls: dir + 'internal_link.xls',
ilxls5: dir + 'internal_link.biff5',
ilxml: dir + 'internal_link.xml',
ilxlsx: dir + 'internal_link.xlsx',
ilxlsb: dir + 'internal_link.xlsb',
ilods: dir + 'internal_link.ods',
lonxls: dir + 'LONumbers.xls',
lonxlsx: dir + 'LONumbers.xlsx',
m19xlsx: dir + 'metadata_2019.xlsx',
m19xlsb: dir + 'metadata_2019.xlsb',
mcxls: dir + 'merge_cells.xls',
mcxml: dir + 'merge_cells.xls.xml',
mcxlsx: dir + 'merge_cells.xlsx',
mcxlsb: dir + 'merge_cells.xlsb',
mcods: dir + 'merge_cells.ods',
nfxls: dir + 'number_format.xls',
nfxml: dir + 'number_format.xls.xml',
nfxlsx: dir + 'number_format.xlsm',
nfxlsb: dir + 'number_format.xlsb',
olxls: dir + 'outline.xls',
olxls5: dir + 'outline.biff5',
olxlsx: dir + 'outline.xlsx',
olxlsb: dir + 'outline.xlsb',
olods: dir + 'outline.ods',
pmxls: dir + 'page_margins_2016.xls',
pmxls5: dir + 'page_margins_2016_5.xls',
pmxml: dir + 'page_margins_2016.xml',
pmxlsx: dir + 'page_margins_2016.xlsx',
pmxlsb: dir + 'page_margins_2016.xlsb',
rhxls: dir + 'row_height.xls',
rhxls5: dir + 'row_height.biff5',
rhxml: dir + 'row_height.xml',
rhxlsx: dir + 'row_height.xlsx',
rhxlsb: dir + 'row_height.xlsb',
rhslk: dir + 'row_height.slk',
svxls: dir + 'sheet_visibility.xls',
svxls5: dir + 'sheet_visibility5.xls',
svxml: dir + 'sheet_visibility.xml',
svxlsx: dir + 'sheet_visibility.xlsx',
svxlsb: dir + 'sheet_visibility.xlsb',
svods: dir + 'sheet_visibility.ods',
swcxls: dir + 'apachepoi_SimpleWithComments.xls',
swcxml: dir + '2011/apachepoi_SimpleWithComments.xls.xml',
swcxlsx: dir + 'apachepoi_SimpleWithComments.xlsx',
swcxlsb: dir + '2013/apachepoi_SimpleWithComments.xlsx.xlsb'
};
function pathit(p, ext) { return ext.map(function(n) { return paths[p + n]; }); }
var FSTPaths = pathit("fst", ["xlsx", "xlsb", "xls", "xml", "ods"]);
var CSTPaths = pathit("cst", ["xlsx", "xlsb", "xls", "xml", "ods"]);
var MCPaths = pathit("mc", ["xlsx", "xlsb", "xls", "xml", "ods"]);
var CSSPaths = pathit("css", ["xlsx", "xlsb", "xls", "xml"]);
var NFPaths = pathit("nf", ["xlsx", "xlsb", "xls", "xml"]);
var DTPaths = pathit("dt", ["xlsx", "xlsb", "xls", "xml"]);
var HLPaths = pathit("hl", ["xlsx", "xlsb", "xls", "xml"]);
var ILPaths = pathit("il", ["xlsx", "xlsb", "xls", "xml", "ods", "xls5"]);
var OLPaths = pathit("ol", ["xlsx", "xlsb", "xls", "ods", "xls5"]);
var PMPaths = pathit("pm", ["xlsx", "xlsb", "xls", "xml", "xls5"]);
var SVPaths = pathit("sv", ["xlsx", "xlsb", "xls", "xml", "xls5"]);
var CWPaths = pathit("cw", ["xlsx", "xlsb", "xls", "xml", "xls5", "slk"]);
var RHPaths = pathit("rh", ["xlsx", "xlsb", "xls", "xml", "xls5", "slk"]);
var artifax = [
"cstods", "cstxls", "cstxlsb", "cstxlsb", "cstxml", "aadbf", "aadif",
"aaxls", "aaxml", "aaxlsx", "ab123", "ab124", "abcsv", "abdif", "abqpw",
"abslk", "abwb1", "abwb2", "abwb3", "abwk1", "abwk3", "abwk4", "abwks",
"abwq1", "abx57", "abx97", "abwke2", "abwb2b"
].map(function(x) { return paths[x]; });
function parsetest(x/*:string*/, wb/*:Workbook*/, full/*:boolean*/, ext/*:?string*/) {
ext = (ext ? " [" + ext + "]": "");
if(!full && ext) return;
describe(x + ext + ' should have all bits', function() {
var sname = dir + '2016/' + x.substr(x.lastIndexOf('/')+1) + '.sheetnames';
if(!fs.existsSync(sname)) sname = dir + '2011/' + x.substr(x.lastIndexOf('/')+1) + '.sheetnames';
if(!fs.existsSync(sname)) sname = dir + '2013/' + x.substr(x.lastIndexOf('/')+1) + '.sheetnames';
it('should have all sheets', function() {
wb.SheetNames.forEach(function(y) { assert.ok(wb.Sheets[y], 'bad sheet ' + y); });
});
if(fs.existsSync(sname)) it('should have the right sheet names', function() {
var file = fs.readFileSync(sname, 'utf-8').replace(/\r/g,"");
var names = wb.SheetNames.map(fixsheetname).join("\n") + "\n";
if(file.length && !x.match(/artifacts/)) assert.equal(names, file);
});
});
describe(x + ext + ' should generate CSV', function() {
wb.SheetNames.forEach(function(ws, i) {
it('#' + i + ' (' + ws + ')', function() {
X.utils.sheet_to_csv(wb.Sheets[ws]);
});
});
});
describe(x + ext + ' should generate JSON', function() {
wb.SheetNames.forEach(function(ws, i) {
it('#' + i + ' (' + ws + ')', function() {
X.utils.sheet_to_json(wb.Sheets[ws]);
});
});
});
describe(x + ext + ' should generate formulae', function() {
wb.SheetNames.forEach(function(ws, i) {
it('#' + i + ' (' + ws + ')', function() {
X.utils.sheet_to_formulae(wb.Sheets[ws]);
});
});
});
if(!full) return;
var getfile = function(dir, x, i, type) {
var name = (dir + x + '.' + i + type);
var root = "";
if(x.substr(-5) === ".xlsb") {
root = x.slice(0,-5);
if(!fs.existsSync(name)) name=(dir + root + '.xlsx.' + i + type);
if(!fs.existsSync(name)) name=(dir + root + '.xlsm.' + i + type);
if(!fs.existsSync(name)) name=(dir + root + '.xls.' + i + type);
}
if(x.substr(-4) === ".xls") {
root = x.slice(0,-4);
if(!fs.existsSync(name)) name=(dir + root + '.xlsx.' + i + type);
if(!fs.existsSync(name)) name=(dir + root + '.xlsm.' + i + type);
if(!fs.existsSync(name)) name=(dir + root + '.xlsb.' + i + type);
}
return name;
};
describe(x + ext + ' should generate correct CSV output', function() {
wb.SheetNames.forEach(function(ws, i) {
var name = getfile(dir, x, i, ".csv");
if(fs.existsSync(name)) it('#' + i + ' (' + ws + ')', function() {
var file = fs.readFileSync(name, 'utf-8');
var csv = X.utils.sheet_to_csv(wb.Sheets[ws]);
assert.equal(fixcsv(csv), fixcsv(file), "CSV badness");
});
});
});
if(typeof JSON !== 'undefined') describe(x + ext + ' should generate correct JSON output', function() {
wb.SheetNames.forEach(function(ws, i) {
var rawjson = getfile(dir, x, i, ".rawjson");
if(fs.existsSync(rawjson)) it('#' + i + ' (' + ws + ')', function() {
var file = fs.readFileSync(rawjson, 'utf-8');
var json = X.utils.sheet_to_json(wb.Sheets[ws],{raw:true});
assert.equal(JSON.stringify(json), fixjson(file), "JSON badness");
});
var jsonf = getfile(dir, x, i, ".json");
if(fs.existsSync(jsonf)) it('#' + i + ' (' + ws + ')', function() {
var file = fs.readFileSync(jsonf, 'utf-8');
var json = X.utils.sheet_to_json(wb.Sheets[ws], {raw:false});
assert.equal(JSON.stringify(json), fixjson(file), "JSON badness");
});
});
});
if(fs.existsSync(dir + '2011/' + x + '.xml'))
describe(x + ext + '.xml from 2011', function() {
it('should parse', function() {
/*var wb = */X.readFile(dir + '2011/' + x + '.xml', opts);
});
});
if(fs.existsSync(dir + '2013/' + x + '.xlsb'))
describe(x + ext + '.xlsb from 2013', function() {
it('should parse', function() {
/*var wb = */X.readFile(dir + '2013/' + x + '.xlsb', opts);
});
});
if(fs.existsSync(dir + x + '.xml' + ext))
describe(x + '.xml', function() {
it('should parse', function() {
/*var wb = */X.readFile(dir + x + '.xml', opts);
});
});
}
var wbtable = {};
if(!browser) describe('should parse test files', function() {
files.forEach(function(x) {
if(x.slice(-8) == ".pending" || !fs.existsSync(dir + x)) return;
it(x, function() {
var wb = X.readFile(dir + x, opts);
wbtable[dir + x] = wb;
parsetest(x, wb, true);
});
fullex.forEach(function(ext) {
it(x + ' [' + ext + ']', function(){
var wb = wbtable[dir + x];
if(!wb) wb = X.readFile(dir + x, opts);
wb = X.read(X.write(wb, {type:"buffer", bookType:ext.replace(/\./,"")}), {WTF:opts.WTF, cellNF: true});
parsetest(x, wb, ext.replace(/\./,"") !== "xlsb", ext);
});
});
});
fileA.forEach(function(x) {
if(x.slice(-8) == ".pending" || !fs.existsSync(dir + x)) return;
it(x, function() {
var wb = X.readFile(dir + x, {WTF:opts.WTF, sheetRows:10});
parsetest(x, wb, false);
});
});
});
function get_cell(ws/*:Worksheet*/, addr/*:string*/) {
if(!Array.isArray(ws)) return ws[addr];
var a = X.utils.decode_cell(addr);
return (ws[a.r]||[])[a.c];
}
function each_cell(ws, f) {
if(Array.isArray(ws)) ws.forEach(function(row) { if(row) row.forEach(f); });
else Object.keys(ws).forEach(function(addr) { if(addr[0] === "!" || !ws.hasOwnProperty(addr)) return; f(ws[addr]); });
}
function each_sheet(wb, f) { wb.SheetNames.forEach(function(n, i) { f(wb.Sheets[n], i); }); }
/* comments_stress_test family */
function check_comments(wb) {
var ws0 = wb.Sheets["Sheet2"];
assert.equal(get_cell(ws0,"A1").c[0].a, 'Author');
assert.equal(get_cell(ws0,"A1").c[0].t, 'Author:\nGod thinks this is good');
assert.equal(get_cell(ws0,"C1").c[0].a, 'Author');
assert.equal(get_cell(ws0,"C1").c[0].t, 'I really hope that xlsx decides not to use magic like rPr');
var ws3 = wb.Sheets["Sheet4"];
assert.equal(get_cell(ws3,"B1").c[0].a, 'Author');
assert.equal(get_cell(ws3,"B1").c[0].t, 'The next comment is empty');
assert.equal(get_cell(ws3,"B2").c[0].a, 'Author');
assert.equal(get_cell(ws3,"B2").c[0].t, '');
}
describe('parse options', function() {
var html_cell_types = ['s'];
describe('cell', function() {
it('XLSX should generate HTML by default', function() {
var wb = X.read(fs.readFileSync(paths.cstxlsx), {type:TYPE});
var ws = wb.Sheets["Sheet1"];
each_cell(ws, function(cell) {
assert.ok(html_cell_types.indexOf(cell.t) === -1 || cell.h);
});
});
it('XLSX should not generate HTML when requested', function() {
var wb = X.read(fs.readFileSync(paths.cstxlsx), {type:TYPE, cellHTML:false});
var ws = wb.Sheets["Sheet1"];
each_cell(ws, function(cell) {
assert.ok(typeof cell.h === 'undefined');
});
});
it('should generate formulae by default', function() {
FSTPaths.forEach(function(p) {
var wb = X.read(fs.readFileSync(p), {type:TYPE});
var found = false;
wb.SheetNames.forEach(function(s) {
each_cell(wb.Sheets[s], function(cell) {
if(typeof cell.f !== 'undefined') return (found = true);
});
});
assert.ok(found);
});
});
it('should not generate formulae when requested', function() {
FSTPaths.forEach(function(p) {
var wb =X.read(fs.readFileSync(p),{type:TYPE,cellFormula:false});
wb.SheetNames.forEach(function(s) {
each_cell(wb.Sheets[s], function(cell) {
assert.ok(typeof cell.f === 'undefined');
});
});
});
});
it('should generate formatted text by default', function() {
FSTPaths.forEach(function(p) {
var wb = X.read(fs.readFileSync(p),{type:TYPE});
var found = false;
wb.SheetNames.forEach(function(s) {
var ws = wb.Sheets[s];
each_cell(ws, function(cell) {
if(typeof cell.w !== 'undefined') return (found = true);
});
});
assert.ok(found);
});
});
it('should not generate formatted text when requested', function() {
FSTPaths.forEach(function(p) {
var wb =X.read(fs.readFileSync(p),{type:TYPE, cellText:false});
wb.SheetNames.forEach(function(s) {
var ws = wb.Sheets[s];
each_cell(ws, function(cell) {
assert.ok(typeof cell.w === 'undefined');
});
});
});
});
it('should not generate number formats by default', function() {
NFPaths.forEach(function(p) {
var wb = X.read(fs.readFileSync(p), {type:TYPE});
wb.SheetNames.forEach(function(s) {
var ws = wb.Sheets[s];
each_cell(ws, function(cell) {
assert.ok(typeof cell.z === 'undefined');
});
});
});
});
it('should generate number formats when requested', function() {
NFPaths.forEach(function(p) {
var wb = X.read(fs.readFileSync(p), {type:TYPE, cellNF: true});
wb.SheetNames.forEach(function(s) {
var ws = wb.Sheets[s];
each_cell(ws, function(cell) {
assert.ok(cell.t!== 'n' || typeof cell.z !== 'undefined');
});
});
});
});
it('should not generate cell styles by default', function() {
CSSPaths.forEach(function(p) {
var wb = X.read(fs.readFileSync(p), {type:TYPE, WTF:true});
wb.SheetNames.forEach(function(s) {
var ws = wb.Sheets[s];
each_cell(ws, function(cell) {
assert.ok(typeof cell.s === 'undefined');
});
});
});
});
it('should generate cell styles when requested', function() {
/* TODO: XLS / XLML */
[paths.cssxlsx /*, paths.cssxlsb, paths.cssxls, paths.cssxml*/].forEach(function(p) {
var wb = X.read(fs.readFileSync(p), {type:TYPE, cellStyles:true});
var found = false;
each_sheet(wb, function(ws/*::, i*/) { /*:: void i; */each_cell(ws, function(cell) {
if(typeof cell.s !== 'undefined') return (found = true);
}); });
assert.ok(found);
});
});
it('should not generate cell dates by default', function() {
DTPaths.forEach(function(p) {
var wb = X.read(fs.readFileSync(p), {type:TYPE});
each_sheet(wb, function(ws/*::, i*/) { /*:: void i; */each_cell(ws, function(cell) {
assert.ok(cell.t !== 'd');
}); });
});
});
it('should generate cell dates when requested', function() {
DTPaths.forEach(function(p) {
var wb = X.read(fs.readFileSync(p), {type:TYPE, cellDates: true, WTF:true});
var found = false;
each_sheet(wb, function(ws/*::, i*/) { /*:: void i; */each_cell(ws, function(cell) {
if(cell.t === 'd') return (found = true);
}); });
assert.ok(found);
});
});
it('should preserve `_xlfn.` only when requested', function() {
var wb = { SheetNames: ["Sheet1"], Sheets: { Sheet1: {
"!ref": "A1:A1",
"A1": { t:"n", v:2, f:"_xlfn.IFS(2>3,1,3>2,2)"}
} } };
var str = X.write(wb, {bookType: "xlsx", type: "binary"});
var wb2 = X.read(str, {type: "binary"});
/*jshint -W069 */
assert.equal(wb2.Sheets["Sheet1"]["A1"].f, "IFS(2>3,1,3>2,2)");
var wb3 = X.read(str, {type: "binary", xlfn: true});
assert.equal(wb3.Sheets["Sheet1"]["A1"].f, "_xlfn.IFS(2>3,1,3>2,2)");
/*jshint +W069 */
});
});
describe('sheet', function() {
it('should not generate sheet stubs by default', function() {
MCPaths.forEach(function(p) {
var wb = X.read(fs.readFileSync(p), {type:TYPE});
assert.throws(function() { return get_cell(wb.Sheets["Merge"], "A2").v; });
});
});
it('should generate sheet stubs when requested', function() {
MCPaths.forEach(function(p) {
var wb = X.read(fs.readFileSync(p), {type:TYPE, sheetStubs:true});
assert.ok(get_cell(wb.Sheets["Merge"], "A2").t == 'z');
});
});
it('should handle stub cells', function() {
MCPaths.forEach(function(p) {
var wb = X.read(fs.readFileSync(p), {type:TYPE, sheetStubs:true});
X.utils.sheet_to_csv(wb.Sheets["Merge"]);
X.utils.sheet_to_json(wb.Sheets["Merge"]);
X.utils.sheet_to_formulae(wb.Sheets["Merge"]);
ofmt.forEach(function(f) { if(f != "dbf") X.write(wb, {type:TYPE, bookType:f}); });
});
});
function checkcells(wb, A46, B26, C16, D2) {
[ ["A46", A46], ["B26", B26], ["C16", C16], ["D2", D2] ].forEach(function(r) {
assert.ok((typeof get_cell(wb.Sheets["Text"], r[0]) !== 'undefined') == r[1]);
});
}
it('should read all cells by default', function() { FSTPaths.forEach(function(p) {
checkcells(X.read(fs.readFileSync(p), {type:TYPE}), true, true, true, true);
}); });
it('sheetRows n=30', function() { FSTPaths.forEach(function(p) {
checkcells(X.read(fs.readFileSync(p), {type:TYPE, sheetRows:30}), false, true, true, true);
}); });
it('sheetRows n=20', function() { FSTPaths.forEach(function(p) {
checkcells(X.read(fs.readFileSync(p), {type:TYPE, sheetRows:20}), false, false, true, true);
}); });
it('sheetRows n=10', function() { FSTPaths.forEach(function(p) {
checkcells(X.read(fs.readFileSync(p), {type:TYPE, sheetRows:10}), false, false, false, true);
}); });
it('sheetRows n=1', function() { ofmt.forEach(function(fmt) {
[TYPE, "base64", "binary", "array"].forEach(function(ot) {
var data = [[1,2],[3,4],[5,6]];
var ws = X.utils.aoa_to_sheet(data);
assert.ok(ws['!ref'] === "A1:B3");
var wb = X.utils.book_new();
X.utils.book_append_sheet(wb, ws, "Sheet1");
var bs = X.write(wb, { bookType: fmt, type: ot, WTF:true });
var wb0 = X.read(bs, { type: ot, WTF:true });
var ws0 = wb0.Sheets["Sheet1"];
assert.equal(ws0['!ref'], "A1:B3");
assert.equal(get_cell(ws0, "A1").v, fmt == "dbf" ? "1" : 1);
assert.equal(get_cell(ws0, "B2").v, 4);
assert.equal(get_cell(ws0, "A3").v, 5);
var wb1 = X.read(bs, { type: ot, sheetRows: 1 });
var ws1 = wb1.Sheets["Sheet1"];
assert.equal(ws1['!ref'], "A1:B1");
assert.equal(get_cell(ws1, "A1").v, fmt == "dbf" ? "1" : 1);
assert.ok(!get_cell(ws1, "B2"));
assert.ok(!get_cell(ws1, "A3"));
if(ws1['!fullref']) assert.equal(ws1['!fullref'], "A1:B3");
var wb2 = X.read(bs, { type: ot, sheetRows: 2 });
var ws2 = wb2.Sheets["Sheet1"];
assert.equal(ws2['!ref'], "A1:B2");
assert.equal(get_cell(ws2, "A1").v, fmt == "dbf" ? "1" : 1);
assert.equal(get_cell(ws2, "B2").v, 4);
assert.ok(!get_cell(ws2, "A3"));
if(ws2['!fullref']) assert.equal(ws2['!fullref'], "A1:B3");
var wb3 = X.read(bs, { type: ot, sheetRows: 3 });
var ws3 = wb3.Sheets["Sheet1"];
assert.equal(ws3['!ref'], "A1:B3");
assert.equal(get_cell(ws3, "A1").v, fmt == "dbf" ? "1" : 1);
assert.equal(get_cell(ws3, "B2").v, 4);
assert.equal(get_cell(ws3, "A3").v, 5);
if(ws3['!fullref']) assert.equal(ws3['!fullref'], "A1:B3");
}); }); });
});
describe('book', function() {
it('bookSheets should not generate sheets', function() {
MCPaths.forEach(function(p) {
var wb = X.read(fs.readFileSync(p), {type:TYPE, bookSheets:true});
assert.ok(typeof wb.Sheets === 'undefined');
});
});
it('bookProps should not generate sheets', function() {
NFPaths.forEach(function(p) {
var wb = X.read(fs.readFileSync(p), {type:TYPE, bookProps:true});
assert.ok(typeof wb.Sheets === 'undefined');
});
});
it('bookProps && bookSheets should not generate sheets', function() {
PMPaths.forEach(function(p) {
if(!fs.existsSync(p)) return;
var wb = X.read(fs.readFileSync(p), {type:TYPE, bookProps:true, bookSheets:true});
assert.ok(typeof wb.Sheets === 'undefined');
});
});
var FSTXL = [
[paths.fstxlsx, true],
[paths.fstxlsb, true],
[paths.fstxls, false]
];
it('should not generate deps by default', function() {
FSTPaths.forEach(function(p) {
var wb = X.read(fs.readFileSync(p), {type:TYPE});
assert.ok(typeof wb.Deps === 'undefined' || !(wb.Deps && wb.Deps.length>0));
});
});
it('bookDeps should generate deps (XLSX/XLSB)', function() {
FSTXL.forEach(function(p) {
if(!p[1]) return;
var wb = X.read(fs.readFileSync(p[0]), {type:TYPE, bookDeps:true});
assert.ok(typeof wb.Deps !== 'undefined' && wb.Deps.length > 0);
});
});
var ckf = function(wb, fields, exists) { fields.forEach(function(f) { assert.ok((typeof wb[f] !== 'undefined') == exists); }); };
it('should not generate book files by default', function() {FSTXL.forEach(function(r) {
var wb = X.read(fs.readFileSync(r[0]), {type:TYPE});
ckf(wb, r[1] ? ['files', 'keys'] : ['cfb'], false);
}); });
it('bookFiles should generate book files', function() {FSTXL.forEach(function(r) {
var wb = X.read(fs.readFileSync(r[0]), {type:TYPE, bookFiles:true});
ckf(wb, r[1] ? ['files', 'keys'] : ['cfb'], true);
}); });
var NFVBA = ["nfxlsx", "nfxlsb", "nfxls"].map(function(n) { return paths[n]; });
it('should not generate VBA by default', function() { NFPaths.forEach(function(p) {
var wb = X.read(fs.readFileSync(p), {type:TYPE}); assert.ok(typeof wb.vbaraw === 'undefined');
}); });
it('bookVBA should generate vbaraw', function() { NFVBA.forEach(function(p) {
var wb = X.read(fs.readFileSync(p),{type: TYPE, bookVBA: true});
assert.ok(wb.vbaraw);
var cfb = X.CFB.read(wb.vbaraw, {type: 'array'});
assert.ok(X.CFB.find(cfb, '/VBA/ThisWorkbook'));
}); });
});
});
describe('input formats', function() {
it('should read binary strings', function() { artifax.forEach(function(p) {
X.read(fs.readFileSync(p, 'binary'), {type: 'binary'});
}); });
it('should read base64 strings', function() { artifax.forEach(function(p) {
X.read(fs.readFileSync(p, 'base64'), {type: 'base64'});
}); });
if(typeof Uint8Array !== 'undefined') it('should read array', function() { artifax.forEach(function(p) {
X.read(fs.readFileSync(p, 'binary').split("").map(function(x) { return x.charCodeAt(0); }), {type:'array'});
}); });
((browser || typeof Buffer === 'undefined') ? it.skip : it)('should read Buffers', function() { artifax.forEach(function(p) {
X.read(fs.readFileSync(p), {type: 'buffer'});
}); });
if(typeof Uint8Array !== 'undefined') it('should read ArrayBuffer / Uint8Array', function() { artifax.forEach(function(p) {
var payload = fs.readFileSync(p, browser ? 'buffer' : null);
var ab = new ArrayBuffer(payload.length), vu = new Uint8Array(ab);
for(var i = 0; i < payload.length; ++i) vu[i] = payload[i];
X.read(ab, {type: 'array'});
X.read(vu, {type: 'array'});
}); });
it('should throw if format is unknown', function() { artifax.forEach(function(p) {
assert.throws(function() { X.read(fs.readFileSync(p), {type: 'dafuq'}); });
}); });
var T = browser ? 'base64' : 'buffer';
it('should default to "' + T + '" type', function() { artifax.forEach(function(p) {
X.read(fs.readFileSync.apply(fs, browser ? [p, 'base64'] : [p]));
}); });
if(!browser) it('should read files', function() { artifax.forEach(function(p) { X.readFile(p); }); });
});
describe('output formats', function() {
var fmts = [
/* fmt unicode str */
["xlsx", true, false],
["xlsb", true, false],
["xls", true, false],
["xlml", true, true],
["ods", true, false],
["fods", true, true],
["csv", true, true],
["txt", true, true],
["rtf", false, true],
["sylk", false, true],
["eth", false, true],
["html", true, true],
["dif", false, true],
["dbf", false, false],
["prn", false, true]
];
function RT(T) {
fmts.forEach(function(fmt) {
var wb = X.utils.book_new();
X.utils.book_append_sheet(wb, X.utils.aoa_to_sheet([['R',"\u2603"],["\u0BEE",2]]), "Sheet1");
if(T == 'string' && !fmt[2]) return assert.throws(function() {X.write(wb, {type: T, bookType:fmt[0], WTF:true});});
var out = X.write(wb, {type: T, bookType:fmt[0], WTF:true});
var nwb = X.read(out, {type: T, PRN: fmt[0] == 'prn', WTF:true});
var nws = nwb.Sheets[nwb.SheetNames[0]];
assert.equal(get_cell(nws, "B2").v, 2);
assert.equal(get_cell(nws, "A1").v, "R");
if(fmt[1]) assert.equal(get_cell(nws, "A2").v, "\u0BEE");
if(fmt[1]) assert.equal(get_cell(nws, "B1").v, "\u2603");
});
}
it('should write binary strings', function() { RT('binary'); });
it('should write base64 strings', function() { RT('base64'); });
it('should write JS strings', function() { RT('string'); });
if(typeof ArrayBuffer !== 'undefined' && (typeof process == 'undefined' || !process.version.match(/v0.12/))) it('should write array buffers', function() { RT('array'); });
if(!browser) it('should write buffers', function() { RT('buffer'); });
it('should throw if format is unknown', function() { assert.throws(function() { RT('dafuq'); }); });
});
function eqarr(a,b) {
assert.equal(a.length, b.length);
a.forEach(function(x, i) { assert.equal(x, b[i]); });
}
describe('API', function() {
it('book_append_sheet', function() {
var wb = X.utils.book_new();
X.utils.book_append_sheet(wb, X.utils.aoa_to_sheet([[1,2,3],[4],[5]]), "A");
X.utils.book_append_sheet(wb, X.utils.aoa_to_sheet([[1,2,3],[4],[5]]));
X.utils.book_append_sheet(wb, X.utils.aoa_to_sheet([[1,2,3],[4],[5]]));
X.utils.book_append_sheet(wb, X.utils.aoa_to_sheet([[1,2,3],[4],[5]]), "B");
X.utils.book_append_sheet(wb, X.utils.aoa_to_sheet([[1,2,3],[4],[5]]));
eqarr(wb.SheetNames, ["A","Sheet1","Sheet2","B","Sheet3"]);
});
it('sheet_add_json', function() {
var ws = X.utils.json_to_sheet([{A:"S", B:"h", C:"e", D:"e", E:"t", F:"J", G:"S"}], {header:["A","B","C","D","E","F","G"], skipHeader:true});
X.utils.sheet_add_json(ws, [{A:1, B:2}, {A:2, B:3}, {A:3, B:4}], {skipHeader:true, origin:"A2"});
X.utils.sheet_add_json(ws, [{A:5, B:6, C:7}, {A:6, B:7, C:8}, {A:7, B:8, C:9}], {skipHeader:true, origin:{r:1, c:4}, header:["A","B","C"]});
X.utils.sheet_add_json(ws, [{A:4, B:5, C:6, D:7, E:8, F:9, G:0}], {header:["A","B","C","D","E","F","G"], skipHeader:true, origin:-1});
assert.equal(X.utils.sheet_to_csv(ws).trim(), "S,h,e,e,t,J,S\n1,2,,,5,6,7\n2,3,,,6,7,8\n3,4,,,7,8,9\n4,5,6,7,8,9,0");
});
it('sheet_add_aoa', function() {
var ws = X.utils.aoa_to_sheet([ "SheetJS".split("") ]);
X.utils.sheet_add_aoa(ws, [[1,2], [2,3], [3,4]], {origin: "A2"});
X.utils.sheet_add_aoa(ws, [[5,6,7], [6,7,8], [7,8,9]], {origin:{r:1, c:4}});
X.utils.sheet_add_aoa(ws, [[4,5,6,7,8,9,0]], {origin: -1});
assert.equal(X.utils.sheet_to_csv(ws).trim(), "S,h,e,e,t,J,S\n1,2,,,5,6,7\n2,3,,,6,7,8\n3,4,,,7,8,9\n4,5,6,7,8,9,0");
});
it('sheet_add_aoa support object cell', function() {
var data = X.utils.aoa_to_sheet([
['url', 'name', 'id'],
[ { l: { Target: 'https://123.com' }, v: 'url', t: 's' }, 'tom', 'xxx' ]
]);
if(assert.deepEqual) assert.deepEqual(data.A2, { l: { Target: 'https://123.com' }, v: 'url', t: 's' });
});
it('decode_range', function() {
var _c = "ABC", _r = "123", _C = "DEF", _R = "456";
var r = X.utils.decode_range(_c + _r + ":" + _C + _R);
assert.ok(r.s != r.e);
assert.equal(r.s.c, X.utils.decode_col(_c)); assert.equal(r.s.r, X.utils.decode_row(_r));
assert.equal(r.e.c, X.utils.decode_col(_C)); assert.equal(r.e.r, X.utils.decode_row(_R));
r = X.utils.decode_range(_c + _r);
assert.ok(r.s != r.e);
assert.equal(r.s.c, X.utils.decode_col(_c)); assert.equal(r.s.r, X.utils.decode_row(_r));
assert.equal(r.e.c, X.utils.decode_col(_c)); assert.equal(r.e.r, X.utils.decode_row(_r));
});
});
function coreprop(props) {
assert.equal(props.Title, 'Example with properties');
assert.equal(props.Subject, 'Test it before you code it');
assert.equal(props.Author, 'Pony Foo');
assert.equal(props.Manager, 'Despicable Drew');
assert.equal(props.Company, 'Vector Inc');
assert.equal(props.Category, 'Quirky');
assert.equal(props.Keywords, 'example humor');
assert.equal(props.Comments, 'some comments');
assert.equal(props.LastAuthor, 'Hugues');
}
function custprop(props) {
assert.equal(props['I am a boolean'], true);
assert.equal(props['Date completed'].toISOString(), '1967-03-09T16:30:00.000Z');
assert.equal(props.Status, 2);
assert.equal(props.Counter, -3.14);
}
function cmparr(x){ for(var i=1;i<x.length;++i) assert.deepEqual(x[0], x[i]); }
function deepcmp(x,y,k,m,c) {
var s = k.indexOf(".");
m = (m||"") + "|" + (s > -1 ? k.substr(0,s) : k);
if(s < 0) return assert[c<0?'notEqual':'equal'](x[k], y[k], m);
return deepcmp(x[k.substr(0,s)],y[k.substr(0,s)],k.substr(s+1),m,c);
}
var styexc = [
'A2|H10|bgColor.rgb',
'F6|H1|patternType'
];
var stykeys = [
"patternType",
"fgColor.rgb",
"bgColor.rgb"
];
function diffsty(ws, r1,r2) {
var c1 = get_cell(ws,r1).s, c2 = get_cell(ws,r2).s;
stykeys.forEach(function(m) {
var c = -1;
if(styexc.indexOf(r1+"|"+r2+"|"+m) > -1) c = 1;
else if(styexc.indexOf(r2+"|"+r1+"|"+m) > -1) c = 1;
deepcmp(c1,c2,m,r1+","+r2,c);
});
}
function hlink1(ws) {[
["A1", "http://www.sheetjs.com"],
["A2", "http://oss.sheetjs.com"],
["A3", "http://oss.sheetjs.com#foo"],
["A4", "mailto:dev@sheetjs.com"],
["A5", "mailto:dev@sheetjs.com?subject=hyperlink"],
["A6", "../../sheetjs/Documents/Test.xlsx"],
["A7", "http://sheetjs.com", "foo bar baz"]
].forEach(function(r) {
assert.equal(get_cell(ws, r[0]).l.Target, r[1]);
if(r[2]) assert.equal(get_cell(ws, r[0]).l.Tooltip, r[2]);
}); }
function hlink2(ws) { [
["A1", "#Sheet2!A1"],
["A2", "#WBScope"],
["A3", "#Sheet1!WSScope1", "#Sheet1!C7:E8"],
["A5", "#Sheet1!A5"]
].forEach(function(r) {
if(r[2] && get_cell(ws, r[0]).l.Target == r[2]) return;
assert.equal(get_cell(ws, r[0]).l.Target, r[1]);
}); }
function check_margin(margins, exp) {
["left", "right", "top", "bottom", "header", "footer"].forEach(function(m,i) {
assert.equal(margins[m], exp[i]);
});
}
describe('parse features', function() {
describe('sheet visibility', function() {
var wbs = [];
var bef = (function() {
wbs = SVPaths.map(function(p) { return X.read(fs.readFileSync(p), {type:TYPE}); });
});
if(typeof before != 'undefined') before(bef);
else it('before', bef);
it('should detect visible sheets', function() {
if(!wbs.length) bef();
wbs.forEach(function(wb) {
assert.ok(!wb.Workbook.Sheets[0].Hidden);
});
});
it('should detect all hidden sheets', function() {
wbs.forEach(function(wb) {
assert.ok(wb.Workbook.Sheets[1].Hidden);
assert.ok(wb.Workbook.Sheets[2].Hidden);
});
});
it('should distinguish very hidden sheets', function() {
wbs.forEach(function(wb) {
assert.equal(wb.Workbook.Sheets[1].Hidden,1);
assert.equal(wb.Workbook.Sheets[2].Hidden,2);
});
});
});
describe('comments', function() {
if(fs.existsSync(paths.swcxlsx)) it('should have comment as part of cell properties', function(){
var sheet = 'Sheet1';
var wb1=X.read(fs.readFileSync(paths.swcxlsx), {type:TYPE});
var wb2=X.read(fs.readFileSync(paths.swcxlsb), {type:TYPE});
var wb3=X.read(fs.readFileSync(paths.swcxls), {type:TYPE});
var wb4=X.read(fs.readFileSync(paths.swcxml), {type:TYPE});
[wb1,wb2,wb3,wb4].map(function(wb) { return wb.Sheets[sheet]; }).forEach(function(ws, i) {
assert.equal(get_cell(ws, "B1").c.length, 1,"must have 1 comment");
assert.equal(get_cell(ws, "B1").c[0].a, "Yegor Kozlov","must have the same author");
assert.equal(get_cell(ws, "B1").c[0].t, "Yegor Kozlov:\nfirst cell", "must have the concatenated texts");
if(i > 0) return;
assert.equal(get_cell(ws, "B1").c[0].r, '<r><rPr><b/><sz val="8"/><color indexed="81"/><rFont val="Tahoma"/></rPr><t>Yegor Kozlov:</t></r><r><rPr><sz val="8"/><color indexed="81"/><rFont val="Tahoma"/></rPr><t xml:space="preserve">\r\nfirst cell</t></r>', "must have the rich text representation");
assert.equal(get_cell(ws, "B1").c[0].h, '<span style="font-size:8pt;"><b>Yegor Kozlov:</b></span><span style="font-size:8pt;"><br/>first cell</span>', "must have the html representation");
});
});
[
['xlsx', paths.cstxlsx],
['xlsb', paths.cstxlsb],
['xls', paths.cstxls],
['xlml', paths.cstxml],
['ods', paths.cstods]
].forEach(function(m) { it(m[0] + ' stress test', function() {
var wb = X.read(fs.readFileSync(m[1]), {type:TYPE});
check_comments(wb);
var ws0 = wb.Sheets["Sheet2"];
assert.equal(get_cell(ws0,"A1").c[0].a, 'Author');
assert.equal(get_cell(ws0,"A1").c[0].t, 'Author:\nGod thinks this is good');
assert.equal(get_cell(ws0,"C1").c[0].a, 'Author');
assert.equal(get_cell(ws0,"C1").c[0].t, 'I really hope that xlsx decides not to use magic like rPr');
}); });
});
describe('should parse core properties and custom properties', function() {
var wbs=[];
var bef = (function() {
wbs = [
X.read(fs.readFileSync(paths.cpxlsx), {type:TYPE, WTF:true}),
X.read(fs.readFileSync(paths.cpxlsb), {type:TYPE, WTF:true}),
X.read(fs.readFileSync(paths.cpxls), {type:TYPE, WTF:true}),
X.read(fs.readFileSync(paths.cpxml), {type:TYPE, WTF:true})
];
});
if(typeof before != 'undefined') before(bef);
else it('before', bef);
['XLSX', 'XLSB', 'XLS', 'XML'].forEach(function(x, i) {
it(x + ' should parse core properties', function() { coreprop(wbs[i].Props); });
it(x + ' should parse custom properties', function() { custprop(wbs[i].Custprops); });
});
[
["asxls", "BIFF8", "\u2603"],
["asxls5", "BIFF5", "_"],
["asxml", "XLML", "\u2603"],
["asods", "ODS", "God"],
["asxlsx", "XLSX", "\u2603"],
["asxlsb", "XLSB", "\u2603"]
].forEach(function(x) {
(fs.existsSync(paths[x[0]]) ? it : it.skip)(x[1] + ' should read ' + (x[2] == "\u2603" ? 'unicode ' : "") + 'author', function() {
var wb = X.read(fs.readFileSync(paths[x[0]]), {type:TYPE});
assert.equal(wb.Props.Author, x[2]);
}); });
var BASE = "இராமா";
/* TODO: ODS, XLS */
[ "xlsx", "xlsb", "xlml"/*, "ods", "xls" */].forEach(function(n) {
it(n + ' should round-trip unicode category', function() {
var wb = X.utils.book_new();
X.utils.book_append_sheet(wb, X.utils.aoa_to_sheet([["a"]]), "Sheet1");
if(!wb.Props) wb.Props = {};
wb.Props.Category = BASE;
var wb2 = X.read(X.write(wb, {bookType:n, type:TYPE}), {type:TYPE});
assert.equal(wb2.Props.Category,BASE);
}); });
});
describe('sheetRows', function() {
it('should use original range if not set', function() {
var opts = {type:TYPE};
FSTPaths.map(function(p) { return X.read(fs.readFileSync(p), opts); }).forEach(function(wb) {
assert.equal(wb.Sheets["Text"]["!ref"],"A1:F49");
});
});
it('should adjust range if set', function() {
var opts = {type:TYPE, sheetRows:10};
var wbs = FSTPaths.map(function(p) { return X.read(fs.readFileSync(p), opts); });
/* TODO: XLS, XML, ODS */
wbs.slice(0,2).forEach(function(wb) {
assert.equal(wb.Sheets["Text"]["!fullref"],"A1:F49");
assert.equal(wb.Sheets["Text"]["!ref"],"A1:F10");
});
});
it('should not generate comment cells', function() {
var opts = {type:TYPE, sheetRows:10};
var wbs = CSTPaths.map(function(p) { return X.read(fs.readFileSync(p), opts); });
/* TODO: XLS, XML, ODS */
wbs.slice(0,2).forEach(function(wb) {
assert.equal(wb.Sheets["Sheet7"]["!fullref"],"A1:N34");
assert.equal(wb.Sheets["Sheet7"]["!ref"],"A1");
});
});
});
describe('column properties', function() {
var wbs = [], wbs_no_slk = [];
var bef = (function() {
wbs = CWPaths.map(function(n) { return X.read(fs.readFileSync(n), {type:TYPE, cellStyles:true}); });
wbs_no_slk = wbs.slice(0, 5);
});
if(typeof before != 'undefined') before(bef);
else it('before', bef);
it('should have "!cols"', function() {
wbs.forEach(function(wb) { assert.ok(wb.Sheets["Sheet1"]['!cols']); });
});
it('should have correct widths', function() {
/* SYLK rounds wch so skip non-integral */
wbs_no_slk.map(function(x) { return x.Sheets["Sheet1"]['!cols']; }).forEach(function(x) {
assert.equal(x[1].width, 0.1640625);
assert.equal(x[2].width, 16.6640625);
assert.equal(x[3].width, 1.6640625);
});
wbs.map(function(x) { return x.Sheets["Sheet1"]['!cols']; }).forEach(function(x) {
assert.equal(x[4].width, 4.83203125);
assert.equal(x[5].width, 8.83203125);
assert.equal(x[6].width, 12.83203125);
assert.equal(x[7].width, 16.83203125);
});
});
it('should have correct pixels', function() {
/* SYLK rounds wch so skip non-integral */
wbs_no_slk.map(function(x) { return x.Sheets["Sheet1"]['!cols']; }).forEach(function(x) {
assert.equal(x[1].wpx, 1);
assert.equal(x[2].wpx, 100);
assert.equal(x[3].wpx, 10);
});
wbs.map(function(x) { return x.Sheets["Sheet1"]['!cols']; }).forEach(function(x) {
assert.equal(x[4].wpx, 29);
assert.equal(x[5].wpx, 53);
assert.equal(x[6].wpx, 77);
assert.equal(x[7].wpx, 101);
});
});
});
describe('row properties', function() {
var wbs = [], ols = [];
var ol = fs.existsSync(paths.olxls);
var bef = (function() {
wbs = RHPaths.map(function(p) { return X.read(fs.readFileSync(p), {type:TYPE, cellStyles:true}); });
/* */
if(!ol) return;
ols = OLPaths.map(function(p) { return X.read(fs.readFileSync(p), {type:TYPE, cellStyles:true}); });
});
if(typeof before != 'undefined') before(bef);
else it('before', bef);
it('should have "!rows"', function() {
wbs.forEach(function(wb) { assert.ok(wb.Sheets["Sheet1"]['!rows']); });
});
it('should have correct points', function() {
wbs.map(function(x) { return x.Sheets["Sheet1"]['!rows']; }).forEach(function(x) {
assert.equal(x[1].hpt, 1);
assert.equal(x[2].hpt, 10);
assert.equal(x[3].hpt, 100);
});
});
it('should have correct pixels', function() {
wbs.map(function(x) { return x.Sheets["Sheet1"]['!rows']; }).forEach(function(x) {
/* note: at 96 PPI hpt == hpx */
assert.equal(x[1].hpx, 1);
assert.equal(x[2].hpx, 10);
assert.equal(x[3].hpx, 100);
});
});
it('should have correct outline levels', function() {
ols.map(function(x) { return x.Sheets["Sheet1"]; }).forEach(function(ws) {
var rows = ws['!rows'];
for(var i = 0; i < 29; ++i) {
var cell = get_cell(ws, "A" + X.utils.encode_row(i));
var lvl = (rows[i]||{}).level||0;
if(!cell || cell.t == 's') assert.equal(lvl, 0);
else if(cell.t == 'n') {
if(cell.v === 0) assert.equal(lvl, 0);
else assert.equal(lvl, cell.v);
}
}
assert.equal(rows[29].level, 7);
});
});
});
describe('merge cells',function() {
var wbs=[];
var bef = (function() {
wbs = MCPaths.map(function(p) { return X.read(fs.readFileSync(p), {type:TYPE}); });
});
if(typeof before != 'undefined') before(bef);
else it('before', bef);
it('should have !merges', function() {
wbs.forEach(function(wb) {
assert.ok(wb.Sheets["Merge"]['!merges']);
});
var m = wbs.map(function(x) { return x.Sheets["Merge"]['!merges'].map(function(y) { return X.utils.encode_range(y); });});
m.slice(1).forEach(function(x) {
assert.deepEqual(m[0].sort(),x.sort());
});
});
});
describe('should find hyperlinks', function() {
var wb1, wb2;
var bef = (function() {
wb1 = HLPaths.map(function(p) { return X.read(fs.readFileSync(p), {type:TYPE, WTF:true}); });
wb2 = ILPaths.map(function(p) { return X.read(fs.readFileSync(p), {type:TYPE, WTF:true}); });
});
if(typeof before != 'undefined') before(bef);
else it('before', bef);
['xlsx', 'xlsb', 'xls', 'xml'].forEach(function(x, i) {
it(x + " external", function() { hlink1(wb1[i].Sheets["Sheet1"]); });
});
['xlsx', 'xlsb', 'xls', 'xml', 'ods'].forEach(function(x, i) {
it(x + " internal", function() { hlink2(wb2[i].Sheets["Sheet1"]); });
});
});
describe('should parse cells with date type (XLSX/XLSM)', function() {
it('Must have read the date', function() {
var wb, ws;
var sheetName = 'Sheet1';
wb = X.read(fs.readFileSync(paths.dtxlsx), {type:TYPE});
ws = wb.Sheets[sheetName];
var sheet = X.utils.sheet_to_json(ws, {raw: false});
assert.equal(sheet[3]['てすと'], '2/14/14');
});
it('cellDates should not affect formatted text', function() {
var sheetName = 'Sheet1';
var ws1 = X.read(fs.readFileSync(paths.dtxlsx), {type:TYPE}).Sheets[sheetName];
var ws2 = X.read(fs.readFileSync(paths.dtxlsb), {type:TYPE}).Sheets[sheetName];
assert.equal(X.utils.sheet_to_csv(ws1),X.utils.sheet_to_csv(ws2));
});
});
describe('cellDates', function() {
var fmts = [
/* desc path sheet cell formatted */
['XLSX', paths.dtxlsx, 'Sheet1', 'B5', '2/14/14'],
['XLSB', paths.dtxlsb, 'Sheet1', 'B5', '2/14/14'],
['XLS', paths.dtxls, 'Sheet1', 'B5', '2/14/14'],
['XLML', paths.dtxml, 'Sheet1', 'B5', '2/14/14'],
['XLSM', paths.nfxlsx, 'Implied', 'B13', '18-Oct-33']
];
it('should not generate date cells by default', function() { fmts.forEach(function(f) {
var wb, ws;
wb = X.read(fs.readFileSync(f[1]), {type:TYPE});
ws = wb.Sheets[f[2]];
assert.equal(get_cell(ws, f[3]).w, f[4]);
assert.equal(get_cell(ws, f[3]).t, 'n');
}); });
it('should generate date cells if cellDates is true', function() { fmts.forEach(function(f) {
var wb, ws;
wb = X.read(fs.readFileSync(f[1]), {type:TYPE, cellDates:true});
ws = wb.Sheets[f[2]];
assert.equal(get_cell(ws, f[3]).w, f[4]);
assert.equal(get_cell(ws, f[3]).t, 'd');
}); });
});
describe('defined names', function() {[
/* desc path cmnt */
['xlsx', paths.dnsxlsx, true],
['xlsb', paths.dnsxlsb, true],
['xls', paths.dnsxls, true],
['xlml', paths.dnsxml, false],
['slk', paths.dnsslk, false]
].forEach(function(m) { it(m[0], function() {
var wb = X.read(fs.readFileSync(m[1]), {type:TYPE});
var names = wb.Workbook.Names;
if(m[0] != 'slk') {
for(var i = 0; i < names.length; ++i) if(names[i].Name == "SheetJS") break;
assert.ok(i < names.length, "Missing name");
assert.equal(names[i].Sheet, null);
assert.equal(names[i].Ref, "Sheet1!$A$1");
if(m[2]) assert.equal(names[i].Comment, "defined names just suck excel formulae are bad MS should feel bad");
}
for(i = 0; i < names.length; ++i) if(names[i].Name == "SHEETjs") break;
assert.ok(i < names.length, "Missing name");
assert.equal(names[i].Sheet, 0);
assert.equal(names[i].Ref, "Sheet1!$A$2");
}); }); });
describe('defined names unicode', function() {[
/* desc path RT */
['xlsx', paths.dnuxlsx, true],
['xlsb', paths.dnuxlsb, true],
['ods', paths.dnuods, true],
['xls', paths.dnuxls, false],
['xlml', paths.dnuxml, false]
].forEach(function(m) { it(m[0], function() {
var wb = X.read(fs.readFileSync(m[1]), {type:TYPE});
var wb2 = X.read(X.write(wb, {type:TYPE, bookType: m[0]}), {type:TYPE});
[
"NoContainsJapanese",
"\u65E5\u672C\u8a9e\u306e\u307f",
"sheet\u65e5\u672c\u8a9e",
"\u65e5\u672c\u8a9esheet",
"sheet\u65e5\u672c\u8a9esheet"
].forEach(function(n, i) { assert.equal(wb.SheetNames[i], n); });
[
["name\u65e5\u672c\u8a9e", "sheet\u65e5\u672c\u8a9e!$A$1"],
["name\u65e5\u672c\u8a9ename", "sheet\u65e5\u672c\u8a9esheet!$B$2"],
["NoContainsJapaneseName", "\u65e5\u672c\u8a9e\u306e\u307f!$A$1"],
["sheet\u65e5\u672c\u8a9e", "sheet\u65e5\u672c\u8a9e!$A$1"],
["\u65e5\u672c\u8a9e", "NoContainsJapanese!$A$1"],
["\u65e5\u672c\u8a9ename", "\u65e5\u672c\u8a9esheet!$I$2"]
].forEach(function(n) {(m[2] ? [ wb, wb2 ] : [ wb ]).forEach(function(wb) {
var DN = null;
var arr = wb.Workbook.Names;
for(var j = 0; j < arr.length; ++j) if(arr[j].Name == n[0]) DN = arr[j];
assert.ok(DN);
// $FlowIgnore
assert.equal(DN.Ref, n[1]);
}); });
}); }); });
describe('workbook codename unicode', function() {
var ws, wb;
var bef = (function() {
wb = X.utils.book_new();
ws = X.utils.aoa_to_sheet([[1]]);
X.utils.book_append_sheet(wb, ws, "Sheet1");
wb.Workbook = { WBProps: { CodeName: "本工作簿" } };
});
if(typeof before != 'undefined') before(bef);
else it('before', bef);
['xlsx', 'xlsb'].forEach(function(m) { it(m, function() {
var bstr = X.write(wb, {type: "binary", bookType: m});
var nwb = X.read(bstr, {type: "binary"});
assert.equal(nwb.Workbook.WBProps.CodeName, wb.Workbook.WBProps.CodeName);
}); });
});
describe('auto filter', function() {[
['xlsx', paths.afxlsx],
['xlsb', paths.afxlsb],
['xls', paths.afxls],
['xlml', paths.afxml],
['ods', paths.afods]
].forEach(function(m) { it(m[0], function() {
var wb = X.read(fs.readFileSync(m[1]), {type:TYPE});
assert.ok(!wb.Sheets[wb.SheetNames[0]]['!autofilter']);
for(var i = 1; i < wb.SheetNames.length; ++i) {
assert.ok(wb.Sheets[wb.SheetNames[i]]['!autofilter']);
assert.equal(wb.Sheets[wb.SheetNames[i]]['!autofilter'].ref,"A1:E22");
}
}); }); });
describe('HTML', function() {
var ws, wb;
var bef = (function() {
ws = X.utils.aoa_to_sheet([
["a","b","c"],
["&","<",">","\n"]
]);
wb = {SheetNames:["Sheet1"],Sheets:{Sheet1:ws}};
});
if(typeof before != 'undefined') before(bef);
else it('before', bef);
['xlsx'].forEach(function(m) { it(m, function() {
var wb2 = X.read(X.write(wb, {bookType:m, type:TYPE}),{type:TYPE, cellHTML:true});
assert.equal(get_cell(wb2.Sheets["Sheet1"], "A2").h, "&amp;");
assert.equal(get_cell(wb2.Sheets["Sheet1"], "B2").h, "&lt;");
assert.equal(get_cell(wb2.Sheets["Sheet1"], "C2").h, "&gt;");
var h = get_cell(wb2.Sheets["Sheet1"], "D2").h;
assert.ok(h == "&#x000a;" || h == "<br/>");
}); });
});
describe('page margins', function() {
var wbs=[];
var bef = (function() {
if(!fs.existsSync(paths.pmxls)) return;
wbs = PMPaths.map(function(p) { return X.read(fs.readFileSync(p), {type:TYPE, WTF:true}); });
});
if(typeof before != 'undefined') before(bef);
else it('before', bef);
[
/* Sheet Name Margins: left right top bottom head foot */
["Normal", [0.70, 0.70, 0.75, 0.75, 0.30, 0.30]],
["Wide", [1.00, 1.00, 1.00, 1.00, 0.50, 0.50]],
["Narrow", [0.25, 0.25, 0.75, 0.75, 0.30, 0.30]],
["Custom 1 Inch Centered", [1.00, 1.00, 1.00, 1.00, 0.30, 0.30]],
["1 Inch HF", [0.70, 0.70, 0.75, 0.75, 1.00, 1.00]]
].forEach(function(t) { it('should parse ' + t[0] + ' margin', function() { wbs.forEach(function(wb) {
check_margin(wb.Sheets[t[0]]["!margins"], t[1]);
}); }); });
});
describe('should correctly handle styles', function() {
var wsxls, wsxlsx, rn, rn2;
var bef = (function() {
wsxls=X.read(fs.readFileSync(paths.cssxls), {type:TYPE,cellStyles:true,WTF:true}).Sheets["Sheet1"];
wsxlsx=X.read(fs.readFileSync(paths.cssxlsx), {type:TYPE,cellStyles:true,WTF:true}).Sheets["Sheet1"];
rn = function(range) {
var r = X.utils.decode_range(range);
var out = [];
for(var R = r.s.r; R <= r.e.r; ++R) for(var C = r.s.c; C <= r.e.c; ++C)
out.push(X.utils.encode_cell({c:C,r:R}));
return out;
};
rn2 = function(r) { return [].concat.apply([], r.split(",").map(rn)); };
});
if(typeof before != 'undefined') before(bef);
else it('before', bef);
var ranges = [
'A1:D1,F1:G1', 'A2:D2,F2:G2', /* rows */
'A3:A10', 'B3:B10', 'E1:E10', 'F6:F8', /* cols */
'H1:J4', 'H10' /* blocks */
];
/*eslint-disable */
var exp/*:Array<any>*/ = [
{ patternType: 'darkHorizontal',
fgColor: { theme: 9, raw_rgb: 'F79646' },
bgColor: { theme: 5, raw_rgb: 'C0504D' } },
{ patternType: 'darkUp',
fgColor: { theme: 3, raw_rgb: '1F497D' },
bgColor: { theme: 7, raw_rgb: '8064A2' } },
{ patternType: 'darkGray',
fgColor: { theme: 3, raw_rgb: '1F497D' },
bgColor: { theme: 1, raw_rgb: '000000' } },
{ patternType: 'lightGray',
fgColor: { theme: 6, raw_rgb: '9BBB59' },
bgColor: { theme: 2, raw_rgb: 'EEECE1' } },
{ patternType: 'lightDown',
fgColor: { theme: 4, raw_rgb: '4F81BD' },
bgColor: { theme: 7, raw_rgb: '8064A2' } },
{ patternType: 'lightGrid',
fgColor: { theme: 6, raw_rgb: '9BBB59' },
bgColor: { theme: 9, raw_rgb: 'F79646' } },
{ patternType: 'lightGrid',
fgColor: { theme: 4, raw_rgb: '4F81BD' },
bgColor: { theme: 2, raw_rgb: 'EEECE1' } },
{ patternType: 'lightVertical',
fgColor: { theme: 3, raw_rgb: '1F497D' },
bgColor: { theme: 7, raw_rgb: '8064A2' } }
];
/*eslint-enable */
ranges.forEach(function(rng) {
it('XLS | ' + rng,function(){cmparr(rn2(rng).map(function(x){ return get_cell(wsxls,x).s; }));});
it('XLSX | ' + rng,function(){cmparr(rn2(rng).map(function(x){ return get_cell(wsxlsx,x).s; }));});
});
it('different styles', function() {
for(var i = 0; i != ranges.length-1; ++i) {
for(var j = i+1; j != ranges.length; ++j) {
diffsty(wsxlsx, rn2(ranges[i])[0], rn2(ranges[j])[0]);
/* TODO: XLS */
//diffsty(wsxls, rn2(ranges[i])[0], rn2(ranges[j])[0]);
}
}
});
it('correct styles', function() {
//var stylesxls = ranges.map(function(r) { return rn2(r)[0]; }).map(function(r) { return get_cell(wsxls,r).s; });
var stylesxlsx = ranges.map(function(r) { return rn2(r)[0]; }).map(function(r) { return get_cell(wsxlsx,r).s; });
exp.forEach(function(e, i) {
[
"fgColor.theme","fgColor.raw_rgb",
"bgColor.theme","bgColor.raw_rgb",
"patternType"
].forEach(function(k) {
deepcmp(e, stylesxlsx[i], k, i + ":" + k, 0);
/* TODO: XLS */
//deepcmp(e, stylesxls[i], k, i + ":" + k, 0);
});
});
});
});
describe('data types formats', function() {[
['xlsx', paths.dtfxlsx],
].forEach(function(m) { it(m[0], function() {
var wb = X.read(fs.readFileSync(m[1]), {type: TYPE, cellDates: true});
var ws = wb.Sheets[wb.SheetNames[0]];
var data = X.utils.sheet_to_json(ws, { header: 1, raw: true, rawNumbers: false });
assert.ok(data[0][1] instanceof Date);
assert.ok(data[1][1] instanceof Date);
assert.equal(data[2][1], '$123.00');
assert.equal(data[3][1], '98.76%');
assert.equal(data[4][1], '456.00');
assert.equal(data[5][1], '7,890');
}); }); });
it('date system', function() {[
"biff5", "ods", "slk", "xls", "xlsb", "xlsx", "xml"
].forEach(function(ext) {
// TODO: verify actual date values
var wb0 = X.read(fs.readFileSync("./test_files/1904/1900." + ext), {type: TYPE, cellNF: true});
assert.ok(!wb0.Workbook || !wb0.Workbook.WBProps || !wb0.Workbook.WBProps.date1904);
assert.equal(X.utils.sheet_to_csv(wb0.Sheets[wb0.SheetNames[0]]), [
"1,1900-01-01,1900-01-01,1900-01-01",
"11,1900-01-11,1900-01-11,1900-01-11",
"111,1900-04-20,1900-04-20,1900-04-20",
"1111,1903-01-15,1903-01-15,1903-01-15",
"11111,1930-06-02,1930-06-02,1930-06-02"
].join("\n"));
var wb4 = X.read(fs.readFileSync("./test_files/1904/1904." + ext), {type: TYPE, cellNF: true});
assert.ok(wb4.Workbook.WBProps.date1904);
assert.equal(X.utils.sheet_to_csv(wb4.Sheets[wb4.SheetNames[0]]), [
"1,1904-01-02,1904-01-02,1904-01-02",
"11,1904-01-12,1904-01-12,1904-01-12",
"111,1904-04-21,1904-04-21,1904-04-21",
"1111,1907-01-16,1907-01-16,1907-01-16",
"11111,1934-06-03,1934-06-03,1934-06-03"
].join("\n"));
}); });
it('bookType metadata', function() {
[
// TODO: keep in sync with BookType, support other formats
"xlsx"/*, "xlsm" */, "xlsb"/* xls / xla / biff# */, "xlml", "ods", "fods"/*, "csv", "txt", */, "sylk", "html", "dif", "rtf"/*, "prn", "eth"*/, "dbf", "numbers"
].forEach(function(r) {
var ws = X.utils.aoa_to_sheet([ ["a", "b", "c"], [1, 2, 3] ]);
var wb = X.utils.book_new(); X.utils.book_append_sheet(wb, ws, "Sheet1");
var data = X.write(wb, {type: TYPE, bookType: r, WTF: true, numbers:XLSX_ZAHL });
assert.equal(X.read(data, {type: TYPE, WTF: true}).bookType, r);
}); });
});
describe('write features', function() {
describe('props', function() {
describe('core', function() {
var baseprops = {
Category: "Newspaper",
ContentStatus: "Published",
Keywords: "☃",
LastAuthor: "Perry White",
LastPrinted: "1978-12-15",
RevNumber: 6969,
AppVersion: 69,
Author: "Lois Lane",
Comments: "Needs work",
Identifier: "1d",
Language: "English",
Subject: "Superman",
Title: "Man of Steel"
};
var ws;
var bef = (function() {
ws = X.utils.aoa_to_sheet([["a","b","c"],[1,2,3]]);
});
if(typeof before != 'undefined') before(bef);
else it('before', bef);
['xlml', 'xlsx', 'xlsb'].forEach(function(w) { it(w, function() {
var wb = {
Props: {},
SheetNames: ["Sheet1"],
Sheets: {Sheet1: ws}
};
Object.keys(baseprops).forEach(function(k) { wb.Props[k] = baseprops[k]; });
var wb2 = X.read(X.write(wb, {bookType:w, type:TYPE}), {type:TYPE});
Object.keys(baseprops).forEach(function(k) { assert.equal(baseprops[k], wb2.Props[k]); });
var wb3 = X.read(X.write(wb2, {bookType:w, type:TYPE, Props: {Author:"SheetJS"}}), {type:TYPE});
assert.equal("SheetJS", wb3.Props.Author);
}); });
});
});
describe('HTML', function() {
it('should use `h` value when present', function() {
var sheet = X.utils.aoa_to_sheet([["abc"]]);
get_cell(sheet, "A1").h = "<b>abc</b>";
var wb = {SheetNames:["Sheet1"], Sheets:{Sheet1:sheet}};
var str = X.write(wb, {bookType:"html", type:"binary"});
assert.ok(str.indexOf("<b>abc</b>") > 0);
});
});
describe('sheet range limits', function() { [
["biff2", "IV16384"],
["biff5", "IV16384"],
["biff8", "IV65536"],
["xlsx", "XFD1048576"],
["xlsb", "XFD1048576"]
].forEach(function(r) { it(r[0], function() {
var C = X.utils.decode_cell(r[1]);
var wopts = {bookType:r[0], type:'binary', WTF:true};
var wb = { SheetNames: ["Sheet1"], Sheets: { Sheet1: {} } };
wb.Sheets["Sheet1"]['!ref'] = "A1:" + X.utils.encode_cell({r:0, c:C.c});
X.write(wb, wopts);
wb.Sheets["Sheet1"]['!ref'] = "A" + X.utils.encode_row(C.r - 5) + ":" + X.utils.encode_cell({r:C.r, c:0});
X.write(wb, wopts);
wb.Sheets["Sheet1"]['!ref'] = "A1:" + X.utils.encode_cell({r:0, c:C.c+1});
assert.throws(function() { X.write(wb, wopts); });
wb.Sheets["Sheet1"]['!ref'] = "A" + X.utils.encode_row(C.r - 5) + ":" + X.utils.encode_cell({r:C.r+1, c:0});
assert.throws(function() { X.write(wb, wopts); });
}); }); });
it('single worksheet formats', function() {
var wb = X.utils.book_new();
X.utils.book_append_sheet(wb, X.utils.aoa_to_sheet([[1,2],[3,4]]), "Sheet1");
X.utils.book_append_sheet(wb, X.utils.aoa_to_sheet([[5,6],[7,8]]), "Sheet2");
assert.equal(X.write(wb, {type:"string", bookType:"csv", sheet:"Sheet1"}), "1,2\n3,4");
assert.equal(X.write(wb, {type:"string", bookType:"csv", sheet:"Sheet2"}), "5,6\n7,8");
assert.throws(function() { X.write(wb, {type:"string", bookType:"csv", sheet:"Sheet3"}); });
});
it('should create/update autofilter defined name on write', function() {[
"xlsx", "xlsb", /* "xls", */ "xlml" /*, "ods" */
].forEach(function(fmt) {
var wb = X.utils.book_new();
var ws = X.utils.aoa_to_sheet([["A","B","C"],[1,2,3]]);
ws["!autofilter"] = { ref: ws["!ref"] };
X.utils.book_append_sheet(wb, ws, "Sheet1");
var wb2 = X.read(X.write(wb, {bookType:fmt, type:TYPE}), {type:TYPE});
var Name = void 0;
((wb2.Workbook||{}).Names || []).forEach(function(dn) { if(dn.Name == "_xlnm._FilterDatabase" && dn.Sheet == 0) Name = dn; });
assert.ok(!!Name, "Could not find _xlnm._FilterDatabases name WR");
assert.equal(Name.Ref, "Sheet1!$A$1:$C$2");
X.utils.sheet_add_aoa(wb2.Sheets["Sheet1"], [[4,5,6]], { origin: -1 });
wb2.Sheets["Sheet1"]["!autofilter"].ref = wb2.Sheets["Sheet1"]["!ref"];
var wb3 = X.read(X.write(wb2, {bookType:fmt, type:TYPE}), {type:TYPE});
Name = void 0;
((wb3.Workbook||{}).Names || []).forEach(function(dn) { if(dn.Name == "_xlnm._FilterDatabase" && dn.Sheet == 0) Name = dn; });
assert.ok(!!Name, "Could not find _xlnm._FilterDatabases name WRWR");
assert.equal(Name.Ref, "Sheet1!$A$1:$C$3");
assert.equal(wb2.Workbook.Names.length, wb3.Workbook.Names.length);
}); });
});
function seq(end/*:number*/, start/*:?number*/)/*:Array<number>*/ {
var s = start || 0;
var o = new Array(end - s);
for(var i = 0; i != o.length; ++i) o[i] = s + i;
return o;
}
var basedate = new Date(1899, 11, 30, 0, 0, 0); // 2209161600000
function datenum(v/*:Date*/, date1904/*:?boolean*/)/*:number*/ {
var epoch = v.getTime();
if(date1904) epoch -= 1462*24*60*60*1000;
var dnthresh = basedate.getTime() + (v.getTimezoneOffset() - basedate.getTimezoneOffset()) * 60000;
return (epoch - dnthresh) / (24 * 60 * 60 * 1000);
}
var good_pd_date = new Date('2017-02-19T19:06:09.000Z');
if(isNaN(good_pd_date.getFullYear())) good_pd_date = new Date('2017-02-19T19:06:09');
if(isNaN(good_pd_date.getFullYear())) good_pd_date = new Date('2/19/17');
var good_pd = good_pd_date.getFullYear() == 2017;
function parseDate(str/*:string|Date*/)/*:Date*/ {
var d = new Date(str);
if(good_pd) return d;
if(str instanceof Date) return str;
if(good_pd_date.getFullYear() == 1917 && !isNaN(d.getFullYear())) {
var s = d.getFullYear();
if(str.indexOf("" + s) > -1) return d;
d.setFullYear(d.getFullYear() + 100); return d;
}
var n = str.match(/\d+/g)||["2017","2","19","0","0","0"];
return new Date(Date.UTC(+n[0], +n[1] - 1, +n[2], +n[3], +n[4], +n[5]));
}
var fixdate = browser ? parseDate("2014-02-19T14:30:00.000Z") : new Date("2014-02-19T14:30Z");
describe('roundtrip features', function() {
describe('should preserve core properties', function() { [
['xls', paths.cpxls],
['xlml', paths.cpxml],
['xlsx', paths.cpxlsx],
['xlsb', paths.cpxlsb]
].forEach(function(w) {
it(w[0], function() {
var wb1 = X.read(fs.readFileSync(w[1]), {type:TYPE});
coreprop(wb1.Props);
var wb2 = X.read(X.write(wb1, {bookType:w[0], type:TYPE}), {type:TYPE});
coreprop(wb2.Props);
});
}); });
describe('should preserve custom properties', function() { [
['xls', paths.cpxls],
['xlml', paths.cpxml],
['xlsx', paths.cpxlsx],
['xlsb', paths.cpxlsb]
].forEach(function(w) {
it(w[0], function() {
var wb1 = X.read(fs.readFileSync(w[1]), {type:TYPE});
custprop(wb1.Custprops);
var wb2 = X.read(X.write(wb1, {bookType:w[0], type:TYPE}), {type:TYPE});
custprop(wb2.Custprops);
});
}); });
describe('should preserve merge cells', function() {
["xlsx", "xlsb", "xlml", "ods", "biff8", "numbers"].forEach(function(f) { it(f, function() {
var wb1 = X.read(fs.readFileSync(paths.mcxlsx), {type:TYPE});
var wb2 = X.read(X.write(wb1,{bookType:f,type:'binary',numbers:XLSX_ZAHL}),{type:'binary'});
var m1 = wb1.Sheets["Merge"]['!merges'].map(X.utils.encode_range);
var m2 = wb2.Sheets["Merge"]['!merges'].map(X.utils.encode_range);
assert.equal(m1.length, m2.length);
for(var i = 0; i < m1.length; ++i) assert.ok(m1.indexOf(m2[i]) > -1);
}); });
});
describe('should preserve dates', function() {
seq(16).forEach(function(n) {
var d = (n & 1) ? 'd' : 'n', dk = d === 'd';
var c = (n & 2) ? 'd' : 'n', dj = c === 'd';
var b = (n & 4) ? 'd' : 'n', di = b === 'd';
var a = (n & 8) ? 'd' : 'n', dh = a === 'd';
var f, sheet, addr;
if(dh) { f = paths.dtxlsx; sheet = 'Sheet1'; addr = 'B5'; }
else { f = paths.nfxlsx; sheet = '2011'; addr = 'J36'; }
it('[' + a + '] -> (' + b + ') -> [' + c + '] -> (' + d + ')', function() {
var wb1 = X.read(fs.readFileSync(f), {type:TYPE, cellNF: true, cellDates: di, WTF: opts.WTF});
var _f = X.write(wb1, {type:'binary', cellDates:dj, WTF:opts.WTF});
var wb2 = X.read(_f, {type:'binary', cellDates: dk, WTF: opts.WTF});
var m = [wb1,wb2].map(function(x) { return get_cell(x.Sheets[sheet], addr); });
assert.equal(m[0].w, m[1].w);
assert.equal(m[0].t, b);
assert.equal(m[1].t, d);
if(m[0].t === 'n' && m[1].t === 'n') assert.equal(m[0].v, m[1].v);
else if(m[0].t === 'd' && m[1].t === 'd') assert.equal(m[0].v.toString(), m[1].v.toString());
else if(m[1].t === 'n') assert.ok(Math.abs(datenum(browser ? parseDate(m[0].v) : new Date(m[0].v)) - m[1].v) < 0.01);
});
});
});
describe('should preserve formulae', function() { var ff = [
['xlml', paths.fstxml],
['xlsx', paths.fstxlsx],
['ods', paths.fstods]
]; ff.forEach(function(w) { it(w[0], function() {
var wb1 = X.read(fs.readFileSync(w[1]), {type:TYPE, cellFormula:true, WTF:true});
var wb2 = X.read(X.write(wb1, {bookType:w[0], type:TYPE}), {cellFormula:true, type:TYPE, WTF:true});
wb1.SheetNames.forEach(function(n) {
assert.equal(
X.utils.sheet_to_formulae(wb1.Sheets[n]).sort().join("\n"),
X.utils.sheet_to_formulae(wb2.Sheets[n]).sort().join("\n")
);
});
}); }); });
describe('should preserve dynamic array formulae', function() { [
['xlsx', paths.m19xlsx]
].forEach(function(w) { it(w[0], function() {
var wb1 = X.read(fs.readFileSync(w[1]), {xlfn: true, type:TYPE, cellFormula:true, WTF:true});
var wb2 = X.read(X.write(wb1, {bookType:w[0], type:TYPE}), {cellFormula:true, xlfn: true, type:TYPE, WTF:true});
assert.equal(!!get_cell(wb2.Sheets["Sheet1"], "B3").D, true);
assert.equal(!!get_cell(wb2.Sheets["Sheet1"], "B13").D, true);
assert.equal(!!get_cell(wb2.Sheets["Sheet1"], "C13").D, true);
get_cell(wb2.Sheets["Sheet1"], "B3").D = false;
var wb3 = X.read(X.write(wb2, {bookType:w[0], type:TYPE}), {cellFormula:true, xlfn: true, type:TYPE, WTF:true});
assert.equal(!!get_cell(wb3.Sheets["Sheet1"], "B3").D, false);
assert.equal(!!get_cell(wb3.Sheets["Sheet1"], "B13").D, true);
assert.equal(!!get_cell(wb3.Sheets["Sheet1"], "C13").D, true);
}); }); });
describe('should preserve hyperlink', function() { [
['xlml', paths.hlxml, true],
['xls', paths.hlxls, true],
['xlsx', paths.hlxlsx, true],
['xlsb', paths.hlxlsb, true],
['xlml', paths.ilxml, false],
['xls', paths.ilxls, false],
['xlsx', paths.ilxlsx, false],
['xlsb', paths.ilxlsb, false],
['ods', paths.ilods, false]
].forEach(function(w) { it(w[0]+" "+(w[2]?"ex":"in")+ "ternal", function() {
var wb = X.read(fs.readFileSync(w[1]), {type:TYPE, WTF:opts.WTF});
var hlink = (w[2] ? hlink1 : hlink2); hlink(wb.Sheets["Sheet1"]);
wb = X.read(X.write(wb, {bookType:w[0], type:TYPE, WTF:opts.WTF}), {type:TYPE, WTF:opts.WTF});
hlink(wb.Sheets["Sheet1"]);
}); }); });
describe('should preserve page margins', function() {[
['xlml', paths.pmxml],
['xlsx', paths.pmxlsx],
['xlsb', paths.pmxlsb]
].forEach(function(w) { it(w[0], function() {
var wb1 = X.read(fs.readFileSync(w[1]), {type:TYPE});
var wb2 = X.read(X.write(wb1, {bookType:w[0], type:"binary"}), {type:"binary"});
[
/* Sheet Name Margins: left right top bottom head foot */
["Normal", [0.70, 0.70, 0.75, 0.75, 0.30, 0.30]],
["Wide", [1.00, 1.00, 1.00, 1.00, 0.50, 0.50]],
["Narrow", [0.25, 0.25, 0.75, 0.75, 0.30, 0.30]],
["Custom 1 Inch Centered", [1.00, 1.00, 1.00, 1.00, 0.30, 0.30]],
["1 Inch HF", [0.70, 0.70, 0.75, 0.75, 1.00, 1.00]]
].forEach(function(t) {
check_margin(wb2.Sheets[t[0]]["!margins"], t[1]);
});
}); }); });
describe('should preserve sheet visibility', function() { [
['xlml', paths.svxml],
['xlsx', paths.svxlsx],
['xlsb', paths.svxlsb],
['xls', paths.svxls],
['biff5', paths.svxls5]
// ['ods', paths.svods]
].forEach(function(w) {
it(w[0], function() {
var wb1 = X.read(fs.readFileSync(w[1]), {type:TYPE});
var wb2 = X.read(X.write(wb1, {bookType:w[0], type:TYPE}), {type:TYPE});
var wbs1 = wb1.Workbook.Sheets;
var wbs2 = wb2.Workbook.Sheets;
assert.equal(wbs1.length, wbs2.length);
for(var i = 0; i < wbs1.length; ++i) {
assert.equal(wbs1[i].name, wbs2[i].name);
assert.equal(wbs1[i].Hidden, wbs2[i].Hidden);
}
});
});
});
describe('should preserve column properties', function() { [
/*'xlml',*/ /*'biff2', 'biff8', */ 'xlsx', 'xlsb', 'slk'
].forEach(function(w) { it(w, function() {
var ws1 = X.utils.aoa_to_sheet([["hpx12", "hpt24",