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:net.geoprism.data.etl.excel.XSSFSheetXMLHandler.java

License:Open Source License

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

    if (isTextTag(name)) {
        vIsOpen = true;// ww w.  j  a  v a  2s  . c  om
        // 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 == ColumnType.NUMBER) {
            nextDataType = ColumnType.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 = ColumnType.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 = ColumnType.BOOLEAN;
        else if ("e".equals(cellType))
            nextDataType = ColumnType.ERROR;
        else if ("inlineStr".equals(cellType))
            nextDataType = ColumnType.INLINE_STRING;
        else if ("s".equals(cellType))
            nextDataType = ColumnType.TEXT;
        else if ("str".equals(cellType))
            nextDataType = ColumnType.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:org.aio.handy.poi.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  w w  w  . j a v  a2  s . c om*/
 */
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:org.apache.metamodel.excel.XlsxSheetToRowsHandler.java

License:Apache License

@Override
public void startElement(String uri, String localName, String qName, Attributes attributes)
        throws SAXException {
    if ("row".equals(qName)) {
        // element is a row

        // excel row numbers are 1-based
        int rowNumber = Integer.parseInt(attributes.getValue("r"));
        rowNumber = rowNumber - 1;//from w  w  w . j ava2  s  .  c  o  m

        if (_configuration.isSkipEmptyLines()) {
            _rowNumber++;
        } else {
            while (_rowNumber + 1 < rowNumber) {
                // empty lines are not skipped, so dispatch empty lines
                _rowNumber++;
                List<String> emptyValues = Collections.emptyList();
                List<Style> emptyStyles = Collections.emptyList();
                _callback.row(_rowNumber, emptyValues, emptyStyles);
            }
            _rowNumber = rowNumber;
        }
    } else if ("c".equals(qName)) {
        // element is a cell

        _inCell = true;

        final 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;
            }
        }
        _columnNumber = nameToColumn(r.substring(0, firstDigit));

        // Set up defaults.
        _dataType = XssfDataType.NUMBER;
        _formatIndex = -1;
        _formatString = null;

        final String cellType = attributes.getValue("t");
        if ("b".equals(cellType)) {
            _dataType = XssfDataType.BOOL;
        } else if ("e".equals(cellType)) {
            _dataType = XssfDataType.ERROR;
        } else if ("inlineStr".equals(cellType)) {
            _dataType = XssfDataType.INLINESTR;
        } else if ("s".equals(cellType)) {
            _dataType = XssfDataType.SSTINDEX;
        } else if ("str".equals(cellType)) {
            _dataType = XssfDataType.FORMULA;
        }

        String cellStyleStr = attributes.getValue("s");
        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);

            configureStyle(style);

            if (_dataType == XssfDataType.NUMBER) {
                this._formatIndex = style.getDataFormat();
                this._formatString = style.getDataFormatString();
                if (this._formatString == null) {
                    this._formatString = BuiltinFormats.getBuiltinFormat(this._formatIndex);
                }
            }
        }
    } else if (_inCell && "f".equals(qName)) {
        // skip the actual formula line
        _inFormula = true;
    }
}

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;/*from  www.jav  a 2 s. c om*/

    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(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;/*  www.ja v  a  2s  .c  o  m*/

    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 12);
    titleFont.setBold(true);
    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.emfforms.internal.spreadsheet.core.converter.EMFFormsSpreadsheetMultiAttributeConverter_Test.java

License:Open Source License

@Before
public void before() {
    converter = new EMFFormsSpreadsheetMultiAttributeConverter();
    reportService = mock(ReportService.class);
    databinding = mock(EMFFormsDatabindingEMF.class);
    domainObject = mock(EObject.class);
    dmr = mock(VDomainModelReference.class);

    final Workbook wb = new HSSFWorkbook();
    final CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text")); //$NON-NLS-1$

    final Sheet sheet = wb.createSheet("test"); //$NON-NLS-1$

    // Create a row and put some cells in it. Rows are 0 based.
    final Row row = sheet.createRow((short) 0);
    cell = row.createCell(0);//w  w  w  .  jav a  2  s.c o m

    viewModelContext = mock(ViewModelContext.class);
    when(viewModelContext.getContextValue(EMFFormsCellStyleConstants.TEXT)).thenReturn(cellStyle);
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.converter.EMFFormsSpreadsheetMultiReferenceConverter_Test.java

License:Open Source License

@Before
public void before() {
    converter = new EMFFormsSpreadsheetMultiReferenceConverter();
    reportService = mock(ReportService.class);
    databinding = mock(EMFFormsDatabindingEMF.class);
    domainObject = mock(EObject.class);
    dmr = mock(VDomainModelReference.class);

    final Workbook wb = new HSSFWorkbook();
    final CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text")); //$NON-NLS-1$

    final Sheet sheet = wb.createSheet("test"); //$NON-NLS-1$

    // Create a row and put some cells in it. Rows are 0 based.
    final Row row = sheet.createRow((short) 0);
    cell = row.createCell(0);/*from  w w  w.  ja v  a 2  s  .  c  o m*/

    viewModelContext = mock(ViewModelContext.class);
    when(viewModelContext.getContextValue(EMFFormsCellStyleConstants.TEXT)).thenReturn(cellStyle);
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.converter.EMFFormsSpreadsheetSingleAttributeConverter_Test.java

License:Open Source License

@Before
public void setup() {
    final Workbook wb = new HSSFWorkbook();
    cellStyle = wb.createCellStyle();/*ww w.j a v a2  s .  c  o m*/
    cellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text")); //$NON-NLS-1$

    cellStyle2 = wb.createCellStyle();
    cellStyle2.setDataFormat((short) BuiltinFormats.getBuiltinFormat("m/d/yy")); //$NON-NLS-1$

    final Sheet sheet = wb.createSheet("test"); //$NON-NLS-1$

    // Create a row and put some cells in it. Rows are 0 based.
    final Row row = sheet.createRow((short) 0);
    cell = row.createCell(0);

    viewModelContext = mock(ViewModelContext.class);
    when(viewModelContext.getContextValue(EMFFormsCellStyleConstants.TEXT)).thenReturn(cellStyle);
    when(viewModelContext.getContextValue(EMFFormsCellStyleConstants.DATE)).thenReturn(cellStyle2);

    converter = new EMFFormsSpreadsheetSingleAttributeConverter();
    reportService = mock(ReportService.class);
    databinding = mock(EMFFormsDatabindingEMF.class);
    domainObject = mock(EObject.class);
    dmr = mock(VDomainModelReference.class);
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.converter.EMFFormsSpreadsheetSingleReferenceConverter_Test.java

License:Open Source License

@Before
public void before() {
    converter = new EMFFormsSpreadsheetSingleReferenceConverter();
    reportService = mock(ReportService.class);
    databinding = mock(EMFFormsDatabindingEMF.class);
    domainObject = mock(EObject.class);
    dmr = mock(VDomainModelReference.class);

    final Workbook wb = new HSSFWorkbook();
    final CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text")); //$NON-NLS-1$

    final Sheet sheet = wb.createSheet("test"); //$NON-NLS-1$

    // Create a row and put some cells in it. Rows are 0 based.
    final Row row = sheet.createRow((short) 0);
    cell = row.createCell(0);//from   w  ww  .j av  a 2 s . c o  m

    viewModelContext = mock(ViewModelContext.class);
    when(viewModelContext.getContextValue(EMFFormsCellStyleConstants.TEXT)).thenReturn(cellStyle);
}

From source file:org.eclipse.emfforms.internal.spreadsheet.core.transfer.EMFFormsSpreadsheetExporterImpl.java

License:Open Source License

private void addDefaultCellStyles(Workbook workbook, Map<String, CellStyle> keyStyleMap) {
    final CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setLocked(true);//from ww  w. ja va  2  s . co m
    keyStyleMap.put(EMFFormsCellStyleConstants.LOCKED, cellStyle);
    final CellStyle cellStyle2 = workbook.createCellStyle();
    cellStyle2.setLocked(true);
    cellStyle2.setWrapText(true);
    keyStyleMap.put(EMFFormsCellStyleConstants.LOCKED_AND_WRAPPED, cellStyle2);
    final CellStyle cellStyle3 = workbook.createCellStyle();
    cellStyle3.setDataFormat((short) BuiltinFormats.getBuiltinFormat("text")); //$NON-NLS-1$
    keyStyleMap.put(EMFFormsCellStyleConstants.TEXT, cellStyle3);
    final CellStyle cellStyle4 = workbook.createCellStyle();
    cellStyle4.setDataFormat((short) BuiltinFormats.getBuiltinFormat("m/d/yy")); //$NON-NLS-1$
    keyStyleMap.put(EMFFormsCellStyleConstants.DATE, cellStyle4);
}