Certainly, we will inform you as soon as we upload the relevant documents containing detailed information and examples for your reference.
Please note, we have now added documents featuring examples with details on the “range” parameter, “if” parameter and using variables in Smart Markers. Please feel free to check these documents, which also include detailed explanation and sample files for your reference.
Thank you very much for providing the example for variables! It clearly illustrates how to define and use variables within the ‘Worksheet’ scope, using ‘Directors’, ‘Reportees’, and ‘ReporteeName’ as excellent demonstrations. This has been very helpful, and based on this, we have some follow-up questions to understand the full capabilities regarding scopes and variable types in Aspose Cells.
| 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 |
We have following open points:
- The Scope column mentions Worksheet. What are all valid scopes supported by Aspose Cells? Let’s document each with example.
- Is Scope optional? Is there default value of the scope?
- We had discussed Worksheet and Workbook scopes earlier. We also thought that it will be good idea to allow variables in Worksheet scope to override those in Workbook scope. Is such overriding supported? Suppose
ReporteeNameis defined at Workbook scope and for certain worksheets, the user would be able to override it, by definingReporteeNameat Worksheet level scope. - The Type column mentions Expression. It appears that it is optional. What are all valid types supported by Aspose Cells? Let’s document each with example.
Looking forward.
Thank you for your feedback and for highlighting your points. We will review them and provide you with the necessary assistance soon.
Thank you. I tried to execute the example given on the if parameter and variables documentation page.
Here are observations:
- The worksheet containing variables is named as
variables, still it needs us to explicitly calldesigner.setContainsVariables(true). It could be set by the Aspose engine if the workbook hasvariablesworksheet. If the variables worksheet is present, it will set it totrueelsefalse. - Earlier I used to set the workbook to designer using code
designer.setWorkbook(workbook). But to make variable processing work I had to usenew WorkbookDesigner(workbook). If that is needed by the engine, then that should be documented. It doesn’t complain about the variables flag value and give error likeNullPointerException: Cannot invoke "java.util.HashMap.get(Object)" because "<local2>" is nullwhich does not add any value to troubleshoot the issue. - I observe that
ifparameter works only when used withvariables. When I tried to use it for smart marker without variables, it failed with similar cryptic errorNullPointerException: Cannot invoke "java.util.HashMap.get(Object)" because "<local2>" is null. As Aspose is closed source module, it becomes very difficult to debug the issues. PFA the templates, sample code and JSON data used, for ready reference.
if-param-test.zip (21.2 KB)
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.
1 Like
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.
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.