Unexpected results when there're empty cells

[JoshYEn]

Greetings Oshiro san,

First I'd like to thank you for this great js lib, it's awsome.

I encountered some unexpected results from xlsx-calc. It seem that xlsx-calc does not perform exactly the same as what excel does.

Considering a table like this:

[
  ["A"],
  [""],
  [""],
  [""],
]

and a function MIN:

B1=MIN(A2:A4)

Normally in excel, B1 will be 0 if cell A2:A4 is empty, but xlsx-calc cannot handle this. I also find another formulae that behaives weired, LOOKUP.

I created a excel sheet to illustrate this, please find the attached file.
TEST1.xlsx

I understand this is not the normal case. But can you provide any suggestions on this to fix it?

Best,
Josh

[JoshYEn]

Kindly consider these two tests:

"use strict";

const XLSX_CALC = require("../src");
const assert = require('assert');
describe('assert.equal', function () {
    let workbook;
    function create_workbook() {
        return {
            Sheets: {
                Sheet1: {
                    A1: {},
                    C3: {
                        v: "",
                        t: "n"
                    },
                    C4: {
                        v: "",
                        t: "n"
                    },
                    C5: {
                        v: "",
                        t: "n"
                    },
                }
            }
        };
    }
    beforeEach(function () {
        workbook = create_workbook();
    });

    describe('MIN', function() {
        it('finds the min in range', function() {
            workbook.Sheets.Sheet1.A1.f = 'MIN(C3:C5)';
            XLSX_CALC(workbook);
            assert.equal(workbook.Sheets.Sheet1.A1.v, 0); // "" == 0
        });
    });
});

and this one:

"use strict";

const XLSX_CALC = require("../src");
const assert = require('assert');
describe('assert. strictEqual', function () {
    let workbook;
    function create_workbook() {
        return {
            Sheets: {
                Sheet1: {
                    A1: {},
                    C3: {
                        v: "",
                        t: "n"
                    },
                    C4: {
                        v: "",
                        t: "n"
                    },
                    C5: {
                        v: "",
                        t: "n"
                    },
                }
            }
        };
    }
    beforeEach(function () {
        workbook = create_workbook();
    });

    describe('MIN', function() {
        it('finds the min in range', function() {
            workbook.Sheets.Sheet1.A1.f = 'MIN(C3:C5)';
            XLSX_CALC(workbook);
            assert.strictEqual(workbook.Sheets.Sheet1.A1.v, 0); // "" === 0
        });
    });
});