https://bz.apache.org/bugzilla/show_bug.cgi?id=65241
Bug ID: 65241
Summary: Slope and Intercept calc doesn't handle blank cells in
range.
Product: POI
Version: 4.1.2-FINAL
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: [email protected]
Reporter: [email protected]
Target Milestone: ---
Created attachment 37809
--> https://bz.apache.org/bugzilla/attachment.cgi?id=37809&action=edit
test case for blank cells in range calc
Hi, attached excel file shows different Excel/poi 4.1.2 Slope/Intercept calcs
caused by the slope/intercept cell range containing blank cells. Code use to
generate Excel file:
public static void main(String[] args) {
try {
File initialFile = new File(
"C:\\Users\\twelch\\eclipse-workspace\\Main\\test_excel\\test_slope_intercept.xlsx");
InputStream is = new FileInputStream(initialFile);
XSSFWorkbook workbook = new XSSFWorkbook(is);
is.close();
// Excel formulas : D2 slope, D3 intercept
// evaluate and save results in F2, F3
XSSFCell slopeCell = cellAtAddr(workbook, "Sheet1!D2");
XSSFCell interceptCell = cellAtAddr(workbook,
"Sheet1!D3");
evalCellSaveAtAddr(workbook, slopeCell, "Sheet1!F2");
evalCellSaveAtAddr(workbook, interceptCell,
"Sheet1!F3");
workbook.setForceFormulaRecalculation(true);
File outFile = new File(
"C:\\Users\\twelch\\eclipse-workspace\\Main\\test_excel\\test_slope_intercept.xlsx");
FileOutputStream os = new FileOutputStream(outFile);
workbook.write(os);
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static XSSFCell cellAtAddr(XSSFWorkbook workbook, String
cellAddr) {
CellReference cr = new CellReference(cellAddr);
XSSFRow row =
workbook.getSheet(cr.getSheetName()).getRow(cr.getRow());
if (row == null)
row =
workbook.getSheet(cr.getSheetName()).createRow(cr.getRow());
XSSFCell cell = row.getCell(cr.getCol());
if (cell == null)
cell = row.createCell(cr.getCol());
return cell;
}
public static void evalCellSaveAtAddr(XSSFWorkbook workbook, XSSFCell
evalCell, String destCellAddr) {
FormulaEvaluator evaluator =
workbook.getCreationHelper().createFormulaEvaluator();
XSSFCell destCell = cellAtAddr(workbook, destCellAddr);
evaluator.evaluateFormulaCell(evalCell);
double dval = evalCell.getNumericCellValue();
destCell.setCellValue(dval);
}
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
