Example usage for org.apache.poi.ss.usermodel Row createCell

List of usage examples for org.apache.poi.ss.usermodel Row createCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. createCell

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Row createCell.

Prototype

Cell createCell(int column);

Source Link

Document

Use this to create new cells within the row and return it.

Usage

From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java

License:Apache License

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(//from w  w w . j  a v a 2  s . c o m
                            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++;
}

From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java

License:Apache License

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;/*  w  w w. j  av  a2s .com*/
    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) { // date

                        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) { // score

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

From source file:com.netsteadfast.greenstep.bsc.command.KpisDashboardExcelCommand.java

License:Apache License

@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);/*from   w w w  .j a  v  a2s  .c o  m*/
    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;
}