Repository navigation
Column Formulas Cause Empty Rows When Importing #2718
Description
I am experiencing an issue when trying to read an xlsx file from user upload that contains column formulas that have an expression that inserts an empty string into a cell. For example, a column formula such as =IF(ISBLANK(A12),"", "Yes") will cause the imported data to have hundreds of empty rows. The imported cells look like { t: "s", v: "" } and none of the import options will filter them out.
This is what my import function looks like:
const getXlsxData = (file: File): Promise<WorkBook> => {
return new Promise(resolve => {
let reader = new FileReader();
reader.onload = (e) => {
if (e.target?.result) {
let workbook = read(e.target.result, {
cellDates: true,
cellFormula: false,
cellNF: false,
cellHTML: false,
cellText: false,
});
resolve(workbook);
}
};
reader.readAsArrayBuffer(file);
});
};
I'm on version 0.17.5. Please help.
Activity
SheetJSDev commentedon Jun 20, 2022
If you can share a file we can take a closer look, but it's likely the case that the cells are explicitly specified and explicitly have an empty string cell value.
To see this behavior, select cell A1 and select the formula bar and type
'and hit enter. Then set B1 to the formula=TYPE(A1)and set cell C1 to=LEN(A1):(TYPE 2 is "Text" and TYPE 1 is "Number" since no value was assigned to the cell)
This is a tricky case because, in all likelihood, the cell itself specifies an empty string value. It's similar to JS in that
"" !== nullIf you are unable to upgrade, the obvious option is just to delete the cells in question by walking the worksheet and deleting all cell objects with type
sand empty string value.If you are able to upgrade,
sheetStubsbehavior could be modified to omit blank string cells without formulae (or whencellFormulais false).