From 04be6fcccb723e54b38ef02a4fec746068043534 Mon Sep 17 00:00:00 2001 From: PJ Fanning Date: Mon, 23 May 2022 10:55:22 +0000 Subject: [PATCH] [bug-66022] issue in Formula Parser with some sheet names git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1901162 13f79535-47bb-0310-9956-ffa450edef68 --- .../xssf/usermodel/TestXSSFFormulaParser.java | 19 +++++++++++++++++++ .../apache/poi/ss/formula/FormulaParser.java | 18 ++++++++++-------- .../poi/hssf/model/TestFormulaParser.java | 5 +++++ 3 files changed, 34 insertions(+), 8 deletions(-) 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 16a42dab55..6f49ef11cd 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 @@ -407,6 +407,7 @@ public final class TestXSSFFormulaParser { Sheet sheet1 = wb.createSheet("Sheet1"); Sheet sheet2 = wb.createSheet("Sheet2"); Sheet sheet3 = wb.createSheet("Sheet 3"); + Sheet sheet4 = wb.createSheet("Sheet4>"); Row tempRow = sheet1.createRow(0); tempRow.createCell(0).setCellValue(1); @@ -420,6 +421,10 @@ public final class TestXSSFFormulaParser { tempRow.createCell(0).setCellValue(5); tempRow.createCell(1).setCellValue(6); + tempRow = sheet4.createRow(0); + tempRow.createCell(0).setCellValue(5); + tempRow.createCell(1).setCellValue(6); + Cell cell = tempRow.createCell(2); // unquoted sheet names @@ -441,6 +446,20 @@ public final class TestXSSFFormulaParser { cell.setCellFormula(formula); cellFormula = cell.getCellFormula(); assertEquals(formula, cellFormula); + + // quoted sheet names with special character + cell = tempRow.createCell(5); + formula = "SUM('Sheet1:Sheet4>'!A1:B1)"; + cell.setCellFormula(formula); + cellFormula = cell.getCellFormula(); + assertEquals(formula, cellFormula); + + // quoted sheet names with special character #2 +// cell = tempRow.createCell(6); +// formula = "SUM('Sheet 3:Sheet4>'!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 4e7f1db0e9..f362828819 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 @@ -465,7 +465,7 @@ public final class FormulaParser { private ParseNode parseRangeable() { SkipWhite(); int savePointer = _pointer; - SheetIdentifier sheetIden = parseSheetName(); + SheetIdentifier sheetIden = parseSheetName(false); if (sheetIden == null) { resetPointer(savePointer); @@ -1154,7 +1154,7 @@ public final class FormulaParser { * Note - caller should reset {@link #_pointer} upon {@code null} result * @return The sheet name as an identifier {@code null} if '!' is not found in the right place */ - private SheetIdentifier parseSheetName() { + private SheetIdentifier parseSheetName(boolean isSndPartOfQuotedRange) { String bookName; if (look == '[') { bookName = getBookName(); @@ -1162,8 +1162,10 @@ public final class FormulaParser { bookName = null; } - if (look == '\'') { - Match('\''); + if (look == '\'' || isSndPartOfQuotedRange) { + if (!isSndPartOfQuotedRange) { + Match('\''); + } if (look == '[') bookName = getBookName(); @@ -1197,7 +1199,7 @@ public final class FormulaParser { } // See if it's a multi-sheet range, eg Sheet1:Sheet3!A1 if (look == ':') { - return parseSheetRange(bookName, iden); + return parseSheetRange(bookName, iden, true); } return null; } @@ -1221,7 +1223,7 @@ public final class FormulaParser { } // See if it's a multi-sheet range, eg Sheet1:Sheet3!A1 if (look == ':') { - return parseSheetRange(bookName, iden); + return parseSheetRange(bookName, iden, false); } return null; } @@ -1237,9 +1239,9 @@ public final class FormulaParser { * If we have something that looks like [book]Sheet1: or * Sheet1, see if it's actually a range eg Sheet1:Sheet2! */ - private SheetIdentifier parseSheetRange(String bookname, NameIdentifier sheet1Name) { + private SheetIdentifier parseSheetRange(String bookname, NameIdentifier sheet1Name, boolean isSndPartOfQuotedRange) { GetChar(); - SheetIdentifier sheet2 = parseSheetName(); + SheetIdentifier sheet2 = parseSheetName(isSndPartOfQuotedRange); if (sheet2 != null) { return new SheetRangeIdentifier(bookname, sheet1Name, sheet2.getSheetIdentifier()); } 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 4122f4734c..f10d035d6c 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 @@ -515,6 +515,7 @@ final class TestFormulaParser { wb.createSheet("Cash_Flow"); wb.createSheet("Test Sheet"); + wb.createSheet("Sheet 3>"); HSSFSheet sheet = wb.createSheet("Test"); HSSFRow row = sheet.createRow(0); @@ -539,6 +540,10 @@ final class TestFormulaParser { formula = cell.getCellFormula(); assertEquals("'Cash_Flow:Test Sheet'!A1", formula); + // special character + cell.setCellFormula("'Cash_Flow:Sheet 3>'!A1"); + formula = cell.getCellFormula(); + assertEquals("'Cash_Flow:Sheet 3>'!A1", formula); // References to a range (area) of cells: -- 2.39.5