exceljs / exceljs/exceljs

[BUG] dataValidation formula returning NaN

Open
#2,181 2 comments 0 reactions 0 assignees View on GitHub
Dominant language
JavaScript
Stars
15.5k
Forks
2k
PR merge metrics
No merged PRs in 30d

Description

## 🐛 Bug Report

dataValidation formulas can be formulas themselves, but return NaN after read in.

Lib version: latest

## Steps To Reproduce

```javascript
if (cell.dataValidation.formulae.includes(Number.NaN)) {
// this shouldn't happen...it should return the raw value that Excel stores
// in Excel, if the formula1 for greater than or equal to is, e.g., =L26, but returns NaN...should return L26
}
```

## The expected behaviour:

If the Excel data validation formula is a formula, return it, not a coerced number.

## Possible solution (optional, but very helpful):

One possible solution is to return the original formula if NaN:
```javascript
case 'formula1':
case 'formula2': {
let formula = this._formula.join('');
switch (this._dataValidation.type) {
case 'whole':
case 'textLength':
formula = parseInt(formula, 10) **|| formula**;
break;
case 'decimal':
formula = parseFloat(formula) **|| formula**;
break;
case 'date':
formula = utils.excelToDate(parseFloat(formula)) **|| formula**;
break;
default:
break;
}
```

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.