@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.
1,
If the variables worksheet does not contains any variables, we still simply process it as variables worksheet and delete it after processing.
2,
We do not care whether some variables are used, we just load all variables.
3,
We will process it as invalid smart marker and set null to cell if you do not want to keep invalid smart markers.
1 Like
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.
1 Like
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.
1 Like
The issues you have found earlier (filed as CELLSJAVA-46480,CELLSJAVA-46481) have been fixed in Aspose.Cells for Java 25.9.
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).
The official documentation, specifically How to Use if Parameter and Variables in SmartMarkers|Documentation, beautifully illustrates how to define variables. For example, on a dedicated “variables” worksheet, we can set them up like this:
| Scope | Name | Variable Name | Variable Value | Type |
|---|---|---|---|---|
| Worksheet | Template | Directors | RootData.Directors | |
| Worksheet | Template | Reportees | Directors.Reportees | |
| Worksheet | Template | ReporteeName | {Reportees.FirstName}& & {Reportees.LastName} | Expression |
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.
Looking forward to your response.
Thanks a bunch for your help!
We will provide more details for your questions soon after performing further investigation.
@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.
FYI
Documentation
has following example of smart marker:
&=&={Directors.FirstName}&" "& {Reportees.LastName}
So, I asked the question #2 above.
Generally, variable declaration should be done before using it. Anyways, we will get back to you with more details soon.
@sagaofsilence.dev
&=&={Directors.FirstName}&" "& {Reportees.LastName}
Directors and Reportees variables could be supported in this formula.
1 Like
I will test the variables with these points in mind and share my findings soon.
Sure, please take your time to test it thoroughly, and we’d appreciate hearing your feedback when you’re ready.
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!
Thank you for sharing your scenario along with the template Excel file, JSON data, and code snippet. Upon testing, I was able to reproduce the issues you mentioned. If possible, we request you to provide a sample Excel file showcasing your desired results across different sheets. This will help us log the appropriate ticket and work towards achieving the expected outcomes. Additionally, we may adjust the inserted markers as needed to ensure your requirements are met effectively.
Thanks for the expected output Excel file.
We did test your scenario/case using your template Excel file, JSON data and code snippet. We noticed your mentioned issues when processing variables expressions in Smart Markers.
We require thorough evaluation of the issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSJAVA-46508
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.
Thank you for reviewing!
Please keep in mind that the examples provided in the template are just representative samples—they don’t limit what you can accomplish. Our users often combine complex smart markers, custom expressions, and unique data layouts, frequently integrating powerful Excel features like sorting, grouping, or using conditional IF statements.
Anything that works with the standard smart marker syntax is expected to work seamlessly when you use variables. This includes familiar actions like accessing specific array elements (e.g., Directors[0]), grabbing a range of data (Directors[0:3]), or applying parameters to control output (e.g., sorting names: Directors.Name(ascending:1)).
Thank you for sharing your concerns and providing additional details.
We appreciate your feedback and will evaluate the issues you’ve highlighted regarding variables and custom expressions in Smart Markers. We remain committed to enhancing the flexibility of the Smart Markers feature when utilizing variables with custom expressions. Additionally, we will continue to improve and expand the module to better handle various data types, structures, and complex multidimensional arrays, ensuring the smart marker syntax adapts seamlessly to meet diverse reporting requirements.
We will get back to you with new updates soon.
@sagaofsilence.dev
Please check the attached file generated after we changed template and fixed some bugs :
employees-variables-001.zip (28.9 KB)
WorkbookDesigner designer = new WorkbookDesigner(new Workbook(dir + "employees-variables-001.xlsx"));
designer.setVariablesWorksheetName ( "DEMO_VARIABLES");
designer.setJsonDataSource(null, ReadText(dir + "employees-data.json"));
designer.process();
designer.getWorkbook().save(dir + "dest.xlsx");
1,If you want to process all smart markers in a row together, please add range smart marker.
2,If you do not insert rows when processing a part of smart markers in a row, please add shift smart marker.
3,If the smart marker is expression , please use &=&= smart marker.
4,In worksheet “EMP-01 (2)”, if shift smart marker is used, a range will be inserted, so the range “I5:I15” will be shifted down, but “J5:J15“ will not move.
Please change marker as &=&={RootData.Directors.FirstName}&” "& {RootData.Directors.Reportees.LastName}~(range:I2:J2,shift) to make above smart markers shift together to shift “I5:J5”.
5,We do not support smart Expression as a variable of dynamic formula(see EMPVAR-00!A5)
If you not want to operate it , please simply use smart Expression as normal smart marker.