Issue with directly accessing nested array in Excel template

Aspose.Cells Product Family
, ,
1

I am following an example of nested arrays in an Excel template using the named range _CellsSmartMarkers

The employees JSON data has a nested array. The directors (outer array) and their reporters (inner array).

When I use both the arrays in Excel template, it shows the data properly.
For each director, it gives reporters under it. (PFA empoyees2A.xlsx, empoyees2A_output.xlsx, employees-data.json)

But if I try to access the reporters directly under the reporters only section, then it fails.
It gives the error: Index -10 out of bounds for length 3
(PFA empoyees2B.xlsx)

In another scenario, when I accessed such a nested array directly (without outputting any property of the object in the outer array), it gave the error: Invalid row index.

Am I missing anything? Is it allowed to access nested/inner arrays directly?

PFA the templates (templates.zip).
templates.zip (28.9 KB)

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.