JSON and Smart Markers

Aspose.Cells Product Family
1

Hello - I want to import a JSON string from a file and use it as a data source in Smart Markes. The JSON file contains the following example:
{“name”:“Doe”, “age”:36, “car”:“None”}

First question - how can I import the JSON into memory - means a temporary structure and not into the opened excel workbook. The following code shows this for a workbook, but I do not want to change my workbook:
string jInput = File.ReadAllText(dataDir + “Test.json”);
JsonLayoutOptions jOptions = new JsonLayoutOptions();
jOptions.IgnoreArrayTitle = false;
jOptions.IgnoreObjectTitle = false;
jOptions.ArrayAsTable = true;
JsonUtility.ImportData(jInput, worksheet.Cells, 0, 0, JOptions);
-> What to use here for “worksheet.Cells, 0, 0” when I don’t want to change my excel.

Second question - I want to use the temporary structure from above (“worksheet.Cells, 0, 0”) as a data source for the smart markers. The following code shows how I set the data source, but not sure, what I have to use for “???”:
myReport.SetDataSource("???");
myReport.Process(true);

Third question - how should the Smart Marker look in excel. For the above example I thought I should use “&=name” is this right? And what do the types of Smart Markes from your documentation mean:
&=DataSource.FieldName
&=[Data Source].[Field Name]
&=$VariableName
&=$VariableArray

And the last question - how do I handle the following JSON strings:
{“name”:“Doe”, “age”:36, “car”:{“type”:“Honda”, “color”:“Blue”}}

[{“address”:“AGENT.OBJECTS.myInt32”,“description”:{“de”:“myInt32”,“en”:“myInt32”},“status”:0,“value”:2},{“address”:“AGENT.OBJECTS.myInt16”,“description”:{“de”:“myInt16”,“en”:“myInt16”},“status”:0,“value”:2},{“address”:“AGENT.OBJECTS.myInt16”,“description”:{“de”:“myInt16”,“en”:“myInt16”},“status”:0,“value”:3},{“address”:“AGENT.OBJECTS.myInt32”,“description”:{“de”:“myInt32”,“en”:“myInt32”},“status”:0,“value”:3}]

Thanks for your help.

2

@DESO,

1). You may try to use a new (empty) Workbook and import JSON data into its default worksheet cells for your needs. Alternatively, you may directly import JSON data into Aspose.Cells’ object model via Workbook constructor and then save to streams via Workbook.Save method or other relevant overloads.
2). and 3). You can export to fill a DataTable or arrays (all in the memory) which will behave as datasource (see the document on how to export datatable from Excel spreadsheet) and then use Smart Markers feature accordingly. See the detailed document on Smart Markers for your reference. Sample marker can be:
&=Table1.Field1
4). Could you elaborate on it and provide more what you want to achieve.

3

Ok, got it working with the first JSON, but with

{“name”:“Doe”, “age”:36, “car”:{“type”:“Honda”, “color”:“Blue”}}

I have trouble. If I want to access
&=myData.name or &=myData.age
In the template it works.

But I am not able to use
&=myData.car.type
Here happens nothing, the marker remains untouched in the excel.

What I am doing wrong?

4

@DESO
Please use Json prasers( NewtonSoft.Json) to parse json source to nested data, then set them as the data source now.

5

Ok, I have tried this, I used JSON.parse from the Library and when I write this to the console it looks like expected. But how do i pass this as datasource? My Code:

string jsonString = File.ReadAllText(jsonInput);
JObject jsonObject = JObject.Parse(jsonString);

Console.WriteLine(jsonString);
Console.WriteLine(jsonObject);

myReport.SetDataSource(“Report”, jsonObject);

myReport.Process(true);

This produces:

{“name”:“Franz”, “age”:96, “car”:{“brand”:“Volvo”, “color”:“blue”}}
{
“name”: “Franz”,
“age”: 96,
“car”: {
“brand”: “Volvo”,
“color”: “blue”
}
}

But when I put &=Report.name in the Template I get the strings “name”, “age”, “car” each in one cell and when I use &=Report.age or &=Report.car.brand nothing happens.

BTW - Thanks for the help!

6

@DESO,

Could you please create a standalone VS.NET console application (complete source code and reference libraries without compilation errors), zip the project with all the resource files and post us. We will check your issue soon.

PS. please exclude Aspose.Cells.Dll in the zipped archive to minimize the size of the project.

7

Here is the project, Aspose DLL and Lic excluded. As it was not clear which version you need, the current version is the one with the NetwtonSoft JSON.parse, the one which is commented out is the one with the JSON import from Aspose.

Thanks
AsposeTest.zip (949.1 KB)

8

@DESO,

Thanks for the template Excel file, JSON data file and sample.

Please notice, after an initial test, I am able to reproduce the issue as you mentioned by using your template file and JSON data. I have logged a ticket with an id “CELLSNET-52306” for your issue. It is quite possible that either your JSON data is not proper when parsed or smart markers are not rightly inserted into the template file. We need to evaluate your issue thoroughly. We will do it soon.

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

9

Thanks - I will wait with the further evaluation until I get feedback, Smart Markers with JSON are the functions we need from the aspose.cells library.

Please give us feedback as soon as possible - also contact me if you need further information.

Thanks

10

@DESO,

Sure, we will keep you posted with latest updates (once available).

11

@DESO
JObjct implement ICollection,we can not simply process JObject as List not a class.so please input JArray as data source of smart marker and arrange your json file as array format.

Please checkjson+smart.zip (7.6 KB)

12

Thanks, but this code does not work. In the result are no markers updated - it looks like the source excel.

Please can you check this?

13

@DESO,

You are right as I tested using the sample code and new template file with (updated) JSON data shared by @simon.zhao. The markers are not processed at all. We have reopened your ticket (logged earlier as “CELLSNET-52306”) now. We will be looking into it soon.

14

@DESO,

It looks like you are using some older version of Newtonsoft.Json. Previously I tested using v9.0.0 which does not work same as yours. I now tested with v13.0 and it works fine. Please find attached the new zipped archive containing the sample (template file, JSON data and sample code), it works fine with newer version of Newtonsoft.Json as I tested.
json+smart1121.zip (7.4 KB)

15

Sorry - for that, now it works. Thanks a lot.

One last question - is this the preferred way to get a JSON prepared for Smart Markers or is there another way we should use? And is all the functionality of the Smart Markers usable with this method or are there some limitations?

Thanks a lot!

16

@DESO,

Good to know that it works fine now.

The proposed approach is better to parse JSON data for Smart Markers. We will also check if you can use other way around. You can use this method to use maximum functionality of Smart Markers.

17

Ok - the first test is ok - but there are some problems with formats. When I have a number in the JSON and set the cell in excel also to a number it gets inserted as text saved number. Also, a problem with date formats. A string “2021-11-20T08:45:00.0010000Z” gets formatted as some kind of date (in my case 20.11.2021 08:45:00) but when I format the cell as date and change the format it gets ignored.

Seems that all the things are seen as strings - and the date gets converted as some kind of string.

18

Update: when I open the sheet then in a excel mark a cess and press enter it gets the right format. So I have to enter each cell and press enter.

19

@DESO,

Could you please try using “numeric” parameter while specifying Smart Markers into the template file sheet cell(s), this will convert to numeric data (if appropriate). See the example marker for your reference:
&=myData.age(numeric)

20

Ok - this works for all numbers - perfect.
But it also works for the date -> This is strange for me -> Why?

And one feature i am missing or cannot find: I want to modify the value I write in a cell. Example:

  • The cell is a string and the Smart Marker in it is &=Report.Content
  • The datasource reports “AAAA.BBBB.CCCC” for Report.Content
  • I want to get not the whole string in the cell, I want to use a excel formula on it to get the substring “BBBB” out if it and put it in the cell

This is a simple example, what I want are formulas defined in the Smart Marker like

  • &=Report.Content(formula:[=MID(<Report.Content>, 6, 4)])

that uses the value as input and put the result in this cell. I know, I can do this with a helper sheet, but this is not a nice solution.

Hope that works - then I can buy and start with the project.

Thanks!