Aspose.Word for Java allows you to define variables within your Word templates, enabling concise expressions. For example, the provided snippet showcases how to iterate through employee data using variables:
<<var [employees = DataRoot.company.employees]>>
<<foreach [emp in employees]>>
<<[emp.firstname]>>
<</foreach>>
However, the question arises: Is there a similar mechanism for defining variables directly within Excel templates?
Repeatedly writing lengthy expressions in Excel is inefficient, increases the likelihood of errors, and makes maintenance a challenge. Therefore, variable definition support in Excel templates would be highly beneficial.
Aspose.Cells for Java does not support template-time variable declarations like Aspose.Words does (e.g., no <<var>> equivalent inside .xlsx templates). Aspose.Cells provides Smart Markers feature that has its own rules and grammar. In Smart Markers you can also define variables/arrays: Using Smart Markers|Documentation to implement your requirements to certain extent.
Upon examining the variable array in the Excel template, I found that it did not enable me to abbreviate or simplify the expressions like the variables in the Word template, which is a functionality that our users are eagerly anticipating.
@sagaofsilence.dev
With Cells products, you only need to create simple template files to meet your needs. Please refer to the following example code and check the attachment. smartmarker.zip (16.7 KB)
@sagaofsilence.dev
You can also use nested objects to implement the requirements. Please refer to the following example code and check the attachment. nestedobjects.zip (16.4 KB)
System.Collections.Generic.ICollection<Employee> employeeList = new System.Collections.Generic.List<Employee>();
employeeList.Add(new Employee("aaa first", "aaa last", "aaa department"));
employeeList.Add(new Employee("bbb first", "bbb last", "bbb department"));
Company company = new Company("Aspose", employeeList);
System.Collections.Generic.ICollection<Company> companyList = new System.Collections.Generic.List<Company>();
companyList.Add(company);
Workbook wb = new Workbook(filePath + "smartmarker.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.Workbook = (wb);
designer.SetDataSource("Company", companyList);
designer.Process(true);
wb.Save(filePath + "out_net_nested_objects.xlsx");
class Company
{
private string mCompanyName;
private System.Collections.Generic.ICollection<Employee> mEmployees;
public Company(string companyName, ICollection<Employee> employees)
{
this.mCompanyName = companyName;
this.mEmployees = employees;
}
public string CompanyName
{
get { return mCompanyName; }
set { mCompanyName = value; }
}
public ICollection<Employee> Employees
{
get { return mEmployees; }
set { mEmployees = value; }
}
}
class Employee
{
public Employee(string fist, string last, string department)
{
this.mFirstName = fist;
this.mLastName = last;
this.mDepartment = department;
}
private String mFirstName;
public String FirstName
{
get { return mFirstName; }
set { mFirstName = value; }
}
private String mLastName;
public String LastName
{
get { return mLastName; }
set { mLastName = value; }
}
private String mDepartment;
public String Department
{
get { return mDepartment; }
set { mDepartment = value; }
}
}
About how to use nested objects for smart markers, please refer to the following document.
Thank you for your response and the helpful code samples demonstrating array and nested object usage within Excel templates for document generation.
Our SaaS product empowers users to create Excel templates with highly flexible structures and complex expressions, leveraging nested objects to any desired depth. We use the Aspose engine to process these templates, along with JSON data payloads, to produce Excel reports.
A key challenge arises from the complexity of these reports, frequently leading to the repetition of lengthy expressions within the templates. Our users, accustomed to the variable support in Aspose Words, find the lack of a similar feature in Excel template authoring cumbersome. They face the tedious task of repeatedly writing long expressions and, critically, having to manually update all related expressions whenever changes are made to the underlying data structure within a table.
For instance, if a user needs to modify the path node.suppliers.products.categories.general.color.external to node.suppliers.products.categories.season.color.external (where only “general” changes to “season”), they must hunt down and revise every instance of that expression. This is a common scenario for our users, and the introduction of variables would significantly streamline their workflow. The ability to define a variable to represent a portion of the expression would allow for easier modifications and automatic propagation of changes across all dependent expressions.
Therefore, I strongly believe that incorporating a variable definition feature, similar to that found in Aspose Words, would greatly enhance the usability and efficiency of Aspose Cells for our users.
@sagaofsilence.dev
I get your need now.
But Excel are collection of worksheet which are collection of cells, Word are collection of pages.
There is a problem that how to set and arrange Variables in Excel template.
1,We can try to accept a variables Hashtable in WorkbookDesigner, if something is changed, codes should be changed too.
2,
Setting variables definition into a worksheet named variables as smartmarker.zip (8.4 KB)
The worksheet will be removed after processing.
This method will replicate the variable definition process used in Word templates. Once the document template is processed, these variables are removed from the final output. Aspose.Words also allows for variable redefinition (overriding values) and using one variable to define another, as demonstrated in the following example:
Variables are defined and accessible from the point of their declaration onwards within a page.
Considering these features, let’s explore how closely we can emulate Word’s variable definition capabilities within Cells by potentially introducing a dedicated worksheet for variables.
PFA updated version of proposed variable definition. smartmarker-var-updated.zip (8.6 KB)
Furthermore, we can consider incorporating name defined at both the worksheet and workbook levels using Name Manager in Excel.
@sagaofsilence.dev
There are some questions:
1). Scope
Do you mean there will be same Variable Name in different scope?
2). message Hello World
We think all data are from WorkbookDesigner.SetDataSource(), so we should not set value of variable in the template. It’s better that there is only definition in the template file, otherwise it will confuse.
3). message2 message + “-xxx”
All data such as message-1, message-2, message-3 and so on in the data source should be processed as message2.
4). Could you post a data source file and Expected result file?
And, you can add more expected variable definition in the template files, then we can better understand your needs.
5). It seems that we cannot support “if … else…”.
6). Does <<var [employees = DataRoot.company.departs.employees]>> mean exporting all employees of company?
7). We will not support variable definitions within the recycling body, such as:
<<foreach [depart in departs]>>
<<var [employees = DataRoot.company.employees]>>
<</foreach>>
@sagaofsilence.dev
Q8. in the Row 6: &=employees.FirstName &=employees.LastName &=employeeDept
Do you still want to process them as a table together if smart marker does not contain same prefix?
In the meantime, we will evaluate your original requirements and try to support variables in Excel template for smart marker. 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-58593
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.
@sagaofsilence.dev 20250618.zip (9.0 KB)
We made some changes in variables worksheet.
1,Type column represents the type of Variable.
2,{} represents a Variable is referred in Expression Variable because the name of columns will contains specific characters such as space , quotation mark, and so on.
@sagaofsilence.dev
Please check files in vmarker.zip (17.6 KB)
which is generated with the following codes and inner hot fix :
Workbook workbook = new Workbook(dir + “vmarker.xlsx”);
WorkbookDesigner workbookDesigner = new WorkbookDesigner(workbook);
workbookDesigner.ContainsVariables = true; // Indicates whether containing variables.
workbookDesigner.SetJsonDataSource(“Company”, File.ReadAllText(dir + “test.json”));
workbookDesigner.Process();
workbook.Save(dir + “dest.xlsx”);
Note: the Expression must follow the rule of formulas of MS Excel. For Example : In MS Excel , “+” only works for number value, if you want to join the string , please use “&” or CONCAT function .
The issues you have found earlier (filed as CELLSNET-58593) have been fixed in this update. This message was posted using Bugs notification tool by leoluo
Scope
Do you mean there will be same Variable Name in different scope? Answer: Yes, the user can declare the same variable name for the different worksheets. This could be intentional and a well-considered decision.
message Hello World
We think all data are from WorkbookDesigner.SetDataSource(), so we should not set value of variable in the template. It’s better that there is only definition in the template file, otherwise it will confuse. Answer: You can use hand-coded data in the template as you wish.,The data may vary from one template to another, and different customers might use different data for the same type of template.
message2 message + “-xxx”
All data such as message-1, message-2, message-3 and so on in the data source should be processed as message2. Answer: The calculated data may not be included in the system-generated JSON payload, so users can create the calculated data from the basic/atomic data in the JSON payload.
Could you post a data source file and Expected result file?
And, you can add more expected variable definitions in the template files, then we can better understand your needs. Answer: I have attached the sample data company.zip (380 Bytes), which can be referred to for the variables declared in the examples. Please note that the examples provided are hypothetical. When writing complex expressions, declaring and reusing variables becomes an important feature.
It seems that we cannot support “if … else…”. Answer: Our users need this feature. When users compare the Word template to the Excel template to achieve the same output, the Excel template does not support the conditional inclusion of sections or changing the cell value conditionally. In Excel, it’s not possible to add expressions into the IF formula; the formula only accepts cell references and values recognized by Excel formulae.