1
votes

I have used autofilter to filter that data in excel and i want number of visible rows excluding hidden rows. I have tried searching for this, but no particular method found. I even tried using excel formula and getting row count, but this only gives cached value.

outputSheet.setAutoFilter(CellRangeAddress.valueOf("A1:C491"));
CTAutoFilter sheetFilter=outputSheet.getCTWorksheet().getAutoFilter();


CTFilterColumn  myFilterColumn=sheetFilter.insertNewFilterColumn(0);
myFilterColumn.setColId(1);

CTFilterColumn  myFilterColumn2=sheetFilter.insertNewFilterColumn(1);
myFilterColumn2.setColId(2);

CTFilters firstColumnFilter=myFilterColumn.addNewFilters();
CTFilter myFilter1=firstColumnFilter.addNewFilter();

CTFilters secondColumnFilter=myFilterColumn2.addNewFilters();
CTFilter myFilter2=secondColumnFilter.addNewFilter();


myFilter1.setVal("New Account");
myFilter2.setVal("Hedge Fund");

List<String> list1 = new ArrayList<String>();
list1.add("Ad Cloud");

List<String> list2 = new ArrayList<String>(); 
list2.add("US");

XSSFRow r1;

for(Row r : my_sheet) {
     for (Cell c : r) {

         c.setCellType(Cell.CELL_TYPE_STRING);
         if ( (c.getColumnIndex()==1 && !list2.contains(c.getStringCellValue())) || (c.getColumnIndex()==2 && !list1.contains(c.getStringCellValue())) ){
         r1=(XSSFRow) c.getRow();

         if (r1.getRowNum()!=0) { /* Ignore top row */
            /* Hide Row that does not meet Filter Criteria */
            r1.getCTRow().setHidden(true); 
        }
    }
}
}

    outputworkbook.write(fos);
    fos.close();
    //format(outputFile);
Please can you provide your code - karen
I have added the code above @karen - Praveen Kumar
When you say visible rows do you mean how many rows the user can see? If so, that will change for each screen and I don't see how you can tell this. You can set the print area like so: //set print area with indexes workbook.setPrintArea( 0, //sheet index 0, //start column 5, //end column 0, //start row 5 //end row ); and you can track how many rows you are inserting data. - karen
@karen by visible i mean resulting rows after filter is applied. Here I am reading data from existing sheet. - Praveen Kumar