diff options
5 files changed, 72 insertions, 10 deletions
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..fcdd530be8 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 @@ -474,7 +474,10 @@ 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)" + formula = FormulaRenderer.toFormulaString(fpb, ptgs); } CTCellFormula f; 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..120184b681 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 @@ -723,4 +723,49 @@ public final class TestXSSFFormulaParser { wb.close(); } + + @Test + void testQuotedSheetNamesReference() { + // quoted sheet names bug fix (https://github.com/apache/poi/pull/267) + 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); + } + } } diff --git a/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java b/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java index 94c88219e9..8be98c5656 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java @@ -1171,10 +1171,11 @@ public final class FormulaParser { while(!done) { sb.appendCodePoint(look); GetChar(); - if(look == '\'') - { - Match('\''); - done = look != '\''; + switch (look){ + case '\'' : + GetChar(); + case ':' : + done = true; } } @@ -1200,6 +1201,9 @@ public final class FormulaParser { sb.appendCodePoint(look); GetChar(); } + if (look == '\'') { + GetChar(); + } NameIdentifier iden = new NameIdentifier(sb.toString(), false); SkipWhite(); if (look == '!') { @@ -1249,6 +1253,7 @@ public final class FormulaParser { switch(ch) { case '.': // dot is OK case '_': // underscore is OK + case ' ': // space is OK return true; } return false; diff --git a/poi/src/main/java/org/apache/poi/ss/formula/ptg/ExternSheetNameResolver.java b/poi/src/main/java/org/apache/poi/ss/formula/ptg/ExternSheetNameResolver.java index 3a35909cc2..2a90ae6f44 100644 --- a/poi/src/main/java/org/apache/poi/ss/formula/ptg/ExternSheetNameResolver.java +++ b/poi/src/main/java/org/apache/poi/ss/formula/ptg/ExternSheetNameResolver.java @@ -44,7 +44,16 @@ final class ExternSheetNameResolver { ExternalSheetRange r = (ExternalSheetRange)externalSheet; if (! r.getFirstSheetName().equals(r.getLastSheetName())) { sb.append(':'); - SheetNameFormatter.appendFormat(sb, r.getLastSheetName()); + // quote should appear at the beginning and end. + StringBuilder innerBuilder = new StringBuilder(); + SheetNameFormatter.appendFormat(innerBuilder, r.getLastSheetName()); + char quote = '\''; + if (innerBuilder.charAt(0) == quote){ + sb.insert(0 , quote); + sb.append(innerBuilder.substring(1)); + } else { + sb.append(innerBuilder); + } } } } else { diff --git a/poi/src/test/java/org/apache/poi/hssf/model/TestFormulaParser.java b/poi/src/test/java/org/apache/poi/hssf/model/TestFormulaParser.java index 6c55d18224..0768cd713e 100644 --- a/poi/src/test/java/org/apache/poi/hssf/model/TestFormulaParser.java +++ b/poi/src/test/java/org/apache/poi/hssf/model/TestFormulaParser.java @@ -535,9 +535,9 @@ final class TestFormulaParser { assertEquals("'Test Sheet'!A1", formula); // Now both - cell.setCellFormula("Cash_Flow:'Test Sheet'!A1"); + cell.setCellFormula("'Cash_Flow:Test Sheet'!A1"); formula = cell.getCellFormula(); - assertEquals("Cash_Flow:'Test Sheet'!A1", formula); + assertEquals("'Cash_Flow:Test Sheet'!A1", formula); // References to a range (area) of cells: @@ -553,9 +553,9 @@ final class TestFormulaParser { assertEquals("'Test Sheet'!A1:B2", formula); // Now both - cell.setCellFormula("Cash_Flow:'Test Sheet'!A1:B2"); + cell.setCellFormula("'Cash_Flow:Test Sheet'!A1:B2"); formula = cell.getCellFormula(); - assertEquals("Cash_Flow:'Test Sheet'!A1:B2", formula); + assertEquals("'Cash_Flow:Test Sheet'!A1:B2", formula); wb.close(); } |