Issue with Apache Poi while Parsing excel in java?

Question

I am using Apache POI to parse excel file and to get values back am doing by row.getCell(5).getNumericCellValue() assuming that cell(5) on that row would always be number.

Is it possible to intelligently parse file so that even if order of columns is changed or jumbled even then we can look for values based on columnName so that contract year always comes from the column labelled Contract Year - regardless of whether that column is the 6th or 7th column?

Francis P · Accepted Answer · 2012-04-19 18:14:50Z

1

You could implement a function called getContractYearColumnIndex() which returns the index you need.

private int getContractYearColumnIndex(){
    final int maxColIndex = 25;
    int colIndex = 0;
    HSSFRow titleRow = yourSheet.getRow(0);
    String value = titleRow.getCell(colIndex).getStringCellValue();

    while(value != "Contract Year" && colIndex < maxColIndex){
        colIndex = colIndex + 1;
        value = titleRow.getCell(colIndex).getStringCellValue();
    }
    return colIndex;
}

Of course, this implementation supposes the title you're looking for is in the first row (index 0).

edited Apr 19, 2012 at 18:14

answered Apr 19, 2012 at 17:58

Francis P

13.7k3 gold badges30 silver badges52 bronze badges

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

5 Comments

Rachel Over a year ago

ok but the thing here is that file is configurable and so there are possibilities that row could be present or they could not be present no file

Rachel Over a year ago

also this would add lots of processing load on the system right.

Kalpak Gadre Over a year ago

Yes, it will be a performance hit. Excel is not a good format to store structured data although I understand that you may be helpless, been in that situation :)

Francis P Over a year ago

well call the function only once for the entire file and store the result in a variable so then you only have to call row.getCell(contractYearIndex).

Francis P Over a year ago

if you suppose the column may not be in the row, then set a limit to the colIndex variable (see edited answer).

Collectives™ on Stack Overflow

Issue with Apache Poi while Parsing excel in java?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related