Here is a snippet of what I use to process our spreadsheets (I think I got
it from a sample on here or the web site):
for (Row row : sheet) { //all rows in the sheet
if(row.getRowNum()==0)continue;
short minColIx = 0;
short maxColIx = row.getLastCellNum();
List<String> cells = new ArrayList<String>();
for(short colIx=minColIx; colIx<maxColIx; colIx++)
{
Cell cell = row.getCell(colIx);
cells.add(getCellContentsAsString(cell));
...
And the called function to get the contents (this is optional):
protected String getCellContentsAsString(Cell cell) throws
Exception {
String rc = "";
Object cellValue = getCellContents(cell);
if(cellValue==null)return "";
if (cellValue instanceof Integer) {
rc = ((Integer)cellValue).toString();
} else if (cellValue instanceof Double) {
rc = ((Double)cellValue).toString();
} else if (cellValue instanceof String) {
rc = (String)cellValue;
} else if (cellValue instanceof Boolean) {
rc = ((Boolean)cellValue).toString();
} else if (cellValue instanceof java.util.Date) {
rc = ((java.util.Date)cellValue).toString();
}
return rc;
}
And the function to get the contents (this is from one of the examples):
protected Object getCellContents(Cell cell) throws Exception {
Object cellValue = null;
switch(cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
cellValue =
cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(cell)) {
cellValue = cell.getDateCellValue();
} else {
cellValue = cell.getNumericCellValue();
}
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
cellValue = cell.getCellFormula();
break;
default:
cellValue = null;
}
return cellValue;
}
-Lou
Patssay <[email protected]> wrote on 11/30/2010 03:12:19 PM:
>
> Hi,
>
> I have used Apache POI to read excel sheet and the cell contents
> Now it may happen that either or more of the cells in a row are empty.
> From what I read the cell iterator returns the null cells but while
> iterating over it cells.next points to the next non-null field.
> I want to dump the contents as they are in the sheet into a database
> So basically a null field has to be null.
> So I wrote a routine which would check the cell.getColumnNumber and the
> count that I maintained to check whether it is indeed the right cell.
> But cell.hasNext() used in the loop makes the pointer move to the next
cell
> and due to which the entire loop goes haywire.
> Could someone help me with this?
> I think I am trying to complicate matters and there is an easy way out.
>
> Bunch of Thanks
> :)
> Patssay
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.
> com/Reading-Excel-sheet-cell-contents-tp3286664p3286664.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
