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:org.jboss.dashboard.displayer.table.ExportTool.java

License:Apache License

private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;/*from   w  w w.j a v  a2s .  co m*/

    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 12);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    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.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(LocaleManager.currentLocale(), dateFormatPattern)));
    styles.put("date_cell", style);
    return styles;
}

From source file:org.talend.dataprep.schema.xls.streaming.StreamingSheetReader.java

License:Open Source License

/**
 * Read the numeric format string out of the styles table for this cell. Stores the result in the Cell.
 *
 * @param startElement// w w w . j  a  v a 2s. co  m
 * @param cell
 */
void setFormatString(StartElement startElement, StreamingCell cell) {
    Attribute cellStyle = startElement.getAttributeByName(new QName("s"));
    String cellStyleString = (cellStyle != null) ? cellStyle.getValue() : null;
    XSSFCellStyle style = null;

    if (cellStyleString != null) {
        style = stylesTable.getStyleAt(Integer.parseInt(cellStyleString));
    } else if (stylesTable.getNumCellStyles() > 0) {
        style = stylesTable.getStyleAt(0);
    }

    if (style != null) {
        cell.setNumericFormatIndex(style.getDataFormat());
        String formatString = style.getDataFormatString();

        if (formatString != null) {
            cell.setNumericFormat(formatString);
        } else {
            cell.setNumericFormat(BuiltinFormats.getBuiltinFormat(cell.getNumericFormatIndex()));
        }
    } else {
        cell.setNumericFormatIndex(null);
        cell.setNumericFormat(null);
    }
}

From source file:service.XSSFSheetHandler.java

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

    if ("inlineStr".equals(name) || "v".equals(name)) {
        vIsOpen = true;/*from ww w.j  av a  2 s . co m*/
        // Clear contents cache
        value.setLength(0);
    }
    // c => cell
    else if ("c".equals(name)) {
        // Get the cell reference
        String r = attributes.getValue("r");
        int firstDigit = -1;
        for (int c = 0; c < r.length(); ++c) {
            if (Character.isDigit(r.charAt(c))) {
                firstDigit = c;
                break;
            }
        }
        thisColumn = nameToColumn(r.substring(0, firstDigit));

        // Set up defaults.
        this.nextDataType = ReadExcelFile.xssfDataType.NUMBER;
        this.formatIndex = -1;
        this.formatString = null;
        String cellType = attributes.getValue("t");
        String cellStyleStr = attributes.getValue("s");
        if ("b".equals(cellType))
            nextDataType = ReadExcelFile.xssfDataType.BOOL;
        else if ("e".equals(cellType))
            nextDataType = ReadExcelFile.xssfDataType.ERROR;
        else if ("inlineStr".equals(cellType))
            nextDataType = ReadExcelFile.xssfDataType.INLINESTR;
        else if ("s".equals(cellType))
            nextDataType = ReadExcelFile.xssfDataType.SSTINDEX;
        else if ("str".equals(cellType))
            nextDataType = ReadExcelFile.xssfDataType.FORMULA;
        else if (cellStyleStr != null) {
            // It's a number, but almost certainly one
            // 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:uk.gov.ofwat.RefTest.java

License:Open Source License

public void writeXLS() throws IOException {
    XSSFWorkbook wb = new XSSFWorkbook();
    CreationHelper creationHelper = wb.getCreationHelper();
    // create a new sheet
    Sheet s = wb.createSheet();//from  w ww  . j  a  v a  2  s  .c o  m
    // declare a row object reference
    Row r = null;
    // declare a cell object reference
    Cell c = null;
    // create 2 cell styles
    XSSFCellStyle cs = wb.createCellStyle();

    XSSFCellStyle 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((short) BuiltinFormats.getBuiltinFormat("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(creationHelper.createRichTextString("Hello! " + cellnum));
        }
    }

    File file = new File("d:\\out.xls");
    FileOutputStream fos = new FileOutputStream(file);
    wb.write(fos);
    //      fos.write(wb.getBytes());
    //      fos.flush();
    //      fos.close();

}

From source file:utilities.SmapSheetXMLHandler.java

License:Apache License

@Override
@SuppressWarnings("unused")
public void startElement(String uri, String localName, String qName, Attributes attributes)
        throws SAXException {

    if (uri != null && !uri.equals(NS_SPREADSHEETML)) {
        return;/*from  www .jav a2s . c o  m*/
    }

    if (isTextTag(localName)) {
        vIsOpen = true;
        // Clear contents cache
        value.setLength(0);
    } else if ("is".equals(localName)) {
        // Inline string outer tag
        isIsOpen = true;
    } else if ("f".equals(localName)) {
        // 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) {
                    logger.log(POILogger.WARN, "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(localName) || "evenHeader".equals(localName)
            || "firstHeader".equals(localName) || "firstFooter".equals(localName)
            || "oddFooter".equals(localName) || "evenFooter".equals(localName)) {
        hfIsOpen = true;
        // Clear contents cache
        headerFooter.setLength(0);
    } else if ("row".equals(localName)) {
        String rowNumStr = attributes.getValue("r");
        if (rowNumStr != null) {
            rowNum = Integer.parseInt(rowNumStr) - 1;
        } else {
            rowNum = nextRowNum;
        }
        output.startRow(rowNum);
    }
    // c => cell
    else if ("c".equals(localName)) {
        // 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 {
            // Number, but almost certainly with a special style or format
            style = null;
            if (stylesTable != null) {
                if (cellStyleStr != null) {
                    int styleIndex = Integer.parseInt(cellStyleStr);
                    style = stylesTable.getStyleAt(styleIndex);
                } else if (stylesTable.getNumCellStyles() > 0) {
                    style = stylesTable.getStyleAt(0);
                }
            }
            if (style != null) {
                this.formatIndex = style.getDataFormat();
                this.formatString = style.getDataFormatString();
                if (this.formatString == null)
                    this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
            }
        }
    }
}