Example usage for org.apache.poi.ss.usermodel CellStyle setFillPattern

List of usage examples for org.apache.poi.ss.usermodel CellStyle setFillPattern

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CellStyle
  6. setFillPattern

Introduction

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

Prototype

void setFillPattern(FillPatternType fp);

Source Link

Document

setting to one fills the cell with the foreground color...

Usage

From source file:nz.ac.auckland.abi.formatting.poi.ModelJSONToExcel.java

License:LGPL

/**
 * Create a library of cell styles//ww w. ja  va2s. c o m
 */
private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 11);
    monthFont.setColor(IndexedColors.WHITE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(monthFont);
    style.setWrapText(true);
    styles.put("header", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula_2", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.RED.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("MAX", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("MIN", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREEN.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("AVERAGE", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("STDEV", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("AVGSERIES", style);

    return styles;
}

From source file:org.alanwilliamson.openbd.plugin.spreadsheet.SpreadSheetFormatOptions.java

License:Open Source License

public static CellStyle createCellStyle(Workbook workbook, cfStructData _struct) throws Exception {
    CellStyle style = workbook.createCellStyle();

    if (_struct.containsKey("alignment")) {
        String v = _struct.getData("alignment").getString();
        Short s = lookup_alignment.get(v);
        if (s == null) {
            throw new Exception("invalid parameter for 'alignment' (" + v + ")");
        } else/*  www.jav a2s . co  m*/
            style.setAlignment(s);
    }

    if (_struct.containsKey("bottomborder")) {
        String v = _struct.getData("bottomborder").getString();
        Short s = lookup_border.get(v);
        if (s == null) {
            throw new Exception("invalid parameter for 'bottomborder' (" + v + ")");
        } else
            style.setBorderBottom(s);
    }

    if (_struct.containsKey("topborder")) {
        String v = _struct.getData("topborder").getString();
        Short s = lookup_border.get(v);
        if (s == null) {
            throw new Exception("invalid parameter for 'topborder' (" + v + ")");
        } else
            style.setBorderTop(s);
    }

    if (_struct.containsKey("leftborder")) {
        String v = _struct.getData("leftborder").getString();
        Short s = lookup_border.get(v);
        if (s == null) {
            throw new Exception("invalid parameter for 'leftborder' (" + v + ")");
        } else
            style.setBorderLeft(s);
    }

    if (_struct.containsKey("rightborder")) {
        String v = _struct.getData("rightborder").getString();
        Short s = lookup_border.get(v);
        if (s == null) {
            throw new Exception("invalid parameter for 'rightborder' (" + v + ")");
        } else
            style.setBorderRight(s);
    }

    if (_struct.containsKey("bottombordercolor")) {
        String v = _struct.getData("bottombordercolor").getString();
        Short s = lookup_colors.get(v);
        if (s == null) {
            throw new Exception("invalid parameter for 'bottombordercolor' (" + v + ")");
        } else
            style.setBottomBorderColor(s);
    }

    if (_struct.containsKey("topbordercolor")) {
        String v = _struct.getData("topbordercolor").getString();
        Short s = lookup_colors.get(v);
        if (s == null) {
            throw new Exception("invalid parameter for 'topbordercolor' (" + v + ")");
        } else
            style.setTopBorderColor(s);
    }

    if (_struct.containsKey("leftbordercolor")) {
        String v = _struct.getData("leftbordercolor").getString();
        Short s = lookup_colors.get(v);
        if (s == null) {
            throw new Exception("invalid parameter for 'leftbordercolor' (" + v + ")");
        } else
            style.setLeftBorderColor(s);
    }

    if (_struct.containsKey("rightbordercolor")) {
        String v = _struct.getData("rightbordercolor").getString();
        Short s = lookup_colors.get(v);
        if (s == null) {
            throw new Exception("invalid parameter for 'rightbordercolor' (" + v + ")");
        } else
            style.setRightBorderColor(s);
    }

    if (_struct.containsKey("fillpattern")) {
        String v = _struct.getData("fillpattern").getString();
        Short s = lookup_fillpatten.get(v);
        if (s == null) {
            throw new Exception("invalid parameter for 'fillpattern' (" + v + ")");
        } else
            style.setFillPattern(s);
    }

    if (_struct.containsKey("fgcolor")) {
        String v = _struct.getData("fgcolor").getString();
        Short s = lookup_colors.get(v);
        if (s == null) {
            throw new Exception("invalid parameter for 'fgcolor' (" + v + ")");
        } else
            style.setFillForegroundColor(s);
    }

    if (_struct.containsKey("bgcolor")) {
        String v = _struct.getData("bgcolor").getString();
        Short s = lookup_colors.get(v);
        if (s == null) {
            throw new Exception("invalid parameter for 'bgcolor' (" + v + ")");
        } else
            style.setFillBackgroundColor(s);
    }

    if (_struct.containsKey("textwrap")) {
        Boolean b = _struct.getData("textwrap").getBoolean();
        style.setWrapText(b);
    }

    if (_struct.containsKey("hidden")) {
        Boolean b = _struct.getData("hidden").getBoolean();
        style.setHidden(b);
    }

    if (_struct.containsKey("locked")) {
        Boolean b = _struct.getData("locked").getBoolean();
        style.setLocked(b);
    }

    if (_struct.containsKey("indent")) {
        style.setIndention((short) _struct.getData("indent").getInt());
    }

    if (_struct.containsKey("rotation")) {
        style.setRotation((short) _struct.getData("rotation").getInt());
    }

    if (_struct.containsKey("dateformat")) {
        style.setDataFormat(workbook.createDataFormat().getFormat(_struct.getData("dateformat").getString()));
    }

    // Manage the fonts
    Font f = workbook.createFont();

    if (_struct.containsKey("strikeout")) {
        f.setStrikeout(true);
    }

    if (_struct.containsKey("bold")) {
        Boolean b = _struct.getData("bold").getBoolean();
        f.setBoldweight(b ? Font.BOLDWEIGHT_BOLD : Font.BOLDWEIGHT_NORMAL);
    }

    if (_struct.containsKey("underline")) {
        String v = _struct.getData("underline").getString();
        Byte b = lookup_underline.get(v);
        if (b == null) {
            throw new Exception("invalid parameter for 'underline' (" + v + ")");
        } else
            f.setUnderline(b);
    }

    if (_struct.containsKey("color")) {
        String v = _struct.getData("color").getString();
        Short s = lookup_colors.get(v);
        if (s == null) {
            throw new Exception("invalid parameter for 'color' (" + v + ")");
        } else
            f.setColor(s);
    }

    if (_struct.containsKey("fontsize")) {
        int s = _struct.getData("fontsize").getInt();
        f.setFontHeightInPoints((short) s);
    }

    if (_struct.containsKey("font")) {
        f.setFontName(_struct.getData("font").getString());
    }

    style.setFont(f);

    return style;
}

From source file:org.aludratest.app.excelwizard.WorkbookTracker.java

License:Apache License

void validate() {
    CellStyle warningCellStyle = this.workbook.createCellStyle();
    warningCellStyle.setFillForegroundColor(HSSFColor.RED.index);
    warningCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    for (Map.Entry<String, List<String>> headerConfig : expectedSheetColumnHeaders.entrySet()) {
        String sheetName = headerConfig.getKey();
        Sheet sheet = this.workbook.getSheet(sheetName);
        Row headerRow = sheet.getRow(0);
        List<String> expectedHeaders = headerConfig.getValue();
        int lastCellNum = headerRow.getLastCellNum();
        int firstCellNum = headerRow.getFirstCellNum();
        for (int i = firstCellNum; i < lastCellNum; i++) {
            Cell headerCell = headerRow.getCell(i);
            if (headerCell != null) {
                String actualHeader = headerCell.getStringCellValue();
                if (actualHeader != null && actualHeader.trim().length() > 0) {
                    if (!expectedHeaders.contains(actualHeader)) {
                        this.warnings.add("Unmappable column '" + actualHeader + "' in sheet '" + sheetName
                                + "' of file '" + file.getName() + "'");
                        headerCell.setCellStyle(warningCellStyle);
                        this.status = STATUS_MODIFIED;
                    }//from w ww.  j a  v  a 2 s  .  c  o  m
                }
            }
        }
    }
}

From source file:org.azkfw.doclet.jaxrs.writer.JAXRSXlsxDocletWriter.java

License:Apache License

private void printSheet(final XSSFWorkbook wb, final XSSFSheet sheet, final List<APIModel> apis) {

    XSSFCell cell = null;/*from  w  ww  .jav  a 2 s  . c  o m*/
    XSSFRow row = null;

    ////////////////////////////////////////////////////
    XSSFFont fontBold = wb.createFont();
    fontBold.setBold(true);

    CellStyle styleHeader = wb.createCellStyle();
    styleHeader.setFillPattern(CellStyle.SOLID_FOREGROUND);
    styleHeader.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
    styleHeader.setFont(fontBold);
    styleHeader.setBorderTop(CellStyle.BORDER_THIN);
    styleHeader.setBorderLeft(CellStyle.BORDER_THIN);
    styleHeader.setBorderRight(CellStyle.BORDER_THIN);
    styleHeader.setBorderBottom(CellStyle.BORDER_DOUBLE);

    row = sheet.createRow(0); //////////////////////////
    cell = row.createCell(1, Cell.CELL_TYPE_STRING);

    CellStyle style1 = wb.createCellStyle();
    style1.setFont(fontBold);
    cell.setCellStyle(style1);
    cell.setCellValue("I/F (API) ");

    row = sheet.createRow(1); //////////////////////////
    cell = row.createCell(1, Cell.CELL_TYPE_STRING);
    cell.setCellValue("API???");

    row = sheet.createRow(4); //////////////////////////
    cell = row.createCell(1, Cell.CELL_TYPE_STRING);
    cell.setCellValue("ID");
    cell.setCellStyle(styleHeader);
    cell = row.createCell(2, Cell.CELL_TYPE_STRING);
    cell.setCellValue("Group");
    cell.setCellStyle(styleHeader);
    cell = row.createCell(3, Cell.CELL_TYPE_STRING);
    cell.setCellValue("Name");
    cell.setCellStyle(styleHeader);
    cell = row.createCell(4, Cell.CELL_TYPE_STRING);
    cell.setCellValue("Comment");
    cell.setCellStyle(styleHeader);

    int offsetRow = 5;
    int offsetCol = 1;
    for (int i = 0; i < apis.size(); i++) {
        APIModel api = apis.get(i);

        row = sheet.createRow(offsetRow + i);

        cell = row.createCell(offsetCol + 0, Cell.CELL_TYPE_STRING);
        cell.setCellValue(s(api.getId()));

        cell = row.createCell(offsetCol + 2, Cell.CELL_TYPE_STRING);
        cell.setCellValue(s(api.getName()));

        cell = row.createCell(offsetCol + 3, Cell.CELL_TYPE_STRING);
        cell.setCellValue(s(api.getComment()));
    }
}

From source file:org.bbreak.excella.core.handler.DebugErrorHandler.java

License:Open Source License

/**
 * ?/*from  ww  w  . ja va  2  s.  com*/
 * 
 * @param workbook 
 * @param errorCell
 * @param exception
 */
protected void markupErrorCell(Workbook workbook, ParseException exception) {
    Cell errorCell = exception.getCell();
    if (errorCell == null) {
        return;
    }
    // ????
    workbook.setActiveSheet(workbook.getSheetIndex(errorCell.getSheet()));
    errorCell.setAsActiveCell();

    if (workbook instanceof XSSFWorkbook) {
        XSSFWorkbook xssfWorkbook = (XSSFWorkbook) workbook;

        CellStyle errorCellStyle = xssfWorkbook.createCellStyle();
        errorCellStyle.setFillForegroundColor(HSSFColorPredefined.ROSE.getIndex());
        errorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        errorCell.setCellStyle(errorCellStyle);

        // TODO:???????????????
        // XSSFComment xssfComment = ((XSSFSheet)sheet).createComment();
        // xssfComment.setRow( errorCell.getRowIndex());
        // xssfComment.setColumn( (short)errorCell.getColumnIndex());
        // XSSFRichTextString string = new XSSFRichTextString( ex.getMessage());
        // xssfComment.setString( ex.getMessage());
    } else {
        HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook;
        int sheetNum = hssfWorkbook.getNumberOfSheets();
        for (int cnt = 0; cnt < sheetNum; cnt++) {
            hssfWorkbook.getSheetAt(cnt).setSelected(false);
        }

        // ?
        CellStyle errorCellStyle = hssfWorkbook.createCellStyle();
        errorCellStyle.setFillForegroundColor(HSSFColorPredefined.ROSE.getIndex());
        errorCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        errorCell.setCellStyle(errorCellStyle);

        // ?
        short commentColFrom = (short) (errorCell.getColumnIndex() + 1);
        short commentColTo = (short) (errorCell.getColumnIndex() + ERROR_COMENT_COL_SIZE);
        int commentRowFrom = errorCell.getRowIndex();
        int commentRowTo = errorCell.getRowIndex() + ERROR_COMENT_ROW_SIZE;

        HSSFSheet hssfSheet = (HSSFSheet) errorCell.getSheet();
        HSSFPatriarch patr = hssfSheet.createDrawingPatriarch();
        hssfSheet.setSelected(true);
        HSSFComment comment = patr.createComment(
                new HSSFClientAnchor(0, 0, 0, 0, commentColFrom, commentRowFrom, commentColTo, commentRowTo));
        comment.setVisible(true);
        comment.setString(new HSSFRichTextString(createCommentMessage(exception)));
        errorCell.setCellComment(comment);
    }
}

From source file:org.cerberus.service.export.ExportServiceFactory.java

License:Open Source License

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
        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);/*w ww  .  j a  va2  s . com*/

    }
    for (TestCaseExecution execution : (List<TestCaseExecution>) list) {
        //check if the country and application shows

        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);
            summaryMap.put(keySummaryTable, stats); //updates the map
        }
        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("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(9).setCellValue("CA");
        row.createCell(10).setCellValue("NOT OK");
        row.createCell(11).setCellValue("Total");

        /*temporary styles*/
        CellStyle styleBlue = workbook.createCellStyle();
        CellStyle styleGreen = workbook.createCellStyle();
        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());
            //CA
            row.createCell(9).setCellValue(sumStats.getCa());
            int rowNumber = row.getRowNum() + 1;
            //NOT OK
            //row.createCell(11).setCellValue(sumStats.getNotOkTotal());
            row.createCell(10).setCellFormula("SUM(E" + rowNumber + ":J" + rowNumber + ")");
            //Total
            row.createCell(11).setCellFormula("SUM(D" + rowNumber + ",K" + rowNumber + ")");
            //row.createCell(12).setCellValue(sumStats.getTotal());

            if (sumStats.getOk() == sumStats.getTotal()) {
                for (int i = 0; i < 12; 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 + ")");
        //CA
        row.createCell(9).setCellFormula("SUM(J" + startRow + ":J" + rowCount + ")");

        int rowNumberTotal = row.getRowNum() + 1;
        //NOT OK
        row.createCell(10).setCellFormula("SUM(E" + rowNumberTotal + ":J" + rowNumberTotal + ")");
        //Total
        row.createCell(11).setCellFormula("SUM(D" + rowNumberTotal + ",K" + rowNumberTotal + ")");
        for (int i = 0; i < 12; 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("");

    }

    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

        Collections.sort(mapCountries);//sorts the list of countries
        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);
        }
    }
}

From source file:org.dashbuilder.dataset.backend.DataSetBackendServicesImpl.java

License:Apache License

private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;

    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 12);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();// w ww .j a v a2  s  .  c  o  m
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(titleFont);
    style.setWrapText(false);
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
    styles.put("header", style);

    Font cellFont = wb.createFont();
    cellFont.setFontHeightInPoints((short) 10);
    cellFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(3)));
    styles.put("integer_number_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(4)));
    styles.put("decimal_number_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text"));
    styles.put("text_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat()
            .getFormat(DateFormatConverter.convert(Locale.getDefault(), dateFormatPattern)));
    styles.put("date_cell", style);
    return styles;
}

From source file:org.dashbuilder.dataset.service.DataSetExportServicesImpl.java

License:Apache License

private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<>();
    CellStyle style;

    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 12);
    titleFont.setBold(true);/*from  w  ww . ja v  a2 s  .c  om*/
    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setFont(titleFont);
    style.setWrapText(false);
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.GREY_80_PERCENT.getIndex());
    styles.put("header", style);

    Font cellFont = wb.createFont();
    cellFont.setFontHeightInPoints((short) 10);
    cellFont.setBold(true);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.RIGHT);
    style.setVerticalAlignment(VerticalAlignment.BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(3)));
    styles.put("integer_number_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.RIGHT);
    style.setVerticalAlignment(VerticalAlignment.BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat().getFormat(BuiltinFormats.getBuiltinFormat(4)));
    styles.put("decimal_number_cell", style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text"));
    styles.put(TEXT_CELL, style);

    style = wb.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);
    style.setVerticalAlignment(VerticalAlignment.BOTTOM);
    style.setFont(cellFont);
    style.setWrapText(false);
    style.setDataFormat(wb.createDataFormat()
            .getFormat(DateFormatConverter.convert(Locale.getDefault(), dateFormatPattern)));
    styles.put("date_cell", style);
    return styles;
}

From source file:org.eclipse.nebula.widgets.nattable.extension.poi.PoiExcelExporter.java

License:Open Source License

private CellStyle getExcelCellStyle(Color fg, Color bg, FontData fontData, String dataFormat, int hAlign,
        int vAlign, boolean vertical) {

    CellStyle xlCellStyle = xlCellStyles
            .get(new ExcelCellStyleAttributes(fg, bg, fontData, dataFormat, hAlign, vAlign, vertical));

    if (xlCellStyle == null) {
        xlCellStyle = xlWorkbook.createCellStyle();

        if (applyBackgroundColor) {
            // Note: xl fill foreground = background
            setFillForegroundColor(xlCellStyle, bg);
            xlCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        }// w w w . java2  s. co m

        Font xlFont = xlWorkbook.createFont();
        setFontColor(xlFont, fg);
        xlFont.setFontName(fontData.getName());
        xlFont.setFontHeightInPoints((short) fontData.getHeight());
        xlCellStyle.setFont(xlFont);

        if (vertical)
            xlCellStyle.setRotation((short) 90);

        switch (hAlign) {
        case SWT.CENTER:
            xlCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
            break;
        case SWT.LEFT:
            xlCellStyle.setAlignment(CellStyle.ALIGN_LEFT);
            break;
        case SWT.RIGHT:
            xlCellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
            break;
        }
        switch (vAlign) {
        case SWT.TOP:
            xlCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
            break;
        case SWT.CENTER:
            xlCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            break;
        case SWT.BOTTOM:
            xlCellStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);
            break;
        }

        if (dataFormat != null) {
            CreationHelper createHelper = xlWorkbook.getCreationHelper();
            xlCellStyle.setDataFormat(createHelper.createDataFormat().getFormat(dataFormat));
        }

        xlCellStyles.put(new ExcelCellStyleAttributes(fg, bg, fontData, dataFormat, hAlign, vAlign, vertical),
                xlCellStyle);
    }
    return xlCellStyle;
}

From source file:org.eclipse.scada.ae.ui.views.export.excel.impl.ExportEventsImpl.java

License:Open Source License

private void makeHeader(final List<Field> columns, final HSSFSheet sheet) {
    final Font font = sheet.getWorkbook().createFont();
    font.setFontName("Arial");
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.WHITE.index);

    final CellStyle style = sheet.getWorkbook().createCellStyle();
    style.setFont(font);//from ww w  . j av a  2 s.  co  m
    style.setFillForegroundColor(HSSFColor.BLACK.index);
    style.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    final HSSFRow row = sheet.createRow(0);

    for (int i = 0; i < columns.size(); i++) {
        final Field field = columns.get(i);

        final HSSFCell cell = row.createCell(i);
        cell.setCellValue(field.getHeader());
        cell.setCellStyle(style);
    }
}