Issue with directly accessing nested array in Excel template

Aspose.Cells Product Family
, ,
2

@sagaofsilence.dev

It seems you are encountering issues when trying to access nested arrays directly in your Excel template using Aspose.Cells. Based on your description, it appears that you are trying to access the reporters directly from the nested structure, which is causing the errors you mentioned.

When working with nested arrays in Aspose.Cells, it is important to ensure that the structure of your JSON data and the way you reference it in the Excel template are correctly aligned. Here are a few points to consider:

  1. Direct Access to Nested Arrays: Accessing nested arrays directly can be tricky. If you are trying to reference the reporters without first referencing the outer array (directors), it may lead to index errors, as the context of the outer array is lost. Ensure that you are correctly referencing the outer array before trying to access the inner array.

  2. Template Structure: Make sure that your Excel template is structured correctly to handle nested data. You should have a section for the outer array (directors) and then a nested section for the inner array (reporters). If you attempt to access the reporters directly without the context of their parent director, it can lead to errors like “Index -10 out of bounds for length 3” or “Invalid row index”.

  3. Example Reference: You can refer to examples that demonstrate how to work with nested objects in Aspose.Cells. For instance, in a Java example, a list of individuals is created, and their properties are accessed in a structured manner. This ensures that the data is processed correctly without running into index issues.

To resolve your issue, I recommend reviewing your JSON structure and the corresponding Excel template to ensure that the references are correctly set up. If you continue to face issues, consider providing a simplified version of your JSON and template for further assistance.

If you need more detailed examples or specific guidance, please let me know!

Sources:
[1]: UsingNestedObjects.java

1 Like
3

Thank you for the thorough explanation provided. Typically, users will access the parent array elements before looping over its children’s properties, including nested array properties.

However, I have encountered instances where users have attempted to directly access nested arrays. While these are considered corner cases, they are still necessary for users in real-world scenarios. For example, in this case, the user wanted to list all the reporters, no matter who they report to.

If there is no way to directly access nested arrays, we will need to document this limitation and provide guidance to our users.

Please confirm whether accessing nested arrays in Excel should be discouraged or not.

4

@sagaofsilence.dev,

For JSON data, inner array is based on or rendered with respect to outer array. Anyways, could you please share your sample (runnable) code that you are using to get the exception. We will evaluate your issue using your sample code and resource files soon.

Please provide your sample code and example file(s). We will check it as well.

5

See the Reportee section (row #11 to 13) in the attached “employees2B.xlsx” template.
JSON data used is also attached.
I am using JSON data sources. The logic creates data source for each top level property in the incoming JSON payload. In this case, it creates a data source named “RootData”.

I will provide template and data for the other error scenario soon.

6

@sagaofsilence.dev
We have reviewed your sample file and JSON data. When you use the following code to use “RootData” as the root node, the “RootData” node in the JSON data needs to be removed. By testing file “employees2A.xlsx” using the following example code, we can obtain the correct results. Please check the attachment. result.zip (20.0 KB)

Workbook wb = new Workbook(filePath + "employees2A.xlsx");
wb.getWorksheets().get(0).getCells().createRange("A2:H14").setName("_CellsSmartMarkers");
WorkbookDesigner designer = new WorkbookDesigner();
designer.setLineByLine(false);
designer.setWorkbook(wb);
designer.setJsonDataSource("RootData", new String(Files.readAllBytes(Paths.get(filePath + "employees-data.json"))));

designer.process(true);

wb.save(filePath + "out_employees2A.xlsx");
7

@sagaofsilence.dev
We can reproduce the issue by testing on the latest version v25.2 using the following sample code. ArrayIndexOutOfBoundsException occurs when using json data as smartmarker data source.

Workbook wb = new Workbook(filePath + "employees2B.xlsx");
wb.getWorksheets().get(0).getCells().createRange("A2:H13").setName("_CellsSmartMarkers");
WorkbookDesigner designer = new WorkbookDesigner();
designer.setLineByLine(false);
designer.setWorkbook(wb);
designer.setJsonDataSource("RootData", new String(Files.readAllBytes(Paths.get(filePath + "employees-data.json"))));

designer.process(true);

wb.save(filePath + "out_employees2B.xlsx");

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-46278

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.

8

Thank you for promptly addressing the issue and troubleshooting it.

As previously mentioned, I have attached two data samples: employees2A.xlsx (success template), employees2A_output.xlsx (success output), employees-data.json (JSON input data source), and employees2B.xlsx (error template).

In my processing logic, when I add a data source for top-level properties like RootData, the key is automatically removed from the input data, and its value is used to prepare the data source.

I look forward to receiving updates on the issue.

Thank you once again.

9

@sagaofsilence.dev
You are welcome. Thank you for your feedback. We will notify you promptly once there are any updates.

10

@sagaofsilence.dev
out_employees2B.zip (9.7 KB)
The attached file is generated by inner hot fix 25.2.2 with the following codes:

public static void main(String[] args) throws Throwable {
		
		Workbook wb = new Workbook(dir + "employees2A.xlsx");
		wb.getWorksheets().get(0).getCells().createRange("A2:H13").setName("_CellsSmartMarkers");
		wb.getWorksheets().get(0).getCells().createRange("A12:H13").setName("_CellsSmartMarkers_level3");
		WorkbookDesigner designer = new WorkbookDesigner();
		designer.setLineByLine(false);
		designer.setWorkbook(wb);
		designer.setJsonDataSource("RootData", new String(Files.readAllBytes(Paths.get(dir + "employees-data.json"))));

		designer.process(true);
		wb.save(dir + "out_employees2A.xlsx");
	  }

As there is no level 2 smart marker “Director” in the template file, so all Reportees are together， does it fit your need?

11

@sagaofsilence.dev
We are pleased to inform you that your issue has been resolved. The fix will be included in our upcoming release (Aspose.Cells for Java v25.3) that we plan to release in the first half of March 2025. You will be notified when the next version is released.

1 Like
12

The issues you have found earlier (filed as CELLSJAVA-46278) have been fixed in Aspose.Cells for Java 25.3.

13

Hello,

Tested with 25.4 version of Aspose Cells for Java.

This time, I tested the scenarios using the combination technique to come up with multiple scenarios of table ranges. Please find attached a zip file containing Java code, an Excel template, an input JSON file, and an output Excel.

1745504563806.zip (121.0 KB)

The output Excel file has a summary sheet. I have updated it with the results. Please go through it and let me know your comments on it.

Is there any Aspose documentation on how cell range is supposed to work in an Excel template with arrays and tables?

For some of the scenarios, either the data rows are not output or are interleaved.

14

@sagaofsilence.dev,

Thank you for providing the Excel template file, output Excel file (results), JSON data, and sample code snippet.

We appreciate your efforts in highlighting the issues related to data missing or alteration across different use-cases (worksheets). We will conduct a thorough evaluation and will get back to you soon.

15

@sagaofsilence.dev,

We have initiated a thorough investigation and have created a new ticket in our internal issue tracking system: Issue ID: “CELLSJAVA-46356”. We will evaluate and address the problems according to our support policies terms and provide updates or details as soon as they become available. We appreciate your patience and will keep you informed of any progress (once available).

16

@sagaofsilence.dev
If you use JSON data source , we will think you are using nested and nested template sheet. So if there are two ranges, we cannot simply know whether to process them together or one by one.

17

@sagaofsilence.dev
Please check
25.4.4.zip (55.5 KB)

We fixed some issues.
We processed smart markers row by row.
So in the worksheet “Misc2”, we only export one record to Range “A12:D13” because of “List #2 of Reporters” range. We can not know whether “List #2 of Reporters” range should be handled separately.

18

Thank you for the prompt reply.

FYI Summary of the test template 25.4.4.xlsx you shared.
image.png (18.2 KB)

Our document generation service is utilized across various domains, meaning users define their data models within templates according to their specific business requirements. We have limited control over this data modeling and report structure. Therefore, our approach focuses on educating users on best practices – highlighting what works effectively and what doesn’t – to empower them to make informed choices.

Our goal is to ensure predictable document generation, minimizing the need for users to rely on trial and error to achieve desired results. That’s why I’m reviewing the Aspose Cells documentation, particularly the examples and guidance on authoring templates when using nested object data structures.

We’ve encountered issues with a scenario involving two lists: list #1 (two directors) and list #2 (reporters associated with the first director – three reporters). Specifically, when using separate cell ranges for each table, an extra row is inserted into the second table. Ideally, the second table should only display the three reporters associated with the first director, without attempting to output reporters related to the second director.

Based on our experimentation, my understanding is the following:

  1. To ensure tables or ranges are output independently, they should be defined in separate cell ranges.
  2. To create a parent-child relationship where one table’s output depends on another, both tables should reside within the same cell range.

Could you confirm if this understanding is accurate? I would appreciate any advice you can offer on this scenario and would welcome any updates to the official documentation addressing these use cases.

19

@sagaofsilence.dev,

Thanks for sharing your findings.

We will evaluate/check and provide our details and insight. We will get back to you soon.

20

@sagaofsilence.dev
a) If WorkbookDesigner.LineByLine is true (default)
We will process smart markers row by row.
The only one director is output because we can not know whether list #1 and list #2 in the worksheet ”Template17“ are independent because they are in the same row.
So if the ranges are independent, please do not put them in the same line.

b) If WorkbookDesigner.LineByLine is false
We will process all smart markers together in the defined named range “_CellsSmartMarkers”, then you can nested layout your markers in two rows of defined named range “_CellsSmartMarkers” as :
&=RootData.Directors.FirstName &=RootData.Directors.MiddleName
&=RootData.Directors.Reportees.id &=RootData.Directors.Reportees.FirstName
If there is no defined named range “_CellsSmartMarkers”, we can know which smart marker should be processed together.

21

Thanks a bunch for the detailed explanation of how Aspose handles nested and related tables using the “_CellSmartMarkers” range and the impact of the LineByLine flag!

To better understand the inner workings and advise users effectively, I’m diving into the Aspose mechanism. My goal is to document and provide clear examples that guide users on when to leverage custom ranges and flags like LineByLine.

For instance, we could explore a naming convention such as “DEMO_01_1” (range with LineByLine = true) and “DEMO_02_2” (range with LineByLine = false).

Given that tables are often positioned side-by-side with related, but not necessarily parent-child, data, understanding how “_CellsSmartMarkers” ranges dictate processing is crucial.

I want to clarify:

  • When to use Custom Ranges: Scenarios that benefit from specific range definitions.
  • When to use Flags (LineByLine): How the LineByLine flag affects processing based on data relationships.
  • When to use Default Processing: Situations where default Aspose processing is sufficient without custom ranges.

By clarifying these points with documented examples, users can make more informed decisions about how to handle their specific table configurations.

See the attached example.
Screenshot of Related Tables (13.1 KB)