public final void testShiftWithMergedRegions() {
Workbook wb = _testDataProvider.createWorkbook();
Sheet sheet = wb.createSheet();
Row row = sheet.createRow(0);
row.createCell(0).setCellValue(1.1);
row.createCell(1).setCellValue(2.2);
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
assertEquals("A1:C1", region.formatAsString());
sheet.addMergedRegion(region);
sheet.shiftRows(0, 1, 2);
region = sheet.getMergedRegion(0);
assertEquals("A3:C3", region.formatAsString());
}
public int addConditionalFormatting(
CellRangeAddress[] regions, ConditionalFormattingRule[] cfRules) {
if (regions == null) {
throw new IllegalArgumentException("regions must not be null");
}
for (CellRangeAddress range : regions) range.validate(SpreadsheetVersion.EXCEL2007);
if (cfRules == null) {
throw new IllegalArgumentException("cfRules must not be null");
}
if (cfRules.length == 0) {
throw new IllegalArgumentException("cfRules must not be empty");
}
if (cfRules.length > 3) {
throw new IllegalArgumentException("Number of rules must not exceed 3");
}
CellRangeAddress[] mergeCellRanges = CellRangeUtil.mergeCellRanges(regions);
CTConditionalFormatting cf = _sheet.getCTWorksheet().addNewConditionalFormatting();
List<String> refs = new ArrayList<String>();
for (CellRangeAddress a : mergeCellRanges) refs.add(a.formatAsString());
cf.setSqref(refs);
int priority = 1;
for (CTConditionalFormatting c : _sheet.getCTWorksheet().getConditionalFormattingArray()) {
priority += c.sizeOfCfRuleArray();
}
for (ConditionalFormattingRule rule : cfRules) {
XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule) rule;
xRule.getCTCfRule().setPriority(priority++);
cf.addNewCfRule().set(xRule.getCTCfRule());
}
return _sheet.getCTWorksheet().sizeOfConditionalFormattingArray() - 1;
}
/** Set multi-cell array formula */
public final void testSetArrayFormula_multiCell() {
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(Cell.CELL_TYPE_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());
}
}
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());
}
/** create and remove array formulas */
public final void testRemoveArrayFormula() {
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
assertTrue(Arrays.equals(cr.getFlattenedCells(), dcells.getFlattenedCells()));
for (Cell acell : cr) {
assertFalse(acell.isPartOfArrayFormulaGroup());
assertEquals(Cell.CELL_TYPE_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) {
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());
}
}
}
public String toString() {
StringBuffer buffer = new StringBuffer();
buffer.append("[HYPERLINK RECORD]\n");
buffer.append(" .range = ").append(_range.formatAsString()).append("\n");
buffer.append(" .guid = ").append(_guid.formatAsString()).append("\n");
buffer.append(" .linkOpts= ").append(HexDump.intToHex(_linkOpts)).append("\n");
buffer.append(" .label = ").append(getLabel()).append("\n");
if ((_linkOpts & HLINK_TARGET_FRAME) != 0) {
buffer.append(" .targetFrame= ").append(getTargetFrame()).append("\n");
}
if ((_linkOpts & HLINK_URL) != 0 && _moniker != null) {
buffer.append(" .moniker = ").append(_moniker.formatAsString()).append("\n");
}
if ((_linkOpts & HLINK_PLACE) != 0) {
buffer.append(" .textMark= ").append(getTextMark()).append("\n");
}
buffer.append(" .address = ").append(getAddress()).append("\n");
buffer.append("[/HYPERLINK RECORD]\n");
return buffer.toString();
}
/** Set single-cell array formula */
public final void testSetArrayFormula_singleCell() {
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());
}