/** * Sets the cell value using object type information. * * @param cell cell to change * @param value value to set */ private void setCellValue(Cell cell, Object value) { if (value == null || cell == null) { return; } if (value instanceof Number) { double doubleValue = ((Number) value).doubleValue(); cell.setCellValue(doubleValue); } else if (value instanceof Date) { cell.setCellValue((Date) value); } else if (value instanceof Calendar) { cell.setCellValue((Calendar) value); } else if (isFormulaDefinition(value)) { cell.setCellFormula(getFormula(value)); } else { cell.setCellValue(value.toString()); } }
}else if (value instanceof FormulaCellValue) { cell.setCellFormula(((FormulaCellValue)value).getValue()); } else if (value instanceof Date) { if (null != styleSet && null != styleSet.getCellStyleForDate()) {
}else if (value instanceof FormulaCellValue) { cell.setCellFormula(((FormulaCellValue)value).getValue()); } else if (value instanceof Date) { if (null != styleSet && null != styleSet.getCellStyleForDate()) {
protected void adjustCellReferencesInsideFormula(Cell cell, Sheet destSheet, int deltaX, int deltaY){ XSSFWorkbook hostWorkbook = (XSSFWorkbook) destSheet.getWorkbook(); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(hostWorkbook); Ptg[] ptgs = FormulaParser.parse(cell.getCellFormula(), fpb, FormulaType.CELL, 0); int destSheetIndex = hostWorkbook.getSheetIndex(destSheet); if(adjustInBothDirections(ptgs, destSheetIndex, deltaX, deltaY)) cell.setCellFormula(FormulaRenderer.toFormulaString(fpb, ptgs)); } }
case FORMULA: String oldFormula = srcCell.getCellFormula(); destCell.setCellFormula(oldFormula); break; default:
cell.setCellFormula( vMeta.getString( v ) ); } else {
/** * Utility method for setting the value of a Cell with a Formula. * * @param cellName * @param formula */ public void setFormulaValue(String cellName, String formula) { Cell cell = getCell(cellName); cell.setCellFormula(formula); }
public ExcelCell formula(String formula) { currentCell.setCellFormula(formula); currentCell.setCellType(CellType.FORMULA); return this; }
/** * @param sFormula * The formula to be set. May be <code>null</code> to set no formula. * @return A new cell in the current row of the current sheet with the passed formula */ @Nonnull public Cell addCellFormula (@Nullable final String sFormula) { final Cell aCell = addCell (); aCell.setCellType (CellType.FORMULA); aCell.setCellFormula (sFormula); return aCell; }
public static void copyCellFormula(Workbook workbook, int sheetIndex, int rowIndex, int sourceColumnIndex, int destinationColumnIndex){ XSSFEvaluationWorkbook formulaParsingWorkbook = XSSFEvaluationWorkbook.create((XSSFWorkbook) workbook); SharedFormula sharedFormula = new SharedFormula(SpreadsheetVersion.EXCEL2007); Sheet sheet = workbook.getSheetAt(sheetIndex); Row lookupRow = sheet.getRow(rowIndex); Cell sourceCell = lookupRow.getCell(sourceColumnIndex); Ptg[] sharedFormulaPtg = FormulaParser.parse(sourceCell.getCellFormula(), formulaParsingWorkbook, FormulaType.CELL, sheetIndex); Ptg[] convertedFormulaPtg = sharedFormula.convertSharedFormulas(sharedFormulaPtg, 0, 1); Cell destinationCell = lookupRow.createCell(destinationColumnIndex); destinationCell.setCellFormula(FormulaRenderer.toFormulaString(formulaParsingWorkbook, convertedFormulaPtg)); }
/** * @param sFormula * The formula to be set. May be <code>null</code> to set no formula. * @return A new cell in the current row of the current sheet with the passed * formula */ @Nonnull public Cell addCellFormula (@Nullable final String sFormula) { final Cell aCell = addCell (); aCell.setCellType (CellType.FORMULA); aCell.setCellFormula (sFormula); return aCell; }
public void setAvg(Cell result) { if (result.getSheet() == this.start.sheet.sheet) { result.setCellFormula(PrintTools.sprintf("AVERAGE(%s)", this)); } else { result.setCellFormula(PrintTools.sprintf("AVERAGE(%s)", this.toSheetString())); } }
public void setSum(Cell result) { if (result.getSheet() == this.start.sheet.sheet) { result.setCellFormula(PrintTools.sprintf("SUM(%s)", this)); } else { result.setCellFormula(PrintTools.sprintf("SUM(%s)", this.toSheetString())); } } }
@Override public Cell attachTo(final Row row) { final Cell cell = new EmptyCell(this.position).attachTo(row); cell.setCellFormula(this.value); return cell; } }
@Override public void update(org.apache.poi.ss.usermodel.Cell cell, Workbook workbook) { this.getStyle().applyTo(cell, workbook); cell.setCellFormula(formula); }
XSSFRow r = nfrntSheet.getRow(i); Cell c = r.createCell(7); c.setCellType(XSSFCell.CELL_TYPE_FORMULA); c.setCellFormula("'Original'!F"+(i+1)+"+'Original'!G"+(i+1)+"*-1");
XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("test"); Row row = sheet.createRow(0); Cell inCell = row.createCell(0); inCell.setCellValue("01.0234500"); Cell outCell = row.createCell(1); FormulaEvaluator fev = wb.getCreationHelper().createFormulaEvaluator(); String value = inCell.getStringCellValue(); outCell.setCellFormula("VALUE(" + value + ")"); fev.evaluateInCell(outCell);
private static void addSummaryCell(final Row row, final int col, final List<CellReference> cellReferenceList, final CellStyle style) { final Cell c = row.createCell(col); c.setCellType(CellType.FORMULA); c.setCellStyle(style); c.setCellFormula(buildAddFormula(cellReferenceList)); }
@SuppressWarnings("UnusedReturnValue") // for consistency with the other methods private RowContext writeFormula(Formula formula, Style style) { checkArgument(formula != null, "Formula is null for column %s", index); checkState(totalsData != null, "Please set totals data before rendering totals formula (setTotalsDataBlock(...)"); String columnIndex = Columns.columnIndexAsLetters(index + 1); String totalString = formula.toString() + '(' + columnIndex + totalsData.getStartRowNo() + ":" + columnIndex + totalsData.getEndRowNo() + ')'; Cell cell = createCell(1, style); cell.setCellFormula(totalString); cacheEvaluatedFormula(cell); return this; }
protected void adjustCellReferencesInsideFormula(Cell cell, Sheet destSheet, int deltaX, int deltaY){ XSSFWorkbook hostWorkbook = (XSSFWorkbook) destSheet.getWorkbook(); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(hostWorkbook); Ptg[] ptgs = FormulaParser.parse(cell.getCellFormula(), fpb, FormulaType.CELL, 0); int destSheetIndex = hostWorkbook.getSheetIndex(destSheet); if(adjustInBothDirections(ptgs, destSheetIndex, deltaX, deltaY)) cell.setCellFormula(FormulaRenderer.toFormulaString(fpb, ptgs)); } }