How to count distinct/unique values using TotalsCalculation?

Aspose.Cells Product Family
1

I am using TotalsCalculation.COUNT but it is giving total count rather than distinct count.


How can I calculate distinct count using Aspose Cells API?

Is there any other way in which we can specify formula that would return the distinct count?

P.S.

I have found the below formula which works fine, can you suggest the code for setting this formula in the sub-totals cell.

<span style=“color: rgb(36, 39, 41); font-family: Consolas, Menlo, Monaco, “Lucida Console”, “Liberation Mono”, “DejaVu Sans Mono”, “Bitstream Vera Sans Mono”, “Courier New”, monospace, sans-serif; font-size: 13px; line-height: 16.9px; white-space: pre-wrap; background-color: rgb(239, 240, 241);”>SUM(IF(FREQUENCY(MATCH(COLUMNRANGE,COLUMNRANGE,0),MATCH(COLUMNRANGE,COLUMNRANGE,0))>0,1))
2

Hi Hitesh,


Thank you for contacting Aspose support.

You can set the formula for any cell by using the Cell.setFormula method. Once you have inserted the formula to a cell, you have to call Workbook.calculateFormula method in order to get the calculated value. Please check the following piece of code as well as the input & output spreadsheets for your reference.

Note: If you have passed wrong arguments to any Excel formula, the API will complain about it during formula calculation.

Java
Workbook book = new Workbook(dir + “book1.xlsx”);
Worksheet sheet = book.getWorksheets().get(0);
sheet.getCells().get(“A11”).setFormula(“SUM(IF(FREQUENCY(MATCH(A2:A10,A2:A10,0),MATCH(A2:A10,A2:A10,0))>0,1))”);
book.calculateFormula();
System.out.println(sheet.getCells().get(“A11”).getValue());
book.save(dir + “output.xlsx”);
3

Hi,

Well, there is no TotalsCalculation option available either in MS Excel or in Aspose.Cells APIs to get distinct values of a list column. You have to do a workaround. Please see the following sample code on how to set the formula dynamically via Aspose.Cells APIs to extract distinct values with the template file (attached) to accomplish your task a bit.

e.g

Sample code:

Workbook workbook = new Workbook(“Bk_TableDistinct1.xlsx”);
//Get the List objects collection in the first worksheet.
ListObjectCollection listObjects = workbook.getWorksheets().get(0).getListObjects();
ListObject table = listObjects.get(0);

//Get the 6th column in the Table’s columns list. 
Range range = table.getListColumns().get(5).getRange();
int frow = range.getFirstRow() +1;
int fcol = range.getFirstColumn();
String sCellName = CellsHelper.cellIndexToName(frow, fcol);
System.out.println(sCellName);
int eRow = range.getRowCount() + range.getFirstRow() -2;
int eCol = fcol;
String eCellName = CellsHelper.cellIndexToName(eRow, eCol);
System.out.println(eCellName);
//workbook.getWorksheets().get(0).getCells().get(eRow+1, eCol).setArrayFormula("=SUM(1/COUNTIFS(" + sCellName + “:” + eCellName + “,” + sCellName + “:” + eCellName + “))”, 1,1);
workbook.getWorksheets().get(0).getCells().get(eRow+1, eCol).setFormula("=SUM(IF(FREQUENCY(MATCH(" + sCellName + “:” + eCellName + “,” + sCellName + “:” + eCellName + “,0),” + “MATCH(” + sCellName + “:” + eCellName + “,” + sCellName + “:” + eCellName + “,0))>0,1))”);
workbook.calculateFormula();
System.out.println(workbook.getWorksheets().get(0).getCells().get(eRow+1, eCol).getStringValue());
workbook.save(“out1.xlsx”);

Hope, this helps a bit.

Thank you.

4
Thanks for the quick reply!


In the below line, though "table.getListColumns().get(5)" evaluates to a valid ListColumn, "getRange()" method returns "null", am I missing anything?

Range range = table.getListColumns().get(5).getRange();

5

Hi,


Well, my code segment should be processed with the template file “Bk_TableDistinct1.xlsx” which is also attached to my previous reply. Furthermore, I am using our latest version/fix: Aspose.Cells for Java (Latest Version) , so kindly try it with my attached template file.

Thank you.

6
Yes, it works great with the newer version, but we are bound to use the older version (8.4.2), thus it does not give the required range from the list columns.

I think I have to derive the target column's range from the ListObject's getDataRange() method (using startRow and startColumn properties of the returned Data Range).
7

Hi,


Well, it looks like a bug in your older version (v8.4.2) that you are using. I am afraid, we cannot evaluate or fix any issue in the older version, we can only recommend you to kindly upgrade to and use latest version of the product. Either you have to sort it out by yourselves (if you want to persist with your older version) or upgrade to the latest APIs set of Aspose.Cells. Also, if you use latest version of Aspose.Cells APIs, we can fix any issue (if found) or help you better.

Thank you.
8

We are using 8.4.2 version with Java 7 run-time, while the latest version is 8.9.2.7. Is it a minor/major version change from 8.4.2? Is it safe to upgrade to the latest version of Aspose Cells without migrating to Java 8?

9

Hi Hitesh,


Thank you for writing back.

Please note, Aspose.Cells for Java 8.9.2.7 it self is a minor release that is on the top of major release 8.9.2. As you are going to upgrade the project from 8.4.2, we strongly recommend you to check the Public API Change section to know what has been changed in the public APIs since your current revision. Moreover, the latest version of the API is fully compatible with JDK 1.6, 1.7 & 1.8. Therefore if you wish to upgrade the JDK to 1.8, there will not be any problems. If you wish to keep your current JDK version and still want to upgrade the API even then you will not face any problems.

10
We have to show sub-totals for all the list columns except few for which we have show "distinct count" using the aforementioned formula. Please find the below code that we use to achieve this.

// Show sub-totals
listObject.setShowTotals(true);
.
.
.
// Show distinct count
cell.setFormula("=SUM(IF(...)...)");
workbook.calculateFormula();

Now, when we use above code to generate Excel file, it gives "Excel found unreadable content..." error while opening it for the first time. Even though, it does not have any visible issues with the content, it gives such error.

Please find the attached workbook and screenshots.

Can you help us fix this error.
11

Hi Hitesh,


Thank you for writing back.

Please note, we need an executable code snippet to fully investigate the recently shared scenario on our side therefore please share a code snippet that could allow us to replicate the problem on our side. However, as we have recently published next major release of Aspose.Cells for Java (Latest Version), we strongly suggest that you should first give the latest version a try on your side.

That said, we also suggest you to always post distinct inquiries in separate forum threads for better visibility and tracking of your requests.