Hi Team,
We’ve put together an Excel template, JSON payload, sample Java code, and the generated report, all using Aspose.Cells 25.9, and they’re attached for your reference. We’ve run into some unexpected behavior, especially concerning how variables and expressions are processed in the templates. We’d really appreciate it if you could take a look!
employees-variables-001_1758892391974.zip (34.1 KB)
Setup Details:
To help you understand our setup, here’s how we’ve configured things:
We’ve designated a special worksheet named DEMO_VARIABLES to hold all our variables. This is initialized using designer.setVariablesWorksheetName("DEMO_VARIABLES");.
The DEMO_VARIABLES sheet contains declarations for both global (workbook-wide) and worksheet-specific variables. Importantly, some worksheet-scoped variables are designed to override the global ones.
We’ve included a couple of ‘Expression’ type variables that combine simple operators like concatenation (&) and multiplication (*) to test custom calculations using these variables.
For each variable scope (global, worksheet-specific, etc.), we’ve created two template worksheets: one with a vertical layout and another with a horizontal layout. This gives us 6 worksheets (EMPVAR-00, EMPVAR-00 (2), EMPVAR-01, EMPVAR-01 (2), EMPVAR-02, EMPVAR-02 (2)) specifically designed to test variable behavior.
Additionally, there are two more worksheets (EMP-01 and EMP-01 (2)) that use variables more sparingly, focusing on expressions without explicit variable declarations.
Issues We’ve Observed:
When generating the report, we’ve noticed a few consistent problems across various worksheets:
- Unexpected Data Output (‘29’): Many expressions, particularly those intended to concatenate text (e.g., {DirectorsVar.FirstName}&" "&{ReporteesVar.LastName}) or calculate totals (e.g., TotalCostOfEmployeesVar), are incorrectly producing the number ‘29’ instead of the expected concatenated names or calculated values. This occurs in EMPVAR-00, EMPVAR-00 (2), EMPVAR-01, EMPVAR-01 (2), EMPVAR-02, EMPVAR-02 (2), EMP-01, and EMP-01 (2).
- Blank Expression Results: Some expressions, like DirectorsReporteesConcatVar in EMPVAR-00 and DOB + 10 in EMP-01 and EMP-01 (2), are not generating any output at all; the cells remain blank. The same happens for Total Cost of Employees using Var in formula and Total Cost of Employees without using Var in formula in EMP-01 (2).
- Content Overwriting: In some cases, an expression meant to display a calculated value (e.g., in EMPVAR-00 and EMP-01) is being completely replaced by a different, static text string. This suggests a conflict in how content is rendered from smart markers.
- Unwanted Empty Rows: We’re seeing empty rows appearing under certain sections (e.g., “Directors using Var DirectorsVar” in EMPVAR-01 (2) and “Reportees using Var ReporteesVar” in EMPVAR-02 (2)). We suspect this might be related to other tables or expressions placed nearby, possibly affecting row height or merging. The same issue occurs under “Directors without using Var” in EMP-01 (2).
Our Questions to You:
We’d greatly appreciate your insights on the following:
- What’s the recommended best practice for defining and utilizing variables within Aspose.Cells templates?
- How should we set up expressions (both with and without explicitly declared variables) to ensure they correctly calculate and display values in smart markers?
- What strategies can we employ to prevent content from being overwritten and avoid the generation of unwanted empty rows in our reports?
Thank you for your time and help in resolving these challenges!