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

Aspose.Cells Product Family
,
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?

21

@Ajinkya281991
By creating sample file and testing the following sample code on the latest version v24.8, we can reproduce the issue. Failed to replace data when using map data as the data source for smart markers. Please check the attachment. result.zip (15.3 KB)

String fileName = filePath + "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(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-46088

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.

22

@Ajinkya281991,

Moreover, we are happy to inform you that the following two issues have also been resolved.

  • CELLSJAVA-46086 - Support JSON as data soure of repeated formulas smart marker
  • CELLSJAVA-46087 - Support converting UTC date string to DateTime in processing Smart Markers

The fixes will be included in our 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.

23

@Ajinkya281991
Please implement ICellsDataTable with List<Map<String, Object>> as the following

class MapCellsDataTable implements ICellsDataTable {
    private final List<Map<String, Object>> data;
    private Iterator<Map<String, Object>> iterator;
    private Map<String, Object> currentRow;

    public MapCellsDataTable(List<Map<String, Object>> data) {
        this.data = data;
        this.beforeFirst();
    }

    @Override
    public String[] getColumns() {
        if (data.isEmpty()) {
            return new String[0];
        }
        return data.get(0).keySet().toArray(new String[0]);
    }

    @Override
    public int getCount() {
        return data.size();
    }

    @Override
    public void beforeFirst() {
        iterator = data.iterator();
        currentRow = null;
    }

    @Override
    public Object get(int index) {
        if (currentRow == null || index < 0 || index >= getColumns().length) {
            return null;
        }
        String columnName = getColumns()[index];
        return currentRow.get(columnName);
    }

    @Override
    public Object get(String columnName) {
        if (currentRow == null) {
            return null;
        }
        return currentRow.get(columnName);
    }

    @Override
    public boolean next() {
        if (iterator.hasNext()) {
            currentRow = iterator.next();
            return true;
        } else {
            currentRow = null;
            return false;
        }
    }
}

We are working on HashMap as a data object.

24

Hello thanks for your reply

But I used above given class but it is still working

Code

 final List<Map<String, Object>> inpuData = new ArrayList<>();
      Map<String, Object> actualData1 = new HashMap<>();
      actualData1.put("x", 10);
      actualData1.put("y", 20);

      Map<String, Object> actualData2 = new HashMap<>();
      actualData2.put("x", 30);
      actualData2.put("y", 20);
      inpuData.add(actualData1);
      inpuData.add(actualData2);

      MapCellsDataTable mapCellsDataTable = new MapCellsDataTable(inpuData);
      designer.setDataSource("c8.node.dynamicFormula",mapCellsDataTable);

Smart marker in template

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

Error given below

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

What we are doing wrong please tell us.

25

@Ajinkya281991,

We are already working on supporting HashMap as a data object. We already logged a ticket with the ID “CELLSJAVA-46088”. We have recorded your (new) scenario/case against the ticket “CELLSJAVA-46088” in our database. We are in the process of supporting/fixing it. Once we have an update, we will let you know.

26

Thanks really for your inputs.

I was trying to insert image using smart marker in template

&=$c8.Image(pIcturE:fittoCell)
&=c8.node.cOdE

As you can see I am using case in-sensitive for (Picture:FitToCell) and c8.node.Code, even I changed it to upper case it is working , I want to know is Aspose.cell is totally case in-sensitive
Is my assumption right please confirm once…

Thanks …

27

@Ajinkya281991,

Your understanding is correct. Smart Markers feature provided by Aspose.Cells is case-insensitive.

1 Like
28

Hello we have some questions related using parameters with JSON as datasource ,

Q1: Smart marker in template

&=c8.node.numericString(numeric)
&=c8.node.arrayOfObjectLevel1.productType(horizontal)
&=c8.node.arrayOfObjectLevel1.quantity(skip:1)

The above does not seems to generate the expected output eg. like skip:1 does not generate a document where 1 row is skipped after each value…

Although in above we have mention only 3 markers with parameters, we have faced similar issue with rest of the parameters , can you please if others are working too with JSON datasource.

please give your inputs on above question , that what we are not using it properly or that operation is not supported for JSON as datasource.

Q2: And can you give us example how we can use Group parameters with JSON as datasource.

29

@Ajinkya281991

  1. Skip is not supported if the data source of json. It is hard to support it in the nested data source.
    We logged it with id: CELLSJAVA-46099 to check this feature. In the repeated nested struct, Skip parameter can have many ambiguities though it’s clear in your demo.

  2. Group smart marker is suitable for two-dimensional data and not suitable for nested structures.
    JSON data source is a nested struct data, so itself is grouped data. We have no plan to support group smart marker with json data source. Please implement ICellsDataTable interface with json data source, then we can process it with group parameters.