// TODO should we have this stuff in the FormulaEvaluator?
private void evaluateWorkbook(Workbook workbook) {
FormulaEvaluator eval = workbook.getCreationHelper().createFormulaEvaluator();
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
Sheet sheet = workbook.getSheetAt(i);
for (Row r : sheet) {
for (Cell c : r) {
if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
eval.evaluateFormulaCell(c);
}
}
}
}
}
private Object evaluateCellFormula(final HSSFWorkbook workbook, final Cell cell) {
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
CellValue cellValue = evaluator.evaluate(cell);
Object result = null;
if (cellValue.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
result = cellValue.getBooleanValue();
} else if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
result = cellValue.getNumberValue();
} else if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
result = cellValue.getStringValue();
}
return result;
}
/**
* セルに設定された計算式を評価して値を取得する
*
* @param nRow 行番号
* @param nColumn 列番号
* @return セルの値
*/
public static Object getDataByEvaluateFormula(XSSFSheet sheet, int nRow, int nColumn) {
assert sheet != null;
XSSFRow row = getRowAnyway(sheet, nRow);
if (row != null) {
XSSFCell cell = row.getCell(nColumn);
if (cell != null) {
FormulaEvaluator eval = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
if (eval != null) {
CellValue value = eval.evaluate(cell);
if (value != null) {
return value.getNumberValue();
}
}
}
}
return null;
}
protected CellValue getCellValueAt(int index) {
if (index < 0 || index >= numOfCells) {
throw new IndexOutOfBoundsException(
"Index must be between 0 and " + (numOfCells - 1) + " (inclusive), given: " + index);
}
int firstRow = cellRangeAddress.getFirstRow();
int firstCol = cellRangeAddress.getFirstColumn();
int lastCol = cellRangeAddress.getLastColumn();
int width = lastCol - firstCol + 1;
int rowIndex = firstRow + index / width;
int cellIndex = firstCol + index % width;
Row row = sheet.getRow(rowIndex);
return (row == null) ? null : evaluator.evaluate(row.getCell(cellIndex));
}
