Variables in Excel template

Aspose.Cells Product Family
,
21

These are indeed great ideas.

22

Thank you for incorporating it and providing working examples.

23

I note that CELLSNET-58593 is mentioned in the release notes for Aspose Cells .NET version 25.7 but not for Aspose Cells Java. We need variable support in Java.

24

@sagaofsilence.dev,

Yes, the ticket was originally logged in .NET. Please note that the fixes and enhancements have also been incorporated into the Java version simultaneously, as the Java version is ported from the .NET codebase. We recommend trying the latest available version of Aspose.Cells for Java v23.7.

Feel free to reach out if you encounter any issues.

25

@sagaofsilence.dev
1, message2 message + “-xxx”
Please check the attached file in Variables in Excel template - #18 by simon.zhao.
Is it your need?

We have to support if… else with two ways:

Now you can define Tax rate as an Expression in variables worksheet as “Message2”.
And we are thinking about whether we can directly process it as an Expression .
The smart marker like : &=&=if({product.Code}== “IMPORTED”,0.3,0.1)~(Expression)

We have to add an “IF” or “Condition” smart marker to support this feature.
&=product.Name(IF:product.Code == "IMPORTED“)
As parentheses represent starting and ending parameters, so will you use complex condition in the “IF”?

26

Given our multi-tenant SaaS environment, where template designs and user needs vary significantly across different domains, the complexity of conditional expressions will likewise differ. We aim to implement a conditional expressions feature that closely mimics the functionality of Word’s <<if>> tag. This feature should ideally control either a single cell smart marker or a specified range of cells containing smart markers. I will prepare and share a sample template for further illustration shortly.

27

@sagaofsilence.dev,

Thank you for providing additional details regarding your requirements and outlining your desired conditional expressions. We will carefully review the information and respond with our evaluation soon.

28

@sagaofsilence.dev
Please check
formula.zip (26.0 KB)

We have supported “if” parameter of smart marker (See Cell A4 )and formulas with smart marker variables(See Cell C4)

29

@sagaofsilence.dev
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): CELLSNET-58756

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.

30

Thank you, We will try to use it as soon as it becomes available.

31

@sagaofsilence.dev
Thank you for your feedback. You are welcome. This is to inform you that your issue (logged earlier as “CELLSNET-58756”) has been resolved. The fix/enhancement will be included in an upcoming release (Aspose.Cells v25.8) that we plan to release in the first half of August 2025. You will be notified when the next version is released.

1 Like
32

The issues you have found earlier (filed as CELLSNET-58756) have been fixed in this update. This message was posted using Bugs notification tool by leoluo

33

Is there any official documentation explaining how to declare and use variables in Aspose Excel template? I could not find it.

34

@sagaofsilence.dev,

We are preparing and adding the relevant docs on “how to declare and use variables in Smart Markers”. We will add the documents with examples soon.

In the meantime, please refer to and try the following example code with the attachment for your reference.
result.zip (26.5 KB)

Workbook workbook = new Workbook(filePath + "template.xlsx");
ABC_1_Data res = (ABC_1_Data)JsonConvert.DeserializeObject(File.ReadAllText(filePath + "data.json"), typeof(ABC_1_Data));
List<ABC_1_Data> listFormData = new List<ABC_1_Data>();
listFormData.Add(res);
                        
WorkbookDesigner workbookDesigner = new WorkbookDesigner(workbook);

workbookDesigner.ContainsVariables = true;
workbookDesigner.SetDataSource("RootData", listFormData);
workbookDesigner.Process();
Assert.AreEqual("director first 2 last fff", workbook.Worksheets[0].Cells["C5"].StringValue);
workbook.Save(filePath + "result.xlsx");

public class ABC_1_Data
{

    public ABC_1_Data()
    {

    }
    [JsonProperty("EntityCin")]
    public string EntityCin { get; set; }

    [JsonProperty("EntityName")]
    public string EntityName { get; set; }
    [JsonProperty("FirstName")]
    public string FirstName { get; set; }
    [JsonProperty("MiddleName")]
    public string MiddleName { get; set; }
    [JsonProperty("LastName")]
    public string LastName { get; set; }
    [JsonProperty("DOB")]
    public string Dob { get; set; }

    [JsonProperty("SSN")]
    public string Ssn { get; set; }
    [JsonProperty("Directors")]
    public List<Employee> Directors { get; set; }
}

public class Employee
{
    [JsonProperty("id")]
    public string Id { get; set; }
    [JsonProperty("FirstName")]
    public string FirstName { get; set; }
    [JsonProperty("MiddleName")]
    public string MiddleName { get; set; }
    [JsonProperty("LastName")]
    public string LastName { get; set; }


    [JsonProperty("Reportees")]
    public List<Employee> Reportees { get; set; }
    [JsonProperty("Department")]
    public string Department { get; set; }
    [JsonProperty("City")]
    public string City { get; set; }
    [JsonProperty("GSTEnabled")]
    public string GstEnabled { get; set; }

    [JsonProperty("ITREnabled")]
    public string ItrEnabled { get; set; }
    public string getFullName()
    {
        return this.FirstName + this.MiddleName + this.LastName;
    }
}

Hope, this helps a bit.

35

Thanks for updating. We are writing user documentation. As soon as Aspose official documentation is ready, please let’s know.

36

@sagaofsilence.dev,

Certainly, we will inform you as soon as we upload the relevant documents containing detailed information and examples for your reference.

37

@sagaofsilence.dev,

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.

38

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:

  1. The Scope column mentions Worksheet. What are all valid scopes supported by Aspose Cells? Let’s document each with example.
  2. Is Scope optional? Is there default value of the scope?
  3. 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 ReporteeName is defined at Workbook scope and for certain worksheets, the user would be able to override it, by defining ReporteeName at Worksheet level scope.
  4. 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.

39

@sagaofsilence.dev,

Thank you for your feedback and for highlighting your points. We will review them and provide you with the necessary assistance soon.

40

Thank you. I tried to execute the example given on the if parameter and variables documentation page.

Here are observations:

  1. The worksheet containing variables is named as variables, still it needs us to explicitly call designer.setContainsVariables(true). It could be set by the Aspose engine if the workbook has variables worksheet. If the variables worksheet is present, it will set it to true else false.
  2. Earlier I used to set the workbook to designer using code designer.setWorkbook(workbook). But to make variable processing work I had to use new 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 like NullPointerException: Cannot invoke "java.util.HashMap.get(Object)" because "<local2>" is null which does not add any value to troubleshoot the issue.
  3. I observe that if parameter works only when used with variables. When I tried to use it for smart marker without variables, it failed with similar cryptic error NullPointerException: 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)