Example usage for org.apache.poi.ss.usermodel BuiltinFormats getBuiltinFormat

List of usage examples for org.apache.poi.ss.usermodel BuiltinFormats getBuiltinFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. BuiltinFormats
  6. getBuiltinFormat

Introduction

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

Prototype

public static int getBuiltinFormat(String pFmt)

Source Link

Document

Get the format index that matches the given format string.
Automatically converts "text" to excel's format string to represent text.

Usage

From source file:com.biomeris.i2b2.export.engine.ExcelCreator.java

License:Open Source License

public void createFileAndWorkbook(File exportDir, String exportName) {
    if (excelFile == null && zipFile == null && workbook == null) {
        excelFile = new File(exportDir, exportName + EXTENSION);
        zipFile = new File(exportDir, exportName + EXTENSION + ZIP_EXTENSION);

        workbook = new SXSSFWorkbook(rowAccessWindowSize);

        dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("m/d/yy h:mm"));

        headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor((short) 1);
        headerStyle.setFillBackgroundColor((short) 41);
        headerStyle.setFillPattern((short) 17);
        headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
        headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        leftSeparatorStyle = workbook.createCellStyle();
        leftSeparatorStyle.setBorderLeft(CellStyle.BORDER_THIN);
    }//from ww  w  .  j  av  a 2  s  .co m
}

From source file:com.cms.utils.ExportExcellCelltype.java

public CellStyle cellTypeFormatNumber(Boolean wraptext) {
    CellStyle cellStyleFormatNumber = wb.createCellStyle();
    cellStyleFormatNumber.setDataFormat((short) BuiltinFormats.getBuiltinFormat("#,##0"));
    cellStyleFormatNumber.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyleFormatNumber.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyleFormatNumber.setBorderRight(CellStyle.BORDER_THIN);
    cellStyleFormatNumber.setBorderTop(CellStyle.BORDER_THIN);
    return cellStyleFormatNumber;
}

From source file:com.github.igor_kudryashov.utils.excel.ExcelWriter.java

License:Apache License

/**
 * Returns a style of cell/*  ww  w .ja  v a2s. c  o m*/
 *
 * @param rownum
 *            the number of row for count odd/even rows
 * @param entry
 *            value of cell
 * @param header
 *            <code>true</code> if this row is the header, otherwise
 *            <code>false</code>
 * @return the cell style
 */
private XSSFCellStyle getCellStyle(int rownum, Object entry, boolean header) {
    XSSFCellStyle style;
    String name = entry.getClass().getName();
    if (header) {
        name += "_header";
    } else if ((rownum % 2) == 0) {
        name += "_even";
    }
    if (styles.containsKey(name)) {
        // if we already have a style for this class, return it
        style = styles.get(name);
    } else {
        // create new style
        style = (XSSFCellStyle) workbook.createCellStyle();
        style.setVerticalAlignment(VerticalAlignment.TOP);
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, colorBorder);
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, colorBorder);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, colorBorder);
        // format data
        XSSFDataFormat fmt = (XSSFDataFormat) workbook.createDataFormat();
        short format = 0;
        if (name.contains("Date")) {
            format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0xe));
            style.setAlignment(CellStyle.ALIGN_LEFT);
        } else if (name.contains("Double")) {
            format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(2));
            style.setAlignment(CellStyle.ALIGN_RIGHT);
        } else if (name.contains("Integer")) {
            format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(1));
            style.setAlignment(CellStyle.ALIGN_RIGHT);
        } else {
            style.setAlignment(CellStyle.ALIGN_LEFT);
            if (!header) {
                style.setWrapText(true);
            }
        }

        if (header) {
            // for header
            style.setFillForegroundColor(colorHeaderBackground);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        } else if (name.contains("_even")) {
            // for even rows
            style.setFillForegroundColor(colorEvenCellBackground);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        }
        style.setDataFormat(format);
        // keep the style for reuse
        styles.put(name, style);
    }
    return style;
}

From source file:com.myjeeva.poi.CustomXSSFSheetXMLHandler.java

License:Apache License

public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {

    if (isTextTag(name)) {
        vIsOpen = true;/*from   ww w  . j a  va 2  s  .  com*/
        // Clear contents cache
        value.setLength(0);
    } else if ("is".equals(name)) {
        // Inline string outer tag
        isIsOpen = true;
    } else if ("f".equals(name)) {
        // Clear contents cache
        formula.setLength(0);

        // Mark us as being a formula if not already
        if (nextDataType == xssfDataType.NUMBER) {
            nextDataType = xssfDataType.FORMULA;
        }

        // Decide where to get the formula string from
        String type = attributes.getValue("t");
        if (type != null && type.equals("shared")) {
            // Is it the one that defines the shared, or uses it?
            String ref = attributes.getValue("ref");
            String si = attributes.getValue("si");

            if (ref != null) {
                // This one defines it
                // TODO Save it somewhere
                fIsOpen = true;
            } else {
                // This one uses a shared formula
                // TODO Retrieve the shared formula and tweak it to
                //  match the CURRENT cell
                if (formulasNotResults) {
                    System.err.println("Warning - shared formulas not yet supported!");
                } else {
                    // It's a shared formula, so we can't get at the formula string yet
                    // However, they don't care about the formula string, so that's ok!
                }
            }
        } else {
            fIsOpen = true;
        }
    } else if ("oddHeader".equals(name) || "evenHeader".equals(name) || "firstHeader".equals(name)
            || "firstFooter".equals(name) || "oddFooter".equals(name) || "evenFooter".equals(name)) {
        hfIsOpen = true;
        // Clear contents cache
        headerFooter.setLength(0);
    } else if ("row".equals(name)) {
        int rowNum = Integer.parseInt(attributes.getValue("r")) - 1;
        output.startRow(rowNum);
    }
    // c => cell
    else if ("c".equals(name)) {
        // Set up defaults.
        this.nextDataType = xssfDataType.NUMBER;
        this.formatIndex = -1;
        this.formatString = null;
        cellRef = attributes.getValue("r");
        String cellType = attributes.getValue("t");
        String cellStyleStr = attributes.getValue("s");
        if ("b".equals(cellType))
            nextDataType = xssfDataType.BOOLEAN;
        else if ("e".equals(cellType))
            nextDataType = xssfDataType.ERROR;
        else if ("inlineStr".equals(cellType))
            nextDataType = xssfDataType.INLINE_STRING;
        else if ("s".equals(cellType))
            nextDataType = xssfDataType.SST_STRING;
        else if ("str".equals(cellType))
            nextDataType = xssfDataType.FORMULA;
        else if (cellStyleStr != null) {
            // Number, but almost certainly with a special style or format
            int styleIndex = Integer.parseInt(cellStyleStr);
            XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
            this.formatIndex = style.getDataFormat();
            this.formatString = style.getDataFormatString();
            if (this.formatString == null)
                this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
        }
    }
}

From source file:com.rapidminer.operator.nio.model.xlsx.XlsxNumberFormats.java

License:Open Source License

/**
 * @param cellStyleId/*www .  j a  va 2s .c  o m*/
 *            the cell style ID stored within the XLSX worksheet cell tag. <code>null</code> is
 *            allowed and will return <code>false</code>
 * @return <code>true</code> in case it is a date, <code>false</code> otherwise
 */
public boolean isDateFormatStyle(String cellStyleId) {
    if (cellStyleId == null) {
        return false;
    }

    /*
     * Cell styles references are stored within the cell tag as a 0 based index of the cell
     * formats.
     */
    int numberFormatId = cellNumberFormatIds[Integer.parseInt(cellStyleId)];
    Boolean isNumberFormat = isDateFormatCache.get(numberFormatId);
    if (isNumberFormat == null) {
        // Check builtin formats if custom date format cache does not contain a hit
        String builtinFormat = BuiltinFormats.getBuiltinFormat(numberFormatId);
        if (builtinFormat != null) {
            isNumberFormat = checkForDateFormat(numberFormatId, builtinFormat);
            isDateFormatCache.put(numberFormatId, isNumberFormat);
        } else {
            // It is neither a custom nor a a built-in format -> probably not a date format
            isNumberFormat = false;
        }
    }
    return isNumberFormat;
}

From source file:com.teradata.demo.utils.excel.XSSFSheetXMLHandler.java

License:Apache License

public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {

    if (isTextTag(name)) {
        vIsOpen = true;//from w ww. jav a  2s  .  c o m
        // Clear contents cache
        value.setLength(0);
    } else if ("is".equals(name)) {
        // Inline string outer tag
        isIsOpen = true;
    } else if ("f".equals(name)) {
        // Clear contents cache
        formula.setLength(0);

        // Mark us as being a formula if not already
        if (nextDataType == xssfDataType.NUMBER) {
            nextDataType = xssfDataType.FORMULA;
        }

        // Decide where to get the formula string from
        String type = attributes.getValue("t");
        if (type != null && type.equals("shared")) {
            // Is it the one that defines the shared, or uses it?
            String ref = attributes.getValue("ref");
            String si = attributes.getValue("si");

            if (ref != null) {
                // This one defines it
                // TODO Save it somewhere
                fIsOpen = true;
            } else {
                // This one uses a shared formula
                // TODO Retrieve the shared formula and tweak it to 
                //  match the current cell
                if (formulasNotResults) {
                    System.err.println("Warning - shared formulas not yet supported!");
                } else {
                    // It's a shared formula, so we can't get at the formula string yet
                    // However, they don't care about the formula string, so that's ok!
                }
            }
        } else {
            fIsOpen = true;
        }
    } else if ("oddHeader".equals(name) || "evenHeader".equals(name) || "firstHeader".equals(name)
            || "firstFooter".equals(name) || "oddFooter".equals(name) || "evenFooter".equals(name)) {
        hfIsOpen = true;
        // Clear contents cache
        headerFooter.setLength(0);
    } else if ("row".equals(name)) {
        int rowNum = Integer.parseInt(attributes.getValue("r")) - 1;
        output.startRow(rowNum);
    }
    // c => cell
    else if ("c".equals(name)) {
        // Set up defaults.
        this.nextDataType = xssfDataType.NUMBER;
        this.formatIndex = -1;
        this.formatString = null;
        cellRef = attributes.getValue("r");

        String cellType = attributes.getValue("t");
        String cellStyleStr = attributes.getValue("s");
        if ("b".equals(cellType))
            nextDataType = xssfDataType.BOOLEAN;
        else if ("e".equals(cellType))
            nextDataType = xssfDataType.ERROR;
        else if ("inlineStr".equals(cellType))
            nextDataType = xssfDataType.INLINE_STRING;
        else if ("s".equals(cellType))
            nextDataType = xssfDataType.SST_STRING;
        else if ("str".equals(cellType))
            nextDataType = xssfDataType.FORMULA;
        else if (cellStyleStr != null) {
            // Number, but almost certainly with a special style or format
            int styleIndex = Integer.parseInt(cellStyleStr);
            XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
            this.formatIndex = style.getDataFormat();
            this.formatString = style.getDataFormatString();
            if (this.formatString == null)
                this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
        }
    }
}

From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java

License:Apache License

/**
 *  Use Excel conditional formatting to highlight payments that are due in the next thirty days.
 *  In this example, Due dates are entered in cells A2:A4.
 *///from ww w  . j a v a  2 s.  c  o  m
static void expiry(Sheet sheet) {
    CellStyle style = sheet.getWorkbook().createCellStyle();
    style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("d-mmm"));

    sheet.createRow(0).createCell(0).setCellValue("Date");
    sheet.createRow(1).createCell(0).setCellFormula("TODAY()+29");
    sheet.createRow(2).createCell(0).setCellFormula("A2+1");
    sheet.createRow(3).createCell(0).setCellFormula("A3+1");

    for (int rownum = 1; rownum <= 3; rownum++)
        sheet.getRow(rownum).getCell(0).setCellStyle(style);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF
            .createConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
    FontFormatting font = rule1.createFontFormatting();
    font.setFontStyle(false, true);
    font.setFontColorIndex(IndexedColors.BLUE.index);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:A4") };

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.getRow(0).createCell(1).setCellValue("Dates within the next 30 days are highlighted");
}

From source file:export.notes.view.to.excel.ExcelWriter.java

License:Apache License

private void createCellStyle(int position, ViewColumn column, ViewEntry entry) throws NotesException {
    CellStyle cellStyle = workbook.createCellStyle();
    Font font = workbook.createFont();
    if (column.isFontBold()) {
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    }//  w  w  w.j ava2 s  .  co  m
    font.setItalic(column.isFontItalic());
    switch (column.getFontColor()) {
    case RichTextStyle.COLOR_BLACK:
        font.setColor(HSSFColor.BLACK.index);
        break;
    case RichTextStyle.COLOR_BLUE:
        font.setColor(HSSFColor.BLUE.index);
        break;
    case RichTextStyle.COLOR_CYAN:
        font.setColor(HSSFColor.CORAL.index);
        break;
    case RichTextStyle.COLOR_DARK_BLUE:
        font.setColor(HSSFColor.DARK_BLUE.index);
        break;
    case RichTextStyle.COLOR_DARK_CYAN:
        font.setColor(HSSFColor.DARK_GREEN.index);
        break;
    case RichTextStyle.COLOR_DARK_GREEN:
        font.setColor(HSSFColor.DARK_GREEN.index);
        break;
    case RichTextStyle.COLOR_DARK_MAGENTA:
        font.setColor(HSSFColor.VIOLET.index);
        break;
    case RichTextStyle.COLOR_DARK_RED:
        font.setColor(HSSFColor.DARK_RED.index);
        break;
    case RichTextStyle.COLOR_DARK_YELLOW:
        font.setColor(HSSFColor.DARK_YELLOW.index);
        break;
    case RichTextStyle.COLOR_GRAY:
        font.setColor(HSSFColor.GREY_80_PERCENT.index);
        break;
    case RichTextStyle.COLOR_GREEN:
        font.setColor(HSSFColor.GREEN.index);
        break;
    case RichTextStyle.COLOR_LIGHT_GRAY:
        font.setColor(HSSFColor.GREY_50_PERCENT.index);
        break;
    case RichTextStyle.COLOR_MAGENTA:
        font.setColor(HSSFColor.VIOLET.index);
        break;
    case RichTextStyle.COLOR_RED:
        font.setColor(HSSFColor.RED.index);
        break;
    case RichTextStyle.COLOR_WHITE:
        font.setColor(HSSFColor.BLACK.index);
        break;
    case RichTextStyle.COLOR_YELLOW:
        font.setColor(HSSFColor.YELLOW.index);
        break;
    default:
        break;
    }

    cellStyle.setFont(font);

    switch (column.getAlignment()) {
    case ViewColumn.ALIGN_CENTER:
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        break;
    case ViewColumn.ALIGN_LEFT:
        cellStyle.setAlignment(CellStyle.ALIGN_LEFT);
        break;
    case ViewColumn.ALIGN_RIGHT:
        cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
        break;
    default:
        break;
    }

    @SuppressWarnings("unchecked")
    Vector<Object> values = entry.getColumnValues();
    Object value = values.get(position);
    String name = value.getClass().getSimpleName();
    short format = 0;
    if (name.contains("Double")) { //$NON-NLS-1$
        XSSFDataFormat fmt = (XSSFDataFormat) workbook.createDataFormat();
        switch (column.getNumberFormat()) {
        case ViewColumn.FMT_CURRENCY:
            format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(6));
            break;
        case ViewColumn.FMT_FIXED:
            String zero = "0"; //$NON-NLS-1$
            String fixedFormat = "#0"; //$NON-NLS-1$
            int digits = column.getNumberDigits();
            if (digits > 0) {
                String n = StringUtils.repeat(zero, digits);
                fixedFormat = fixedFormat + "." + n;
            }
            format = fmt.getFormat(fixedFormat);
            break;
        default:
            format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(1));
            break;
        }
    } else if (name.contains("DateTime")) { //$NON-NLS-1$                     
        XSSFDataFormat fmt = (XSSFDataFormat) workbook.createDataFormat();
        switch (column.getTimeDateFmt()) {
        case ViewColumn.FMT_DATE:
            format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0xe));
            break;
        case ViewColumn.FMT_DATETIME:
            format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0x16));
            break;
        case ViewColumn.FMT_TIME:
            format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0x15));
            break;
        default:
            format = fmt.getFormat(BuiltinFormats.getBuiltinFormat(0xe));
            break;
        }
    }
    cellStyle.setDataFormat(format);
    styles.add(cellStyle);
}

From source file:hu.webhejj.commons.io.table.excel.ExcelTableAppender.java

License:Apache License

public ExcelTableAppender(Sheet sheet) {
    this.sheet = sheet;
    rowIndex = 0; // sheet.getPhysicalNumberOfRows();
    columnIndex = 0;//from www .j ava2s  . co m

    textCellStyle = sheet.getWorkbook().createCellStyle();
    textCellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text"));
}

From source file:it.eng.spagobi.engines.qbe.exporter.QbeXLSXExporter.java

License:Mozilla Public License

protected short getBuiltinFormat(String formatStr) {
    short format = (short) BuiltinFormats.getBuiltinFormat(formatStr);
    return format;
}