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

Example 31 with XSSFCellStyle

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

the class ExcelComparator method isCellBorderMatches.

/**
     * Checks if cell border bottom matches.
     */
private void isCellBorderMatches(Locator loc1, Locator loc2, char borderSide) {
    if (!(loc1.cell instanceof XSSFCell))
        return;
    XSSFCellStyle style1 = ((XSSFCell) loc1.cell).getCellStyle();
    XSSFCellStyle style2 = ((XSSFCell) loc2.cell).getCellStyle();
    boolean b1, b2;
    String borderName;
    switch(borderSide) {
        case 't':
        default:
            b1 = style1.getBorderTopEnum() == BorderStyle.THIN;
            b2 = style2.getBorderTopEnum() == BorderStyle.THIN;
            borderName = "TOP";
            break;
        case 'b':
            b1 = style1.getBorderBottomEnum() == BorderStyle.THIN;
            b2 = style2.getBorderBottomEnum() == BorderStyle.THIN;
            borderName = "BOTTOM";
            break;
        case 'l':
            b1 = style1.getBorderLeftEnum() == BorderStyle.THIN;
            b2 = style2.getBorderLeftEnum() == BorderStyle.THIN;
            borderName = "LEFT";
            break;
        case 'r':
            b1 = style1.getBorderRightEnum() == BorderStyle.THIN;
            b2 = style2.getBorderRightEnum() == BorderStyle.THIN;
            borderName = "RIGHT";
            break;
    }
    if (b1 != b2) {
        addMessage(loc1, loc2, "Cell Border Attributes does not Match ::", (b1 ? "" : "NOT ") + borderName + " BORDER", (b2 ? "" : "NOT ") + borderName + " BORDER");
    }
}

Example 32 with XSSFCellStyle

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

Example 33 with XSSFCellStyle

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

the class ObjectivesDashboardExcelCommand method putCharts.

@SuppressWarnings("unchecked")
private int putCharts(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception {
    String barBase64Content = SimpleUtils.getPNGBase64Content((String) context.get("barChartsData"));
    BufferedImage barImage = SimpleUtils.decodeToImage(barBase64Content);
    ByteArrayOutputStream barBos = new ByteArrayOutputStream();
    ImageIO.write(barImage, "png", barBos);
    barBos.flush();
    SimpleUtils.setCellPicture(wb, sh, barBos.toByteArray(), 0, 0);
    int row = 28;
    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);
    cellHeadStyle.setFont(cellHeadFont);
    int titleCellSize = 14;
    Row headRow = sh.createRow(row);
    for (int i = 0; i < titleCellSize; i++) {
        Cell headCell = headRow.createCell(i);
        headCell.setCellStyle(cellHeadStyle);
        headCell.setCellValue("Objectives metrics gauge ( " + year + " )");
    }
    sh.addMergedRegion(new CellRangeAddress(row, row, 0, titleCellSize - 1));
    row = row + 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 34 with XSSFCellStyle

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

the class TimeSeriesAnalysisExcelCommand method putTables.

private void putTables(XSSFWorkbook wb, XSSFSheet sh, Context context) throws Exception {
    TsaVO tsa = (TsaVO) context.get("tsa");
    @SuppressWarnings("unchecked") List<BbTsaMaCoefficients> coefficients = (List<BbTsaMaCoefficients>) context.get("coefficients");
    @SuppressWarnings("unchecked") List<TimeSeriesAnalysisResult> tsaResults = (List<TimeSeriesAnalysisResult>) context.get("tsaResults");
    XSSFFont cellHeadFont = wb.createFont();
    cellHeadFont.setBold(true);
    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);
    cellHeadStyle.setFont(cellHeadFont);
    XSSFCellStyle cellHeadStyleBlank = wb.createCellStyle();
    cellHeadStyleBlank.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff")));
    cellHeadStyleBlank.setFont(cellHeadFont);
    XSSFCellStyle cellHeadStyle2 = wb.createCellStyle();
    cellHeadStyle2.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#ffffff")));
    cellHeadStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cellHeadStyle2.setBorderBottom(BorderStyle.THIN);
    cellHeadStyle2.setBorderTop(BorderStyle.THIN);
    cellHeadStyle2.setBorderRight(BorderStyle.THIN);
    cellHeadStyle2.setBorderLeft(BorderStyle.THIN);
    sh.setColumnWidth(0, 12000);
    int row = 0;
    // ==============================================================
    Row nowRow = sh.createRow(row);
    Cell cellTitle = nowRow.createCell(0);
    cellTitle.setCellStyle(cellHeadStyleBlank);
    cellTitle.setCellValue("Forecast analysis - " + context.get("visionName"));
    row++;
    // ==============================================================
    nowRow = sh.createRow(row);
    Cell cell0a = nowRow.createCell(0);
    cell0a.setCellStyle(cellHeadStyleBlank);
    cell0a.setCellValue("Frequency: " + context.get("frequencyName") + " , Date range: " + context.get("date1") + " - " + context.get("date2") + " , " + "Measure data type for: " + context.get("dataFor") + " , " + context.get("organizationName") + context.get("employeeName"));
    row++;
    // ==============================================================
    nowRow = sh.createRow(row);
    Cell cell0b = nowRow.createCell(0);
    cell0b.setCellStyle(cellHeadStyleBlank);
    cell0b.setCellValue("");
    row++;
    // ==============================================================
    nowRow = sh.createRow(row);
    Cell cell1 = nowRow.createCell(0);
    cell1.setCellStyle(cellHeadStyleBlank);
    cell1.setCellValue("Param infornation");
    row++;
    // ==============================================================
    nowRow = sh.createRow(row);
    Cell cell2_a = nowRow.createCell(0);
    cell2_a.setCellStyle(cellHeadStyle);
    cell2_a.setCellValue("Item");
    Cell cell2_b = nowRow.createCell(1);
    cell2_b.setCellStyle(cellHeadStyle);
    cell2_b.setCellValue("Value");
    row++;
    // ==============================================================	
    nowRow = sh.createRow(row);
    Cell cell3_a = nowRow.createCell(0);
    cell3_a.setCellStyle(cellHeadStyle2);
    cell3_a.setCellValue("Param name");
    Cell cell3_b = nowRow.createCell(1);
    cell3_b.setCellStyle(cellHeadStyle2);
    cell3_b.setCellValue(tsa.getName());
    row++;
    // ==============================================================	
    nowRow = sh.createRow(row);
    Cell cell4_a = nowRow.createCell(0);
    cell4_a.setCellStyle(cellHeadStyle2);
    cell4_a.setCellValue("Integration order");
    Cell cell4_b = nowRow.createCell(1);
    cell4_b.setCellStyle(cellHeadStyle2);
    cell4_b.setCellValue(tsa.getIntegrationOrder());
    row++;
    // ==============================================================	
    nowRow = sh.createRow(row);
    Cell cell5_a = nowRow.createCell(0);
    cell5_a.setCellStyle(cellHeadStyle2);
    cell5_a.setCellValue("Forecast next");
    Cell cell5_b = nowRow.createCell(1);
    cell5_b.setCellStyle(cellHeadStyle2);
    cell5_b.setCellValue(tsa.getForecastNext());
    row++;
    // ==============================================================	
    nowRow = sh.createRow(row);
    Cell cell6_a = nowRow.createCell(0);
    cell6_a.setCellStyle(cellHeadStyle2);
    cell6_a.setCellValue("Description");
    Cell cell6_b = nowRow.createCell(1);
    cell6_b.setCellStyle(cellHeadStyle2);
    cell6_b.setCellValue(StringUtils.defaultString(tsa.getDescription()).trim());
    row++;
    // ==============================================================
    for (int i = 0; coefficients != null && i < coefficients.size(); i++) {
        BbTsaMaCoefficients coefficient = coefficients.get(i);
        nowRow = sh.createRow(row);
        Cell cell7x_a = nowRow.createCell(0);
        cell7x_a.setCellStyle(cellHeadStyle2);
        cell7x_a.setCellValue("Coefficient (" + (i + 1) + ")");
        Cell cell7x_b = nowRow.createCell(1);
        cell7x_b.setCellStyle(cellHeadStyle2);
        cell7x_b.setCellValue(String.valueOf(coefficient.getSeqValue()));
        row++;
    }
    // ==============================================================
    nowRow = sh.createRow(row);
    Cell cellTitle3a = nowRow.createCell(0);
    cellTitle3a.setCellStyle(cellHeadStyleBlank);
    cellTitle3a.setCellValue("");
    row++;
    // 填寫標題
    nowRow = sh.createRow(row);
    Cell cellTitle2a = nowRow.createCell(0);
    cellTitle2a.setCellStyle(cellHeadStyle);
    cellTitle2a.setCellValue("KPIs");
    int j = 1;
    TimeSeriesAnalysisResult firstResult = tsaResults.get(0);
    for (int i = 0; i < firstResult.getKpi().getDateRangeScores().size(); i++) {
        DateRangeScoreVO dateRangeScore = firstResult.getKpi().getDateRangeScores().get(i);
        Cell cellTitle2a_dateRange = nowRow.createCell(j);
        j++;
        cellTitle2a_dateRange.setCellStyle(cellHeadStyle);
        cellTitle2a_dateRange.setCellValue(dateRangeScore.getDate());
    }
    for (int i = 0; i < firstResult.getForecastNext().size(); i++) {
        Cell cellTitle2a_dateRange = nowRow.createCell(j);
        j++;
        cellTitle2a_dateRange.setCellStyle(cellHeadStyle);
        cellTitle2a_dateRange.setCellValue("next(" + (i + 1) + ")");
    }
    row++;
    // 填寫 Date Range score 與 Forecast next score
    for (int i = 0; i < tsaResults.size(); i++) {
        nowRow = sh.createRow(row);
        j = 0;
        TimeSeriesAnalysisResult resultModel = tsaResults.get(i);
        Cell cell_kpi = nowRow.createCell(j);
        cell_kpi.setCellStyle(cellHeadStyle);
        cell_kpi.setCellValue(resultModel.getKpi().getName());
        j++;
        for (int n = 0; n < resultModel.getKpi().getDateRangeScores().size(); n++) {
            DateRangeScoreVO dateRangeScore = resultModel.getKpi().getDateRangeScores().get(n);
            Cell cell_dateRangeScore = nowRow.createCell(j);
            cell_dateRangeScore.setCellStyle(cellHeadStyle2);
            cell_dateRangeScore.setCellValue(dateRangeScore.getScore());
            j++;
        }
        for (int n = 0; n < resultModel.getForecastNext().size(); n++) {
            double forecastScore = resultModel.getForecastNext().get(n);
            Cell cell_forecastScore = nowRow.createCell(j);
            cell_forecastScore.setCellStyle(cellHeadStyle2);
            cell_forecastScore.setCellValue(forecastScore);
            j++;
        }
        row++;
    }
}

Example 35 with XSSFCellStyle

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

the class PersonalReportExcelCommand method createFoot.

private void createFoot(XSSFWorkbook wb, XSSFSheet sh, int row, VisionVO vision, Context context) throws Exception {
    Row footRow = sh.createRow(row);
    Row footRowB = sh.createRow(row + 1);
    XSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(new XSSFColor(SimpleUtils.getColorRGB4POIColor("#FFFFFF")));
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    XSSFFont cellFont = wb.createFont();
    cellFont.setBold(true);
    cellStyle.setFont(cellFont);
    cellStyle.setWrapText(true);
    Cell footCell1 = footRow.createCell(0);
    footCell1.setCellValue("assess:");
    footCell1.setCellStyle(cellStyle);
    Cell footCell1B = footRowB.createCell(0);
    footCell1B.setCellValue("assess:");
    footCell1B.setCellStyle(cellStyle);
    sh.addMergedRegion(new CellRangeAddress(row, row + 1, 0, 0));
    Cell footCell2 = footRow.createCell(1);
    footCell2.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel());
    footCell2.setCellStyle(cellStyle);
    Cell footCell3 = footRow.createCell(2);
    footCell3.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel());
    footCell3.setCellStyle(cellStyle);
    Cell footCell4 = footRow.createCell(3);
    footCell4.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel());
    footCell4.setCellStyle(cellStyle);
    Cell footCell2B = footRowB.createCell(1);
    footCell2B.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel());
    footCell2B.setCellStyle(cellStyle);
    Cell footCell3B = footRowB.createCell(2);
    footCell3B.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel());
    footCell3B.setCellStyle(cellStyle);
    Cell footCell4B = footRowB.createCell(3);
    footCell4B.setCellValue(BscReportPropertyUtils.getPersonalReportClassLevel());
    footCell4B.setCellStyle(cellStyle);
    sh.addMergedRegion(new CellRangeAddress(row, row + 1, 1, 3));
    Cell footCell5 = footRow.createCell(4);
    footCell5.setCellValue("Total");
    footCell5.setCellStyle(cellStyle);
    float total = 0.0f;
    if (context.get("total") != null && context.get("total") instanceof Float) {
        total = (Float) context.get("total");
    }
    Cell footCell6 = footRow.createCell(5);
    footCell6.setCellValue(BscReportSupportUtils.parse2(total));
    footCell6.setCellStyle(cellStyle);
    Cell footCell5b = footRowB.createCell(4);
    footCell5b.setCellValue("Class");
    footCell5b.setCellStyle(cellStyle);
    Cell footCell6b = footRowB.createCell(5);
    footCell6b.setCellValue("");
    footCell6b.setCellStyle(cellStyle);
}