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
The issues you have found earlier (filed as CELLSJAVA-46508) have been fixed in Aspose.Cells for Java 25.10.
Hi Aspose Team,
Iām working with Aspose Cells (version 25.11) and have run into a bit of a challenge when using variables within the if parameter of smart markers in my Excel templates.
It seems that variables only function correctly when they are part of a full conditional expression within the if parameter. For example, if:{myVariable} > 100 works perfectly.
However, if I try to use a variable to directly define the entire if parameter (like if:{myBooleanVariable} or just myIfVariable), the smart marker either produces no output or incorrect results. Itās almost as if the variable itself isnāt being evaluated as a boolean condition.
To help illustrate the issue, Iāve included examples below showing both the working scenarios and the ones that arenāt producing the expected output.
Example 1: Book Price Check
- Working as expected: (These formulas give the correct output, highlighted in green in the output file.)
&=node.books.price(if:{node.books.price} <= 100)&=bookPrice(if:{bookPrice} <= 100)
- Not working as expected: (These formulas result in no output or incorrect data.)
&=bookPrice(if:{booksUpto100Check})&={booksUpto100}&=booksUpto100
Example 2: Book Availability & Price Range
- Working as expected: (These also give the correct output, highlighted in green in the output file.)
&=node.books.price(if:AND({node.books.isAvailable}, {node.books.price} > 200,{ node.books.price} <= 300)) &=node.books.isAvailable&=bookPrice(if:AND({bookAvailable}, {bookPrice} > 200,{ bookPrice} <= 300)) &=bookAvailable
- Not working as expected: (These do not yield the desired results.)
&=bookPrice(if:AND({bookAvailable}, {booksAbove200Check},{booksUpto300Check})) &=bookAvailable&=bookPrice(if:{availableBooks201upto300Check}) &=bookAvailable&=availableBooks201upto300 &=bookAvailable
Iāve attached my Excel template, the JSON payload, output file and the sample program Iām using to generate these reports.
template-if-condition-with-variables_1764658473073.zip (53.9 KB)
Could you please take a look and let me know if Iām missing any crucial steps in how I should be declaring or using these variables for direct if conditions?
Thanks a lot for your help!