Lou, Great example. If I wanted to manipulate the data and then update the existing spreadsheet, what approach would be necessary? Would I need to build a new sheet, read the sheet and all the necessary styles, indexes etc. or is there a facility available in poi for xssf.
Regards, -Craig Sent from my iPhone On Nov 30, 2010, at 19:38, [email protected] wrote: > 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] >> --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
