Filter excel data with apache poi in java

Question

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?

winklerrr · Accepted Answer · 2015-11-25 13:23:29Z

2

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 + "]");
        }               
    }
}

edited Nov 25, 2015 at 13:23

answered Nov 25, 2015 at 13:07

winklerrr

15.2k9 gold badges83 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gagravarr Over a year ago

You can use CellReference.convertColStringToIndex(String) to turn the column label A into 0 to make it clearer

winklerrr Over a year ago

@Gagravarr thanks! By the way, is there also a function for the other way round? To generate the correct coordinate String from the row and column number?

Gagravarr Over a year ago

Yup, just create a CellReference with two ints, then call formatAsString on it

javadev Over a year ago

I think the question was : Is there a way to filer on columns before parsing. Rows will parsed anyway. But in order to reduce number of iterations, is there a way to filter before iterating?

centic · Accepted Answer · 2015-11-25 07:18:28Z

1

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.

answered Nov 25, 2015 at 7:18

centic

16k10 gold badges74 silver badges131 bronze badges

1 Comment

4862610 Over a year ago

tks,but Ive read there before, but its too general, I asked this question because need some example, at last I do it myself and it cost me the whole day, anyhow, tks

Collectives™ on Stack Overflow

Filter excel data with apache poi in java

2 Answers 2

Iterate column A

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Iterate column A

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related