I am trying to export json data into excel by using smart marker in java

Aspose.Cells Product Family
,
1

In excel I am using tags like
&=c8.node.DefaultRecipe.CurrentRevision.BTY_ID_String
&=c8.node.DefaultRecipe.CurrentRevision.BTY_Expert_String

This tags are working fine and I am getting desired values for this tags

But my now uses case is I want to access “BTY_Source_string” and “BTY_Ingredient_PurposeOfUse_enumlist” which is present inside “LibraryItem” and it is array of “Ingredients”,

So tags that I used are
1: &=c8.node.DefaultRecipe.CurrentRevision.RecipeFoodLabelRevision.Ingredients.LibraryItem.BTY_Source_string
2:
&=c8.node.DefaultRecipe.CurrentRevision.RecipeFoodLabelRevision.Ingredients.LibraryItem.BTY_Ingredient_CAS1_string

So in above case it is printing only first object value of “BTY_Source_string” and “BTY_Ingredient_PurposeOfUse_enumlist” ,
I want to print full list how I can print whole list in this case ?
Please refer below JSON that I am using.
Please send me some links so that I can refer and complete my requirnments.

{
“node”: {
“DefaultRecipe”: {
“RecipeFoodLabelRevision”: {
“Ingredients”: [
{
“LibraryItem”: {
“BTY_Source_string”: “XXX”,
“C$Name”: “XXX”,
“BTY_Ingredient_PurposeOfUse_enumlist”: [
“XX”
],
“BTY_Ingredient_CAS1_string”: “XX”
},
“RelativeQty”: 0.878
},
{
“LibraryItem”: {
“BTY_Source_string”: “XXX”,
“C$Name”: “XX”,
“BTY_Ingredient_PurposeOfUse_enumlist”: [
“XX”
],
“BTY_Ingredient_CAS1_string”: “XX”
},
“RelativeQty”: 0.121288
},
{
“LibraryItem”: {
“BTY_Source_string”: “XX”,
“C$Name”: “XX”,
“BTY_Ingredient_PurposeOfUse_enumlist”: [
“XX”
],
“BTY_Ingredient_CAS1_string”: “XX”
},
“RelativeQty”: 0.10768799999999998
},
{
“LibraryItem”: {
“BTY_Source_string”: “XX”,
“C$Name”: “XX”,
“BTY_Ingredient_PurposeOfUse_enumlist”: [
“XXXT”
],
“BTY_Ingredient_CAS1_string”: “XX”
},
“RelativeQty”: 0.104712
}
]
}
}
}
}
2

@Ajinkya281991
You can refer to the following document on how to use smart markers.

If you still have questions or confusion, please provide your sample files, expected result files, and executable sample code. You can compress them into zip format and upload them here. We will check them soon.

3

I have gone through all above links but no solution how can I access nested arrays objects
My sample code

      String data = "template-excel-002-data.json";
      String fileName = "src/test/resources/input/templates/valid-excel-002.xlsx";
      final WorkbookDesigner designer = new WorkbookDesigner();
      designer.setWorkbook(new Workbook(fileName));
      String[] smartMarkers = designer.getSmartMarkers();
      System.out.println("smart marker: "+Arrays.asList(smartMarkers));
      designer.setJsonDataSource("c8", testFileUtils.readJsonFile("input/data", data));

      designer.process();
      designer
          .getWorkbook()
          .save("src/main/resources/test-smart-marker-output.xlsx", SaveFormat.XLSX);

Json data

{
“node”: {
“DefaultRecipe”: {
“RecipeFoodLabelRevision”: {
“Ingredients”: [
{
“LibraryItem”: {
“BTY_Source_string”: “XXX”,
“C$Name”: “XXX”,
“BTY_Ingredient_PurposeOfUse_enumlist”: [
“XX”
],
“BTY_Ingredient_CAS1_string”: “XX”
},
“RelativeQty”: 0.878
},
{
“LibraryItem”: {
“BTY_Source_string”: “XXX”,
“C$Name”: “XX”,
“BTY_Ingredient_PurposeOfUse_enumlist”: [
“XX”
],
“BTY_Ingredient_CAS1_string”: “XX”
},
“RelativeQty”: 0.121288
},
{
“LibraryItem”: {
“BTY_Source_string”: “XX”,
“C$Name”: “XX”,
“BTY_Ingredient_PurposeOfUse_enumlist”: [
“XX”
],
“BTY_Ingredient_CAS1_string”: “XX”
},
“RelativeQty”: 0.10768799999999998
},
{
“LibraryItem”: {
“BTY_Source_string”: “XX”,
“C$Name”: “XX”,
“BTY_Ingredient_PurposeOfUse_enumlist”: [
“XXXT”
],
“BTY_Ingredient_CAS1_string”: “XX”
},
“RelativeQty”: 0.104712
}
]
}
}
}
}

template file is present in excel and smart marker tags are

1: &=c8.node.DefaultRecipe.CurrentRevision.RecipeFoodLabelRevision.Ingredients.LibraryItem.BTY_Source_string
2:
&=c8.node.DefaultRecipe.CurrentRevision.RecipeFoodLabelRevision.Ingredients.LibraryItem.BTY_Ingredient_CAS1_string

Q2: Another question is I want to access simple array of string eg. languages given in below json

Json data:

{
  "node": {
    "DefaultRecipe": {
      "C$Name": "ROUGE DIOR INTER 2020 743",
      "languages": ["English", "Arabic", "Hindi", "Urdu", "French"],
                                      }
                    }
}

I tried smart markers
&=$c8.node.DefaultRecipe.languages
But no data is printing. How can I print languages ?

Q3: Is it possible to add images in base64 format in JSON data and bind those in template ?

4

@Ajinkya281991
By testing on the latest version v24.7 using the detailed information and sample code provided by you, we found that NullPointerException occurs when importing JSON data to Excel using smart markers. Please refer to the attachment. samplefiles.zip (6.5 KB)

The sample code as follows:

String data = filePath + "template-excel-002-data.json";
String fileName = filePath + "valid-excel-002.xlsx";
final WorkbookDesigner designer = new WorkbookDesigner();
designer.setWorkbook(new Workbook(fileName));

String[] smartMarkers = designer.getSmartMarkers();
System.out.println("smart marker: "+Arrays.asList(smartMarkers));
//designer.setJsonDataSource("c8", testFileUtils.readJsonFile("input/data", data));

String jsonData = new String(Files.readAllBytes(Paths.get(data)));
System.out.println(jsonData);
designer.setJsonDataSource("c8", jsonData);

designer.process();
designer.getWorkbook().save(filePath + "test-smart-marker-output.xlsx", SaveFormat.XLSX);

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

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.

About how to use image markers, please refer to the following document.

5

Thanks for you quick reply we appreciate it.

What is the solution for Q2 ?
Json data as input

{
"node":{
"simpleArray": [1,2,3,4,5]
}
}

I was unable to print this by using smart markers ,
&=c8.node.simpleArray
&=$c8.node.simpleArray

Q3:
Regarding images we have gone through that links that you provide , our question is it possible to have base64 image in JSON payload and bind directly in template.
eg. JSON payload as input

{
"node":{
    "image-key" : "data in base 64"
    }
}

And what we want in template is
&=c8.node.image-key(Picture:FitToCell)

Is this supported ?

6

@Ajinkya281991,

We will soon evaluate your Q2 and Q3 and get back to you with more details.

7

@Ajinkya281991
By creating sample files and testing with the following sample code, we can reproduce your issues. Please check the attachment. result.zip (13.7 KB)

At present, we do not support array data and base64 picture data when importing json data to excel using smart markers.

String data = filePath + "sample.json";
String fileName = filePath + "sample.xlsx";
final WorkbookDesigner designer = new WorkbookDesigner();
designer.setWorkbook(new Workbook(fileName));

String[] smartMarkers = designer.getSmartMarkers();
System.out.println("smart marker: "+Arrays.asList(smartMarkers));

String jsonData = new String(Files.readAllBytes(Paths.get(data)));
System.out.println(jsonData);
designer.setJsonDataSource("c8", jsonData);

designer.process();
designer.getWorkbook().save(filePath + "out_java.xlsx", SaveFormat.XLSX);

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-46081: Support array data when importing json data to excel using smart markers
CELLSJAVA-46082: Support base64 picture data when importing json data to excel using smart markers

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.

8

Thank you for response

I am trying to insert image with byte[] below is my code , please let me know what I am missing bcoz image is not coming in output excel

Code that I trying to insert image from java

  Path path = Paths.get(imagePath);
    byte[] image = Files.readAllBytes(path);
    designer.setDataSource("node.smallImage", image);
 designer.process();
      designer
          .getWorkbook()
          .save("src/main/resources/test-smart-marker-output.xlsx", SaveFormat.XLSX);

smart marker tag in template excel file

&=node.smallImage(Picture:FitToCell)

Please let me know what I am doing wrong here ?

9

@Ajinkya281991
According to your requirements, we have created a sample file and tested it using the following sample code, and we can obtain the correct results. Please refer to the attachment. result.zip (17.8 KB)

//Get the image
Path path = Paths.get(filePath + "1.png");
byte[] photo1 = Files.readAllBytes(path);

//Create a new workbook and access its worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);

worksheet.getCells().get("A1").putValue("Photo");

worksheet.getCells().get("A2").putValue("&=Node.Photo(Picture:FitToCell)");


//Create a workbook designer
WorkbookDesigner designer = new WorkbookDesigner(workbook);

ArrayList<Node> nodes = new ArrayList<Node>(); 
nodes.add(new Node(photo1));

//Set the data source and process smart marker tags
designer.setDataSource("Node", nodes);
designer.process();

//Save the workbook
workbook.save(filePath + "out_java.xlsx", SaveFormat.XLSX);

Hope helps a bit.

10

Thank you for your quick response,

But the smart marker tags that I get is dynamic eg. given below

&=node.photo(Picture:FitToCell)
&=node.smallImage(Picture:FitToCell)
&=node.Images.smallImage(Picture:FitToCell)

It could be any variable name followed after node.-- , So I cannot create a POJO class , I get smart marker tags dynamic So I was trying below code where I can give smart marker tag with byte[] array

designer.setDataSource("node.smallImage", image);

Can you please give me solution for such use case.
Thanks again

11

@Ajinkya281991
If you want to directly set image data as the data source, please refer to the following example code.

The sample code as follows:

//Get the image
Path path = Paths.get(filePath + "1.png");
byte[] photo1 = Files.readAllBytes(path);

//Create a new workbook and access its worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.getWorksheets().get(0);

worksheet.getCells().get("A1").putValue("Photo");

worksheet.getCells().get("A2").putValue("&=$node.smallImage(Picture:FitToCell)");


//Create a workbook designer
WorkbookDesigner designer = new WorkbookDesigner(workbook);

//Set the data source and process smart marker tags
designer.setDataSource("node.smallImage", photo1);
designer.process();

//Save the workbook
workbook.save(filePath + "out_java.xlsx", SaveFormat.XLSX);

Hope helps a bit.

1 Like
12

Thanks it worked with dynamic tags

&=$node.smallImage(Picture:FitToCell)
Are their in more flags for picture parameters similar to what we have in Aspose word Linq Report Engine , like keepRatio , fitHeight

13

@Ajinkya281991,

Good to know that it works for dynamic tags.

In Aspose.Cells, there are certain parameters regarding width, height and scale for image markers and you may make use of them accordingly. Please see the document with details for your reference.
https://docs.aspose.com/cells/net/using-smart-markers/#image-markers

14

Thanks for your response,

If we have smart marker tag in excel and the same key is not present in json input file , then it throws NULL error , In linq reporting we are using ‘?’ in such cases to handle null values.
How to handle null checks in excel please give us some solution on this

15

@Ajinkya281991
By creating sample files with the provided detailed information for testing, we can reproduce the issue. When the smart marker tag is not present in the JSON input file, NullPointerException occurred. Please check the attachment. sample.zip (6.3 KB)

The sample code as follows:

String data = filePath + "sample.json";
String fileName = filePath + "sample.xlsx";
final WorkbookDesigner designer = new WorkbookDesigner();
designer.setWorkbook(new Workbook(fileName));

String[] smartMarkers = designer.getSmartMarkers();
System.out.println("smart marker: "+Arrays.asList(smartMarkers));

String jsonData = new String(Files.readAllBytes(Paths.get(data)));
System.out.println(jsonData);
designer.setJsonDataSource("c8", jsonData);

designer.process();
designer.getWorkbook().save(filePath + "out_java.xlsx", SaveFormat.XLSX);

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

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.

16

@Ajinkya281991
We are pleased to inform you that your four issues has been resolved. The fixes will be included in the upcoming release (Aspose.Cells v24.9) which we plan to release in the first half of September 2024. We will notify you when the next release is released.

17

Awesome news Thank you. :grinning:

There are some of more uses case that we are trying to implement using JSON data with smart marker

Q1: Date format
our use case: In Linq Report engine we can format UTC date (2024-08-09T07:34:41Z) using the string format (dd/MM/YYYY) in template itself , is this possible using smart marker if yes please let us know how do this ?

Eg.

<<[expression]:"dd/MM/YY">>

Q2: For RepeatDynamicFormula
In template smart marker

&=c8.node.dynamic.x	&=c8.node.dynamic.y	&=&=A{r}+B{r}

Json data source

{
"node"{
"dynamic": [{
  "x": 1,
  "y": 20
},{
  "x": 2,
  "y": 30
}],
}
}

It throws and error

java.lang.RuntimeException: java.lang.NullPointerException: Cannot read the array length because "<local19>.y" is null

Are we doing some thing wrong here or is this an issue ?

Q3;
Our use case: In Linq report engine we can use conditional rendering of a data inside a template using if tag

<<if[expression]>>YES<<else>>NO<</if>>

, is there similar way of handling conditional wit smart markers ? If yes please let us know

Thanks again for your quick support…

18

@Ajinkya281991

Please set number format in the Excel template file.
But there is an issue that did not support parsing the UTC date string value when processing smart markers. We have logged it with inner issue id CELLSJAVA-46087

It’s not supported. We will support it later on. We have logged a ticket with an id “CELLSJAVA-46086” for it.

Please use repeated formulas to support this feature.

19

@Ajinkya281991
24.8.1.zip (13.3 KB)

Please check above attached zip which contains template and result files. Do they fit your need?

BTW if you are using Linq Report engine, please use simple list as data source.

20

Hello ,
We have tried using basic list for dynamic formula, .
Please refer below code for more details

@Test
void generatedDocumentWithRepeatFormulas() {
  try {
 
    String fileName = "src/test/resources/input/templates/valid-excel-003.xlsx";
    final WorkbookDesigner designer = new WorkbookDesigner();
    designer.setWorkbook(new Workbook(fileName));
    List<Map<String, Object>> dataList = new ArrayList<>();

    // Create the first entry
    Map<String, Object> entry1 = new HashMap<>();
    entry1.put("x", 1);
    entry1.put("y", "2024-08-09T07:34:41Z");

    // Create the second entry
    Map<String, Object> entry2 = new HashMap<>();
    entry2.put("x", 2);
    entry2.put("y", "2024-08-09T07:34:41Z");

    // Add the entries to the ArrayList
    dataList.add(entry1);
    dataList.add(entry2);
    for (Map<String, Object> entry : dataList) {
      System.out.println("x: " + entry.get("x") + ", y: " + entry.get("y"));
    }
    // set array in data source
    designer.setDataSource("c8",dataList );
    designer.process();

    designer
            .getWorkbook()
            .save("src/main/resources/test-smart-marker-output.xlsx", SaveFormat.XLSX);
  } catch (Exception e) {
    throw new RuntimeException(e);
  }
}

In template we have smart marker as

&=c8.x	&=c8.y	&=&=A{r}+B{r}	&=&=if(A{r}=1,true,false)

We are still getting blank in output template. Please tell what are we doing wrong here?