Excel template with nested array and named ranges

Aspose.Cells Product Family
, ,
1

As per the example given on page Smartly importing and placing data with Smart markers|Documentation, I am trying the employees example (directors and reporters) with simple to complex scenarios.

I found that if there is one nested table per worksheet, the suggested solution suggesting to use a range with name _CellsSmartMarkers and LineByLine set to false works.
I added a name _CellsSmartMarkers with refersTo value =Template!$A$11:$H$15

But there might be more than one such nested table in a single worksheet that might need us to turn off LineByLine processing. In Excel, we can define a name with comma-separated ranges to refer to multiple sections of the worksheet.
So I added a name _CellsSmartMarkers with refersTo value =Template!$A$11:$H$15,Template!$A$24:$H$28

But this did not work. The document generation failed with the error: Invalid row index.

Is it acceptable to use _CellsSmartMarkers to refer to multiple ranges?
Or it is restricted to refer to a single range in a worksheet.
Attaching the excel template and screenshot for the name range
nested-tables-name-range.zip (204.3 KB).

2

@sagaofsilence.dev

Can you please clarify what error message you are receiving when trying to use multiple ranges with the name _CellsSmartMarkers? Additionally, could you provide more details about the structure of your Excel template?

3

I have already mentioned the error I got: Invalid row index.
And the template used can be found in the zip file attached to the thread.

4

@sagaofsilence.dev

Thank you for the provided resources. Multple references in a defined name make the process much more complicated. Anyways, we will investigate it and provide feedback soon.

5

@sagaofsilence.dev
At present, Aspose.Cells does not support setting multiple ranges for smartmarker.

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

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
6

@sagaofsilence.dev
Could you create an excepted file with such template file and json data?
We are confused how to set data.

7

employees_multi_range_output.zip (10.8 KB)

multi-range-output.png (31.3 KB)

PFA

8

@sagaofsilence.dev
Thank you for your feedback and detailed information provided. Once there is an update, we will notify you promptly.

9

@sagaofsilence.dev
We can not process all smart markers in A1:H28 because same level smart markers are under different parent markers.
We have to process area by area as the following:
designer.setOnlyArea(true);//in designing…
wb.getWorksheets().get(0).getCells().createRange(“A25:H28”).setName(“_CellsSmartMarkers”);
designer.process(true);
wb.getWorksheets().get(0).getCells().createRange(“A2:H15”).setName(“_CellsSmartMarkers”);
designer.process(true);
Does it fit your need? If yes, we will start to add designer.setOnlyArea(true) and implement it.

1 Like
10

Probably I will be able to use this idea of processing one area at a time. I will add dedicated name ranges in the input template and then if such name ranges are found, I will process one area at a time. I will get back to you if I can achieve it.

Is this idea of area-by-area processing documented in the official documentation of smart markers?

11

@sagaofsilence.dev,

Sure, please take your time to implement the proposed approach. Please feel free to write us back with details, if you find any issue.

We will consider adding/updating relevant docs for Smart Markers after adding the specified use-case (if possible).

12

@sagaofsilence.dev
We will add WorkbookDesigner .Process(range, true); method to process a range in the next version 25.3 as the following :

Workbook wb = new Workbook(dir + "CELLSJAVA46279.xlsx");
		WorkbookDesigner designer = new WorkbookDesigner();
		designer.setLineByLine(false);
		designer.setWorkbook(wb);
		designer.setJsonDataSource("RootData", new String(Files.readAllBytes(Paths.get(dir + "CELLSJAVA46279.json"))));
		Range range = wb.getWorksheets().get(0).getCells().createRange("A24:H28");
		range.setName ("_CellsSmartMarkers");
		designer.process(range, true);

		range =  wb.getWorksheets().get(0).getCells().createRange("A18:D19");
		range.setName ("_CellsSmartMarkers");
		designer.process(range, true);

		range =  wb.getWorksheets().get(0).getCells().createRange("A2:H15");
		range.setName ("_CellsSmartMarkers");
		designer.process(range, true);
		
		wb.save(dir + "CELLSJAVA46279.xlsx");

CELLSJAVA46279.zip (12.0 KB)

1 Like
13

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