[BUG] Excel shows error when reading generated file

[nbelyh]

🐛 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.

[trylovetom]

I use your version https://github.com/exceljs/exceljs/pull/2185, but it still exists.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <logFileName>Repair Result to 0.xml</logFileName>
  <summary>Errors were detected in file '/Users/tzu-yen/Downloads/「大象班」的「批閱」成績報表 (65).xlsx'</summary>
  <removedFeatures summary="Following is a list of removed features:">
    <removedFeature>Removed Feature: AutoFilter from /xl/tables/table3.xml part (Table)</removedFeature>
    <removedFeature>Removed Feature: Table from /xl/tables/table3.xml part (Table)</removedFeature>
  </removedFeatures>
</recoveryLog>

[nbelyh]

@trylovetom could you share the file? I could quickly check. Maybe something else I'm missing 🤔 I have checked with file that has single formatted table on the first page.

[trylovetom]

OK 「大象班」的「批閱」成績報表 (65).xlsx

[nbelyh]

@trylovetom Thanks! Will check today.

[trylovetom]

@nbelyh Thanks

[nbelyh]

@trylovetom just checked, for me seems to be working? Please note that my fix does not repair the file, it just does not break it. So if the file was already "broken", it will not auto-heal. The sample file you attached seems to be already kind of "broken" by the library. If you "repair" it by Excel (open, agree on that message box and re-save), and then run the "test" code below it seems to be okay (i.e. if "65.slxs" was fine then "y.xlsx" shdould be also file). That's what I tried:

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

The "y.xlsx" seems to be okay for me.

Note that I've also published the code as a package (private build), so it is possible try it like this:

// Instead of "npm install exceljs"
> npm install @nbelyh/exceljs

// instead of import ExcelJS from 'exceljs'
import ExcelJS from '@nbelyh/exceljs';

[nbelyh]

@trylovetom did the above help? I just wanted to confirm if fix is working this way or the issue is still there?

[nbelyh]

@trylovetom ping. Was the issue fixed by the above comment for you or not???

[solegaonkar]

Facing the same problem. Any guess how long this might take? I tried to use the '@nbelyh/exceljs';, but that does not help.

[nbelyh]

@solegaonkar did you try generating from a fresh file or already broken one? My fix only works if the file was not already broken before (see the comment above). Means, the file you are reading should be valid, then the file generated with fixed version of the lib should be also valid. If you start with an already broken file, the result will still be broken, even with my fix.

If this is not the case (you are starting with a valid file, but then after read/write sequence using the lib with my fix get a broken file) could you share the file, please?

[solegaonkar]

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

[nbelyh]

@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");

[solegaonkar]

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!

[nbelyh]

@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

[solegaonkar]

Wow! Yes this is working. Thanks @nbelyh for looking into this. You saved my day! Will you keep this '@nbelyh/exceljs' for some time? Or planning to merge it into the main branch soon?

[nbelyh]

@solegaonkar The "@nbelyh/exceljs" package is published to npmjs, it cannot be unpublished (npm does not allow unpublishing packages since that case with "left-pad"), so it will probably stay there forever 😅 I've aready created a pull request for the exceljs main branch: https://github.com/exceljs/exceljs/pull/2185 just not sure when it will be merged, considering this: https://github.com/exceljs/exceljs/discussions/2194

[Xalavar]

@nbelyh Hey, I tried using your minified js file from your npmjs package, but any spreadsheets I exported with it still seem to give the same errors. Also, I noticed you had uploaded 3 different versions. Is there any substantial difference between the 3 versions? In my case, these are the errors that remain unchanged, even after switching to your PR version:

Removed Feature: Data validation from /xl/worksheets/sheet1.xml part Removed Feature: AutoFilter from /xl/tables/table1.xml part (Table) Removed Feature: Table from /xl/tables/table1.xml part (Table)

UPDATE: It turns out autoFilter was the culprit for the last two removed features. Now it's just data validation, which I guess might not be relevant to this post.

[nbelyh]

@Xalavar Please use the latest version (4.3.2). They differ basically by description (functionally they are the same), npm creates the build for every version bump, and I was stupid enough to add the description warning saying this is not an official build only on the third time :) If the thing is not working, then please attach the example file that is not working and the code you are using (sufficient to reproduce the problem). Please note, the patch does not fix the already broken file; it does not break the valid one, so you should start with a working Excel file.

[tonioriol]

thanks @nbelyh, it did solve the issue for me. I can show you more or less what my setup/functionality was:

import { Workbook } from '@nbelyh/exceljs'

export const testsToXlsx = async (tests, template) => {
  const workbook = new Workbook()
  await workbook.xlsx.load(template)

  const worksheet = workbook.getWorksheet(1)

  let rowNumber = 9

  tests.forEach((test) => {
    test.fields.questions
      .filter((x) => !x.answersAsImages)
      .forEach((question) => {
        const q = [
          sanitize(question.fields.questionText),
          sanitize(question.fields.answers[0].fields.answerText),
          sanitize(question.fields.answers[1].fields.answerText),
          sanitize(question.fields.answers[2].fields.answerText),
          '',
          '20',
          `${findIndices(question.fields.answers, (x) => x?.fields?.correctAnswer)
            .map((x) => x + 1)
            .join(',')}`,
        ]

        const row = worksheet.getRow(rowNumber)

        // Set cell values individually, so we preserve styling. Starting from column "C".
        q.forEach((value, index) => {
          row.getCell(index + 2).value = value
        })

        row.commit()

        rowNumber += 1
      })
  })

  return await workbook.xlsx.writeBuffer()
}

Just replacing exceljs by @nbelyh/exceljs solved the issue. Which was that:

[maelp]

@nbelyh would it be possible to update your fork with the latest upstream changes, so we have an updated version with the fix?