Column Formulas Cause Empty Rows When Importing #2718

New Issue

2022-06-15T20:08:19Z

crazyjat commented

2022-06-15 20:08:19 +00:00

(Migrated from github.com)

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.

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.

SheetJSDev commented

2022-06-20 04:19:58 +00:00

(Migrated from github.com)

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 "" !== null

If 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 s and empty string value.

If you are able to upgrade, sheetStubs behavior could be modified to omit blank string cells without formulae (or when cellFormula is false).

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)`: <img width="414" alt="issue2718formulae" src="https://user-images.githubusercontent.com/6070939/174524135-3d35820c-b300-4b6f-a002-907219ce4546.png"> <img width="221" alt="issue2718values" src="https://user-images.githubusercontent.com/6070939/174524150-9496dfd7-b8b1-4f8e-ad22-d1fc39f2ce61.png"> (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 `"" !== null` If 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 `s` and empty string value. If you are able to upgrade, `sheetStubs` behavior could be modified to omit blank string cells without formulae (or when `cellFormula` is false).

Sign in to join this conversation.