private boolean isMerged(Cell cell) {
if (cell == null) return false;
for (CellRangeAddress range : mergedRegions) {
if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) return true;
}
return false;
}
/** 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());
}
}
}
/**
* @param srcSheet the sheet to copy.
* @param destSheet the sheet to create.
* @param srcRow the row to copy.
* @param destRow the row to create.
* @param styleMap -
*/
public static void copyRow(
XSSFSheet srcSheet,
XSSFSheet destSheet,
XSSFRow srcRow,
XSSFRow destRow,
Map<Integer, XSSFCellStyle> styleMap) {
Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
destRow.setHeight(srcRow.getHeight());
for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
XSSFCell oldCell = srcRow.getCell(j);
XSSFCell newCell = destRow.getCell(j);
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
copyCell(oldCell, newCell, styleMap);
CellRangeAddress mergedRegion =
getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());
if (mergedRegion != null) {
CellRangeAddress newMergedRegion =
new CellRangeAddress(
mergedRegion.getFirstRow(),
mergedRegion.getFirstColumn(),
mergedRegion.getLastRow(),
mergedRegion.getLastColumn());
CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
if (isNewMergedRegion(wrapper, mergedRegions)) {
mergedRegions.add(wrapper);
destSheet.addMergedRegion(wrapper.range);
}
}
}
}
}
/** 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 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;
}
private CellRangeAddress[] computeNewFormattingRanges(
CellRangeAddress[] ranges, int startRow, int lastRow) {
for (CellRangeAddress range : ranges) {
range.setFirstRow(startRow);
range.setLastRow(lastRow);
}
return ranges;
}
private void processCell(Element tr, XSSFCell cell) {
int num = cell.getSheet().getNumMergedRegions();
// System.out.println(cell.getCTCell());
for (int i = 0; i < num; i++) {
CellRangeAddress c = cell.getSheet().getMergedRegion(i);
System.out.println(c.getFirstColumn());
;
System.out.println(c.getLastColumn());
System.out.println(c.getFirstRow());
System.out.println(c.getLastRow());
System.out.println();
System.out.println(cell.getRowIndex());
System.out.println(cell.getColumnIndex());
System.out.println("\n\n\n");
// System.out.println(cra);
}
// System.exit(0);
Element td = htmlDocumentFacade.createTableCell();
Object value;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
value = "\u00a0";
break;
case Cell.CELL_TYPE_NUMERIC:
value = cell.getNumericCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
value = cell.getNumericCellValue();
break;
default:
value = cell.getRichStringCellValue();
break;
}
if (value instanceof XSSFRichTextString) {
processCellStyle(td, cell.getCellStyle(), (XSSFRichTextString) value);
td.setTextContent(value.toString());
} else {
processCellStyle(td, cell.getCellStyle(), null);
td.setTextContent(value.toString());
}
// System.err.println(value);
tr.appendChild(td);
}
public List<String> getCellConstraints(Cell cell) {
for (DataValidation _validation : getValidations())
for (CellRangeAddress _region : _validation.getRegions().getCellRangeAddresses())
if (_region.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
if (_validation.getValidationConstraint().getExplicitListValues() != null)
return Arrays.asList(_validation.getValidationConstraint().getExplicitListValues());
else if (_validation.getValidationConstraint().getFormula1() != null) {
String formula = _validation.getValidationConstraint().getFormula1().split("\"")[1];
String[] _names = formula.split("!");
String _sheetName = _names[0];
String _arrName = _names[1];
int sheetIndex = evalWorkbook.getSheetIndex(_sheetName);
EvaluationName nm = evalWorkbook.getName(_arrName, sheetIndex);
if (nm == null || !nm.isRange()) {
throw new RuntimeException(
"Specified name '" + _arrName + "' is not a range as expected.");
}
OperationEvaluationContext ec =
new OperationEvaluationContext(
new WorkbookEvaluator(evalWorkbook, null, null),
evalWorkbook,
defaultSheet,
cell.getRowIndex(),
cell.getColumnIndex(),
null);
Ptg[] ptgs = nm.getNameDefinition();
if (ptgs.length == 1 && ptgs[0] instanceof Area3DPtg) {
ValueEval result = ec.getArea3DEval((Area3DPtg) ptgs[0]);
if (result instanceof AreaEvalBase) {
AreaEvalBase _area = (AreaEvalBase) result;
ArrayList<String> resultStrings = new ArrayList<>();
for (int i = _area.getFirstRow(); i <= _area.getLastRow(); i++) {
String value =
getStringValue(
new CellCoord(_area.getFirstSheetIndex(), _area.getFirstColumn(), i));
if (value != null && value.length() > 0) resultStrings.add(value);
}
return resultStrings;
}
return null;
}
}
}
return null;
}
/**
* @param
* @return void
*/
private void fillMergedRegion(HSSFSheet sheet, CellRangeAddress address, HSSFCellStyle style) {
for (int i = address.getFirstRow(); i <= address.getLastRow(); i++) {
HSSFRow row = sheet.getRow(i);
if (row == null) row = sheet.createRow(i);
for (int j = address.getFirstColumn(); j <= address.getLastColumn(); j++) {
HSSFCell cell = row.getCell(j);
if (cell == null) {
cell = row.createCell(j);
if (style != null) cell.setCellStyle(style);
}
}
}
}
/**
* 取单元格所在的合并区域，如果返回空，则说明没有在合并区域
*
* @param cell -- 指定的单元格
* @return
*/
private static CellRangeAddress getMergedRegion(HSSFCell cell) {
HSSFSheet sheet = cell.getSheet();
CellRangeAddress range = null;
int mergedNum = sheet.getNumMergedRegions();
for (int i = 0; i < mergedNum; i++) {
range = sheet.getMergedRegion(i);
if (range.getFirstColumn() == cell.getColumnIndex()
&& range.getFirstRow() == cell.getRowIndex()) {
return range;
}
}
return null;
}
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());
}
/**
* 输出图片到指定的单元格，参考POI例子中的ReportImageUtil类。
*
* @param cell -- 单元格
* @param bytes -- 图片内容
*/
public static void addImageToSheet(HSSFCell cell, byte[] bytes) {
if (cell == null) {
_log.showError("-----insertImageToSheet: cell is null!");
return;
}
if (bytes == null || bytes.length == 0) {
_log.showError("-----insertImageToSheet: bytes is null!");
return;
}
// 取所在表单对象
HSSFSheet sheet = cell.getSheet();
// 取图片输出行与列
int firstRow = cell.getRowIndex();
int lastRow = cell.getRowIndex();
int firstCol = cell.getColumnIndex();
int lastCol = cell.getColumnIndex();
// 取单元格所在的区域
CellRangeAddress range = getMergedRegion(cell);
if (range != null) {
firstRow = range.getFirstRow();
lastRow = range.getLastRow();
firstCol = range.getFirstColumn();
lastCol = range.getLastColumn();
}
_log.showDebug(
"---------image cells=[" + firstRow + "," + firstCol + "," + lastRow + "," + lastCol + "]");
// 图片输出要比单元格的高与宽偏5个值，保留单元的边框，宽度1023表示填充满，高度255表示填充满
HSSFClientAnchor anchor =
new HSSFClientAnchor(5, 5, 1023, 255, (short) firstCol, firstRow, (short) lastCol, lastRow);
anchor.setAnchorType(HSSFClientAnchor.MOVE_AND_RESIZE);
// 取图片管理器，如果没有则创建
HSSFPatriarch draw = sheet.getDrawingPatriarch();
if (draw == null) {
draw = sheet.createDrawingPatriarch();
}
// 插入新图片，返回的新图片序号无效
sheet.getWorkbook().addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
// 上面代码中新建图片的序号没有考虑原有图片数量，所以取原图片数量+1作为新图片的序号
List<HSSFPicture> lsPicture = getAllPicture(sheet);
int index = lsPicture.size() + 1;
_log.showDebug("---------new image index=" + index);
draw.createPicture(anchor, index);
}
public static void copyBlock(
Sheet sheet,
int startRow,
int startCol,
int endRow,
int endCol,
boolean copyStyle,
int rowOffset,
int colOffset,
List<CellRangeAddress> mergedRegions) {
for (int row = startRow; row <= endRow; row++) {
Row oldRow = sheet.getRow(row);
if (oldRow == null) continue;
Row newRow = sheet.getRow(row + rowOffset);
if (newRow == null) newRow = sheet.createRow(row + rowOffset);
if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
if (logger.isDebugEnabled()) {
logger.debug("copy row {} to {}", row, row + rowOffset);
logger.debug("Set row height :{}", newRow.getHeightInPoints());
}
for (int col = startCol; col <= endCol; col++) {
Cell oldCell = oldRow.getCell(col);
if (oldCell == null) continue;
Cell newCell = newRow.getCell(col + colOffset);
if (newCell == null) newCell = newRow.createCell(col + colOffset);
copyCell(oldCell, newCell, copyStyle, rowOffset, colOffset);
}
}
for (int col = startCol; col <= endCol; col++) {
if (sheet.getColumnWidth(col) >= 0)
sheet.setColumnWidth(col + colOffset, sheet.getColumnWidth(col));
}
if (mergedRegions != null) {
for (CellRangeAddress cra : mergedRegions) {
CellRangeAddress craNew =
new CellRangeAddress(
cra.getFirstRow() + rowOffset,
cra.getLastRow() + rowOffset,
cra.getFirstColumn() + colOffset,
cra.getLastColumn() + colOffset);
sheet.addMergedRegion(craNew);
}
}
}
// traversal cell
public void traversalCell(String filePath) {
try {
Workbook workBook = null;
try {
workBook = new XSSFWorkbook(filePath); // 支持2007
} catch (Exception ex) {
workBook = new HSSFWorkbook(new FileInputStream(filePath)); // 支持2003及以前
}
// 获得Excel中工作表个数
System.out.println("工作表个数 :" + workBook.getNumberOfSheets());
// 循环每个工作表
for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
// 创建工作表
Sheet sheet = workBook.getSheetAt(i);
int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
System.out.println(
"工作表" + sheet.getSheetName() + " 行数 :" + sheet.getPhysicalNumberOfRows());
if (rows > 0) {
sheet.getMargin(Sheet.TopMargin);
for (int r = 0; r < rows; r++) { // 行循环
Row row = sheet.getRow(r);
if (row != null) {
int cells = row.getLastCellNum(); // 获得列数
for (short c = 0; c < cells; c++) { // 列循环
Cell cell = row.getCell(c);
if (cell != null) {
String value = getCellData(cell);
System.out.println("第" + r + "行 " + "第" + c + "列：" + value);
}
}
}
}
}
// 查询合并的单元格
for (i = 0; i < sheet.getNumMergedRegions(); i++) {
System.out.println("第" + i + "个合并单元格");
CellRangeAddress region = sheet.getMergedRegion(i);
int row = region.getLastRow() - region.getFirstRow() + 1;
int col = region.getLastColumn() - region.getFirstColumn() + 1;
System.out.println("起始行:" + region.getFirstRow());
System.out.println("起始列:" + region.getFirstColumn());
System.out.println("所占行:" + row);
System.out.println("所占列:" + col);
}
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
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 serialize(LittleEndianOutput out) {
_range.serialize(out);
_guid.serialize(out);
out.writeInt(0x00000002); // TODO const
out.writeInt(_linkOpts);
if ((_linkOpts & HLINK_LABEL) != 0) {
out.writeInt(_label.length());
StringUtil.putUnicodeLE(_label, out);
}
if ((_linkOpts & HLINK_TARGET_FRAME) != 0) {
out.writeInt(_targetFrame.length());
StringUtil.putUnicodeLE(_targetFrame, out);
}
if ((_linkOpts & HLINK_URL) != 0 && (_linkOpts & HLINK_UNC_PATH) != 0) {
out.writeInt(_address.length());
StringUtil.putUnicodeLE(_address, out);
}
if ((_linkOpts & HLINK_URL) != 0 && (_linkOpts & HLINK_UNC_PATH) == 0) {
_moniker.serialize(out);
if (URL_MONIKER.equals(_moniker)) {
if (_uninterpretedTail == null) {
out.writeInt(_address.length() * 2);
StringUtil.putUnicodeLE(_address, out);
} else {
out.writeInt(_address.length() * 2 + TAIL_SIZE);
StringUtil.putUnicodeLE(_address, out);
writeTail(_uninterpretedTail, out);
}
} else if (FILE_MONIKER.equals(_moniker)) {
out.writeShort(_fileOpts);
out.writeInt(_shortFilename.length());
StringUtil.putCompressedUnicode(_shortFilename, out);
writeTail(_uninterpretedTail, out);
if (_address == null) {
out.writeInt(0);
} else {
int addrLen = _address.length() * 2;
out.writeInt(addrLen + 6);
out.writeInt(addrLen);
out.writeShort(0x0003); // TODO const
StringUtil.putUnicodeLE(_address, out);
}
}
}
if ((_linkOpts & HLINK_PLACE) != 0) {
out.writeInt(_textMark.length());
StringUtil.putUnicodeLE(_textMark, out);
}
}
/** 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 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());
}
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());
}
*/
}
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());
}
}
public Object clone() {
HyperlinkRecord rec = new HyperlinkRecord();
rec._range = _range.copy();
rec._guid = _guid;
rec._linkOpts = _linkOpts;
rec._fileOpts = _fileOpts;
rec._label = _label;
rec._address = _address;
rec._moniker = _moniker;
rec._shortFilename = _shortFilename;
rec._targetFrame = _targetFrame;
rec._textMark = _textMark;
rec._uninterpretedTail = _uninterpretedTail;
return rec;
}
public void testOneSeriePlot() throws Exception {
Workbook wb = new XSSFWorkbook();
Sheet sheet = new SheetBuilder(wb, plotData).build();
Drawing drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 10, 30);
Chart chart = drawing.createChart(anchor);
ChartAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
ChartAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
ScatterChartData scatterChartData = chart.getChartDataFactory().createScatterChartData();
ChartDataSource<String> xs =
DataSources.fromStringCellRange(sheet, CellRangeAddress.valueOf("A1:J1"));
ChartDataSource<Number> ys =
DataSources.fromNumericCellRange(sheet, CellRangeAddress.valueOf("A2:J2"));
ScatterChartSerie serie = scatterChartData.addSerie(xs, ys);
assertNotNull(serie);
assertEquals(1, scatterChartData.getSeries().size());
assertTrue(scatterChartData.getSeries().contains(serie));
chart.plot(scatterChartData, bottomAxis, leftAxis);
}
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 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();
}
private HSSFSheet createWorkSheet(HSSFWorkbook workbook) {
HSSFSheet sheet = workbook.createSheet("TIH-Demurage");
SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
// Condition 1: Formula Is =A2=A1 (White Font)
ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)");
org.apache.poi.ss.usermodel.PatternFormatting fill1 = rule1.createPatternFormatting();
fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
CellRangeAddress[] regions = {CellRangeAddress.valueOf("A1:C5")};
sheetCF.addConditionalFormatting(regions, rule1);
sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows");
sheet
.createRow(1)
.createCell(1)
.setCellValue("Condition: Formula Is =MOD(ROW(),2) (Light Green Fill)");
return sheet;
}
@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();
}
public static void main(String[] args) throws Exception {
Workbook wb;
if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook();
else wb = new XSSFWorkbook();
Map<String, CellStyle> styles = createStyles(wb);
Sheet sheet = wb.createSheet("Timesheet");
PrintSetup printSetup = sheet.getPrintSetup();
printSetup.setLandscape(true);
sheet.setFitToPage(true);
sheet.setHorizontallyCenter(true);
// title row
Row titleRow = sheet.createRow(0);
titleRow.setHeightInPoints(45);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellValue("Weekly Timesheet");
titleCell.setCellStyle(styles.get("title"));
sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
// header row
Row headerRow = sheet.createRow(1);
headerRow.setHeightInPoints(40);
Cell headerCell;
for (int i = 0; i < titles.length; i++) {
headerCell = headerRow.createCell(i);
headerCell.setCellValue(titles[i]);
headerCell.setCellStyle(styles.get("header"));
}
int rownum = 2;
for (int i = 0; i < 10; i++) {
Row row = sheet.createRow(rownum++);
for (int j = 0; j < titles.length; j++) {
Cell cell = row.createCell(j);
if (j == 9) {
// the 10th cell contains sum over week days, e.g. SUM(C3:I3)
String ref = "C" + rownum + ":I" + rownum;
cell.setCellFormula("SUM(" + ref + ")");
cell.setCellStyle(styles.get("formula"));
} else if (j == 11) {
cell.setCellFormula("J" + rownum + "-K" + rownum);
cell.setCellStyle(styles.get("formula"));
} else {
cell.setCellStyle(styles.get("cell"));
}
}
}
// row with totals below
Row sumRow = sheet.createRow(rownum++);
sumRow.setHeightInPoints(35);
Cell cell;
cell = sumRow.createCell(0);
cell.setCellStyle(styles.get("formula"));
cell = sumRow.createCell(1);
cell.setCellValue("Total Hrs:");
cell.setCellStyle(styles.get("formula"));
for (int j = 2; j < 12; j++) {
cell = sumRow.createCell(j);
String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
cell.setCellFormula("SUM(" + ref + ")");
if (j >= 9) cell.setCellStyle(styles.get("formula_2"));
else cell.setCellStyle(styles.get("formula"));
}
rownum++;
sumRow = sheet.createRow(rownum++);
sumRow.setHeightInPoints(25);
cell = sumRow.createCell(0);
cell.setCellValue("Total Regular Hours");
cell.setCellStyle(styles.get("formula"));
cell = sumRow.createCell(1);
cell.setCellFormula("L13");
cell.setCellStyle(styles.get("formula_2"));
sumRow = sheet.createRow(rownum++);
sumRow.setHeightInPoints(25);
cell = sumRow.createCell(0);
cell.setCellValue("Total Overtime Hours");
cell.setCellStyle(styles.get("formula"));
cell = sumRow.createCell(1);
cell.setCellFormula("K13");
cell.setCellStyle(styles.get("formula_2"));
// set sample data
for (int i = 0; i < sample_data.length; i++) {
Row row = sheet.getRow(2 + i);
for (int j = 0; j < sample_data[i].length; j++) {
if (sample_data[i][j] == null) continue;
if (sample_data[i][j] instanceof String) {
row.getCell(j).setCellValue((String) sample_data[i][j]);
} else {
row.getCell(j).setCellValue((Double) sample_data[i][j]);
}
}
}
// finally set column widths, the width is measured in units of 1/256th of a character width
sheet.setColumnWidth(0, 30 * 256); // 30 characters wide
for (int i = 2; i < 9; i++) {
sheet.setColumnWidth(i, 6 * 256); // 6 characters wide
}
sheet.setColumnWidth(10, 10 * 256); // 10 characters wide
// Write the output to a file
String file = "timesheet.xls";
if (wb instanceof XSSFWorkbook) file += "x";
FileOutputStream out = new FileOutputStream(file);
wb.write(out);
out.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());
}
