Examples with CellRangeAddress org.apache.poi.ss.util.CellRangeAddress used on opensource projects

Example 96 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project Gargoyle by callakrsos.

the class FxExcelUtil method drawHeader.

/**
	 * 헤더부분을 실제 엑셀에 처리하기 위한 코드.
	 * @작성자 : KYJ
	 * @작성일 : 2016. 9. 7. 
	 * @param sheetConverter
	 * @param createSheet
	 * @param columnExpr
	 * @param startRow
	 * @throws Exception
	 */
private static void drawHeader(IExcelDataSetHandler<Sheet, LinkedHashMap<ExcelColumnExpression, List<Object>>> sheetConverter, Sheet createSheet, ExcelColumnExpression columnExpr, final int maxLevel) throws Exception {
    Cell createCell = sheetConverter.createCell(createSheet, columnExpr.getDisplayText(), (/*startRow +*/
    columnExpr.getLevel()), START_COLUMN_INDEX + columnExpr.getIndex());
    List<ExcelColumnExpression> childrens = columnExpr.getChildrens();
    if (childrens != null && !childrens.isEmpty()) {
        int horizontalSize = childrens.size();
        for (ExcelColumnExpression subColumnExpr : childrens) {
            if (subColumnExpr.isVisible()) {
                drawHeader(sheetConverter, createSheet, subColumnExpr, maxLevel);
            }
        }
        /* 셀병합. Horizontal Merge  부모 레벨.*/
        {
            int level = columnExpr.getParent() == null ? HEADER_ROW_INDEX : columnExpr.getParent().getLevel();
            int index = columnExpr.getIndex() + START_COLUMN_INDEX;
            CellRangeAddress cellMerge = cellMerge(createSheet, level, level, index, (index + horizontalSize - 1));
            border(cellMerge, createSheet);
            headerStyle(createCell);
        }
    } else {
        /*셀병합. Vertical Merge*/
        //case1 자식레벨은 없고 , 본인 레벨이 max 레벨이 낮은경우
        int level = columnExpr.getLevel();
        int index = columnExpr.getIndex() + START_COLUMN_INDEX;
        if (level < maxLevel) {
            CellRangeAddress cellMerge = cellMerge(createSheet, level, maxLevel, index, index);
            //				style(createCell, DEFAULT_HEADER_STYLE);
            border(cellMerge, createSheet);
        }
        /* 현재 레벨.*/
        {
            headerStyle(createCell, style -> {
                style.setBorderTop(CellStyle.BORDER_THIN);
                style.setBorderLeft(CellStyle.BORDER_THIN);
                style.setBorderRight(CellStyle.BORDER_THIN);
                style.setBorderBottom(CellStyle.BORDER_THIN);
            });
            //컬럼 width 지정.
            width(createSheet, columnExpr, index);
        }
    }
}

Example 97 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project ocvn by devgateway.

the class ExcelChartSheetDefault method getChartDataSource.

private ChartDataSource getChartDataSource(final int row) {
    final int lastCellNum = excelSheet.getRow(row).getLastCellNum() - 1;
    final CellRangeAddress cellRangeAddress = new CellRangeAddress(row, row, 0, lastCellNum);
    final ChartDataSource chartDataSource;
    if (row == 0) {
        chartDataSource = DataSources.fromStringCellRange(excelSheet, cellRangeAddress);
    } else {
        chartDataSource = DataSources.fromNumericCellRange(excelSheet, cellRangeAddress);
    }
    return chartDataSource;
}

Example 98 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project bamboobsc by billchen198318.

the class PerspectivesDashboardExcelCommand method putCharts.

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

Example 99 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress 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 100 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress 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;
}