List of usage examples for org.apache.poi.ss.usermodel Cell setCellStyle
void setCellStyle(CellStyle style);
Set the style for the cell.
From source file:com.netsteadfast.greenstep.bsc.command.ObjectivesDashboardExcelCommand.java
License:Apache License
@SuppressWarnings("unchecked") private int putCharts(XSSFWorkbook wb, XSSFSheet sh, Context context) 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 ww w .j av a2s .c o m*/ SimpleUtils.setCellPicture(wb, sh, barBos.toByteArray(), 0, 0); int row = 28; List<Map<String, Object>> chartDatas = (List<Map<String, Object>>) context.get("chartDatas"); String year = (String) context.get("year"); 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 = 14; Row headRow = sh.createRow(row); for (int i = 0; i < titleCellSize; i++) { Cell headCell = headRow.createCell(i); headCell.setCellStyle(cellHeadStyle); headCell.setCellValue("Objectives metrics gauge ( " + year + " )"); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, titleCellSize - 1)); row = row + 1; int cellLeft = 10; 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("Target: " + String.valueOf(nodeData.get("target"))); nowRow = sh.createRow(row + 2); Cell cell3 = nowRow.createCell(cellLeft); cell3.setCellValue("Min: " + String.valueOf(nodeData.get("min"))); nowRow = sh.createRow(row + 3); Cell cell4 = nowRow.createCell(cellLeft); cell4.setCellValue("Score: " + String.valueOf(nodeData.get("score"))); row += rowSpace; } return row; }
From source file:com.netsteadfast.greenstep.bsc.command.OrganizationReportExcelCommand.java
License:Apache License
private int createHead(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context) throws Exception { String dateType = (String) context.get("dateType"); String year = (String) context.get("startYearDate"); String orgId = (String) context.get("orgId"); String departmentName = ""; String dateTypeName = "Year"; if ("1".equals(dateType)) { dateTypeName = "In the first half"; }/*w w w. j ava2 s.c om*/ if ("2".equals(dateType)) { dateTypeName = "In the second half"; } OrganizationVO organization = new OrganizationVO(); organization.setOrgId(orgId); DefaultResult<OrganizationVO> result = this.organizationService.findByUK(organization); if (result.getValue() != null) { organization = result.getValue(); departmentName = organization.getName(); } Row headRow = sh.createRow(row); headRow.setHeight((short) 700); XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#F2F2F2")); XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#000000")); 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 = 6; for (int i = 0; i < cols; i++) { sh.setColumnWidth(i, 6000); Cell headCell1 = headRow.createCell(i); headCell1.setCellValue("Personal Balance SourceCard"); headCell1.setCellStyle(cellHeadStyle); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1)); row++; headRow = sh.createRow(row); for (int i = 0; i < cols; i++) { sh.setColumnWidth(i, 6000); Cell headCell1 = headRow.createCell(i); headCell1.setCellValue(vision.getTitle()); headCell1.setCellStyle(cellHeadStyle); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1)); row++; headRow = sh.createRow(row); Cell titleCell1 = headRow.createCell(0); titleCell1.setCellValue("Department"); titleCell1.setCellStyle(cellHeadStyle); Cell titleCell2 = headRow.createCell(1); titleCell2.setCellValue(departmentName); titleCell2.setCellStyle(cellHeadStyle); Cell titleCell3 = headRow.createCell(2); titleCell3.setCellValue(departmentName); titleCell3.setCellStyle(cellHeadStyle); Cell titleCell4 = headRow.createCell(3); titleCell4.setCellValue(departmentName); titleCell4.setCellStyle(cellHeadStyle); Cell titleCell5 = headRow.createCell(4); titleCell5.setCellValue(departmentName); titleCell5.setCellStyle(cellHeadStyle); Cell titleCell6 = headRow.createCell(5); titleCell6.setCellValue(year + " " + dateTypeName); titleCell6.setCellStyle(cellHeadStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, cols - 2)); row++; headRow = sh.createRow(row); titleCell1 = headRow.createCell(0); titleCell1.setCellValue(BscReportPropertyUtils.getPerspectiveTitle()); titleCell1.setCellStyle(cellHeadStyle); titleCell2 = headRow.createCell(1); titleCell2.setCellValue(BscReportPropertyUtils.getObjectiveTitle()); titleCell2.setCellStyle(cellHeadStyle); titleCell3 = headRow.createCell(2); titleCell3.setCellValue(BscReportPropertyUtils.getKpiTitle()); titleCell3.setCellStyle(cellHeadStyle); titleCell4 = headRow.createCell(3); titleCell4.setCellValue("Weight"); titleCell4.setCellStyle(cellHeadStyle); titleCell5 = headRow.createCell(4); titleCell5.setCellValue("Maximum\nTarget\nMinimum"); titleCell5.setCellStyle(cellHeadStyle); titleCell6 = headRow.createCell(5); titleCell6.setCellValue("Score"); titleCell6.setCellStyle(cellHeadStyle); row = row + 1; return row; }
From source file:com.netsteadfast.greenstep.bsc.command.OrganizationReportExcelCommand.java
License:Apache License
private int createMainBody(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision) throws Exception { XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff"))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false);/* w w w. ja va 2s. c om*/ cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#000000"))); 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); 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++); Cell cell1 = contentRow.createCell(0); cell1.setCellValue(perspective.getName()); cell1.setCellStyle(cellStyle); Cell titleCell2 = contentRow.createCell(1); titleCell2.setCellValue(objective.getName()); titleCell2.setCellStyle(cellStyle); Cell titleCell3 = contentRow.createCell(2); titleCell3.setCellValue(kpi.getName()); titleCell3.setCellStyle(cellStyle); Cell titleCell4 = contentRow.createCell(3); titleCell4.setCellValue(kpi.getWeight() + "%"); titleCell4.setCellStyle(cellStyle); Cell titleCell5 = contentRow.createCell(4); titleCell5.setCellValue("max: " + kpi.getMax() + "\n" + "target: " + kpi.getTarget() + "\n" + "min: " + kpi.getMin() + "\n" + "unit: " + kpi.getUnit()); titleCell5.setCellStyle(cellStyle); DateRangeScoreVO dateRangeScore = kpi.getDateRangeScores().get(0); XSSFCellStyle cellStyle2 = wb.createCellStyle(); cellStyle2.setFillForegroundColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(dateRangeScore.getBgColor()))); cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont2 = wb.createFont(); cellFont2.setBold(false); cellFont2.setColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(dateRangeScore.getFontColor()))); cellStyle2.setFont(cellFont2); cellStyle2.setWrapText(true); cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle2.setBorderBottom(BorderStyle.THIN); cellStyle2.setBorderTop(BorderStyle.THIN); cellStyle2.setBorderRight(BorderStyle.THIN); cellStyle2.setBorderLeft(BorderStyle.THIN); Cell titleCell6 = contentRow.createCell(5); titleCell6.setCellValue(BscReportSupportUtils.parse2(dateRangeScore.getScore())); titleCell6.setCellStyle(cellStyle2); } } } for (int px = 0; px < vision.getPerspectives().size(); px++) { PerspectiveVO perspective = vision.getPerspectives().get(px); // 2016-12-13 old work with POI 3.12 //sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow + perspective.getRow()-1, 0, 0)); // 2016-12-13 new work with POI 3.15 int mrRow1 = mrRow + perspective.getRow() - 1; if (mrRow1 > mrRow) { sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow1, 0, 0)); } for (int ox = 0; ox < perspective.getObjectives().size(); ox++) { ObjectiveVO objective = perspective.getObjectives().get(ox); // 2016-12-13 old work with POI 3.12 //sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow + objective.getRow()-1, 1, 1)); // 2016-12-13 new work with POI 3.15 int mrRow2 = mrRow + objective.getRow() - 1; if (mrRow2 > mrRow) { sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow2, 1, 1)); } mrRow += objective.getKpis().size(); } } return row++; }
From source file:com.netsteadfast.greenstep.bsc.command.PdcaReportExcelCommand.java
License:Apache License
private int createPdca(XSSFWorkbook wb, XSSFSheet sh, int row, Context context) throws Exception { PdcaVO pdca = (PdcaVO) this.getResult(context); Row headRow = sh.createRow(row);/*w w w . j a v a 2 s . co m*/ headRow.setHeight((short) 700); // -------------------------------------------------------------------------------------- XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#d8d8d8")); XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#000000")); 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 = 6; for (int i = 0; i < cols; i++) { sh.setColumnWidth(i, 6000); Cell headCell1 = headRow.createCell(i); headCell1.setCellValue(pdca.getTitle()); headCell1.setCellStyle(cellHeadStyle); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1)); // -------------------------------------------------------------------------------------- XSSFColor bgLabelColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#F2F2F2")); XSSFCellStyle cellLabelStyle = wb.createCellStyle(); cellLabelStyle.setFillForegroundColor(bgLabelColor); cellLabelStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellLabelFont = wb.createFont(); cellLabelFont.setBold(true); cellLabelFont.setColor(fnColor); cellLabelStyle.setFont(cellLabelFont); cellLabelStyle.setBorderBottom(BorderStyle.THIN); cellLabelStyle.setBorderTop(BorderStyle.THIN); cellLabelStyle.setBorderRight(BorderStyle.THIN); cellLabelStyle.setBorderLeft(BorderStyle.THIN); cellLabelStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellLabelStyle.setAlignment(HorizontalAlignment.LEFT); cellLabelStyle.setWrapText(true); // -------------------------------------------------------------------------------------- XSSFColor bgNormalColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff")); XSSFCellStyle cellNormalStyle = wb.createCellStyle(); cellNormalStyle.setFillForegroundColor(bgNormalColor); cellNormalStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellNormalFont = wb.createFont(); cellNormalFont.setBold(false); cellNormalFont.setColor(fnColor); cellNormalStyle.setFont(cellNormalFont); cellNormalStyle.setBorderBottom(BorderStyle.THIN); cellNormalStyle.setBorderTop(BorderStyle.THIN); cellNormalStyle.setBorderRight(BorderStyle.THIN); cellNormalStyle.setBorderLeft(BorderStyle.THIN); cellNormalStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellNormalStyle.setAlignment(HorizontalAlignment.LEFT); cellNormalStyle.setWrapText(true); // -------------------------------------------------------------------------------------- row++; Row labelRow = sh.createRow(row); Cell labelCell_0_1 = labelRow.createCell(0); labelCell_0_1.setCellValue("Responsibility"); labelCell_0_1.setCellStyle(cellLabelStyle); Cell labelCell_0_2 = labelRow.createCell(1); labelCell_0_2.setCellValue(pdca.getResponsibilityAppendNames()); labelCell_0_2.setCellStyle(cellNormalStyle); Cell labelCell_0_3 = labelRow.createCell(2); labelCell_0_3.setCellValue(pdca.getResponsibilityAppendNames()); labelCell_0_3.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, 2)); Cell labelCell_0_4 = labelRow.createCell(3); labelCell_0_4.setCellValue("Date range"); labelCell_0_4.setCellStyle(cellLabelStyle); Cell labelCell_0_5 = labelRow.createCell(4); labelCell_0_5.setCellValue(pdca.getStartDateDisplayValue() + " ~ " + pdca.getEndDateDisplayValue()); labelCell_0_5.setCellStyle(cellNormalStyle); Cell labelCell_0_6 = labelRow.createCell(5); labelCell_0_6.setCellValue(pdca.getStartDateDisplayValue() + " ~ " + pdca.getEndDateDisplayValue()); labelCell_0_6.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 4, 5)); // -------------------------------------------------------------------------------------- row++; labelRow = sh.createRow(row); Cell labelCell_1_1 = labelRow.createCell(0); labelCell_1_1.setCellValue("Confirm"); labelCell_1_1.setCellStyle(cellLabelStyle); Cell labelCell_1_2 = labelRow.createCell(1); labelCell_1_2.setCellValue(pdca.getConfirmEmployeeName()); labelCell_1_2.setCellStyle(cellNormalStyle); Cell labelCell_1_3 = labelRow.createCell(2); labelCell_1_3.setCellValue(pdca.getConfirmEmployeeName()); labelCell_1_3.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, 2)); Cell labelCell_1_4 = labelRow.createCell(3); labelCell_1_4.setCellValue("Confirm date"); labelCell_1_4.setCellStyle(cellLabelStyle); Cell labelCell_1_5 = labelRow.createCell(4); labelCell_1_5.setCellValue(pdca.getConfirmDateDisplayValue()); labelCell_1_5.setCellStyle(cellNormalStyle); Cell labelCell_1_6 = labelRow.createCell(5); labelCell_1_6.setCellValue(pdca.getConfirmDateDisplayValue()); labelCell_1_6.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 4, 5)); // -------------------------------------------------------------------------------------- row++; labelRow = sh.createRow(row); Cell labelCell_2_1 = labelRow.createCell(0); labelCell_2_1.setCellValue("Organization\nDepartment"); labelCell_2_1.setCellStyle(cellLabelStyle); Cell labelCell_2_2 = labelRow.createCell(1); labelCell_2_2.setCellValue(pdca.getOrganizationAppendNames()); labelCell_2_2.setCellStyle(cellNormalStyle); Cell labelCell_2_3 = labelRow.createCell(2); labelCell_2_3.setCellValue(pdca.getOrganizationAppendNames()); labelCell_2_3.setCellStyle(cellNormalStyle); Cell labelCell_2_4 = labelRow.createCell(3); labelCell_2_4.setCellValue(pdca.getOrganizationAppendNames()); labelCell_2_4.setCellStyle(cellNormalStyle); Cell labelCell_2_5 = labelRow.createCell(4); labelCell_2_5.setCellValue(pdca.getOrganizationAppendNames()); labelCell_2_5.setCellStyle(cellNormalStyle); Cell labelCell_2_6 = labelRow.createCell(5); labelCell_2_6.setCellValue(pdca.getOrganizationAppendNames()); labelCell_2_6.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, 5)); // -------------------------------------------------------------------------------------- row++; labelRow = sh.createRow(row); Cell labelCell_3_1 = labelRow.createCell(0); labelCell_3_1.setCellValue("KPIs"); labelCell_3_1.setCellStyle(cellLabelStyle); Cell labelCell_3_2 = labelRow.createCell(1); labelCell_3_2.setCellValue(pdca.getKpisAppendNames()); labelCell_3_2.setCellStyle(cellNormalStyle); Cell labelCell_3_3 = labelRow.createCell(2); labelCell_3_3.setCellValue(pdca.getKpisAppendNames()); labelCell_3_3.setCellStyle(cellNormalStyle); Cell labelCell_3_4 = labelRow.createCell(3); labelCell_3_4.setCellValue(pdca.getKpisAppendNames()); labelCell_3_4.setCellStyle(cellNormalStyle); Cell labelCell_3_5 = labelRow.createCell(4); labelCell_3_5.setCellValue(pdca.getKpisAppendNames()); labelCell_3_5.setCellStyle(cellNormalStyle); Cell labelCell_3_6 = labelRow.createCell(5); labelCell_3_6.setCellValue(pdca.getKpisAppendNames()); labelCell_3_6.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, 5)); // -------------------------------------------------------------------------------------- row++; labelRow = sh.createRow(row); Cell labelCell_4_1 = labelRow.createCell(0); labelCell_4_1.setCellValue("Parent PDCA"); labelCell_4_1.setCellStyle(cellLabelStyle); Cell labelCell_4_2 = labelRow.createCell(1); labelCell_4_2.setCellValue(pdca.getParentName()); labelCell_4_2.setCellStyle(cellNormalStyle); Cell labelCell_4_3 = labelRow.createCell(2); labelCell_4_3.setCellValue(pdca.getParentName()); labelCell_4_3.setCellStyle(cellNormalStyle); Cell labelCell_4_4 = labelRow.createCell(3); labelCell_4_4.setCellValue(pdca.getParentName()); labelCell_4_4.setCellStyle(cellNormalStyle); Cell labelCell_4_5 = labelRow.createCell(4); labelCell_4_5.setCellValue(pdca.getParentName()); labelCell_4_5.setCellStyle(cellNormalStyle); Cell labelCell_4_6 = labelRow.createCell(5); labelCell_4_6.setCellValue(pdca.getParentName()); labelCell_4_6.setCellStyle(cellNormalStyle); sh.addMergedRegion(new CellRangeAddress(row, row, 1, 5)); // -------------------------------------------------------------------------------------- row++; labelRow = sh.createRow(row); Cell labelCell_6_1 = labelRow.createCell(0); labelCell_6_1.setCellValue("TYPE"); labelCell_6_1.setCellStyle(cellLabelStyle); Cell labelCell_6_2 = labelRow.createCell(1); labelCell_6_2.setCellValue("Title"); labelCell_6_2.setCellStyle(cellLabelStyle); Cell labelCell_6_3 = labelRow.createCell(2); labelCell_6_3.setCellValue("Responsibility"); labelCell_6_3.setCellStyle(cellLabelStyle); Cell labelCell_6_4 = labelRow.createCell(3); labelCell_6_4.setCellValue("Date range"); labelCell_6_4.setCellStyle(cellLabelStyle); Cell labelCell_6_5 = labelRow.createCell(4); labelCell_6_5.setCellValue("Audit"); labelCell_6_5.setCellStyle(cellLabelStyle); Cell labelCell_6_6 = labelRow.createCell(5); labelCell_6_6.setCellValue("Audit date"); labelCell_6_6.setCellStyle(cellLabelStyle); // -------------------------------------------------------------------------------------- row++; int nRow = row; row = this.createPdcaItem(wb, sh, row, cellNormalStyle, pdca.getItemPlan(), pdca.getAuditPlan()); row = this.createPdcaItem(wb, sh, row, cellNormalStyle, pdca.getItemDo(), pdca.getAuditDo()); row = this.createPdcaItem(wb, sh, row, cellNormalStyle, pdca.getItemCheck(), pdca.getAuditCheck()); row = this.createPdcaItem(wb, sh, row, cellNormalStyle, pdca.getItemAction(), pdca.getAuditAction()); nRow = this.mergedRegionForItemsRow(wb, sh, nRow, pdca.getItemPlan()); nRow = this.mergedRegionForItemsRow(wb, sh, nRow, pdca.getItemDo()); nRow = this.mergedRegionForItemsRow(wb, sh, nRow, pdca.getItemCheck()); nRow = this.mergedRegionForItemsRow(wb, sh, nRow, pdca.getItemAction()); return row; }
From source file:com.netsteadfast.greenstep.bsc.command.PdcaReportExcelCommand.java
License:Apache License
private int createPdcaItem(XSSFWorkbook wb, XSSFSheet sh, int row, XSSFCellStyle cellNormalStyle, List<PdcaItemVO> items, PdcaAuditVO audit) throws Exception { XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#000000")); XSSFColor bgLabelColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#F2F2F2")); XSSFCellStyle cellLabelStyle = wb.createCellStyle(); cellLabelStyle.setFillForegroundColor(bgLabelColor); cellLabelStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellLabelFont = wb.createFont(); cellLabelFont.setBold(true);//from w w w . ja v a 2 s . c om cellLabelFont.setColor(fnColor); cellLabelStyle.setFont(cellLabelFont); cellLabelStyle.setBorderBottom(BorderStyle.THIN); cellLabelStyle.setBorderTop(BorderStyle.THIN); cellLabelStyle.setBorderRight(BorderStyle.THIN); cellLabelStyle.setBorderLeft(BorderStyle.THIN); cellLabelStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellLabelStyle.setAlignment(HorizontalAlignment.CENTER); cellLabelStyle.setWrapText(true); Map<String, String> pdcaTypeMap = PdcaType.getDataMap(false); for (PdcaItemVO item : items) { Row labelRow = sh.createRow(row); Cell labelCell_6_1 = labelRow.createCell(0); labelCell_6_1.setCellValue(pdcaTypeMap.get(item.getType())); labelCell_6_1.setCellStyle(cellLabelStyle); Cell labelCell_6_2 = labelRow.createCell(1); labelCell_6_2.setCellValue(item.getTitle() + (!StringUtils.isBlank(item.getDescription()) ? "\n\n" + item.getDescription() : "")); labelCell_6_2.setCellStyle(cellNormalStyle); Cell labelCell_6_3 = labelRow.createCell(2); labelCell_6_3.setCellValue(item.getEmployeeAppendNames()); labelCell_6_3.setCellStyle(cellNormalStyle); Cell labelCell_6_4 = labelRow.createCell(3); labelCell_6_4.setCellValue(item.getStartDateDisplayValue() + " ~ " + item.getEndDateDisplayValue()); labelCell_6_4.setCellStyle(cellNormalStyle); Cell labelCell_6_5 = labelRow.createCell(4); labelCell_6_5.setCellValue((audit != null ? audit.getEmpId() : " ")); labelCell_6_5.setCellStyle(cellNormalStyle); Cell labelCell_6_6 = labelRow.createCell(5); labelCell_6_6.setCellValue((audit != null ? audit.getConfirmDateDisplayValue() : " ")); labelCell_6_6.setCellStyle(cellNormalStyle); row++; } return row; }
From source file:com.netsteadfast.greenstep.bsc.command.PersonalReportExcelCommand.java
License:Apache License
private int createHead(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context) throws Exception { String dateType = (String) context.get("dateType"); String year = (String) context.get("startYearDate"); String empId = (String) context.get("empId"); String account = (String) context.get("account"); String fullName = ""; String jobTitle = ""; String departmentName = ""; String dateTypeName = "Year"; if ("1".equals(dateType)) { dateTypeName = "In the first half"; }/* ww w. j a v a2 s .co m*/ if ("2".equals(dateType)) { dateTypeName = "In the second half"; } EmployeeVO employee = new EmployeeVO(); employee.setEmpId(empId); employee.setAccount(account); DefaultResult<EmployeeVO> result = this.employeeService.findByUK(employee); if (result.getValue() != null) { fullName = result.getValue().getEmpId() + " - " + result.getValue().getFullName(); jobTitle = result.getValue().getEmpId() + " - " + result.getValue().getFullName(); List<String> appendIds = this.organizationService .findForAppendOrganizationOids(result.getValue().getEmpId()); List<String> appendNames = this.organizationService.findForAppendNames(appendIds); StringBuilder sb = new StringBuilder(); for (int i = 0; appendNames != null && i < appendNames.size(); i++) { sb.append(appendNames.get(i)).append(Constants.ID_DELIMITER); } departmentName = sb.toString(); } Row headRow = sh.createRow(row); headRow.setHeight((short) 700); XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#F2F2F2")); XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor("#000000")); 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 = 6; for (int i = 0; i < cols; i++) { sh.setColumnWidth(i, 6000); Cell headCell1 = headRow.createCell(i); headCell1.setCellValue("Personal Balance SourceCard"); headCell1.setCellStyle(cellHeadStyle); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1)); row++; headRow = sh.createRow(row); for (int i = 0; i < cols; i++) { sh.setColumnWidth(i, 6000); Cell headCell1 = headRow.createCell(i); headCell1.setCellValue(vision.getTitle()); headCell1.setCellStyle(cellHeadStyle); } sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1)); row++; headRow = sh.createRow(row); headRow.setHeight((short) 700); Cell titleCell1 = headRow.createCell(0); titleCell1.setCellValue("Job Title"); titleCell1.setCellStyle(cellHeadStyle); Cell titleCell2 = headRow.createCell(1); titleCell2.setCellValue(jobTitle); titleCell2.setCellStyle(cellHeadStyle); Cell titleCell3 = headRow.createCell(2); titleCell3.setCellValue("Department"); titleCell3.setCellStyle(cellHeadStyle); Cell titleCell4 = headRow.createCell(3); titleCell4.setCellValue(departmentName); titleCell4.setCellStyle(cellHeadStyle); Cell titleCell5 = headRow.createCell(4); titleCell5.setCellValue("name: " + fullName); titleCell5.setCellStyle(cellHeadStyle); Cell titleCell6 = headRow.createCell(5); titleCell6.setCellValue("Annual assessment: " + year); titleCell6.setCellStyle(cellHeadStyle); row++; headRow = sh.createRow(row); titleCell1 = headRow.createCell(0); titleCell1.setCellValue(BscReportPropertyUtils.getObjectiveTitle()); titleCell1.setCellStyle(cellHeadStyle); titleCell2 = headRow.createCell(1); titleCell2.setCellValue(BscReportPropertyUtils.getKpiTitle()); titleCell2.setCellStyle(cellHeadStyle); titleCell3 = headRow.createCell(2); titleCell3.setCellValue("Maximum\nTarget\nMinimum"); titleCell3.setCellStyle(cellHeadStyle); titleCell4 = headRow.createCell(3); titleCell4.setCellValue("Weight"); titleCell4.setCellStyle(cellHeadStyle); titleCell5 = headRow.createCell(4); titleCell5.setCellValue("Formula"); titleCell5.setCellStyle(cellHeadStyle); titleCell6 = headRow.createCell(5); titleCell6.setCellValue("Score"); titleCell6.setCellStyle(cellHeadStyle); row++; headRow = sh.createRow(row); headRow.setHeight((short) 1000); titleCell1 = headRow.createCell(0); titleCell1.setCellValue("Objective of Strategy"); titleCell1.setCellStyle(cellHeadStyle); titleCell2 = headRow.createCell(1); titleCell2.setCellValue("KPI"); titleCell2.setCellStyle(cellHeadStyle); titleCell3 = headRow.createCell(2); titleCell3.setCellValue("Target"); titleCell3.setCellStyle(cellHeadStyle); titleCell4 = headRow.createCell(3); titleCell4.setCellValue("Weight"); titleCell4.setCellStyle(cellHeadStyle); titleCell5 = headRow.createCell(4); titleCell5.setCellValue("Formula"); titleCell5.setCellStyle(cellHeadStyle); XSSFCellStyle titleStyle = wb.createCellStyle(); titleStyle.setFillForegroundColor(bgColor); titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); titleStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#F5F4F4"))); titleStyle.setFont(cellHeadFont); titleStyle.setBorderBottom(BorderStyle.THIN); titleStyle.setBorderTop(BorderStyle.THIN); titleStyle.setBorderRight(BorderStyle.THIN); titleStyle.setBorderLeft(BorderStyle.THIN); titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); titleStyle.setAlignment(HorizontalAlignment.CENTER); titleStyle.setWrapText(true); titleCell6 = headRow.createCell(5); titleCell6.setCellValue(dateTypeName); titleCell6.setCellStyle(titleStyle); for (int i = 0; i < 5; i++) { sh.addMergedRegion(new CellRangeAddress(row - 1, row, i, i)); } return 5; }
From source file:com.netsteadfast.greenstep.bsc.command.PersonalReportExcelCommand.java
License:Apache License
private int createMainBody(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context) throws Exception { int mrRow = row; XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff"))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellFont = wb.createFont(); cellFont.setBold(false);//from www. j ava 2 s . com cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#000000"))); 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); cellStyle.setWrapText(true); for (PerspectiveVO perspective : vision.getPerspectives()) { for (ObjectiveVO objective : perspective.getObjectives()) { for (KpiVO kpi : objective.getKpis()) { int kCol = 0; Row contentRow = sh.createRow(row++); contentRow.setHeight((short) 1000); Cell contentCell1 = contentRow.createCell(kCol++); contentCell1.setCellValue(objective.getName()); contentCell1.setCellStyle(cellStyle); Cell contentCell2 = contentRow.createCell(kCol++); contentCell2.setCellValue(kpi.getName()); contentCell2.setCellStyle(cellStyle); Cell contentCell3 = contentRow.createCell(kCol++); contentCell3.setCellValue("max: " + kpi.getMax() + "\n" + "target: " + kpi.getTarget() + "\n" + "min: " + kpi.getMin() + "\n" + "unit: " + kpi.getUnit()); contentCell3.setCellStyle(cellStyle); Cell contentCell4 = contentRow.createCell(kCol++); contentCell4.setCellValue(kpi.getWeight() + "%"); contentCell4.setCellStyle(cellStyle); Cell contentCell5 = contentRow.createCell(kCol++); contentCell5.setCellValue(kpi.getFormula().getName()); contentCell5.setCellStyle(cellStyle); DateRangeScoreVO dateRangeScore = kpi.getDateRangeScores().get(0); // ? XSSFCellStyle cellStyleScore = wb.createCellStyle(); cellStyleScore.setFillForegroundColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(dateRangeScore.getBgColor()))); cellStyleScore.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellScoreFont = wb.createFont(); cellScoreFont.setBold(false); cellScoreFont.setColor( new XSSFColor(SimpleUtils.getColorRGB4POIColor(dateRangeScore.getFontColor()))); cellStyleScore.setFont(cellScoreFont); cellStyleScore.setWrapText(true); cellStyleScore.setVerticalAlignment(VerticalAlignment.CENTER); cellStyleScore.setBorderBottom(BorderStyle.THIN); cellStyleScore.setBorderTop(BorderStyle.THIN); cellStyleScore.setBorderRight(BorderStyle.THIN); cellStyleScore.setBorderLeft(BorderStyle.THIN); Cell contentCell6 = contentRow.createCell(kCol++); contentCell6.setCellValue(BscReportSupportUtils.parse2(dateRangeScore.getScore())); contentCell6.setCellStyle(cellStyleScore); } } } for (PerspectiveVO perspective : vision.getPerspectives()) { for (ObjectiveVO objective : perspective.getObjectives()) { int rowspan = objective.getRow(); if (objective.getRow() > 1) { // 2016-12-13 old work with POI 3.12 //sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow+rowspan-1, 0, 0)); // 2016-12-13 new work with POI 3.15 int mrRow1 = mrRow + rowspan - 1; if (mrRow1 > mrRow) { sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow1, 0, 0)); } } mrRow += rowspan; } } return row; }
From source file:com.netsteadfast.greenstep.bsc.command.PersonalReportExcelCommand.java
License:Apache License
private void createFoot(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context) throws Exception { Row footRow = sh.createRow(row);/*from w ww . j a v a 2 s .c o m*/ Row footRowB = sh.createRow(row + 1); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#FFFFFF"))); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); XSSFFont cellFont = wb.createFont(); cellFont.setBold(true); cellStyle.setFont(cellFont); cellStyle.setWrapText(true); Cell footCell1 = footRow.createCell(0); footCell1.setCellValue("assess:"); footCell1.setCellStyle(cellStyle); Cell footCell1B = footRowB.createCell(0); footCell1B.setCellValue("assess:"); footCell1B.setCellStyle(cellStyle); sh.addMergedRegion(new CellRangeAddress(row, row + 1, 0, 0)); Cell footCell2 = footRow.createCell(1); footCell2.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel()); footCell2.setCellStyle(cellStyle); Cell footCell3 = footRow.createCell(2); footCell3.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel()); footCell3.setCellStyle(cellStyle); Cell footCell4 = footRow.createCell(3); footCell4.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel()); footCell4.setCellStyle(cellStyle); Cell footCell2B = footRowB.createCell(1); footCell2B.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel()); footCell2B.setCellStyle(cellStyle); Cell footCell3B = footRowB.createCell(2); footCell3B.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel()); footCell3B.setCellStyle(cellStyle); Cell footCell4B = footRowB.createCell(3); footCell4B.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel()); footCell4B.setCellStyle(cellStyle); sh.addMergedRegion(new CellRangeAddress(row, row + 1, 1, 3)); Cell footCell5 = footRow.createCell(4); footCell5.setCellValue("Total"); footCell5.setCellStyle(cellStyle); float total = 0.0f; if (context.get("total") != null && context.get("total") instanceof Float) { total = (Float) context.get("total"); } Cell footCell6 = footRow.createCell(5); footCell6.setCellValue(BscReportSupportUtils.parse2(total)); footCell6.setCellStyle(cellStyle); Cell footCell5b = footRowB.createCell(4); footCell5b.setCellValue("Class"); footCell5b.setCellStyle(cellStyle); Cell footCell6b = footRowB.createCell(5); footCell6b.setCellValue(""); footCell6b.setCellStyle(cellStyle); }
From source file:com.netsteadfast.greenstep.bsc.command.PerspectivesDashboardExcelCommand.java
License:Apache License
@SuppressWarnings("unchecked") private int putCharts(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception { String pieBase64Content = SimpleUtils.getPNGBase64Content((String) context.get("pieCanvasToData")); String barBase64Content = SimpleUtils.getPNGBase64Content((String) context.get("barCanvasToData")); BufferedImage pieImage = SimpleUtils.decodeToImage(pieBase64Content); BufferedImage barImage = SimpleUtils.decodeToImage(barBase64Content); ByteArrayOutputStream pieBos = new ByteArrayOutputStream(); ImageIO.write(pieImage, "png", pieBos); pieBos.flush();/*from w w w .j av a 2 s.c om*/ ByteArrayOutputStream barBos = new ByteArrayOutputStream(); ImageIO.write(barImage, "png", barBos); barBos.flush(); SimpleUtils.setCellPicture(wb, sh, pieBos.toByteArray(), 0, 0); SimpleUtils.setCellPicture(wb, sh, barBos.toByteArray(), 0, 9); int row = 21; List<Map<String, Object>> chartDatas = (List<Map<String, Object>>) context.get("chartDatas"); String year = (String) context.get("year"); XSSFCellStyle cellHeadStyle = wb.createCellStyle(); cellHeadStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#f5f5f5"))); cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); XSSFFont cellHeadFont = wb.createFont(); cellHeadFont.setBold(true); //cellHeadFont.setColor( new XSSFColor( SimpleUtils.getColorRGB4POIColor( "#000000" ) ) ); cellHeadStyle.setFont(cellHeadFont); int titleRow = row - 1; int titleCellSize = 14; Row headRow = sh.createRow(titleRow); for (int i = 0; i < titleCellSize; i++) { Cell headCell = headRow.createCell(i); headCell.setCellStyle(cellHeadStyle); headCell.setCellValue("Perspectives metrics gauge ( " + year + " )"); } sh.addMergedRegion(new CellRangeAddress(titleRow, titleRow, 0, titleCellSize - 1)); int cellLeft = 10; 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("Target: " + String.valueOf(nodeData.get("target"))); nowRow = sh.createRow(row + 2); Cell cell3 = nowRow.createCell(cellLeft); cell3.setCellValue("Min: " + String.valueOf(nodeData.get("min"))); nowRow = sh.createRow(row + 3); Cell cell4 = nowRow.createCell(cellLeft); cell4.setCellValue("Score: " + String.valueOf(nodeData.get("score"))); row += rowSpace; } return row; }
From source file:com.netsteadfast.greenstep.bsc.command.TimeSeriesAnalysisExcelCommand.java
License:Apache License
private void putTables(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception { TsaVO tsa = (TsaVO) context.get("tsa"); @SuppressWarnings("unchecked") List<BbTsaMaCoefficients> coefficients = (List<BbTsaMaCoefficients>) context.get("coefficients"); @SuppressWarnings("unchecked") List<TimeSeriesAnalysisResult> tsaResults = (List<TimeSeriesAnalysisResult>) context.get("tsaResults"); XSSFFont cellHeadFont = wb.createFont(); cellHeadFont.setBold(true);// w w w . j a v a 2s.c om 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); cellHeadStyle.setFont(cellHeadFont); XSSFCellStyle cellHeadStyleBlank = wb.createCellStyle(); cellHeadStyleBlank.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff"))); cellHeadStyleBlank.setFont(cellHeadFont); XSSFCellStyle cellHeadStyle2 = wb.createCellStyle(); cellHeadStyle2.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff"))); cellHeadStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND); cellHeadStyle2.setBorderBottom(BorderStyle.THIN); cellHeadStyle2.setBorderTop(BorderStyle.THIN); cellHeadStyle2.setBorderRight(BorderStyle.THIN); cellHeadStyle2.setBorderLeft(BorderStyle.THIN); sh.setColumnWidth(0, 12000); int row = 0; // ============================================================== Row nowRow = sh.createRow(row); Cell cellTitle = nowRow.createCell(0); cellTitle.setCellStyle(cellHeadStyleBlank); cellTitle.setCellValue("Forecast analysis - " + context.get("visionName")); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell0a = nowRow.createCell(0); cell0a.setCellStyle(cellHeadStyleBlank); cell0a.setCellValue("Frequency: " + context.get("frequencyName") + " , Date range: " + context.get("date1") + " - " + context.get("date2") + " , " + "Measure data type for: " + context.get("dataFor") + " , " + context.get("organizationName") + context.get("employeeName")); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell0b = nowRow.createCell(0); cell0b.setCellStyle(cellHeadStyleBlank); cell0b.setCellValue(""); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell1 = nowRow.createCell(0); cell1.setCellStyle(cellHeadStyleBlank); cell1.setCellValue("Param infornation"); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell2_a = nowRow.createCell(0); cell2_a.setCellStyle(cellHeadStyle); cell2_a.setCellValue("Item"); Cell cell2_b = nowRow.createCell(1); cell2_b.setCellStyle(cellHeadStyle); cell2_b.setCellValue("Value"); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell3_a = nowRow.createCell(0); cell3_a.setCellStyle(cellHeadStyle2); cell3_a.setCellValue("Param name"); Cell cell3_b = nowRow.createCell(1); cell3_b.setCellStyle(cellHeadStyle2); cell3_b.setCellValue(tsa.getName()); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell4_a = nowRow.createCell(0); cell4_a.setCellStyle(cellHeadStyle2); cell4_a.setCellValue("Integration order"); Cell cell4_b = nowRow.createCell(1); cell4_b.setCellStyle(cellHeadStyle2); cell4_b.setCellValue(tsa.getIntegrationOrder()); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell5_a = nowRow.createCell(0); cell5_a.setCellStyle(cellHeadStyle2); cell5_a.setCellValue("Forecast next"); Cell cell5_b = nowRow.createCell(1); cell5_b.setCellStyle(cellHeadStyle2); cell5_b.setCellValue(tsa.getForecastNext()); row++; // ============================================================== nowRow = sh.createRow(row); Cell cell6_a = nowRow.createCell(0); cell6_a.setCellStyle(cellHeadStyle2); cell6_a.setCellValue("Description"); Cell cell6_b = nowRow.createCell(1); cell6_b.setCellStyle(cellHeadStyle2); cell6_b.setCellValue(StringUtils.defaultString(tsa.getDescription()).trim()); row++; // ============================================================== for (int i = 0; coefficients != null && i < coefficients.size(); i++) { BbTsaMaCoefficients coefficient = coefficients.get(i); nowRow = sh.createRow(row); Cell cell7x_a = nowRow.createCell(0); cell7x_a.setCellStyle(cellHeadStyle2); cell7x_a.setCellValue("Coefficient (" + (i + 1) + ")"); Cell cell7x_b = nowRow.createCell(1); cell7x_b.setCellStyle(cellHeadStyle2); cell7x_b.setCellValue(String.valueOf(coefficient.getSeqValue())); row++; } // ============================================================== nowRow = sh.createRow(row); Cell cellTitle3a = nowRow.createCell(0); cellTitle3a.setCellStyle(cellHeadStyleBlank); cellTitle3a.setCellValue(""); row++; // nowRow = sh.createRow(row); Cell cellTitle2a = nowRow.createCell(0); cellTitle2a.setCellStyle(cellHeadStyle); cellTitle2a.setCellValue("KPIs"); int j = 1; TimeSeriesAnalysisResult firstResult = tsaResults.get(0); for (int i = 0; i < firstResult.getKpi().getDateRangeScores().size(); i++) { DateRangeScoreVO dateRangeScore = firstResult.getKpi().getDateRangeScores().get(i); Cell cellTitle2a_dateRange = nowRow.createCell(j); j++; cellTitle2a_dateRange.setCellStyle(cellHeadStyle); cellTitle2a_dateRange.setCellValue(dateRangeScore.getDate()); } for (int i = 0; i < firstResult.getForecastNext().size(); i++) { Cell cellTitle2a_dateRange = nowRow.createCell(j); j++; cellTitle2a_dateRange.setCellStyle(cellHeadStyle); cellTitle2a_dateRange.setCellValue("next(" + (i + 1) + ")"); } row++; // Date Range score Forecast next score for (int i = 0; i < tsaResults.size(); i++) { nowRow = sh.createRow(row); j = 0; TimeSeriesAnalysisResult resultModel = tsaResults.get(i); Cell cell_kpi = nowRow.createCell(j); cell_kpi.setCellStyle(cellHeadStyle); cell_kpi.setCellValue(resultModel.getKpi().getName()); j++; for (int n = 0; n < resultModel.getKpi().getDateRangeScores().size(); n++) { DateRangeScoreVO dateRangeScore = resultModel.getKpi().getDateRangeScores().get(n); Cell cell_dateRangeScore = nowRow.createCell(j); cell_dateRangeScore.setCellStyle(cellHeadStyle2); cell_dateRangeScore.setCellValue(dateRangeScore.getScore()); j++; } for (int n = 0; n < resultModel.getForecastNext().size(); n++) { double forecastScore = resultModel.getForecastNext().get(n); Cell cell_forecastScore = nowRow.createCell(j); cell_forecastScore.setCellStyle(cellHeadStyle2); cell_forecastScore.setCellValue(forecastScore); j++; } row++; } }