Examples with HSSFPalette org.apache.poi.hssf.usermodel.HSSFPalette used on opensource projects

Example 1 with HSSFPalette

use of org.apache.poi.hssf.usermodel.HSSFPalette in project cerberus-source by cerberustesting.

the class ExportServiceFactory method createReportByTagExport.

private void createReportByTagExport(Workbook workbook) {
    // handles the export of the execution by tag data
    HashMap<String, SummaryStatisticsDTO> summaryMap = new HashMap<String, SummaryStatisticsDTO>();
    HashMap<String, HashMap<String, List<TestCaseExecution>>> mapList = new HashMap<String, HashMap<String, List<TestCaseExecution>>>();
    List<String> mapCountries = new ArrayList<String>();
    List<CellStyle> stylesList = new LinkedList<CellStyle>();
    if (exportOptions.contains("chart") || exportOptions.contains("list")) {
        // then we need to create the default colors for each cell
        try (HSSFWorkbook hwb = new HSSFWorkbook()) {
            HSSFPalette palette = hwb.getCustomPalette();
            CellStyle okStyle = workbook.createCellStyle();
            // get the color which most closely matches the color you want to use
            // code to get the style for the cell goes here
            okStyle.setFillForegroundColor(palette.findSimilarColor(92, 184, 0).getIndex());
            okStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
            // okStyle.setFont();
            stylesList.add(okStyle);
        } catch (IOException e) {
            LOG.warn(e.toString());
        }
    }
    for (TestCaseExecution execution : (List<TestCaseExecution>) list) {
        if (exportOptions.contains("chart") || exportOptions.contains("summary")) {
            String keySummaryTable = execution.getApplication() + " " + execution.getCountry() + " " + execution.getEnvironment();
            SummaryStatisticsDTO stats;
            String status = execution.getControlStatus();
            if (summaryMap.containsKey(keySummaryTable)) {
                stats = summaryMap.get(keySummaryTable);
            } else {
                stats = new SummaryStatisticsDTO();
                stats.setApplication(execution.getApplication());
                stats.setCountry(execution.getCountry());
                stats.setEnvironment(execution.getEnvironment());
            }
            stats.updateStatisticByStatus(status);
            // updates the map
            summaryMap.put(keySummaryTable, stats);
        }
        if (exportOptions.contains("list")) {
            if (exportOptions.contains("filter")) {
            // filter active
            } else {
            // all data is saved
            }
            HashMap<String, List<TestCaseExecution>> listExecution;
            List<TestCaseExecution> testCaseList;
            String testKey = execution.getTest();
            String testCaseKey = execution.getTestCase();
            if (mapList.containsKey(testKey)) {
                listExecution = mapList.get(testKey);
            } else {
                listExecution = new HashMap<String, List<TestCaseExecution>>();
            }
            if (listExecution.containsKey(testCaseKey)) {
                testCaseList = listExecution.get(testCaseKey);
            } else {
                testCaseList = new ArrayList<TestCaseExecution>();
            }
            testCaseList.add(execution);
            listExecution.put(testCaseKey, testCaseList);
            mapList.put(testKey, listExecution);
            if (mapCountries.indexOf(execution.getCountry()) == -1) {
                mapCountries.add(execution.getCountry());
            }
        }
    }
    int rowCount = -1;
    // Create a blank sheet
    Sheet sheet = workbook.createSheet("Report by Tag");
    sheet.getPrintSetup().setLandscape(true);
    PrintSetup ps = sheet.getPrintSetup();
    sheet.setAutobreaks(true);
    // ps.setFitHeight((short) 1);
    ps.setFitWidth((short) 1);
    sheet.setFitToPage(true);
    sheet.setColumnWidth(0, 9000);
    if (exportOptions.contains("chart")) {
        SummaryStatisticsDTO sumsTotal = calculateTotalValues(summaryMap);
        Row row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Report By Status");
        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Status");
        row.createCell(1).setCellValue("Total");
        row.createCell(2).setCellValue("Percentage");
        row = sheet.createRow(++rowCount);
        CellStyle okStyle = stylesList.get(0);
        Cell cellOk = row.createCell(0);
        cellOk.setCellValue("OK");
        cellOk.setCellStyle(okStyle);
        row.createCell(1).setCellValue(sumsTotal.getOK());
        row.createCell(2).setCellValue(sumsTotal.getPercOK());
        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("KO");
        row.createCell(1).setCellValue(sumsTotal.getKO());
        row.createCell(2).setCellValue(sumsTotal.getPercKO());
        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("FA");
        row.createCell(1).setCellValue(sumsTotal.getFA());
        row.createCell(2).setCellValue(sumsTotal.getPercFA());
        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("NA");
        row.createCell(1).setCellValue(sumsTotal.getNA());
        row.createCell(2).setCellValue(sumsTotal.getPercNA());
        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("NE");
        row.createCell(1).setCellValue(sumsTotal.getNE());
        row.createCell(2).setCellValue(sumsTotal.getPercNE());
        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("QU");
        row.createCell(1).setCellValue(sumsTotal.getQU());
        row.createCell(2).setCellValue(sumsTotal.getPercQU());
        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("PE");
        row.createCell(1).setCellValue(sumsTotal.getPE());
        row.createCell(2).setCellValue(sumsTotal.getPercPE());
        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("CA");
        row.createCell(1).setCellValue(sumsTotal.getCA());
        row.createCell(2).setCellValue(sumsTotal.getPercCA());
        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Total");
        row.createCell(1).setCellValue(sumsTotal.getTotal());
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
        sheet.createRow(++rowCount).createCell(0).setCellValue("");
    }
    if (exportOptions.contains("summary")) {
        // draw the table with data
        Row row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Summary Table");
        // start creating data
        row = sheet.createRow(++rowCount);
        row.createCell(0).setCellValue("Application");
        row.createCell(1).setCellValue("Country");
        row.createCell(2).setCellValue("Environment");
        row.createCell(3).setCellValue("OK");
        row.createCell(4).setCellValue("KO");
        row.createCell(5).setCellValue("FA");
        row.createCell(6).setCellValue("NA");
        row.createCell(7).setCellValue("NE");
        row.createCell(8).setCellValue("PE");
        row.createCell(8).setCellValue("QU");
        row.createCell(9).setCellValue("CA");
        row.createCell(10).setCellValue("NOT OK");
        row.createCell(11).setCellValue("Total");
        /*temporary styles*/
        CellStyle styleBlue = workbook.createCellStyle();
        CellStyle styleGreen = workbook.createCellStyle();
        try (HSSFWorkbook hwb = new HSSFWorkbook()) {
            HSSFPalette palette = hwb.getCustomPalette();
            // get the color which most closely matches the color you want to use
            HSSFColor myColor = palette.findSimilarColor(66, 139, 202);
            // get the palette index of that color
            short palIndex = myColor.getIndex();
            // code to get the style for the cell goes here
            styleBlue.setFillForegroundColor(palIndex);
            styleBlue.setFillPattern(CellStyle.SPARSE_DOTS);
            HSSFColor myColorGreen = palette.findSimilarColor(92, 184, 0);
            styleGreen.setFillForegroundColor(myColorGreen.getIndex());
            styleGreen.setFillPattern(CellStyle.SPARSE_DOTS);
            int startRow = (rowCount + 2);
            TreeMap<String, SummaryStatisticsDTO> sortedSummaryMap = new TreeMap<String, SummaryStatisticsDTO>(summaryMap);
            for (String key : sortedSummaryMap.keySet()) {
                row = sheet.createRow(++rowCount);
                SummaryStatisticsDTO sumStats = summaryMap.get(key);
                // application
                row.createCell(0).setCellValue((String) sumStats.getApplication());
                // country
                row.createCell(1).setCellValue((String) sumStats.getCountry());
                // environment
                row.createCell(2).setCellValue((String) sumStats.getEnvironment());
                // OK
                row.createCell(3).setCellValue(sumStats.getOK());
                // KO
                row.createCell(4).setCellValue(sumStats.getKO());
                // FA
                row.createCell(5).setCellValue(sumStats.getFA());
                // NA
                row.createCell(6).setCellValue(sumStats.getNA());
                // NE
                row.createCell(7).setCellValue(sumStats.getNE());
                // PE
                row.createCell(8).setCellValue(sumStats.getPE());
                // QU
                row.createCell(9).setCellValue(sumStats.getQU());
                // CA
                row.createCell(10).setCellValue(sumStats.getCA());
                int rowNumber = row.getRowNum() + 1;
                // NOT OK
                // row.createCell(11).setCellValue(sumStats.getNotOkTotal());
                row.createCell(11).setCellFormula("SUM(E" + rowNumber + ":J" + rowNumber + ")");
                // Total
                row.createCell(12).setCellFormula("SUM(D" + rowNumber + ",K" + rowNumber + ")");
                if (sumStats.getOK() == sumStats.getTotal()) {
                    for (int i = 0; i < 13; i++) {
                        row.getCell(i).setCellStyle(styleGreen);
                    }
                }
            }
            // TODO:FN percentages missing
            // Total row
            row = sheet.createRow(++rowCount);
            row.createCell(0).setCellValue("Total");
            row.createCell(1).setCellValue("");
            row.createCell(2).setCellValue("");
            // OK
            row.createCell(3).setCellFormula("SUM(D" + startRow + ":D" + rowCount + ")");
            // KO
            row.createCell(4).setCellFormula("SUM(E" + startRow + ":E" + rowCount + ")");
            // FA
            row.createCell(5).setCellFormula("SUM(F" + startRow + ":F" + rowCount + ")");
            // NA
            row.createCell(6).setCellFormula("SUM(G" + startRow + ":G" + rowCount + ")");
            // NE
            row.createCell(7).setCellFormula("SUM(H" + startRow + ":H" + rowCount + ")");
            // PE
            row.createCell(8).setCellFormula("SUM(I" + startRow + ":I" + rowCount + ")");
            // QU
            row.createCell(9).setCellFormula("SUM(J" + startRow + ":I" + rowCount + ")");
            // CA
            row.createCell(10).setCellFormula("SUM(K" + startRow + ":J" + rowCount + ")");
            int rowNumberTotal = row.getRowNum() + 1;
            // NOT OK
            row.createCell(11).setCellFormula("SUM(E" + rowNumberTotal + ":J" + rowNumberTotal + ")");
            // Total
            row.createCell(12).setCellFormula("SUM(D" + rowNumberTotal + ",K" + rowNumberTotal + ")");
            for (int i = 0; i < 13; i++) {
                row.getCell(i).setCellStyle(styleBlue);
            }
            // add some empty rows
            sheet.createRow(++rowCount).createCell(0).setCellValue("");
            sheet.createRow(++rowCount).createCell(0).setCellValue("");
            sheet.createRow(++rowCount).createCell(0).setCellValue("");
            sheet.createRow(++rowCount).createCell(0).setCellValue("");
        } catch (IOException e) {
            LOG.warn(e.toString());
        }
    }
    if (exportOptions.contains("list")) {
        // exports the data from test cases' executions
        Row r = sheet.createRow(++rowCount);
        r.createCell(0).setCellValue("Test");
        r.createCell(1).setCellValue("Test Case");
        r.createCell(2).setCellValue("Description");
        r.createCell(3).setCellValue("Application");
        r.createCell(4).setCellValue("Environment");
        r.createCell(5).setCellValue("Browser");
        // creates the country list
        // sorts the list of countries
        Collections.sort(mapCountries);
        int startIndexForCountries = 6;
        for (String country : mapCountries) {
            r.createCell(startIndexForCountries).setCellValue(country);
            startIndexForCountries++;
        }
        TreeMap<String, HashMap<String, List<TestCaseExecution>>> sortedKeys = new TreeMap<String, HashMap<String, List<TestCaseExecution>>>(mapList);
        rowCount++;
        for (String keyMapList : sortedKeys.keySet()) {
            rowCount = createRow(keyMapList, mapList.get(keyMapList), sheet, rowCount, mapCountries);
        }
    }
}

Example 2 with HSSFPalette

use of org.apache.poi.hssf.usermodel.HSSFPalette in project nebula.widgets.nattable by eclipse.

the class HSSFExcelExporter method getColorIndex.

/**
 * Note: The Excel HSSF format only supports a maximum of 56 custom colors.
 * If you have more than that number of colors, bad things will happen when
 * you try to export.
 */
private short getColorIndex(Color swtColor) {
    if (!this.colorIndex.contains(swtColor)) {
        this.colorIndex.add(swtColor);
        HSSFPalette palette = ((HSSFWorkbook) this.xlWorkbook).getCustomPalette();
        palette.setColorAtIndex((short) (55 - this.colorIndex.indexOf(swtColor)), (byte) swtColor.getRed(), (byte) swtColor.getGreen(), (byte) swtColor.getBlue());
    }
    return (short) (55 - this.colorIndex.indexOf(swtColor));
}