List of usage examples for org.apache.poi.ss.usermodel CellStyle cloneStyleFrom
void cloneStyleFrom(CellStyle source);
From source file:org.drugepi.table.ExcelUtils.java
License:Mozilla Public License
public static void restyleCell(CellStyleLookup csl, Cell cell) { CellStyle origStyle = cell.getCellStyle(); CellStyle newStyle = csl.getExistingStyle(origStyle); if (newStyle == null) { newStyle = cell.getRow().getSheet().getWorkbook().createCellStyle(); newStyle.cloneStyleFrom(origStyle); newStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); csl.putNewStyle(origStyle, newStyle); }/*w ww . j ava2 s . c o m*/ cell.setCellStyle(newStyle); }
From source file:org.drugepi.table.ExcelUtils.java
License:Mozilla Public License
public static void formatNumericCell(Cell cell, String s, String[] excelFormats) { CellStyle origStyle = cell.getCellStyle(); CellStyle newStyle = cell.getRow().getSheet().getWorkbook().createCellStyle(); DataFormat newFormat = cell.getRow().getSheet().getWorkbook().createDataFormat(); newStyle.cloneStyleFrom(origStyle); newStyle.setAlignment(CellStyle.ALIGN_LEFT); int numDecimals = -1; if (s != null) { int decimalIndex = s.indexOf("."); if (decimalIndex >= 0) numDecimals = s.length() - decimalIndex - 1; }/*from w ww. j a v a2 s . c om*/ if (numDecimals < 0) numDecimals = 0; if ((numDecimals >= 0) && (numDecimals <= 8)) newStyle.setDataFormat(newFormat.getFormat(excelFormats[numDecimals])); cell.setCellStyle(newStyle); }
From source file:org.efaps.esjp.common.file.FileUtil_Base.java
License:Apache License
/** * Copy cell./*from w ww . j a v a 2 s . c om*/ * * @param _oldCell the old cell * @param _newCell the new cell * @param _styleMap the style map */ protected void copyCell(final Cell _oldCell, final Cell _newCell, final Map<Integer, CellStyle> _styleMap) { if (_styleMap != null) { if (_oldCell.getSheet().getWorkbook() == _newCell.getSheet().getWorkbook()) { _newCell.setCellStyle(_oldCell.getCellStyle()); } else { final int stHashCode = _oldCell.getCellStyle().hashCode(); CellStyle newCellStyle = _styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = _newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(_oldCell.getCellStyle()); _styleMap.put(stHashCode, newCellStyle); } _newCell.setCellStyle(newCellStyle); } } switch (_oldCell.getCellType()) { case Cell.CELL_TYPE_STRING: _newCell.setCellValue(_oldCell.getStringCellValue()); 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; } }
From source file:org.joeffice.spreadsheet.TableStyleable.java
License:Apache License
/** * Add the attribute as defined in {@link AttributedString} to the {@link MutableAttributeSet} for the JTextPane. * * @see java.awt.font.TextAttribute//w ww . j ava2 s. co m */ protected void addAttribute(AttributedCharacterIterator.Attribute attribute, Object attributeValue, Cell cell) { CellStyle oldStyle = cell.getCellStyle(); Workbook workbook = cell.getSheet().getWorkbook(); CellStyle style = cell.getSheet().getWorkbook().createCellStyle(); style.cloneStyleFrom(oldStyle); Font newFont = copyFont(cell); if (attribute == FAMILY) { newFont.setFontName((String) attributeValue); CellUtil.setFont(cell, workbook, newFont); } else if (attribute == FOREGROUND) { Color color = (Color) attributeValue; if (cell instanceof XSSFCell) { ((XSSFCellStyle) style).setFillForegroundColor(new XSSFColor(color)); } else { HSSFWorkbook xlsWorkbook = (HSSFWorkbook) workbook; HSSFColor xlsColor = xlsWorkbook.getCustomPalette().findColor((byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue()); if (xlsColor == null) { xlsColor = xlsWorkbook.getCustomPalette().addColor((byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue()); } style.setFillForegroundColor(xlsColor.getIndex()); } style.setFillPattern(CellStyle.SOLID_FOREGROUND); } else if (attribute == BACKGROUND) { Color color = (Color) attributeValue; if (cell instanceof XSSFCell) { ((XSSFCellStyle) style).setFillBackgroundColor(new XSSFColor(color)); } else { HSSFWorkbook xlsWorkbook = (HSSFWorkbook) workbook; HSSFColor xlsColor = xlsWorkbook.getCustomPalette().findColor((byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue()); if (xlsColor == null) { xlsColor = xlsWorkbook.getCustomPalette().addColor((byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue()); } style.setFillBackgroundColor(xlsColor.getIndex()); } } else if (attribute == WEIGHT) { short boldValue = Font.BOLDWEIGHT_BOLD; if (newFont.getBoldweight() == Font.BOLDWEIGHT_BOLD) { boldValue = Font.BOLDWEIGHT_NORMAL; } newFont.setBoldweight(boldValue); CellUtil.setFont(cell, workbook, newFont); } else if (attribute == UNDERLINE) { byte underlineValue = Font.U_SINGLE; if (newFont.getUnderline() == Font.U_SINGLE) { underlineValue = Font.U_NONE; } newFont.setUnderline(underlineValue); CellUtil.setFont(cell, workbook, newFont); } else if (attribute == SUPERSCRIPT) { short superscriptValue = Font.SS_NONE; if (SUPERSCRIPT_SUB.equals(attributeValue)) { superscriptValue = Font.SS_SUB; } else if (SUPERSCRIPT_SUPER.equals(attributeValue)) { superscriptValue = Font.SS_SUPER; } newFont.setTypeOffset(superscriptValue); CellUtil.setFont(cell, workbook, newFont); } else if (attribute == STRIKETHROUGH) { boolean strikeThrough = true; if (newFont.getStrikeout()) { strikeThrough = false; } newFont.setStrikeout(strikeThrough); CellUtil.setFont(cell, workbook, newFont); } else if (attribute == POSTURE) { boolean italic = true; if (newFont.getItalic()) { italic = false; } newFont.setItalic(italic); CellUtil.setFont(cell, workbook, newFont); } else if (attribute == SIZE) { newFont.setFontHeightInPoints(((Number) attributeValue).shortValue()); CellUtil.setFont(cell, workbook, newFont); } else if (attribute == JUSTIFICATION) { CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_JUSTIFY); } else if (attribute == ALIGNMENT) { if (attributeValue.equals(StyleConstants.ALIGN_LEFT)) { CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_LEFT); } else if (attributeValue.equals(StyleConstants.ALIGN_RIGHT)) { CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_RIGHT); } else if (attributeValue.equals(StyleConstants.ALIGN_CENTER)) { CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_CENTER); } } else if (attribute == INDENTATION) { style.setIndention(((Number) attributeValue).shortValue()); } else if (attribute == TEXT_TRANSFORM) { String text = CellUtils.getFormattedText(cell); String transformedText = ((TextTransformer) attributeValue).transformText(text); cell.setCellValue(transformedText); } }
From source file:org.openpythia.plugin.worststatements.DeltaSnapshotWriter.java
License:Apache License
private CellStyle createHyperlinkStyle(Workbook workbook) { // cell style for hyperlinks // by default hyperlinks are blue and underlined CellStyle hyperlinkStyle = workbook.createCellStyle(); hyperlinkStyle.cloneStyleFrom(statementsSheet.getRow(INDEX_ROW_TEMPLATE_DELTA_SQL_STATEMENT) .getCell(INDEX_COLUMN_SQL_TEXT).getCellStyle()); Font hyperlinkFont = workbook.createFont(); hyperlinkFont.setUnderline(Font.U_SINGLE); hyperlinkFont.setColor(IndexedColors.BLUE.getIndex()); hyperlinkStyle.setFont(hyperlinkFont); return hyperlinkStyle; }
From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java
License:Apache License
/** * Set specified cell format/*from w w w . j av a 2 s. c o m*/ * * @param excelFieldFormat the specified format * @param cell the cell to set up format */ private void setDataFormat(String excelFieldFormat, Cell cell) { if (log.isDebug()) { logDebug(BaseMessages.getString(PKG, "ExcelWriterStep.Log.SetDataFormat", excelFieldFormat, CellReference.convertNumToColString(cell.getColumnIndex()), cell.getRowIndex())); } DataFormat format = data.wb.createDataFormat(); short formatIndex = format.getFormat(excelFieldFormat); CellStyle style = data.wb.createCellStyle(); style.cloneStyleFrom(cell.getCellStyle()); style.setDataFormat(formatIndex); cell.setCellStyle(style); }
From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep_StyleFormatTest.java
License:Apache License
/** * Setup the data necessary for Excel Writer step * * @param fileType//from ww w .ja va 2s . com * @throws KettleException */ private void createStepData(String fileType) throws KettleException { stepData = new ExcelWriterStepData(); stepData.inputRowMeta = step.getInputRowMeta().clone(); stepData.outputRowMeta = step.getInputRowMeta().clone(); // we don't run transformation so ExcelWriterStep.processRow() doesn't get executed // we populate the ExcelWriterStepData with bare minimum required values CellReference cellRef = new CellReference(stepMeta.getStartingCell()); stepData.startingRow = cellRef.getRow(); stepData.startingCol = cellRef.getCol(); stepData.posX = stepData.startingCol; stepData.posY = stepData.startingRow; int numOfFields = stepData.inputRowMeta.size(); stepData.fieldnrs = new int[numOfFields]; stepData.linkfieldnrs = new int[numOfFields]; stepData.commentfieldnrs = new int[numOfFields]; for (int i = 0; i < numOfFields; i++) { stepData.fieldnrs[i] = i; stepData.linkfieldnrs[i] = -1; stepData.commentfieldnrs[i] = -1; } // we avoid reading/writing Excel files, so ExcelWriterStep.prepareNextOutputFile() doesn't get executed // create Excel workbook object stepData.wb = stepMeta.getExtension().equalsIgnoreCase("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook(); stepData.sheet = stepData.wb.createSheet(); stepData.file = null; stepData.clearStyleCache(numOfFields); // we avoid reading template file from disk // so set beforehand cells with custom style and formatting DataFormat format = stepData.wb.createDataFormat(); Row xlsRow = stepData.sheet.createRow(0); // Cell F1 has custom style applied, used as template Cell cell = xlsRow.createCell(5); CellStyle cellStyle = stepData.wb.createCellStyle(); cellStyle.setBorderRight(BorderStyle.THICK); cellStyle.setFillPattern(FillPatternType.FINE_DOTS); cell.setCellStyle(cellStyle); // Cell G1 has same style, but also a custom data format cellStyle = stepData.wb.createCellStyle(); cellStyle.cloneStyleFrom(cell.getCellStyle()); cell = xlsRow.createCell(6); cellStyle.setDataFormat(format.getFormat("##0,000.0")); cell.setCellStyle(cellStyle); }
From source file:org.projectforge.excel.ExportCell.java
License:Open Source License
/** * Excel shares the cell formats and the number of cell formats is limited. This method uses a new cell style!<br/> * Don't forget to call #setCellStyle(CellStyle) if you want to apply this cloned one. * @return//from w w w . j av a 2 s .c om */ public CellStyle cloneCellStyle() { final CellStyle cellStyle = styleProvider.getWorkbook().createCellStyle(); final CellStyle origCellStyle = this.poiCell.getCellStyle(); if (origCellStyle != null) { cellStyle.cloneStyleFrom(origCellStyle); } return cellStyle; }
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(); }/*from www. j a 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.tiefaces.components.websheet.utility.CellUtility.java
License:MIT License
/** * create cell style from source cell.//from ww w . jav a2 s. c o m * * @param destSheet * dest sheet. * @param sourceCell * source cell. * @return cell style. */ private static CellStyle getCellStyleFromSourceCell(final Sheet destSheet, final Cell sourceCell) { Workbook wb = destSheet.getWorkbook(); // Copy style from old cell and apply to new cell CellStyle newCellStyle = wb.createCellStyle(); newCellStyle.cloneStyleFrom(sourceCell.getCellStyle()); return newCellStyle; }