Overview | Package | Class | Use | Tree | Deprecated | Index | Help
PREV CLASS NEXT CLASS | FRAMES NO FRAMES
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD

## org.apache.poi.ss.usermodel
Class DataFormatter

    java.lang.Object
      org.apache.poi.ss.usermodel.DataFormatter

All Implemented Interfaces:
  java.util.Observer

Direct Known Subclasses:
  HSSFDataFormatter

    public class DataFormatter

extends java.lang.Object
implements java.util.Observer

DataFormatter contains methods for formatting the value stored in an Cell. This can be useful for reports and GUI presentations when you need to display data exactly as it appears in Excel. Supported formats include currency, SSN, percentages, decimals, dates, phone numbers, zip codes, etc.

Internally, formats will be implemented using subclasses of `Format` such as `DecimalFormat` and `SimpleDateFormat`. Therefore the formats used by this class must obey the same pattern rules as these Format subclasses. This means that only legal number pattern characters ("0", "#", ".", "," etc.) may appear in number formats. Other characters can be inserted before or after the number pattern to form a prefix or suffix.

For example the Excel pattern `"$#,##0.00 "USD"_);($#,##0.00 "USD")"` will be correctly formatted as "$1,000.00 USD" or "($1,000.00 USD)". However the pattern `"00-00-00"` is incorrectly formatted by DecimalFormat as "000000--". For Excel formats that are not compatible with DecimalFormat, you can provide your own custom `Format` implementation via `DataFormatter.addFormat(String,Format)`. The following custom formats are already provided by this class:

     SSN "000-00-0000"
         Phone Number "(###) ###-####"
         Zip plus 4 "00000-0000"

If the Excel format pattern cannot be parsed successfully, then a default format will be used. The default number format will mimic the Excel General format: "#" for whole numbers and "#.##########" for decimal numbers. You can override the default format pattern with `DataFormatter.setDefaultNumberFormat(Format)`. Note: the default format will only be used when a Format cannot be created from the cell's data format string.

Note that by default formatted numeric values are trimmed. Excel formats can contain spacers and padding and the default behavior is to strip them off.

Example:

Consider a numeric cell with a value `12.343` and format `"##.##_ "`. The trailing underscore and space ("_ ") in the format adds a space to the end and Excel formats this cell as `"12.34 "`, but `DataFormatter` trims the formatted value and returns `"12.34"`.

You can enable spaces by passing the `emulateCSV=true` flag in the `DateFormatter` cosntructor. If set to true, then the output tries to conform to what you get when you take an xls or xlsx in Excel and Save As CSV file:

- returned values are not trimmed
- Invalid dates are formatted as 255 pound signs ("#")
- simulate Excel's handling of a format string of all # when the value is 0. Excel will output "", `DataFormatter` will output "0".

Some formats are automatically "localized" by Excel, eg show as mm/dd/yyyy when loaded in Excel in some Locales but as dd/mm/yyyy in others. These are always returned in the "default" (US) format, as stored in the file. Some format strings request an alternate locale, eg `[$-809]d/m/yy h:mm AM/PM` which explicitly requests UK locale. These locale directives are (currently) ignored. You can use `DateFormatConverter` to do some of this localisation if you need it.

Constructor Summary
`DataFormatter()` Creates a formatter using the `default locale`.
`DataFormatter(boolean emulateCSV)` Creates a formatter using the `default locale`.
`DataFormatter(java.util.Locale locale)` Creates a formatter using the given locale.
`DataFormatter(java.util.Locale locale, boolean emulateCSV)` Creates a formatter using the given locale.

Method Summary
`void` | `addFormat(java.lang.String excelFormatStr, java.text.Format format)` Adds a new format to the available formats.
`java.text.Format` | `createFormat(Cell cell)` Create and return a Format based on the format string from a cell's style.
`java.lang.String` | `formatCellValue(Cell cell)` Returns the formatted value of a cell as a `String` regardless of the cell type.
`java.lang.String` | `formatCellValue(Cell cell, FormulaEvaluator evaluator)` Returns the formatted value of a cell as a `String` regardless of the cell type.
`java.lang.String` | `formatCellValue(Cell cell, FormulaEvaluator evaluator, ConditionalFormattingEvaluator cfEvaluator)` Returns the formatted value of a cell as a `String` regardless of the cell type.
`java.lang.String` | `formatRawCellContents(double value, int formatIndex, java.lang.String formatString)` Formats the given raw cell value, based on the supplied format index and string, according to excel style rules.
`java.lang.String` | `formatRawCellContents(double value, int formatIndex, java.lang.String formatString, boolean use1904Windowing)` Formats the given raw cell value, based on the supplied format index and string, according to excel style rules.
`java.text.Format` | `getDefaultFormat(Cell cell)` Returns a default format for a cell.
`java.util.Observable` | `getLocaleChangedObservable()` If the Locale has been changed via `LocaleUtil.setUserLocale(Locale)` the stored formats need to be refreshed.
`void` | `setDefaultNumberFormat(java.text.Format format)` Sets a default number format to be used when the Excel format cannot be parsed successfully.
`static void` | `setExcelStyleRoundingMode(java.text.DecimalFormat format)` Enables excel style rounding mode (round half up) on the Decimal Format given.
`static void` | `setExcelStyleRoundingMode(java.text.DecimalFormat format, java.math.RoundingMode roundingMode)` Enables custom rounding mode on the given Decimal Format.
`void` | `update(java.util.Observable observable, java.lang.Object localeObj)` Update formats when locale has been changed

Methods inherited from class java.lang.Object
`clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait`

Constructor Detail

### DataFormatter

    public DataFormatter()

  Creates a formatter using the `default locale`.

### DataFormatter

    public DataFormatter(boolean emulateCSV)

  Creates a formatter using the `default locale`.

  Parameters:
    `emulateCSV` - whether to emulate CSV output.

### DataFormatter

    public DataFormatter(java.util.Locale locale)

  Creates a formatter using the given locale.

### DataFormatter

    public DataFormatter(java.util.Locale locale,
                         boolean emulateCSV)

  Creates a formatter using the given locale.

  Parameters:
    `emulateCSV` - whether to emulate CSV output.

Method Detail

### createFormat

    public java.text.Format createFormat(Cell cell)

  Create and return a Format based on the format string from a cell's style. If the pattern cannot be parsed, return a default pattern.

  Parameters:
    `cell` - The Excel cell
  Returns:
    A Format representing the excel format. May return null.

### getDefaultFormat

    public java.text.Format getDefaultFormat(Cell cell)

  Returns a default format for a cell.

  Parameters:
    `cell` - The cell
  Returns:
    a default format

### formatRawCellContents

    public java.lang.String formatRawCellContents(double value,
                                                  int formatIndex,
                                                  java.lang.String formatString)

  Formats the given raw cell value, based on the supplied format index and string, according to excel style rules.

  See Also:
    `formatCellValue(Cell)`

### formatRawCellContents

    public java.lang.String formatRawCellContents(double value,
                                                  int formatIndex,
                                                  java.lang.String formatString,
                                                  boolean use1904Windowing)

  Formats the given raw cell value, based on the supplied format index and string, according to excel style rules.

  See Also:
    `formatCellValue(Cell)`

### formatCellValue

    public java.lang.String formatCellValue(Cell cell)

  Returns the formatted value of a cell as a `String` regardless of the cell type. If the Excel format pattern cannot be parsed then the cell value will be formatted using a default format.

  When passed a null or blank cell, this method will return an empty String (""). Formulas in formula type cells will not be evaluated.

  Parameters:
    `cell` - The cell
  Returns:
    the formatted cell value as a String

### formatCellValue

    public java.lang.String formatCellValue(Cell cell,
                                            FormulaEvaluator evaluator)

  Returns the formatted value of a cell as a `String` regardless of the cell type. If the Excel number format pattern cannot be parsed then the cell value will be formatted using a default format.

  When passed a null or blank cell, this method will return an empty String (""). Formula cells will be evaluated using the given `FormulaEvaluator` if the evaluator is non-null. If the evaluator is null, then the formula String will be returned. The caller is responsible for setting the currentRow on the evaluator

  Parameters:
    `cell` - The cell (can be null)
    `evaluator` - The FormulaEvaluator (can be null)
  Returns:
    a string value of the cell

### formatCellValue

    public java.lang.String formatCellValue(Cell cell,
                                            FormulaEvaluator evaluator,
                                            ConditionalFormattingEvaluator cfEvaluator)

  Returns the formatted value of a cell as a `String` regardless of the cell type. If the Excel number format pattern cannot be parsed then the cell value will be formatted using a default format.

  When passed a null or blank cell, this method will return an empty String (""). Formula cells will be evaluated using the given `FormulaEvaluator` if the evaluator is non-null. If the evaluator is null, then the formula String will be returned. The caller is responsible for setting the currentRow on the evaluator

  When a ConditionalFormattingEvaluator is present, it is checked first to see if there is a number format to apply. If multiple rules apply, the last one is used. If no ConditionalFormattingEvaluator is present, no rules apply, or the applied rules do not define a format, the cell's style format is used.

  The two evaluators should be from the same context, to avoid inconsistencies in cached values.

  Parameters:
    `cell` - The cell (can be null)
    `evaluator` - The FormulaEvaluator (can be null)
    `cfEvaluator` - ConditionalFormattingEvaluator (can be null)
  Returns:
    a string value of the cell

### setDefaultNumberFormat

    public void setDefaultNumberFormat(java.text.Format format)

  Sets a default number format to be used when the Excel format cannot be parsed successfully. Note: This is a fall back for when an error occurs while parsing an Excel number format pattern. This will not affect cells with the General format.

  The value that will be passed to the Format's format method (specified by `java.text.Format#format`) will be a double value from a numeric cell. Therefore the code in the format method should expect a `Number` value.

  Parameters:
    `format` - A Format instance to be used as a default
  See Also:
    `Format.format(java.lang.Object)`

### addFormat

    public void addFormat(java.lang.String excelFormatStr,
                          java.text.Format format)

  Adds a new format to the available formats.

  The value that will be passed to the Format's format method (specified by `java.text.Format#format`) will be a double value from a numeric cell. Therefore the code in the format method should expect a `Number` value.

  Parameters:
    `excelFormatStr` - The data format string
    `format` - A Format instance

### setExcelStyleRoundingMode

    public static void setExcelStyleRoundingMode(java.text.DecimalFormat format)

  Enables excel style rounding mode (round half up) on the Decimal Format given.

### setExcelStyleRoundingMode

    public static void setExcelStyleRoundingMode(java.text.DecimalFormat format,
                                                 java.math.RoundingMode roundingMode)

  Enables custom rounding mode on the given Decimal Format.

  Parameters:
    `format` - DecimalFormat
    `roundingMode` - RoundingMode

### getLocaleChangedObservable

    public java.util.Observable getLocaleChangedObservable()

  If the Locale has been changed via `LocaleUtil.setUserLocale(Locale)` the stored formats need to be refreshed. All formats which aren't originated from DataFormatter itself, i.e. all Formats added via `addFormat(String, Format)` and `setDefaultNumberFormat(Format)`, need to be added again. To notify callers, the returned `Observable` should be used. The Object in `Observer.update(Observable, Object)` is the new Locale.

  Returns:
    the listener object, where callers can register themselves

### update

    public void update(java.util.Observable observable,
                       java.lang.Object localeObj)

  Update formats when locale has been changed

  Specified by:
    `update` in interface `java.util.Observer`
  Parameters:
    `observable` - usually this is our own Observable instance
    `localeObj` - only reacts on Locale objects

Overview | Package | Class | Use | Tree | Deprecated | Index | Help
PREV CLASS NEXT CLASS | FRAMES NO FRAMES
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD

Copyright 2017 The Apache Software Foundation or its licensors, as applicable.