exceljs: [BUG] Excel shows error when reading generated file

🐛 Bug Report

Excel does not open the file generated by exceljs. When opening, it shows the error below

Error log (the meaningful part of it):

<removedRecord>Removed Records: AutoFilter from /xl/tables/table1.xml part (Table)</removedRecord>

Lib version: 4.3.0

Steps To Reproduce

Use an Excel file that has a Table and AutoFilter enabled for that table, and run the following code:

  let workBook = new ExcelJS.Workbook()
  await workBook.xlsx.readFile("./x.xlsx");
  await workBook.xlsx.writeFile("./y.xlsx");

When opening “y.xlsx” the above error occurs.

The expected behaviour:

The file is opened without any issues.

Possible solution (optional, but very helpful):

Please see the linked PR that fixes the problem.

The issue is caused by invalid combination written into excel file. Original file is on the left, saved is on the right:

The problem is library reaction on “undefined” (missing) value in the source file. It uses wrong default (“1”) instead of missing value. Another thing is it turns “0” into “1” for the header, but that does not block opening.

About this issue

Original URL
State: open
Created 2 years ago
Comments: 20

Most upvoted comments

@solegaonkar I have checked today - the fix seems to work? I have tested using NODE 18 LTS, like this 🤔

import ExcelJS from '@nbelyh/exceljs';

async function foo() {
  let workBook = new ExcelJS.Workbook()
  await workBook.xlsx.readFile("./test2.xlsx");
  await workBook.xlsx.writeFile("./test3.xlsx");
}

foo();

package.json:

  "dependencies": {
    "@nbelyh/exceljs": "^4.3.2"
  }

Are you sure you are importing ExcelJS from the modified package ‘@nbelyh/exceljs’? The original library gives an error on this file, yes.

Could you please share a full (self-sufficient) sample? In the code above there are some variables, like “i”, “idMap”, etc - not sure where to get them. I’ve tried some simple modifications like this (set a value in a cell, for example) - is also okay (below)

  let workBook = new ExcelJS.Workbook()
  await workBook.xlsx.readFile("./test2.xlsx");

  const worksheet = workBook.worksheets[0];
  const row = worksheet.getRow(1);
  row.getCell(1).value = "42";
  row.commit();

  await workBook.xlsx.writeFile("./test3.xlsx");

A code sandbox test that just does read/write (in the browser directly, pick any file): https://codesandbox.io/s/node-playground-forked-c35kd1?file=/src/index.js

nbelyh on Feb 19, 2023

Yes. It is reproducible - node v18.12.1 Additionally, If I have these lines between the read and write, the saved file cannot be recovered by Excel.

  const worksheet = workbook.getWorksheet(1);
  const row = worksheet.getRow(i);
  row.getCell(1).value = idMap.get(id);
  row.commit();

Thanks a lot for looking into this!

solegaonkar on Feb 17, 2023

@solegaonkar Thanks will check this weekend 👍 The problem is reproducible with the code above, right? I mean:

  let workBook = new ExcelJS.Workbook()
  await workBook.xlsx.readFile("./test2.xlsx");
  await workBook.xlsx.writeFile("./test3.xlsx");

nbelyh on Feb 17, 2023

@nbelyh I did it with a fresh file created with Microsoft XLSX. Here is the file for your reference test2.xlsx

solegaonkar on Feb 17, 2023