Examples with XSSFFont org.apache.poi.xssf.usermodel.XSSFFont used on opensource projects

Example 11 with XSSFFont

use of org.apache.poi.xssf.usermodel.XSSFFont in project bamboobsc by billchen198318.

the class OrganizationReportExcelCommand method createHead.

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";
    }
    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;
}

Example 12 with XSSFFont

use of org.apache.poi.xssf.usermodel.XSSFFont in project bamboobsc by billchen198318.

the class KpisDashboardExcelCommand method putTables.

@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);
    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;
}

Example 13 with XSSFFont

use of org.apache.poi.xssf.usermodel.XSSFFont in project bamboobsc by billchen198318.

the class PersonalReportExcelCommand method createMainBody.

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);
    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;
}

Example 14 with XSSFFont

use of org.apache.poi.xssf.usermodel.XSSFFont in project bamboobsc by billchen198318.

the class PersonalReportExcelCommand method createHead.

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";
    }
    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;
}

Example 15 with XSSFFont

use of org.apache.poi.xssf.usermodel.XSSFFont in project bamboobsc by billchen198318.

the class KpisDashboardExcelCommand method putCharts.

@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();
    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;
}