I am using aspose-cells-7.4.2 for java.I have applied autofilter and able to get filtered rows only. How to get sum of specific column values from the reange of filtered rows only. Once i get, i need to remove the applied filters and change the filter options and capture the result again. How to do ? Please guide me.
Hi,
I have written a sample code for your reference to accomplish your task a bit. In order to retrieve the filtered data only, you have to first check what rows are hidden, and store the un-hidden cell values in a container for later use.The sample code loads a template file (attached) containing the filtered data in first column of the first worksheet, then iterates over all the worksheet rows values of the second column only to store the corresponding cell values in an ArrayList. Finally I populate the cells in second worksheet with filtered data in the second column, then I applied the formula to that column values only.
Please refer to the code segment and create your own codes by yourself for your needs for your reference accordingly.
e.g
Sample code:
Workbook book = new Workbook(“FilteredBk1.xlsx”);
Cells cells = book.getWorksheets().get(0).getCells();
RowCollection rows = cells.getRows();
ArrayList list = new ArrayList();
for (int index = 0; index < rows.getCount(); index++)
{
if (!rows.<span style=“font-size:12.0pt;font-family:“Courier New”;mso-fareast-font-family:“Times New Roman”;
color:blue;mso-ansi-language:EN-SG;mso-fareast-language:EN-SG;mso-bidi-language:
AR-SA”>getRowByIndex(index).isHidden())
{
//Add values to the list in the second column
list.add(cells.get(index, 1).getStringValue());
}
}
Worksheet sheet = book.getWorksheets().get(1);
for (int listIndex = 0; listIndex < list.size(); listIndex++)
{
sheet.getCells().get(listIndex, 1).putValue(list.get(listIndex), true);
}
int i = sheet.getCells().getMaxDataRow();
sheet.getCells().get(i+1, 1).setFormula("=Sum(B2:B" + (i+1) + “)”);
book.calculateFormula();
System.out.println(sheet.getCells().get(i+1, 1).getStringValue());
book.save(“out1.xlsx”);
Hope, this helps a bit.
Thank you.