public void testShiftWithComments() { // TODO - enable XSSF test
Workbook wb =
_testDataProvider.openSampleWorkbook(
"comments." + _testDataProvider.getStandardFileNameExtension());
Sheet sheet = wb.getSheet("Sheet1");
assertEquals(3, sheet.getLastRowNum());
// Verify comments are in the position expected
assertNotNull(sheet.getCellComment(0, 0));
assertNull(sheet.getCellComment(1, 0));
assertNotNull(sheet.getCellComment(2, 0));
assertNotNull(sheet.getCellComment(3, 0));
String comment1 = sheet.getCellComment(0, 0).getString().getString();
assertEquals(comment1, "comment top row1 (index0)\n");
String comment3 = sheet.getCellComment(2, 0).getString().getString();
assertEquals(comment3, "comment top row3 (index2)\n");
String comment4 = sheet.getCellComment(3, 0).getString().getString();
assertEquals(comment4, "comment top row4 (index3)\n");
// Shifting all but first line down to test comments shifting
sheet.shiftRows(1, sheet.getLastRowNum(), 1, true, true);
// Test that comments were shifted as expected
assertEquals(4, sheet.getLastRowNum());
assertNotNull(sheet.getCellComment(0, 0));
assertNull(sheet.getCellComment(1, 0));
assertNull(sheet.getCellComment(2, 0));
assertNotNull(sheet.getCellComment(3, 0));
assertNotNull(sheet.getCellComment(4, 0));
String comment1_shifted = sheet.getCellComment(0, 0).getString().getString();
assertEquals(comment1, comment1_shifted);
String comment3_shifted = sheet.getCellComment(3, 0).getString().getString();
assertEquals(comment3, comment3_shifted);
String comment4_shifted = sheet.getCellComment(4, 0).getString().getString();
assertEquals(comment4, comment4_shifted);
// Write out and read back in again
// Ensure that the changes were persisted
wb = _testDataProvider.writeOutAndReadBack(wb);
sheet = wb.getSheet("Sheet1");
assertEquals(4, sheet.getLastRowNum());
// Verify comments are in the position expected after the shift
assertNotNull(sheet.getCellComment(0, 0));
assertNull(sheet.getCellComment(1, 0));
assertNull(sheet.getCellComment(2, 0));
assertNotNull(sheet.getCellComment(3, 0));
assertNotNull(sheet.getCellComment(4, 0));
comment1_shifted = sheet.getCellComment(0, 0).getString().getString();
assertEquals(comment1, comment1_shifted);
comment3_shifted = sheet.getCellComment(3, 0).getString().getString();
assertEquals(comment3, comment3_shifted);
comment4_shifted = sheet.getCellComment(4, 0).getString().getString();
assertEquals(comment4, comment4_shifted);
}
/** See bug #34023 */
public final void testShiftWithFormulas() {
Workbook wb =
_testDataProvider.openSampleWorkbook(
"ForShifting." + _testDataProvider.getStandardFileNameExtension());
Sheet sheet = wb.getSheet("Sheet1");
assertEquals(20, sheet.getLastRowNum());
confirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
confirmRow(sheet, 1, 2, 172, 1, "ROW(D2)", "100+B2", "COUNT(D2:E2)");
confirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");
confirmCell(sheet, 6, 1, 271, "200+B1");
confirmCell(sheet, 7, 1, 272, "200+B2");
confirmCell(sheet, 8, 1, 273, "200+B3");
confirmCell(sheet, 14, 0, 0.0, "A12"); // the cell referred to by this formula will be replaced
// -----------
// Row index 1 -> 11 (row "2" -> row "12")
sheet.shiftRows(1, 1, 10);
// Now check what sheet looks like after move
// no changes on row "1"
confirmRow(sheet, 0, 1, 171, 1, "ROW(D1)", "100+B1", "COUNT(D1:E1)");
// row "2" is now empty
confirmEmptyRow(sheet, 1);
// Row "2" moved to row "12", and the formula has been updated.
// note however that the cached formula result (2) has not been updated. (POI differs from Excel
// here)
confirmRow(sheet, 11, 2, 172, 1, "ROW(D12)", "100+B12", "COUNT(D12:E12)");
// no changes on row "3"
confirmRow(sheet, 2, 3, 173, 1, "ROW(D3)", "100+B3", "COUNT(D3:E3)");
confirmCell(sheet, 14, 0, 0.0, "#REF!");
// Formulas on rows that weren't shifted:
confirmCell(sheet, 6, 1, 271, "200+B1");
confirmCell(sheet, 7, 1, 272, "200+B12"); // this one moved
confirmCell(sheet, 8, 1, 273, "200+B3");
// check formulas on other sheets
Sheet sheet2 = wb.getSheet("Sheet2");
confirmCell(sheet2, 0, 0, 371, "300+Sheet1!B1");
confirmCell(sheet2, 1, 0, 372, "300+Sheet1!B12");
confirmCell(sheet2, 2, 0, 373, "300+Sheet1!B3");
confirmCell(sheet2, 11, 0, 300, "300+Sheet1!#REF!");
// Note - named ranges formulas have not been updated
}
/** 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());
}
}
@Test
public void rotatedText() throws Exception {
Workbook workbook = _testDataProvider.createWorkbook();
fixFonts(workbook);
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
CellStyle style1 = workbook.createCellStyle();
style1.setRotation((short) 90);
Cell cell0 = row.createCell(0);
cell0.setCellValue("Apache Software Foundation");
cell0.setCellStyle(style1);
Cell cell1 = row.createCell(1);
cell1.setCellValue("Apache Software Foundation");
for (int i = 0; i < 2; i++) sheet.autoSizeColumn(i);
int w0 = sheet.getColumnWidth(0);
int w1 = sheet.getColumnWidth(1);
assertTrue(
w0 * 5 < w1); // rotated text occupies at least five times less horizontal space than normal
// text
workbook.close();
}
/** 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 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());
}
/** Tests when rows are null. */
public final void testShiftRow() {
Workbook b = _testDataProvider.createWorkbook();
Sheet s = b.createSheet();
s.createRow(0).createCell(0).setCellValue("TEST1");
s.createRow(3).createCell(0).setCellValue("TEST2");
s.shiftRows(0, 4, 1);
}
/** When shifting rows, the page breaks should go with it */
public void testShiftRowBreaks() { // TODO - enable XSSF test
Workbook b = _testDataProvider.createWorkbook();
Sheet s = b.createSheet();
Row row = s.createRow(4);
row.createCell(0).setCellValue("test");
s.setRowBreak(4);
s.shiftRows(4, 4, 2);
assertTrue("Row number 6 should have a pagebreak", s.isRowBroken(6));
}
@Test
public void numericCells() throws Exception {
Workbook workbook = _testDataProvider.createWorkbook();
fixFonts(workbook);
DataFormat df = workbook.getCreationHelper().createDataFormat();
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
row.createCell(0)
.setCellValue(0); // getCachedFormulaResult() returns 0 for not evaluated formula cells
row.createCell(1).setCellValue(10);
row.createCell(2).setCellValue("10");
row.createCell(3).setCellFormula("(A1+B1)*1.0"); // a formula that returns '10'
Cell cell4 = row.createCell(4); // numeric cell with a custom style
CellStyle style4 = workbook.createCellStyle();
style4.setDataFormat(df.getFormat("0.0000"));
cell4.setCellStyle(style4);
cell4.setCellValue(10); // formatted as '10.0000'
row.createCell(5).setCellValue("10.0000");
// autosize not-evaluated cells, formula cells are sized as if the result is 0
for (int i = 0; i < 6; i++) sheet.autoSizeColumn(i);
assertTrue(
sheet.getColumnWidth(0)
< sheet.getColumnWidth(1)); // width of '0' is less then width of '10'
assertEquals(
sheet.getColumnWidth(1), sheet.getColumnWidth(2)); // 10 and '10' should be sized equally
assertEquals(
sheet.getColumnWidth(3),
sheet.getColumnWidth(0)); // formula result is unknown, the width is calculated for '0'
assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(5)); // 10.0000 and '10.0000'
// evaluate formulas and re-autosize
evaluateWorkbook(workbook);
for (int i = 0; i < 6; i++) sheet.autoSizeColumn(i);
assertTrue(
sheet.getColumnWidth(0)
< sheet.getColumnWidth(1)); // width of '0' is less then width of '10'
assertEquals(
sheet.getColumnWidth(1),
sheet.getColumnWidth(2)); // columns 1, 2 and 3 should have the same width
assertEquals(
sheet.getColumnWidth(2),
sheet.getColumnWidth(3)); // columns 1, 2 and 3 should have the same width
assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(5)); // 10.0000 and '10.0000'
workbook.close();
}
/** Passing an incorrect formula to sheet.setArrayFormula should throw FormulaParseException */
public final void testSetArrayFormula_incorrectFormula() {
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
}
}
/** Test that when reading a workbook from input stream, array formulas are recognized */
public final void testReadArrayFormula() {
Cell[] cells;
Workbook workbook = _testDataProvider.createWorkbook();
Sheet sheet1 = workbook.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 = workbook.createSheet();
cells =
sheet2
.setArrayFormula("MIN(A1:A3*B1:B3)", CellRangeAddress.valueOf("D2:D4"))
.getFlattenedCells();
assertEquals(3, cells.length);
workbook = _testDataProvider.writeOutAndReadBack(workbook);
sheet1 = workbook.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 = workbook.getSheetAt(1);
for (int rownum = 1; rownum <= 3; rownum++) {
Cell cell1 = sheet2.getRow(rownum).getCell(3);
assertTrue(cell1.isPartOfArrayFormulaGroup());
}
}
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 final void testAutoCreateOtherCells() {
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());
}
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_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());
}
}
/**
* Tests the shiftRows function. Does three different shifts. After each shift, writes the
* workbook to file and reads back to check. This ensures that if some changes code that breaks
* writing or what not, they realize it.
*
* @param sampleName the sample file to test against
*/
public final void testShiftRows() {
// Read initial file in
String sampleName = "SimpleMultiCell." + _testDataProvider.getStandardFileNameExtension();
Workbook wb = _testDataProvider.openSampleWorkbook(sampleName);
Sheet s = wb.getSheetAt(0);
// Shift the second row down 1 and write to temp file
s.shiftRows(1, 1, 1);
wb = _testDataProvider.writeOutAndReadBack(wb);
// Read from temp file and check the number of cells in each
// row (in original file each row was unique)
s = wb.getSheetAt(0);
assertEquals(s.getRow(0).getPhysicalNumberOfCells(), 1);
confirmEmptyRow(s, 1);
assertEquals(s.getRow(2).getPhysicalNumberOfCells(), 2);
assertEquals(s.getRow(3).getPhysicalNumberOfCells(), 4);
assertEquals(s.getRow(4).getPhysicalNumberOfCells(), 5);
// Shift rows 1-3 down 3 in the current one. This tests when
// 1 row is blank. Write to a another temp file
s.shiftRows(0, 2, 3);
wb = _testDataProvider.writeOutAndReadBack(wb);
// Read and ensure things are where they should be
s = wb.getSheetAt(0);
confirmEmptyRow(s, 0);
confirmEmptyRow(s, 1);
confirmEmptyRow(s, 2);
assertEquals(s.getRow(3).getPhysicalNumberOfCells(), 1);
confirmEmptyRow(s, 4);
assertEquals(s.getRow(5).getPhysicalNumberOfCells(), 2);
// Read the first file again
wb = _testDataProvider.openSampleWorkbook(sampleName);
s = wb.getSheetAt(0);
// Shift rows 3 and 4 up and write to temp file
s.shiftRows(2, 3, -2);
wb = _testDataProvider.writeOutAndReadBack(wb);
s = wb.getSheetAt(0);
assertEquals(s.getRow(0).getPhysicalNumberOfCells(), 3);
assertEquals(s.getRow(1).getPhysicalNumberOfCells(), 4);
confirmEmptyRow(s, 2);
confirmEmptyRow(s, 3);
assertEquals(s.getRow(4).getPhysicalNumberOfCells(), 5);
}
/**
* Calls of cell.getArrayFormulaRange and sheet.removeArrayFormula on a not-array-formula cell
* throw IllegalStateException
*/
public final void testArrayFormulas_illegalCalls() {
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());
}
}
@Test
public void stringCells() throws Exception {
Workbook workbook = _testDataProvider.createWorkbook();
fixFonts(workbook);
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
Font defaultFont = workbook.getFontAt((short) 0);
CellStyle style1 = workbook.createCellStyle();
Font font1 = workbook.createFont();
font1.setFontHeight((short) (2 * defaultFont.getFontHeight()));
style1.setFont(font1);
row.createCell(0).setCellValue("x");
row.createCell(1).setCellValue("xxxx");
row.createCell(2).setCellValue("xxxxxxxxxxxx");
row.createCell(3)
.setCellValue("Apache\nSoftware Foundation"); // the text is splitted into two lines
row.createCell(4).setCellValue("Software Foundation");
Cell cell5 = row.createCell(5);
cell5.setCellValue("Software Foundation");
cell5.setCellStyle(
style1); // same as in column 4 but the font is twice larger than the default font
for (int i = 0; i < 10; i++) sheet.autoSizeColumn(i);
assertTrue(
2 * sheet.getColumnWidth(0)
< sheet.getColumnWidth(1)); // width is roughly proportional to the number of characters
assertTrue(2 * sheet.getColumnWidth(1) < sheet.getColumnWidth(2));
assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3));
boolean ignoreFontSizeX2 = JvmBugs.hasLineBreakMeasurerBug();
assertTrue(
ignoreFontSizeX2
|| sheet.getColumnWidth(5)
> sheet.getColumnWidth(4)); // larger font results in a wider column width
workbook.close();
}
public final void testShiftWithNames() {
Workbook wb = _testDataProvider.createWorkbook();
Sheet sheet1 = wb.createSheet("Sheet1");
wb.createSheet("Sheet2");
Row row = sheet1.createRow(0);
row.createCell(0).setCellValue(1.1);
row.createCell(1).setCellValue(2.2);
Name name1 = wb.createName();
name1.setNameName("name1");
name1.setRefersToFormula("Sheet1!$A$1+Sheet1!$B$1");
Name name2 = wb.createName();
name2.setNameName("name2");
name2.setRefersToFormula("Sheet1!$A$1");
// refers to A1 but on Sheet2. Should stay unaffected.
Name name3 = wb.createName();
name3.setNameName("name3");
name3.setRefersToFormula("Sheet2!$A$1");
// The scope of this one is Sheet2. Should stay unaffected.
Name name4 = wb.createName();
name4.setNameName("name4");
name4.setRefersToFormula("A1");
name4.setSheetIndex(1);
sheet1.shiftRows(0, 1, 2); // shift down the top row on Sheet1.
name1 = wb.getNameAt(0);
assertEquals("Sheet1!$A$3+Sheet1!$B$3", name1.getRefersToFormula());
name2 = wb.getNameAt(1);
assertEquals("Sheet1!$A$3", name2.getRefersToFormula());
// name3 and name4 refer to Sheet2 and should not be affected
name3 = wb.getNameAt(2);
assertEquals("Sheet2!$A$1", name3.getRefersToFormula());
name4 = wb.getNameAt(3);
assertEquals("A1", name4.getRefersToFormula());
}
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());
}
*/
}
@Test
public void mergedCells() throws Exception {
Workbook workbook = _testDataProvider.createWorkbook();
fixFonts(workbook);
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
sheet.addMergedRegion(CellRangeAddress.valueOf("A1:B1"));
Cell cell0 = row.createCell(0);
cell0.setCellValue("Apache Software Foundation");
int defaulWidth = sheet.getColumnWidth(0);
sheet.autoSizeColumn(0);
// column is unchanged if merged regions are ignored (Excel like behavior)
assertEquals(defaulWidth, sheet.getColumnWidth(0));
sheet.autoSizeColumn(0, true);
assertTrue(sheet.getColumnWidth(0) > defaulWidth);
workbook.close();
}
/**
* Auto-Sizing a column needs to work when we have rows passed the 32767 boundary. See bug #48079
*/
@Test
public void largeRowNumbers() throws Exception {
Workbook workbook = _testDataProvider.createWorkbook();
fixFonts(workbook);
Sheet sheet = workbook.createSheet();
Row r0 = sheet.createRow(0);
r0.createCell(0).setCellValue("I am ROW 0");
Row r200 = sheet.createRow(200);
r200.createCell(0).setCellValue("I am ROW 200");
// This should work fine
sheet.autoSizeColumn(0);
// Get close to 32767
Row r32765 = sheet.createRow(32765);
r32765.createCell(0).setCellValue("Nearly there...");
sheet.autoSizeColumn(0);
// To it
Row r32767 = sheet.createRow(32767);
r32767.createCell(0).setCellValue("At the boundary");
sheet.autoSizeColumn(0);
// And passed it
Row r32768 = sheet.createRow(32768);
r32768.createCell(0).setCellValue("Passed");
Row r32769 = sheet.createRow(32769);
r32769.createCell(0).setCellValue("More Passed");
sheet.autoSizeColumn(0);
// Long way passed
Row r60708 = sheet.createRow(60708);
r60708.createCell(0).setCellValue("Near the end");
sheet.autoSizeColumn(0);
workbook.close();
}
@Test
public void booleanCells() throws Exception {
Workbook workbook = _testDataProvider.createWorkbook();
fixFonts(workbook);
Sheet sheet = workbook.createSheet();
Row row = sheet.createRow(0);
row.createCell(0)
.setCellValue(0); // getCachedFormulaResult() returns 0 for not evaluated formula cells
row.createCell(1).setCellValue(true);
row.createCell(2).setCellValue("TRUE");
row.createCell(3).setCellFormula("1 > 0"); // a formula that returns true
// autosize not-evaluated cells, formula cells are sized as if the result is 0
for (int i = 0; i < 4; i++) sheet.autoSizeColumn(i);
assertTrue(sheet.getColumnWidth(1) > sheet.getColumnWidth(0)); // 'true' is wider than '0'
assertEquals(
sheet.getColumnWidth(1), sheet.getColumnWidth(2)); // 10 and '10' should be sized equally
assertEquals(
sheet.getColumnWidth(3),
sheet.getColumnWidth(0)); // formula result is unknown, the width is calculated for '0'
// evaluate formulas and re-autosize
evaluateWorkbook(workbook);
for (int i = 0; i < 4; i++) sheet.autoSizeColumn(i);
assertTrue(sheet.getColumnWidth(1) > sheet.getColumnWidth(0)); // 'true' is wider than '0'
assertEquals(
sheet.getColumnWidth(1),
sheet.getColumnWidth(2)); // columns 1, 2 and 3 should have the same width
assertEquals(
sheet.getColumnWidth(2),
sheet.getColumnWidth(3)); // columns 1, 2 and 3 should have the same width
workbook.close();
}
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());
}
}
/** 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());
}
}
@Test
public void dateCells() throws Exception {
Workbook workbook = _testDataProvider.createWorkbook();
fixFonts(workbook);
Sheet sheet = workbook.createSheet();
DataFormat df = workbook.getCreationHelper().createDataFormat();
CellStyle style1 = workbook.createCellStyle();
style1.setDataFormat(df.getFormat("m"));
CellStyle style3 = workbook.createCellStyle();
style3.setDataFormat(df.getFormat("mmm"));
CellStyle style5 = workbook.createCellStyle(); // rotated text
style5.setDataFormat(df.getFormat("mmm/dd/yyyy"));
Calendar calendar = LocaleUtil.getLocaleCalendar(2010, 0, 1); // Jan 1 2010
Row row = sheet.createRow(0);
row.createCell(0).setCellValue(DateUtil.getJavaDate(0)); // default date
Cell cell1 = row.createCell(1);
cell1.setCellValue(calendar);
cell1.setCellStyle(style1);
row.createCell(2).setCellValue("1"); // column 1 should be sized as '1'
Cell cell3 = row.createCell(3);
cell3.setCellValue(calendar);
cell3.setCellStyle(style3);
row.createCell(4).setCellValue("Jan");
Cell cell5 = row.createCell(5);
cell5.setCellValue(calendar);
cell5.setCellStyle(style5);
row.createCell(6).setCellValue("Jan/01/2010");
Cell cell7 = row.createCell(7);
cell7.setCellFormula("DATE(2010,1,1)");
cell7.setCellStyle(style3); // should be sized as 'Jan'
// autosize not-evaluated cells, formula cells are sized as if the result is 0
for (int i = 0; i < 8; i++) sheet.autoSizeColumn(i);
assertEquals(sheet.getColumnWidth(2), sheet.getColumnWidth(1)); // date formatted as 'm'
assertTrue(sheet.getColumnWidth(3) > sheet.getColumnWidth(1)); // 'mmm' is wider than 'm'
assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3)); // date formatted as 'mmm'
assertTrue(
sheet.getColumnWidth(5) > sheet.getColumnWidth(3)); // 'mmm/dd/yyyy' is wider than 'mmm'
assertEquals(
sheet.getColumnWidth(6), sheet.getColumnWidth(5)); // date formatted as 'mmm/dd/yyyy'
// YK: width of not-evaluated formulas that return data is not determined
// POI seems to conevert '0' to Excel date which is the beginng of the Excel's date system
// evaluate formulas and re-autosize
evaluateWorkbook(workbook);
for (int i = 0; i < 8; i++) sheet.autoSizeColumn(i);
assertEquals(sheet.getColumnWidth(2), sheet.getColumnWidth(1)); // date formatted as 'm'
assertTrue(sheet.getColumnWidth(3) > sheet.getColumnWidth(1)); // 'mmm' is wider than 'm'
assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3)); // date formatted as 'mmm'
assertTrue(
sheet.getColumnWidth(5) > sheet.getColumnWidth(3)); // 'mmm/dd/yyyy' is wider than 'mmm'
assertEquals(
sheet.getColumnWidth(6), sheet.getColumnWidth(5)); // date formatted as 'mmm/dd/yyyy'
assertEquals(
sheet.getColumnWidth(4), sheet.getColumnWidth(7)); // date formula formatted as 'mmm'
workbook.close();
}
/** 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());
}
