protected final String formatReferenceAsString() {
CellReference topLeft =
new CellReference(
getFirstRow(), getFirstColumn(), !isFirstRowRelative(), !isFirstColRelative());
CellReference botRight =
new CellReference(
getLastRow(), getLastColumn(), !isLastRowRelative(), !isLastColRelative());
if (AreaReference.isWholeColumnReference(SpreadsheetVersion.EXCEL97, topLeft, botRight)) {
return (new AreaReference(topLeft, botRight)).formatAsString();
}
return topLeft.formatAsString() + ":" + botRight.formatAsString();
}
public void printWorksheetContent() throws Exception {
FrontDesk desk = FrontDesk.getInstance();
// read policy & rule
DynamicPolicy policy = SifMarshaller.unmarshal(new File(policyPath));
@SuppressWarnings("unchecked")
DynamicInspectionRequest<Workbook> req =
(DynamicInspectionRequest<Workbook>)
desk.requestNewDynamicInspection("fubar", new File(filepath));
FrontDesk.getInstance().scan();
FrontDesk.getInstance().register(policy);
FrontDesk.getInstance().setPolicy(policy);
DynamicPolicyRule rule = (DynamicPolicyRule) policy.getRuleByName("greaterThan0");
assertTrue("No rule with name \"greaterThan0\" was found", rule != null);
// write TestInput in PoiWorkbook
POIWriter writer = new POIWriter();
writer.insertTestInput(rule, req.getExternalSpreadsheet());
Workbook wb = req.getExternalSpreadsheet();
Sheet sheet1 = wb.getSheetAt(0);
for (Row row : sheet1) {
for (Cell cell : row) {
CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
System.out.print(row.getRowNum() + ", " + cell.getColumnIndex());
System.out.print(" - ");
System.out.print(cellRef.formatAsString());
System.out.print(" - ");
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(cell.getDateCellValue());
} else {
System.out.println(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.println(cell.getCellFormula());
break;
default:
System.out.println();
}
}
}
}
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_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_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 static String offsetCellIndex(String cellIndex, int rowOffset, int colOffset) {
CellReference cell = new CellReference(cellIndex);
CellReference newCell = new CellReference(cell.getRow() + rowOffset, cell.getCol() + colOffset);
return newCell.formatAsString().replaceAll("\\$", "");
}
public static String getCellIndex(int row, int col) {
CellReference cell = new CellReference(row, col);
return cell.formatAsString().replaceAll("\\$", "");
}
