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

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

30

@Ajinkya281991,

We are pleased to inform you that your issue (Ticket ID: “CELLSJAVA-46099”) has been resolved. We have supported supported “Skip” parameter in Smart markers with JSON data source. The enhancement/fix will be included in an upcoming release (Aspose.Cells v24.9) that we plan to release in the first half of September 2024. You will be notified when the next version is released.

31

The issues you have found earlier (filed as CELLSJAVA-46099,CELLSJAVA-46080,CELLSJAVA-46081,CELLSJAVA-46082,CELLSJAVA-46086,CELLSJAVA-46087,CELLSJAVA-46088,CELLSJAVA-46084) have been fixed in Aspose.Cells for Java 24.9.

32

Hello I upgraded aspose cell with 24.9 version below are my findings with tickets that are still not working for JSON as input for smart markers.

Q1: Ticket - CELLSJAVA-46086
 : dynamic repatede formauls &=&= not working showing current row only

JSON INPUT DATA

"arrayOfObjectLevel1": [
        {
          "productType": "Shirt",
          "quantity": 20,
          "price": 20
        },
        {
          "productType": "Books",
          "quantity": 10,
          "price": 20
        },
        {
          "productType": "Shoes",
          "quantity": 2,
          "price": 20
        }
      ]

INPUT SMART MARKER TAG

&=$node.arrayOfObjectLevel1.quantity &=$node.arrayOfObjectLevel1.price	&=&=A{r}+B{r}

Actual output :
showing only one row with 0 value

expected  output:
20	20	40
10	20	30
2 	20	22

Q2: CELLSJAVA-46087 - Support converting UTC date-not working

JSON INPUT DATA

"node": {
    "price": 20,
    "quantity": 2,
    "date": "1970-01-01T00:00:00Z",
}

INPUT SMART MARKER TAG

Date dd-mmm-yyyy	&=c8.node.date
mmm-dd-yyyy	&=c8.node.date
mm-dd-yy	&=c8.node.date
yyyy-mm-dd	&=c8.node.date

Actual output:
Date dd-mmm-yyyy	1970-01-01T00:00:00Z
mmm-dd-yyyy	1970-01-01T00:00:00Z
mm-dd-yy	1970-01-01T00:00:00Z
yyyy-mm-dd	1970-01-01T00:00:00Z

expected :
dd-mmm-yyyy
mmm-dd-yyyy
yyyy-mm-dd

Q3: CELLSJAVA-46099 - skip, horizontal param not working , even simple array is not working

JSON INPUT DATA

input {
  "node": {
    "price": 20,
    "quantity": 2,
    "date": "1970-01-01T00:00:00Z",
    "CreatedAt":43862,
    "VariableArray": ["English", "Arabic", "Hindi", "Urdu", "French"],
	}

INPUT SMART MARKER TAG

&=$c8.node.VariableArray(noadd)		&=$c8.node.VariableArray(noadd)	&=c8.node.VariableArray(skip:2,noadd)

actual :
com.aspose.cells.awl@73bd146c		com.aspose.cells.awl@73bd146c	com.aspose.cells.awl@73bd146c
 

expected:
should skip row in array

Q4: Simple array is not working

JSON INPUT DATA

"VariableArray": ["English", "Arabic", "Hindi", "Urdu", "French"],

SMART MARKER TAG

c8..VariableArray

No value is getting printed.

Below is the test code that we are using to test the smart markers with 24.9 version

  @Test
  void generatedDocumentWithExcel() {
    try {
      String data = "template-excel-002-data.json";
      String fileName = "src/test/resources/input/templates/valid-excel-002.xlsx";
      String imagePath = "src/test/resources/input/images/image-001.png";

      final WorkbookDesigner designer = new WorkbookDesigner();
      designer.setWorkbook(new Workbook(fileName));
      String[] smartMarkers = designer.getSmartMarkers();
      List<String> smartMarkersList = Arrays.asList(smartMarkers);
      log.debug("Got smart markers {}", smartMarkersList);
      String jsonFile = testFileUtils.readJsonFile("input/data", data);
      designer.setJsonDataSource("c8", jsonFile);
      designer.process();
      designer
          .getWorkbook()
          .save("src/main/resources/test-smart-marker-output.xlsx", SaveFormat.XLSX);
    } catch (Exception e) {
      throw new RuntimeException(e);
    }
  }

Please let us your findings for each questions
Thanks

33

@Ajinkya281991
Thank you for your feedback. We will analyze the issues you mentioned. Thanks!

34

@Ajinkya281991

We tried 24.10 with the following codes and template file, it works fine.
t (2).zip (14.8 KB)

  Workbook workbook = new Workbook(dir + "Book2.xlsx");
        WorkbookDesigner workbookDesigner = new WorkbookDesigner();
        workbookDesigner.setWorkbook(workbook);
        String data = ReadText(dir + "t (2).txt");
        workbookDesigner.setJsonDataSource("node", data);
        workbookDesigner.process();
        workbook.save(dir + "dest.xlsx");
35

@Ajinkya281991

utc.zip (14.6 KB)

public static void main(String[] args) throws Throwable {
	
		

        Workbook workbook = new Workbook(dir + "Book1.xlsx");
        WorkbookDesigner workbookDesigner = new WorkbookDesigner();
        workbookDesigner.setWorkbook(workbook);
        String data = ReadText(dir + "t (2).txt");
        workbookDesigner.setJsonDataSource("node", data);
        workbookDesigner.process();
        workbook.save(dir + "dest.xlsx");
	       
	}

With above codes and templates ,it works fine.
Please add “numeric” param with smart marker, otherwise we will not convert string to number or date time. And if you want to format them as date, please set date format in the template file.

36

@Ajinkya281991

There is still an issue about this feature. So I reopened this post.

37

@Ajinkya281991

It’s same issue as Q3. We will fix them together.