Apache POI - Common 5.3.0 · org.apache.poi.ss.usermodel.DataFormatter

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.

Nested and Inner Type Summary

Modifier and Type	Class and Description
private class	DataFormatter.CellFormatResultWrapper Workaround until we merge `DataFormatter` with `CellFormat`.
private static class	DataFormatter.ConstantStringFormat Format class that does nothing and always returns a constant string.
private static class	DataFormatter.InternalDecimalFormatWithScale
private static class	DataFormatter.PhoneFormat Format class for Excel phone number format.
private static class	DataFormatter.SSNFormat Format class for Excel's SSN format.
private static class	DataFormatter.ZipPlusFourFormat Format class for Excel Zip + 4 format.

Field Summary

Modifier and Type	Field and Description
private static final Pattern	alternateGrouping A regex to detect if an alternate grouping character is used in a numeric format
private static final Pattern	amPmPattern Pattern to find "AM/PM" marker
private static final Pattern	colorPattern A regex to match the colour formatting's rules.
private DateFormatSymbols	dateSymbols The date symbols of the locale used for formatting values.
private static final Pattern	daysAsText Pattern to find days of week as text "ddd...."
private DecimalFormatSymbols	decimalSymbols The decimal symbols of the locale used for formatting values.
private DateFormat	defaultDateformat A default date format, if no date format was given
private static final String	defaultFractionFractionPartFormat
private static final String	defaultFractionWholePartFormat
private Format	defaultNumFormat A default format to use when a number pattern cannot be parsed.
private boolean	emulateCSV whether CSV friendly adjustments should be made to the formatted text
private final Map<String, Format>	formats A map to cache formats.
private static final Pattern	fractionPattern A regex to identify a fraction pattern.
private static final Pattern	fractionStripper A regex to strip junk out of fraction formats
private Format	generalNumberFormat General format for numbers.
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 ("#").
private Locale	locale stores the locale set by updateLocale method
private boolean	localeIsAdapting stores if the locale should change according to `LocaleUtil#getUserLocale()`
private static final Pattern	localePatternGroup A regex to find locale patterns like [$$-1009] and [$?-452].
private static final Logger	LOG For logging any problems we find
private static final Pattern	numPattern Pattern to find a number format: "0" or "#"
private final PropertyChangeSupport	pcs
private static final Pattern	rangeConditionalPattern Pattern to find formats with condition ranges e.g. [>=100]
private boolean	use4DigitYearsInAllDateFormats whether years in dates should be displayed with 4 digits even if the formatString specifies only 2
private boolean	useCachedValuesForFormulaCells if set to true, avoid recalculating the values if there is a cached value available (default is false)

Constructor Summary

Access	Constructor and Description
public	DataFormatter() Creates a formatter using the `default locale`.
public	DataFormatter(boolean whether to emulate CSV output. emulateCSV) Creates a formatter using the `default locale`.
public	DataFormatter(Locale locale) Creates a formatter using the given locale.
public	DataFormatter(Locale locale, boolean whether to emulate CSV output. emulateCSV) Creates a formatter using the given locale.
public	DataFormatter(Locale locale, boolean (true only if locale is not user-specified) localeIsAdapting, boolean whether to emulate CSV output. emulateCSV) Creates a formatter using the given locale.

Method Summary

Modifier and Type	Method and Description
public void	addFormat(String The data format string excelFormatStr, Format A Format instance format) Adds a new format to the available formats.
pack-priv String	adjustTo4DigitYearsIfConfigured(String format)
private void	checkForLocaleChange()
private void	checkForLocaleChange(Locale newLocale)
private String	cleanFormatForNumber(String formatStrIn)
private Format	createDateFormat(String pFormatStr, double cellValue)
public Format	Returns: A Format representing the excel format. May return null. createFormat(Cell The Excel cell cell) Create and return a Format based on the format string from a cell's style.
private Format	createFormat(double cellValue, int formatIndex, String sFormat)
private static DecimalFormat	Returns: a `DecimalFormat` with parseIntegerOnly set `true` createIntegerOnlyFormat(String fmt)
private Format	createNumberFormat(String formatStr, double cellValue)
public String	Returns: the formatted cell value as a String formatCellValue(Cell The cell cell) Returns the formatted value of a cell as a `String` regardless of the cell type.
public String	Returns: a string value of the cell formatCellValue(Cell The cell (can be null) cell, FormulaEvaluator The FormulaEvaluator (can be null) evaluator) Returns the formatted value of a cell as a `String` regardless of the cell type.
public String	Returns: a string value of the cell formatCellValue(Cell The cell (can be null) cell, FormulaEvaluator The FormulaEvaluator (can be null) evaluator, ConditionalFormattingEvaluator ConditionalFormattingEvaluator (can be null) cfEvaluator) Returns the formatted value of a cell as a `String` regardless of the cell type.
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.
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.
public Format	Returns: a default format getDefaultFormat(Cell The cell cell) Returns a default format for a cell.
private Format	getDefaultFormat(double cellValue)
private Format	Returns: A Format for the format String getFormat(Cell The cell to retrieve a Format for cell, ConditionalFormattingEvaluator cfEvaluator) Return a Format for the given cell if one exists, otherwise try to create one.
private Format	getFormat(double cellValue, int formatIndex, String formatStrIn, boolean use1904Windowing)
private String	Returns: Formatted value getFormattedDateString(Cell to format cell, ConditionalFormattingEvaluator ConditionalFormattingEvaluator (if available) cfEvaluator) Returns the formatted value of an Excel date as a `String` based on the cell's `DataFormat`.
private String	Returns: a formatted number string getFormattedNumberString(Cell The cell cell, ConditionalFormattingEvaluator if available, or null cfEvaluator) Returns the formatted value of an Excel number as a `String` based on the cell's `DataFormat`.
public PropertyChangeSupport	Returns: the listener object, where callers can register themselves getLocaleChangedObservable() If the Locale has been changed via `LocaleUtil#setUserLocale(Locale)` the stored formats need to be refreshed.
private boolean	isDate1904(Cell cell)
public boolean	Returns: whether to emulate CSV output (default false). isEmulateCSV()
private String	performDateFormatting(Date d, Format dateFormat) Performs Excel-style date formatting, using the supplied Date and format
public void	setDefaultNumberFormat(Format A Format instance to be used as a default 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.
public void	setEmulateCSV(boolean whether to emulate CSV output (default false). emulateCSV)
public static void	setExcelStyleRoundingMode(DecimalFormat format) Enables excel style rounding mode (round half up) on the Decimal Format given.
public static void	setExcelStyleRoundingMode(DecimalFormat DecimalFormat format, RoundingMode RoundingMode roundingMode) Enables custom rounding mode on the given Decimal Format.
public void	setUse4DigitYearsInAllDateFormats(boolean 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) use4DigitYearsInAllDateFormats)
public void	setUseCachedValuesForFormulaCells(boolean 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. The default is false and this means we return the formula itself. useCachedValuesForFormulaCells)
public void	updateLocale(Locale the new locale newLocale) Update formats when locale has been changed
public boolean	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) use4DigitYearsInAllDateFormats()
public boolean	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. The default is false and this means we return the formula itself. useCachedValuesForFormulaCells()

Inherited from java.lang.Object:: clone equals finalize getClass hashCode notify notifyAll toString wait wait wait

public Class DataFormatter

Nested and Inner Type Summary

Field Summary

Constructor Summary

Method Summary