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

Example 16 with XSSFColor

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

the class KpiReportExcelCommand method createDateRange.

private int createDateRange(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context) throws Exception {
    String frequency = (String) context.get("frequency");
    String startYearDate = StringUtils.defaultString((String) context.get("startYearDate")).trim();
    String endYearDate = StringUtils.defaultString((String) context.get("endYearDate")).trim();
    String startDate = StringUtils.defaultString((String) context.get("startDate")).trim();
    String endDate = StringUtils.defaultString((String) context.get("endDate")).trim();
    String date1 = startDate;
    String date2 = endDate;
    if (BscMeasureDataFrequency.FREQUENCY_QUARTER.equals(frequency) || BscMeasureDataFrequency.FREQUENCY_HALF_OF_YEAR.equals(frequency) || BscMeasureDataFrequency.FREQUENCY_YEAR.equals(frequency)) {
        date1 = startYearDate + "/01/01";
        date2 = endYearDate + "/12/" + SimpleUtils.getMaxDayOfMonth(Integer.parseInt(endYearDate), 12);
    }
    Map<String, Object> headContentMap = new HashMap<String, Object>();
    this.fillHeadContent(context, headContentMap);
    XSSFCellStyle cellStyleLabel = wb.createCellStyle();
    cellStyleLabel.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getBackgroundColor())));
    cellStyleLabel.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    XSSFFont cellFontLabel = wb.createFont();
    cellFontLabel.setBold(false);
    cellFontLabel.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getFontColor())));
    cellStyleLabel.setFont(cellFontLabel);
    cellStyleLabel.setWrapText(true);
    //cellStyleLabel.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyleLabel.setBorderBottom(BorderStyle.THIN);
    cellStyleLabel.setBorderTop(BorderStyle.THIN);
    cellStyleLabel.setBorderRight(BorderStyle.THIN);
    cellStyleLabel.setBorderLeft(BorderStyle.THIN);
    int cols = 4 + vision.getPerspectives().get(0).getObjectives().get(0).getKpis().get(0).getDateRangeScores().size();
    int cell = 0;
    for (int i = 0; i < cols; i++) {
        String content = "Frequency: " + BscMeasureDataFrequency.getFrequencyMap(false).get(frequency) + " Date range: " + date1 + " ~ " + date2 + "\n" + StringUtils.defaultString((String) headContentMap.get("headContent"));
        Row headRow = sh.createRow(row);
        headRow.setHeight((short) 700);
        Cell headCell1 = headRow.createCell(cell);
        headCell1.setCellValue(content);
        headCell1.setCellStyle(cellStyleLabel);
    }
    sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1));
    row++;
    int kpiCols = 4;
    int kpiRows = 2;
    for (PerspectiveVO perspective : vision.getPerspectives()) {
        for (ObjectiveVO objective : perspective.getObjectives()) {
            for (KpiVO kpi : objective.getKpis()) {
                cell = 0;
                for (int r = 0; r < kpiRows; r++) {
                    Row contentRow = sh.createRow(row++);
                    contentRow.setHeight((short) 400);
                    for (int c = 0; c < kpiCols; c++) {
                        XSSFCellStyle cellStyle = wb.createCellStyle();
                        cellStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getBgColor())));
                        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        XSSFFont cellFont = wb.createFont();
                        cellFont.setBold(false);
                        cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getFontColor())));
                        cellStyle.setFont(cellFont);
                        cellStyle.setWrapText(true);
                        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                        cellStyle.setBorderBottom(BorderStyle.THIN);
                        cellStyle.setBorderTop(BorderStyle.THIN);
                        cellStyle.setBorderRight(BorderStyle.THIN);
                        cellStyle.setBorderLeft(BorderStyle.THIN);
                        Cell contentCell1 = contentRow.createCell(c);
                        contentCell1.setCellValue(kpi.getName());
                        contentCell1.setCellStyle(cellStyle);
                    }
                    cell = 4;
                    if (r == 0) {
                        for (int d = 0; d < kpi.getDateRangeScores().size(); d++) {
                            DateRangeScoreVO dateRangeScore = kpi.getDateRangeScores().get(d);
                            XSSFCellStyle cellStyle = wb.createCellStyle();
                            cellStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(dateRangeScore.getBgColor())));
                            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            XSSFFont cellFont = wb.createFont();
                            cellFont.setBold(false);
                            cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(dateRangeScore.getFontColor())));
                            cellStyle.setFont(cellFont);
                            cellStyle.setWrapText(true);
                            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                            cellStyle.setBorderBottom(BorderStyle.THIN);
                            cellStyle.setBorderTop(BorderStyle.THIN);
                            cellStyle.setBorderRight(BorderStyle.THIN);
                            cellStyle.setBorderLeft(BorderStyle.THIN);
                            Cell contentCell1 = contentRow.createCell(cell++);
                            contentCell1.setCellValue(dateRangeScore.getDate());
                            contentCell1.setCellStyle(cellStyle);
                        }
                    }
                    if (r == 1) {
                        for (int d = 0; d < kpi.getDateRangeScores().size(); d++) {
                            DateRangeScoreVO dateRangeScore = kpi.getDateRangeScores().get(d);
                            XSSFCellStyle cellStyle = wb.createCellStyle();
                            cellStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(dateRangeScore.getBgColor())));
                            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            XSSFFont cellFont = wb.createFont();
                            cellFont.setBold(false);
                            cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(dateRangeScore.getFontColor())));
                            cellStyle.setFont(cellFont);
                            cellStyle.setWrapText(true);
                            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                            cellStyle.setBorderBottom(BorderStyle.THIN);
                            cellStyle.setBorderTop(BorderStyle.THIN);
                            cellStyle.setBorderRight(BorderStyle.THIN);
                            cellStyle.setBorderLeft(BorderStyle.THIN);
                            Cell contentCell1 = contentRow.createCell(cell++);
                            contentCell1.setCellValue("      " + BscReportSupportUtils.parse2(dateRangeScore.getScore()));
                            contentCell1.setCellStyle(cellStyle);
                            byte[] imgBytes = BscReportSupportUtils.getByteIcon(kpi, dateRangeScore.getScore());
                            if (null != imgBytes) {
                                SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(), contentCell1.getColumnIndex());
                            }
                        }
                    }
                }
                sh.addMergedRegion(new CellRangeAddress(row - 2, row - 1, 0, kpiCols - 1));
            }
        }
    }
    return row++;
}

Example 17 with XSSFColor

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

the class KpiReportExcelCommand method createMainBody.

private int createMainBody(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision) throws Exception {
    Map<String, String> managementMap = BscKpiCode.getManagementMap(false);
    //Map<String, String> calculationMap = BscKpiCode.getCalculationMap(false);
    int itemCols = 4;
    int mrRow = row;
    for (int px = 0; px < vision.getPerspectives().size(); px++) {
        PerspectiveVO perspective = vision.getPerspectives().get(px);
        for (int ox = 0; ox < perspective.getObjectives().size(); ox++) {
            ObjectiveVO objective = perspective.getObjectives().get(ox);
            for (int kx = 0; kx < objective.getKpis().size(); kx++) {
                KpiVO kpi = objective.getKpis().get(kx);
                Row contentRow = sh.createRow(row++);
                contentRow.setHeight((short) 4000);
                int cell = 0;
                for (int i = 0; i < itemCols; i++) {
                    String content = this.getItemsContent(perspective.getName(), perspective.getScore(), perspective.getWeight(), perspective.getTarget(), perspective.getMin());
                    XSSFCellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(perspective.getBgColor())));
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    XSSFFont cellFont = wb.createFont();
                    cellFont.setBold(false);
                    cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(perspective.getFontColor())));
                    cellStyle.setFont(cellFont);
                    cellStyle.setWrapText(true);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    Cell contentCell1 = contentRow.createCell(cell++);
                    contentCell1.setCellValue("\n" + content);
                    contentCell1.setCellStyle(cellStyle);
                    if (i == 0 && ox == 0) {
                        byte[] imgBytes = BscReportSupportUtils.getByteIconBase("PERSPECTIVES", perspective.getTarget(), perspective.getMin(), perspective.getScore(), "", "", 0);
                        if (null != imgBytes) {
                            SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(), contentCell1.getColumnIndex());
                        }
                    }
                }
                for (int i = 0; i < itemCols; i++) {
                    String content = this.getItemsContent(objective.getName(), objective.getScore(), objective.getWeight(), objective.getTarget(), objective.getMin());
                    XSSFCellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(objective.getBgColor())));
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    XSSFFont cellFont = wb.createFont();
                    cellFont.setBold(false);
                    cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(objective.getFontColor())));
                    cellStyle.setFont(cellFont);
                    cellStyle.setWrapText(true);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    Cell contentCell1 = contentRow.createCell(cell++);
                    contentCell1.setCellValue("\n" + content);
                    contentCell1.setCellStyle(cellStyle);
                    if (i == 0 && kx == 0) {
                        byte[] imgBytes = BscReportSupportUtils.getByteIconBase("OBJECTIVES", objective.getTarget(), objective.getMin(), objective.getScore(), "", "", 0);
                        if (null != imgBytes) {
                            SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(), contentCell1.getColumnIndex());
                        }
                    }
                }
                for (int i = 0; i < itemCols; i++) {
                    //String content = this.getKpisContent(kpi, managementMap, calculationMap);
                    String content = this.getKpisContent(kpi, managementMap);
                    XSSFCellStyle cellStyle = wb.createCellStyle();
                    cellStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getBgColor())));
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    XSSFFont cellFont = wb.createFont();
                    cellFont.setBold(false);
                    cellFont.setColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor(kpi.getFontColor())));
                    cellStyle.setFont(cellFont);
                    cellStyle.setWrapText(true);
                    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    cellStyle.setBorderBottom(BorderStyle.THIN);
                    cellStyle.setBorderTop(BorderStyle.THIN);
                    cellStyle.setBorderRight(BorderStyle.THIN);
                    cellStyle.setBorderLeft(BorderStyle.THIN);
                    Cell contentCell1 = contentRow.createCell(cell++);
                    contentCell1.setCellValue("\n" + content);
                    contentCell1.setCellStyle(cellStyle);
                    if (i == 0) {
                        byte[] imgBytes = BscReportSupportUtils.getByteIconBase("KPI", kpi.getTarget(), kpi.getMin(), kpi.getScore(), kpi.getCompareType(), kpi.getManagement(), kpi.getQuasiRange());
                        if (null != imgBytes) {
                            SimpleUtils.setCellPicture(wb, sh, imgBytes, contentCell1.getRowIndex(), contentCell1.getColumnIndex());
                        }
                    }
                }
            }
        }
    }
    for (int px = 0; px < vision.getPerspectives().size(); px++) {
        PerspectiveVO perspective = vision.getPerspectives().get(px);
        sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow + perspective.getRow() - 1, 0, 3));
        for (int ox = 0; ox < perspective.getObjectives().size(); ox++) {
            ObjectiveVO objective = perspective.getObjectives().get(ox);
            sh.addMergedRegion(new CellRangeAddress(mrRow, mrRow + objective.getRow() - 1, 4, 7));
            for (int kx = 0; kx < objective.getKpis().size(); kx++) {
                sh.addMergedRegion(new CellRangeAddress(mrRow + kx, mrRow + kx, 8, 11));
            }
            mrRow += objective.getKpis().size();
        }
    }
    return row++;
}

Example 18 with XSSFColor

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

the class KpiPeriodTrendsExcelCommand method putTables.

@SuppressWarnings("unchecked")
private void putTables(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception {
    XSSFCellStyle cellHeadStyle = wb.createCellStyle();
    cellHeadStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#f5f5f5")));
    cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cellHeadStyle.setBorderBottom(BorderStyle.THIN);
    cellHeadStyle.setBorderTop(BorderStyle.THIN);
    cellHeadStyle.setBorderRight(BorderStyle.THIN);
    cellHeadStyle.setBorderLeft(BorderStyle.THIN);
    XSSFFont cellHeadFont = wb.createFont();
    cellHeadFont.setBold(true);
    cellHeadStyle.setFont(cellHeadFont);
    sh.setColumnWidth(0, 12000);
    int row = 0;
    Row nowRow = sh.createRow(row);
    Cell cell1 = nowRow.createCell(0);
    cell1.setCellStyle(cellHeadStyle);
    cell1.setCellValue("KPI");
    Cell cell2 = nowRow.createCell(1);
    cell2.setCellStyle(cellHeadStyle);
    cell2.setCellValue("Maximum");
    Cell cell3 = nowRow.createCell(2);
    cell3.setCellStyle(cellHeadStyle);
    cell3.setCellValue("Target");
    Cell cell4 = nowRow.createCell(3);
    cell4.setCellStyle(cellHeadStyle);
    cell4.setCellValue("Minimum");
    Cell cell5 = nowRow.createCell(4);
    cell5.setCellStyle(cellHeadStyle);
    cell5.setCellValue("Current score");
    Cell cell6 = nowRow.createCell(5);
    cell6.setCellStyle(cellHeadStyle);
    cell6.setCellValue("Previous score");
    Cell cell7 = nowRow.createCell(6);
    cell7.setCellStyle(cellHeadStyle);
    cell7.setCellValue("Change(%)");
    row++;
    List<PeriodTrendsData<KpiVO>> periodDatas = (List<PeriodTrendsData<KpiVO>>) context.get("periodDatas");
    for (PeriodTrendsData<KpiVO> periodData : periodDatas) {
        nowRow = sh.createRow(row);
        cell1 = nowRow.createCell(0);
        cell1.setCellValue(periodData.getCurrent().getName());
        cell2 = nowRow.createCell(1);
        cell2.setCellValue(periodData.getCurrent().getMax());
        cell3 = nowRow.createCell(2);
        cell3.setCellValue(periodData.getCurrent().getTarget());
        cell4 = nowRow.createCell(3);
        cell4.setCellValue(periodData.getCurrent().getMin());
        cell5 = nowRow.createCell(4);
        cell5.setCellValue(BscReportSupportUtils.parse2(periodData.getCurrent().getScore()));
        cell6 = nowRow.createCell(5);
        cell6.setCellValue(BscReportSupportUtils.parse2(periodData.getPrevious().getScore()));
        cell7 = nowRow.createCell(6);
        cell7.setCellValue(BscReportSupportUtils.parse2(periodData.getChange()));
        row++;
    }
    nowRow = sh.createRow(row);
    cell1 = nowRow.createCell(0);
    cell1.setCellValue("Current period: " + (String) context.get("currentPeriodDateRange") + " , Previous period: " + (String) context.get("previousPeriodDateRange"));
}

Example 19 with XSSFColor

use of org.apache.poi.xssf.usermodel.XSSFColor in project Gargoyle by callakrsos.

the class AbstractExcelModel method work.

/**
	 * 2014. 11. 4. KYJ
	 * 
	 * @param
	 * @return
	 * @throws Exception
	 * @처리내용 : 입력된 엑셀파일로부터 ExcelSVO객체를 생성하여 반환한다.
	 */
public ExcelSVO work() throws Exception {
    // 결과반환용 SVO
    ExcelSVO excelSVO = new ExcelSVO();
    // 컬럼부
    /* 시작 엑셀관련 메타정보 처리객체 */
    FormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
    DecimalFormat df = new DecimalFormat();
    /* 끝 엑셀관련 메타정보 처리객체 */
    for (int sheetIndex = 0; sheetIndex < excel.getNumberOfSheets(); sheetIndex++) {
        Sheet sheetAt = excel.getSheetAt(sheetIndex);
        String sheetName = sheetAt.getSheetName();
        List<ExcelColDVO> columnDVOList = new ArrayList<ExcelColDVO>();
        excelSVO.setColDvoList(sheetName, columnDVOList);
        int maxColumIndex = 0;
        // 시작 데이터부 처리
        List<ExcelDataDVO> arrayList = new ArrayList<ExcelDataDVO>();
        // 컬럼부에 정의되어야하는데 없음. 데이터부에는 존재할경우 컬럼부를 추가하기 위한 플래그
        boolean existsOutOfColumn = false;
        // while (rowIterator.hasNext())
        for (int row = 0; row < sheetAt.getLastRowNum(); row++) {
            Row next = sheetAt.getRow(row);
            if (next != null) {
                short lastCellNum = next.getLastCellNum();
                for (int col = 0; col < lastCellNum; col++) {
                    Cell cell = next.getCell(col);
                    if (cell != null) {
                        // 엑셀 셀
                        // Cell cell = cellIterator.next();
                        CellStyle cellStyle = cell.getCellStyle();
                        Color fillBackgroundColorColor = cellStyle.getFillBackgroundColorColor();
                        String backColorRgb = "";
                        if (fillBackgroundColorColor instanceof HSSFColor) {
                            HSSFColor backColor = (HSSFColor) fillBackgroundColorColor;
                            backColorRgb = backColor.getHexString();
                        } else if (fillBackgroundColorColor instanceof XSSFColor) {
                            XSSFColor backColor = (XSSFColor) fillBackgroundColorColor;
                            backColorRgb = backColor.getARGBHex();
                        }
                        int cellType = cell.getCellType();
                        String stringCellValue = "";
                        switch(cellType) {
                            case Cell.CELL_TYPE_FORMULA:
                                if (!(cell.toString() == "")) {
                                    if (evaluator.evaluateFormulaCell(cell) == 0) {
                                        double fddata = cell.getNumericCellValue();
                                        stringCellValue = String.valueOf(fddata);
                                    } else if (evaluator.evaluateFormulaCell(cell) == 1) {
                                        stringCellValue = cell.getStringCellValue();
                                    } else if (evaluator.evaluateFormulaCell(cell) == 4) {
                                        boolean fbdata = cell.getBooleanCellValue();
                                        stringCellValue = String.valueOf(fbdata);
                                    }
                                    break;
                                }
                                stringCellValue = cell.getCellFormula();
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                break;
                            case Cell.CELL_TYPE_BOOLEAN:
                                stringCellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case Cell.CELL_TYPE_ERROR:
                                /* N/A */
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                // double numericCellValue =
                                // cell.getNumericCellValue();
                                String dateFormatString = cellStyle.getDataFormatString();
                                short dataFormat = cellStyle.getDataFormat();
                                boolean internalDateFormat = HSSFDateUtil.isInternalDateFormat(dataFormat);
                                double numericCellValue = cell.getNumericCellValue();
                                boolean validExcelDate = HSSFDateUtil.isValidExcelDate(numericCellValue);
                                boolean cellDateFormatted = HSSFDateUtil.isCellDateFormatted(cell);
                                boolean cellInternalDateFormatted = HSSFDateUtil.isCellInternalDateFormatted(cell);
                                LOGGER.debug("sheet : [ " + sheetName + " ] dateFormatString : [ " + dateFormatString + " ] dataFormat : [ " + dataFormat + " ] isInternalDateFormat : [ " + internalDateFormat + " ] validExcelDate : [ " + validExcelDate + " ] cellDateFormatted : [ " + cellDateFormatted + " ]  cellInternalDateFormatted : [" + cellInternalDateFormatted + " ] numericCellValue : [ " + numericCellValue + " ] rowIndex : [ " + row + " ] columnIndex : [ " + col + " ]");
                                if (cellDateFormatted || cellInternalDateFormatted || (!"GENERAL".equals(dateFormatString.toUpperCase()))) {
                                    Date date = cell.getDateCellValue();
                                    LOGGER.debug("dateFmt : %s", dateFormatString);
                                    stringCellValue = new CellDateFormatter(dateFormatString).format(date);
                                } else {
                                    double ddata = cell.getNumericCellValue();
                                    stringCellValue = df.format(ddata);
                                }
                                break;
                            case Cell.CELL_TYPE_STRING:
                                stringCellValue = cell.getStringCellValue();
                                break;
                            default:
                                /* N/A */
                                break;
                        }
                        // 시작 컬럼부 처리
                        if (row == 0) {
                            short alignment = cellStyle.getAlignment();
                            int columnWidth = sheetAt.getColumnWidth(col);
                            columnDVOList.add(new ExcelColDVO(col, stringCellValue, columnWidth, alignment));
                        }
                        // 끝 컬럼부 처리
                        ExcelDataDVO excelDataDVO = new ExcelDataDVO(row, col, stringCellValue, backColorRgb);
                        arrayList.add(excelDataDVO);
                    } else {
                        ExcelDataDVO excelDataDVO = new ExcelDataDVO(row, col, "");
                        arrayList.add(excelDataDVO);
                    }
                }
            // end for
            } else {
                ExcelDataDVO excelDataDVO = new ExcelDataDVO(row, 0, "");
                arrayList.add(excelDataDVO);
            }
        }
        // end for
        // 끝 데이터부 처리
        excelSVO.addSheetExcelDVO(sheetName, arrayList);
    }
    return excelSVO;
}

Example 20 with XSSFColor

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

the class KpiReportExcelCommand method createHead.

private int createHead(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision) throws Exception {
    Row headRow = sh.createRow(row);
    headRow.setHeight((short) 700);
    int cell = 0;
    XSSFColor bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(vision.getBgColor()));
    XSSFColor fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(vision.getFontColor()));
    XSSFCellStyle cellHeadStyle = wb.createCellStyle();
    cellHeadStyle.setFillForegroundColor(bgColor);
    cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    XSSFFont cellHeadFont = wb.createFont();
    cellHeadFont.setBold(true);
    cellHeadFont.setColor(fnColor);
    cellHeadStyle.setFont(cellHeadFont);
    cellHeadStyle.setBorderBottom(BorderStyle.THIN);
    cellHeadStyle.setBorderTop(BorderStyle.THIN);
    cellHeadStyle.setBorderRight(BorderStyle.THIN);
    cellHeadStyle.setBorderLeft(BorderStyle.THIN);
    cellHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellHeadStyle.setAlignment(HorizontalAlignment.CENTER);
    cellHeadStyle.setWrapText(true);
    int cols = 12;
    for (int i = 0; i < cols; i++) {
        sh.setColumnWidth(i, 4000);
        Cell headCell1 = headRow.createCell(cell++);
        headCell1.setCellValue(vision.getTitle() + "\nscore: " + BscReportSupportUtils.parse2(vision.getScore()));
        headCell1.setCellStyle(cellHeadStyle);
    }
    sh.addMergedRegion(new CellRangeAddress(row, row, 0, cols - 1));
    // ------------------------------------------------------------------------
    bgColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getBackgroundColor()));
    fnColor = new XSSFColor(SimpleUtils.getColorRGB4POIColor(BscReportPropertyUtils.getFontColor()));
    cellHeadStyle = wb.createCellStyle();
    cellHeadStyle.setFillForegroundColor(bgColor);
    cellHeadStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cellHeadFont = wb.createFont();
    cellHeadFont.setBold(true);
    cellHeadFont.setColor(fnColor);
    cellHeadStyle.setFont(cellHeadFont);
    cellHeadStyle.setBorderBottom(BorderStyle.THIN);
    cellHeadStyle.setBorderTop(BorderStyle.THIN);
    cellHeadStyle.setBorderRight(BorderStyle.THIN);
    cellHeadStyle.setBorderLeft(BorderStyle.THIN);
    cellHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellHeadStyle.setAlignment(HorizontalAlignment.CENTER);
    cellHeadStyle.setWrapText(true);
    row++;
    headRow = sh.createRow(row);
    cell = 0;
    int titleCols = 4;
    for (int i = 0; i < titleCols; i++) {
        Cell headCell1 = headRow.createCell(cell++);
        headCell1.setCellValue(BscReportPropertyUtils.getPerspectiveTitle());
        headCell1.setCellStyle(cellHeadStyle);
    }
    for (int i = 0; i < titleCols; i++) {
        Cell headCell1 = headRow.createCell(cell++);
        headCell1.setCellValue(BscReportPropertyUtils.getObjectiveTitle());
        headCell1.setCellStyle(cellHeadStyle);
    }
    for (int i = 0; i < titleCols; i++) {
        Cell headCell1 = headRow.createCell(cell++);
        headCell1.setCellValue(BscReportPropertyUtils.getKpiTitle());
        headCell1.setCellStyle(cellHeadStyle);
    }
    sh.addMergedRegion(new CellRangeAddress(row, row, 0, 3));
    sh.addMergedRegion(new CellRangeAddress(row, row, 4, 7));
    sh.addMergedRegion(new CellRangeAddress(row, row, 8, 11));
    return 2;
}