How to get the value of a cell at a specified position in an excel sheet using JAVA

Question

How to get the value of a specific cell from a .xlsm file using java ..?? I want to fetch the cell value by specifying the particular row and column for example i need the cell value at row 1 and column C1 or row5 and column C6 ... I am getting the values by specifying the row and column number like this

XSSFRow row = sheet.getRow(4); // 4 is the row number

cell = row.getCell(4); // 4 is the column number

But this is working only if the sheet has column starting from A,B,C,D...so on...when i try with the same coding to fetach another sheet but it does not work... In this sheet, column starts from C,D,E ... so on

Can any one help me out to get to know what can i use therr to get the specified result ?

If I understand correctly, your sheet has its two first columns (A and B) hidden. They're there, but only hidden. A is still at index 0, B at index 1, C at index 2, etc. — JB Nizet, Dec 27 '12 at 11:06

score 11 · Answer 1 · answered Dec 27 '12 at 22:35

11

You probably want to use the CellReference utility class to help you out.

You can then do something like:

 Sheet sheet = workbook.getSheet("MyInterestingSheet");

 CellReference ref = new CellReference("B12");
 Row r = sheet.getRow(ref.getRow());
 if (r != null) {
    Cell c = r.getCell(ref.getCol());
 }

That will let you find the cell at a given Excel-style reference

answered Dec 27 '12 at 22:35

Gagravarr

47,320
10
111
156

Great, glad it did! Please mark the answer as accepted when you have a minute (click the tick next to the question), so that others in future know it works – Gagravarr Jan 02 '13 at 12:37
@Gagravarr How about reading a value that spans multiple columns like B3:G3? – EMM Feb 16 '16 at 22:20
@EMM As in a merged cell? If so, just read the top-left hand cell to get the value out – Gagravarr Feb 16 '16 at 23:25
@Gagravarr Thanks for the helping me out mate. Is it always true that value in a merged cell will always be at the top-left hand cell? – EMM Feb 16 '16 at 23:35

score 0 · Answer 2 · edited Dec 27 '12 at 12:31

0

if that sheet has name,u can get the sheet name and use iterator.

  Iterator iterator = workSheet.rowIterator();
  while(iterator.next){
       Row row = (Row) iterator.next();
        //u can iterate and use row.getCell(i)
  }

edited Dec 27 '12 at 12:31

Ralph

118,862
56
287
383

answered Dec 27 '12 at 12:08

swamy

1,200
10
23

How to get the value of a cell at a specified position in an excel sheet using JAVA

2 Answers2