List of usage examples for org.apache.poi.ss.usermodel Font U_SINGLE
byte U_SINGLE
To view the source code for org.apache.poi.ss.usermodel Font U_SINGLE.
Click Source Link
From source file:opn.greenwebs.HyperlinkExample.java
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); //cell style for hyperlinks //by default hyperlinks are blue and underlined CellStyle hlink_style = wb.createCellStyle(); Font hlink_font = wb.createFont(); hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); hlink_style.setFont(hlink_font);//from w w w. ja v a 2 s . c o m Cell cell; Sheet sheet = wb.createSheet("Hyperlinks"); //URL cell = sheet.createRow(0).createCell((short) 0); cell.setCellValue("URL Link"); Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL); link.setAddress("http://poi.apache.org/"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //link to a file in the current directory cell = sheet.createRow(1).createCell((short) 0); cell.setCellValue("File Link"); link = createHelper.createHyperlink(Hyperlink.LINK_FILE); link.setAddress("link1.xls"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //e-mail link cell = sheet.createRow(2).createCell((short) 0); cell.setCellValue("Email Link"); link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL); //note, if subject contains white spaces, make sure they are url-encoded link.setAddress("mailto:poi@apache.org?subject=Hyperlinks"); cell.setHyperlink(link); cell.setCellStyle(hlink_style); //link to a place in this workbook //create a target sheet and cell Sheet sheet2 = wb.createSheet("Target Sheet"); sheet2.createRow(0).createCell((short) 0).setCellValue("Target Cell"); cell = sheet.createRow(3).createCell((short) 0); cell.setCellValue("Worksheet Link"); Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT); link2.setAddress("'Target Sheet'!A1"); cell.setHyperlink(link2); cell.setCellStyle(hlink_style); FileOutputStream out = new FileOutputStream("hyperinks.xlsx"); wb.write(out); out.close(); }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SpreadSheetFormatOptions.java
License:Open Source License
public static void initialize() { lookup_colors = new HashMap<String, Short>(); lookup_alignment = new HashMap<String, Short>(); lookup_border = new HashMap<String, Short>(); lookup_fillpatten = new HashMap<String, Short>(); lookup_underline = new HashMap<String, Byte>(); lookup_color = new HashMap<String, Color>(); lookup_underline.put("double", Font.U_DOUBLE); lookup_underline.put("double_accounting", Font.U_DOUBLE_ACCOUNTING); lookup_underline.put("single", Font.U_SINGLE); lookup_underline.put("single_accounting", Font.U_SINGLE_ACCOUNTING); lookup_underline.put("none", Font.U_NONE); lookup_colors.put("black", IndexedColors.BLACK.getIndex()); lookup_colors.put("brown", IndexedColors.BROWN.getIndex()); lookup_colors.put("olive_green", IndexedColors.OLIVE_GREEN.getIndex()); lookup_colors.put("dark_green", IndexedColors.DARK_GREEN.getIndex()); lookup_colors.put("dark_teal", IndexedColors.DARK_TEAL.getIndex()); lookup_colors.put("dark_blue", IndexedColors.DARK_BLUE.getIndex()); lookup_colors.put("indigo", IndexedColors.INDIGO.getIndex()); lookup_colors.put("grey_80_percent", IndexedColors.GREY_80_PERCENT.getIndex()); lookup_colors.put("grey_50_percent", IndexedColors.GREY_50_PERCENT.getIndex()); lookup_colors.put("grey_40_percent", IndexedColors.GREY_40_PERCENT.getIndex()); lookup_colors.put("grey_25_percent", IndexedColors.GREY_25_PERCENT.getIndex()); lookup_colors.put("orange", IndexedColors.ORANGE.getIndex()); lookup_colors.put("dark_yellow", IndexedColors.DARK_YELLOW.getIndex()); lookup_colors.put("green", IndexedColors.GREEN.getIndex()); lookup_colors.put("teal", IndexedColors.TEAL.getIndex()); lookup_colors.put("blue", IndexedColors.BLUE.getIndex()); lookup_colors.put("blue_grey", IndexedColors.BLUE_GREY.getIndex()); lookup_colors.put("red", IndexedColors.RED.getIndex()); lookup_colors.put("light_orange", IndexedColors.LIGHT_ORANGE.getIndex()); lookup_colors.put("lime", IndexedColors.LIME.getIndex()); lookup_colors.put("sea_green", IndexedColors.SEA_GREEN.getIndex()); lookup_colors.put("aqua", IndexedColors.AQUA.getIndex()); lookup_colors.put("light_blue", IndexedColors.LIGHT_BLUE.getIndex()); lookup_colors.put("violet", IndexedColors.VIOLET.getIndex()); lookup_colors.put("pink", IndexedColors.PINK.getIndex()); lookup_colors.put("gold", IndexedColors.GOLD.getIndex()); lookup_colors.put("yellow", IndexedColors.YELLOW.getIndex()); lookup_colors.put("bright_green", IndexedColors.BRIGHT_GREEN.getIndex()); lookup_colors.put("turquoise", IndexedColors.TURQUOISE.getIndex()); lookup_colors.put("dark_red", IndexedColors.DARK_RED.getIndex()); lookup_colors.put("sky_blue", IndexedColors.SKY_BLUE.getIndex()); lookup_colors.put("plum", IndexedColors.PLUM.getIndex()); lookup_colors.put("rose", IndexedColors.ROSE.getIndex()); lookup_colors.put("light_yellow", IndexedColors.LIGHT_YELLOW.getIndex()); lookup_colors.put("light_green", IndexedColors.LIGHT_GREEN.getIndex()); lookup_colors.put("light_turquoise", IndexedColors.LIGHT_TURQUOISE.getIndex()); lookup_colors.put("pale_blue", IndexedColors.PALE_BLUE.getIndex()); lookup_colors.put("lavender", IndexedColors.LAVENDER.getIndex()); lookup_colors.put("white", IndexedColors.WHITE.getIndex()); lookup_colors.put("cornflower_blue", IndexedColors.CORNFLOWER_BLUE.getIndex()); lookup_colors.put("lemon_chiffon", IndexedColors.LEMON_CHIFFON.getIndex()); lookup_colors.put("maroon", IndexedColors.MAROON.getIndex()); lookup_colors.put("orchid", IndexedColors.ORCHID.getIndex()); lookup_colors.put("coral", IndexedColors.CORAL.getIndex()); lookup_colors.put("royal_blue", IndexedColors.ROYAL_BLUE.getIndex()); lookup_colors.put("light_cornflower_blue", IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); lookup_alignment.put("left", CellStyle.ALIGN_LEFT); lookup_alignment.put("right", CellStyle.ALIGN_RIGHT); lookup_alignment.put("center", CellStyle.ALIGN_CENTER); lookup_alignment.put("justify", CellStyle.ALIGN_JUSTIFY); lookup_alignment.put("general", CellStyle.ALIGN_GENERAL); lookup_alignment.put("fill", CellStyle.ALIGN_FILL); lookup_alignment.put("center_selection", CellStyle.ALIGN_CENTER_SELECTION); lookup_alignment.put("vertical_top", CellStyle.VERTICAL_TOP); lookup_alignment.put("vertical_bottom", CellStyle.VERTICAL_BOTTOM); lookup_alignment.put("vertical_center", CellStyle.VERTICAL_CENTER); lookup_alignment.put("vertical_justify", CellStyle.VERTICAL_JUSTIFY); lookup_border.put("none", CellStyle.BORDER_NONE); lookup_border.put("thin", CellStyle.BORDER_THIN); lookup_border.put("medium", CellStyle.BORDER_MEDIUM); lookup_border.put("dashed", CellStyle.BORDER_DASHED); lookup_border.put("hair", CellStyle.BORDER_HAIR); lookup_border.put("thick", CellStyle.BORDER_THICK); lookup_border.put("double", CellStyle.BORDER_DOUBLE); lookup_border.put("dotted", CellStyle.BORDER_DOTTED); lookup_border.put("medium_dashed", CellStyle.BORDER_MEDIUM_DASHED); lookup_border.put("dash_dot", CellStyle.BORDER_DASH_DOT); lookup_border.put("medium_dash_dot", CellStyle.BORDER_MEDIUM_DASH_DOT); lookup_border.put("dash_dot_dot", CellStyle.BORDER_DASH_DOT_DOT); lookup_border.put("medium_dash_dot_dot", CellStyle.BORDER_MEDIUM_DASH_DOT_DOT); lookup_border.put("slanted_dash_dot", CellStyle.BORDER_SLANTED_DASH_DOT); lookup_fillpatten.put("big_spots", CellStyle.BIG_SPOTS); lookup_fillpatten.put("squares", CellStyle.SQUARES); lookup_fillpatten.put("nofill", CellStyle.NO_FILL); lookup_fillpatten.put("solid_foreground", CellStyle.SOLID_FOREGROUND); lookup_fillpatten.put("fine_dots", CellStyle.FINE_DOTS); lookup_fillpatten.put("alt_bars", CellStyle.ALT_BARS); lookup_fillpatten.put("sparse_dots", CellStyle.SPARSE_DOTS); lookup_fillpatten.put("thick_horz_bands", CellStyle.THICK_HORZ_BANDS); lookup_fillpatten.put("thick_vert_bands", CellStyle.THICK_VERT_BANDS); lookup_fillpatten.put("thick_backward_diag", CellStyle.THICK_BACKWARD_DIAG); lookup_fillpatten.put("thick_forward_diag", CellStyle.THICK_FORWARD_DIAG); lookup_fillpatten.put("thin_horz_bands", CellStyle.THIN_HORZ_BANDS); lookup_fillpatten.put("thin_vert_bands", CellStyle.THIN_VERT_BANDS); lookup_fillpatten.put("thin_backward_diag", CellStyle.THIN_BACKWARD_DIAG); lookup_fillpatten.put("thin_forward_diag", CellStyle.THIN_FORWARD_DIAG); lookup_fillpatten.put("diamonds", CellStyle.DIAMONDS); lookup_fillpatten.put("less_dots", CellStyle.LESS_DOTS); lookup_fillpatten.put("least_dots", CellStyle.LEAST_DOTS); lookup_color.put("black", Color.BLACK); lookup_color.put("blue", Color.BLUE); lookup_color.put("cyan", Color.CYAN); lookup_color.put("dark_gray", Color.DARK_GRAY); lookup_color.put("darkGray", Color.DARK_GRAY); lookup_color.put("gray", Color.GRAY); lookup_color.put("green", Color.GREEN); lookup_color.put("light_gray", Color.LIGHT_GRAY); lookup_color.put("lightGray", Color.LIGHT_GRAY); lookup_color.put("magenta", Color.MAGENTA); lookup_color.put("orange", Color.ORANGE); lookup_color.put("pink", Color.PINK); lookup_color.put("red", Color.RED); lookup_color.put("white", Color.WHITE); lookup_color.put("yellow", Color.YELLOW); }
From source file:org.apache.metamodel.excel.ExcelInsertBuilder.java
License:Apache License
@Override public void execute() { final Object[] values = getValues(); final Style[] styles = getStyles(); final Row row = getUpdateCallback().createRow(getTable().getName()); final Column[] columns = getColumns(); for (int i = 0; i < columns.length; i++) { Object value = values[i]; if (value != null) { int columnNumber = columns[i].getColumnNumber(); Cell cell = row.createCell(columnNumber); // use a lazyref and the isFetched method to only create style // if nescesary LazyRef<CellStyle> cellStyle = new LazyRef<CellStyle>() { @Override/*from w ww . j a v a 2 s. com*/ protected CellStyle fetch() { return getUpdateCallback().createCellStyle(); } }; if (value instanceof Number) { cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Boolean) { cell.setCellValue((Boolean) value); } else if (value instanceof Date) { cell.setCellValue((Date) value); } else { cell.setCellValue(value.toString()); } Style style = styles[i]; if (style != null && !Style.NO_STYLE.equals(style)) { LazyRef<Font> font = new LazyRef<Font>() { @Override protected Font fetch() { return getUpdateCallback().createFont(); } }; if (style.isBold()) { font.get().setBoldweight(Font.BOLDWEIGHT_BOLD); } if (style.isItalic()) { font.get().setItalic(true); } if (style.isUnderline()) { font.get().setUnderline(Font.U_SINGLE); } if (style.getFontSize() != null) { Integer fontSize = style.getFontSize(); SizeUnit sizeUnit = style.getFontSizeUnit(); if (sizeUnit == SizeUnit.PERCENT) { fontSize = convertFontPercentageToPt(fontSize); } font.get().setFontHeightInPoints(fontSize.shortValue()); } Color foregroundColor = style.getForegroundColor(); if (foregroundColor != null) { short index = getUpdateCallback().getColorIndex(foregroundColor); font.get().setColor(index); } if (font.isFetched()) { cellStyle.get().setFont(font.get()); } if (style.getAlignment() != null) { cellStyle.get().setAlignment(getAlignment(style.getAlignment())); } final Color backgroundColor = style.getBackgroundColor(); if (backgroundColor != null) { cellStyle.get().setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle.get().setFillForegroundColor(getUpdateCallback().getColorIndex(backgroundColor)); } } if (value instanceof Date) { if (cellStyle.isFetched()) { cellStyle.get().setDataFormat(getUpdateCallback().getDateCellFormat()); } else { cellStyle = new LazyRef<CellStyle>() { @Override protected CellStyle fetch() { return getUpdateCallback().getDateCellStyle(); } }; // trigger the fetch cellStyle.get(); } } if (cellStyle.isFetched()) { cell.setCellStyle(cellStyle.get()); } } } }
From source file:org.apache.tika.eval.reports.XLSXHREFFormatter.java
License:Apache License
@Override public void reset(XSSFWorkbook workbook) { this.workbook = workbook; style = workbook.createCellStyle();//from w w w . j a va 2 s . c o m Font hlinkFont = workbook.createFont(); hlinkFont.setUnderline(Font.U_SINGLE); hlinkFont.setColor(IndexedColors.BLUE.getIndex()); style.setFont(hlinkFont); links = 0; }
From source file:org.azkfw.document.database.xlsx.XLSXWriter.java
License:Apache License
private XSSFWorkbook write(final DatabaseModel datasource) { workbook = new XSSFWorkbook(); styleManager = new CellStyleManager(workbook); // ?// ww w.ja v a2 s. com fontTitle = workbook.createFont(); fontTitle.setBold(true); fontTitle.setItalic(true); fontTitle.setFontHeightInPoints((short) 12); fontLabel = workbook.createFont(); fontLabel.setBold(true); fontLabel.setFontHeightInPoints((short) 9); fontValue = workbook.createFont(); fontValue.setBold(false); fontValue.setFontHeightInPoints((short) 9); fontLink = workbook.createFont(); fontLink.setUnderline(Font.U_SINGLE); fontLink.setColor(IndexedColors.BLUE.getIndex()); fontLink.setFontHeightInPoints((short) 9); styleTitle = workbook.createCellStyle(); styleTitle.setFont(fontTitle); ///////////////////////////////////////////////////////////////////// XSSFFont fontPK = workbook.createFont(); fontPK.setBold(true); fontPK.setItalic(true); fontPK.setFontHeightInPoints((short) 9); // Value defStyleHeadValue = workbook.createCellStyle(); defStyleHeadValue.setFillPattern(CellStyle.SOLID_FOREGROUND); defStyleHeadValue.setFillForegroundColor(IndexedColors.WHITE.getIndex()); defStyleHeadValue.setFont(fontValue); defStyleHeadValue.setBorderTop(CellStyle.BORDER_THIN); defStyleHeadValue.setBorderBottom(CellStyle.BORDER_THIN); defStyleHeadValue.setBorderLeft(CellStyle.BORDER_THIN); defStyleHeadValue.setBorderRight(CellStyle.BORDER_THIN); styleManager.set(defStyleHeadValue); defStyleLabel = workbook.createCellStyle(); defStyleLabel.setFillPattern(CellStyle.SOLID_FOREGROUND); defStyleLabel.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); defStyleLabel.setFont(fontLabel); defStyleLabel.setBorderTop(CellStyle.BORDER_THIN); defStyleLabel.setBorderBottom(CellStyle.BORDER_THIN); defStyleLabel.setBorderLeft(CellStyle.BORDER_THIN); defStyleLabel.setBorderRight(CellStyle.BORDER_THIN); styleManager.set(defStyleLabel); // defStyleListValue = workbook.createCellStyle(); defStyleListValue.setFillPattern(CellStyle.SOLID_FOREGROUND); defStyleListValue.setFillForegroundColor(IndexedColors.WHITE.getIndex()); defStyleListValue.setFont(fontValue); defStyleListValue.setBorderTop(CellStyle.BORDER_DOTTED); defStyleListValue.setBorderBottom(CellStyle.BORDER_DOTTED); defStyleListValue.setBorderLeft(CellStyle.BORDER_THIN); defStyleListValue.setBorderRight(CellStyle.BORDER_THIN); styleManager.set(defStyleListValue); // defStyleListValueNo = workbook.createCellStyle(); defStyleListValueNo.setAlignment(CellStyle.ALIGN_RIGHT); defStyleListValueNo.setFillPattern(CellStyle.SOLID_FOREGROUND); defStyleListValueNo.setFillForegroundColor(IndexedColors.WHITE.getIndex()); defStyleListValueNo.setFont(fontValue); defStyleListValueNo.setBorderTop(CellStyle.BORDER_DOTTED); defStyleListValueNo.setBorderBottom(CellStyle.BORDER_DOTTED); defStyleListValueNo.setBorderLeft(BD_RECT); defStyleListValueNo.setBorderRight(CellStyle.BORDER_THIN); styleManager.set(defStyleListValueNo); // Value(pk) defStyleListValuePK = workbook.createCellStyle(); defStyleListValuePK.setFillPattern(CellStyle.SOLID_FOREGROUND); defStyleListValuePK.setFillForegroundColor(IndexedColors.WHITE.getIndex()); defStyleListValuePK.setFont(fontPK); defStyleListValuePK.setBorderTop(CellStyle.BORDER_DOTTED); defStyleListValuePK.setBorderBottom(CellStyle.BORDER_DOTTED); defStyleListValuePK.setBorderLeft(CellStyle.BORDER_THIN); defStyleListValuePK.setBorderRight(CellStyle.BORDER_THIN); styleManager.set(defStyleListValuePK); // Value(center) defStyleListValueCenter = workbook.createCellStyle(); defStyleListValueCenter.setFillPattern(CellStyle.SOLID_FOREGROUND); defStyleListValueCenter.setFillForegroundColor(IndexedColors.WHITE.getIndex()); defStyleListValueCenter.setFont(fontValue); defStyleListValueCenter.setAlignment(CellStyle.ALIGN_CENTER); defStyleListValueCenter.setBorderTop(CellStyle.BORDER_DOTTED); defStyleListValueCenter.setBorderBottom(CellStyle.BORDER_DOTTED); defStyleListValueCenter.setBorderLeft(CellStyle.BORDER_THIN); defStyleListValueCenter.setBorderRight(CellStyle.BORDER_THIN); styleManager.set(defStyleListValueCenter); // Value(link) defStyleListValueLink = workbook.createCellStyle(); defStyleListValueLink.setFillPattern(CellStyle.SOLID_FOREGROUND); defStyleListValueLink.setFillForegroundColor(IndexedColors.WHITE.getIndex()); defStyleListValueLink.setFont(fontLink); defStyleListValueLink.setBorderTop(CellStyle.BORDER_DOTTED); defStyleListValueLink.setBorderBottom(CellStyle.BORDER_DOTTED); defStyleListValueLink.setBorderLeft(CellStyle.BORDER_THIN); defStyleListValueLink.setBorderRight(CellStyle.BORDER_THIN); styleManager.set(defStyleListValueLink); // ? workbook.createSheet(getTableListSheetName()); for (TableModel table : datasource.getTables()) { workbook.createSheet(getTableSheetName(table.getName())); } // createTableListSheet(datasource, workbook.getSheet(getTableListSheetName())); // for (TableModel table : datasource.getTables()) { XSSFSheet sheet = workbook.getSheet(getTableSheetName(table.getName())); createTableSheet(datasource, table, sheet); } return workbook; }
From source file:org.gedantic.web.servlet.WorkbookCreator.java
License:Open Source License
/** * Setup styles./* w w w . j a v a 2 s . c om*/ */ private void setupStyles() { styleTitle = wb.createCellStyle(); Font title_font = wb.createFont(); title_font.setFontName("Helvetica"); title_font.setColor(IndexedColors.BLACK.getIndex()); title_font.setFontHeightInPoints((short) 24); styleTitle.setFont(title_font); styleSubtitle = wb.createCellStyle(); Font subtitle_font = wb.createFont(); subtitle_font.setFontName("Helvetica"); subtitle_font.setColor(IndexedColors.GREY_50_PERCENT.getIndex()); subtitle_font.setFontHeightInPoints((short) 18); styleSubtitle.setFont(subtitle_font); styleHyperlink = wb.createCellStyle(); Font hlink_font = wb.createFont(); hlink_font.setFontName("Helvetica"); hlink_font.setUnderline(Font.U_SINGLE); hlink_font.setColor(IndexedColors.BLUE.getIndex()); styleHyperlink.setFont(hlink_font); styleNormal = wb.createCellStyle(); Font normal_font = wb.createFont(); normal_font.setFontName("Helvetica"); normal_font.setColor(IndexedColors.BLACK.getIndex()); normal_font.setFontHeightInPoints((short) 12); styleNormal.setFont(normal_font); styleNormal.setWrapText(true); styleHeader = wb.createCellStyle(); Font header_font = wb.createFont(); header_font.setFontName("Helvetica"); header_font.setColor(IndexedColors.WHITE.getIndex()); header_font.setBold(true); header_font.setFontHeightInPoints((short) 12); XSSFColor bg = new XSSFColor(new java.awt.Color(0x28, 0x60, 0x90)); ((XSSFCellStyle) styleHeader).setFillForegroundColor(bg); styleHeader.setFillPattern(CellStyle.SOLID_FOREGROUND); styleHeader.setFont(header_font); }
From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java
License:Apache License
@SuppressWarnings("unused") private static void writeCell(Cell cell, Object val, boolean userTemplate, ExcelWriteFieldMappingAttribute attribute, Object bean) { if (attribute != null && attribute.getLinkField() != null) { String addressFieldName = attribute.getLinkField(); String address = null;/* ww w . j a va 2 s .c o m*/ if (bean != null) { address = (String) getFieldValue(bean, addressFieldName, true); } Workbook wb = cell.getRow().getSheet().getWorkbook(); Hyperlink link = wb.getCreationHelper().createHyperlink(attribute.getLinkType()); link.setAddress(address); cell.setHyperlink(link); // Its style can't inherit from cell. CellStyle style = wb.createCellStyle(); Font hlinkFont = wb.createFont(); hlinkFont.setUnderline(Font.U_SINGLE); hlinkFont.setColor(IndexedColors.BLUE.getIndex()); style.setFont(hlinkFont); if (cell.getCellStyle() != null) { style.setFillBackgroundColor(cell.getCellStyle().getFillBackgroundColor()); } cell.setCellStyle(style); } if (val == null) { cell.setCellValue((String) null); return; } Class<?> clazz = val.getClass(); if (val instanceof Byte) {// Double Byte temp = (Byte) val; cell.setCellValue((double) temp.byteValue()); } else if (val instanceof Short) { Short temp = (Short) val; cell.setCellValue((double) temp.shortValue()); } else if (val instanceof Integer) { Integer temp = (Integer) val; cell.setCellValue((double) temp.intValue()); } else if (val instanceof Long) { Long temp = (Long) val; cell.setCellValue((double) temp.longValue()); } else if (val instanceof Float) { Float temp = (Float) val; cell.setCellValue((double) temp.floatValue()); } else if (val instanceof Double) { Double temp = (Double) val; cell.setCellValue((double) temp.doubleValue()); } else if (val instanceof Date) {// Date Date dateVal = (Date) val; long time = dateVal.getTime(); // read is based on 1899/12/31 but DateUtil.getExcelDate is base on // 1900/01/01 if (time >= TIME_1899_12_31_00_00_00_000 && time < TIME_1900_01_01_00_00_00_000) { Date incOneDay = new Date(time + 24 * 60 * 60 * 1000); double d = DateUtil.getExcelDate(incOneDay); cell.setCellValue(d - 1); } else { cell.setCellValue(dateVal); } if (!userTemplate) { Workbook wb = cell.getRow().getSheet().getWorkbook(); CellStyle cellStyle = cell.getCellStyle(); if (cellStyle == null) { cellStyle = wb.createCellStyle(); } DataFormat dataFormat = wb.getCreationHelper().createDataFormat(); // @see #BuiltinFormats // 0xe, "m/d/yy" // 0x14 "h:mm" // 0x16 "m/d/yy h:mm" // {@linke https://en.wikipedia.org/wiki/Year_10,000_problem} /** [1899/12/31 00:00:00:000~1900/01/01 00:00:000) */ if (time >= TIME_1899_12_31_00_00_00_000 && time < TIME_1900_01_02_00_00_00_000) { cellStyle.setDataFormat(dataFormat.getFormat("h:mm")); // cellStyle.setDataFormat(dataFormat.getFormat("m/d/yy h:mm")); } else { // if ( time % (24 * 60 * 60 * 1000) == 0) {//for time // zone,we can't use this way. Calendar calendar = Calendar.getInstance(); calendar.setTime(dateVal); int hour = calendar.get(Calendar.HOUR_OF_DAY); int minute = calendar.get(Calendar.MINUTE); int second = calendar.get(Calendar.SECOND); int millisecond = calendar.get(Calendar.MILLISECOND); if (millisecond == 0 && second == 0 && minute == 0 && hour == 0) { cellStyle.setDataFormat(dataFormat.getFormat("m/d/yy")); } else { cellStyle.setDataFormat(dataFormat.getFormat("m/d/yy h:mm")); } } cell.setCellStyle(cellStyle); } } else if (val instanceof Boolean) {// Boolean cell.setCellValue(((Boolean) val).booleanValue()); } else {// String cell.setCellValue((String) val.toString()); } }
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//from w ww . jav a2 s .c o 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.netxilia.impexp.impl.PoiUtils.java
License:Open Source License
public static CellStyle netxiliaStyle2Poi(Styles nxStyle, Workbook workbook, CellStyle poiStyle) { if (nxStyle == null) { return poiStyle; }/*from ww w. ja v a2 s . c o m*/ poiStyle.setWrapText(nxStyle.contains(DefaultStyle.nowrap.getStyle())); // font short bold = nxStyle.contains(DefaultStyle.bold.getStyle()) ? Font.BOLDWEIGHT_BOLD : Font.BOLDWEIGHT_NORMAL; byte underline = nxStyle.contains(DefaultStyle.underline.getStyle()) ? Font.U_SINGLE : Font.U_NONE; boolean italic = nxStyle.contains(DefaultStyle.italic.getStyle()); boolean strikeout = nxStyle.contains(DefaultStyle.strikeout.getStyle()); Font defaultFont = workbook.getFontAt(poiStyle.getFontIndex()); Font font = workbook.findFont(bold, defaultFont.getColor(), defaultFont.getFontHeight(), defaultFont.getFontName(), italic, strikeout, defaultFont.getTypeOffset(), underline); if (font == null) { font = workbook.createFont(); font.setBoldweight(bold); font.setItalic(italic); font.setUnderline(underline); font.setStrikeout(strikeout); } poiStyle.setFont(font); // borders if (nxStyle.contains(DefaultStyle.borderLeft.getStyle())) { poiStyle.setBorderLeft(CellStyle.BORDER_THIN); } if (nxStyle.contains(DefaultStyle.borderRight.getStyle())) { poiStyle.setBorderRight(CellStyle.BORDER_THIN); } if (nxStyle.contains(DefaultStyle.borderTop.getStyle())) { poiStyle.setBorderTop(CellStyle.BORDER_THIN); } if (nxStyle.contains(DefaultStyle.borderBottom.getStyle())) { poiStyle.setBorderBottom(CellStyle.BORDER_THIN); } // align if (nxStyle.contains(DefaultStyle.alignLeft.getStyle())) { poiStyle.setAlignment(CellStyle.ALIGN_LEFT); } else if (nxStyle.contains(DefaultStyle.alignRight.getStyle())) { poiStyle.setAlignment(CellStyle.ALIGN_RIGHT); } else if (nxStyle.contains(DefaultStyle.alignCenter.getStyle())) { poiStyle.setAlignment(CellStyle.ALIGN_CENTER); } else if (nxStyle.contains(DefaultStyle.alignJustify.getStyle())) { poiStyle.setAlignment(CellStyle.ALIGN_JUSTIFY); } return poiStyle; }
From source file:org.openmrs.module.reporting.common.ExcelUtilTest.java
License:Open Source License
@Test public void shouldAddStyle() throws Exception { Workbook wb = ExcelUtil.loadWorkbookFromResource("org/openmrs/module/reporting/common/ExcelUtilTest.xls"); Sheet sheet = wb.getSheet("Testing"); // Test Fonts Cell cell = getCellToTheRightOf(sheet, "String"); Assert.assertEquals("This is a String", ExcelUtil.getCellContents(cell)); Assert.assertEquals(Font.BOLDWEIGHT_NORMAL, ExcelUtil.getFont(cell).getBoldweight()); cell.setCellStyle(ExcelUtil.createCellStyle(wb, "bold")); Assert.assertEquals(Font.BOLDWEIGHT_BOLD, ExcelUtil.getFont(cell).getBoldweight()); Assert.assertFalse(ExcelUtil.getFont(cell).getItalic()); Assert.assertEquals(Font.U_NONE, ExcelUtil.getFont(cell).getUnderline()); cell.setCellStyle(ExcelUtil.createCellStyle(wb, "italic,underline")); Assert.assertTrue(ExcelUtil.getFont(cell).getItalic()); Assert.assertEquals(Font.U_SINGLE, ExcelUtil.getFont(cell).getUnderline()); int fontSize = ExcelUtil.getFont(cell).getFontHeightInPoints() + 1; cell.setCellStyle(ExcelUtil.createCellStyle(wb, "size=" + fontSize)); Assert.assertEquals((short) fontSize, ExcelUtil.getFont(cell).getFontHeightInPoints()); // Test other styles Assert.assertFalse(cell.getCellStyle().getWrapText()); Assert.assertEquals(CellStyle.ALIGN_GENERAL, cell.getCellStyle().getAlignment()); Assert.assertEquals(CellStyle.BORDER_NONE, cell.getCellStyle().getBorderBottom()); cell.setCellStyle(ExcelUtil.createCellStyle(wb, "wraptext,align=center,border=bottom")); Assert.assertTrue(cell.getCellStyle().getWrapText()); Assert.assertEquals(CellStyle.ALIGN_CENTER, cell.getCellStyle().getAlignment()); Assert.assertEquals(CellStyle.BORDER_THIN, cell.getCellStyle().getBorderBottom()); // Test Date//w w w . j a va 2 s . c om Date date = DateUtil.getDateTime(2013, 10, 31); cell.setCellValue(date); ExcelUtil.formatAsDate(cell); Assert.assertEquals(Cell.CELL_TYPE_NUMERIC, cell.getCellType()); Assert.assertTrue(ExcelUtil.isCellDateFormatted(cell)); Assert.assertEquals(date, ExcelUtil.getCellContents(cell)); }