List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle getFillForegroundColor
@Override public short getFillForegroundColor()
From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java
License:Open Source License
/** * Transfers the spreadsheet data from the POI <code>HSSFWorkbook</code> into the <code>IGridWMServer</code>. * Only the sheet on the given sheetIndex is copied. //w ww . ja va2 s. co m * @param igrid the XMA model where to copy the data * @param book the POI represntation of the data * @param sheetIndex the index of the sheet to copy * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges */ public static List poi2xma(IGridWM igrid, HSSFWorkbook book, int sheetIndex) { GridWM grid = (GridWM) igrid; try { List errorList = new ArrayList(); grid.setSheetName(book.getSheetName(sheetIndex)); grid.colors.clear(); grid.initBuildInColors(); short ic = GridWM.HSSF_FIRST_COLOR_INDEX; HSSFPalette palette = book.getCustomPalette(); for (HSSFColor color = palette.getColor(ic); ic < 64 && color != null; color = palette.getColor(++ic)) { grid.colors.add(ic, new GridColor(color.getTriplet())); } grid.fonts.clear(); int numFonts = book.getNumberOfFonts(); if (numFonts > 4) { // adjust for "There is no 4" see code of org.apache.poi.hssf.model.Workbook.getFontRecordAt() numFonts += 1; } for (short i = 0; i < numFonts; i++) { HSSFFont font = book.getFontAt(i); byte fontstyle = GridFont.FONT_NORML; if (font.getBoldweight() >= HSSFFont.BOLDWEIGHT_BOLD) fontstyle |= GridFont.FONT_BOLD; if (font.getItalic()) fontstyle |= GridFont.FONT_ITALIC; grid.fonts.add(i, new GridFont(font.getFontName(), fontstyle, font.getColor())); } grid.styles.clear(); for (short i = 0, numStyles = book.getNumCellStyles(); i < numStyles; i++) { HSSFCellStyle style = book.getCellStyleAt(i); grid.styles.add(i, new GridCellStyle(style.getFontIndex(), style.getFillForegroundColor())); } grid.namedRanges.clear(); for (int i = 0, numRanges = book.getNumberOfNames(); i < numRanges; i++) { HSSFName name = book.getNameAt(i); String rangeName = name.getNameName(); String rangeRef = null; try { // ranges not defined but referenced by formulas have a name but no reference in HSSF rangeRef = name.getReference(); } catch (Exception exc) { errorList.add(new SysException(exc, ((GridWM) grid).getMessage("inconsistentRange", rangeName)) .setCode(GridWM.CODE_inconsistentRange)); } if (rangeRef != null) { try { GridRange range = grid.getJeksDelegate().toRange(rangeRef); range.setKey(rangeName); grid.namedRanges.put(rangeName, range); } catch (Exception exc) { errorList.add(new SysException(exc, ((GridWM) grid).getMessage("unsupportedReference", rangeName, rangeRef)) .setCode(GridWM.CODE_unsupportedReference)); } } } grid.rows.clear(); grid.cols.clear(); grid.cells.clear(); grid.delegate = new GridJeksDelegate(grid); HSSFSheet sheet = book.getSheetAt(sheetIndex); int firstColNum = Integer.MAX_VALUE; int lastColNum = Integer.MIN_VALUE; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); if (row == null) continue; if (row.getFirstCellNum() >= 0) firstColNum = Math.min(firstColNum, row.getFirstCellNum()); lastColNum = Math.max(lastColNum, row.getLastCellNum()); if (lastColNum > 255) lastColNum = 255; for (short j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { HSSFCell hssfcell = row.getCell(j); if (hssfcell == null) continue; GridCell gridcell = grid.getOrCreateCell(i, j); switch (hssfcell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_BOOLEAN: gridcell.setValue(hssfcell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: // TODO: recherche error text byte errorCode = hssfcell.getErrorCellValue(); // gridcell.setValue(errorCode); gridcell.setValue("#ERROR"); errorList.add(new SysException(((GridWM) grid).getMessage("errorRecord", grid.getJeksDelegate().toExcelRef(i, j), Byte.toString(errorCode))) .setCode(GridWM.CODE_errorRecord)); break; case HSSFCell.CELL_TYPE_FORMULA: String formula = null; try { formula = hssfcell.getCellFormula(); gridcell.setFormula(formula); } catch (SysException e) { if (formula != null) gridcell.setValue("=" + formula); //set it as text without interpretation errorList.add(e); } break; case HSSFCell.CELL_TYPE_NUMERIC: if (isDateCell(book, hssfcell)) { gridcell.setValue(hssfcell.getDateCellValue()); } else { gridcell.setValue(hssfcell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: gridcell.setValue(hssfcell.getStringCellValue()); break; default: throw new SysException("unknown cell type " + hssfcell.getCellType()); } gridcell.setEditable(!hssfcell.getCellStyle().getLocked()); gridcell.setStyle(hssfcell.getCellStyle().getIndex()); } } final int scalefactor = 256 / 7; // empirically testet // int width = sheet.getDefaultColumnWidth(); // returns nonsense // width = width/scalefactor; // grid.setDefaultColumnWidth(width); for (short i = (short) firstColNum; i <= lastColNum; i++) { int width = sheet.getColumnWidth(i); width = width / scalefactor; grid.getOrCreateColumn(i).setWidth(width); } if (firstColNum == Integer.MAX_VALUE) firstColNum = 0; if (lastColNum == Integer.MIN_VALUE) lastColNum = 0; grid.setMaxRange( new GridRange(grid, sheet.getFirstRowNum(), firstColNum, sheet.getLastRowNum(), lastColNum)); grid.setVisibleRange(grid.getMaxRange()); return errorList; } finally { grid.handle(grid.new GridReloadEvent()); } }
From source file:cc.landking.converter.office.excel.HSSFHtmlHelper.java
License:Apache License
public void colorStyles(CellStyle style, Formatter out) { HSSFCellStyle cs = (HSSFCellStyle) style; out.format(" /* fill pattern = %d */%n", cs.getFillPattern()); styleColor(out, "background-color", cs.getFillForegroundColor()); styleColor(out, "color", cs.getFont(wb).getColor()); styleColor(out, "border-left-color", cs.getLeftBorderColor()); styleColor(out, "border-right-color", cs.getRightBorderColor()); styleColor(out, "border-top-color", cs.getTopBorderColor()); styleColor(out, "border-bottom-color", cs.getBottomBorderColor()); }
From source file:cn.trymore.core.util.excel.PoiExcelParser.java
License:Open Source License
public String getCellBackgroundStyle(HSSFCell cell) { HSSFCellStyle style = cell.getCellStyle(); if ((cell != null) && (style != null)) { return new StringBuilder().append("background-color:") .append((this.cfmCells.containsKey(cell)) ? (String) this.cfmCells.get(cell) : tripleToRGBString(style.getFillForegroundColor())) .append(";").toString(); }/*from w w w.j a v a 2 s. co m*/ return ""; }
From source file:com.develog.utils.report.engine.export.JRXlsExporter.java
License:Open Source License
/** * */// w w w. ja va2 s . c o m protected HSSFCellStyle getLoadedCellStyle(short mode, short backcolor, short horizontalAlignment, short verticalAlignment, short rotation, HSSFFont font) { HSSFCellStyle cellStyle = null; if (loadedCellStyles != null && loadedCellStyles.size() > 0) { HSSFCellStyle cs = null; for (int i = 0; i < loadedCellStyles.size(); i++) { cs = (HSSFCellStyle) loadedCellStyles.get(i); if (cs.getFillPattern() == mode && cs.getFillForegroundColor() == backcolor && cs.getAlignment() == horizontalAlignment && cs.getVerticalAlignment() == verticalAlignment && cs.getRotation() == rotation && cs.getFontIndex() == font.getIndex()) { cellStyle = cs; break; } } } if (cellStyle == null) { cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(backcolor); cellStyle.setFillPattern(mode); cellStyle.setAlignment(horizontalAlignment); cellStyle.setVerticalAlignment(verticalAlignment); cellStyle.setRotation(rotation); cellStyle.setFont(font); cellStyle.setWrapText(true); loadedCellStyles.add(cellStyle); } return cellStyle; }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static String dumpCellStyle(HSSFCellStyle style) { StringBuffer sb = new StringBuffer(); sb.append(style.getHidden()).append(","); sb.append(style.getLocked()).append(","); sb.append(style.getWrapText()).append(","); sb.append(style.getAlignment()).append(","); sb.append(style.getBorderBottom()).append(","); sb.append(style.getBorderLeft()).append(","); sb.append(style.getBorderRight()).append(","); sb.append(style.getBorderTop()).append(","); sb.append(style.getBottomBorderColor()).append(","); sb.append(style.getDataFormat()).append(","); sb.append(style.getFillBackgroundColor()).append(","); sb.append(style.getFillForegroundColor()).append(","); sb.append(style.getFillPattern()).append(","); sb.append(style.getIndention()).append(","); sb.append(style.getLeftBorderColor()).append(","); sb.append(style.getRightBorderColor()).append(","); sb.append(style.getRotation()).append(","); sb.append(style.getTopBorderColor()).append(","); sb.append(style.getVerticalAlignment()); return sb.toString(); }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static HSSFCellStyle findStyle(HSSFCellStyle style, HSSFWorkbook srcwb, HSSFWorkbook destwb) { HSSFPalette srcpalette = srcwb.getCustomPalette(); HSSFPalette destpalette = destwb.getCustomPalette(); for (short i = 0; i < destwb.getNumCellStyles(); i++) { HSSFCellStyle old = destwb.getCellStyleAt(i); if (old == null) continue; if (style.getAlignment() == old.getAlignment() && style.getBorderBottom() == old.getBorderBottom() && style.getBorderLeft() == old.getBorderLeft() && style.getBorderRight() == old.getBorderRight() && style.getBorderTop() == old.getBorderTop() && isSameColor(style.getBottomBorderColor(), old.getBottomBorderColor(), srcpalette, destpalette)/*w w w . j av a2 s .co m*/ && style.getDataFormat() == old.getDataFormat() && isSameColor(style.getFillBackgroundColor(), old.getFillBackgroundColor(), srcpalette, destpalette) && isSameColor(style.getFillForegroundColor(), old.getFillForegroundColor(), srcpalette, destpalette) && style.getFillPattern() == old.getFillPattern() && style.getHidden() == old.getHidden() && style.getIndention() == old.getIndention() && isSameColor(style.getLeftBorderColor(), old.getLeftBorderColor(), srcpalette, destpalette) && style.getLocked() == old.getLocked() && isSameColor(style.getRightBorderColor(), old.getRightBorderColor(), srcpalette, destpalette) && style.getRotation() == old.getRotation() && isSameColor(style.getTopBorderColor(), old.getTopBorderColor(), srcpalette, destpalette) && style.getVerticalAlignment() == old.getVerticalAlignment() && style.getWrapText() == old.getWrapText()) { HSSFFont oldfont = destwb.getFontAt(old.getFontIndex()); HSSFFont font = srcwb.getFontAt(style.getFontIndex()); if (oldfont.getBoldweight() == font.getBoldweight() && oldfont.getItalic() == font.getItalic() && oldfont.getStrikeout() == font.getStrikeout() && oldfont.getCharSet() == font.getCharSet() && isSameColor(oldfont.getColor(), font.getColor(), srcpalette, destpalette) && oldfont.getFontHeight() == font.getFontHeight() && oldfont.getFontName().equals(font.getFontName()) && oldfont.getTypeOffset() == font.getTypeOffset() && oldfont.getUnderline() == font.getUnderline()) { return old; } } } return null; }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copyCellStyle(HSSFWorkbook destwb, HSSFCellStyle dest, HSSFWorkbook srcwb, HSSFCellStyle src) { if (src == null || dest == null) return;/* w w w . j a va 2 s .co m*/ dest.setAlignment(src.getAlignment()); dest.setBorderBottom(src.getBorderBottom()); dest.setBorderLeft(src.getBorderLeft()); dest.setBorderRight(src.getBorderRight()); dest.setBorderTop(src.getBorderTop()); dest.setBottomBorderColor(findColor(src.getBottomBorderColor(), srcwb, destwb)); dest.setDataFormat( destwb.createDataFormat().getFormat(srcwb.createDataFormat().getFormat(src.getDataFormat()))); dest.setFillPattern(src.getFillPattern()); dest.setFillForegroundColor(findColor(src.getFillForegroundColor(), srcwb, destwb)); dest.setFillBackgroundColor(findColor(src.getFillBackgroundColor(), srcwb, destwb)); dest.setHidden(src.getHidden()); dest.setIndention(src.getIndention()); dest.setLeftBorderColor(findColor(src.getLeftBorderColor(), srcwb, destwb)); dest.setLocked(src.getLocked()); dest.setRightBorderColor(findColor(src.getRightBorderColor(), srcwb, destwb)); dest.setRotation(src.getRotation()); dest.setTopBorderColor(findColor(src.getTopBorderColor(), srcwb, destwb)); dest.setVerticalAlignment(src.getVerticalAlignment()); dest.setWrapText(src.getWrapText()); HSSFFont f = srcwb.getFontAt(src.getFontIndex()); HSSFFont nf = findFont(f, srcwb, destwb); if (nf == null) { nf = destwb.createFont(); nf.setBoldweight(f.getBoldweight()); nf.setCharSet(f.getCharSet()); nf.setColor(findColor(f.getColor(), srcwb, destwb)); nf.setFontHeight(f.getFontHeight()); nf.setFontHeightInPoints(f.getFontHeightInPoints()); nf.setFontName(f.getFontName()); nf.setItalic(f.getItalic()); nf.setStrikeout(f.getStrikeout()); nf.setTypeOffset(f.getTypeOffset()); nf.setUnderline(f.getUnderline()); } dest.setFont(nf); }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
@Override public void apply() { for (DataObject dataObject : data) { HSSFCell templateCell = dataObject.templateCell; HSSFCell resultCell = dataObject.resultCell; BandData bandData = dataObject.bandData; HSSFWorkbook resultWorkbook = resultCell.getSheet().getWorkbook(); HSSFWorkbook templateWorkbook = templateCell.getSheet().getWorkbook(); String templateCellValue = templateCell.getStringCellValue(); Matcher matcher = pattern.matcher(templateCellValue); if (matcher.find()) { String paramName = matcher.group(1); String styleName = (String) bandData.getParameterValue(paramName); if (styleName == null) continue; HSSFCellStyle cellStyle = styleCache.getStyleByName(styleName); if (cellStyle == null) continue; HSSFCellStyle resultStyle = styleCache.getNamedCachedStyle(cellStyle); if (resultStyle == null) { HSSFCellStyle newStyle = resultWorkbook.createCellStyle(); // color newStyle.setFillBackgroundColor(cellStyle.getFillBackgroundColor()); newStyle.setFillForegroundColor(cellStyle.getFillForegroundColor()); newStyle.setFillPattern(cellStyle.getFillPattern()); // borders newStyle.setBorderLeft(cellStyle.getBorderLeft()); newStyle.setBorderRight(cellStyle.getBorderRight()); newStyle.setBorderTop(cellStyle.getBorderTop()); newStyle.setBorderBottom(cellStyle.getBorderBottom()); // border colors newStyle.setLeftBorderColor(cellStyle.getLeftBorderColor()); newStyle.setRightBorderColor(cellStyle.getRightBorderColor()); newStyle.setBottomBorderColor(cellStyle.getBottomBorderColor()); newStyle.setTopBorderColor(cellStyle.getTopBorderColor()); // alignment newStyle.setAlignment(cellStyle.getAlignment()); newStyle.setVerticalAlignment(cellStyle.getVerticalAlignment()); // misc DataFormat dataFormat = resultWorkbook.getCreationHelper().createDataFormat(); newStyle.setDataFormat(dataFormat.getFormat(cellStyle.getDataFormatString())); newStyle.setHidden(cellStyle.getHidden()); newStyle.setLocked(cellStyle.getLocked()); newStyle.setIndention(cellStyle.getIndention()); newStyle.setRotation(cellStyle.getRotation()); newStyle.setWrapText(cellStyle.getWrapText()); // font HSSFFont cellFont = cellStyle.getFont(templateWorkbook); HSSFFont newFont = fontCache.getFontByTemplate(cellFont); if (newFont == null) { newFont = resultWorkbook.createFont(); newFont.setFontName(cellFont.getFontName()); newFont.setItalic(cellFont.getItalic()); newFont.setStrikeout(cellFont.getStrikeout()); newFont.setTypeOffset(cellFont.getTypeOffset()); newFont.setBoldweight(cellFont.getBoldweight()); newFont.setCharSet(cellFont.getCharSet()); newFont.setColor(cellFont.getColor()); newFont.setUnderline(cellFont.getUnderline()); newFont.setFontHeight(cellFont.getFontHeight()); newFont.setFontHeightInPoints(cellFont.getFontHeightInPoints()); fontCache.addCachedFont(cellFont, newFont); }//from www . j a va2 s. c om newStyle.setFont(newFont); resultStyle = newStyle; styleCache.addCachedNamedStyle(cellStyle, resultStyle); } fixNeighbourCellBorders(cellStyle, resultCell); resultCell.setCellStyle(resultStyle); Sheet sheet = resultCell.getSheet(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) { int firstRow = mergedRegion.getFirstRow(); int lastRow = mergedRegion.getLastRow(); int firstCol = mergedRegion.getFirstColumn(); int lastCol = mergedRegion.getLastColumn(); for (int row = firstRow; row <= lastRow; row++) for (int col = firstCol; col <= lastCol; col++) sheet.getRow(row).getCell(col).setCellStyle(resultStyle); // cell includes only in one merged region break; } } } } }
From source file:com.vaadin.addon.spreadsheet.HSSFColorConverter.java
License:Open Source License
@Override public void colorStyles(final CellStyle cellStyle, final StringBuilder sb) { HSSFCellStyle cs = (HSSFCellStyle) cellStyle; // TODO Fill pattern not supported // out.format(" /* fill pattern = %d */%n", cs.getFillPattern()); short fillForegroundColor = cs.getFillForegroundColor(); short fillBackgroundColor = cs.getFillBackgroundColor(); String backgroundColor = null; HSSFColor fillForegroundColorColor = cs.getFillForegroundColorColor(); if (fillForegroundColorColor != null && fillForegroundColor != HSSFColor.AUTOMATIC.index) { backgroundColor = styleColor(fillForegroundColor); } else {/* w w w . j a v a 2 s .c o m*/ HSSFColor fillBackgroundColorColor = cs.getFillBackgroundColorColor(); if (fillBackgroundColorColor != null && fillBackgroundColor != HSSFColor.AUTOMATIC.index) { backgroundColor = styleColor(fillBackgroundColor); } } if (backgroundColor != null && !backgroundColor.equals(defaultBackgroundColor)) { sb.append("background-color:"); sb.append(backgroundColor); } String color = styleColor(cs.getFont(wb).getColor()); if (color != null && !color.equals(defaultColor)) { sb.append("color:"); sb.append(color); } }
From source file:com.vaadin.addon.spreadsheet.HSSFColorConverter.java
License:Open Source License
@Override public boolean hasBackgroundColor(CellStyle cellStyle) { HSSFCellStyle cs = (HSSFCellStyle) cellStyle; short fillForegroundColor = cs.getFillForegroundColor(); short fillBackgroundColor = cs.getFillBackgroundColor(); HSSFColor fillForegroundColorColor = cs.getFillForegroundColorColor(); if (fillForegroundColorColor != null && fillForegroundColor != HSSFColor.AUTOMATIC.index) { return true; } else {/*from w w w . j a v a 2 s.c om*/ HSSFColor fillBackgroundColorColor = cs.getFillBackgroundColorColor(); if (fillBackgroundColorColor != null && fillBackgroundColor != HSSFColor.AUTOMATIC.index) { return true; } } return false; }