You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by fa...@apache.org on 2021/11/02 18:59:18 UTC
svn commit: r1894695 - in /poi/trunk:
poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/
poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/
poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/
poi/src/main/java/org/apache/poi/ss/formula/ poi/...
Author: fanningpj
Date: Tue Nov 2 18:59:18 2021
New Revision: 1894695
URL: http://svn.apache.org/viewvc?rev=1894695&view=rev
Log:
[github-269] more work on external workbook links (formulas). Thanks to @aspojo. This closes #269
Modified:
poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFCreationHelper.java
poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFCell.java
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/ptg/AreaPtgBase.java
poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/ptg/ExternSheetNameResolver.java
poi/trunk/poi/src/test/java/org/apache/poi/hssf/model/TestFormulaParser.java
Modified: poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFCreationHelper.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFCreationHelper.java?rev=1894695&r1=1894694&r2=1894695&view=diff
==============================================================================
--- poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFCreationHelper.java (original)
+++ poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/streaming/SXSSFCreationHelper.java Tue Nov 2 18:59:18 2021
@@ -61,6 +61,7 @@ public class SXSSFCreationHelper impleme
@Override
public SXSSFFormulaEvaluator createFormulaEvaluator() {
+
return new SXSSFFormulaEvaluator(wb);
}
Modified: poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java?rev=1894695&r1=1894694&r2=1894695&view=diff
==============================================================================
--- poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java (original)
+++ poi/trunk/poi-ooxml/src/main/java/org/apache/poi/xssf/usermodel/XSSFCell.java Tue Nov 2 18:59:18 2021
@@ -29,6 +29,7 @@ import org.apache.poi.ss.formula.Formula
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 Cell
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;
Modified: poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFCell.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFCell.java?rev=1894695&r1=1894694&r2=1894695&view=diff
==============================================================================
--- poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFCell.java (original)
+++ poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFCell.java Tue Nov 2 18:59:18 2021
@@ -563,14 +563,14 @@ public final class TestXSSFCell extends
@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
Modified: poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java?rev=1894695&r1=1894694&r2=1894695&view=diff
==============================================================================
--- poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java (original)
+++ poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java Tue Nov 2 18:59:18 2021
@@ -186,8 +186,8 @@ public final class TestXSSFFormulaEvalua
// 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());
Modified: poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java?rev=1894695&r1=1894694&r2=1894695&view=diff
==============================================================================
--- poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java (original)
+++ poi/trunk/poi-ooxml/src/test/java/org/apache/poi/xssf/usermodel/TestXSSFFormulaParser.java Tue Nov 2 18:59:18 2021
@@ -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);
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java?rev=1894695&r1=1894694&r2=1894695&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/FormulaParser.java Tue Nov 2 18:59:18 2021
@@ -1171,10 +1171,18 @@ public final class FormulaParser {
while(!done) {
sb.appendCodePoint(look);
GetChar();
- if(look == '\'')
- {
- Match('\'');
- done = look != '\'';
+ switch (look){
+ case '\'' : {
+ GetChar();
+ if (look == '\''){
+ // Any single quotes which were already present in the sheet name will be converted to double single quotes ('')
+ // so switch back to single quote
+ GetChar();
+ break;
+ }
+ }
+ case ':':
+ done = true;
}
}
@@ -1200,6 +1208,9 @@ public final class FormulaParser {
sb.appendCodePoint(look);
GetChar();
}
+ if (look == '\'') {
+ GetChar();
+ }
NameIdentifier iden = new NameIdentifier(sb.toString(), false);
SkipWhite();
if (look == '!') {
@@ -1249,6 +1260,7 @@ public final class FormulaParser {
switch(ch) {
case '.': // dot is OK
case '_': // underscore is OK
+ case ' ': // space is OK
return true;
}
return false;
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/ptg/AreaPtgBase.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/ptg/AreaPtgBase.java?rev=1894695&r1=1894694&r2=1894695&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/ptg/AreaPtgBase.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/ptg/AreaPtgBase.java Tue Nov 2 18:59:18 2021
@@ -290,6 +290,9 @@ public abstract class AreaPtgBase extend
if(AreaReference.isWholeColumnReference(SpreadsheetVersion.EXCEL97, topLeft, botRight)) {
return (new AreaReference(topLeft, botRight, SpreadsheetVersion.EXCEL97)).formatAsString();
}
+ if (AreaReference.isWholeColumnReference(SpreadsheetVersion.EXCEL2007, topLeft, botRight)){
+ return new AreaReference(topLeft, botRight,SpreadsheetVersion.EXCEL2007).formatAsString();
+ }
return topLeft.formatAsString() + ":" + botRight.formatAsString();
}
Modified: poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/ptg/ExternSheetNameResolver.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/ptg/ExternSheetNameResolver.java?rev=1894695&r1=1894694&r2=1894695&view=diff
==============================================================================
--- poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/ptg/ExternSheetNameResolver.java (original)
+++ poi/trunk/poi/src/main/java/org/apache/poi/ss/formula/ptg/ExternSheetNameResolver.java Tue Nov 2 18:59:18 2021
@@ -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 temp = new StringBuilder();
+ SheetNameFormatter.appendFormat(temp, r.getLastSheetName());
+ char quote = '\'';
+ if (temp.charAt(0) == quote){
+ sb.insert(0 , quote);
+ sb.append(temp.substring(1));
+ }else {
+ sb.append(temp);
+ }
}
}
} else {
Modified: poi/trunk/poi/src/test/java/org/apache/poi/hssf/model/TestFormulaParser.java
URL: http://svn.apache.org/viewvc/poi/trunk/poi/src/test/java/org/apache/poi/hssf/model/TestFormulaParser.java?rev=1894695&r1=1894694&r2=1894695&view=diff
==============================================================================
--- poi/trunk/poi/src/test/java/org/apache/poi/hssf/model/TestFormulaParser.java (original)
+++ poi/trunk/poi/src/test/java/org/apache/poi/hssf/model/TestFormulaParser.java Tue Nov 2 18:59:18 2021
@@ -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();
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org