public void insertDataToExcel(int numRow, Object[] object) { try { if (null != wb.getSheetAt(0)) { Sheet aSheet = wb.getSheetAt(0); Row row = aSheet.getRow((short) numRow); if (row == null) row = aSheet.createRow((short) numRow); for (int i = 0; i < object.length; i++) { Cell csCell = row.createCell((short) i); CellStyle style = wb.createCellStyle(); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBottomBorderColor(HSSFColor.BLACK.index); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setRightBorderColor(HSSFColor.BLACK.index); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setTopBorderColor(HSSFColor.BLACK.index); csCell.setCellStyle(style); if (object[i] != null) csCell.setCellValue(object[i].toString()); else csCell.setCellValue("0"); } } } catch (Exception e) { System.out.println("insertDataToExcel" + e); } }
/** * 创建表格样式 * * @param wb 工作薄对象 * @return 样式列表 */ private Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); Font titleFont = wb.createFont(); titleFont.setFontName("Arial"); titleFont.setFontHeightInPoints((short) 16); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(titleFont); styles.put("title", style); style = wb.createCellStyle(); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); Font dataFont = wb.createFont(); dataFont.setFontName("Arial"); dataFont.setFontHeightInPoints((short) 10); style.setFont(dataFont); styles.put("data", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_LEFT); styles.put("data1", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_CENTER); styles.put("data2", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); style.setAlignment(CellStyle.ALIGN_RIGHT); styles.put("data3", style); style = wb.createCellStyle(); style.cloneStyleFrom(styles.get("data")); // style.setWrapText(true); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); Font headerFont = wb.createFont(); headerFont.setFontName("Arial"); headerFont.setFontHeightInPoints((short) 10); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); style.setFont(headerFont); styles.put("header", style); return styles; }
public CellStyle buildDimensionCellStyle(Sheet sheet) { CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER); String headerBGColor = (String) this.getProperty(PROPERTY_DIMENSION_NAME_BACKGROUND_COLOR); logger.debug("Header background color : " + headerBGColor); short backgroundColorIndex = headerBGColor != null ? IndexedColors.valueOf(headerBGColor).getIndex() : IndexedColors.valueOf(DEFAULT_DIMENSION_NAME_BACKGROUND_COLOR).getIndex(); cellStyle.setFillForegroundColor(backgroundColorIndex); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setBorderTop(CellStyle.BORDER_THIN); String bordeBorderColor = (String) this.getProperty(PROPERTY_HEADER_BORDER_COLOR); logger.debug("Header border color : " + bordeBorderColor); short borderColorIndex = bordeBorderColor != null ? IndexedColors.valueOf(bordeBorderColor).getIndex() : IndexedColors.valueOf(DEFAULT_HEADER_BORDER_COLOR).getIndex(); cellStyle.setLeftBorderColor(borderColorIndex); cellStyle.setRightBorderColor(borderColorIndex); cellStyle.setBottomBorderColor(borderColorIndex); cellStyle.setTopBorderColor(borderColorIndex); Font font = sheet.getWorkbook().createFont(); Short headerFontSize = (Short) this.getProperty(PROPERTY_HEADER_FONT_SIZE); logger.debug("Header font size : " + headerFontSize); short headerFontSizeShort = headerFontSize != null ? headerFontSize.shortValue() : DEFAULT_HEADER_FONT_SIZE; font.setFontHeightInPoints(headerFontSizeShort); String fontName = (String) this.getProperty(PROPERTY_FONT_NAME); logger.debug("Font name : " + fontName); fontName = fontName != null ? fontName : DEFAULT_FONT_NAME; font.setFontName(fontName); String color = (String) this.getProperty(PROPERTY_DIMENSION_NAME_COLOR); logger.debug("Dimension color : " + color); short colorIndex = bordeBorderColor != null ? IndexedColors.valueOf(color).getIndex() : IndexedColors.valueOf(DEFAULT_DIMENSION_NAME_COLOR).getIndex(); font.setColor(colorIndex); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setItalic(true); cellStyle.setFont(font); return cellStyle; }
private Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; // Title Style Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 16); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(titleFont); style.setWrapText(true); styles.put("title", style); // Sub Title Style Font subTitleFont = wb.createFont(); subTitleFont.setFontHeightInPoints((short) 14); subTitleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(subTitleFont); style.setWrapText(true); styles.put("subTitle", style); Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 11); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); return styles; }
public CellStyle buildDataCellStyle(Sheet sheet) { CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_RIGHT); cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER); String cellBGColor = (String) this.getProperty(PROPERTY_CELL_BACKGROUND_COLOR); logger.debug("Cell background color : " + cellBGColor); short backgroundColorIndex = cellBGColor != null ? IndexedColors.valueOf(cellBGColor).getIndex() : IndexedColors.valueOf(DEFAULT_CELL_BACKGROUND_COLOR).getIndex(); cellStyle.setFillForegroundColor(backgroundColorIndex); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setBorderTop(CellStyle.BORDER_THIN); String bordeBorderColor = (String) this.getProperty(PROPERTY_CELL_BORDER_COLOR); logger.debug("Cell border color : " + bordeBorderColor); short borderColorIndex = bordeBorderColor != null ? IndexedColors.valueOf(bordeBorderColor).getIndex() : IndexedColors.valueOf(DEFAULT_CELL_BORDER_COLOR).getIndex(); cellStyle.setLeftBorderColor(borderColorIndex); cellStyle.setRightBorderColor(borderColorIndex); cellStyle.setBottomBorderColor(borderColorIndex); cellStyle.setTopBorderColor(borderColorIndex); Font font = sheet.getWorkbook().createFont(); Short cellFontSize = (Short) this.getProperty(PROPERTY_CELL_FONT_SIZE); logger.debug("Cell font size : " + cellFontSize); short cellFontSizeShort = cellFontSize != null ? cellFontSize.shortValue() : DEFAULT_CELL_FONT_SIZE; font.setFontHeightInPoints(cellFontSizeShort); String fontName = (String) this.getProperty(PROPERTY_FONT_NAME); logger.debug("Font name : " + fontName); fontName = fontName != null ? fontName : DEFAULT_FONT_NAME; font.setFontName(fontName); String cellColor = (String) this.getProperty(PROPERTY_CELL_COLOR); logger.debug("Cell color : " + cellColor); short cellColorIndex = cellColor != null ? IndexedColors.valueOf(cellColor).getIndex() : IndexedColors.valueOf(DEFAULT_CELL_COLOR).getIndex(); font.setColor(cellColorIndex); cellStyle.setFont(font); return cellStyle; }
private void getAllAroundBorder(CellStyle style) { style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); }
/** Create a library of cell styles */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 11); monthFont.setColor(IndexedColors.WHITE.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); style.setWrapText(true); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
private CellStyle buildCellStyle() { CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setBorderTop(CellStyle.BORDER_THIN); cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderLeft(CellStyle.BORDER_THIN); cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderRight(CellStyle.BORDER_THIN); cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderBottom(CellStyle.BORDER_THIN); cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); return cellStyle; }
/** @Description: 初始化内容行样式 */ private static void initContentCellStyle() { contentStyle.setAlignment(CellStyle.ALIGN_CENTER); contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); contentStyle.setFont(contentFont); contentStyle.setBorderTop(CellStyle.BORDER_THIN); contentStyle.setBorderBottom(CellStyle.BORDER_THIN); contentStyle.setBorderLeft(CellStyle.BORDER_THIN); contentStyle.setBorderRight(CellStyle.BORDER_THIN); contentStyle.setTopBorderColor(IndexedColors.BLUE.index); contentStyle.setBottomBorderColor(IndexedColors.BLUE.index); contentStyle.setLeftBorderColor(IndexedColors.BLUE.index); contentStyle.setRightBorderColor(IndexedColors.BLUE.index); contentStyle.setWrapText(true); // 字段换行 }
/** @Description: 初始化表头行样式 */ private static void initHeadCellStyle() { headStyle.setAlignment(CellStyle.ALIGN_CENTER); headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); headStyle.setFont(headFont); headStyle.setFillBackgroundColor(IndexedColors.YELLOW.index); headStyle.setBorderTop(CellStyle.BORDER_MEDIUM); headStyle.setBorderBottom(CellStyle.BORDER_THIN); headStyle.setBorderLeft(CellStyle.BORDER_THIN); headStyle.setBorderRight(CellStyle.BORDER_THIN); headStyle.setTopBorderColor(IndexedColors.BLUE.index); headStyle.setBottomBorderColor(IndexedColors.BLUE.index); headStyle.setLeftBorderColor(IndexedColors.BLUE.index); headStyle.setRightBorderColor(IndexedColors.BLUE.index); }
public void setCellMoney(int numRow, int numCol) { try { if (wb.getSheetAt(0) != null) { Sheet aSheet = wb.getSheetAt(0); DataFormat format = wb.createDataFormat(); Row row = aSheet.getRow((short) numRow); Cell csCell = row.getCell((short) numCol); CellStyle style = wb.createCellStyle(); style.setDataFormat(format.getFormat("0.00")); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBottomBorderColor(HSSFColor.BLACK.index); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setRightBorderColor(HSSFColor.BLACK.index); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setTopBorderColor(HSSFColor.BLACK.index); csCell.setCellStyle(style); } } catch (Exception e) { System.out.println("insertDataToExcel" + e); } }
private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); short borderColor = IndexedColors.GREY_50_PERCENT.getIndex(); CellStyle style; DataFormat format = wb.createDataFormat(); Font titleFont = wb.createFont(); titleFont.setFontName("Tahoma"); titleFont.setFontHeightInPoints((short) 12); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font headFont = wb.createFont(); headFont.setFontName("Tahoma"); headFont.setFontHeightInPoints((short) 11); headFont.setColor(IndexedColors.WHITE.getIndex()); headFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headFont); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setWrapText(true); styles.put("header", style); Font groupFont = wb.createFont(); groupFont.setFontName("Tahoma"); groupFont.setFontHeightInPoints((short) 11); groupFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setWrapText(true); style.setFont(groupFont); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("groupleft", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setWrapText(true); style.setFont(groupFont); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("groupright", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setWrapText(true); /* style.setFont(groupFont); */ style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("groupcenter", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setWrapText(true); style.setDataFormat(format.getFormat("#,##0")); /* style.setFont(groupFont); */ style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("groupnumber", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); /* style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); */ styles.put("cellleft", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cellcenter", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cellright", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setWrapText(true); style.setDataFormat(format.getFormat("#,##0")); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cellnumber", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setWrapText(true); style.setDataFormat(format.getFormat("#,##0.00")); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cellnumber2", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); style.setWrapText(true); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); styles.put("celltop", style); return styles; }
private static Map createStyles(Workbook wb) { Map styles = new HashMap(); Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 14); titleFont.setFontName("Trebuchet MS"); CellStyle style = wb.createCellStyle(); style.setFont(titleFont); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); styles.put("title", style); Font itemFont = wb.createFont(); itemFont.setFontHeightInPoints((short) 9); itemFont.setFontName("Trebuchet MS"); style = wb.createCellStyle(); style.setAlignment((short) 1); style.setFont(itemFont); styles.put("item_left", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); styles.put("item_right", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat( wb.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)")); styles.put("input_$", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0.000%")); styles.put("input_%", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); styles.put("input_i", style); style = wb.createCellStyle(); style.setAlignment((short) 2); style.setFont(itemFont); style.setDataFormat(wb.createDataFormat().getFormat("m/d/yy")); styles.put("input_d", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("$##,##0.00")); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern((short) 1); styles.put("formula_$", style); style = wb.createCellStyle(); style.setAlignment((short) 3); style.setFont(itemFont); style.setBorderRight((short) 7); style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderLeft((short) 7); style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setBorderTop((short) 7); style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setDataFormat(wb.createDataFormat().getFormat("0")); style.setBorderBottom((short) 7); style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern((short) 1); styles.put("formula_i", style); return styles; }