sheet_to_csv does not ignore rows with formulas that have blank output #2274

New Issue

2021-04-12T14:31:35Z

dmongit commented

2021-04-12 14:31:35 +00:00

(Migrated from github.com)

I have this formula in a column (for all rows) in an xlsx (default value of 1 if there is data in B2, B3, B4, etc, based on current row):
=IF(B2<>"",1,"")

I have this code to extract data from an xlsx file:
const data = XLSX.utils.sheet_to_csv(ws, { header: 0, blankrows: false, defval: '' });

The console.log of data is (truncated):

Is there a setting/property that I'm missing so that blank rows can be ignored, even if the rows have formulas? Currently, I iterate through data. The browser gets bogged down because I'm iterating through 2000 rows of blank data (the formulas exists in a column for 2000 rows).

In code, I could skip the iteration if object representing B2 is blank to speed things up. But, how can I just ignore the rows that only have formulas?

Secondary question... Is there a way to use defval specific for a column? Something like
const data = XLSX.utils.sheet_to_csv(ws, { header: 0, blankrows: false }, { b: { defval: '1' } } );

I have this formula in a column (for all rows) in an xlsx (default value of 1 if there is data in B2, B3, B4, etc, based on current row): `=IF(B2<>"",1,"")` I have this code to extract data from an xlsx file: `const data = XLSX.utils.sheet_to_csv(ws, { header: 0, blankrows: false, defval: '' });` The `console.log` of `data` is (truncated): ![image](https://user-images.githubusercontent.com/28873991/114410950-c564d580-9b79-11eb-9bfa-45f53087173c.png) Is there a setting/property that I'm missing so that blank rows can be ignored, even if the rows have formulas? Currently, I iterate through `data`. The browser gets bogged down because I'm iterating through 2000 rows of blank data (the formulas exists in a column for 2000 rows). In code, I could skip the iteration if object representing B2 is blank to speed things up. But, how can I just ignore the rows that only have formulas? Secondary question... Is there a way to use `defval` specific for a column? Something like `const data = XLSX.utils.sheet_to_csv(ws, { header: 0, blankrows: false }, { b: { defval: '1' } } );`

👍 1

SheetJSDev commented

2022-01-11 18:16:44 +00:00

(Migrated from github.com)

Can you log an example cell? In specific, look at the v property. A cell like {t: "s", v:""} is "empty" but has a value that won't be ignored

Can you log an example cell? In specific, look at the `v` property. A cell like `{t: "s", v:""}` is "empty" but has a value that won't be ignored

SheetJSDev commented

2022-03-09 01:43:47 +00:00

(Migrated from github.com)

The options are blankrows: false, strip: true

The options are `blankrows: false, strip: true`

👍 1 ❤️ 1 🚀 1