/** * * Transforms the contents of a given cell from the POI-model to a {@link Formula} and sets the * formula as content of the given cell from the internal model. * * @param cell The given cell from the internal model. * @param poiCell The given cell form the POI-model. */ protected void transformFormulaContent(Cell cell, org.apache.poi.ss.usermodel.Cell poiCell) { // Create formula and add to cell. Formula formula = new Formula(); cell.setFormula(formula); formula.setCell(cell); formula.setFormulaString(poiCell.getCellFormula()); // Set formula result type. switch (poiCell.getCachedFormulaResultType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: formula.setResultType(CellContentType.NUMERIC); cell.setNumericContent(poiCell.getNumericCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: formula.setResultType(CellContentType.TEXT); cell.setTextContent(poiCell.getStringCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: formula.setResultType(CellContentType.BOOLEAN); cell.setBooleanContent(poiCell.getBooleanCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR: formula.setResultType(CellContentType.ERROR); cell.setErrorContent(FormulaError.forInt(poiCell.getErrorCellValue()).toString()); break; default: break; } // Set formula content. Stack<Ptg> ptgs = getPtgStackFor(poiCell); transform(cell, ptgs, formula, ptgs.size()); }
/** * 由于Excel当中的单元格Cell存在类型,若获取类型错误就会产生异常, 所以通过此方法将Cell内容全部转换为String类型 * * @param cell * @return */ public static String getCellValue(Cell cell) { String str = null; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: str = ""; break; case Cell.CELL_TYPE_BOOLEAN: str = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: str = String.valueOf(cell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { str = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(cell.getDateCellValue()); } else { str = String.valueOf((long) cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: str = String.valueOf(cell.getStringCellValue()); break; default: str = null; break; } } return StringUtils.trim(str); }
public static void copyCell(Cell oldCell, Cell newCell, boolean copyStyle) { if (copyStyle) { newCell.setCellStyle(oldCell.getCellStyle()); } switch (oldCell.getCellType()) { case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_BLANK: newCell.setCellType(Cell.CELL_TYPE_BLANK); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }
/** 把单元格内的类型转换至String类型 */ private String ConvertCellStr(Cell cell, String cellStr) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: // 读取String cellStr = cell.getStringCellValue().toString(); break; case Cell.CELL_TYPE_BOOLEAN: // 得到Boolean对象的方法 cellStr = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: // 先看是否是日期格式 if (DateUtil.isCellDateFormatted(cell)) { // 读取日期格式 cellStr = cell.getDateCellValue().toString(); } else { // 读取数字 cellStr = String.valueOf(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_FORMULA: // 读取公式 cellStr = cell.getCellFormula().toString(); break; } return cellStr; }
/** * Watch list serve for formula changes. Basically all the rows appeared in the formula in the * current sheet will be watched. Note if the cell reference is from other sheet or workbooks, it * will be ignored. * * @param wbWrapper XSSFEvaluationWorkbook used for formula parse. * @param sheet current sheet. * @return List row number for monitoring. */ private List<Integer> buildFormWatchList( final XSSFEvaluationWorkbook wbWrapper, final Sheet sheet) { List<Integer> watchList = new ArrayList<Integer>(); ConfigRange cRange = this.getConfigRange(); List<ConfigCommand> commandList = cRange.getCommandList(); if (commandList.size() <= 0) { // if no command then no dynamic changes. then no need formula shifts. return watchList; } int lastStaticRow = commandList.get(0).getTopRow() - 1; if (lastStaticRow < 0) { lastStaticRow = this.getTopRow(); } Workbook wb = sheet.getWorkbook(); for (int i = this.getTopRow(); i <= this.getLastRow(); i++) { Row row = sheet.getRow(i); for (Cell cell : row) { if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { String formula = cell.getCellFormula(); Ptg[] ptgs = FormulaParser.parse(formula, wbWrapper, FormulaType.CELL, wb.getSheetIndex(sheet)); for (int k = 0; k < ptgs.length; k++) { Object ptg = ptgs[k]; // For area formula, only first row is watched. // Reason is the lastRow must shift same rows with // firstRow. // Otherwise it's difficult to calculate. // In case some situation cannot fit, then should make // change to the formula. int areaInt = ShiftFormula.getFirstSupportedRowNumFromPtg(ptg); if (areaInt >= 0) { addToWatchList(sheet, areaInt, lastStaticRow, watchList); } } // when insert row, the formula may changed. so here is the workaround. // change formula to user formula to preserve the row changes. cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue( ConfigurationHelper.USER_FORMULA_PREFIX + formula + ConfigurationHelper.USER_FORMULA_SUFFIX); } } } return watchList; }
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(); } } } }
/** Verify that FormulaParser handles defined names beginning with underscores, see Bug #49640 */ public void testFormulasWithUnderscore() { HSSFWorkbook wb = new HSSFWorkbook(); Name nm1 = wb.createName(); nm1.setNameName("_score1"); nm1.setRefersToFormula("A1"); Name nm2 = wb.createName(); nm2.setNameName("_score2"); nm2.setRefersToFormula("A2"); Sheet sheet = wb.createSheet(); Cell cell = sheet.createRow(0).createCell(2); cell.setCellFormula("_score1*SUM(_score1+_score2)"); assertEquals("_score1*SUM(_score1+_score2)", cell.getCellFormula()); }
/** * * Gets the formula content of a given cell from the POI-model as a stack of ptgs.The Ptgs are * in reverse polish notation. * * @param poiCell The given cell from the POI-model. * @return The contents of the given cell as a stack of ptgs. */ private Stack<Ptg> getPtgStackFor(org.apache.poi.ss.usermodel.Cell poiCell) { // Parse POI-Formula to ptgs. Ptg[] ptgArray = FormulaParser.parse( poiCell.getCellFormula(), poiIO.formulaParsingWorkbook, poiCell.getCellType(), poiIO.poiWorkbook.getSheetIndex(poiCell.getSheet())); // Create stack to keep the reverse polish notation and to ease the // transformation. Stack<Ptg> ptgs = new Stack<Ptg>(); for (Ptg ptg : ptgArray) { ptgs.push(ptg); } return ptgs; }
/** * 取对应单元格类型的值 * * @param c 列数 * @return 单元格的值 */ private String getCellValue(Cell c) { String o = null; switch (c.getCellType()) { case Cell.CELL_TYPE_BLANK: o = ""; break; case Cell.CELL_TYPE_BOOLEAN: o = String.valueOf(c.getBooleanCellValue()); break; case CELL_TYPE_FORMULA: o = String.valueOf(c.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(c)) { // 处理日期格式、时间格式 SimpleDateFormat sdf; if (c.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) { sdf = new SimpleDateFormat("HH:mm"); } else { // 日期 sdf = new SimpleDateFormat("yyyy-MM-dd"); } Date date = c.getDateCellValue(); o = sdf.format(date).equals("1899-12-31") ? "" : sdf.format(date); } else if (c.getCellStyle().getDataFormat() == 58) { // 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58) SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); double value = c.getNumericCellValue(); Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value); o = sdf.format(date); } break; case Cell.CELL_TYPE_STRING: o = c.getStringCellValue(); break; default: o = null; break; } return o; }
/** * 获取单元格值 * * @param row 获取的行 * @param column 获取单元格列号 * @return 单元格值 */ public Object getCellValue(Row row, int column) { Object val = ""; try { Cell cell = row.getCell(column); if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { val = cell.getNumericCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { val = cell.getStringCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { val = cell.getCellFormula(); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { val = cell.getBooleanCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) { val = cell.getErrorCellValue(); } } } catch (Exception e) { return val; } return val; }
/** * 过滤 Cell 内容,返回字符串. * * @param c * @return */ private static String filter(Cell c) { int type = c.getCellType(); switch (type) { case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_BOOLEAN: return String.valueOf(c.getBooleanCellValue()); case Cell.CELL_TYPE_STRING: { RichTextString text = c.getRichStringCellValue(); return text.getString(); } case Cell.CELL_TYPE_FORMULA: { return c.getCellFormula(); } case Cell.CELL_TYPE_NUMERIC: { return String.valueOf(c.getNumericCellValue()); } } return "no-value"; }
/** * 获得指定位置的值,返回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; }
/** Adds in a Row to the given Sheet */ public Row addRow( Workbook wb, SheetToAdd sheetToAdd, RowToAdd rowToAdd, int rowIndex, ReportData reportData, ReportDesign design, Map<String, String> repeatSections) { // Create a new row and copy over style attributes from the row to add Row newRow = sheetToAdd.getSheet().createRow(rowIndex); Row rowToClone = rowToAdd.getRowToClone(); try { CellStyle rowStyle = rowToClone.getRowStyle(); if (rowStyle != null) { newRow.setRowStyle(rowStyle); } } catch (Exception e) { // No idea why this is necessary, but this has thrown IndexOutOfBounds errors getting the // rowStyle. Mysteries of POI } newRow.setHeight(rowToClone.getHeight()); // Iterate across all of the cells in the row, and configure all those that need to be // added/cloned List<CellToAdd> cellsToAdd = new ArrayList<CellToAdd>(); int totalCells = rowToClone.getPhysicalNumberOfCells(); int cellsFound = 0; for (int cellNum = 0; cellsFound < totalCells; cellNum++) { Cell currentCell = rowToClone.getCell(cellNum); log.debug("Handling cell: " + currentCell); if (currentCell != null) { cellsFound++; } // If we find that the cell that we are on is a repeating cell, then add the appropriate // number of cells to clone String repeatingColumnProperty = getRepeatingColumnProperty(sheetToAdd.getOriginalSheetNum(), cellNum, repeatSections); if (repeatingColumnProperty != null) { String[] dataSetSpanSplit = repeatingColumnProperty.split(","); String dataSetName = dataSetSpanSplit[0]; DataSet dataSet = getDataSet(reportData, dataSetName, rowToAdd.getReplacementData()); int numCellsToRepeat = 1; if (dataSetSpanSplit.length == 2) { numCellsToRepeat = Integer.parseInt(dataSetSpanSplit[1]); } log.debug( "Repeating this cell with dataset: " + dataSet + " and repeat of " + numCellsToRepeat); int repeatNum = 0; for (DataSetRow dataSetRow : dataSet) { repeatNum++; for (int i = 0; i < numCellsToRepeat; i++) { Cell cell = (i == 0 ? currentCell : rowToClone.getCell(cellNum + i)); if (repeatNum == 1 && cell != null && cell != currentCell) { cellsFound++; } Map<String, Object> newReplacements = getReplacementData( rowToAdd.getReplacementData(), reportData, design, dataSetName, dataSetRow, repeatNum); cellsToAdd.add(new CellToAdd(cell, newReplacements)); log.debug("Adding " + cell + " with dataSetRow: " + dataSetRow); } } cellNum += numCellsToRepeat; } else { cellsToAdd.add(new CellToAdd(currentCell, rowToAdd.getReplacementData())); log.debug("Adding " + currentCell); } } // Now, go through all of the collected cells, and add them back in ExcelStyleHelper styleHelper = new ExcelStyleHelper(wb); String prefix = getExpressionPrefix(design); String suffix = getExpressionSuffix(design); for (int i = 0; i < cellsToAdd.size(); i++) { CellToAdd cellToAdd = cellsToAdd.get(i); Cell newCell = newRow.createCell(i); Cell cellToClone = cellToAdd.getCellToClone(); if (cellToClone != null) { String contents = ExcelUtil.getCellContentsAsString(cellToClone); newCell.setCellStyle(cellToClone.getCellStyle()); try { newCell.setCellFormula(cellToClone.getCellFormula()); } catch (Exception e) { // Do nothing here. I don't know why POI throw exceptions here when the cell is not a // formula, but this suppresses them... } int numFormattings = sheetToAdd.getSheet().getSheetConditionalFormatting().getNumConditionalFormattings(); for (int n = 0; n < numFormattings; n++) { ConditionalFormatting f = sheetToAdd.getSheet().getSheetConditionalFormatting().getConditionalFormattingAt(n); for (CellRangeAddress add : f.getFormattingRanges()) { if (add.getFirstRow() == rowToAdd.getRowToClone().getRowNum() && add.getLastRow() == rowToClone.getRowNum()) { if (add.getFirstColumn() == cellToClone.getColumnIndex() && add.getLastColumn() == cellToClone.getColumnIndex()) { ConditionalFormattingRule[] rules = new ConditionalFormattingRule[f.getNumberOfRules()]; for (int j = 0; j < f.getNumberOfRules(); j++) { rules[j] = f.getRule(j); } CellRangeAddress[] cellRange = new CellRangeAddress[1]; cellRange[0] = new CellRangeAddress(rowIndex, rowIndex, i, i); sheetToAdd .getSheet() .getSheetConditionalFormatting() .addConditionalFormatting(cellRange, rules); } } } } if (ObjectUtil.notNull(contents)) { Object newContents = EvaluationUtil.evaluateExpression( contents, cellToAdd.getReplacementData(), prefix, suffix); ExcelUtil.setCellContents(styleHelper, newCell, newContents); } } } return newRow; }