Author: yegor
Date: Tue Dec 29 19:47:38 2009
New Revision: 894469
URL: http://svn.apache.org/viewvc?rev=894469&view=rev
Log:
improved usermodel to prevent data corruption when setting array formulas,
mimic Excel behaviour and block changing cells included in multi-cell arrays
Modified:
poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetUpdateArrayFormulas.java
poi/trunk/src/testcases/org/apache/poi/hssf/record/aggregates/TestFormulaRecordAggregate.java
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheetUpdateArrayFormulas.java
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetUpdateArrayFormulas.java
Modified:
poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java?rev=894469&r1=894468&r2=894469&view=diff
==============================================================================
---
poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java
(original)
+++
poi/trunk/src/java/org/apache/poi/hssf/record/aggregates/FormulaRecordAggregate.java
Tue Dec 29 19:47:38 2009
@@ -230,7 +230,9 @@
if (_sharedFormulaRecord != null) {
return false;
}
- return _formulaRecord.getFormula().getExpReference() != null;
+ CellReference expRef = _formulaRecord.getFormula().getExpReference();
+ ArrayRecord arec = expRef == null ? null :
_sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol());
+ return arec != null;
}
public CellRangeAddress getArrayFormulaRange() {
@@ -260,6 +262,8 @@
*/
public CellRangeAddress removeArrayFormula(int rowIndex, int
columnIndex) {
CellRangeAddress8Bit a =
_sharedValueManager.removeArrayFormula(rowIndex, columnIndex);
- return new CellRangeAddress(a.getFirstRow(), a.getLastRow(),
a.getFirstColumn(), a.getLastColumn());
+ // at this point FormulaRecordAggregate#isPartOfArrayFormula() should
return false
+ _formulaRecord.setParsedExpression(null);
+ return new CellRangeAddress(a.getFirstRow(), a.getLastRow(),
a.getFirstColumn(), a.getLastColumn());
}
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java?rev=894469&r1=894468&r2=894469&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java Tue Dec 29
19:47:38 2009
@@ -284,6 +284,9 @@
*/
public void setCellType(int cellType) {
notifyFormulaChanging();
+ if(isPartOfArrayFormulaGroup()){
+ notifyArrayFormulaChanging();
+ }
int row=_record.getRow();
short col=_record.getColumn();
short styleIndex=_record.getXFIndex();
@@ -578,6 +581,10 @@
}
public void setCellFormula(String formula) {
+ if(isPartOfArrayFormulaGroup()){
+ notifyArrayFormulaChanging();
+ }
+
int row=_record.getRow();
short col=_record.getColumn();
short styleIndex=_record.getXFIndex();
@@ -1191,4 +1198,40 @@
}
return ((FormulaRecordAggregate)_record).isPartOfArrayFormula();
}
+
+ /**
+ * The purpose of this method is to validate the cell state prior to
modification
+ *
+ * @see #notifyArrayFormulaChanging()
+ */
+ void notifyArrayFormulaChanging(String msg){
+ CellRangeAddress cra = getArrayFormulaRange();
+ if(cra.getNumberOfCells() > 1) {
+ throw new IllegalStateException(msg);
+ }
+ //un-register the single-cell array formula from the parent XSSFSheet
+ getRow().getSheet().removeArrayFormula(this);
+ }
+
+ /**
+ * Called when this cell is modified.
+ * <p>
+ * The purpose of this method is to validate the cell state prior to
modification.
+ * </p>
+ *
+ * @see #setCellType(int)
+ * @see #setCellFormula(String)
+ * @see HSSFRow#removeCell(org.apache.poi.ss.usermodel.Cell)
+ * @see
org.apache.poi.hssf.usermodel.HSSFSheet#removeRow(org.apache.poi.ss.usermodel.Row)
+ * @see org.apache.poi.hssf.usermodel.HSSFSheet#shiftRows(int, int, int)
+ * @see
org.apache.poi.hssf.usermodel.HSSFSheet#addMergedRegion(org.apache.poi.ss.util.CellRangeAddress)
+ * @throws IllegalStateException if modification is not allowed
+ */
+ void notifyArrayFormulaChanging(){
+ CellReference ref = new CellReference(this);
+ String msg = "Cell "+ref.formatAsString()+" is part of a multi-cell
array formula. " +
+ "You cannot change part of an array.";
+ notifyArrayFormulaChanging(msg);
+ }
+
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java?rev=894469&r1=894468&r2=894469&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java Tue Dec 29
19:47:38 2009
@@ -163,6 +163,10 @@
if(column >= cells.length || cell != cells[column]) {
throw new RuntimeException("Specified cell is not from this row");
}
+ if(cell.isPartOfArrayFormulaGroup()){
+ cell.notifyArrayFormulaChanging();
+ }
+
cells[column]=null;
if(alsoRemoveRecords) {
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java?rev=894469&r1=894468&r2=894469&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java Tue Dec 29
19:47:38 2009
@@ -238,6 +238,13 @@
if (row.getSheet() != this) {
throw new IllegalArgumentException("Specified row does not belong
to this sheet");
}
+ for(Cell cell : row) {
+ HSSFCell xcell = (HSSFCell)cell;
+ if(xcell.isPartOfArrayFormulaGroup()){
+ String msg = "Row[rownum="+row.getRowNum()+"] contains cell(s)
included in a multi-cell array formula. You cannot change part of an array.";
+ xcell.notifyArrayFormulaChanging(msg);
+ }
+ }
if (_rows.size() > 0) {
Integer key = Integer.valueOf(row.getRowNum());
@@ -571,12 +578,45 @@
public int addMergedRegion(CellRangeAddress region)
{
region.validate(SpreadsheetVersion.EXCEL97);
+
+ // throw IllegalStateException if the argument CellRangeAddress
intersects with
+ // a multi-cell array formula defined in this sheet
+ validateArrayFormulas(region);
+
return _sheet.addMergedRegion( region.getFirstRow(),
region.getFirstColumn(),
region.getLastRow(),
region.getLastColumn());
}
+ private void validateArrayFormulas(CellRangeAddress region){
+ int firstRow = region.getFirstRow();
+ int firstColumn = region.getFirstColumn();
+ int lastRow = region.getLastRow();
+ int lastColumn = region.getLastColumn();
+ for (int rowIn = firstRow; rowIn <= lastRow; rowIn++) {
+ for (int colIn = firstColumn; colIn <= lastColumn; colIn++) {
+ HSSFRow row = getRow(rowIn);
+ if (row == null) continue;
+
+ HSSFCell cell = row.getCell(colIn);
+ if(cell == null) continue;
+
+ if(cell.isPartOfArrayFormulaGroup()){
+ CellRangeAddress arrayRange = cell.getArrayFormulaRange();
+ if (arrayRange.getNumberOfCells() > 1 &&
+ ( arrayRange.isInRange(region.getFirstRow(),
region.getFirstColumn()) ||
+ arrayRange.isInRange(region.getFirstRow(),
region.getFirstColumn())) ){
+ String msg = "The range " + region.formatAsString() +
" intersects with a multi-cell array formula. " +
+ "You cannot merge cells of an array.";
+ throw new IllegalStateException(msg);
+ }
+ }
+ }
+ }
+
+ }
+
/**
* Whether a record must be inserted or not at generation to indicate that
* formula must be recalculated when workbook is opened.
@@ -1214,6 +1254,11 @@
for ( int rowNum = s; rowNum >= startRow && rowNum <= endRow && rowNum
>= 0 && rowNum < 65536; rowNum += inc ) {
HSSFRow row = getRow( rowNum );
+ // notify all cells in this row that we are going to shift them,
+ // it can throw IllegalStateException if the operation is not
allowed, for example,
+ // if the row contains cells included in a multi-cell array formula
+ if(row != null) notifyRowShifting(row);
+
HSSFRow row2Replace = getRow( rowNum + n );
if ( row2Replace == null )
row2Replace = createRow( rowNum + n );
@@ -1301,6 +1346,17 @@
_sheet.getRecords().addAll(window2Loc, records);
}
+ private void notifyRowShifting(HSSFRow row){
+ String msg = "Row[rownum="+row.getRowNum()+"] contains cell(s)
included in a multi-cell array formula. " +
+ "You cannot change part of an array.";
+ for(Cell cell : row){
+ HSSFCell hcell = (HSSFCell)cell;
+ if(hcell.isPartOfArrayFormulaGroup()){
+ hcell.notifyArrayFormulaChanging(msg);
+ }
+ }
+ }
+
/**
* Creates a split (freezepane). Any existing freezepane or split pane is
overwritten.
* @param colSplit Horizonatal position of split.
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java?rev=894469&r1=894468&r2=894469&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
(original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java Tue
Dec 29 19:47:38 2009
@@ -385,8 +385,13 @@
* @param formula the formula to set, e.g. <code>"SUM(C4:E4)"</code>.
* If the argument is <code>null</code> then the current formula is
removed.
* @throws org.apache.poi.ss.formula.FormulaParseException if the formula
has incorrect syntax or is otherwise invalid
+ * @throws IllegalStateException if the operation is not allowed, for
example,
+ * when the cell is a part of a multi-cell array formula
*/
public void setCellFormula(String formula) {
+ if(isPartOfArrayFormulaGroup()){
+ notifyArrayFormulaChanging();
+ }
setFormula(formula, FormulaType.CELL);
}
@@ -694,6 +699,10 @@
* @see #CELL_TYPE_ERROR
*/
public void setCellType(int cellType) {
+ if(isPartOfArrayFormulaGroup()){
+ notifyArrayFormulaChanging();
+ }
+
int prevType = getCellType();
switch (cellType) {
case CELL_TYPE_BLANK:
@@ -982,4 +991,41 @@
public boolean isPartOfArrayFormulaGroup() {
return getSheet().isCellInArrayFormulaContext(this);
}
+
+ /**
+ * The purpose of this method is to validate the cell state prior to
modification
+ *
+ * @see #notifyArrayFormulaChanging()
+ */
+ void notifyArrayFormulaChanging(String msg){
+ if(isPartOfArrayFormulaGroup()){
+ CellRangeAddress cra = getArrayFormulaRange();
+ if(cra.getNumberOfCells() > 1) {
+ throw new IllegalStateException(msg);
+ }
+ //un-register the single-cell array formula from the parent
XSSFSheet
+ getRow().getSheet().removeArrayFormula(this);
+ }
+ }
+
+ /**
+ * Called when this cell is modified.
+ * <p>
+ * The purpose of this method is to validate the cell state prior to
modification.
+ * </p>
+ *
+ * @see #setCellType(int)
+ * @see #setCellFormula(String)
+ * @see XSSFRow#removeCell(org.apache.poi.ss.usermodel.Cell)
+ * @see
org.apache.poi.xssf.usermodel.XSSFSheet#removeRow(org.apache.poi.ss.usermodel.Row)
+ * @see org.apache.poi.xssf.usermodel.XSSFSheet#shiftRows(int, int, int)
+ * @see
org.apache.poi.xssf.usermodel.XSSFSheet#addMergedRegion(org.apache.poi.ss.util.CellRangeAddress)
+ * @throws IllegalStateException if modification is not allowed
+ */
+ void notifyArrayFormulaChanging(){
+ CellReference ref = new CellReference(this);
+ String msg = "Cell "+ref.formatAsString()+" is part of a multi-cell
array formula. " +
+ "You cannot change part of an array.";
+ notifyArrayFormulaChanging(msg);
+ }
}
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java?rev=894469&r1=894468&r2=894469&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java
(original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java Tue Dec
29 19:47:38 2009
@@ -361,7 +361,15 @@
* @param cell the cell to remove
*/
public void removeCell(Cell cell) {
- _cells.remove(cell.getColumnIndex());
+ if (cell.getRow() != this) {
+ throw new IllegalArgumentException("Specified cell does not belong
to this row");
+ }
+
+ XSSFCell xcell = (XSSFCell)cell;
+ if(xcell.isPartOfArrayFormulaGroup()){
+ xcell.notifyArrayFormulaChanging();
+ }
+ _cells.remove(cell.getColumnIndex());
}
/**
@@ -409,8 +417,13 @@
int rownum = getRowNum() + n;
CalculationChain calcChain =
_sheet.getWorkbook().getCalculationChain();
int sheetId = (int)_sheet.sheet.getSheetId();
+ String msg = "Row[rownum="+getRowNum()+"] contains cell(s) included in
a multi-cell array formula. " +
+ "You cannot change part of an array.";
for(Cell c : this){
XSSFCell cell = (XSSFCell)c;
+ if(cell.isPartOfArrayFormulaGroup()){
+ cell.notifyArrayFormulaChanging(msg);
+ }
//remove the reference in the calculation chain
if(calcChain != null) calcChain.removeItem(sheetId,
cell.getReference());
Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java?rev=894469&r1=894468&r2=894469&view=diff
==============================================================================
--- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
(original)
+++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java Tue
Dec 29 19:47:38 2009
@@ -244,18 +244,50 @@
/**
* Adds a merged region of cells (hence those cells form one).
*
- * @param cra (rowfrom/colfrom-rowto/colto) to merge
+ * @param region (rowfrom/colfrom-rowto/colto) to merge
* @return index of this region
*/
- public int addMergedRegion(CellRangeAddress cra) {
- cra.validate(SpreadsheetVersion.EXCEL2007);
+ public int addMergedRegion(CellRangeAddress region) {
+ region.validate(SpreadsheetVersion.EXCEL2007);
+
+ // throw IllegalStateException if the argument CellRangeAddress
intersects with
+ // a multi-cell array formula defined in this sheet
+ validateArrayFormulas(region);
CTMergeCells ctMergeCells = worksheet.isSetMergeCells() ?
worksheet.getMergeCells() : worksheet.addNewMergeCells();
CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();
- ctMergeCell.setRef(cra.formatAsString());
+ ctMergeCell.setRef(region.formatAsString());
return ctMergeCells.sizeOfMergeCellArray();
}
+ private void validateArrayFormulas(CellRangeAddress region){
+ int firstRow = region.getFirstRow();
+ int firstColumn = region.getFirstColumn();
+ int lastRow = region.getLastRow();
+ int lastColumn = region.getLastColumn();
+ for (int rowIn = firstRow; rowIn <= lastRow; rowIn++) {
+ for (int colIn = firstColumn; colIn <= lastColumn; colIn++) {
+ XSSFRow row = getRow(rowIn);
+ if (row == null) continue;
+
+ XSSFCell cell = row.getCell(colIn);
+ if(cell == null) continue;
+
+ if(cell.isPartOfArrayFormulaGroup()){
+ CellRangeAddress arrayRange = cell.getArrayFormulaRange();
+ if (arrayRange.getNumberOfCells() > 1 &&
+ ( arrayRange.isInRange(region.getFirstRow(),
region.getFirstColumn()) ||
+ arrayRange.isInRange(region.getFirstRow(),
region.getFirstColumn())) ){
+ String msg = "The range " + region.formatAsString() +
" intersects with a multi-cell array formula. " +
+ "You cannot merge cells of an array.";
+ throw new IllegalStateException(msg);
+ }
+ }
+ }
+ }
+
+ }
+
/**
* Adjusts the column width to fit the contents.
*
@@ -1279,7 +1311,13 @@
if (row.getSheet() != this) {
throw new IllegalArgumentException("Specified row does not belong
to this sheet");
}
-
+ for(Cell cell : row) {
+ XSSFCell xcell = (XSSFCell)cell;
+ String msg = "Row[rownum="+row.getRowNum()+"] contains cell(s)
included in a multi-cell array formula. You cannot change part of an array.";
+ if(xcell.isPartOfArrayFormulaGroup()){
+ xcell.notifyArrayFormulaChanging(msg);
+ }
+ }
rows.remove(row.getRowNum());
}
Modified:
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetUpdateArrayFormulas.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetUpdateArrayFormulas.java?rev=894469&r1=894468&r2=894469&view=diff
==============================================================================
---
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetUpdateArrayFormulas.java
(original)
+++
poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheetUpdateArrayFormulas.java
Tue Dec 29 19:47:38 2009
@@ -19,8 +19,7 @@
import junit.framework.AssertionFailedError;
-import org.apache.poi.ss.usermodel.BaseTestSheetUpdateArrayFormulas;
-import org.apache.poi.ss.usermodel.CellRange;
+import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.XSSFITestDataProvider;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
@@ -106,4 +105,5 @@
assertEquals(STCellFormulaType.ARRAY, f.getT());
}
}
+
}
Modified:
poi/trunk/src/testcases/org/apache/poi/hssf/record/aggregates/TestFormulaRecordAggregate.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/aggregates/TestFormulaRecordAggregate.java?rev=894469&r1=894468&r2=894469&view=diff
==============================================================================
---
poi/trunk/src/testcases/org/apache/poi/hssf/record/aggregates/TestFormulaRecordAggregate.java
(original)
+++
poi/trunk/src/testcases/org/apache/poi/hssf/record/aggregates/TestFormulaRecordAggregate.java
Tue Dec 29 19:47:38 2009
@@ -100,5 +100,8 @@
Ptg[] ptg = agg.getFormulaTokens();
String fmlaSer = FormulaRenderer.toFormulaString(null, ptg);
assertEquals(formula, fmlaSer);
- }
+
+ agg.removeArrayFormula(rownum, colnum);
+ assertFalse(agg.isPartOfArrayFormula());
+ }
}
Modified:
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheetUpdateArrayFormulas.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheetUpdateArrayFormulas.java?rev=894469&r1=894468&r2=894469&view=diff
==============================================================================
---
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheetUpdateArrayFormulas.java
(original)
+++
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFSheetUpdateArrayFormulas.java
Tue Dec 29 19:47:38 2009
@@ -27,8 +27,7 @@
import org.apache.poi.hssf.record.aggregates.RowRecordsAggregate;
import org.apache.poi.hssf.record.aggregates.SharedValueManager;
import org.apache.poi.hssf.record.aggregates.TestSharedValueManager;
-import org.apache.poi.ss.usermodel.BaseTestSheetUpdateArrayFormulas;
-import org.apache.poi.ss.usermodel.CellRange;
+import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
/**
Modified:
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetUpdateArrayFormulas.java
URL:
http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetUpdateArrayFormulas.java?rev=894469&r1=894468&r2=894469&view=diff
==============================================================================
---
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetUpdateArrayFormulas.java
(original)
+++
poi/trunk/src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetUpdateArrayFormulas.java
Tue Dec 29 19:47:38 2009
@@ -233,4 +233,264 @@
assertTrue( cell1.isPartOfArrayFormulaGroup());
}
}
+
+ /**
+ * Test that we can set pre-calculated formula result for array formulas
+ */
+ public void testModifyArrayCells_setFormulaResult(){
+ Workbook workbook = _testDataProvider.createWorkbook();
+ Sheet sheet = workbook.createSheet();
+
+ //single-cell array formula
+ CellRange<? extends Cell> srange =
+ sheet.setArrayFormula("SUM(A4:A6,B4:B6)",
CellRangeAddress.valueOf("B5"));
+ Cell scell = srange.getTopLeftCell();
+ assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
+ assertEquals(0.0, scell.getNumericCellValue());
+ scell.setCellValue(1.1);
+ assertEquals(1.1, scell.getNumericCellValue());
+
+ //multi-cell array formula
+ CellRange<? extends Cell> mrange =
+ sheet.setArrayFormula("A1:A3*B1:B3",
CellRangeAddress.valueOf("C1:C3"));
+ for(Cell mcell : mrange){
+ assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
+ assertEquals(0.0, mcell.getNumericCellValue());
+ double fmlaResult = 1.2;
+ mcell.setCellValue(fmlaResult);
+ assertEquals(fmlaResult, mcell.getNumericCellValue());
+ }
+ }
+
+ public void testModifyArrayCells_setCellType(){
+ Workbook workbook = _testDataProvider.createWorkbook();
+ Sheet sheet = workbook.createSheet();
+
+ // single-cell array formulas behave just like normal cells -
+ // changing cell type removes the array formula and associated cached
result
+ CellRange<? extends Cell> srange =
+ sheet.setArrayFormula("SUM(A4:A6,B4:B6)",
CellRangeAddress.valueOf("B5"));
+ Cell scell = srange.getTopLeftCell();
+ assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
+ assertEquals(0.0, scell.getNumericCellValue());
+ scell.setCellType(Cell.CELL_TYPE_STRING);
+ assertEquals(Cell.CELL_TYPE_STRING, scell.getCellType());
+ scell.setCellValue("string cell");
+ assertEquals("string cell", scell.getStringCellValue());
+
+ //once you create a multi-cell array formula, you cannot change the
type of its cells
+ CellRange<? extends Cell> mrange =
+ sheet.setArrayFormula("A1:A3*B1:B3",
CellRangeAddress.valueOf("C1:C3"));
+ for(Cell mcell : mrange){
+ try {
+ assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
+ mcell.setCellType(Cell.CELL_TYPE_NUMERIC);
+ fail("expected exception");
+ } catch (IllegalStateException e){
+ CellReference ref = new CellReference(mcell);
+ String msg = "Cell "+ref.formatAsString()+" is part of a
multi-cell array formula. You cannot change part of an array.";
+ assertEquals(msg, e.getMessage());
+ }
+ // a failed invocation of Cell.setCellType leaves the cell
+ // in the state that it was in prior to the invocation
+ assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
+ assertTrue(mcell.isPartOfArrayFormulaGroup());
+ }
+ }
+
+ public void testModifyArrayCells_setCellFormula(){
+ Workbook workbook = _testDataProvider.createWorkbook();
+ Sheet sheet = workbook.createSheet();
+
+ CellRange<? extends Cell> srange =
+ sheet.setArrayFormula("SUM(A4:A6,B4:B6)",
CellRangeAddress.valueOf("B5"));
+ Cell scell = srange.getTopLeftCell();
+ assertEquals("SUM(A4:A6,B4:B6)", scell.getCellFormula());
+ assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
+ assertTrue(scell.isPartOfArrayFormulaGroup());
+ scell.setCellFormula("SUM(A4,A6)");
+ //we are now a normal formula cell
+ assertEquals("SUM(A4,A6)", scell.getCellFormula());
+ assertFalse(scell.isPartOfArrayFormulaGroup());
+ assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
+ //check that setting formula result works
+ assertEquals(0.0, scell.getNumericCellValue());
+ scell.setCellValue(33.0);
+ assertEquals(33.0, scell.getNumericCellValue());
+
+ //multi-cell array formula
+ CellRange<? extends Cell> mrange =
+ sheet.setArrayFormula("A1:A3*B1:B3",
CellRangeAddress.valueOf("C1:C3"));
+ for(Cell mcell : mrange){
+ //we cannot set individual formulas for cells included in an array
formula
+ try {
+ assertEquals("A1:A3*B1:B3", mcell.getCellFormula());
+ mcell.setCellFormula("A1+A2");
+ fail("expected exception");
+ } catch (IllegalStateException e){
+ CellReference ref = new CellReference(mcell);
+ String msg = "Cell "+ref.formatAsString()+" is part of a
multi-cell array formula. You cannot change part of an array.";
+ assertEquals(msg, e.getMessage());
+ }
+ // a failed invocation of Cell.setCellFormula leaves the cell
+ // in the state that it was in prior to the invocation
+ assertEquals("A1:A3*B1:B3", mcell.getCellFormula());
+ assertTrue(mcell.isPartOfArrayFormulaGroup());
+ }
+ }
+
+ public void testModifyArrayCells_removeCell(){
+ Workbook workbook = _testDataProvider.createWorkbook();
+ Sheet sheet = workbook.createSheet();
+
+ //single-cell array formulas behave just like normal cells
+ CellRangeAddress cra = CellRangeAddress.valueOf("B5");
+ CellRange<? extends Cell> srange =
+ sheet.setArrayFormula("SUM(A4:A6,B4:B6)", cra);
+ Cell scell = srange.getTopLeftCell();
+
+ Row srow = sheet.getRow(cra.getFirstRow());
+ assertSame(srow, scell.getRow());
+ srow.removeCell(scell);
+ assertNull(srow.getCell(cra.getFirstColumn()));
+
+ //re-create the removed cell
+ scell = srow.createCell(cra.getFirstColumn());
+ assertEquals(Cell.CELL_TYPE_BLANK, scell.getCellType());
+ assertFalse(scell.isPartOfArrayFormulaGroup());
+
+ //we cannot remove cells included in a multi-cell array formula
+ CellRange<? extends Cell> mrange =
+ sheet.setArrayFormula("A1:A3*B1:B3",
CellRangeAddress.valueOf("C1:C3"));
+ for(Cell mcell : mrange){
+ int columnIndex = mcell.getColumnIndex();
+ Row mrow = mcell.getRow();
+ try {
+ mrow.removeCell(mcell);
+ fail("expected exception");
+ } catch (IllegalStateException e){
+ CellReference ref = new CellReference(mcell);
+ String msg = "Cell "+ref.formatAsString()+" is part of a
multi-cell array formula. You cannot change part of an array.";
+ assertEquals(msg, e.getMessage());
+ }
+ // a failed invocation of Row.removeCell leaves the row
+ // in the state that it was in prior to the invocation
+ assertSame(mcell, mrow.getCell(columnIndex));
+ assertTrue(mcell.isPartOfArrayFormulaGroup());
+ assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
+ }
+ }
+
+ public void testModifyArrayCells_removeRow(){
+ Workbook workbook = _testDataProvider.createWorkbook();
+ Sheet sheet = workbook.createSheet();
+
+ //single-cell array formulas behave just like normal cells
+ CellRangeAddress cra = CellRangeAddress.valueOf("B5");
+ CellRange<? extends Cell> srange =
+ sheet.setArrayFormula("SUM(A4:A6,B4:B6)", cra);
+ Cell scell = srange.getTopLeftCell();
+ assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
+
+ Row srow = scell.getRow();
+ assertSame(srow, sheet.getRow(cra.getFirstRow()));
+ sheet.removeRow(srow);
+ assertNull(sheet.getRow(cra.getFirstRow()));
+
+ //re-create the removed row and cell
+ scell =
sheet.createRow(cra.getFirstRow()).createCell(cra.getFirstColumn());
+ assertEquals(Cell.CELL_TYPE_BLANK, scell.getCellType());
+ assertFalse(scell.isPartOfArrayFormulaGroup());
+
+ //we cannot remove rows with cells included in a multi-cell array
formula
+ CellRange<? extends Cell> mrange =
+ sheet.setArrayFormula("A1:A3*B1:B3",
CellRangeAddress.valueOf("C1:C3"));
+ for(Cell mcell : mrange){
+ int columnIndex = mcell.getColumnIndex();
+ Row mrow = mcell.getRow();
+ try {
+ sheet.removeRow(mrow);
+ fail("expected exception");
+ } catch (IllegalStateException e){
+ String msg = "Row[rownum="+mrow.getRowNum()+"] contains
cell(s) included in a multi-cell array formula. You cannot change part of an
array.";
+ assertEquals(msg, e.getMessage());
+ }
+ // a failed invocation of Row.removeCell leaves the row
+ // in the state that it was in prior to the invocation
+ assertSame(mrow, sheet.getRow(mrow.getRowNum()));
+ assertSame(mcell, mrow.getCell(columnIndex));
+ assertTrue(mcell.isPartOfArrayFormulaGroup());
+ assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
+ }
+ }
+
+ public void testModifyArrayCells_mergeCells(){
+ Workbook workbook = _testDataProvider.createWorkbook();
+ Sheet sheet = workbook.createSheet();
+ assertEquals(0, sheet.getNumMergedRegions());
+
+ //single-cell array formulas behave just like normal cells
+ CellRange<? extends Cell> srange =
+ sheet.setArrayFormula("SUM(A4:A6,B4:B6)",
CellRangeAddress.valueOf("B5"));
+ Cell scell = srange.getTopLeftCell();
+ sheet.addMergedRegion(CellRangeAddress.valueOf("B5:C6"));
+ //we are still an array formula
+ assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
+ assertTrue(scell.isPartOfArrayFormulaGroup());
+ assertEquals(1, sheet.getNumMergedRegions());
+
+ //we cannot merge cells included in an array formula
+ CellRange<? extends Cell> mrange =
+ sheet.setArrayFormula("A1:A3*B1:B3",
CellRangeAddress.valueOf("C1:C3"));
+ CellRangeAddress cra = CellRangeAddress.valueOf("C1:C3");
+ try {
+ sheet.addMergedRegion(cra);
+ fail("expected exception");
+ } catch (IllegalStateException e){
+ String msg = "The range "+cra.formatAsString()+" intersects with a
multi-cell array formula. You cannot merge cells of an array.";
+ assertEquals(msg, e.getMessage());
+ }
+ //the number of merged regions remains the same
+ assertEquals(1, sheet.getNumMergedRegions());
+ }
+
+ public void testModifyArrayCells_shiftRows(){
+ Workbook workbook = _testDataProvider.createWorkbook();
+ Sheet sheet = workbook.createSheet();
+
+ //single-cell array formulas behave just like normal cells - we can
change the cell type
+ CellRange<? extends Cell> srange =
+ sheet.setArrayFormula("SUM(A4:A6,B4:B6)",
CellRangeAddress.valueOf("B5"));
+ Cell scell = srange.getTopLeftCell();
+ assertEquals("SUM(A4:A6,B4:B6)", scell.getCellFormula());
+ sheet.shiftRows(0, 0, 1);
+ sheet.shiftRows(0, 1, 1);
+
+ //we cannot set individual formulas for cells included in an array
formula
+ CellRange<? extends Cell> mrange =
+ sheet.setArrayFormula("A1:A3*B1:B3",
CellRangeAddress.valueOf("C1:C3"));
+
+ try {
+ sheet.shiftRows(0, 0, 1);
+ fail("expected exception");
+ } catch (IllegalStateException e){
+ String msg = "Row[rownum=0] contains cell(s) included in a
multi-cell array formula. You cannot change part of an array.";
+ assertEquals(msg, e.getMessage());
+ }
+ /*
+ TODO: enable shifting the whole array
+
+ sheet.shiftRows(0, 2, 1);
+ //the array C1:C3 is now C2:C4
+ CellRangeAddress cra = CellRangeAddress.valueOf("C2:C4");
+ for(Cell mcell : mrange){
+ //TODO define equals and hashcode for CellRangeAddress
+ assertEquals(cra.formatAsString(),
mcell.getArrayFormulaRange().formatAsString());
+ assertEquals("A2:A4*B2:B4", mcell.getCellFormula());
+ assertTrue(mcell.isPartOfArrayFormulaGroup());
+ assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
+ }
+
+ */
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
