Example usage for org.apache.poi.ss.usermodel DataFormat getFormat

Introduction

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

Prototype

String getFormat(short index);

Source Link

Document

get the format string that matches the given format index

Usage

From source file:org.dbunit.dataset.excel.XlsDataSetWriter.java

License:Open Source License

protected static CellStyle createDateCellStyle(Workbook workbook) {
    DataFormat format = workbook.createDataFormat();
    short dateFormatCode = format.getFormat(DATE_FORMAT_AS_NUMBER_DBUNIT);
    return getCellStyle(workbook, dateFormatCode);
}

---

From source file:org.dbunit.dataset.excel.XlsDataSetWriter.java

License:Open Source License

protected void setNumericCell(Cell cell, BigDecimal value, Workbook workbook) {
    if (logger.isDebugEnabled())
        logger.debug("setNumericCell(cell={}, value={}, workbook={}) - start",
                new Object[] { cell, value, workbook });

    cell.setCellValue(((BigDecimal) value).doubleValue());

    DataFormat df = workbook.createDataFormat();
    int scale = ((BigDecimal) value).scale();
    short format;
    if (scale <= 0) {
        format = df.getFormat("####");
    } else {/*from w  w w.  ja va  2 s. c om*/
        String zeros = createZeros(((BigDecimal) value).scale());
        format = df.getFormat("####." + zeros);
    }
    if (logger.isDebugEnabled())
        logger.debug("Using format '{}' for value '{}'.", String.valueOf(format), value);

    CellStyle cellStyleNumber = getCellStyle(workbook, format);
    cell.setCellStyle(cellStyleNumber);
}

---

From source file:org.drugepi.table.ExcelUtils.java

License:Mozilla Public License

public static void formatNumericCell(Cell cell, String s, String[] excelFormats) {
    CellStyle origStyle = cell.getCellStyle();
    CellStyle newStyle = cell.getRow().getSheet().getWorkbook().createCellStyle();
    DataFormat newFormat = cell.getRow().getSheet().getWorkbook().createDataFormat();
    newStyle.cloneStyleFrom(origStyle);//from   w w w.j a  v a2  s.c  o m

    newStyle.setAlignment(CellStyle.ALIGN_LEFT);

    int numDecimals = -1;
    if (s != null) {
        int decimalIndex = s.indexOf(".");
        if (decimalIndex >= 0)
            numDecimals = s.length() - decimalIndex - 1;
    }

    if (numDecimals < 0)
        numDecimals = 0;

    if ((numDecimals >= 0) && (numDecimals <= 8))
        newStyle.setDataFormat(newFormat.getFormat(excelFormats[numDecimals]));

    cell.setCellStyle(newStyle);
}

---

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

License:Open Source License

private void prepareNumberCellStyles(Workbook workbook, Set<String> retrievedFormats,
        Map<String, CellStyle> keyStyleMap) {
    final DataFormat dataFormat = workbook.createDataFormat();
    for (final String format : retrievedFormats) {
        final CellStyle cellStyleNumberFormat = workbook.createCellStyle();
        cellStyleNumberFormat.setDataFormat(dataFormat.getFormat(format));
        keyStyleMap.put(format, cellStyleNumberFormat);
    }//from w  ww .j  a v a 2  s  .co m
}

---

From source file:org.geoserver.wfs.response.ExcelCellStyles.java

License:Open Source License

public ExcelCellStyles(Workbook wb) {
    CreationHelper helper = wb.getCreationHelper();
    DataFormat fmt = helper.createDataFormat();

    dateStyle = wb.createCellStyle();/*  w ww . ja v a 2s  .co  m*/
    dateStyle.setDataFormat(fmt.getFormat("yyyy-mm-dd hh:mm:ss"));

    headerStyle = wb.createCellStyle();
    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);

    warningStyle = wb.createCellStyle();
    Font warningFont = wb.createFont();
    warningFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    warningFont.setColor(Font.COLOR_RED);
    warningStyle.setFont(warningFont);
}

---

From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java

License:Apache License

@SuppressWarnings("unused")
private static void writeCell(Cell cell, Object val, boolean userTemplate,
        ExcelWriteFieldMappingAttribute attribute, Object bean) {
    if (attribute != null && attribute.getLinkField() != null) {
        String addressFieldName = attribute.getLinkField();
        String address = null;/*www  . j ava  2 s  .  c om*/
        if (bean != null) {
            address = (String) getFieldValue(bean, addressFieldName, true);
        }
        Workbook wb = cell.getRow().getSheet().getWorkbook();

        Hyperlink link = wb.getCreationHelper().createHyperlink(attribute.getLinkType());
        link.setAddress(address);
        cell.setHyperlink(link);
        // Its style can't inherit from cell.
        CellStyle style = wb.createCellStyle();
        Font hlinkFont = wb.createFont();
        hlinkFont.setUnderline(Font.U_SINGLE);
        hlinkFont.setColor(IndexedColors.BLUE.getIndex());
        style.setFont(hlinkFont);
        if (cell.getCellStyle() != null) {
            style.setFillBackgroundColor(cell.getCellStyle().getFillBackgroundColor());
        }
        cell.setCellStyle(style);
    }
    if (val == null) {
        cell.setCellValue((String) null);
        return;
    }
    Class<?> clazz = val.getClass();
    if (val instanceof Byte) {// Double
        Byte temp = (Byte) val;
        cell.setCellValue((double) temp.byteValue());
    } else if (val instanceof Short) {
        Short temp = (Short) val;
        cell.setCellValue((double) temp.shortValue());
    } else if (val instanceof Integer) {
        Integer temp = (Integer) val;
        cell.setCellValue((double) temp.intValue());
    } else if (val instanceof Long) {
        Long temp = (Long) val;
        cell.setCellValue((double) temp.longValue());
    } else if (val instanceof Float) {
        Float temp = (Float) val;
        cell.setCellValue((double) temp.floatValue());
    } else if (val instanceof Double) {
        Double temp = (Double) val;
        cell.setCellValue((double) temp.doubleValue());
    } else if (val instanceof Date) {// Date
        Date dateVal = (Date) val;
        long time = dateVal.getTime();
        // read is based on 1899/12/31 but DateUtil.getExcelDate is base on
        // 1900/01/01
        if (time >= TIME_1899_12_31_00_00_00_000 && time < TIME_1900_01_01_00_00_00_000) {
            Date incOneDay = new Date(time + 24 * 60 * 60 * 1000);
            double d = DateUtil.getExcelDate(incOneDay);
            cell.setCellValue(d - 1);
        } else {
            cell.setCellValue(dateVal);
        }

        if (!userTemplate) {
            Workbook wb = cell.getRow().getSheet().getWorkbook();
            CellStyle cellStyle = cell.getCellStyle();
            if (cellStyle == null) {
                cellStyle = wb.createCellStyle();
            }
            DataFormat dataFormat = wb.getCreationHelper().createDataFormat();
            // @see #BuiltinFormats
            // 0xe, "m/d/yy"
            // 0x14 "h:mm"
            // 0x16 "m/d/yy h:mm"
            // {@linke https://en.wikipedia.org/wiki/Year_10,000_problem}
            /** [1899/12/31 00:00:00:000~1900/01/01 00:00:000) */
            if (time >= TIME_1899_12_31_00_00_00_000 && time < TIME_1900_01_02_00_00_00_000) {
                cellStyle.setDataFormat(dataFormat.getFormat("h:mm"));
                // cellStyle.setDataFormat(dataFormat.getFormat("m/d/yy h:mm"));
            } else {
                // if ( time % (24 * 60 * 60 * 1000) == 0) {//for time
                // zone,we can't use this way.
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(dateVal);
                int hour = calendar.get(Calendar.HOUR_OF_DAY);
                int minute = calendar.get(Calendar.MINUTE);
                int second = calendar.get(Calendar.SECOND);
                int millisecond = calendar.get(Calendar.MILLISECOND);
                if (millisecond == 0 && second == 0 && minute == 0 && hour == 0) {
                    cellStyle.setDataFormat(dataFormat.getFormat("m/d/yy"));
                } else {
                    cellStyle.setDataFormat(dataFormat.getFormat("m/d/yy h:mm"));
                }
            }
            cell.setCellStyle(cellStyle);
        }
    } else if (val instanceof Boolean) {// Boolean
        cell.setCellValue(((Boolean) val).booleanValue());
    } else {// String
        cell.setCellValue((String) val.toString());
    }
}

---

From source file:org.joeffice.spreadsheet.actions.FormatAction.java

License:Apache License

@Override
public void actionPerformed(ActionEvent ae) {
    SpreadsheetTopComponent currentTopComponent = OfficeTopComponent
            .getSelectedComponent(SpreadsheetTopComponent.class);
    if (currentTopComponent != null) {
        JTable currentTable = currentTopComponent.getSelectedTable();
        SheetTableModel tableModel = (SheetTableModel) currentTable.getModel();
        List<Cell> selectedCells = CellUtils.getSelectedCells(currentTable);
        if (selectedCells.isEmpty()) {
            return;
        }/*from w  ww.j av  a  2s  .  c o  m*/
        if (choosePattern) {
            pattern = askFromList();
        } else if (definePattern) {
            pattern = askFromInputField();
        }
        if (pattern == null) {
            return;
        }
        Workbook workbook = selectedCells.get(0).getSheet().getWorkbook();
        DataFormat format = workbook.createDataFormat();
        short formatIndex = format.getFormat(pattern);
        for (Cell cell : selectedCells) {
            cell.getCellStyle().setDataFormat(formatIndex);
            tableModel.fireTableCellUpdated(cell.getRowIndex(), cell.getColumnIndex());
        }
    }
}

---

From source file:org.opentestsystem.delivery.testreg.rest.ExcelBasedTemplateCreator.java

License:Open Source License

private CellStyle getTextCellSytle(Workbook workbook) {
    DataFormat dataFormat = workbook.createDataFormat();
    CellStyle textStyle = workbook.createCellStyle();
    textStyle.setDataFormat(dataFormat.getFormat("@")); //@ is equivalent to Text
    textStyle.setHidden(false);//from ww  w  .jav  a  2 s.co  m
    return textStyle;
}

---

From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.java

License:Apache License

/**
 * Set specified cell format//from w w w . j av a 2 s . co  m
 *
 * @param excelFieldFormat the specified format
 * @param cell             the cell to set up format
 */
private void setDataFormat(String excelFieldFormat, Cell cell) {
    if (log.isDebug()) {
        logDebug(BaseMessages.getString(PKG, "ExcelWriterStep.Log.SetDataFormat", excelFieldFormat,
                CellReference.convertNumToColString(cell.getColumnIndex()), cell.getRowIndex()));
    }

    DataFormat format = data.wb.createDataFormat();
    short formatIndex = format.getFormat(excelFieldFormat);
    CellStyle style = data.wb.createCellStyle();
    style.cloneStyleFrom(cell.getCellStyle());
    style.setDataFormat(formatIndex);
    cell.setCellStyle(style);
}

---

From source file:org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep_StyleFormatTest.java

License:Apache License

/**
 * Test applying Format and Style from cell (from a template) when writing fields
 *
 * @param fileType/*  ww w .j av  a2s .  c om*/
 * @throws Exception
 */
private void testStyleFormat(String fileType) throws Exception {
    setupStepMock(fileType);
    createStepMeta(fileType);
    createStepData(fileType);
    step.init(stepMeta, stepData);

    // We do not run transformation or executing the whole step
    // instead we just execute ExcelWriterStepData.writeNextLine() to write to Excel workbook object
    // Values are written in A2:D2 and A3:D3 rows
    List<Object[]> rows = createRowData();
    for (int i = 0; i < rows.size(); i++) {
        step.writeNextLine(rows.get(i));
    }

    // Custom styles are loaded from G1 cell
    Row xlsRow = stepData.sheet.getRow(0);
    Cell baseCell = xlsRow.getCell(6);
    CellStyle baseCellStyle = baseCell.getCellStyle();
    DataFormat format = stepData.wb.createDataFormat();

    // Check style of the exported values in A3:D3
    xlsRow = stepData.sheet.getRow(2);
    for (int i = 0; i < stepData.inputRowMeta.size(); i++) {
        Cell cell = xlsRow.getCell(i);
        CellStyle cellStyle = cell.getCellStyle();

        if (i > 0) {
            assertEquals(cellStyle.getBorderRight(), baseCellStyle.getBorderRight());
            assertEquals(cellStyle.getFillPattern(), baseCellStyle.getFillPattern());
        } else {
            // cell A2/A3 has no custom style
            assertFalse(cellStyle.getBorderRight() == baseCellStyle.getBorderRight());
            assertFalse(cellStyle.getFillPattern() == baseCellStyle.getFillPattern());
        }

        if (i != 1) {
            assertEquals(format.getFormat(cellStyle.getDataFormat()), "0.00000");
        } else {
            // cell B2/B3 use different format from the custom style
            assertEquals(format.getFormat(cellStyle.getDataFormat()), "##0,000.0");
        }
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• »