Package org.xlsx4j.org.apache.poi.ss.usermodel

Class DataFormatter

java.lang.Object
org.xlsx4j.org.apache.poi.ss.usermodel.DataFormatter
All Implemented Interfaces:
Observer
public class DataFormatter extends Object implements 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.

  • Field Details

    • defaultFractionWholePartFormat

      private static final String defaultFractionWholePartFormat
      See Also:

    • defaultFractionFractionPartFormat

      private static final String defaultFractionFractionPartFormat
      See Also:

    • numPattern

      private static final Pattern numPattern
      Pattern to find a number format: "0" or "#"

    • daysAsText

      private static final Pattern daysAsText
      Pattern to find days of week as text "ddd...."

    • amPmPattern

      private static final Pattern amPmPattern
      Pattern to find "AM/PM" marker

    • localePatternGroup

      private static final Pattern localePatternGroup
      A regex to find locale patterns like [$$-1009] and [$?-452]. Note that we don't currently process these into locales

    • colorPattern

      private static final Pattern colorPattern
      A regex to match the colour formattings rules. Allowed colours are: Black, Blue, Cyan, Green, Magenta, Red, White, Yellow, "Color n" (1<=n<=56)

    • fractionPattern

      private static final Pattern fractionPattern
      A regex to identify a fraction pattern. This requires that replaceAll("\\?", "#") has already been called

    • fractionStripper

      private static final Pattern fractionStripper
      A regex to strip junk out of fraction formats

    • alternateGrouping

      private static final Pattern alternateGrouping
      A regex to detect if an alternate grouping character is used in a numeric format

    • invalidDateTimeString

      private static final String invalidDateTimeString
      Cells formatted with a date or time format and which contain invalid date or time values show 255 pound signs ("#").

    • decimalSymbols

      private DecimalFormatSymbols decimalSymbols
      The decimal symbols of the locale used for formatting values.

    • dateSymbols

      private DateFormatSymbols dateSymbols
      The date symbols of the locale used for formatting values.

    • defaultDateformat

      private DateFormat defaultDateformat
      A default date format, if no date format was given

    • generalNumberFormat

      private Format generalNumberFormat
      General format for numbers.

    • defaultNumFormat

      private Format defaultNumFormat
      A default format to use when a number pattern cannot be parsed.

    • formats

      private final Map<String,Format> formats
      A map to cache formats. Map<String,Format> formats

    • emulateCSV

      private final boolean emulateCSV

    • locale

      private Locale locale
      stores the locale valid it the last formatting call

    • localeIsAdapting

      private boolean localeIsAdapting
      stores if the locale should change according to LocaleUtil.getUserLocale()

    • localeChangedObservable

      private final DataFormatter.LocaleChangeObservable localeChangedObservable
      the Observable to notify, when the locale has been changed

    • logger

      protected static org.slf4j.Logger logger
      For logging any problems we find

  • 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

      private 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

    • getFormat

      private Format getFormat(Cell cell)
      Return a Format for the given cell if one exists, otherwise try to create one. This method will return null if the any of the following is true:
      • the cell's style is null
      • the style's data format string is null or empty
      • the format string cannot be recognized as either a number or date
      Parameters:
      cell - The cell to retrieve a Format for
      Returns:
      A Format for the format String

    • getFormat

      private Format getFormat(double cellValue, long formatIndex, String formatStrIn)

    • 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

      private Format createFormat(double cellValue, long formatIndex, String sFormat)

    • createDateFormat

      private Format createDateFormat(String pFormatStr, double cellValue)

    • cleanFormatForNumber

      private String cleanFormatForNumber(String formatStr)

    • createNumberFormat

      private Format createNumberFormat(String formatStr, double cellValue)

    • getDefaultFormat

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

    • getDefaultFormat

      private Format getDefaultFormat(double cellValue)

    • performDateFormatting

      private String performDateFormatting(Date d, Format dateFormat)
      Performs Excel-style date formatting, using the supplied Date and format

    • getFormattedDateString

      private String getFormattedDateString(Cell cell)
      Returns the formatted value of an Excel date as a String based on the cell's DataFormat. i.e. "Thursday, January 02, 2003" , "01/02/2003" , "02-Jan" , etc.
      Parameters:
      cell - The cell
      Returns:
      a formatted date string

    • getFormattedNumberString

      private String getFormattedNumberString(Cell cell)
      Returns the formatted value of an Excel number as a String based on the cell's DataFormat. Supported formats include currency, percents, decimals, phone number, SSN, etc.: "61.54%", "$100.00", "(800) 555-1234".
      Parameters:
      cell - The cell
      Returns:
      a formatted number string

    • 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 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

      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.

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

    • getCellStringValue

      private String getCellStringValue(Cell c)

    • getCellFormula

      public String getCellFormula(Cell _cell)
      Return a formula for the cell, for example, SUM(C4:E4)
      Returns:
      a formula for the cell
      Throws:
      IllegalStateException - if the cell type returned by #getCellType() is not CELL_TYPE_FORMULA

    • 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

    • createIntegerOnlyFormat

      private static DecimalFormat createIntegerOnlyFormat(String fmt)
      Returns:
      a DecimalFormat with parseIntegerOnly set true

    • 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 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(Observable observable, Object localeObj)
      Update formats when locale has been changed
      Specified by:
      update in interface Observer
      Parameters:
      observable - usually this is our own Observable instance
      localeObj - only reacts on Locale objects