Skip to content

[BUG] Formulae in "Formatting Rules" throws exception #2311

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
jonathankeebler opened this issue Jul 19, 2023 · 5 comments
Closed

[BUG] Formulae in "Formatting Rules" throws exception #2311

jonathankeebler opened this issue Jul 19, 2023 · 5 comments

Comments

@jonathankeebler
Copy link

🐛 Bug Report

If a formatting rule is setup for a sheet, that references another sheet, an exception is thrown when you call XLSX.writeBuffer
Screenshot 2023-07-19 at 2 51 12 PM

Cell model looks like this:

{
  type: 'expression',
  x14Id: '{BB2F621F-E459-4F3E-9A72-51C5F7F3EB5F}',
  priority: 42,
  ref: 'C16'
}

Exception:

TypeError: Cannot read properties of undefined (reading 'forEach')
    at CfRuleXform.renderCellIs (/Users/jonathan/baseline/service-2.0/node_modules/exceljs/lib/xlsx/xform/sheet/cf/cf-rule-xform.js:175:20)
    at CfRuleXform.render (/Users/jonathan/baseline/service-2.0/node_modules/exceljs/lib/xlsx/xform/sheet/cf/cf-rule-xform.js:125:14)
    at /Users/jonathan/baseline/service-2.0/node_modules/exceljs/lib/xlsx/xform/sheet/cf/conditional-formatting-xform.js:29:25
    at Array.forEach (<anonymous>)
    at ConditionalFormattingXform.render (/Users/jonathan/baseline/service-2.0/node_modules/exceljs/lib/xlsx/xform/sheet/cf/conditional-formatting-xform.js:26:17)
    at /Users/jonathan/baseline/service-2.0/node_modules/exceljs/lib/xlsx/xform/sheet/cf/conditional-formattings-xform.js:41:20
    at Array.forEach (<anonymous>)
    at ConditionalFormattingsXform.render (/Users/jonathan/baseline/service-2.0/node_modules/exceljs/lib/xlsx/xform/sheet/cf/conditional-formattings-xform.js:40:11)
    at WorkSheetXform.render (/Users/jonathan/baseline/service-2.0/node_modules/exceljs/lib/xlsx/xform/sheet/worksheet-xform.js:329:36)
    at /Users/jonathan/baseline/service-2.0/node_modules/exceljs/lib/xlsx/xlsx.js:562:22
    at Array.forEach (<anonymous>)
    at XLSX.addWorksheets (/Users/jonathan/baseline/service-2.0/node_modules/exceljs/lib/xlsx/xlsx.js:560:22)
    at XLSX.write (/Users/jonathan/baseline/service-2.0/node_modules/exceljs/lib/xlsx/xlsx.js:654:16)
    at async XLSX.writeBuffer (/Users/jonathan/baseline/service-2.0/node_modules/exceljs/lib/xlsx/xlsx.js:684:5)

Lib version: 4.3.0

Steps To Reproduce

sample.xlsx

const workbook = new Excel.Workbook();
await workbook.xlsx.load(file);
await workbook.xlsx.writeBuffer();

The expected behaviour:

The conditional formatting is applied.

Possible solution (optional, but very helpful):

In /lib/xlsx/xform/sheet/cf/cf-rule-xform.js, this code could be patched to not assume formulae is set:

renderExpression(xmlStream, model) {
    xmlStream.openNode(this.tag, {
      type: 'expression',
      dxfId: model.dxfId,
      priority: model.priority,
    });

    this.formulaXform.render(xmlStream, model.formulae[0]);

    xmlStream.closeNode();
  }
@MarcosParengo
Copy link

This is solved?

@Jervis-Miao
Copy link

Has the problem been solved？

@mashpie
Copy link

mashpie commented Jan 29, 2025

bug seams to persist:

[...]/monorepo/node_modules/.pnpm/exceljs@4.4.0/node_modules/exceljs/lib/xlsx/xform/sheet/cf/cf-rule-xform.js:158
    this.formulaXform.render(xmlStream, model.formulae[0]);
                                                      ^
TypeError: Cannot read properties of undefined (reading '0')

@AndresDSoto AndresDSoto mentioned this issue Jan 29, 2025
Open
@AndresDSoto
Copy link

In the above pr I have a very simple fix if you want to point to that branch in your package.json. You can install it using this command.
npm i https://github.com/AndresDSoto/exceljs/tarball/bug-fix/2311-x14Id-expressions

@mashpie
Copy link

mashpie commented Jan 30, 2025

Thank you! For now I apply a similar patch with pnpm patch:

diff --git a/lib/xlsx/xform/sheet/cf/cf-rule-xform.js b/lib/xlsx/xform/sheet/cf/cf-rule-xform.js
index 22b3e44ecc8731a3a04bac22d43f32c4211dfbe3..95208161a56e98b6ba49ba9dee8524c8051c1d0a 100644
--- a/lib/xlsx/xform/sheet/cf/cf-rule-xform.js
+++ b/lib/xlsx/xform/sheet/cf/cf-rule-xform.js
@@ -155,7 +155,7 @@ class CfRuleXform extends CompositeXform {
       priority: model.priority,
     });
 
-    this.formulaXform.render(xmlStream, model.formulae[0]);
+    this.formulaXform.render(xmlStream, model.formulae?.[0]);
 
     xmlStream.closeNode();
   }

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
6 participants
@mashpie @Siemienik @jonathankeebler @AndresDSoto @Jervis-Miao @MarcosParengo