/* ==================================================================== Licensed to the Apache Software Foundation (ASF) under one or more contributor license agreements. See the NOTICE file distributed with this work for additional information regarding copyright ownership. The ASF licenses this file to You under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. ==================================================================== */ package org.apache.poi.ss.usermodel; import static org.junit.Assert.assertArrayEquals; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertFalse; import static org.junit.Assert.assertNotNull; import static org.junit.Assert.assertNull; import static org.junit.Assert.assertSame; import static org.junit.Assert.assertTrue; import static org.junit.Assume.assumeTrue; import static org.junit.Assert.fail; import java.io.IOException; import java.util.Arrays; import org.apache.poi.ss.ITestDataProvider; import org.apache.poi.ss.formula.FormulaParseException; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellReference; import org.junit.Ignore; import org.junit.Test; /** * Common superclass for testing usermodel API for array formulas.
* Formula evaluation is not tested here. * * @author Yegor Kozlov * @author Josh Micich */ public abstract class BaseTestSheetUpdateArrayFormulas { protected final ITestDataProvider _testDataProvider; protected BaseTestSheetUpdateArrayFormulas(ITestDataProvider testDataProvider) { _testDataProvider = testDataProvider; } @Test public final void testAutoCreateOtherCells() throws IOException { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet("Sheet1"); Row row1 = sheet.createRow(0); Cell cellA1 = row1.createCell(0); Cell cellB1 = row1.createCell(1); String formula = "42"; sheet.setArrayFormula(formula, CellRangeAddress.valueOf("A1:B2")); assertEquals(formula, cellA1.getCellFormula()); assertEquals(formula, cellB1.getCellFormula()); Row row2 = sheet.getRow(1); assertNotNull(row2); assertEquals(formula, row2.getCell(0).getCellFormula()); assertEquals(formula, row2.getCell(1).getCellFormula()); workbook.close(); } /** * Set single-cell array formula */ @Test public final void testSetArrayFormula_singleCell() throws IOException { Cell[] cells; Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); Cell cell = sheet.createRow(0).createCell(0); assertFalse(cell.isPartOfArrayFormulaGroup()); try { cell.getArrayFormulaRange(); fail("expected exception"); } catch (IllegalStateException e){ assertEquals("Cell A1 is not part of an array formula.", e.getMessage()); } // row 3 does not yet exist assertNull(sheet.getRow(2)); CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2); cells = sheet.setArrayFormula("SUM(C11:C12*D11:D12)", range).getFlattenedCells(); assertEquals(1, cells.length); // sheet.setArrayFormula creates rows and cells for the designated range assertNotNull(sheet.getRow(2)); cell = sheet.getRow(2).getCell(2); assertNotNull(cell); assertTrue(cell.isPartOfArrayFormulaGroup()); //retrieve the range and check it is the same assertEquals(range.formatAsString(), cell.getArrayFormulaRange().formatAsString()); //check the formula assertEquals("SUM(C11:C12*D11:D12)", cell.getCellFormula()); workbook.close(); } /** * Set multi-cell array formula */ @Test public final void testSetArrayFormula_multiCell() throws IOException { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); // multi-cell formula // rows 3-5 don't exist yet assertNull(sheet.getRow(3)); assertNull(sheet.getRow(4)); assertNull(sheet.getRow(5)); CellRangeAddress range = CellRangeAddress.valueOf("C4:C6"); Cell[] cells = sheet.setArrayFormula("SUM(A1:A3*B1:B3)", range).getFlattenedCells(); assertEquals(3, cells.length); // sheet.setArrayFormula creates rows and cells for the designated range assertSame(cells[0], sheet.getRow(3).getCell(2)); assertSame(cells[1], sheet.getRow(4).getCell(2)); assertSame(cells[2], sheet.getRow(5).getCell(2)); for(Cell acell : cells){ assertTrue(acell.isPartOfArrayFormulaGroup()); assertEquals(CellType.FORMULA, acell.getCellTypeEnum()); assertEquals("SUM(A1:A3*B1:B3)", acell.getCellFormula()); //retrieve the range and check it is the same assertEquals(range.formatAsString(), acell.getArrayFormulaRange().formatAsString()); } workbook.close(); } /** * Passing an incorrect formula to sheet.setArrayFormula * should throw FormulaParseException */ @Test public final void testSetArrayFormula_incorrectFormula() throws IOException { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); try { sheet.setArrayFormula("incorrect-formula(C11_C12*D11_D12)", new CellRangeAddress(10, 10, 10, 10)); fail("expected exception"); } catch (FormulaParseException e){ //expected exception } workbook.close(); } /** * Calls of cell.getArrayFormulaRange and sheet.removeArrayFormula * on a not-array-formula cell throw IllegalStateException */ @Test public final void testArrayFormulas_illegalCalls() throws IOException { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); Cell cell = sheet.createRow(0).createCell(0); assertFalse(cell.isPartOfArrayFormulaGroup()); try { cell.getArrayFormulaRange(); fail("expected exception"); } catch (IllegalStateException e){ assertEquals("Cell A1 is not part of an array formula.", e.getMessage()); } try { sheet.removeArrayFormula(cell); fail("expected exception"); } catch (IllegalArgumentException e){ assertEquals("Cell A1 is not part of an array formula.", e.getMessage()); } workbook.close(); } /** * create and remove array formulas */ @Test public final void testRemoveArrayFormula() throws IOException { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); CellRangeAddress range = new CellRangeAddress(3, 5, 2, 2); assertEquals("C4:C6", range.formatAsString()); CellRange cr = sheet.setArrayFormula("SUM(A1:A3*B1:B3)", range); assertEquals(3, cr.size()); // remove the formula cells in C4:C6 CellRange dcells = sheet.removeArrayFormula(cr.getTopLeftCell()); // removeArrayFormula should return the same cells as setArrayFormula assertArrayEquals(cr.getFlattenedCells(), dcells.getFlattenedCells()); for(Cell acell : cr){ assertFalse(acell.isPartOfArrayFormulaGroup()); assertEquals(CellType.BLANK, acell.getCellTypeEnum()); } // cells C4:C6 are not included in array formula, // invocation of sheet.removeArrayFormula on any of them throws IllegalArgumentException for(Cell acell : cr){ try { sheet.removeArrayFormula(acell); fail("expected exception"); } catch (IllegalArgumentException e){ String ref = new CellReference(acell).formatAsString(); assertEquals("Cell "+ref+" is not part of an array formula.", e.getMessage()); } } workbook.close(); } /** * Test that when reading a workbook from input stream, array formulas are recognized */ @Test public final void testReadArrayFormula() throws IOException { Cell[] cells; Workbook workbook1 = _testDataProvider.createWorkbook(); Sheet sheet1 = workbook1.createSheet(); cells = sheet1.setArrayFormula("SUM(A1:A3*B1:B3)", CellRangeAddress.valueOf("C4:C6")).getFlattenedCells(); assertEquals(3, cells.length); cells = sheet1.setArrayFormula("MAX(A1:A3*B1:B3)", CellRangeAddress.valueOf("A4:A6")).getFlattenedCells(); assertEquals(3, cells.length); Sheet sheet2 = workbook1.createSheet(); cells = sheet2.setArrayFormula("MIN(A1:A3*B1:B3)", CellRangeAddress.valueOf("D2:D4")).getFlattenedCells(); assertEquals(3, cells.length); Workbook workbook2 = _testDataProvider.writeOutAndReadBack(workbook1); workbook1.close(); sheet1 = workbook2.getSheetAt(0); for(int rownum=3; rownum <= 5; rownum++) { Cell cell1 = sheet1.getRow(rownum).getCell(2); assertTrue( cell1.isPartOfArrayFormulaGroup()); Cell cell2 = sheet1.getRow(rownum).getCell(0); assertTrue( cell2.isPartOfArrayFormulaGroup()); } sheet2 = workbook2.getSheetAt(1); for(int rownum=1; rownum <= 3; rownum++) { Cell cell1 = sheet2.getRow(rownum).getCell(3); assertTrue( cell1.isPartOfArrayFormulaGroup()); } workbook2.close(); } /** * Test that we can set pre-calculated formula result for array formulas */ @Test public void testModifyArrayCells_setFormulaResult() throws IOException { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); //single-cell array formula CellRange srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5")); Cell scell = srange.getTopLeftCell(); assertEquals(CellType.FORMULA, scell.getCellTypeEnum()); assertEquals(0.0, scell.getNumericCellValue(), 0); scell.setCellValue(1.1); assertEquals(1.1, scell.getNumericCellValue(), 0); //multi-cell array formula CellRange mrange = sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3")); for(Cell mcell : mrange){ assertEquals(CellType.FORMULA, mcell.getCellTypeEnum()); assertEquals(0.0, mcell.getNumericCellValue(), 0); double fmlaResult = 1.2; mcell.setCellValue(fmlaResult); assertEquals(fmlaResult, mcell.getNumericCellValue(), 0); } workbook.close(); } @Test public void testModifyArrayCells_setCellType() throws IOException { 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 srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5")); Cell scell = srange.getTopLeftCell(); assertEquals(CellType.FORMULA, scell.getCellTypeEnum()); assertEquals(0.0, scell.getNumericCellValue(), 0); scell.setCellType(CellType.STRING); assertEquals(CellType.STRING, scell.getCellTypeEnum()); 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 mrange = sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3")); for(Cell mcell : mrange){ try { assertEquals(CellType.FORMULA, mcell.getCellTypeEnum()); mcell.setCellType(CellType.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(CellType.FORMULA, mcell.getCellTypeEnum()); assertTrue(mcell.isPartOfArrayFormulaGroup()); } workbook.close(); } @Test public void testModifyArrayCells_setCellFormula() throws IOException { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); CellRange srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5")); Cell scell = srange.getTopLeftCell(); assertEquals("SUM(A4:A6,B4:B6)", scell.getCellFormula()); assertEquals(CellType.FORMULA, scell.getCellTypeEnum()); 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(CellType.FORMULA, scell.getCellTypeEnum()); //check that setting formula result works assertEquals(0.0, scell.getNumericCellValue(), 0); scell.setCellValue(33.0); assertEquals(33.0, scell.getNumericCellValue(), 0); //multi-cell array formula CellRange 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()); } workbook.close(); } @Test public void testModifyArrayCells_removeCell() throws IOException { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); //single-cell array formulas behave just like normal cells CellRangeAddress cra = CellRangeAddress.valueOf("B5"); CellRange 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(CellType.BLANK, scell.getCellTypeEnum()); assertFalse(scell.isPartOfArrayFormulaGroup()); //we cannot remove cells included in a multi-cell array formula CellRange 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(CellType.FORMULA, mcell.getCellTypeEnum()); } workbook.close(); } @Test public void testModifyArrayCells_removeRow() throws IOException { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); //single-cell array formulas behave just like normal cells CellRangeAddress cra = CellRangeAddress.valueOf("B5"); CellRange srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", cra); Cell scell = srange.getTopLeftCell(); assertEquals(CellType.FORMULA, scell.getCellTypeEnum()); 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(CellType.BLANK, scell.getCellTypeEnum()); assertFalse(scell.isPartOfArrayFormulaGroup()); //we cannot remove rows with cells included in a multi-cell array formula CellRange 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(CellType.FORMULA, mcell.getCellTypeEnum()); } workbook.close(); } @Test public void testModifyArrayCells_mergeCellsSingle() throws IOException { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); assertEquals(0, sheet.getNumMergedRegions()); //single-cell array formulas behave just like normal cells CellRange 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(CellType.FORMULA, scell.getCellTypeEnum()); assertTrue(scell.isPartOfArrayFormulaGroup()); assertEquals(1, sheet.getNumMergedRegions()); workbook.close(); } @Test public void testModifyArrayCells_mergeCellsMulti() throws IOException { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); int expectedNumMergedRegions = 0; assertEquals(expectedNumMergedRegions, sheet.getNumMergedRegions()); // we cannot merge cells included in an array formula sheet.setArrayFormula("A1:A4*B1:B4", CellRangeAddress.valueOf("C2:F5")); for (String ref : Arrays.asList( "C2:F5", // identity "D3:E4", "B1:G6", // contains "B1:C2", "F1:G2", "F5:G6", "B5:C6", // 1x1 corner intersection "B1:C6", "B1:G2", "F1:G6", "B5:G6", // 1-row/1-column intersection "B1:D3", "E1:G3", "E4:G6", "B4:D6", // 2x2 corner intersection "B1:D6", "B1:G3", "E1:G6", "B4:G6" // 2-row/2-column intersection )) { CellRangeAddress cra = CellRangeAddress.valueOf(ref); try { sheet.addMergedRegion(cra); fail("expected exception with ref " + ref); } 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(expectedNumMergedRegions, sheet.getNumMergedRegions()); // we can merge non-intersecting cells for (String ref : Arrays.asList( "C1:F1", //above "G2:G5", //right "C6:F6", //bottom "B2:B5", "H7:J9")) { CellRangeAddress cra = CellRangeAddress.valueOf(ref); try { sheet.addMergedRegion(cra); expectedNumMergedRegions++; assertEquals(expectedNumMergedRegions, sheet.getNumMergedRegions()); } catch (IllegalStateException e) { fail("did not expect exception with ref: " + ref + "\n" + e.getMessage()); } } workbook.close(); } @Test public void testModifyArrayCells_shiftRows() throws IOException { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); //single-cell array formulas behave just like normal cells - we can change the cell type CellRange 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 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(CellType.FORMULA, mcell.getCellTypeEnum()); } */ workbook.close(); } @Ignore @Test public void shouldNotBeAbleToCreateArrayFormulaOnPreexistingMergedRegion() throws IOException { /* * m = merged region * f = array formula * fm = cell belongs to a merged region and an array formula (illegal, that's what this tests for) * * A B C * 1 f f * 2 fm fm * 3 f f */ Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); CellRangeAddress mergedRegion = CellRangeAddress.valueOf("B2:C2"); sheet.addMergedRegion(mergedRegion); CellRangeAddress arrayFormula = CellRangeAddress.valueOf("C1:C3"); assumeTrue(mergedRegion.intersects(arrayFormula)); assumeTrue(arrayFormula.intersects(mergedRegion)); try { sheet.setArrayFormula("SUM(A1:A3)", arrayFormula); fail("expected exception: should not be able to create an array formula that intersects with a merged region"); } catch (IllegalStateException e) { // expected } workbook.close(); } }