| /* ==================================================================== |
| 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.jupiter.api.Assertions.assertArrayEquals; |
| import static org.junit.jupiter.api.Assertions.assertEquals; |
| import static org.junit.jupiter.api.Assertions.assertFalse; |
| import static org.junit.jupiter.api.Assertions.assertNotNull; |
| import static org.junit.jupiter.api.Assertions.assertNull; |
| import static org.junit.jupiter.api.Assertions.assertSame; |
| import static org.junit.jupiter.api.Assertions.assertThrows; |
| import static org.junit.jupiter.api.Assertions.assertTrue; |
| import static org.junit.jupiter.api.Assumptions.assumeTrue; |
| |
| 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.jupiter.api.Disabled; |
| import org.junit.jupiter.api.Test; |
| |
| /** |
| * Common superclass for testing usermodel API for array formulas.<br> |
| * Formula evaluation is not tested here. |
| */ |
| public abstract class BaseTestSheetUpdateArrayFormulas { |
| protected final ITestDataProvider _testDataProvider; |
| |
| protected BaseTestSheetUpdateArrayFormulas(ITestDataProvider testDataProvider) { |
| _testDataProvider = testDataProvider; |
| } |
| |
| @Test |
| public final void testAutoCreateOtherCells() throws IOException { |
| try (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()); |
| } |
| } |
| |
| /** |
| * Set single-cell array formula |
| */ |
| @Test |
| public final void testSetArrayFormula_singleCell() throws IOException { |
| try (Workbook workbook = _testDataProvider.createWorkbook()) { |
| Sheet sheet = workbook.createSheet(); |
| Cell cell = sheet.createRow(0).createCell(0); |
| assertFalse(cell.isPartOfArrayFormulaGroup()); |
| IllegalStateException e = assertThrows(IllegalStateException.class, cell::getArrayFormulaRange); |
| assertEquals("Cell Sheet0!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); |
| Cell[] 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()); |
| } |
| } |
| |
| /** |
| * Set multi-cell array formula |
| */ |
| @Test |
| public final void testSetArrayFormula_multiCell() throws IOException { |
| try (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.getCellType()); |
| assertEquals("SUM(A1:A3*B1:B3)", acell.getCellFormula()); |
| //retrieve the range and check it is the same |
| assertEquals(range.formatAsString(), acell.getArrayFormulaRange().formatAsString()); |
| } |
| } |
| } |
| |
| /** |
| * Passing an incorrect formula to sheet.setArrayFormula |
| * should throw FormulaParseException |
| */ |
| @Test |
| public final void testSetArrayFormula_incorrectFormula() throws IOException { |
| try (Workbook workbook = _testDataProvider.createWorkbook()) { |
| Sheet sheet = workbook.createSheet(); |
| CellRangeAddress cra = new CellRangeAddress(10, 10, 10, 10); |
| assertThrows(FormulaParseException.class, () -> sheet.setArrayFormula("incorrect-formula(C11_C12*D11_D12)", cra)); |
| } |
| } |
| |
| /** |
| * Calls of cell.getArrayFormulaRange and sheet.removeArrayFormula |
| * on a not-array-formula cell throw IllegalStateException |
| */ |
| @Test |
| public final void testArrayFormulas_illegalCalls() throws IOException { |
| try (Workbook workbook = _testDataProvider.createWorkbook()) { |
| Sheet sheet = workbook.createSheet(); |
| |
| Cell cell = sheet.createRow(0).createCell(0); |
| assertFalse(cell.isPartOfArrayFormulaGroup()); |
| IllegalStateException e = assertThrows(IllegalStateException.class, cell::getArrayFormulaRange); |
| assertEquals("Cell Sheet0!A1 is not part of an array formula.", e.getMessage()); |
| |
| IllegalArgumentException e2 = assertThrows(IllegalArgumentException.class, () -> sheet.removeArrayFormula(cell)); |
| assertEquals("Cell Sheet0!A1 is not part of an array formula.", e.getMessage()); |
| } |
| } |
| |
| /** |
| * create and remove array formulas |
| */ |
| @Test |
| public final void testRemoveArrayFormula() throws IOException { |
| try (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.getCellType()); |
| } |
| |
| // cells C4:C6 are not included in array formula, |
| // invocation of sheet.removeArrayFormula on any of them throws IllegalArgumentException |
| for (Cell acell : cr) { |
| IllegalArgumentException e = assertThrows(IllegalArgumentException.class, () -> sheet.removeArrayFormula(acell)); |
| String ref = new CellReference(acell).formatAsString(); |
| assertEquals("Cell " + ref + " is not part of an array formula.", e.getMessage()); |
| } |
| } |
| } |
| |
| /** |
| * Test that when reading a workbook from input stream, array formulas are recognized |
| */ |
| @Test |
| public final void testReadArrayFormula() throws IOException { |
| try (Workbook workbook1 = _testDataProvider.createWorkbook()) { |
| Sheet sheet1 = workbook1.createSheet(); |
| Cell[] 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); |
| |
| try (Workbook workbook2 = _testDataProvider.writeOutAndReadBack(workbook1)) { |
| 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()); |
| } |
| } |
| } |
| } |
| |
| /** |
| * Test that we can set pre-calculated formula result for array formulas |
| */ |
| @Test |
| void testModifyArrayCells_setFormulaResult() throws IOException { |
| try (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(CellType.FORMULA, scell.getCellType()); |
| assertEquals(0.0, scell.getNumericCellValue(), 0); |
| scell.setCellValue(1.1); |
| assertEquals(1.1, scell.getNumericCellValue(), 0); |
| |
| //multi-cell array formula |
| CellRange<? extends Cell> mrange = |
| sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3")); |
| for (Cell mcell : mrange) { |
| assertEquals(CellType.FORMULA, mcell.getCellType()); |
| assertEquals(0.0, mcell.getNumericCellValue(), 0); |
| double fmlaResult = 1.2; |
| mcell.setCellValue(fmlaResult); |
| assertEquals(fmlaResult, mcell.getNumericCellValue(), 0); |
| } |
| } |
| } |
| |
| @Test |
| void testModifyArrayCells_setCellType() throws IOException { |
| try (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(CellType.FORMULA, scell.getCellType()); |
| assertEquals(0.0, scell.getNumericCellValue(), 0); |
| scell.setCellType(CellType.STRING); |
| assertEquals(CellType.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) { |
| assertEquals(CellType.FORMULA, mcell.getCellType()); |
| IllegalStateException e = assertThrows(IllegalStateException.class, () -> mcell.setCellType(CellType.NUMERIC)); |
| 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.getCellType()); |
| assertTrue(mcell.isPartOfArrayFormulaGroup()); |
| } |
| } |
| } |
| |
| @Test |
| void testModifyArrayCells_setCellFormula() throws IOException { |
| try (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(CellType.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(CellType.FORMULA, scell.getCellType()); |
| //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<? 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 |
| assertEquals("A1:A3*B1:B3", mcell.getCellFormula()); |
| IllegalStateException e = assertThrows(IllegalStateException.class, () -> mcell.setCellFormula("A1+A2")); |
| 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()); |
| } |
| } |
| } |
| |
| @Test |
| void testModifyArrayCells_removeCell() throws IOException { |
| try (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(CellType.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(); |
| IllegalStateException e = assertThrows(IllegalStateException.class, () -> mrow.removeCell(mcell)); |
| 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.getCellType()); |
| } |
| |
| } |
| } |
| |
| @Test |
| void testModifyArrayCells_removeRow() throws IOException { |
| try (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(CellType.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(CellType.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(); |
| assertThrows(IllegalStateException.class, () -> sheet.removeRow(mrow)); |
| // 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.getCellType()); |
| } |
| } |
| } |
| |
| @Test |
| void testModifyArrayCells_mergeCellsSingle() throws IOException { |
| try (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(); |
| assertEquals(0, sheet.addMergedRegion(CellRangeAddress.valueOf("B5:C6"))); |
| //we are still an array formula |
| assertEquals(CellType.FORMULA, scell.getCellType()); |
| assertTrue(scell.isPartOfArrayFormulaGroup()); |
| assertEquals(1, sheet.getNumMergedRegions()); |
| } |
| } |
| |
| @Test |
| void testModifyArrayCells_mergeCellsMulti() throws IOException { |
| try (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); |
| IllegalStateException e = assertThrows(IllegalStateException.class, () -> sheet.addMergedRegion(cra)); |
| 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); |
| sheet.addMergedRegion(cra); |
| expectedNumMergedRegions++; |
| assertEquals(expectedNumMergedRegions, sheet.getNumMergedRegions()); |
| } |
| |
| } |
| } |
| |
| @Test |
| void testModifyArrayCells_shiftRows() throws IOException { |
| try (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 |
| sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3")); |
| IllegalStateException e = assertThrows(IllegalStateException.class, () -> sheet.shiftRows(0, 0, 1)); |
| 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.getCellType()); |
| } |
| |
| */ |
| } |
| } |
| |
| @Disabled("See bug 59728") |
| @Test |
| 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 |
| */ |
| try (Workbook workbook = _testDataProvider.createWorkbook()) { |
| Sheet sheet = workbook.createSheet(); |
| |
| CellRangeAddress mergedRegion = CellRangeAddress.valueOf("B2:C2"); |
| assertEquals(0, sheet.addMergedRegion(mergedRegion)); |
| CellRangeAddress arrayFormula = CellRangeAddress.valueOf("C1:C3"); |
| assumeTrue(mergedRegion.intersects(arrayFormula)); |
| assumeTrue(arrayFormula.intersects(mergedRegion)); |
| // expected exception: should not be able to create an array formula that intersects with a merged region |
| assertThrows(IllegalStateException.class, () -> sheet.setArrayFormula("SUM(A1:A3)", arrayFormula)); |
| } |
| } |
| } |
