You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ni...@apache.org on 2014/07/31 17:07:27 UTC
svn commit: r1614916 - in /poi/trunk:
src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java
src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
test-data/spreadsheet/56502.xlsx
Author: nick
Date: Thu Jul 31 15:07:27 2014
New Revision: 1614916
URL: http://svn.apache.org/r1614916
Log:
Fix bug #56502 - When shifting XSSF rows with formula cells, if the formula can't be parsed, log + leave it unchanged rather than failing
Added:
poi/trunk/test-data/spreadsheet/56502.xlsx (with props)
Modified:
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java?rev=1614916&r1=1614915&r2=1614916&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/helpers/XSSFRowShifter.java Thu Jul 31 15:07:27 2014
@@ -20,6 +20,7 @@ package org.apache.poi.xssf.usermodel.he
import java.util.ArrayList;
import java.util.List;
+import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaRenderer;
import org.apache.poi.ss.formula.FormulaShifter;
@@ -30,6 +31,8 @@ import org.apache.poi.ss.formula.ptg.Ptg
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.util.POILogFactory;
+import org.apache.poi.util.POILogger;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
import org.apache.poi.xssf.usermodel.XSSFName;
@@ -43,9 +46,10 @@ import org.openxmlformats.schemas.spread
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
/**
- * @author Yegor Kozlov
+ * Helper for shifting rows up or down
*/
public final class XSSFRowShifter {
+ private static POILogger logger = POILogFactory.getLogger(XSSFRowShifter.class);
private final XSSFSheet sheet;
public XSSFRowShifter(XSSFSheet sh) {
@@ -194,12 +198,19 @@ public final class XSSFRowShifter {
XSSFWorkbook wb = sheet.getWorkbook();
int sheetIndex = wb.getSheetIndex(sheet);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
- Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex);
- String shiftedFmla = null;
- if (shifter.adjustFormula(ptgs, sheetIndex)) {
- shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
+
+ try {
+ Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex);
+ String shiftedFmla = null;
+ if (shifter.adjustFormula(ptgs, sheetIndex)) {
+ shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
+ }
+ return shiftedFmla;
+ } catch (FormulaParseException fpe) {
+ // Log, but don't change, rather than breaking
+ logger.log(POILogger.WARN, "Error shifting formula on row ", row.getRowNum(), fpe);
+ return formula;
}
- return shiftedFmla;
}
public void updateConditionalFormatting(FormulaShifter shifter) {
Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java?rev=1614916&r1=1614915&r2=1614916&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java Thu Jul 31 15:07:27 2014
@@ -1787,6 +1787,10 @@ public final class TestXSSFBugs extends
checkValue(excel, eval.getStringValue() + ".0");
}
+ /**
+ * New hyperlink with no initial cell reference, still need
+ * to be able to change it
+ */
@Test
public void testBug56527() {
XSSFWorkbook wb = new XSSFWorkbook();
@@ -1816,6 +1820,30 @@ public final class TestXSSFBugs extends
assertEquals(5, hyperlink.getLastRow());
assertEquals(3, hyperlink.getLastColumn());
}
+
+ /**
+ * Shifting rows with a formula that references a
+ * function in another file
+ */
+ @Test
+ public void bug56502() throws Exception {
+ Workbook wb = XSSFTestDataSamples.openSampleWorkbook("56502.xlsx");
+ Sheet sheet = wb.getSheetAt(0);
+
+ Cell cFunc = sheet.getRow(3).getCell(0);
+ assertEquals("[1]!LUCANET(\"Ist\")", cFunc.getCellFormula());
+ Cell cRef = sheet.getRow(3).createCell(1);
+ cRef.setCellFormula("A3");
+
+ // Shift it down one row
+ sheet.shiftRows(1, sheet.getLastRowNum(), 1);
+
+ // Check the new formulas: Function won't change, Reference will
+ cFunc = sheet.getRow(4).getCell(0);
+ assertEquals("[1]!LUCANET(\"Ist\")", cFunc.getCellFormula());
+ cRef = sheet.getRow(4).getCell(1);
+ assertEquals("A4", cRef.getCellFormula());
+ }
private void checkValue(XSSFWorkbook excel, String expect) {
XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(excel);
Added: poi/trunk/test-data/spreadsheet/56502.xlsx
URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/56502.xlsx?rev=1614916&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/test-data/spreadsheet/56502.xlsx
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org