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:com.ocs.dynamo.ui.composite.table.export.TableExportActionHandlerTest.java

License:Apache License

@Test
public void testExportWithCustomCellStyle() throws IOException {

    List<EntityModel<?>> models = new ArrayList<>();
    models.add(entityModelFactory.getModel(Person.class));

    handler = new TableExportActionHandler(ui, entityModelFactory, models, messageService, REPORT_TITLE,
            columnIds, true, new CustomCellStyleGenerator() {

                private CellStyle cellStyle;

                private CellStyle bdStyle;

                @Override/*from   w w  w.ja v  a 2 s . c om*/
                public CellStyle getCustomCellStyle(Workbook workbook, Object propId, Object value,
                        AttributeModel attributeModel) {
                    if (cellStyle == null) {
                        cellStyle = workbook.createCellStyle();
                        cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
                        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
                        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
                        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
                        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
                        Font font = workbook.createFont();
                        font.setColor(IndexedColors.BLUE.getIndex());
                        cellStyle.setFont(font);
                    }

                    if (bdStyle == null) {
                        DataFormat format = workbook.createDataFormat();
                        bdStyle = workbook.createCellStyle();
                        bdStyle.setAlignment(CellStyle.ALIGN_RIGHT);
                        bdStyle.setBorderBottom(CellStyle.BORDER_THIN);
                        bdStyle.setBorderTop(CellStyle.BORDER_THIN);
                        bdStyle.setBorderLeft(CellStyle.BORDER_THIN);
                        bdStyle.setBorderRight(CellStyle.BORDER_THIN);
                        bdStyle.setDataFormat(format.getFormat("#,##0.00"));

                        Font font = workbook.createFont();
                        font.setColor(IndexedColors.BLUE.getIndex());
                        bdStyle.setFont(font);
                    }

                    if ("name".equals(propId)) {
                        return cellStyle;
                    } else if ("percentage".equals(propId)) {
                        return bdStyle;
                    }
                    return null;
                }
            });

    handler.handleAction(handler.getActions(null, null)[0], getTable(), null);

    byte[] bytes = captureSave();
    Workbook wb = importer.createWorkbook(bytes);

    Assert.assertEquals("Bas, Bob", wb.getSheetAt(0).getRow(2).getCell(0).getStringCellValue());
    Font font = wb.getFontAt(wb.getSheetAt(0).getRow(2).getCell(0).getCellStyle().getFontIndex());
    Assert.assertEquals(IndexedColors.BLUE.getIndex(), font.getColor());

    Assert.assertEquals("Patrick", wb.getSheetAt(0).getRow(3).getCell(0).getStringCellValue());

    Assert.assertEquals(35, wb.getSheetAt(0).getRow(2).getCell(1).getNumericCellValue(), 0.001);
    Assert.assertEquals(44, wb.getSheetAt(0).getRow(3).getCell(1).getNumericCellValue(), 0.001);

    // totals must be summed up
    Assert.assertEquals(79, wb.getSheetAt(0).getRow(4).getCell(1).getNumericCellValue(), 0.001);

    // percentage
    Assert.assertEquals(0.12, wb.getSheetAt(0).getRow(2).getCell(3).getNumericCellValue(), 0.001);
    Assert.assertEquals(0.15, wb.getSheetAt(0).getRow(3).getCell(3).getNumericCellValue(), 0.001);

}

From source file:com.perceptive.epm.perkolcentral.bl.ExcelReportBL.java

private void setCellBorder(Workbook wb, Cell cell) {
    CellStyle style = wb.createCellStyle();
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());

    // Create a new font and alter it.
    Font font = wb.createFont();//from  w ww  .ja v  a  2s  . c  o  m
    font.setFontHeightInPoints((short) 9);
    font.setFontName("Georgia");
    style.setFont(font);

    style.setWrapText(true);
    style.setAlignment(CellStyle.VERTICAL_CENTER);
    style.setAlignment(CellStyle.ALIGN_LEFT);
    cell.setCellStyle(style);
    cell.setCellType(Cell.CELL_TYPE_STRING);
}

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

private static void formatCell(Workbook workbook, Cell cell, ExcelCell excell,
        Map<IndexedColors, CellStyle> s_cellStyle, Font font, Font invisibleFont) {

    if (excell.getFormat() != null) {

        ExcelFormat format = excell.getFormat();

        CellStyle style = s_cellStyle.get(format.getBackgroundColor());

        if (format.isDate()) {
            // for date create a new style
            style = getDateStyle("date", cell.getSheet(), font);
            XSSFCreationHelper createHelper = (XSSFCreationHelper) cell.getSheet().getWorkbook()
                    .getCreationHelper();
            style.setDataFormat(createHelper.createDataFormat().getFormat("MMMM dd, yyyy"));
            font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            font.setBold(false);//from w w w . java  2  s .co m
            font.setFontHeightInPoints((short) 12);
            style.setFont(font);
            cell.setCellValue(new Date());
        }

        if (style == null) {
            style = workbook.createCellStyle();
            s_cellStyle.put(format.getBackgroundColor(), style);
        }

        if (format.getAlignment() > 0) {
            style.setAlignment(format.getAlignment());
        }
        if (format.getBackgroundColor() != null && !IndexedColors.WHITE.equals(format.getBackgroundColor())) {
            style.setFillForegroundColor(format.getBackgroundColor().getIndex());
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        }
        if (format.getTextColor() != null) {
            font.setColor(format.getTextColor().getIndex());
            style.setFont(font);
        }
        if (format.isBold()) {
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }
        if (format.getFontHeight() > 0) {
            font.setFontHeightInPoints(format.getFontHeight());
        }
        if (format.isWrapText()) {
            style.setWrapText(true);
        }
        style.setFont(font);
        if (format.isHideText()) {
            invisibleFont.setColor(IndexedColors.WHITE.getIndex());
            style.setFont(invisibleFont);
        }
        cell.setCellStyle(style);

    } else {
        // Let's set default formatting for free text cell
        IndexedColors defaultStyle = IndexedColors.AUTOMATIC; // we are using this index
        CellStyle style = s_cellStyle.get(defaultStyle);
        if (style == null) {
            style = workbook.createCellStyle();
            s_cellStyle.put(defaultStyle, style);
        }
        style.setWrapText(true);
        cell.setCellStyle(style);

    }

}

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

private static void setCellValueFeeFormat(Workbook wb, Cell cell, Object oneCellValue, String vendor) {
    CellStyle style = wb.createCellStyle();
    style.setDataFormat(wb.createDataFormat().getFormat("$#,#0.00"));
    cell.setCellStyle(style);/*from ww w .j a  v  a 2s  .c  om*/

    if (oneCellValue == null) {
        cell.setCellValue(Double.parseDouble("0.0"));
        return;
    }

    String feeStr = StringUtils.replace(oneCellValue.toString(), "$", StringUtils.EMPTY);
    feeStr = StringUtils.trimToEmpty(feeStr);
    feeStr = feeStr.replaceAll("\\p{javaSpaceChar}", StringUtils.EMPTY);
    if (StringUtils.isEmpty(feeStr)) {
        cell.setCellValue(Double.parseDouble("0.0"));
        return;
    }

    if (StringUtils.contains(vendor, TOLL_COMPANY_EZ_PASS_PA)
            || StringUtils.contains(vendor, TOLL_COMPANY_IPASS)
            || StringUtils.contains(vendor, TOLL_COMPANY_SUN_PASS)) {
        if (StringUtils.startsWith(feeStr, "-")) {
            feeStr = StringUtils.substring(feeStr, 1);
        } else {
            feeStr = "-" + feeStr;
        }
    }

    cell.setCellValue(Double.parseDouble(feeStr));
}

From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java

private static void setCellValueDateFormat(Workbook wb, Cell cell, Object oneCellValue, String vendor)
        throws ParseException {
    System.out.println("Incoming vendor = " + vendor);
    String tollCompanyDateFormat = tollCompanyToDateFormatMapping.get(vendor);
    System.out.println("Value = " + tollCompanyDateFormat);

    int columnIndex = cell.getColumnIndex();

    if (oneCellValue instanceof Date) {
        System.out.println("Incoming date is a Date Object.");
        tollCompanyDateFormat = "EEE MMM dd HH:mm:ss z yyyy";
    }//w ww  . ja  va2  s. c  om

    String dateStr = StringUtils.trimToEmpty(oneCellValue.toString());

    if (StringUtils.isEmpty(dateStr)) {
        cell.setCellValue(StringUtils.EMPTY);
    } else {
        if (columnIndex == 7) { // Transaction time
            cell.setCellValue(convertToExpectedTimeFormatStr(dateStr, tollCompanyDateFormat));
            return;
        } else {
            cell.setCellValue(convertToExpectedDateFormat(dateStr, tollCompanyDateFormat));
        }
    }

    CellStyle style = wb.createCellStyle();
    style.setDataFormat(wb.createDataFormat().getFormat(expectedDateFormat.toPattern()));
    cell.setCellStyle(style);
}

From source file:com.quanticate.opensource.datalistdownload.DataListDownloadWebScript.java

License:Open Source License

@Override
protected void populateBody(Object resource, Workbook workbook, Sheet sheet, List<QName> properties)
        throws IOException {
    NodeRef list = (NodeRef) resource;//from  www .j  a  va2 s .  com
    List<NodeRef> items = getItems(list);

    // Our various formats
    DataFormat formatter = workbook.createDataFormat();

    CellStyle styleInt = workbook.createCellStyle();
    styleInt.setDataFormat(formatter.getFormat("0"));
    CellStyle styleDate = workbook.createCellStyle();
    styleDate.setDataFormat(formatter.getFormat("yyyy-mm-dd"));
    CellStyle styleDouble = workbook.createCellStyle();
    styleDouble.setDataFormat(formatter.getFormat("General"));
    CellStyle styleNewLines = workbook.createCellStyle();
    styleNewLines.setWrapText(true);

    // Export the items
    int rowNum = 1, colNum = 0;
    for (NodeRef item : items) {
        Row r = sheet.createRow(rowNum);

        colNum = 0;
        for (QName prop : properties) {
            Cell c = r.createCell(colNum);

            Pair<Object, String> valAndLink = identifyValueAndLink(item, prop);

            if (valAndLink == null) {
                // This property isn't set
                c.setCellType(Cell.CELL_TYPE_BLANK);
            } else {
                Object val = valAndLink.getFirst();

                // Multi-line property?
                if (val instanceof String[]) {
                    String[] lines = (String[]) val;
                    StringBuffer text = new StringBuffer();

                    for (String line : lines) {
                        if (text.length() > 0) {
                            text.append('\n');
                        }
                        text.append(line);
                    }

                    String v = text.toString();
                    c.setCellValue(v);
                    if (lines.length > 1) {
                        c.setCellStyle(styleNewLines);
                        r.setHeightInPoints(lines.length * sheet.getDefaultRowHeightInPoints());
                    }
                }

                // Regular properties
                else if (val instanceof String) {
                    c.setCellValue((String) val);
                } else if (val instanceof Date) {
                    c.setCellValue((Date) val);
                    c.setCellStyle(styleDate);
                } else if (val instanceof Integer || val instanceof Long) {
                    double v = 0.0;
                    if (val instanceof Long)
                        v = (double) (Long) val;
                    if (val instanceof Integer)
                        v = (double) (Integer) val;
                    c.setCellValue(v);
                    c.setCellStyle(styleInt);
                } else if (val instanceof Float || val instanceof Double) {
                    double v = 0.0;
                    if (val instanceof Float)
                        v = (double) (Float) val;
                    if (val instanceof Double)
                        v = (double) (Double) val;
                    c.setCellValue(v);
                    c.setCellStyle(styleDouble);
                } else {
                    // TODO
                    System.err
                            .println("TODO: Handle Excel output of " + val.getClass().getName() + " - " + val);
                }
            }

            colNum++;
        }

        rowNum++;
    }

    // Sensible column widths please!
    colNum = 0;
    for (QName prop : properties) {
        sheet.autoSizeColumn(colNum);
        colNum++;
    }
}

From source file:com.quanticate.opensource.datalistdownload.DeclarativeSpreadsheetWebScript.java

License:Open Source License

/**
 * Generates the spreadsheet, based on the properties in the header
 *  and a callback for the body.//from w w w.  jav  a 2 s . co m
 */
public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status,
        Map<String, Object> model) throws IOException {
    Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)");

    // Build up the details of the header
    List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req);
    String[] headings = new String[propertyDetails.size()];
    String[] descriptions = new String[propertyDetails.size()];
    boolean[] required = new boolean[propertyDetails.size()];
    for (int i = 0; i < headings.length; i++) {
        Pair<QName, Boolean> property = propertyDetails.get(i);
        if (property == null || property.getFirst() == null) {
            headings[i] = "";
            required[i] = false;
        } else {
            QName column = property.getFirst();
            required[i] = property.getSecond();

            // Ask the dictionary service nicely for the details
            PropertyDefinition pd = dictionaryService.getProperty(column);
            if (pd != null && pd.getTitle(dictionaryService) != null) {
                // Use the friendly titles, which may even be localised!
                headings[i] = pd.getTitle(dictionaryService);
                descriptions[i] = pd.getDescription(dictionaryService);
            } else {
                // Nothing friendly found, try to munge the raw qname into
                //  something we can show to a user...
                String raw = column.getLocalName();
                raw = raw.substring(0, 1).toUpperCase() + raw.substring(1);

                Matcher m = qnameMunger.matcher(raw);
                if (m.matches()) {
                    headings[i] = m.group(1) + " " + m.group(2);
                } else {
                    headings[i] = raw;
                }
            }
        }
    }

    // Build a list of just the properties
    List<QName> properties = new ArrayList<QName>(propertyDetails.size());
    for (Pair<QName, Boolean> p : propertyDetails) {
        QName qn = null;
        if (p != null) {
            qn = p.getFirst();
        }
        properties.add(qn);
    }

    // Output
    if ("csv".equals(format)) {
        StringWriter sw = new StringWriter();
        CSVPrinter csv = new CSVPrinter(sw, CSVStrategy.EXCEL_STRATEGY);
        csv.println(headings);

        populateBody(resource, csv, properties);

        model.put(MODEL_CSV, sw.toString());
    } else if ("odf".equals(format) || "ods".equals(format)) {
        try {
            SpreadsheetDocument odf = SpreadsheetDocument.newSpreadsheetDocument();

            // Add the header row
            Table sheet = odf.appendSheet("Export");
            org.odftoolkit.simple.table.Row hr = sheet.appendRow();

            // TODO

            // Have the contents populated
            // TODO

            // Save it for the template
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            odf.save(baos);
            model.put(MODEL_ODF, baos.toByteArray());
        } catch (Exception e) {
            throw new WebScriptException("Error creating ODF file", e);
        }
    } else {
        Workbook wb;
        if ("xlsx".equals(format)) {
            wb = new XSSFWorkbook();
            // TODO Properties
        } else {
            wb = new HSSFWorkbook();
            // TODO Properties
        }

        // Add our header row
        Sheet sheet = wb.createSheet("Export");
        Row hr = sheet.createRow(0);
        sheet.createFreezePane(0, 1);

        Font fb = wb.createFont();
        fb.setBoldweight(Font.BOLDWEIGHT_BOLD);
        Font fi = wb.createFont();
        fi.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fi.setItalic(true);

        CellStyle csReq = wb.createCellStyle();
        csReq.setFont(fb);
        CellStyle csOpt = wb.createCellStyle();
        csOpt.setFont(fi);

        // Populate the header
        Drawing draw = null;
        for (int i = 0; i < headings.length; i++) {
            Cell c = hr.createCell(i);
            c.setCellValue(headings[i]);

            if (required[i]) {
                c.setCellStyle(csReq);
            } else {
                c.setCellStyle(csOpt);
            }

            if (headings[i].length() == 0) {
                sheet.setColumnWidth(i, 3 * 250);
            } else {
                sheet.setColumnWidth(i, 18 * 250);
            }

            if (descriptions[i] != null && descriptions[i].length() > 0) {
                // Add a description for it too
                if (draw == null) {
                    draw = sheet.createDrawingPatriarch();
                }
                ClientAnchor ca = wb.getCreationHelper().createClientAnchor();
                ca.setCol1(c.getColumnIndex());
                ca.setCol2(c.getColumnIndex() + 1);
                ca.setRow1(hr.getRowNum());
                ca.setRow2(hr.getRowNum() + 2);

                Comment cmt = draw.createCellComment(ca);
                cmt.setAuthor("");
                cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i]));
                cmt.setVisible(false);
                c.setCellComment(cmt);
            }
        }

        // Have the contents populated
        populateBody(resource, wb, sheet, properties);

        // Save it for the template
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        wb.write(baos);
        model.put(MODEL_EXCEL, baos.toByteArray());
    }
}

From source file:com.runwaysdk.dataaccess.io.excel.ErrorSheet.java

License:Open Source License

public void addRow(Row _row) {
    Row row = this.errorSheet.createRow(count++);
    row.setZeroHeight(_row.getZeroHeight());
    row.setHeight(_row.getHeight());//  w  w  w  .  j a  va2 s  .co  m

    CellStyle style = _row.getRowStyle();

    if (style != null) {
        Workbook workbook = row.getSheet().getWorkbook();

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

        row.setRowStyle(clone);
    }

    Iterator<Cell> cellIterator = _row.cellIterator();
    while (cellIterator.hasNext()) {
        Cell oldCell = cellIterator.next();
        Cell newCell = row.createCell(oldCell.getColumnIndex());

        int cellType = oldCell.getCellType();

        if (cellType == Cell.CELL_TYPE_FORMULA) {
            cellType = oldCell.getCachedFormulaResultType();
        }

        switch (cellType) {
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }
    }
}

From source file:com.runwaysdk.dataaccess.io.ExcelExporter.java

License:Open Source License

/**
 * Simple constructor that sets up the workbook
 *//*from  w  w w .  ja va 2s .  c om*/
public ExcelExporter(Workbook workbook) {
    this.workbook = workbook;
    this.listeners = new LinkedList<ExcelExportListener>();
    this.sheets = new LinkedList<ExcelExportSheet>();

    Font font = workbook.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);

    this.boldStyle = workbook.createCellStyle();
    this.boldStyle.setFont(font);
}

From source file:com.runwaysdk.query.ExcelExporter.java

License:Open Source License

public ExcelExporter(String sheetName, Workbook workbook) {
    this.workbook = workbook;
    this.dateStyle = workbook.createCellStyle();
    this.dateStyle.setDataFormat((short) 0xe);

    this.sheetName = sheetName;
}