2

I'm opening a Excel (xls) file in my Java Application with POI. There are 30 Lines in this Excelfile. I need to get the Value at ColumnIndex 9.

My code:

Workbook wb;
wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);
for (Row row : sheet) {
    if (row.getLastCellNum() >= 6) {
        for (Cell cell : row) {
             if(cell.getColumnIndex == 9) {
                 //do something
              }
         }
     }
}

Every Row in Excel has Values in Columns 1-14. My problem is, only some Values are recognized. I wrote the same value in every cell in ColumnIndex 9 (10th Column in my Excel sheet), but the Problem is still the same.

What could cause this problem?

jussi
  • 2,166
  • 1
  • 22
  • 37
  • what type of value in cell and how do you get value from cell? – user1516873 Oct 15 '12 at 09:54
  • The Value is a Date. In Excel it is set as a Date. in my code, i set the cell type to numeric and get the Date with: cell.getCellDateValue(). (this should work, because i am getting Date Values from another column). The File contains arrival and departure dates. All arrival dates are read correctly, but only some departure dates are read. and for testing, they all have the same value. – jussi Oct 15 '12 at 10:22
  • cell.getDateCellValue() is the right function i use – jussi Oct 15 '12 at 10:37
  • I am confused - why you set the cell type to numeric when you deal with the date? Or by the "date" you actually mean a number representing "day of the month"? EDIT: I do not think you have to set the cell type to anything just so you could read values... There is no real need for that. I believe the reason is that the format of those dates that are missing is not recognised. – DejanLekic Oct 15 '12 at 10:44
  • Date is handled as numeric in Excel, this is correct and works – jussi Oct 15 '12 at 10:47
  • i checked the excel file and filled in the dates myself. Cell Format is "date" in excel and every cell has the same value (not copied, but typed in) – jussi Oct 15 '12 at 10:49
  • 1
    I think your question is related to the following: http://stackoverflow.com/questions/861877/reading-date-values-from-excel-cell-using-poi-hssf-api . – DejanLekic Oct 15 '12 at 10:50
  • as mentioned before, 40/60 dates are read correctly (they are all the same) – jussi Oct 15 '12 at 10:51

3 Answers3

0

Make sure you set the same Date format for all cells in column (select column and set format explicity) And i belive using DataUtil class to get data is more appropriate, than call cell.getDateCellValue().

user1516873
  • 5,060
  • 2
  • 37
  • 56
0

POI uses 0 based counting for columns. So, if you want the 9th Column, you need to fetch the cell with index 8, not 9. It looks like you're checking for column with index 9, so are one column out.

If you're not sure about 0 based indexing, then the safest thing is to use the CellReference class to help you. This will translate between Excel style references, eg A1, and POI style 0-based offsets eg 0,0. Use something like:

CellReference ref = new CellReference("I10");
Row r = sheet.getRow(ref.getRow());
if (r == null) {
    // That row is empty
} else {
    Cell c = r.getCell(ref.getCol());
    // c is now the cell at I10
}
Gagravarr
  • 47,320
  • 10
  • 111
  • 156
  • it is in column 10 in excel so columnindex 9 as mentioned in my question. Cell c is null. – jussi Oct 16 '12 at 06:58
  • Then you're on the wrong sheet or the wrong row... POI gives you exactly what Excel stores in the file, if POI is giving you null for a cell then there really isn't anything there! Double check your sheet indexes, row indexes, cell indexes etc – Gagravarr Oct 16 '12 at 10:15
  • I checked everything. There is only one Sheet. It seems like POI skips some values. – jussi Oct 16 '12 at 10:36
0

Seems to be a Problem with the excel document(s).

Converting them to csv and then back to xls solves the problem.

jussi
  • 2,166
  • 1
  • 22
  • 37
  • I just ran across the same issue. In my case the cells were some numeric and dates types. Seems like an apache POI bug. Similarly I could export as CSV and recreate the xlsx as a workaround. – Dan Tanner Oct 04 '13 at 16:30