I have been using the following code block to retrieve sheet contents into a
String[][] array with success:
for (int i = 0, len = sheet.getLastRowNum(); i <= len; i++) {
Row row = sheet.getRow(i);
if (row != null) {
for (int j = 0, jlen = row.getLastCellNum(); j < jlen; j++) {
Cell cell = row.getCell(j);
if (cell != null) {
if (cell.getCellType() ==
Cell.CELL_TYPE_FORMULA) {
if (cell.getCachedFormulaResultType()
== Cell.CELL_TYPE_ERROR) {
data[i][j] = "#VALUE!";
}
else {
data[i][j] =
formatter.formatCellValue(cell, evaluator);
}
}
else if (cell.getCellType() ==
Cell.CELL_TYPE_NUMERIC && !
DateUtil.isCellDateFormatted(cell)) {
short formatIndex =
cell.getCellStyle().getDataFormat();
if (formatIndex >= 164) { //custom
format
CellFormat cellFormat =
CellFormat.getInstance(cell.getCellStyle().getDataFormatString());
data[i][j] =
cellFormat.apply(cell).text;
}
else {
data[i][j] =
formatter.formatCellValue(cell);
}
}
else {
try {
data[i][j] =
formatter.formatCellValue(cell);
}
catch(Exception e) {
cell.setCellType(Cell.CELL_TYPE_STRING);
data[i][j] =
cell.getRichStringCellValue().toString();
}
}
}
else {
data[i][j] = "";
}
}
}
}
So far this has been working for all my test cases and no new issues have
been reported. This is obviously not the entire code, just the portion that
deals with the formatting of each cell value. There is other stuff there
that strips empty rows, etc.
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/Reading-cell-values-that-have-a-custom-format-tp5486315p5524910.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]
