Package org.apache.poi.ss.usermodel

Class DataFormatter

java.lang.Object
org.apache.poi.ss.usermodel.DataFormatter
Direct Known Subclasses:
HSSFDataFormatter
public class DataFormatter extends Object
DataFormatter contains methods for formatting the value stored in a 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 constructor. 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 Details

    • 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(Locale locale)
      Creates a formatter using the given locale.

    • DataFormatter

      public DataFormatter(Locale locale, boolean emulateCSV)
      Creates a formatter using the given locale.
      Parameters:
      emulateCSV - whether to emulate CSV output.

    • DataFormatter

      public DataFormatter(Locale locale, boolean localeIsAdapting, boolean emulateCSV)
      Creates a formatter using the given locale.
      Parameters:
      localeIsAdapting - (true only if locale is not user-specified)
      emulateCSV - whether to emulate CSV output.

  • Method Details

    • setEmulateCSV

      public void setEmulateCSV(boolean emulateCSV)
      Parameters:
      emulateCSV - whether to emulate CSV output (default false).
      Since:
      5.2.0

    • isEmulateCSV

      public boolean isEmulateCSV()
      Returns:
      whether to emulate CSV output (default false).
      Since:
      5.2.0

    • setUseCachedValuesForFormulaCells

      public void setUseCachedValuesForFormulaCells(boolean useCachedValuesForFormulaCells)
      Parameters:
      useCachedValuesForFormulaCells - if set to true, when you do not provide a FormulaEvaluator, for cells with formulas, we will return the cached value for the cell (if available), otherwise - we return the formula itself. Since POI 6.0.0, the default is true, so the cached value is used when it is available. In POI 5.x, the default was false and the formula itself was returned. Set this to false to restore the POI 5.x behaviour.
      Since:
      5.2.0

    • useCachedValuesForFormulaCells

      public boolean useCachedValuesForFormulaCells()
      Returns:
      useCachedValuesForFormulaCells if set to true, when you do not provide a FormulaEvaluator, for cells with formulas, we will return the cached value for the cell (if available), otherwise - we return the formula itself. Since POI 6.0.0, the default is true, so the cached value is used when it is available. In POI 5.x, the default was false and the formula itself was returned.
      Since:
      5.2.0

    • setUse4DigitYearsInAllDateFormats

      public void setUse4DigitYearsInAllDateFormats(boolean use4DigitYearsInAllDateFormats)
      Parameters:
      use4DigitYearsInAllDateFormats - set to true if you want to have all dates formatted with 4 digit years (even if the format associated with the cell specifies just 2)
      Since:
      5.2.0

    • use4DigitYearsInAllDateFormats

      public boolean use4DigitYearsInAllDateFormats()
      Returns:
      use4DigitYearsInAllDateFormats set to true if you want to have all dates formatted with 4 digit years (even if the format associated with the cell specifies just 2)
      Since:
      5.2.0

    • getFormat

      protected Format getFormat(double cellValue, int formatIndex, String formatStrIn, boolean use1904Windowing)
      Returns the Format to use for a value with the given format.

      Formats with several sections or conditions are evaluated with CellFormat for this value and are not cached. Other formats are looked up in the cache of formats (see addFormat(java.lang.String, java.text.Format)), and otherwise created with createFormat(double, int, String) and cached.

      Subclasses can override this to change the Format used for some format strings. For date formats, the returned Format is given a Date to format.

      Parameters:
      cellValue - the value to format
      formatIndex - the index of the format
      formatStrIn - the format string
      use1904Windowing - whether the 1904 date system is used
      Returns:
      the Format to use, or null if the value should be shown with String.valueOf(double)
      Since:
      6.0.0

    • createFormat

      public 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.

    • createFormat

      protected Format createFormat(double cellValue, int formatIndex, String sFormat)
      Creates a Format for the given format string. The result is not cached by this method, getFormat(double, int, String, boolean) caches it.

      Subclasses can override this to change how some format strings are handled.

      Parameters:
      cellValue - the value to format
      formatIndex - the index of the format
      sFormat - the format string
      Returns:
      the Format to use, a default Format if the pattern cannot be parsed, or null if the value should be shown with String.valueOf(double)
      Since:
      6.0.0

    • getDefaultFormat

      public Format getDefaultFormat(Cell cell)
      Returns a default format for a cell.
      Parameters:
      cell - The cell
      Returns:
      a default format

    • formatRawCellContents

      public String formatRawCellContents(double value, int formatIndex, String formatString)
      Formats the given raw cell value, based on the supplied format index and string, according to excel style rules.
      See Also:

    • formatRawCellContents

      public String formatRawCellContents(double value, int formatIndex, String formatStringIn, boolean use1904Windowing)
      Formats the given raw cell value, based on the supplied format index and string, according to excel style rules.
      See Also:

    • formatCellValue

      public 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. setUseCachedValuesForFormulaCells(boolean) controls how these cells are evaluated. Since POI 6.0.0, that setting defaults to true, so the cached value of a formula cell is returned when one is available (POI 5.x returned the formula itself by default).

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

    • formatCellValue

      public 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
      See Also:

    • formatCellValue

      public 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
      See Also:

    • setDefaultNumberFormat

      public void setDefaultNumberFormat(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:

    • addFormat

      public void addFormat(String excelFormatStr, 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

    • setBuiltinFormatOverride

      public void setBuiltinFormatOverride(int formatIndex, String formatString)
      Sets the format string to use for a built-in format, in place of the default (en-US) one from BuiltinFormats.

      Excel does not store the format string of most built-in formats in a workbook, just the format index, and it displays some of them (for example 37-40) differently depending on the locale of the Excel installation. POI only knows the en-US versions, so for example index 40 is formatted as #,##0.00_);[Red](#,##0.00), whereas German Excel shows it as #,##0.00 _€;[Red]-#,##0.00 _€. Use this method to supply the format string that matches the locale the workbook was made for.

      The override only applies when the format string of the cell is the default one for that index, so a format string that the workbook explicitly stores for the index is still used.

      Parameters:
      formatIndex - index of a built-in format, see BuiltinFormats
      formatString - the format string to use for the index, or null to remove an override
      Throws:
      IllegalArgumentException - if formatIndex is not the index of a built-in format
      Since:
      6.0.0

    • getBuiltinFormatOverride

      public String getBuiltinFormatOverride(int formatIndex)
      Parameters:
      formatIndex - index of a built-in format, see BuiltinFormats
      Returns:
      the format string set with setBuiltinFormatOverride(int, String) for the index, or null if there is none
      Since:
      6.0.0

    • setExcelStyleRoundingMode

      public static void setExcelStyleRoundingMode(DecimalFormat format)
      Enables excel style rounding mode (round half up) on the Decimal Format given.

    • setExcelStyleRoundingMode

      public static void setExcelStyleRoundingMode(DecimalFormat format, RoundingMode roundingMode)
      Enables custom rounding mode on the given Decimal Format.
      Parameters:
      format - DecimalFormat
      roundingMode - RoundingMode

    • getLocaleChangedObservable

      public PropertyChangeSupport 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 PropertyChangeSupport should be used. The Locale in updateLocale(Locale) is the new Locale.
      Returns:
      the listener object, where callers can register themselves

    • updateLocale

      public void updateLocale(Locale newLocale)
      Update formats when locale has been changed
      Parameters:
      newLocale - the new locale