CODEWITHSUNDEEP
javaexcelapache-poi
sunpat
sunpat

Reputation: 399

How to get the Excel sheet cell data by column name in using apache POI library

I want to get the cell data of a particular sheet with column name rather than column index, Currently i can get the cell data using 

fis = new FileInputStream(ExcelPath);
wb = new XSSFWorkbook(fis);
sh = wb.getSheet(SheetName);
cell = sh.getRow(rownum).getCell(colnum);

but I am looking for a way where I can get the cell data by,

cell = sh.getRow(rownum).getCell(ColName); //not working

Upvotes: 0

Views: 5057

Answers (1)

Gagravarr
Gagravarr

Reputation: 48376

Excel columns don't have names. They only have letters, eg A, D, BB

To work out how to map an Excel-style column letter into a POI-style 0-based index, you need to use the method convertColStringToIndex(java.lang.String ref) which does it for you

If you mean you want to lookup a column based on the value held in the first row, you should do something like:

DataFormatter fmt = new DataFormatter();
Map<String,Integer> headings = new HashMap<String,Integer>();
Row headings = sheet.getRow(0);
if (headings == null) throw new IllegalArgumentException("Empty headings row");
for (Cell c : headings) {
    headings.put(fmt.formatCellValue(c), c.getColumnIndex());
}

If you have more complex use cases, such as a heading running over multiple rows, you'll need to write more complex code... It's all driven by your use-case

Upvotes: 1

Related Questions