Free Support Forum - aspose.com

Smart Markers rendering two dimensional arrays

I’ve got a json data source and the root object has an array of accounts. I’d like to render each account as its own row. The first couple of columns would be the code and title associated with the account. Then I want to include an array of monthly expenses for each account. The monthly expenses may be of an arbitrary size and thus cannot be represented as twelve distinct properties.

I’ve been trying to achieve this with a smart marker like this:
&=sourceData.accounts.monthlyExpenses.value(horizontal, numeric)

However, this renders all the monthly expenses for all accounts in a single row
image.png (9.5 KB)

My desired output would look something like this:
image.png (7.4 KB)

I’ve attached the example designer spreadsheet and json data.
TwoDimensionalTemplate.zip (8.6 KB)

Please note that I’m attempting to do this exclusively with smart markers and a single json data source. the c# app is completely ignorant of the content of the Designer Spreadsheet and the json data - it just loads them from disk, calls process and saves the output.

@michaelrichards,

Thanks for the template file, json data and screenshots.

I checked your template file and found you are mixing horizontal and vertical markers while grouping data which is a bit complex. Could you also provide your code segment (runnable), we will check it soon. Also, provide your expected Excel file containing your desired data in order.

The json data is in ExcelFullDotNetPoc.App\ExcelFullDotNetPoc.App\Data\TwoDimensionalExample.json

The excel data is in ExcelFullDotNetPoc.App\ExcelFullDotNetPoc.App\Templates\TwoDimensionalTemplate.xlsx.
The ‘Monthly Expenses Summary’ worksheet is where my smart markers reside. I have added an ‘Expected Results’ worksheet to that showing how I would like the data to be laid out.

The logic, such as it is for loading the json data and template from disk, setting it as the data source and calling process on the worksheet is in WorkbookBuilder.cs.

The output file is dumped into the bin\debug directory and is named output-TwoDimensionalTemplate.xlsx

Please let me know if there’s anything else I can provide to help clarify the request. Thank you!

ExcelFullDotNetPoc.App.zip (5.6 MB)

@michaelrichards,

Thanks for the sample project with template file and JSON data file.

After an initial test, I am able to reproduce the problem as you mentioned by using your sample project with your template file and json data. This may not be an issue rather limitation with Smart Markers feature regarding grouping data horizontally. Anyway, I have logged a ticket with an id “CELLSNET-47270” for your issue. We will check the possibility if your task can be done in Smart Markers,

Once we have an update on it, we will let you know.

@michaelrichards,

We evaluated your issue in details.
We could not support nested arrays for smart markers now. We recommend you to kindly implement ICellsDataTable interface to make the nested data as simple datatable by yourself, then set ICellsDataTable as data source if it works for your needs.