Hi, Observed that count or count numbers aggregate function in subtotals not counting the blank cells and it is effecting the total count. Is there a way to consider the blank cells. Please suggest.
Could you please share a template Excel file and your sample (runnable) code to demonstrate the issue, we will check it soon.
enclosed the input, out and sample code is as follows. If you see the “release year” there are few blanks and those are not considering by the count.
public static void main(String[] args) {
setAsposeExcelLicense();
// Instantiate a new workbook
Workbook workbook = null;
try {
workbook = new Workbook(Thread.currentThread().getContextClassLoader().getResourceAsStream("subtotals-count.xlsx"));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// Get the Cells collection in the first worksheet
Cells cells = workbook.getWorksheets().get(0).getCells();
// Create a cellarea i.e.., B3:C19
CellArea ca = new CellArea();
ca.StartRow = 1;// don't count header
ca.StartColumn = 0;
ca.EndRow = cells.getMaxDataRow();
ca.EndColumn = 7;
// Get the workbook datasorter object.
DataSorter sorter = workbook.getDataSorter();
// Set the first order for datasorter object.
sorter.addKey(3,SortOrder.ASCENDING);
sorter.sort(workbook.getWorksheets().get(0).getCells(), ca);
// Apply subtotal, the consolidation function is Sum and it will applied
// to
// Second column (C) in the list
cells.subtotal(ca, 3, ConsolidationFunction.COUNT, new int[] {6});
// applyStylesToSubtotalRow(workbook);
//Extend the column width a bit
cells.setColumnWidthPixel(0,172);
// Save the excel file
try {
//Auto-fit rows in the worksheet
workbook.getWorksheets().get(0).autoFitRows();
workbook.save("AsposeSubtotalsCount_out3.xlsx");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// Print message
System.out.println("Process completed successfully");
}
AsposeSubtotalsCount_out3.zip (33.3 KB)
Thanks for the sample files.
I tested your scenario using your sample file and code segment. I generated the output Excel file using Aspose.Cells. Then, I performed the same operations in MS Excel manually. I sorted the data by the (Brand) key first and then implemented the Subtotal function manually. I noticed the same behavior and got the same output as with Aspose.Cells. In short, this is not an issue with Aspose.Cells, as it follows MS Excel standards and specifications for the Subtotal feature. MS Excel also does not consider blank cells when implementing Subtotals. You have to input some blank values (even a single space can do the trick) into those cells to get your desired output.