List of usage examples for org.apache.poi.ss.usermodel Cell setCellFormula
void setCellFormula(String formula) throws FormulaParseException, IllegalStateException;
From source file:org.openpythia.utilities.SSUtilities.java
License:Apache License
public static Row copyRow(Sheet sheet, Row sourceRow, int destination) { Row newRow = sheet.createRow(destination); // get the last row from the headings int lastCol = sheet.getRow(0).getLastCellNum(); for (int currentCol = 0; currentCol <= lastCol; currentCol++) { Cell newCell = newRow.createCell(currentCol); // if there is a cell in the template, copy its content and style Cell currentCell = sourceRow.getCell(currentCol); if (currentCell != null) { newCell.setCellStyle(currentCell.getCellStyle()); newCell.setCellComment(currentCell.getCellComment()); switch (currentCell.getCellType()) { case Cell.CELL_TYPE_STRING: newCell.setCellValue(currentCell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(currentCell.getNumericCellValue()); break; case Cell.CELL_TYPE_FORMULA: String dummy = currentCell.getCellFormula(); dummy = dummy.replace("Row", String.valueOf(destination + 1)); newCell.setCellFormula(dummy); newCell.setCellFormula(//from ww w.jav a 2 s . c o m currentCell.getCellFormula().replace("Row", String.valueOf(destination + 1))); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(currentCell.getBooleanCellValue()); break; default: } } } // if the row contains merged regions, copy them to the new row int numberMergedRegions = sheet.getNumMergedRegions(); for (int i = 0; i < numberMergedRegions; i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.getFirstRow() == sourceRow.getRowNum() && mergedRegion.getLastRow() == sourceRow.getRowNum()) { // this region is within the row - so copy it sheet.addMergedRegion(new CellRangeAddress(destination, destination, mergedRegion.getFirstColumn(), mergedRegion.getLastColumn())); } } return newRow; }
From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java
License:Apache License
void writeField(Object v, ValueMetaInterface vMeta, ExcelWriterStepField excelField, Row xlsRow, int posX, Object[] row, int fieldNr, boolean isTitle) throws KettleException { try {/*from ww w . jav a 2s . c o m*/ boolean cellExisted = true; // get the cell Cell cell = xlsRow.getCell(posX); if (cell == null) { cellExisted = false; cell = xlsRow.createCell(posX); } // if cell existed and existing cell's styles should not be changed, don't if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) { // if the style of this field is cached, reuse it if (!isTitle && data.getCachedStyle(fieldNr) != null) { cell.setCellStyle(data.getCachedStyle(fieldNr)); } else { // apply style if requested if (excelField != null) { // determine correct cell for title or data rows String styleRef = null; if (!isTitle && !Utils.isEmpty(excelField.getStyleCell())) { styleRef = excelField.getStyleCell(); } else if (isTitle && !Utils.isEmpty(excelField.getTitleStyleCell())) { styleRef = excelField.getTitleStyleCell(); } if (styleRef != null) { Cell styleCell = getCellFromReference(styleRef); if (styleCell != null && cell != styleCell) { cell.setCellStyle(styleCell.getCellStyle()); } } } // set cell format as specified, specific format overrides cell specification if (!isTitle && excelField != null && !Utils.isEmpty(excelField.getFormat()) && !excelField.getFormat().startsWith("Image")) { setDataFormat(excelField.getFormat(), cell); } // cache it for later runs if (!isTitle) { data.cacheStyle(fieldNr, cell.getCellStyle()); } } } // create link on cell if requested if (!isTitle && excelField != null && data.linkfieldnrs[fieldNr] >= 0) { String link = data.inputRowMeta.getValueMeta(data.linkfieldnrs[fieldNr]) .getString(row[data.linkfieldnrs[fieldNr]]); if (!Utils.isEmpty(link)) { CreationHelper ch = data.wb.getCreationHelper(); // set the link on the cell depending on link type Hyperlink hyperLink = null; if (link.startsWith("http:") || link.startsWith("https:") || link.startsWith("ftp:")) { hyperLink = ch.createHyperlink(HyperlinkType.URL); hyperLink.setLabel("URL Link"); } else if (link.startsWith("mailto:")) { hyperLink = ch.createHyperlink(HyperlinkType.EMAIL); hyperLink.setLabel("Email Link"); } else if (link.startsWith("'")) { hyperLink = ch.createHyperlink(HyperlinkType.DOCUMENT); hyperLink.setLabel("Link within this document"); } else { hyperLink = ch.createHyperlink(HyperlinkType.FILE); hyperLink.setLabel("Link to a file"); } hyperLink.setAddress(link); cell.setHyperlink(hyperLink); // if cell existed and existing cell's styles should not be changed, don't if (!(cellExisted && meta.isLeaveExistingStylesUnchanged())) { if (data.getCachedLinkStyle(fieldNr) != null) { cell.setCellStyle(data.getCachedLinkStyle(fieldNr)); } else { // CellStyle style = cell.getCellStyle(); Font origFont = data.wb.getFontAt(cell.getCellStyle().getFontIndex()); Font hlink_font = data.wb.createFont(); // reporduce original font characteristics hlink_font.setBold(origFont.getBold()); hlink_font.setCharSet(origFont.getCharSet()); hlink_font.setFontHeight(origFont.getFontHeight()); hlink_font.setFontName(origFont.getFontName()); hlink_font.setItalic(origFont.getItalic()); hlink_font.setStrikeout(origFont.getStrikeout()); hlink_font.setTypeOffset(origFont.getTypeOffset()); // make it blue and underlined hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); CellStyle style = cell.getCellStyle(); style.setFont(hlink_font); cell.setCellStyle(style); data.cacheLinkStyle(fieldNr, cell.getCellStyle()); } } } } // create comment on cell if requrested if (!isTitle && excelField != null && data.commentfieldnrs[fieldNr] >= 0 && data.wb instanceof XSSFWorkbook) { String comment = data.inputRowMeta.getValueMeta(data.commentfieldnrs[fieldNr]) .getString(row[data.commentfieldnrs[fieldNr]]); if (!Utils.isEmpty(comment)) { String author = data.commentauthorfieldnrs[fieldNr] >= 0 ? data.inputRowMeta.getValueMeta(data.commentauthorfieldnrs[fieldNr]).getString( row[data.commentauthorfieldnrs[fieldNr]]) : "Kettle PDI"; cell.setCellComment(createCellComment(author, comment)); } } // cell is getting a formula value or static content if (!isTitle && excelField != null && excelField.isFormula()) { // formula case cell.setCellFormula(vMeta.getString(v)); } else { // static content case switch (vMeta.getType()) { case ValueMetaInterface.TYPE_DATE: if (v != null && vMeta.getDate(v) != null) { cell.setCellValue(vMeta.getDate(v)); } break; case ValueMetaInterface.TYPE_BOOLEAN: if (v != null) { cell.setCellValue(vMeta.getBoolean(v)); } break; case ValueMetaInterface.TYPE_STRING: case ValueMetaInterface.TYPE_BINARY: if (v != null) { cell.setCellValue(vMeta.getString(v)); } break; case ValueMetaInterface.TYPE_BIGNUMBER: case ValueMetaInterface.TYPE_NUMBER: case ValueMetaInterface.TYPE_INTEGER: if (v != null) { cell.setCellValue(vMeta.getNumber(v)); } break; default: break; } } } catch (Exception e) { logError("Error writing field (" + data.posX + "," + data.posY + ") : " + e.toString()); logError(Const.getStackTracker(e)); throw new KettleException(e); } }
From source file:org.pentaho.reporting.engine.classic.core.modules.output.fast.xls.FastExcelPrinter.java
License:Open Source License
/** * Applies the cell value and determines whether the cell should be merged. Merging will only take place if the cell * has a row or colspan greater than one. Images will never be merged, as image content is rendered into an anchored * frame on top of the cells.// w ww.j av a 2 s .c o m * * @return true, if the cell may to be put into a merged region, false otherwise. */ private boolean applyCellValue(final ReportElement content, final Cell cell, final TableRectangle rectangle, final ExpressionRuntime runtime) throws ContentProcessingException { final Object value = textExtractor.compute(content, runtime); if (handleImageValues(content, rectangle, value)) { return false; } final String linkTarget = (String) content.getComputedStyle() .getStyleProperty(ElementStyleKeys.HREF_TARGET); if (linkTarget != null) { // this may be wrong if we have quotes inside. We should escape them .. final RotatedTextDrawable extracted = RotatedTextDrawable.extract(value); final String linkText = extracted == null ? textExtractor.getText() : extracted.getText(); final String formula = "HYPERLINK(" + splitAndQuoteExcelFormula(linkTarget) + "," + splitAndQuoteExcelFormula(linkText) + ")"; if (formula.length() < 1024) { cell.setCellFormula(formula); return true; } logger.warn( "Excel-Cells cannot contain formulas longer than 1023 characters. Converting hyperlink into plain text"); } final Object attr1 = content.getAttributes().getAttribute(AttributeNames.Excel.NAMESPACE, AttributeNames.Excel.FIELD_FORMULA); if (attr1 != null) { final String formula = String.valueOf(attr1); if (formula.length() < 1024) { cell.setCellFormula(formula); return true; } logger.warn( "Excel-Cells cannot contain formulas longer than 1023 characters. Converting excel formula into plain text"); } handleValueType(cell, getValueIfVisible(content, value), workbook); return true; }
From source file:org.pentaho.reporting.engine.classic.core.modules.output.table.xls.helper.ExcelPrinter.java
License:Open Source License
/** * Applies the cell value and determines whether the cell should be merged. Merging will only take place if the cell * has a row or colspan greater than one. Images will never be merged, as image content is rendered into an anchored * frame on top of the cells.// w w w .j av a 2 s .c o m * * @param content * @param cell * @param sheetLayout * @param rectangle * @return true, if the cell may to be put into a merged region, false otherwise. */ private boolean applyCellValue(final RenderBox content, final Cell cell, final SheetLayout sheetLayout, final TableRectangle rectangle, final long contentOffset) { final Object value = textExtractor.compute(content); if (handleImageValues(content, sheetLayout, rectangle, contentOffset, value)) { return false; } final String linkTarget = (String) content.getStyleSheet().getStyleProperty(ElementStyleKeys.HREF_TARGET); if (linkTarget != null) { // this may be wrong if we have quotes inside. We should escape them .. final RotatedTextDrawable extracted = RotatedTextDrawable.extract(value); final String linkText = extracted == null ? textExtractor.getText() : extracted.getText(); final String formula = "HYPERLINK(" + splitAndQuoteExcelFormula(linkTarget) + "," + splitAndQuoteExcelFormula(linkText) + ")"; if (formula.length() < 1024) { cell.setCellFormula(formula); return true; } ExcelPrinter.logger.warn( "Excel-Cells cannot contain formulas longer than 1023 characters. Converting hyperlink into plain text"); } final Object attr1 = content.getAttributes().getAttribute(AttributeNames.Excel.NAMESPACE, AttributeNames.Excel.FIELD_FORMULA); if (attr1 != null) { final String formula = String.valueOf(attr1); if (formula.length() < 1024) { cell.setCellFormula(formula); return true; } ExcelPrinter.logger.warn( "Excel-Cells cannot contain formulas longer than 1023 characters. Converting excel formula into plain text"); } handleValueType(cell, value, workbook); return true; }
From source file:org.projectforge.excel.ExportSheet.java
License:Open Source License
private static Row copyRow(Sheet worksheet, int rowNum) { Row sourceRow = worksheet.getRow(rowNum); //Save the text of any formula before they are altered by row shifting String[] formulasArray = new String[sourceRow.getLastCellNum()]; for (int i = 0; i < sourceRow.getLastCellNum(); i++) { if (sourceRow.getCell(i) != null && sourceRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA) formulasArray[i] = sourceRow.getCell(i).getCellFormula(); }//www .ja v a 2 s . c o m worksheet.shiftRows(rowNum, worksheet.getLastRowNum(), 1); Row newRow = sourceRow; //Now sourceRow is the empty line, so let's rename it sourceRow = worksheet.getRow(rowNum + 1); //Now the source row is at rowNum+1 // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell Cell oldCell = sourceRow.getCell(i); Cell newCell; // If the old cell is null jump to next cell if (oldCell == null) { continue; } else { newCell = newRow.createCell(i); } // Copy style from old cell and apply to new cell CellStyle newCellStyle = worksheet.getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case 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(formulasArray[i]); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; default: break; } } // If there are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } return newRow; }
From source file:org.projectforge.excel.XlsContentProvider.java
License:Open Source License
/** * /*w ww .jav a 2 s . c om*/ */ @Override public XlsContentProvider setValue(final ExportCell cell, final Object value, final String property) { final Cell poiCell = cell.getPoiCell(); final Object customizedValue = getCustomizedValue(value); if (customizedValue != null) { if (customizedValue instanceof Calendar) { poiCell.setCellValue((Calendar) customizedValue); } else if (customizedValue instanceof Date) { poiCell.setCellValue((Date) customizedValue); } else if (customizedValue instanceof String) { poiCell.setCellValue((String) customizedValue); } else { poiCell.setCellValue(String.valueOf(customizedValue)); } } else if (value instanceof Date) { // Attention: Time zone is not given! poiCell.setCellValue((Date) value); } else if (value instanceof Calendar) { poiCell.setCellValue((Calendar) value); } else if (value instanceof Boolean) { poiCell.setCellValue(((Boolean) value).booleanValue()); } else if (value instanceof Number) { poiCell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Formula) { poiCell.setCellFormula(((Formula) value).getExpr()); } else { poiCell.setCellValue(ConvertUtils.convert(value)); } CellFormat cellFormat = getCellFormat(cell, value, property, formatMap); if (cellFormat == null) { cellFormat = getCellFormat(cell, value, property, defaultFormatMap); } if (cellFormat == null) { cellFormat = new CellFormat(); cellFormat.setAlignment(CellStyle.ALIGN_LEFT); cellFormat.setDataFormat("@"); cellFormat.setWrapText(true); } cell.setCellFormat(cellFormat); return this; }
From source file:org.projectforge.export.XlsContentProvider.java
License:Open Source License
/** * /*from www . jav a 2 s .c o m*/ */ public void setValue(final ExportCell cell, final Object value, final String property) { final Cell poiCell = cell.getPoiCell(); if (value instanceof Date) { // Attention: Time zone is not given! poiCell.setCellValue((Date) value); } else if (value instanceof DateHolder) { poiCell.setCellValue(((DateHolder) value).getCalendar()); } else if (value instanceof Calendar) { poiCell.setCellValue((Calendar) value); } else if (value instanceof Boolean) { poiCell.setCellValue(((Boolean) value).booleanValue()); } else if (value instanceof Number) { poiCell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Formula) { poiCell.setCellFormula(((Formula) value).getExpr()); } else { poiCell.setCellValue(ConvertUtils.convert(value)); } CellFormat cellFormat = getCellFormat(cell, value, property, formatMap); if (cellFormat == null) { cellFormat = getCellFormat(cell, value, property, defaultFormatMap); } if (cellFormat == null) { cellFormat = new CellFormat(); cellFormat.setAlignment(CellStyle.ALIGN_LEFT); cellFormat.setDataFormat("@"); cellFormat.setWrapText(true); } cell.setCellFormat(cellFormat); }
From source file:org.seasar.fisshplate.core.element.GenericCell.java
License:Apache License
protected void mergeImpl(FPContext context, Cell out) { Object cellValue = getCellValue(); int cellType = cell.getCellType(); if (cellType == Cell.CELL_TYPE_FORMULA) { out.setCellFormula((String) cellValue); } else if (cellType == Cell.CELL_TYPE_ERROR) { out.setCellErrorValue(((Byte) cellValue).byteValue()); } else if (cellValue instanceof Date) { out.setCellValue(((Date) cellValue)); out.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (cellValue instanceof String) { out.setCellValue((String) cellValue); out.setCellType(Cell.CELL_TYPE_STRING); } else if (cellValue instanceof Boolean) { out.setCellValue(((Boolean) cellValue).booleanValue()); out.setCellType(Cell.CELL_TYPE_BOOLEAN); } else if (isNumber(cellValue)) { out.setCellValue(Double.valueOf(cellValue.toString()).doubleValue()); out.setCellType(Cell.CELL_TYPE_NUMERIC); }//from w w w. ja v a 2s.c o m }
From source file:org.tiefaces.components.websheet.utility.CommandUtility.java
License:MIT License
/** * Evaluate user formula.//w w w . java2s . co m * * @param cell * the cell * @param strValue * the str value */ private static void evaluateUserFormula(final Cell cell, final String strValue) { String formulaStr = strValue.substring(2, strValue.length() - 1); if ((formulaStr != null) && (!formulaStr.isEmpty())) { cell.setCellFormula(formulaStr); } }
From source file:org.tiefaces.components.websheet.utility.ConfigurationUtility.java
License:MIT License
/** * Builds the cell formula for shifted rows. * * @param sheet/*w w w .ja v a 2s.c o m*/ * the sheet * @param wbWrapper * the wb wrapper * @param shiftFormulaRef * the shift formula ref * @param cell * the cell * @param originFormula * the origin formula */ public static void buildCellFormulaForShiftedRows(final Sheet sheet, final XSSFEvaluationWorkbook wbWrapper, final ShiftFormulaRef shiftFormulaRef, final Cell cell, final String originFormula) { // only shift when there's watchlist exist. if ((shiftFormulaRef.getWatchList() != null) && (!shiftFormulaRef.getWatchList().isEmpty())) { Ptg[] ptgs = FormulaParser.parse(originFormula, wbWrapper, FormulaType.CELL, sheet.getWorkbook().getSheetIndex(sheet)); Ptg[] convertedFormulaPtg = ShiftFormulaUtility.convertSharedFormulas(ptgs, shiftFormulaRef); if (shiftFormulaRef.getFormulaChanged() > 0) { // only change formula when indicator is true cell.setCellFormula(FormulaRenderer.toFormulaString(wbWrapper, convertedFormulaPtg)); } } }