Example usage for org.apache.poi.ss.usermodel Workbook createCellStyle

List of usage examples for org.apache.poi.ss.usermodel Workbook createCellStyle

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createCellStyle

Introduction

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

Prototype

CellStyle createCellStyle();

Source Link

Document

Create a new Cell style and add it to the workbook's style table

Usage

From source file:guru.qas.martini.report.DefaultState.java

License:Apache License

protected void colorCompromisedThemes() {
    Collection<Cell> failed = statii.get("FAILED");

    if (!failed.isEmpty()) {
        List<Row> rows = Lists.newArrayListWithExpectedSize(failed.size());
        for (Cell cell : failed) {
            Row row = cell.getRow();/*ww w.  ja v a2 s . c o  m*/
            rows.add(row);
        }

        Set<Cell> compromisedThemeCells = Sets.newHashSet();

        Map<String, Collection<Cell>> themeMap = themes.asMap();
        for (Map.Entry<String, Collection<Cell>> mapEntry : themeMap.entrySet()) {
            Collection<Cell> themeCells = mapEntry.getValue();

            boolean compromised = false;
            for (Iterator<Cell> iterator = themeCells.iterator(); !compromised && iterator.hasNext();) {
                Cell themeCell = iterator.next();
                Row row = themeCell.getRow();
                compromised = rows.contains(row);
            }

            if (compromised) {
                compromisedThemeCells.addAll(themeCells);
            }
        }

        Set<String> compromisedThemes = Sets.newHashSet();
        for (Cell themeCell : compromisedThemeCells) {
            String contents = themeCell.getStringCellValue();
            if (null != contents) {
                Iterable<String> themes = Splitter.onPattern("\\s+").omitEmptyStrings().split(contents);
                Iterables.addAll(compromisedThemes, themes);
            }
        }

        for (String theme : compromisedThemes) {
            Collection<Cell> cells = themes.get(theme);
            for (Cell cell : cells) {
                CellStyle cellStyle = cell.getCellStyle();
                Sheet sheet = cell.getSheet();
                Workbook workbook = sheet.getWorkbook();

                int originalFontIndex = cellStyle.getFontIndexAsInt();
                Font originalFont = workbook.getFontAt(originalFontIndex);

                CellStyle clone = workbook.createCellStyle();
                clone.cloneStyleFrom(cellStyle);

                Font font = workbook.findFont(true, IndexedColors.DARK_RED.getIndex(),
                        originalFont.getFontHeight(), originalFont.getFontName(), originalFont.getItalic(),
                        originalFont.getStrikeout(), originalFont.getTypeOffset(), originalFont.getUnderline());

                if (null == font) {
                    font = workbook.createFont();
                    font.setBold(true);
                    font.setColor(IndexedColors.DARK_RED.getIndex());
                    font.setFontHeight(originalFont.getFontHeight());
                    font.setFontName(originalFont.getFontName());
                    font.setItalic(originalFont.getItalic());
                    font.setStrikeout(originalFont.getStrikeout());
                    font.setTypeOffset(originalFont.getTypeOffset());
                    font.setUnderline(originalFont.getUnderline());
                }
                clone.setFont(font);
                cell.setCellStyle(clone);
            }
        }
    }
}

From source file:guru.qas.martini.report.DefaultState.java

License:Apache License

@Override
public void updateSuites(Sheet sheet) {
    int lastRowNum = sheet.getLastRowNum();
    Row row = sheet.createRow(0 == lastRowNum ? 0 : lastRowNum + 1);
    row.createCell(0, CellType.STRING).setCellValue("ID");
    row.createCell(1, CellType.STRING).setCellValue("Date");
    row.createCell(2, CellType.STRING).setCellValue("Name");
    row.createCell(3, CellType.STRING).setCellValue("Hostname");
    row.createCell(4, CellType.STRING).setCellValue("IP");
    row.createCell(5, CellType.STRING).setCellValue("Username");
    row.createCell(6, CellType.STRING).setCellValue("Profiles");
    row.createCell(7, CellType.STRING).setCellValue("Environment Variables");

    for (Map.Entry<String, JsonObject> mapEntry : suites.entrySet()) {
        row = sheet.createRow(sheet.getLastRowNum() + 1);

        String id = mapEntry.getKey();
        row.createCell(0, CellType.STRING).setCellValue(id);

        JsonObject suite = mapEntry.getValue();
        JsonPrimitive primitive = suite.getAsJsonPrimitive("startTimestamp");
        Long timestamp = null == primitive ? null : primitive.getAsLong();

        Cell cell = row.createCell(1);//ww w .ja v a  2s  .c o m
        if (null != timestamp) {
            Workbook workbook = sheet.getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();
            CreationHelper creationHelper = workbook.getCreationHelper();
            cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm"));
            cellStyle.setVerticalAlignment(VerticalAlignment.TOP);
            cell.setCellValue(new Date(timestamp));
            cell.setCellStyle(cellStyle);
        }

        cell = row.createCell(2);
        primitive = suite.getAsJsonPrimitive("name");
        cell.setCellValue(null == primitive ? "" : primitive.getAsString());

        cell = row.createCell(3);
        JsonObject host = suite.getAsJsonObject("host");
        primitive = null == host ? null : host.getAsJsonPrimitive("name");
        cell.setCellValue(null == primitive ? "" : primitive.getAsString());

        cell = row.createCell(4);
        primitive = null == host ? null : host.getAsJsonPrimitive("ip");
        cell.setCellValue(null == primitive ? "" : primitive.getAsString());

        cell = row.createCell(5);
        primitive = null == host ? null : host.getAsJsonPrimitive("username");
        cell.setCellValue(null == primitive ? "" : primitive.getAsString());

        cell = row.createCell(6);
        JsonArray array = suite.getAsJsonArray("profiles");
        List<String> profiles = Lists.newArrayList();
        if (null != array) {
            int size = array.size();
            for (int i = 0; i < size; i++) {
                JsonElement element = array.get(i);
                String profile = null == element ? null : element.getAsString();
                profiles.add(profile);
            }
            String profilesValue = Joiner.on('\n').skipNulls().join(profiles);
            cell.setCellValue(profilesValue);
        }

        cell = row.createCell(7);
        JsonObject environmentVariables = suite.getAsJsonObject("environment");
        Map<String, String> index = new TreeMap<>();
        if (null != environmentVariables) {
            Set<Map.Entry<String, JsonElement>> entries = environmentVariables.entrySet();
            for (Map.Entry<String, JsonElement> environmentEntry : entries) {
                String key = environmentEntry.getKey();
                JsonElement element = environmentEntry.getValue();
                String value = null == element ? "" : element.getAsString();
                index.put(key, value);
            }

            String variablesValue = Joiner.on('\n').withKeyValueSeparator('=').useForNull("").join(index);
            cell.setCellValue(variablesValue);
        }
    }

    for (int i = 0; i < 8; i++) {
        sheet.autoSizeColumn(i, false);
    }
}

From source file:guru.qas.martini.report.DefaultTraceabilityMatrix.java

License:Apache License

protected CellStyle getHeaderStyle(Workbook workbook) {
    CellStyle style = workbook.createCellStyle();
    style.setBorderBottom(BorderStyle.MEDIUM);
    Font headerFont = getHeaderFont(workbook);
    style.setFont(headerFont);/*  ww w  .  j  a va  2s . com*/
    return style;
}

From source file:Import.Utils.XSSFConvert.java

public void convert() throws IOException {
    Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() };
    for (int i = 0; i < wbs.length; i++) {
        Workbook wb = wbs[i];
        CreationHelper createHelper = wb.getCreationHelper();

        // create a new sheet
        org.apache.poi.ss.usermodel.Sheet s = wb.createSheet();
        // declare a row object reference
        Row r = null;//from  ww  w  .j a v  a 2s .  co m
        // declare a cell object reference
        Cell c = null;
        // create 2 cell styles
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();
        DataFormat df = wb.createDataFormat();

        // create 2 fonts objects
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // Set font 1 to 12 point type, blue and bold
        f.setFontHeightInPoints((short) 12);
        f.setColor(IndexedColors.RED.getIndex());
        f.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // Set font 2 to 10 point type, red and bold
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.RED.getIndex());
        f2.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // Set cell style and formatting
        cs.setFont(f);
        cs.setDataFormat(df.getFormat("#,##0.0"));

        // Set the other cell style and formatting
        cs2.setBorderBottom(cs2.BORDER_THIN);
        cs2.setDataFormat(df.getFormat("text"));
        cs2.setFont(f2);

        // Define a few rows
        for (int rownum = 0; rownum < 30; rownum++) {
            r = s.createRow(rownum);
            for (int cellnum = 0; cellnum < 10; cellnum += 2) {
                c = r.createCell(cellnum);
                Cell c2 = r.createCell(cellnum + 1);

                c.setCellValue((double) rownum + (cellnum / 10));
                c2.setCellValue(createHelper.createRichTextString("Hello! " + cellnum));
            }
        }

        // Save
        String filename = "workbook.xls";
        if (wb instanceof XSSFWorkbook) {
            filename = filename + "x";
        }

        FileOutputStream out = null;
        try {
            out = new FileOutputStream(filename);
        } catch (FileNotFoundException ex) {
            Logger.getLogger(XSSFConvert.class.getName()).log(Level.SEVERE, null, ex);
        }
        wb.write(out);
        out.close();
    }
}

From source file:io.vulpine.lib.kalo.PoiConfig.java

License:Apache License

public CellStyle getHeaderStyle(final Workbook book, final Poi poi) {
    final CellStyle style = book.createCellStyle();
    final Font font = book.createFont();

    font.setFontName("Arial");
    font.setBold(true);/*from  w  ww .  j  a va  2s .c om*/
    font.setFontHeightInPoints((short) 12);

    style.setAlignment(HorizontalAlignment.CENTER);
    style.setFont(font);

    return style;
}

From source file:it.eng.spagobi.engines.console.exporter.types.ExporterExcel.java

License:Mozilla Public License

public CellStyle[] fillSheetHeader(Sheet sheet, Workbook wb, CreationHelper createHelper,
        int beginRowHeaderData, int beginColumnHeaderData) {
    CellStyle hCellStyle = buildHeaderCellStyle(sheet);

    int colnum = extractedFieldsMetaData.size();
    Row row = sheet.getRow(beginRowHeaderData);
    CellStyle[] cellTypes = new CellStyle[colnum]; // array for numbers patterns storage

    for (int j = 0; j < colnum; j++) {
        Cell cell = row.createCell(j + beginColumnHeaderData);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        IFieldMetaData fieldMetaData = extractedFieldsMetaData.get(j);
        String fieldName = fieldMetaData.getName();
        String format = (String) fieldMetaData.getProperty("format");
        String alias = (String) fieldMetaData.getAlias();
        Boolean visible = (Boolean) fieldMetaData.getProperty("visible");
        if (extractedFields != null && extractedFields.get(j) != null) {
            Object f = extractedFields.get(j);
            logger.debug("Extracted field " + fieldName + " is instance of " + f.getClass().getName());
            if (f instanceof Field) {
                Field field = (Field) f;
                fieldName = field.getName();
                if (field.getPattern() != null) {
                    format = field.getPattern();
                }/*from   www . j  a va2s . co m*/
            }
        }
        CellStyle aCellStyle = wb.createCellStyle();
        if (format != null) {
            short formatInt = HSSFDataFormat.getBuiltinFormat(format);
            aCellStyle.setDataFormat(formatInt);
            cellTypes[j] = aCellStyle;
        }
        if (visible != null && visible.booleanValue() == true) {
            if (alias != null && !alias.equals("")) {
                cell.setCellValue(createHelper.createRichTextString(alias));
            } else {
                cell.setCellValue(createHelper.createRichTextString(fieldName));
            }
            cell.setCellStyle(hCellStyle);
        }
    }
    return cellTypes;
}

From source file:it.eng.spagobi.engines.console.exporter.types.ExporterExcel.java

License:Mozilla Public License

public void fillSheetData(Sheet sheet, Workbook wb, CreationHelper createHelper, CellStyle[] cellTypes,
        int beginRowData, int beginColumnData) {

    CellStyle dCellStyle = buildDataCellStyle(sheet);

    int rownum = beginRowData;
    short formatIndexInt = HSSFDataFormat.getBuiltinFormat("#,##0");
    CellStyle cellStyleInt = wb.createCellStyle(); // cellStyleInt is the default cell style for integers
    cellStyleInt.cloneStyleFrom(dCellStyle);
    cellStyleInt.setDataFormat(formatIndexInt);

    short formatIndexDoub = HSSFDataFormat.getBuiltinFormat("#,##0.00");
    CellStyle cellStyleDoub = wb.createCellStyle(); // cellStyleDoub is the default cell style for doubles
    cellStyleDoub.cloneStyleFrom(dCellStyle);
    cellStyleDoub.setDataFormat(formatIndexDoub);

    CellStyle cellStyleDate = wb.createCellStyle(); // cellStyleDate is the default cell style for dates
    cellStyleDate.cloneStyleFrom(dCellStyle);
    //cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("yy-m-d h:mm"));
    cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("yy-m-d"));

    for (int i = 0; i < numberOfRows; i++) {
        Row rowVal = sheet.getRow(rownum);
        IRecord record = (IRecord) dataStore.getRecordAt(i);
        List fields = record.getFields();
        int length = extractedFieldsMetaData.size();
        for (int fieldIndex = 0; fieldIndex < length; fieldIndex++) {
            IFieldMetaData metaField = extractedFieldsMetaData.get(fieldIndex);
            IField f = (IField) record.getFieldAt((Integer) metaField.getProperty("index"));
            if (f != null && f.getValue() != null) {

                Boolean visible = (Boolean) metaField.getProperty("visible");
                if (visible) {
                    Class c = metaField.getType();
                    logger.debug("Column [" + (fieldIndex) + "] class is equal to [" + c.getName() + "]");
                    if (rowVal == null) {
                        rowVal = sheet.createRow(rownum);
                    }//from   w  w  w. j a  v  a 2 s  . co m
                    Cell cell = rowVal.createCell(fieldIndex + beginColumnData);
                    cell.setCellStyle(dCellStyle);
                    if (Integer.class.isAssignableFrom(c) || Short.class.isAssignableFrom(c)) {
                        logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "INTEGER" + "]");
                        Number val = (Number) f.getValue();
                        cell.setCellValue(val.intValue());
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellStyle(
                                (cellTypes[fieldIndex] != null) ? cellTypes[fieldIndex] : cellStyleInt);
                    } else if (Number.class.isAssignableFrom(c)) {
                        logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "NUMBER" + "]");
                        Number val = (Number) f.getValue();
                        cell.setCellValue(val.doubleValue());
                        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellStyle(
                                (cellTypes[fieldIndex] != null) ? cellTypes[fieldIndex] : cellStyleDoub);
                    } else if (String.class.isAssignableFrom(c)) {
                        logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "STRING" + "]");
                        String val = (String) f.getValue();
                        cell.setCellValue(createHelper.createRichTextString(val));
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    } else if (Boolean.class.isAssignableFrom(c)) {
                        logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "BOOLEAN" + "]");
                        Boolean val = (Boolean) f.getValue();
                        cell.setCellValue(val.booleanValue());
                        cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
                    } else if (Date.class.isAssignableFrom(c)) {
                        logger.debug("Column [" + (fieldIndex + 1) + "] type is equal to [" + "DATE" + "]");
                        Date val = (Date) f.getValue();

                        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                        String dtString = df.format(val);
                        cell.setCellValue(dtString);
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    } else {
                        logger.warn("Column [" + (fieldIndex + 1) + "] type is equal to [" + "???" + "]");
                        String val = f.getValue().toString();
                        cell.setCellValue(createHelper.createRichTextString(val));
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    }
                }

            }
        }

        rownum++;
    }
}

From source file:it.unitn.elisco.utils.Utilities.java

public static Workbook getExcelFromQuestionList(String workbookName, List<Question> questions) {

    // Create EXCEL File (Workbook with sheets)
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet(workbookName);

    // Create styles for cells
    CellStyle questionStyle = workbook.createCellStyle();
    questionStyle.setWrapText(true);/*from   w w w . j ava 2  s.c o m*/
    questionStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    CellStyle othersStyle = workbook.createCellStyle();
    othersStyle.setAlignment(HorizontalAlignment.CENTER);
    othersStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    // Create header row 
    Row headerRow = sheet.createRow(0);
    headerRow.createCell(0).setCellValue("ID");
    headerRow.createCell(1).setCellValue("TAG");
    headerRow.createCell(2).setCellValue("DOMANDA");
    headerRow.createCell(3).setCellValue("APRROVATA (SI/NO)");
    headerRow.getCell(0).setCellStyle(othersStyle);
    headerRow.getCell(1).setCellStyle(othersStyle);
    headerRow.getCell(2).setCellStyle(othersStyle);
    headerRow.getCell(3).setCellStyle(othersStyle);

    int rownum = 1;
    for (Question question : questions) {
        // Create a row
        Row row = sheet.createRow(rownum++);

        // Create cells for id and question and set their values
        row.createCell(0).setCellValue(question.getId());
        row.createCell(1).setCellValue(question.getTag());
        row.createCell(2).setCellValue(question.getBody());
        // Create empty cell for admin input
        row.createCell(3);

        // Set cell styles
        row.getCell(0).setCellStyle(othersStyle);
        row.getCell(1).setCellStyle(othersStyle);
        row.getCell(2).setCellStyle(questionStyle);
        row.getCell(3).setCellStyle(othersStyle);
    }

    sheet.autoSizeColumn(0);
    sheet.autoSizeColumn(1);
    sheet.setColumnWidth(2, 100 * 256); // Set questionStyle column width to 100 characters
    sheet.autoSizeColumn(3);

    return workbook;
}

From source file:jdbreport.model.io.xls.poi.Excel2003Writer.java

License:Apache License

private CellStyle getStyle(Object styleId, Type cellType, Workbook wb, CreationHelper createHelper) {
    if (cellType == Type.DATE || cellType == Type.FLOAT || cellType == Type.CURRENCY) {
        String key = String.valueOf(styleId) + cellType;
        CellStyle style = styleMap.get(key);
        if (style == null) {
            style = wb.createCellStyle();
            CellStyle parentStyle = styleMap.get(styleId);
            if (parentStyle != null) {
                style.cloneStyleFrom(parentStyle);
            }/*from w  ww.  ja v a 2s  .c  o  m*/
            if (cellType == Type.DATE) {
                style.setDataFormat(createHelper.createDataFormat().getFormat("dd.mm.yyyy"));
            } else {
                style.setDataFormat(createHelper.createDataFormat().getFormat("General"));
            }
            styleMap.put(key, style);
        }
        return style;
    }
    return styleMap.get(styleId);
}

From source file:jdbreport.model.io.xls.poi.Excel2003Writer.java

License:Apache License

protected CellStyle createStyle(jdbreport.model.CellStyle style, Workbook wb) {

    CellStyle newStyle = wb.createCellStyle();
    newStyle.setAlignment(convertHorizontalAlign(style.getHorizontalAlignment()));
    newStyle.setVerticalAlignment(convertVerticalAlign(style.getVerticalAlignment()));

    Border border = style.getBorders(Border.LINE_BOTTOM);
    if (border != null) {
        newStyle.setBorderBottom(getBorder(border));
        newStyle.setBottomBorderColor(colorToIndex(wb, border.getColor()));
    }//from   w ww . j av  a  2 s.  co  m
    border = style.getBorders(Border.LINE_TOP);
    if (border != null) {
        newStyle.setBorderTop(getBorder(border));
        newStyle.setTopBorderColor(colorToIndex(wb, border.getColor()));
    }
    border = style.getBorders(Border.LINE_LEFT);
    if (border != null) {
        newStyle.setBorderLeft(getBorder(border));
        newStyle.setLeftBorderColor(colorToIndex(wb, border.getColor()));
    }
    border = style.getBorders(Border.LINE_RIGHT);
    if (border != null) {
        newStyle.setBorderRight(getBorder(border));
        newStyle.setRightBorderColor(colorToIndex(wb, border.getColor()));
    }

    Font font = wb.createFont();
    font.setFontName(style.getFamily());
    if (style.isBold()) {
        font.setBold(true);
    }
    font.setItalic(style.isItalic());
    if (style.isUnderline()) {
        font.setUnderline(Font.U_SINGLE);
    }
    if (style.isStrikethrough()) {
        font.setStrikeout(true);
    }
    font.setFontHeightInPoints((short) style.getSize());
    if (style.getForegroundColor() != null && !style.getForegroundColor().equals(Color.black)) {
        font.setColor(colorToIndex(wb, style.getForegroundColor()));
    }

    newStyle.setFont(font);

    if (style.getBackground() != null && !style.getBackground().equals(Color.white)) {
        short colorIndex = colorToIndex(wb, style.getBackground());
        newStyle.setFillForegroundColor(colorIndex);
        newStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    }

    if (style.getAngle() != 0) {
        int angle = style.getAngle();
        if (angle > 90 && angle <= 180) {
            angle = 90;
        } else if (angle > 180 && angle <= 270) {
            angle = -90;
        } else if (angle > 270) {
            angle = -(360 - angle);
        }
        newStyle.setRotation((short) angle);
    }

    newStyle.setWrapText(style.isWrapLine());

    return newStyle;
}