diff options
Diffstat (limited to 'poi-ooxml')
5 files changed, 64 insertions, 5 deletions
diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFCreationHelper.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFCreationHelper.java index d18177c032..4095bf78b7 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFCreationHelper.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFCreationHelper.java @@ -61,6 +61,7 @@ public class SXSSFCreationHelper implements CreationHelper { @Override public SXSSFFormulaEvaluator createFormulaEvaluator() { + return new SXSSFFormulaEvaluator(wb); } diff --git a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java index a1d33c9600..d7ed668d1f 100644 --- a/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java +++ b/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java @@ -29,6 +29,7 @@ import org.apache.poi.ss.formula.FormulaRenderer; import org.apache.poi.ss.formula.FormulaType; import org.apache.poi.ss.formula.SharedFormula; import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.ptg.ErrPtg; import org.apache.poi.ss.formula.ptg.Ptg; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellBase; @@ -474,7 +475,19 @@ public final class XSSFCell extends CellBase { if (wb.getCellFormulaValidation()) { XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb); //validate through the FormulaParser - FormulaParser.parse(formula, fpb, formulaType, wb.getSheetIndex(getSheet()), getRowIndex()); + Ptg[] ptgs = FormulaParser.parse(formula, fpb, formulaType, wb.getSheetIndex(getSheet()), getRowIndex()); + // Make its format consistent with Excel. + // eg: "SUM('Sheet1:Sheet2'!A1:B1)" will be trans to "SUM(Sheet1:Sheet2!A1:B1)" + boolean hasError = false; + for (Ptg ptg : ptgs) { + if (ptg instanceof ErrPtg) { + hasError = true; + break; + } + } + if (!hasError) { + formula = FormulaRenderer.toFormulaString(fpb, ptgs); + } } CTCellFormula f; diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFCell.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFCell.java index 54dd5c0b46..a98f762d79 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFCell.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFCell.java @@ -563,14 +563,14 @@ public final class TestXSSFCell extends BaseTestXCell { @Test public final void testCopyCellFrom_CellCopyPolicy_formulaWithUnregisteredUDF() { setUp_testCopyCellFrom_CellCopyPolicy(); - + // this will format the formula string, srcCell.setCellFormula("MYFUNC2(123, $A5, Sheet1!$B7)"); // Copy formula verbatim (no shifting). This is okay because copyCellFrom is Internal. // Users should use higher-level copying functions to row- or column-shift formulas. final CellCopyPolicy policy = new CellCopyPolicy.Builder().cellFormula(true).build(); destCell.copyCellFrom(srcCell, policy); - assertEquals("MYFUNC2(123, $A5, Sheet1!$B7)", destCell.getCellFormula()); + assertEquals("MYFUNC2(123,$A5,Sheet1!$B7)", destCell.getCellFormula()); } @Test diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java index 887e23ae11..e8f2290935 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java @@ -186,8 +186,8 @@ public final class TestXSSFFormulaEvaluation extends BaseTestFormulaEvaluator { // Add another formula referencing these workbooks Cell cXSL_cell2 = rXSL.createCell(40); cXSL_cell2.setCellFormula("[56737.xls]Uses!$C$1"); - // TODO Shouldn't it become [2] like the others? - assertEquals("[56737.xls]Uses!$C$1", cXSL_cell2.getCellFormula()); + // it become [2] like the others + assertEquals("[2]Uses!$C$1", cXSL_cell2.getCellFormula()); assertEquals("\"Hello!\"", evaluator.evaluate(cXSL_cell2).formatAsString()); diff --git a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java index 7b56cf8965..9cf439faa9 100644 --- a/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java +++ b/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java @@ -399,6 +399,51 @@ public final class TestXSSFFormulaParser { } } + @Test + void testQuotedSheetNamesReference() { + // quoted sheet names bug fix + Workbook[] wbs = new Workbook[]{new HSSFWorkbook(), new XSSFWorkbook()}; + for (Workbook wb : wbs) { + Sheet sheet1 = wb.createSheet("Sheet1"); + Sheet sheet2 = wb.createSheet("Sheet2"); + Sheet sheet3 = wb.createSheet("Sheet 3"); + + Row tempRow = sheet1.createRow(0); + tempRow.createCell(0).setCellValue(1); + tempRow.createCell(1).setCellValue(2); + + tempRow = sheet2.createRow(0); + tempRow.createCell(0).setCellValue(3); + tempRow.createCell(1).setCellValue(4); + + tempRow = sheet3.createRow(0); + tempRow.createCell(0).setCellValue(5); + tempRow.createCell(1).setCellValue(6); + + Cell cell = tempRow.createCell(2); + + // unquoted sheet names + String formula = "SUM(Sheet1:Sheet2!A1:B1)"; + cell.setCellFormula(formula); + String cellFormula = cell.getCellFormula(); + assertEquals(formula, cellFormula); + + // quoted sheet names with no space + cell = tempRow.createCell(3); + formula = "SUM('Sheet1:Sheet2'!A1:B1)"; + cell.setCellFormula(formula); + cellFormula = cell.getCellFormula(); + assertEquals("SUM(Sheet1:Sheet2!A1:B1)", cellFormula); + + // quoted sheet names with space + cell = tempRow.createCell(4); + formula = "SUM('Sheet1:Sheet 3'!A1:B1)"; + cell.setCellFormula(formula); + cellFormula = cell.getCellFormula(); + assertEquals(formula, cellFormula); + } + } + private static String toFormulaString(Ptg ptg, FormulaParsingWorkbook wb) { if (ptg instanceof WorkbookDependentFormula) { return ((WorkbookDependentFormula)ptg).toFormulaString((FormulaRenderingWorkbook)wb); |