CODEWITHSUNDEEP
javaexcelapacheapache-poi
4862610
4862610

Reputation: 325

Filter excel data with apache poi in java

I want filter excel data by program. for example

   |  a  b  c  |
   |  1  x  x  |
   |  2  x  x  |
   .............

I want to filter column a where a values 1 and ignore other , I have google a lot and search docs in apache poi website but no effect, is there any way to do this?

Upvotes: 0

Views: 6748

Answers (2)

winklerrr
winklerrr

Reputation: 14727

The index of rows and columns are 0-based. Therefore if you want to scan column A, you have to use the index 0.

Further, if you want to iterate cells in a specific column, you have to iterate the rows and then get the cell from each row for the specific column number.

To filter cells by a given value, the following steps are necessary:

  • check if the cell is not null
  • check if the cell has the correct cell type (CELL_TYPE_STRING / CELL_TYPE_NUMERIC)
  • check if the value contained by the cell is equivalent to the given filter value

Iterate column A

An example for your specific problem. The code iterates all cells in the first column (column A) and prints out the corresponding coordinates.

final int firstRowNumber = sheet.getFirstRowNum();
final int lastRowNumber = sheet.getLastRowNum();

// Column A (index = 0)
// final int columnNumber = CellReference.convertColStringToIndex("A")
final int columnNumber = 0;

final int filterValue = 1;

// Iterate rows
for (int rowNumber = firstRowNumber; rowNumber < lastRowNumber; rowNumber++) {
    final Row row = sheet.getRow(rowNumber);

    if (row != null) {
        final Cell cell = row.getCell(columnNumber);

        // Filter cells
        if (cell != null && cell.getCellType() == Cell.CELL_TYPE_NUMERIC && cell.getNumericCellValue() == filterValue) {
            System.out.println("Found cell with value " + filterValue + " at [" + rowNumber + "," + columnNumber + "]");
        }               
    }
}

Upvotes: 2

centic
centic

Reputation: 15872

It's a very general question, so I can only give a general answer:

I think you should start with reading in the file and iterating over the Rows/Cells, then you can query the Cell-Value and go from there.

See the rest of the Guides at http://poi.apache.org/spreadsheet/quick-guide.html and http://poi.apache.org/spreadsheet/how-to.html for additional things that can be done.

Upvotes: 1

Related Questions