@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.
Awesome news Thank you. 
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…
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.
@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.
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?
@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.
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.
@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.
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.
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.
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 …
Your understanding is correct. Smart Markers feature provided by Aspose.Cells is case-insensitive.
1 Like
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.
-
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. -
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.
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.
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.
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
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");
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.