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

Example 16 with XSSFCellStyle

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

the class OrganizationReportExcelCommand method createMainBody.

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

Example 17 with XSSFCellStyle

use of org.apache.poi.xssf.usermodel.XSSFCellStyle 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 18 with XSSFCellStyle

use of org.apache.poi.xssf.usermodel.XSSFCellStyle 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 19 with XSSFCellStyle

use of org.apache.poi.xssf.usermodel.XSSFCellStyle 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 20 with XSSFCellStyle

use of org.apache.poi.xssf.usermodel.XSSFCellStyle 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;
}