Thanks for sharing sample code and template files to demonstrate your observations.
I tested your scenario/case using your sample files. I noticed your findings and I think we might enhance the the Smart Markers’ feature (using variables in Smart Markers) a bit. We have opened the following new ticket(s) in our internal issue tracking system and may deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s):
CELLSJAVA-46479 - Smart Markers need to explicitly call WorkbookDesigner.setContainsVariables(true) to make it work
CELLSJAVA-46480 - Need to use new WorkbookDesigner(workbook) unnecessarily to make variables processing work
CELLSJAVA-46481 - if" parameter works only when used with variables
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
There is ambiguity here because some files from users really contain variables worksheet too.
So please explicitly call designer.setContainsVariables(true) to confirm worksheet is variables worksheet
Thanks for confirming! We absolutely agree that we shouldn’t force users to name a worksheet “variables.”
Since our product is a multi-tenant SaaS where customers control their own template designs, having this be configurable is really important.
Our plan is to provide clear documentation for our customers, showing them how to use Aspose features (including variable declaration) with syntax and examples.
So, our main question is: Does the Aspose engine inherently expect variables to be declared in a worksheet specifically named ‘variables’?
Ideally, we’d love for this worksheet name to be configurable – perhaps defaulting to ‘variables’ but allowing customers to easily override it within the designer, like this:
designer.setVariablesWorksheet("DEMO_VARIABLES");
This flexibility would then allow us to dynamically check for its presence and set a flag accordingly:
// Checks if the configured variables worksheet is present
boolean variablesPresent = hasVariablesWorksheet(workbook);
designer.setContainsVariables(variablesPresent);
We could implement this either directly in the Aspose designer or through custom logic in our service.
We’re really keen to hear your thoughts on this approach!
@sagaofsilence.dev
Thank you for your feedback and suggestions. We will further analyze your issues and suggestions. We will notify you promptly once there are any updates.
@sagaofsilence.dev
Thanks for your info.
We will obsolete ContainsVariables property and add VariablesWorksheet property, then you can assign which worksheet is variable worksheet.
That’s a solid plan! I suggest we set the default worksheet name for our Aspose Cells variables to variables, making it easy for our service logic to override this if a different name is required.
To ensure we’re prepared for all situations, let’s also discuss how we’d handle the following scenarios regarding Smart Markers:
What if the variables worksheet is present but doesn’t contain any variables at all?
How should we manage cases where the variables worksheet exists, but one or more (or even all) of its variables aren’t actually used by any Smart Markers?
And what’s our approach if a Smart Marker tries to use a variable name that’s incorrect (e.g., a typo or a variable that simply doesn’t exist)?
@sagaofsilence.dev
Thank you for your feedback. We will further analyze your suggestions and various scenarios. We will notify you promptly once there are any updates.
We have fixed this issue. It’s better to use new WorkbookDesigner(workbook). If you use new WorkbookDesigner() and designer.setWorkbook(workbook), we have to initialize some options twice.
I am checking whether new WorkbookDesigner() and setWorkbook(workbook) should be obsoleted.
This is to notify you that your issue (Ticket ID: “CELLSJAVA-46481”) has been resolved. This fix/enhancement will be included in the next release (Aspose.Cells v25.9), which is scheduled for the second week of September 2025. We will inform once the new version is available.
I’m working with Aspose.Cells v25.9 and have some follow-up questions about declaring and using variables in our Excel templates.
We understand that Aspose.Cells allows for two main types of dynamic formulas: those that generate a single output, and “repeat” formulas that produce output for each row inserted (like a list of items).
Then, we can simply use these variables in our template worksheet, like &=ReporteeName (or directly as &=&={Reportees.FirstName}&" "& {Reportees.LastName} for a repeating formula).
This brings us to a few specific questions:
Non-Repeating Formulas: Can these same variables be used within dynamic formulas that don’t repeat across multiple rows?
Direct Expressions: Instead of declaring a variable and then using it, can we simply embed the full expression directly into the smart marker? For instance, would something like &=&={RootData.Directors.Reportees.FirstName}&" "& {RootData.Directors.Reportees.LastName} work, or are we limited to only using declared variables inside the curly braces {}?
Other Operators: The example shows string concatenation (&). Can we also use other operators—like arithmetic (+, -, *, /), logical, or comparison operators—within these expressions?
Understanding the full “grammar” of these expressions is really important for us. It will help us correctly parse them, identify all the data attributes they reference, and validate them against our model rules before generating documents.
@sagaofsilence.dev
At first, we do not support Expression Variables as variables of dynamic formula inside the curly braces {} now. For Example: &=&={Reportees.FirstName}&" "& {ReporteeName}. This may require repeated recursive operations. Do you need such feature?
They can be used in non-repeating formulas.
Yes. Both direct Expressions and defined variables(exclude Expression variables) can be used in dynamic formulas.
Any operator can be used as long as Excel supports it. And Excel functions can be used too.
You can think it as normal formula of Excel if variables are simple value or rang reference.
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!
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!