List of usage examples for org.apache.poi.xssf.usermodel XSSFCellStyle setFillPattern
@Override public void setFillPattern(FillPatternType pattern)
From source file:com.frameworkset.platform.util.POIExcelUtil.java
License:Open Source License
public static XSSFCellStyle getHeadCellStyle(XSSFWorkbook wb, XSSFFont font) { XSSFCellStyle headCellStyle = wb.createCellStyle(); headCellStyle.setFont(font);//from w ww .j a va 2 s . c om headCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headCellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); headCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headCellStyle.setWrapText(false); headCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return headCellStyle; }
From source file:com.github.igor_kudryashov.utils.excel.ExcelWriter.java
License:Apache License
/** * Returns a style of cell//w w w . j a va 2s . c o m * * @param rownum * the number of row for count odd/even rows * @param entry * value of cell * @param header * <code>true</code> if this row is the header, otherwise * <code>false</code> * @return the cell style */ private XSSFCellStyle getCellStyle(int rownum, Object entry, boolean header) { XSSFCellStyle style; String name = entry.getClass().getName(); if (header) { name += "_header"; } else if ((rownum % 2) == 0) { name += "_even"; } if (styles.containsKey(name)) { // if we already have a style for this class, return it style = styles.get(name); } else { // create new style style = (XSSFCellStyle) workbook.createCellStyle(); style.setVerticalAlignment(VerticalAlignment.TOP); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, colorBorder); style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, colorBorder); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, colorBorder); // format data XSSFDataFormat fmt = (XSSFDataFormat) workbook.createDataFormat(); short format = 0; if (name.contains("Date")) { format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0xe)); style.setAlignment(CellStyle.ALIGN_LEFT); } else if (name.contains("Double")) { format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(2)); style.setAlignment(CellStyle.ALIGN_RIGHT); } else if (name.contains("Integer")) { format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(1)); style.setAlignment(CellStyle.ALIGN_RIGHT); } else { style.setAlignment(CellStyle.ALIGN_LEFT); if (!header) { style.setWrapText(true); } } if (header) { // for header style.setFillForegroundColor(colorHeaderBackground); style.setFillPattern(CellStyle.SOLID_FOREGROUND); } else if (name.contains("_even")) { // for even rows style.setFillForegroundColor(colorEvenCellBackground); style.setFillPattern(CellStyle.SOLID_FOREGROUND); } style.setDataFormat(format); // keep the style for reuse styles.put(name, style); } return style; }
From source file:com.hauldata.dbpa.file.book.XlsxTargetSheet.java
License:Apache License
/** * Translate styling to workbook CellStyle. * * @param stylesUsed tracks the styles that have been used in the workbook; it will be updated * @param fontsUsed tracks the fonts that have been used in the workbook; it may be updated * @param colorsUsed tracks the colors that have been used in the workbook; it may be updated *//*w w w . j a v a 2 s .c om*/ public CellStyle getCellStyle(SXSSFWorkbook book, Map<StylesWithFormatting, XSSFCellStyle> stylesUsed, Map<FontStyles, XSSFFont> fontsUsed, Map<Integer, XSSFColor> colorsUsed) { XSSFCellStyle cellStyle = stylesUsed.get(this); if (cellStyle != null) { return cellStyle; } cellStyle = (XSSFCellStyle) book.createCellStyle(); cellStyle.cloneStyleFrom(book.getCellStyleAt(formatIndex)); if (styles.bottomBorder.style != null) { cellStyle.setBorderBottom(resolveBorderStyle(styles.bottomBorder)); } if (styles.leftBorder.style != null) { cellStyle.setBorderLeft(resolveBorderStyle(styles.leftBorder)); } if (styles.rightBorder.style != null) { cellStyle.setBorderRight(resolveBorderStyle(styles.rightBorder)); } if (styles.topBorder.style != null) { cellStyle.setBorderTop(resolveBorderStyle(styles.topBorder)); } if (styles.bottomBorder.color != null) { cellStyle.setBottomBorderColor(getColor(styles.bottomBorder.color, book, colorsUsed)); } if (styles.leftBorder.color != null) { cellStyle.setLeftBorderColor(getColor(styles.leftBorder.color, book, colorsUsed)); } if (styles.rightBorder.color != null) { cellStyle.setRightBorderColor(getColor(styles.rightBorder.color, book, colorsUsed)); } if (styles.topBorder.color != null) { cellStyle.setTopBorderColor(getColor(styles.topBorder.color, book, colorsUsed)); } if (styles.backgroundColor != null) { cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(getColor(styles.backgroundColor, book, colorsUsed)); } if (styles.textAlign != null) { cellStyle.setAlignment(styles.textAlign); } if (!styles.font.areDefault()) { cellStyle.setFont(getFont(styles.font, book, fontsUsed, colorsUsed)); } stylesUsed.put(this, cellStyle); return cellStyle; }
From source file:com.l3.info.magenda.emplois_du_temps.Workbook.java
public void addCellStyle(String nom, Categorie cat) { XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle(); XSSFColor border = new XSSFColor(cat.getBorder()); style.setBorderBottom(BorderStyle.MEDIUM); style.setBottomBorderColor(border);/* w w w . ja v a 2 s .co m*/ style.setBorderLeft(BorderStyle.MEDIUM); style.setLeftBorderColor(border); style.setBorderRight(BorderStyle.MEDIUM); style.setRightBorderColor(border); style.setBorderTop(BorderStyle.MEDIUM); style.setTopBorderColor(border); style.setWrapText(true); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFillForegroundColor(new XSSFColor(cat.getBackground())); XSSFFont font = workbook.createFont(); font.setColor(new XSSFColor(cat.getForeground())); style.setFont(font); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); liste_des_styles.put("Style_" + nom, style); }
From source file:com.netsteadfast.greenstep.bsc.command.KpiPeriodTrendsExcelCommand.java
License:Apache License
@SuppressWarnings("unchecked") private void putTables(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception { XSSFCellStyle cellHeadStyle = wb.createCellStyle(); cellHeadStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#f5f5f5"))); cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellHeadStyle.setBorderBottom(BorderStyle.THIN); cellHeadStyle.setBorderTop(BorderStyle.THIN); cellHeadStyle.setBorderRight(BorderStyle.THIN); cellHeadStyle.setBorderLeft(BorderStyle.THIN); XSSFFont cellHeadFont = wb.createFont(); cellHeadFont.setBold(true);//from w ww . j av a 2 s. co m cellHeadStyle.setFont(cellHeadFont); sh.setColumnWidth(0, 12000); int row = 0; Row nowRow = sh.createRow(row); Cell cell1 = nowRow.createCell(0); cell1.setCellStyle(cellHeadStyle); cell1.setCellValue("KPI"); Cell cell2 = nowRow.createCell(1); cell2.setCellStyle(cellHeadStyle); cell2.setCellValue("Maximum"); Cell cell3 = nowRow.createCell(2); cell3.setCellStyle(cellHeadStyle); cell3.setCellValue("Target"); Cell cell4 = nowRow.createCell(3); cell4.setCellStyle(cellHeadStyle); cell4.setCellValue("Minimum"); Cell cell5 = nowRow.createCell(4); cell5.setCellStyle(cellHeadStyle); cell5.setCellValue("Current score"); Cell cell6 = nowRow.createCell(5); cell6.setCellStyle(cellHeadStyle); cell6.setCellValue("Previous score"); Cell cell7 = nowRow.createCell(6); cell7.setCellStyle(cellHeadStyle); cell7.setCellValue("Change(%)"); row++; List<PeriodTrendsData<KpiVO>> periodDatas = (List<PeriodTrendsData<KpiVO>>) context.get("periodDatas"); for (PeriodTrendsData<KpiVO> periodData : periodDatas) { nowRow = sh.createRow(row); cell1 = nowRow.createCell(0); cell1.setCellValue(periodData.getCurrent().getName()); cell2 = nowRow.createCell(1); cell2.setCellValue(periodData.getCurrent().getMax()); cell3 = nowRow.createCell(2); cell3.setCellValue(periodData.getCurrent().getTarget()); cell4 = nowRow.createCell(3); cell4.setCellValue(periodData.getCurrent().getMin()); cell5 = nowRow.createCell(4); cell5.setCellValue(BscReportSupportUtils.parse2(periodData.getCurrent().getScore())); cell6 = nowRow.createCell(5); cell6.setCellValue(BscReportSupportUtils.parse2(periodData.getPrevious().getScore())); cell7 = nowRow.createCell(6); cell7.setCellValue(BscReportSupportUtils.parse2(periodData.getChange())); row++; } nowRow = sh.createRow(row); cell1 = nowRow.createCell(0); cell1.setCellValue("Current period: " + (String) context.get("currentPeriodDateRange") + " , Previous period: " + (String) context.get("previousPeriodDateRange")); }
From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java
License:Apache License
private int createHead(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision) throws Exception { Row headRow = sh.createRow(row);/* ww w .j a v a 2s . c o m*/ headRow.setHeight((short) 700); int cell = 0; XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(vision.getBgColor())); XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(vision.getFontColor())); XSSFCellStyle cellHeadStyle = wb.createCellStyle(); cellHeadStyle.setFillForegroundColor(bgColor); cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellHeadFont = wb.createFont(); cellHeadFont.setBold(true); cellHeadFont.setColor(fnColor); cellHeadStyle.setFont(cellHeadFont); cellHeadStyle.setBorderBottom(BorderStyle.THIN); cellHeadStyle.setBorderTop(BorderStyle.THIN); cellHeadStyle.setBorderRight(BorderStyle.THIN); cellHeadStyle.setBorderLeft(BorderStyle.THIN); cellHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellHeadStyle.setAlignment(HorizontalAlignment.CENTER); cellHeadStyle.setWrapText(true); int cols = 12; for (int i = 0; i < cols; i++) { sh.setColumnWidth(i, 4000); Cell headCell1 = headRow.createCell(cell++); headCell1.setCellValue( vision.getTitle() + "\nscore: " + BscReportSupportUtils.parse2(vision.getScore())); headCell1.setCellStyle(cellHeadStyle); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1)); // ------------------------------------------------------------------------ bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getBackgroundColor())); fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getFontColor())); cellHeadStyle = wb.createCellStyle(); cellHeadStyle.setFillForegroundColor(bgColor); cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellHeadFont = wb.createFont(); cellHeadFont.setBold(true); cellHeadFont.setColor(fnColor); cellHeadStyle.setFont(cellHeadFont); cellHeadStyle.setBorderBottom(BorderStyle.THIN); cellHeadStyle.setBorderTop(BorderStyle.THIN); cellHeadStyle.setBorderRight(BorderStyle.THIN); cellHeadStyle.setBorderLeft(BorderStyle.THIN); cellHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellHeadStyle.setAlignment(HorizontalAlignment.CENTER); cellHeadStyle.setWrapText(true); row++; headRow = sh.createRow(row); cell = 0; int titleCols = 4; for (int i = 0; i < titleCols; i++) { Cell headCell1 = headRow.createCell(cell++); headCell1.setCellValue(BscReportPropertyUtils.getPerspectiveTitle()); headCell1.setCellStyle(cellHeadStyle); } for (int i = 0; i < titleCols; i++) { Cell headCell1 = headRow.createCell(cell++); headCell1.setCellValue(BscReportPropertyUtils.getObjectiveTitle()); headCell1.setCellStyle(cellHeadStyle); } for (int i = 0; i < titleCols; i++) { Cell headCell1 = headRow.createCell(cell++); headCell1.setCellValue(BscReportPropertyUtils.getKpiTitle()); headCell1.setCellStyle(cellHeadStyle); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, 3)); sh.addMergedRegion(new CellRangeAddress(row, row, 4, 7)); sh.addMergedRegion(new CellRangeAddress(row, row, 8, 11)); // ------------------------------------------------------------------------ return 2; }
From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java
License:Apache License
private int createMainBody(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision) throws Exception { Map<String, String> managementMap = BscKpiCode.getManagementMap(false); //Map<String, String> calculationMap = BscKpiCode.getCalculationMap(false); int itemCols = 4; int mrRow = row; for (int px = 0; px < vision.getPerspectives().size(); px++) { PerspectiveVO perspective = vision.getPerspectives().get(px); for (int ox = 0; ox < perspective.getObjectives().size(); ox++) { ObjectiveVO objective = perspective.getObjectives().get(ox); for (int kx = 0; kx < objective.getKpis().size(); kx++) { KpiVO kpi = objective.getKpis().get(kx); Row contentRow = sh.createRow(row++); contentRow.setHeight((short) 4000); int cell = 0; for (int i = 0; i < itemCols; i++) { String content = this.getItemsContent(perspective.getName(), perspective.getScore(), perspective.getWeight(), perspective.getTarget(), perspective.getMin()); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(perspective.getBgColor()))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false); cellFont.setColor(/*from w w w. j a v a2 s. c o m*/ new XSSFColor(SimpleUtils.getColorRGB4POIColor(perspective.getFontColor()))); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); Cell contentCell1 = contentRow.createCell(cell++); contentCell1.setCellValue("\n" + content); contentCell1.setCellStyle(cellStyle); if (i == 0 && ox == 0) { byte[] imgBytes = BscReportSupportUtils.getByteIconBase("PERSPECTIVES", perspective.getTarget(), perspective.getMin(), perspective.getScore(), "", "", 0); if (null != imgBytes) { SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(), contentCell1.getColumnIndex()); } } } for (int i = 0; i < itemCols; i++) { String content = this.getItemsContent(objective.getName(), objective.getScore(), objective.getWeight(), objective.getTarget(), objective.getMin()); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(objective.getBgColor()))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false); cellFont.setColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(objective.getFontColor()))); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); Cell contentCell1 = contentRow.createCell(cell++); contentCell1.setCellValue("\n" + content); contentCell1.setCellStyle(cellStyle); if (i == 0 && kx == 0) { byte[] imgBytes = BscReportSupportUtils.getByteIconBase("OBJECTIVES", objective.getTarget(), objective.getMin(), objective.getScore(), "", "", 0); if (null != imgBytes) { SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(), contentCell1.getColumnIndex()); } } } for (int i = 0; i < itemCols; i++) { //String content = this.getKpisContent(kpi, managementMap, calculationMap); String content = this.getKpisContent(kpi, managementMap); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getBgColor()))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false); cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getFontColor()))); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); Cell contentCell1 = contentRow.createCell(cell++); contentCell1.setCellValue("\n" + content); contentCell1.setCellStyle(cellStyle); if (i == 0) { byte[] imgBytes = BscReportSupportUtils.getByteIconBase("KPI", kpi.getTarget(), kpi.getMin(), kpi.getScore(), kpi.getCompareType(), kpi.getManagement(), kpi.getQuasiRange()); if (null != imgBytes) { SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(), contentCell1.getColumnIndex()); } } } } } } for (int px = 0; px < vision.getPerspectives().size(); px++) { PerspectiveVO perspective = vision.getPerspectives().get(px); sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow + perspective.getRow() - 1, 0, 3)); for (int ox = 0; ox < perspective.getObjectives().size(); ox++) { ObjectiveVO objective = perspective.getObjectives().get(ox); sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow + objective.getRow() - 1, 4, 7)); for (int kx = 0; kx < objective.getKpis().size(); kx++) { sh.addMergedRegion(new CellRangeAddress(mrRow + kx, mrRow + kx, 8, 11)); } mrRow += objective.getKpis().size(); } } return row++; }
From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java
License:Apache License
private int createDateRange(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context) throws Exception { String frequency = (String) context.get("frequency"); String startYearDate = StringUtils.defaultString((String) context.get("startYearDate")).trim(); String endYearDate = StringUtils.defaultString((String) context.get("endYearDate")).trim(); String startDate = StringUtils.defaultString((String) context.get("startDate")).trim(); String endDate = StringUtils.defaultString((String) context.get("endDate")).trim(); String date1 = startDate;/*from ww w.j a v a 2 s .c o m*/ String date2 = endDate; if (BscMeasureDataFrequency.FREQUENCY_QUARTER.equals(frequency) || BscMeasureDataFrequency.FREQUENCY_HALF_OF_YEAR.equals(frequency) || BscMeasureDataFrequency.FREQUENCY_YEAR.equals(frequency)) { date1 = startYearDate + "/01/01"; date2 = endYearDate + "/12/" + SimpleUtils.getMaxDayOfMonth(Integer.parseInt(endYearDate), 12); } Map<String, Object> headContentMap = new HashMap<String, Object>(); this.fillHeadContent(context, headContentMap); XSSFCellStyle cellStyleLabel = wb.createCellStyle(); cellStyleLabel.setFillForegroundColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getBackgroundColor()))); cellStyleLabel.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFontLabel = wb.createFont(); cellFontLabel.setBold(false); cellFontLabel .setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getFontColor()))); cellStyleLabel.setFont(cellFontLabel); cellStyleLabel.setWrapText(true); //cellStyleLabel.setVerticalAlignment(VerticalAlignment.CENTER); cellStyleLabel.setBorderBottom(BorderStyle.THIN); cellStyleLabel.setBorderTop(BorderStyle.THIN); cellStyleLabel.setBorderRight(BorderStyle.THIN); cellStyleLabel.setBorderLeft(BorderStyle.THIN); int cols = 4 + vision.getPerspectives().get(0).getObjectives().get(0).getKpis().get(0).getDateRangeScores() .size(); int cell = 0; for (int i = 0; i < cols; i++) { String content = "Frequency: " + BscMeasureDataFrequency.getFrequencyMap(false).get(frequency) + " Date range: " + date1 + " ~ " + date2 + "\n" + StringUtils.defaultString((String) headContentMap.get("headContent")); Row headRow = sh.createRow(row); headRow.setHeight((short) 700); Cell headCell1 = headRow.createCell(cell); headCell1.setCellValue(content); headCell1.setCellStyle(cellStyleLabel); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1)); row++; int kpiCols = 4; int kpiRows = 2; for (PerspectiveVO perspective : vision.getPerspectives()) { for (ObjectiveVO objective : perspective.getObjectives()) { for (KpiVO kpi : objective.getKpis()) { cell = 0; for (int r = 0; r < kpiRows; r++) { Row contentRow = sh.createRow(row++); contentRow.setHeight((short) 400); for (int c = 0; c < kpiCols; c++) { XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getBgColor()))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false); cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getFontColor()))); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); Cell contentCell1 = contentRow.createCell(c); contentCell1.setCellValue(kpi.getName()); contentCell1.setCellStyle(cellStyle); } cell = 4; if (r == 0) { // date for (int d = 0; d < kpi.getDateRangeScores().size(); d++) { DateRangeScoreVO dateRangeScore = kpi.getDateRangeScores().get(d); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(new XSSFColor( SimpleUtils.getColorRGB4POIColor(dateRangeScore.getBgColor()))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false); cellFont.setColor(new XSSFColor( SimpleUtils.getColorRGB4POIColor(dateRangeScore.getFontColor()))); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); Cell contentCell1 = contentRow.createCell(cell++); contentCell1.setCellValue(dateRangeScore.getDate()); contentCell1.setCellStyle(cellStyle); } } if (r == 1) { // score for (int d = 0; d < kpi.getDateRangeScores().size(); d++) { DateRangeScoreVO dateRangeScore = kpi.getDateRangeScores().get(d); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(new XSSFColor( SimpleUtils.getColorRGB4POIColor(dateRangeScore.getBgColor()))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false); cellFont.setColor(new XSSFColor( SimpleUtils.getColorRGB4POIColor(dateRangeScore.getFontColor()))); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); Cell contentCell1 = contentRow.createCell(cell++); contentCell1.setCellValue( " " + BscReportSupportUtils.parse2(dateRangeScore.getScore())); contentCell1.setCellStyle(cellStyle); byte[] imgBytes = BscReportSupportUtils.getByteIcon(kpi, dateRangeScore.getScore()); if (null != imgBytes) { SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(), contentCell1.getColumnIndex()); } } } } sh.addMergedRegion(new CellRangeAddress(row - 2, row - 1, 0, kpiCols - 1)); } } } return row++; }
From source file:com.netsteadfast.greenstep.bsc.command.KpisDashboardExcelCommand.java
License:Apache License
@SuppressWarnings("unchecked") private int putTables(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception { XSSFCellStyle cellHeadStyle = wb.createCellStyle(); cellHeadStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#f5f5f5"))); cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellHeadStyle.setBorderBottom(BorderStyle.THIN); cellHeadStyle.setBorderTop(BorderStyle.THIN); cellHeadStyle.setBorderRight(BorderStyle.THIN); cellHeadStyle.setBorderLeft(BorderStyle.THIN); XSSFFont cellHeadFont = wb.createFont(); cellHeadFont.setBold(true);/*from w w w .j ava2s. c o m*/ cellHeadStyle.setFont(cellHeadFont); sh.setColumnWidth(0, 12000); int left = 0; int row = 0; List<Map<String, Object>> chartDatas = (List<Map<String, Object>>) context.get("chartDatas"); for (Map<String, Object> data : chartDatas) { Row nowRow = sh.createRow(row); Map<String, Object> nodeData = (Map<String, Object>) ((List<Object>) data.get("datas")).get(0); if (row == 0) { Cell cell1 = nowRow.createCell(0); cell1.setCellStyle(cellHeadStyle); cell1.setCellValue("KPI"); Cell cell2 = nowRow.createCell(1); cell2.setCellStyle(cellHeadStyle); cell2.setCellValue("Maximum"); Cell cell3 = nowRow.createCell(2); cell3.setCellStyle(cellHeadStyle); cell3.setCellValue("Target"); Cell cell4 = nowRow.createCell(3); cell4.setCellStyle(cellHeadStyle); cell4.setCellValue("Minimum"); Cell cell5 = nowRow.createCell(4); cell5.setCellStyle(cellHeadStyle); cell5.setCellValue("Score"); List<Map<String, Object>> dateRangeScores = (List<Map<String, Object>>) nodeData .get("dateRangeScores"); for (Map<String, Object> rangeScore : dateRangeScores) { Cell cell = nowRow.createCell(5 + left); cell.setCellStyle(cellHeadStyle); cell.setCellValue(String.valueOf(rangeScore.get("date"))); left++; } row++; } left = 0; nowRow = sh.createRow(row); XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) nodeData.get("bgColor"))); XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) nodeData.get("fontColor"))); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(bgColor); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false); cellFont.setColor(fnColor); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); Cell cell1 = nowRow.createCell(0); cell1.setCellValue(String.valueOf(nodeData.get("name"))); Cell cell2 = nowRow.createCell(1); cell2.setCellValue(String.valueOf(nodeData.get("max"))); Cell cell3 = nowRow.createCell(2); cell3.setCellValue(String.valueOf(nodeData.get("target"))); Cell cell4 = nowRow.createCell(3); cell4.setCellValue(String.valueOf(nodeData.get("min"))); Cell cell5 = nowRow.createCell(4); cell5.setCellValue(String.valueOf(nodeData.get("score"))); cell5.setCellStyle(cellStyle); List<Map<String, Object>> dateRangeScores = (List<Map<String, Object>>) nodeData.get("dateRangeScores"); for (Map<String, Object> rangeScore : dateRangeScores) { bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) rangeScore.get("bgColor"))); fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) rangeScore.get("fontColor"))); cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(bgColor); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellFont = wb.createFont(); cellFont.setBold(false); cellFont.setColor(fnColor); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setAlignment(HorizontalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); Cell cell = nowRow.createCell(5 + left); cell.setCellStyle(cellHeadStyle); cell.setCellValue(String.valueOf(rangeScore.get("score"))); cell.setCellStyle(cellStyle); left++; } row++; } return row + 1; }
From source file:com.netsteadfast.greenstep.bsc.command.KpisDashboardExcelCommand.java
License:Apache License
@SuppressWarnings("unchecked") private int putCharts(XSSFWorkbook wb, XSSFSheet sh, Context context, int row) throws Exception { String barBase64Content = SimpleUtils.getPNGBase64Content((String) context.get("barChartsData")); BufferedImage barImage = SimpleUtils.decodeToImage(barBase64Content); ByteArrayOutputStream barBos = new ByteArrayOutputStream(); ImageIO.write(barImage, "png", barBos); barBos.flush();/*from w w w . j av a 2s .co m*/ SimpleUtils.setCellPicture(wb, sh, barBos.toByteArray(), row, 0); //int row = 28; row = row + 32; List<Map<String, Object>> chartDatas = (List<Map<String, Object>>) context.get("chartDatas"); String year = (String) context.get("dateRangeLabel"); XSSFCellStyle cellHeadStyle = wb.createCellStyle(); cellHeadStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#f5f5f5"))); cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellHeadFont = wb.createFont(); cellHeadFont.setBold(true); cellHeadStyle.setFont(cellHeadFont); int titleCellSize = 9; Row headRow = sh.createRow(row); for (int i = 0; i < titleCellSize; i++) { Cell headCell = headRow.createCell(i); headCell.setCellStyle(cellHeadStyle); headCell.setCellValue("KPIs metrics gauge ( " + year + " )"); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, titleCellSize - 1)); row = row + 1; int cellLeft = 5; int rowSpace = 17; for (Map<String, Object> data : chartDatas) { Map<String, Object> nodeData = (Map<String, Object>) ((List<Object>) data.get("datas")).get(0); String pngImageData = SimpleUtils.getPNGBase64Content((String) nodeData.get("outerHTML")); BufferedImage imageData = SimpleUtils.decodeToImage(pngImageData); ByteArrayOutputStream imgBos = new ByteArrayOutputStream(); ImageIO.write(imageData, "png", imgBos); imgBos.flush(); SimpleUtils.setCellPicture(wb, sh, imgBos.toByteArray(), row, 0); XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) nodeData.get("bgColor"))); XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor((String) nodeData.get("fontColor"))); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(bgColor); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(true); cellFont.setColor(fnColor); cellStyle.setFont(cellFont); int perTitleCellSize = 4; Row nowRow = sh.createRow(row); for (int i = 0; i < perTitleCellSize; i++) { Cell cell1 = nowRow.createCell(cellLeft); cell1.setCellStyle(cellStyle); cell1.setCellValue((String) nodeData.get("name")); } sh.addMergedRegion(new CellRangeAddress(row, row, cellLeft, cellLeft + perTitleCellSize - 1)); nowRow = sh.createRow(row + 1); Cell cell2 = nowRow.createCell(cellLeft); cell2.setCellValue("Maximum: " + String.valueOf(nodeData.get("max"))); nowRow = sh.createRow(row + 2); Cell cell3 = nowRow.createCell(cellLeft); cell3.setCellValue("Target: " + String.valueOf(nodeData.get("target"))); nowRow = sh.createRow(row + 3); Cell cell4 = nowRow.createCell(cellLeft); cell4.setCellValue("Min: " + String.valueOf(nodeData.get("min"))); nowRow = sh.createRow(row + 4); Cell cell5 = nowRow.createCell(cellLeft); cell5.setCellValue("Score: " + String.valueOf(nodeData.get("score"))); row += rowSpace; } return row; }