Aspose.Cell for java Using SmartMarkers And Json DataSource

abdullah240 · November 10, 2023, 4:19pm

Hi Aspose Team,

We are using Aspose words (version:23.8) java and now we are evaluating the Aspose cell for java product for a use case.

We would want to have a an xlsx or xls template using smartMarkers feature.

How can we have an excel template using smart markers and passing the data to those cells using one json datasource.

We have the below:
Object, Array, String values in that json data and see the below screenshot of our excel template.
image.png (8.8 KB)

This is the java code we have which is not working.

Workbook workbook = new Workbook(dataDir + “TestSmartMarkers.xlsx”);

    String data = "{\n" +
            "    \"array\": [\n" +
            "        \"English\",\n" +
            "        \"Arabic\",\n" +
            "        \"Hindi\",\n" +
            "        \"Urdu\",\n" +
            "        \"French\"\n" +
            "    ],\n" +
            "    \"var\": \"This is a sentence using one place holder\",\n" +
            "\"arrObj\": [\n" +
            "    {\n" +
            "        \"name\": \"John Doe\",\n" +
            "        \"age\": \"27\"\n" +
            "    },\n" +
            "    {\n" +
            "        \"name\": \"Jane Doe\",\n" +
            "        \"age\": \"27\"\n" +
            "    }\n" +
            "]"+
            "}";
    JSONObject jsonDataSource =
            new ObjectMapper().readValue(data, JSONObject.class);
    WorkbookDesigner designer = new WorkbookDesigner();
    designer.setDataSource("ds", jsonDataSource);
    designer.process();
    workbook.save(dataDir + "UsingNestedObjects_out.xlsx");

How can we achieve this?

Thanks

amjad.sahi · November 10, 2023, 5:55pm

@abdullah240,

We suggest you to kindly see another thread, i.e., JSON and Smart Markers with complete details and examples/sample projects for your reference.

Then you can create and devise JSON data file and template file (with Smart Markers) accordingly by yourselves.

Let us know if you still have any issue.

abdullah240 · November 10, 2023, 6:11pm

@amjad.sahi

I did find this link before this request and did not find it helpful as most of the documentation was for .Net version.

I also did not see how the json data was set as the data source and using smart markers on template.

Can you help give us solution for java using the code and template I have shared?

I would appreciate any help you can provide.

Thanks

abdullah240 · November 10, 2023, 6:51pm

@amjad.sahi

I did find this link before this request and did not find it helpful as most of the documentation was for .Net version.

I also did not see how the json data was set as the data source and using smart markers on template.

Can you help give us solution for java using the code and template I have shared?

I would appreciate any help you can provide.

Thanks

amjad.sahi · November 11, 2023, 9:11am

@abdullah240,

I think NewtonSoft JSON is designed for the .NET platform and it is not there in Java. You may try some other options for your needs:
1). Directly import JSON data into Aspose.Cells’ object model via Workbook constructor and then save to file/streams via Workbook.Save method or other relevant overloads.
2). Import JSON data directly into Excel spreadsheet. See the data importing option in the document for your reference.

Hope, this helps a bit.

abdullah240 · November 13, 2023, 3:48pm

@amjad.sahi

This is helping by giving us different options.

Is there a way to combine this with smart markers?

Can we have smart markers that use the Json file as the data source, and only data will be populated into those smart markers?

We have cases where we might get this Json file and we expect to format the cells or use some excel functions.

This seems like it will just put the data in the excel without allowing us to have a template.

Thanks

amjad.sahi · November 13, 2023, 5:16pm

@abdullah240,

Apparently, this is possible in .NET but may not be in Java. Anyways, we will further evaluate it and get back to you.

The suggested option 2) will work even if you are using a template Excel file and you need to import JSON data into an existing worksheet cells.

simon.zhao · November 14, 2023, 7:38am

@abdullah240
Now it’s not support setting json as data source of smart marker.
We have log it with id CELLSJAVA-45705. But it is not esay to implement it .
Now please use Java bean list or implement ICellsDataTable by yourself.
See document : ICellsDataTable

abdullah240 · November 15, 2023, 5:16pm

@amjad.sahi & @simon.zhao

Thanks for the updates, how long should we expect to have this feature available?

To give you more context on what we are trying to achieve see below to see how you can help.

We are currently using the licensed Aspose.word for java product to generate pdf from word template files. We have a generic way where we can get data as json data source and we just create a template accordingly which allows us to have a generic way to keep generating documents. We are using the aspose for word placeholders to place that data in that template where we need.

Is there any way to achieve that so any excel I can generate just by creating the template and not updating the java code.
If not what is the closest solution for this?

amjad.sahi · November 15, 2023, 5:51pm

@abdullah240,

As we told you that currently Aspose.Cells for Java does not support JSON as datasource for markers. Since it is a complex feature, so it might take time. Once we have an ETA on it, we will let you know here.

Also, thanks for sharing your scenario. We will evaluate and get back to you soon.

johnson.shi · November 16, 2023, 2:11am

@abdullah240
Maybe you can check and confirm whether the data parsed and imported into cells is the expected data structure(apis mentioned by @amjad.sahi at this reply).

If it is not the expected data, we are afraid currently you have to parse the json data into proper object(ICellsDataTable or other java class) by yourself and then set it as the data source for smart markers. And because of the variety of json data and user’s requirements, maybe we cannot provide one unified way to parse the data to fit all user’s requirements.

If the imported data by those apis is the expected one, as an workaround we think you may import the json data into a temporary workbook in this way firstly, and then export data from cells by apis Cells.Export…(). At last you may set the exported data as data source for smart markers to process your templates.

amjad.sahi · December 6, 2023, 7:59am

@abdullah240,

This is to inform you that your issue (logged earlier as “CELLSJAVA-45705”) has been resolved. The fix will be included in our upcoming release (Aspose.Cells v23.12) that we plan to release in the next week. You will be notified when the new version is published.

abdullah240 · December 6, 2023, 5:02pm

@amjad.sahi

Thanks for the update and looking forward to this smart marker feature.

One question on the same topic.

Can we create pie charts, graphs, tables in excel with smart markers using the data from Json data source?

Just like with Aspose.Words where we can pass array in that Json data source to create pie chart or graphs…

Thanks

amjad.sahi · December 6, 2023, 6:06pm

@abdullah240,

Yes, when you will use upcoming Aspose.Cells v23.12 (which includes the feature, i.e., support json as data source of smart marker), you could create charts and tables based on the data source range having smart markers.

simon.zhao · December 7, 2023, 1:27am

@abdullah240
Now it’s supported that directly setting array as data source to expand data source of the chart.

We have to check whether we support parsing Json Array to expand chart.

And it’s better you can share a template file, json and expected file here, we will check them soon.

aspose.notifier · December 13, 2023, 11:03am

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

abdullah240 · December 13, 2023, 3:51pm

Hi Aspose Cell team,

Thanks for letting us know on the feature release.

How do we implement json data source to cell smart markers, see sample code below:

Workbook workbook = new Workbook(dataDir + "TestSmartMarkers.xls");

	String data = "{\n" + "    \"array\": [\n" + "        \"English\",\n" + "        \"Arabic\",\n"
			+ "        \"Hindi\",\n" + "        \"Urdu\",\n" + "        \"French\"\n" + "    ],\n"
			+ "    \"var\": \"This is a sentence using one place holder\",\n" + "\"arrObj\": [\n" + "    {\n"
			+ "        \"name\": \"John Doe\",\n" + "        \"age\": \"27\"\n" + "    },\n" + "    {\n"
			+ "        \"name\": \"Jane Doe\",\n" + "        \"age\": \"27\"\n" + "    }\n" + "]" + "}";

	WorkbookDesigner designer = new WorkbookDesigner();
	designer.setDataSource("ds", data);
	designer.setWorkbook(workbook);
	designer.process();
	workbook.save(dataDir + "UsingNestedObjects_out.xls");

Excel file:
image.png (6.1 KB)

This above implementation is not working.

What is the correct way to achieve this.

Thanks

amjad.sahi · December 13, 2023, 5:43pm

@abdullah240,

We will get back to you with details and samples for your needs.

simon.zhao · December 14, 2023, 2:23am

@abdullah240
We have reopened this issue.
Sorry for my mistake.
We will check it soon.

simon.zhao · December 14, 2023, 3:26am

@abdullah240
Now please try the attached template file
TestSmartMarkers.zip (7.2 KB)

and following codes with the latest version 23.12:

            Workbook workbook = new Workbook(dir  + "TestSmartMarkers.xlsx");

            String data = "{\n" + "    \"array\": [\n" + "        \"English\",\n" + "        \"Arabic\",\n"
                    + "        \"Hindi\",\n" + "        \"Urdu\",\n" + "        \"French\"\n" + "    ],\n"
                    + "    \"var\": \"This is a sentence using one place holder\",\n" + "\"arrObj\": [\n" + "    {\n"
                    + "        \"name\": \"John Doe\",\n" + "        \"age\": \"27\"\n" + "    },\n" + "    {\n"
                    + "        \"name\": \"Jane Doe\",\n" + "        \"age\": \"27\"\n" + "    }\n" + "]" + "}";
           
            WorkbookDesigner designer = new WorkbookDesigner(workbook);
            designer.setJsonDataSource("ds", data);
            designer.setLineByLine(false);

            designer.process();
            workbook.save(dir + "dest.xlsx");

But there is an issue about parsing JsonArray, we will fix it soon.