/** * Create a cell, and give it a value. * *@param row the row to create the cell in *@param column the column index to create the cell in *@param value The value of the cell *@return A new Cell. */ public static Cell createCell(Row row, int column, String value) { return createCell(row, column, value, null); }
/** * Take a cell, and vertically align it. * * This is superior to cell.getCellStyle().setVerticalAlignment(align) because * this method will not modify the CellStyle object that may be referenced * by multiple cells. Instead, this method will search for existing CellStyles * that match the desired CellStyle, creating a new CellStyle with the desired * style if no match exists. * * @param cell the cell to set the alignment for * @param align the vertical alignment to use. * * @see VerticalAlignment for alignment options * @since POI 3.15 beta 3 */ public static void setVerticalAlignment(Cell cell, VerticalAlignment align) { setCellStyleProperty(cell, VERTICAL_ALIGNMENT, align); }
/** * Copies the entries in src to dest, using the preferential data type * so that maps can be compared for equality * * @param src the property map to copy from (read-only) * @param dest the property map to copy into * @since POI 3.15 beta 3 */ private static void putAll(final Map<String, Object> src, Map<String, Object> dest) { for (final String key : src.keySet()) { if (shortValues.contains(key)) { dest.put(key, getShort(src, key)); } else if (booleanValues.contains(key)) { dest.put(key, getBoolean(src, key)); } else if (borderTypeValues.contains(key)) { dest.put(key, getBorderStyle(src, key)); } else if (ALIGNMENT.equals(key)) { dest.put(key, getHorizontalAlignment(src, key)); } else if (VERTICAL_ALIGNMENT.equals(key)) { dest.put(key, getVerticalAlignment(src, key)); } else if (FILL_PATTERN.equals(key)) { dest.put(key, getFillPattern(src, key)); } else { if (log.check(POILogger.INFO)) { log.log(POILogger.INFO, "Ignoring unrecognized CellUtil format properties key: " + key); } } } }
public void setProperty(Row row, int column) { // create cell if it does not exist Cell cell = CellUtil.getCell(row, column); CellUtil.setCellStyleProperty(cell, _propertyName, _propertyValue); } }
CellStyle originalStyle = cell.getCellStyle(); CellStyle newStyle = null; Map<String, Object> values = getFormatProperties(originalStyle); putAll(properties, values); Map<String, Object> wbStyleMap = getFormatProperties(wbStyle); setFormatProperties(newStyle, workbook, values);
/** * Sets the right border color for a region of cells by manipulating the cell style of the individual * cells on the right * * @param color The color of the border * @param region The region that should have the border * @param sheet The sheet that the region is on. * @since POI 3.15 beta 2 */ public static void setRightBorderColor(int color, CellRangeAddress region, Sheet sheet) { int rowStart = region.getFirstRow(); int rowEnd = region.getLastRow(); int column = region.getLastColumn(); CellPropertySetter cps = new CellPropertySetter(CellUtil.RIGHT_BORDER_COLOR, color); for (int i = rowStart; i <= rowEnd; i++) { cps.setProperty(CellUtil.getRow(i, sheet), column); } }
/** * Applies the drawn borders to a Sheet. The borders that are applied are * the ones that have been drawn by the {@link #drawBorders} and * {@link #drawBorderColors} methods. * * @param sheet * - {@link Sheet} on which to apply borders */ public void applyBorders(Sheet sheet) { Workbook wb = sheet.getWorkbook(); for (Map.Entry<CellAddress, Map<String, Object>> entry : _propertyTemplate .entrySet()) { CellAddress cellAddress = entry.getKey(); if (cellAddress.getRow() < wb.getSpreadsheetVersion().getMaxRows() && cellAddress.getColumn() < wb.getSpreadsheetVersion() .getMaxColumns()) { Map<String, Object> properties = entry.getValue(); Row row = CellUtil.getRow(cellAddress.getRow(), sheet); Cell cell = CellUtil.getCell(row, cellAddress.getColumn()); CellUtil.setCellStyleProperties(cell, properties); } } }
/** * Sets the format properties of the given style based on the given map. * * @param style cell style * @param workbook parent workbook * @param properties map of format properties (String -> Object) * @see #getFormatProperties(CellStyle) */ private static void setFormatProperties(CellStyle style, Workbook workbook, Map<String, Object> properties) { style.setAlignment(getShort(properties, ALIGNMENT)); style.setBorderBottom(getShort(properties, BORDER_BOTTOM)); style.setBorderLeft(getShort(properties, BORDER_LEFT)); style.setBorderRight(getShort(properties, BORDER_RIGHT)); style.setBorderTop(getShort(properties, BORDER_TOP)); style.setBottomBorderColor(getShort(properties, BOTTOM_BORDER_COLOR)); style.setDataFormat(getShort(properties, DATA_FORMAT)); style.setFillBackgroundColor(getShort(properties, FILL_BACKGROUND_COLOR)); style.setFillForegroundColor(getShort(properties, FILL_FOREGROUND_COLOR)); style.setFillPattern(getShort(properties, FILL_PATTERN)); style.setFont(workbook.getFontAt(getShort(properties, FONT))); style.setHidden(getBoolean(properties, HIDDEN)); style.setIndention(getShort(properties, INDENTION)); style.setLeftBorderColor(getShort(properties, LEFT_BORDER_COLOR)); style.setLocked(getBoolean(properties, LOCKED)); style.setRightBorderColor(getShort(properties, RIGHT_BORDER_COLOR)); style.setRotation(getShort(properties, ROTATION)); style.setTopBorderColor(getShort(properties, TOP_BORDER_COLOR)); style.setVerticalAlignment(getShort(properties, VERTICAL_ALIGNMENT)); style.setWrapText(getBoolean(properties, WRAP_TEXT)); }
CellStyle originalStyle = cell.getCellStyle(); CellStyle newStyle = null; Map<String, Object> values = getFormatProperties(originalStyle); values.put(propertyName, propertyValue); Map<String, Object> wbStyleMap = getFormatProperties(wbStyle); setFormatProperties(newStyle, workbook, values);
/** * Creates a cell, gives it a value, and applies a style if provided * * @param row the row to create the cell in * @param column the column index to create the cell in * @param value The value of the cell * @param style If the style is not null, then set * @return A new Cell */ public static Cell createCell(Row row, int column, String value, CellStyle style) { Cell cell = getCell(row, column); cell.setCellValue(cell.getRow().getSheet().getWorkbook().getCreationHelper() .createRichTextString(value)); if (style != null) { cell.setCellStyle(style); } return cell; }
/** * Take a cell, and apply a font to it * *@param cell the cell to set the alignment for *@param workbook The workbook that is being worked with. *@param font The HSSFFont that you want to set... */ public static void setFont(HSSFCell cell, HSSFWorkbook workbook, HSSFFont font) { CellUtil.setFont(cell, workbook, font); }
/** * Sets the top border style for a region of cells by manipulating the cell style of the individual * cells on the top * * @param border The new border * @param region The region that should have the border * @param sheet The sheet that the region is on. * @since POI 3.16 beta 1 */ public static void setBorderTop(BorderStyle border, CellRangeAddress region, Sheet sheet) { int colStart = region.getFirstColumn(); int colEnd = region.getLastColumn(); int rowIndex = region.getFirstRow(); CellPropertySetter cps = new CellPropertySetter(CellUtil.BORDER_TOP, border); Row row = CellUtil.getRow(rowIndex, sheet); for (int i = colStart; i <= colEnd; i++) { cps.setProperty(row, i); } }
public void setProperty(Row row, int column) { // create cell if it does not exist Cell cell = CellUtil.getCell(row, column); CellUtil.setCellStyleProperty(cell, _propertyName, _propertyValue); } }
/** * Applies the drawn borders to a Sheet. The borders that are applied are * the ones that have been drawn by the {@link #drawBorders} and * {@link #drawBorderColors} methods. * * @param sheet * - {@link Sheet} on which to apply borders */ public void applyBorders(Sheet sheet) { Workbook wb = sheet.getWorkbook(); for (Map.Entry<CellAddress, Map<String, Object>> entry : _propertyTemplate .entrySet()) { CellAddress cellAddress = entry.getKey(); if (cellAddress.getRow() < wb.getSpreadsheetVersion().getMaxRows() && cellAddress.getColumn() < wb.getSpreadsheetVersion() .getMaxColumns()) { Map<String, Object> properties = entry.getValue(); Row row = CellUtil.getRow(cellAddress.getRow(), sheet); Cell cell = CellUtil.getCell(row, cellAddress.getColumn()); CellUtil.setCellStyleProperties(cell, properties); } } }
CellStyle originalStyle = cell.getCellStyle(); CellStyle newStyle = null; Map<String, Object> values = getFormatProperties(originalStyle); putAll(properties, values); Map<String, Object> wbStyleMap = getFormatProperties(wbStyle); setFormatProperties(newStyle, workbook, values);
/** * Sets the format properties of the given style based on the given map. * * @param style cell style * @param workbook parent workbook * @param properties map of format properties (String -> Object) * @see #getFormatProperties(CellStyle) */ private static void setFormatProperties(CellStyle style, Workbook workbook, Map<String, Object> properties) { style.setAlignment(getShort(properties, ALIGNMENT)); style.setBorderBottom(getShort(properties, BORDER_BOTTOM)); style.setBorderLeft(getShort(properties, BORDER_LEFT)); style.setBorderRight(getShort(properties, BORDER_RIGHT)); style.setBorderTop(getShort(properties, BORDER_TOP)); style.setBottomBorderColor(getShort(properties, BOTTOM_BORDER_COLOR)); style.setDataFormat(getShort(properties, DATA_FORMAT)); style.setFillBackgroundColor(getShort(properties, FILL_BACKGROUND_COLOR)); style.setFillForegroundColor(getShort(properties, FILL_FOREGROUND_COLOR)); style.setFillPattern(getShort(properties, FILL_PATTERN)); style.setFont(workbook.getFontAt(getShort(properties, FONT))); style.setHidden(getBoolean(properties, HIDDEN)); style.setIndention(getShort(properties, INDENTION)); style.setLeftBorderColor(getShort(properties, LEFT_BORDER_COLOR)); style.setLocked(getBoolean(properties, LOCKED)); style.setRightBorderColor(getShort(properties, RIGHT_BORDER_COLOR)); style.setRotation(getShort(properties, ROTATION)); style.setTopBorderColor(getShort(properties, TOP_BORDER_COLOR)); style.setVerticalAlignment(getShort(properties, VERTICAL_ALIGNMENT)); style.setWrapText(getBoolean(properties, WRAP_TEXT)); }
CellStyle originalStyle = cell.getCellStyle(); CellStyle newStyle = null; Map<String, Object> values = getFormatProperties(originalStyle); values.put(propertyName, propertyValue); Map<String, Object> wbStyleMap = getFormatProperties(wbStyle); setFormatProperties(newStyle, workbook, values);
/** * Get a specific cell from a row. If the cell doesn't exist, * then create it. * *@param row The row that the cell is part of *@param columnIndex The column index that the cell is in. *@return The cell indicated by the column. */ public static HSSFCell getCell(HSSFRow row, int columnIndex) { return (HSSFCell) CellUtil.getCell(row, columnIndex); }
/** * Take a cell, and apply a font to it * *@param cell the cell to set the alignment for *@param workbook The workbook that is being worked with. *@param font The HSSFFont that you want to set... */ public static void setFont(HSSFCell cell, HSSFWorkbook workbook, HSSFFont font) { CellUtil.setFont(cell, workbook, font); }
/** * Sets the format properties of the given style based on the given map. * * @param style cell style * @param workbook parent workbook * @param properties map of format properties (String -> Object) * @see #getFormatProperties(CellStyle) */ private static void setFormatProperties(CellStyle style, Workbook workbook, Map<String, Object> properties) { style.setAlignment(getHorizontalAlignment(properties, ALIGNMENT)); style.setVerticalAlignment(getVerticalAlignment(properties, VERTICAL_ALIGNMENT)); style.setBorderBottom(getBorderStyle(properties, BORDER_BOTTOM)); style.setBorderLeft(getBorderStyle(properties, BORDER_LEFT)); style.setBorderRight(getBorderStyle(properties, BORDER_RIGHT)); style.setBorderTop(getBorderStyle(properties, BORDER_TOP)); style.setBottomBorderColor(getShort(properties, BOTTOM_BORDER_COLOR)); style.setDataFormat(getShort(properties, DATA_FORMAT)); style.setFillPattern(getFillPattern(properties, FILL_PATTERN)); style.setFillForegroundColor(getShort(properties, FILL_FOREGROUND_COLOR)); style.setFillBackgroundColor(getShort(properties, FILL_BACKGROUND_COLOR)); style.setFont(workbook.getFontAt(getInt(properties, FONT))); style.setHidden(getBoolean(properties, HIDDEN)); style.setIndention(getShort(properties, INDENTION)); style.setLeftBorderColor(getShort(properties, LEFT_BORDER_COLOR)); style.setLocked(getBoolean(properties, LOCKED)); style.setRightBorderColor(getShort(properties, RIGHT_BORDER_COLOR)); style.setRotation(getShort(properties, ROTATION)); style.setTopBorderColor(getShort(properties, TOP_BORDER_COLOR)); style.setWrapText(getBoolean(properties, WRAP_TEXT)); }