Queries about Aspose cells smart markers

Aspose.Cells Product Family
41

@pgcentric
25.4.3.zip (8.1 KB)

Please check the above attached file which is generated by inner hot fix 25.4.3.

42

Thanks for the quick response. When will it be available ?

43

@pgcentric,

The fix/enhancement will be included in our upcoming release (Aspose.Cells for Java 25.5) that we plan to release in the first half of May 2025. You will be notified when the next version is released.

44

The issues you have found earlier (filed as CELLSJAVA-46248) have been fixed in Aspose.Cells for Java 25.5.

45

Hi thanks for informing. I tested the same template with 25.5 version and observed that it doesn’t calculate the last array element. For example in same data and template the last cell value should be 10 instead its blank. please refer to the attachment. Could you please check this
Thanks
dynamic_formula.png (13.4 KB)

46

@pgcentric
Thank you for your feedback. We can reproduce the issue by testing on the latest version v25.5 using the following sample code. Please refer to the attachment. CellsJava46248_java_out.zip (8.9 KB)

		Workbook workbook = new Workbook(filePath + "CellsJava46248.xlsx");
		WorkbookDesigner designer = new WorkbookDesigner();
		designer.setWorkbook(workbook);
		String jsonFile =   readFileToString(filePath + "CellsJava46248.json");
		designer.setJsonDataSource("node", jsonFile);
		designer.process();

		workbook.save(filePath + "CellsJava46248_java_out.xlsx");

public static String readFileToString(String filePath) {
        String jsonString = null;
        FileInputStream fileInputStream = null;
 
        try {
            fileInputStream = new FileInputStream(filePath);
            jsonString = IOUtils.toString(fileInputStream, "UTF-8");
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fileInputStream != null) {
                try {
                    fileInputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
 
        return jsonString;
    }

We deeply apologize for any inconvenience caused to you. We have reopened the ticket and will fix it as soon as possible. We will notify you promptly once there are any updates.

47

@pgcentric
25.5.3.zip (10.7 KB)

Please check the above file generated by hotfix 25.5.3.

48

Thank you , We are currently testing the subtotalN functionality with JSON input using Smart Markers in Aspose.Cells, but we’re not getting the expected output.

We’ve followed the documentation here:
Using Smart Markers | Aspose.Cells for Java

Could you please review our approach and confirm if we’re using the correct syntax and structure?

Note: You may refer to the Java code sample we used earlier to test this.

Thanks in advance!

smartMarker.zip (28.7 KB)

49

@pgcentric,

I did test your sceanrio/case using your template Excel file and JSON data file with the following sample code. I think it produced expected results.
e.g.,
Sample code:


       String data = "d:\\files\\smartMarkers.json";
       String fileName = "d:\\files\\smart_markers.xlsx";

       final WorkbookDesigner designer = new WorkbookDesigner();
       designer.setWorkbook(new Workbook(fileName));
       String jsonFile = new String(Files.readAllBytes(Paths.get(data)));
       designer.setJsonDataSource("node", jsonFile);
       designer.process();
       designer
               .getWorkbook()
               .save("d:\\files\\out1.xlsx", SaveFormat.XLSX);

out1.zip (12.8 KB)

Please find attached the output Excel file.

Do you see any issue with the results in the output Excel file? Could you please point out where it works wrongly? Could you please share an Excel file with your expected results which you required after processing smart markers. We will check it soon.

50

subtotal from excel produces following output.
image.png (38.0 KB)

51

@pgcentric,

Thanks for the screenshot.

I tested and found the subtotalN functionality with JSON input using Smart Markers are not working as expected (as per your screenshot).

We require thorough evaluation of the issue. 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-46379

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.

52

@pgcentric
We do not support subtotalN functionality with JSON input using Smart Markers now. We have created an task : CELLSJAVA-46379 for it. We only support two-dimensional data as the data source for the Group and subtotal smart marker. In the nested data, it’s hard to find which item should be grouped.

BTW Subtotal is not allowed in the table in MS Excel.

Now you only can process as Amjad’s post, then subtotal data as
Creating Subtotals|Documentation

53

@pgcentric
CELLSJAVA46379.zip (15.6 KB)

Please check the above template and result file.
There are still two issues: how to set the style of the subtotal row and remove the additional empty row?

54

@pgcentric,

We are pleased to inform you that your issue (Ticket ID: “CELLSJAVA-46379”) has been resolved. The fix/enhancement will be included in the upcoming release (Aspose.Cells v25.6) planned for the first half of June 2025. We will notify you once the next version is published.

55

The issues you have found earlier (filed as CELLSJAVA-46379, CELLSJAVA-46248) have been fixed in Aspose.Cells for Java 25.6.

56

Thank you ! Working as per expectation. Could you please provide link of updated documentation of subtotal with JOSN data.

57

@pgcentric
Sorry, there are currently no relevant documents for subtotal with JOSN data. Please refer to the following example code and check the attachment. result_json.zip (19.8 KB)

Workbook workbook = new Workbook(filePath +  "CELLSJAVA46379.xlsx");
WorkbookDesigner designer = new WorkbookDesigner();
designer.setWorkbook(workbook);


String jsonData = new String(Files.readAllBytes(Paths.get(filePath + "CELLSJAVA46379.json")));
designer.setJsonDataSource("node", jsonData);
designer.process();

designer.getWorkbook().calculateFormula();

designer.getWorkbook().save(filePath + "CELLSJAVA46379_out.xlsx");

Hope helps a bit.

58

Hello,
We would like to document the subtotal functionality in Smart Markers for our users. Since there is currently no official documentation available, we have created a rough draft based on the behavior we’ve observed and tested.

I’ve attached the draft document for your review. Could you please take a look and confirm whether this usage is accurate, or if there are any details or edge cases we might have missed?

Your feedback will help ensure we provide correct and complete guidance to our users.
Subtotal.docx (16.9 KB)

59

@pgcentric
We have added the relevant document for subtotal with JOSN data, please refer to it.

60

Thanks, I’ve checked the link.

A couple of points seem to be missing from the documentation. Could you please confirm if our understanding below is correct?

  1. subtotal9 syntax: From what we understand, the number 9 refers to the Excel SUBTOTAL function code for SUM. Similarly, other function codes like 1 for AVERAGE, 2 for COUNT, etc., should also be supported.
  2. (group) parameter: Adding (group) to a field groups the data by that field, and subtotals are then applied per group. This seems to automatically insert a subtotal row after each group.

Please let us know if this is accurate or if there are any additional details we should be aware of. We’re working on internal documentation and want to ensure it’s fully aligned with intended behavior.

Thanks again!