I am evaluating possibility of using Aspose Cells for Java for one of the project where we need to create Application form with multiple repeating sections.
I have tried smart marker and it is working fine for when we have single element per row or listing a nested object in flat table manner.
But I am not able to create template / use smart marker when we have to show the nested object’s data spanning multiple rows.
For example, we have an information about an organization. This organization has multiple directors and every director has multiple reportees.
The organization information is getting filled properly using excel template and smart markers,
image.png (10.8 KB)
The issue is that every reportee has an information associated which will span across 2 / 3 rows and every director has multiple such reportees. In this case the data is getting repeated for all the directors first and the for all the reportess.
I am attaching Template - Form_ABC_1_Template.xlsx,
Actual output - Form_ABC_1_Filled.xlsx
and Expected output - Form_ABC_1_Filled - Expected.xlsx
Can you please help me with this?
Smart markers - Excel Repeated section issue.docx (89.0 KB)
Please provide the source code, preferably a runnable project, so that we can quickly identify the issue. Thank you!
asposepoc.zip (10.9 KB)
Hello PFA zip file containing the source code Regards
The expected out put is as -
Expected Output.png (29.6 KB)
If you need to treat an area as a whole. You can create a named range. For your template file, you can use the following code to set the range:
Worksheet templateDataSheet = filledWorkbook.getWorksheets().get(0);
And then Then set the line by line replacement to false:
You can refer to the following document. Sorry, this is a .NET document and there are currently no Java related documents available. We will add it later.
Thank you I tried your solution and it works very well if specify the child data in a single row.
It does not work well when we want to display child information across the multiple rows -
In my case the reportee information spans across two rows …
And even if the director grouping is happening correctly with _CellsSmartMarkers… the repotrees are information is getting printed line by line.
What I mean is the data is getting displayed as below -
image.png (29.8 KB)
The expected output is -
Expected Output.png (29.6 KB)
Through sample file testing, we can observe that there are still issues with the smart marker in multi lines area.
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-45708
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.
Thank you @John.He for the update.
Will I get a notification once the issue is resolved ?
Yes, once we resolve this issue, we will notify you here.
Hello @John.He , @xinya.zhu ,
I have another observation and I need help.
When we have multiline area (“_CellsSmartMarkers”) as the last section in the template everything works properly.
BUT if we have some lines or some other data in the template after the multiline area, it does not get rendered in the output file after we call designer.setLineByLine(false); designer.process(true);
For example -
image.png (25.6 KB)
Please refer to the area marked in red
Out that we get -
image.png (20.6 KB)
Please notice that the List of Directors is not getting rendered / printed.
Is is possible that the rows are not getting inserted (shifted down) after designer processes the multiline area? (Which means the list of directors section is getting overwritten)
The Expected output -
image.png (26.4 KB)
Can you please let me know if I am missing any code / logic after creating a named range (“_CellsSmartMarkers”) and setting designer.setLineByLine(false); ?
We need this functionality as in our case the requirements is such that the form may contain multiple sections (multiline areas) with some data / lines in between
Thank you for your feedback and detailed information. The code is correct, and there are currently some issues with non line by line replacement. Also, please check the correspondence between the template and the data. We will further investigate and fix the issue. Once there are updates, we will notify you immediately.
As we can not know how many rows should be inserted when processing not line by line, we did not call inserting rows for performance. We will fix issue later
We have fixed your metioned issue with inserting rows.
But the data “ID” and “Allowed to File GST” of the “Reportee” are split to differect sections, is it your excepted?
“ID” and “Allowed to File GST” are part of Reportee (one or many) under each director so it should be part of same “Directors” Section. You may think “Reportee” as a sub section under “Director” Section
image.png (26.7 KB)
Please refer to the image of expected behavior where there are two director section with ID 111 and 222 respectively. And reportees aaa, bbb and ccc fall under Director 111,
Reportess “eee” and “fff” fall undee director with id 222
Also please let me know how / when I can get the version with the fix of row insertion.
Thank you !
Thank you for your feedback and detailed information. We will further investigate and fix the issue. Once there are updates, we will notify you immediately.
@John.He , @simon.zhao ,
Thank you for your quick response … In the meanwhile Can I get the version of the row insertion fix that @simon.zhao mentioned ?
Sorry, but we only provide interim releases and fixes to paid support users. You will need to wait a little longer as the new enhancements and fixes regarding Smart Markers will be included in our upcoming release (Aspose.Cells v23.12), which is scheduled for release in the second week of December 2023. Please stay tuned!
It’s hard for us to know how many rows should be processed together.Should we define named ranges for each level? We need time to check how to support your need.
We will provide a hotfix if we can support gathering“ID” and “Allowed to File GST” are part of Reportee later.
Thank you for reverting back…
It would be great if you can implement named ranges for each level… but it is not on top priority.
Therefore you can put the sub section (Reportee) implementation at lower priority.
Currently it would be great if we can get a fix that inserts necessary number rows after processing the “Directors” section.
(I am referring to your responses -)
Thank you for your feedback. We are already implementing your requirements and hope we can fix it soon. Once there are updates, we will notify you immediately.