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 2015/07/31 23:31:53 UTC
svn commit: r1693654 - in /poi/trunk/src/ooxml:
java/org/apache/poi/xssf/streaming/ java/org/apache/poi/xssf/usermodel/
testcases/org/apache/poi/xssf/streaming/
Author: nick
Date: Fri Jul 31 21:31:53 2015
New Revision: 1693654
URL: http://svn.apache.org/r1693654
Log:
Get basic SXSSF formula evaluation working, for cells/references in the window #58200
Modified:
poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationSheet.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationWorkbook.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFFormulaEvaluator.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/BaseXSSFEvaluationWorkbook.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFFormulaEvaluation.java
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationSheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationSheet.java?rev=1693654&r1=1693653&r2=1693654&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationSheet.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationSheet.java Fri Jul 31 21:31:53 2015
@@ -36,6 +36,9 @@ final class SXSSFEvaluationSheet impleme
public EvaluationCell getCell(int rowIndex, int columnIndex) {
SXSSFRow row = _xs.getRow(rowIndex);
if (row == null) {
+ if (rowIndex <= _xs.getLastFlushedRowNum()) {
+ throw new SXSSFFormulaEvaluator.RowFlushedException(rowIndex);
+ }
return null;
}
SXSSFCell cell = row.getCell(columnIndex);
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationWorkbook.java?rev=1693654&r1=1693653&r2=1693654&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationWorkbook.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFEvaluationWorkbook.java Fri Jul 31 21:31:53 2015
@@ -17,7 +17,11 @@
package org.apache.poi.xssf.streaming;
+import org.apache.poi.ss.formula.EvaluationCell;
import org.apache.poi.ss.formula.EvaluationSheet;
+import org.apache.poi.ss.formula.FormulaParser;
+import org.apache.poi.ss.formula.FormulaType;
+import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.xssf.usermodel.BaseXSSFEvaluationWorkbook;
/**
@@ -46,4 +50,10 @@ public final class SXSSFEvaluationWorkbo
public EvaluationSheet getSheet(int sheetIndex) {
return new SXSSFEvaluationSheet(_uBook.getSheetAt(sheetIndex));
}
+
+ public Ptg[] getFormulaTokens(EvaluationCell evalCell) {
+ SXSSFCell cell = ((SXSSFEvaluationCell)evalCell).getSXSSFCell();
+ SXSSFEvaluationWorkbook frBook = SXSSFEvaluationWorkbook.create(_uBook);
+ return FormulaParser.parse(cell.getCellFormula(), frBook, FormulaType.CELL, _uBook.getSheetIndex(cell.getSheet()));
+ }
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFFormulaEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFFormulaEvaluator.java?rev=1693654&r1=1693653&r2=1693654&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFFormulaEvaluator.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFFormulaEvaluator.java Fri Jul 31 21:31:53 2015
@@ -18,6 +18,9 @@
package org.apache.poi.xssf.streaming;
import org.apache.poi.ss.formula.EvaluationCell;
+import org.apache.poi.ss.formula.IStabilityClassifier;
+import org.apache.poi.ss.formula.WorkbookEvaluator;
+import org.apache.poi.ss.formula.udf.UDFFinder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.util.POILogFactory;
@@ -34,11 +37,27 @@ public class SXSSFFormulaEvaluator exten
private SXSSFWorkbook wb;
public SXSSFFormulaEvaluator(SXSSFWorkbook workbook) {
- super(workbook.getXSSFWorkbook());
+ this(workbook, null, null);
+ }
+ private SXSSFFormulaEvaluator(SXSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) {
+ this(workbook, new WorkbookEvaluator(SXSSFEvaluationWorkbook.create(workbook), stabilityClassifier, udfFinder));
+ }
+ private SXSSFFormulaEvaluator(SXSSFWorkbook workbook, WorkbookEvaluator bookEvaluator) {
+ super(workbook.getXSSFWorkbook(), bookEvaluator);
this.wb = workbook;
}
/**
+ * @param stabilityClassifier used to optimise caching performance. Pass <code>null</code>
+ * for the (conservative) assumption that any cell may have its definition changed after
+ * evaluation begins.
+ * @param udfFinder pass <code>null</code> for default (AnalysisToolPak only)
+ */
+ public static SXSSFFormulaEvaluator create(SXSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) {
+ return new SXSSFFormulaEvaluator(workbook, stabilityClassifier, udfFinder);
+ }
+
+ /**
* Turns a SXSSFCell into a SXSSFEvaluationCell
*/
@Override
@@ -63,7 +82,7 @@ public class SXSSFFormulaEvaluator exten
// Check they're all available
for (int i=0; i<wb.getNumberOfSheets(); i++) {
SXSSFSheet s = wb.getSheetAt(i);
- if (s.isFlushed()) {
+ if (s.areAllRowsFlushed()) {
throw new SheetsFlushedException();
}
}
@@ -73,12 +92,10 @@ public class SXSSFFormulaEvaluator exten
SXSSFSheet s = wb.getSheetAt(i);
// Check if any rows have already been flushed out
- int firstRowNum = s.getFirstRowNum();
- int firstAvailableRowNum = s.iterator().next().getRowNum();
- if (firstRowNum != firstAvailableRowNum) {
- if (skipOutOfWindow) throw new RowsFlushedException();
- logger.log(POILogger.INFO, "Rows from " + firstRowNum + " to" +
- (firstAvailableRowNum-1) + " have already been flushed, skipping");
+ int lastFlushedRowNum = s.getLastFlushedRowNum();
+ if (lastFlushedRowNum > -1) {
+ if (! skipOutOfWindow) throw new RowFlushedException(0);
+ logger.log(POILogger.INFO, "Rows up to " + lastFlushedRowNum + " have already been flushed, skipping");
}
// Evaluate what we have
@@ -109,9 +126,9 @@ public class SXSSFFormulaEvaluator exten
super("One or more sheets have been flushed, cannot evaluate all cells");
}
}
- public static class RowsFlushedException extends IllegalStateException {
- protected RowsFlushedException() {
- super("One or more rows have been flushed, cannot evaluate all cells");
+ public static class RowFlushedException extends IllegalStateException {
+ protected RowFlushedException(int rowNum) {
+ super("Row " + rowNum + " has been flushed, cannot evaluate all cells");
}
}
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java?rev=1693654&r1=1693653&r2=1693654&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java Fri Jul 31 21:31:53 2015
@@ -59,7 +59,8 @@ public class SXSSFSheet implements Sheet
private SheetDataWriter _writer;
private int _randomAccessWindowSize = SXSSFWorkbook.DEFAULT_WINDOW_SIZE;
private int outlineLevelRow = 0;
- private boolean flushed = false;
+ private int lastFlushedRowNumber = -1;
+ private boolean allFlushed = false;
public SXSSFSheet(SXSSFWorkbook workbook, XSSFSheet xSheet) throws IOException {
_workbook = workbook;
@@ -135,7 +136,7 @@ public class SXSSFSheet implements Sheet
initialAllocationSize=10;
SXSSFRow newRow=new SXSSFRow(this,initialAllocationSize);
_rows.put(new Integer(rownum),newRow);
- flushed = false;
+ allFlushed = false;
if(_randomAccessWindowSize>=0&&_rows.size()>_randomAccessWindowSize)
{
try
@@ -1211,7 +1212,7 @@ public class SXSSFSheet implements Sheet
* @param rowIndex the zero based row index to collapse
*/
private void collapseRow(int rowIndex) {
- SXSSFRow row = (SXSSFRow) getRow(rowIndex);
+ SXSSFRow row = getRow(rowIndex);
if(row == null) {
throw new IllegalArgumentException("Invalid row number("+ rowIndex + "). Row does not exist.");
} else {
@@ -1219,7 +1220,7 @@ public class SXSSFSheet implements Sheet
// Hide all the columns until the end of the group
int lastRow = writeHidden(row, startRow, true);
- SXSSFRow lastRowObj = (SXSSFRow) getRow(lastRow);
+ SXSSFRow lastRowObj = getRow(lastRow);
if (lastRowObj != null) {
lastRowObj.setCollapsed(true);
} else {
@@ -1241,7 +1242,7 @@ public class SXSSFSheet implements Sheet
}
int currentRow = rowIndex;
while (getRow(currentRow) != null) {
- if (((SXSSFRow) getRow(currentRow)).getOutlineLevel() < level)
+ if (getRow(currentRow).getOutlineLevel() < level)
return currentRow + 1;
currentRow--;
}
@@ -1250,12 +1251,12 @@ public class SXSSFSheet implements Sheet
private int writeHidden(SXSSFRow xRow, int rowIndex, boolean hidden) {
int level = xRow.getOutlineLevel();
- SXSSFRow currRow = (SXSSFRow) getRow(rowIndex);
+ SXSSFRow currRow = getRow(rowIndex);
while (currRow != null && currRow.getOutlineLevel() >= level) {
currRow.setHidden(hidden);
rowIndex++;
- currRow = (SXSSFRow) getRow(rowIndex);
+ currRow = getRow(rowIndex);
}
return rowIndex;
}
@@ -1466,8 +1467,14 @@ public class SXSSFSheet implements Sheet
/**
* Are all rows flushed to disk?
*/
- public boolean isFlushed() {
- return flushed;
+ public boolean areAllRowsFlushed() {
+ return allFlushed;
+ }
+ /**
+ * @return Last row number to be flushed to disk, or -1 if none flushed yet
+ */
+ public int getLastFlushedRowNum() {
+ return lastFlushedRowNumber;
}
/**
@@ -1478,7 +1485,7 @@ public class SXSSFSheet implements Sheet
public void flushRows(int remaining) throws IOException
{
while(_rows.size() > remaining) flushOneRow();
- if (remaining == 0) flushed = true;
+ if (remaining == 0) allFlushed = true;
}
/**
@@ -1499,6 +1506,7 @@ public class SXSSFSheet implements Sheet
SXSSFRow row = _rows.get(firstRowNum);
_writer.writeRow(rowIndex, row);
_rows.remove(firstRowNum);
+ lastFlushedRowNumber = rowIndex;
}
}
public void changeRowNum(SXSSFRow row, int newRowNum)
@@ -1524,7 +1532,7 @@ public class SXSSFSheet implements Sheet
* @return true if the file was deleted, false if it wasn't.
*/
boolean dispose() throws IOException {
- if (!flushed) flushRows();
+ if (!allFlushed) flushRows();
return _writer.dispose();
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/BaseXSSFEvaluationWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/BaseXSSFEvaluationWorkbook.java?rev=1693654&r1=1693653&r2=1693654&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/BaseXSSFEvaluationWorkbook.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/BaseXSSFEvaluationWorkbook.java Fri Jul 31 21:31:53 2015
@@ -292,11 +292,6 @@ public abstract class BaseXSSFEvaluation
int ix = namePtg.getIndex();
return new Name(_uBook.getNameAt(ix), ix, this);
}
- public Ptg[] getFormulaTokens(EvaluationCell evalCell) {
- XSSFCell cell = ((XSSFEvaluationCell)evalCell).getXSSFCell();
- XSSFEvaluationWorkbook frBook = XSSFEvaluationWorkbook.create(_uBook);
- return FormulaParser.parse(cell.getCellFormula(), frBook, FormulaType.CELL, _uBook.getSheetIndex(cell.getSheet()));
- }
public UDFFinder getUDFFinder(){
return _uBook.getUDFFinder();
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java?rev=1693654&r1=1693653&r2=1693654&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java Fri Jul 31 21:31:53 2015
@@ -17,7 +17,11 @@
package org.apache.poi.xssf.usermodel;
+import org.apache.poi.ss.formula.EvaluationCell;
import org.apache.poi.ss.formula.EvaluationSheet;
+import org.apache.poi.ss.formula.FormulaParser;
+import org.apache.poi.ss.formula.FormulaType;
+import org.apache.poi.ss.formula.ptg.Ptg;
/**
* Internal POI use only
@@ -42,4 +46,10 @@ public final class XSSFEvaluationWorkboo
public EvaluationSheet getSheet(int sheetIndex) {
return new XSSFEvaluationSheet(_uBook.getSheetAt(sheetIndex));
}
+
+ public Ptg[] getFormulaTokens(EvaluationCell evalCell) {
+ XSSFCell cell = ((XSSFEvaluationCell)evalCell).getXSSFCell();
+ XSSFEvaluationWorkbook frBook = XSSFEvaluationWorkbook.create(_uBook);
+ return FormulaParser.parse(cell.getCellFormula(), frBook, FormulaType.CELL, _uBook.getSheetIndex(cell.getSheet()));
+ }
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java?rev=1693654&r1=1693653&r2=1693654&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java (original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java Fri Jul 31 21:31:53 2015
@@ -51,7 +51,10 @@ public class XSSFFormulaEvaluator implem
this(workbook, null, null);
}
private XSSFFormulaEvaluator(XSSFWorkbook workbook, IStabilityClassifier stabilityClassifier, UDFFinder udfFinder) {
- _bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.create(workbook), stabilityClassifier, udfFinder);
+ this(workbook, new WorkbookEvaluator(XSSFEvaluationWorkbook.create(workbook), stabilityClassifier, udfFinder));
+ }
+ protected XSSFFormulaEvaluator(XSSFWorkbook workbook, WorkbookEvaluator bookEvaluator) {
+ _bookEvaluator = bookEvaluator;
_book = workbook;
}
Modified: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFFormulaEvaluation.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFFormulaEvaluation.java?rev=1693654&r1=1693653&r2=1693654&view=diff
==============================================================================
--- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFFormulaEvaluation.java (original)
+++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFFormulaEvaluation.java Fri Jul 31 21:31:53 2015
@@ -28,7 +28,6 @@ import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.SXSSFITestDataProvider;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
-import org.junit.Ignore;
import org.junit.Test;
/**
@@ -38,7 +37,6 @@ import org.junit.Test;
* cell is in the current window, and all references
* from the cell are in the current window
*/
-@Ignore
public final class TestSXSSFFormulaEvaluation {
public static final SXSSFITestDataProvider _testDataProvider = SXSSFITestDataProvider.instance;
@@ -54,24 +52,18 @@ public final class TestSXSSFFormulaEvalu
FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
- // References outside window will fail
s.createRow(0).createCell(0).setCellFormula("1+2");
s.createRow(1).createCell(0).setCellFormula("A21");
- try {
- eval.evaluateAll();
- fail("Evaluate All shouldn't work, as references outside the window");
- } catch(Exception e) {
- System.err.println(e); // TODO
- }
+ for (int i=2; i<19; i++) { s.createRow(i); }
- // Cells outside window will fail
- s.createRow(10).createCell(0).setCellFormula("A1+A2");
+ // Cells outside window will fail, whether referenced or not
+ s.createRow(19).createCell(0).setCellFormula("A1+A2");
s.createRow(20).createCell(0).setCellFormula("A1+A11+100");
try {
eval.evaluateAll();
fail("Evaluate All shouldn't work, as some cells outside the window");
- } catch(Exception e) {
- System.err.println(e); // TODO
+ } catch(SXSSFFormulaEvaluator.RowFlushedException e) {
+ // Expected
}
@@ -97,16 +89,24 @@ public final class TestSXSSFFormulaEvalu
public void testEvaluateRefOutsideWindowFails() {
SXSSFWorkbook wb = new SXSSFWorkbook(5);
SXSSFSheet s = wb.createSheet();
+
s.createRow(0).createCell(0).setCellFormula("1+2");
+ assertEquals(false, s.areAllRowsFlushed());
+ assertEquals(-1, s.getLastFlushedRowNum());
+
+ for (int i=1; i<=19; i++) { s.createRow(i); }
Cell c = s.createRow(20).createCell(0);
c.setCellFormula("A1+100");
+ assertEquals(false, s.areAllRowsFlushed());
+ assertEquals(15, s.getLastFlushedRowNum());
+
FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
try {
eval.evaluateFormulaCell(c);
fail("Evaluate shouldn't work, as reference outside the window");
- } catch(Exception e) {
- System.err.println(e); // TODO
+ } catch(SXSSFFormulaEvaluator.RowFlushedException e) {
+ // Expected
}
}
@@ -163,7 +163,6 @@ public final class TestSXSSFFormulaEvalu
c = s.createRow(1).createCell(0);
c.setCellFormula("CONCATENATE(\"hello\",\" \",\"world\")");
- assertEquals("", c.getStringCellValue());
eval.evaluateFormulaCell(c);
assertEquals("hello world", c.getStringCellValue());
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org