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.
I will check this and get back.
Please take your time to check the template Excel file, carefully review/evaluate it, and adhere to the provided guidelines. Should you have any questions or encounter any issue, please don’t hesitate to reach out to us.
Thanks so much for the swift response and for providing the helpful, working template! It immediately addressed the initial issues.
I’m happy to report that the resulting dest.xlsx output is exactly what we needed; using the shift parameter was a major help!
However, I have a few follow-up questions regarding the syntax and a new error I encountered:
- Documentation Query: I noticed the use of the
~operator when adding parameters like shift (for example:&=&={CtcPerEmployeeVar} * {CountOfEmployeesVar}~ (shift)). Could you confirm if this specific syntax is officially documented within the Aspose Cells smart markers documentation? - Compatibility Issue: When I tried running the program using the updated template you provided, I encountered a NullPointerException (stack trace is shared below). Does the syntax used in your template fully support Aspose Cells for Java v 25.9?
Exception in thread "main" java.lang.RuntimeException: java.lang.RuntimeException: java.lang.NullPointerException: Cannot invoke "com.aspose.cells.t05.f(com.aspose.cells.p5g)" because "<local3>" is null
at AsposeCellsDemoRun.main(AsposeCellsDemoRun.java:26)
Caused by: java.lang.RuntimeException: java.lang.NullPointerException: Cannot invoke "com.aspose.cells.t05.f(com.aspose.cells.p5g)" because "<local3>" is null
at AsposeCellsDemoRun.testExcelTemplate2(AsposeCellsDemoRun.java:53)
at AsposeCellsDemoRun.main(AsposeCellsDemoRun.java:24)
Caused by: java.lang.NullPointerException: Cannot invoke "com.aspose.cells.t05.f(com.aspose.cells.p5g)" because "<local3>" is null
at com.aspose.cells.WorkbookDesigner.a(Unknown Source)
at com.aspose.cells.WorkbookDesigner.a(Unknown Source)
at com.aspose.cells.WorkbookDesigner.a(Unknown Source)
at com.aspose.cells.WorkbookDesigner.a(Unknown Source)
at com.aspose.cells.WorkbookDesigner.a(Unknown Source)
at com.aspose.cells.WorkbookDesigner.a(Unknown Source)
at com.aspose.cells.WorkbookDesigner.a(Unknown Source)
at com.aspose.cells.WorkbookDesigner.a(Unknown Source)
at com.aspose.cells.WorkbookDesigner.process(Unknown Source)
at com.aspose.cells.WorkbookDesigner.process(Unknown Source)
at AsposeCellsDemoRun.testExcelTemplate2(AsposeCellsDemoRun.java:47)
@sagaofsilence.dev
1, For dynamic formulas, we have to add special char (~) as a separator . We will update documents about it.
2,There is a bug in current version 25.9. We will fix it in the next version 25.10
1 Like
Thank you for the clarification! We plan to retest this functionality immediately after version 25.10 is publicly released.
Validation Requirements
To maintain data integrity, we must be able to parse all smart markers and variable declarations accurately. This process allows us to extract every field being used and validate those fields against our underlying data model.
Expression Variable Syntax
Our main question concerns the exact syntax allowed when defining a variable of the Expression type.
For example, when we define: TotalCostOfEmployeesVar (Type: Expression) = {CtcPerEmployeeVar} * {CountOfEmployeesVar}
We then use it simply as: &=TotalCostOfEmployeesVar.
This usage appears to replace the variable placeholder with the actual formula. We would like to know the allowed scope for this formula: Can these Expression variables support any valid Excel-like formula?
For instance, are complex functions supported, such as:
- MAX({RootData.LastYearCountOfEmployees}, {RootData.CountOfEmployees})
- ROUND({TotalCostOfEmployeesVar}, 2)
- And other standard mathematical operations?
Using Variables in Dynamic Formula
There is example of this:
&=&={CtcPerEmployeeVar} * {CountOfEmployeesVar}~ (shift)
So, same question is applicable to this smart marker too. Can we use any valid Excel formula or expression in dynamic formula smart marker using variables?
Request for Clarification
Finally, we need help understanding the following point you raised. We checked the template reference (EMPVAR-00!A5) but are still unclear on the implication:
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.
Could you please elaborate on what you define as a “dynamic formula” in this context? Furthermore, what is the practical difference between treating a smart Expression as a variable versus using it as a normal smart marker?
@sagaofsilence.dev
If an Expression Variable was defined:
TotalCostOfEmployeesVar (Type: Expression) = {CtcPerEmployeeVar} * {CountOfEmployeesVar}
1, Yes,Expression variables supports any valid Excel-like formula.
2,please simply use as: &=TotalCostOfEmployeesVar .
3,
The expression in EMPVAR-00!A5 of original template file is &=&={DirectorsReporteesConcatVar}.
&=&= means this smart marker is an expression or formula.
This “TotalCostOfEmployeesVar” Expression Variable can not be a Variable in another Expression .
It’s not supported now because it’s hard to implement nest calculating smart marker expression variable .And I guess it will work slowly.
Thanks for the clarification! To make sure we’re on the same page, here’s a quick summary:
Chaining Variables: Variables can refer to other variables, which is super handy for building up complex data paths or breaking things down step-by-step.
- For example:
DirectorsVar = RootData.Directors ReporteesVar = DirectorsVar.Reportees(Notice howReporteesVarusesDirectorsVar!)
Expression Variable Limit: Expression type variables are a bit special – they can’t directly reference other variables. This helps keep things clean and prevents complicated ‘nesting’ issues.
Excel-like Power: On the bright side, both Expression type variables and dynamic formulas are incredibly flexible! You can use any valid Excel-like formula within them, giving you a huge amount of power.
Output Syntax: To output a plain variable’s value, simply use &=. You don’t need &=&= for that! The &=&= syntax is specifically reserved for outputting the result of an expression or formula.
Your understanding of variables, expressions, and dynamic formulas in Smart Markers appears to be OK. However, we will check and conduct a thorough evaluation and provide you with our feedback soon.
@sagaofsilence.dev
Your understanding is right.
About Expression Variable Limit : they can’t directly reference other Expression Variables. Other normal variables could be referenced.
1 Like
That was very helpful, thank you for clarifying!
Before we cover the follow-up questions, please note that worksheet names often contain spaces, such as “Annual Report” or “Monthly Sales Target.”
I have a few follow-up questions regarding the variable naming conventions:
- Could you outline the specific naming rules for variables?
- Are spaces allowed in variable names, or are special characters (like @ or $) permitted? If spaces are allowed, then does that affect the way we refer/use the variable in other variable declaration and smart marker?
- Are these variable names case-sensitive?
1, It’s better that we can define variable as Program language : C#, Java or C++.
Dot is not allowed.
2, Though we do not add limitation about spaces or special characters (like @ or $) , if they are not necessary, please do not add them. If you really need some special characters , we will do some tests about them.
3,These variable names are not case sensitive.
This is to inform you that your issue (Ticket ID: “CELLSJAVA-46508”) has been resolved. The fix/enhancement will be included in the upcoming release (Aspose.Cells v25.10) that we plan to release in the first half of October 2025. You will be notified in this thread once the new version is published.
1 Like