/** 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 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_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_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()); } */ }
/** * 获得指定位置的值,返回object,可为数值,字符串,布尔类型,null类型 * * @param column * @return */ public Object getCellValueObject(int rowNum, int column) { // 定义返回的数组 Object tempObject = null; row = sheet.getRow(rowNum); cell = row.getCell(column); // 判断值类型 switch (cell.getCellType()) { // 字符串类型 case Cell.CELL_TYPE_STRING: tempObject = cell.getRichStringCellValue().getString(); // System.out.println(cell.getRichStringCellValue().getString()); break; // 数值类型 case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { tempObject = cell.getDateCellValue(); // System.out.println(cell.getDateCellValue()); } else { tempObject = cell.getNumericCellValue(); // System.out.println(cell.getNumericCellValue()); } break; // 布尔类型 case Cell.CELL_TYPE_BOOLEAN: tempObject = cell.getBooleanCellValue(); // System.out.println(cell.getBooleanCellValue()); break; // 数学公式类型 case Cell.CELL_TYPE_FORMULA: tempObject = cell.getCellFormula(); // System.out.println(cell.getCellFormula()); break; default: System.out.println(); } return tempObject; }
public static List<List<List<String>>> readExcel(File file, Rule rule) { int start = rule.getStart(); int end = rule.getEnd(); List<List<List<String>>> result = Lists.newArrayList(); Workbook wb; try { wb = WorkbookFactory.create(file); } catch (Exception e) { throw new ExcelException(e); } for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); List<List<String>> sheetList = Lists.newArrayList(); int rows = sheet.getLastRowNum(); if (start <= sheet.getFirstRowNum()) { start = sheet.getFirstRowNum(); } if (end >= rows) { end = rows; } else if (end <= 0) { end = rows + end; } for (int rowIndex = start; rowIndex <= end; rowIndex++) { Row row = sheet.getRow(rowIndex); List<String> columns = Lists.newArrayList(); int cellNum = row.getLastCellNum(); System.out.println(row.getLastCellNum()); System.out.println(row.getPhysicalNumberOfCells()); for (int cellIndex = row.getFirstCellNum(); cellIndex < cellNum; cellIndex++) { Cell cell = row.getCell(cellIndex); int cellType = cell.getCellType(); String column = ""; switch (cellType) { case Cell.CELL_TYPE_NUMERIC: // DecimalFormat format = new DecimalFormat(); // format.setGroupingUsed(false); column = String.valueOf(cell.getDateCellValue()); break; case Cell.CELL_TYPE_STRING: column = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: column = cell.getBooleanCellValue() + ""; break; case Cell.CELL_TYPE_FORMULA: column = cell.getCellFormula(); break; case Cell.CELL_TYPE_ERROR: case Cell.CELL_TYPE_BLANK: column = " "; break; default: } columns.add(column.trim()); } List<Boolean> rowFilterFlagList = Lists.newArrayList(); List<RowFilter> rowFilterList = Lists.newArrayList(); for (int k = 0; k < rowFilterList.size(); k++) { RowFilter rowFilter = rowFilterList.get(k); rowFilterFlagList.add(rowFilter.doFilter(rowIndex, columns)); } if (!rowFilterFlagList.contains(false)) { sheetList.add(columns); } } result.add(sheetList); } return result; }
protected SCell importCell(Cell poiCell, int row, SSheet sheet) { SCell cell = sheet.getCell(row, poiCell.getColumnIndex()); cell.setCellStyle(importCellStyle(poiCell.getCellStyle())); switch (poiCell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: cell.setNumberValue(poiCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: RichTextString poiRichTextString = poiCell.getRichStringCellValue(); if (poiRichTextString != null && poiRichTextString.numFormattingRuns() > 0) { SRichText richText = cell.setupRichTextValue(); importRichText(poiCell, poiRichTextString, richText); } else { cell.setStringValue(poiCell.getStringCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: cell.setBooleanValue(poiCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: cell.setFormulaValue(poiCell.getCellFormula()); // ZSS-873 if (isImportCache() && !poiCell.isCalcOnLoad() && !mustCalc(cell)) { ValueEval val = null; switch (poiCell.getCachedFormulaResultType()) { case Cell.CELL_TYPE_NUMERIC: val = new NumberEval(poiCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: RichTextString poiRichTextString0 = poiCell.getRichStringCellValue(); if (poiRichTextString0 != null && poiRichTextString0.numFormattingRuns() > 0) { SRichText richText = new RichTextImpl(); importRichText(poiCell, poiRichTextString0, richText); val = new StringEval(richText.getText()); } else { val = new StringEval(poiCell.getStringCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: val = BoolEval.valueOf(poiCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: val = ErrorEval.valueOf(poiCell.getErrorCellValue()); break; case Cell.CELL_TYPE_BLANK: default: // do nothing } if (val != null) { ((AbstractCellAdv) cell).setFormulaResultValue(val); } } break; case Cell.CELL_TYPE_ERROR: cell.setErrorValue(PoiEnumConversion.toErrorCode(poiCell.getErrorCellValue())); break; case Cell.CELL_TYPE_BLANK: // do nothing because spreadsheet model auto creates blank cells default: // TODO log: leave an unknown cell type as a blank cell. break; } Hyperlink poiHyperlink = poiCell.getHyperlink(); if (poiHyperlink != null) { String addr = poiHyperlink.getAddress(); String label = poiHyperlink.getLabel(); SHyperlink hyperlink = cell.setupHyperlink( PoiEnumConversion.toHyperlinkType(poiHyperlink.getType()), addr == null ? "" : addr, label == null ? "" : label); cell.setHyperlink(hyperlink); } Comment poiComment = poiCell.getCellComment(); if (poiComment != null) { SComment comment = cell.setupComment(); comment.setAuthor(poiComment.getAuthor()); comment.setVisible(poiComment.isVisible()); RichTextString poiRichTextString = poiComment.getString(); if (poiRichTextString != null && poiRichTextString.numFormattingRuns() > 0) { importRichText(poiCell, poiComment.getString(), comment.setupRichText()); } else { comment.setText(poiComment.toString()); } } return cell; }
private static void confirmCell( Sheet sheet, int rowIx, int colIx, double expectedValue, String expectedFormula) { Cell cell = sheet.getRow(rowIx).getCell(colIx); assertEquals(expectedValue, cell.getNumericCellValue(), 0.0); assertEquals(expectedFormula, cell.getCellFormula()); }
/** 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()); }