Class DataFormatter
- All Implemented Interfaces:
Observer
- Direct Known Subclasses:
HSSFDataFormatter
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"
.
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
ConstructorsConstructorDescriptionCreates a formatter using thedefault locale
.DataFormatter
(boolean emulateCSV) Creates a formatter using thedefault locale
.DataFormatter
(Locale locale) Creates a formatter using the given locale.DataFormatter
(Locale locale, boolean emulateCSV) Creates a formatter using the given locale.DataFormatter
(Locale locale, boolean localeIsAdapting, boolean emulateCSV) Creates a formatter using the given locale. -
Method Summary
Modifier and TypeMethodDescriptionvoid
Adds a new format to the available formats.createFormat
(Cell cell) Create and return a Format based on the format string from a cell's style.formatCellValue
(Cell cell) Returns the formatted value of a cell as a String regardless of the cell type.formatCellValue
(Cell cell, FormulaEvaluator evaluator) Returns the formatted value of a cell as a String regardless of the cell type.formatCellValue
(Cell cell, FormulaEvaluator evaluator, ConditionalFormattingEvaluator cfEvaluator) Returns the formatted value of a cell as a String regardless of the cell type.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.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.getDefaultFormat
(Cell cell) Returns a default format for a cell.If the Locale has been changed viaLocaleUtil.setUserLocale(Locale)
the stored formats need to be refreshed.void
setDefaultNumberFormat
(Format format) Sets a default number format to be used when the Excel format cannot be parsed successfully.static void
Enables excel style rounding mode (round half up) on the Decimal Format given.static void
setExcelStyleRoundingMode
(DecimalFormat format, RoundingMode roundingMode) Enables custom rounding mode on the given Decimal Format.void
update
(Observable observable, Object localeObj) Update formats when locale has been changed
-
Constructor Details
-
DataFormatter
public DataFormatter()Creates a formatter using thedefault locale
. -
DataFormatter
public DataFormatter(boolean emulateCSV) Creates a formatter using thedefault locale
.- Parameters:
emulateCSV
- whether to emulate CSV output.
-
DataFormatter
Creates a formatter using the given locale. -
DataFormatter
Creates a formatter using the given locale.- Parameters:
emulateCSV
- whether to emulate CSV output.
-
DataFormatter
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
-
createFormat
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
Returns a default format for a cell.- Parameters:
cell
- The cell- Returns:
- a default format
-
formatRawCellContents
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
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
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 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 evaluatorWhen 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
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 aNumber
value.- Parameters:
format
- A Format instance to be used as a default- See Also:
-
addFormat
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 aNumber
value.- Parameters:
excelFormatStr
- The data format stringformat
- A Format instance
-
setExcelStyleRoundingMode
Enables excel style rounding mode (round half up) on the Decimal Format given. -
setExcelStyleRoundingMode
Enables custom rounding mode on the given Decimal Format.- Parameters:
format
- DecimalFormatroundingMode
- RoundingMode
-
getLocaleChangedObservable
If the Locale has been changed viaLocaleUtil.setUserLocale(Locale)
the stored formats need to be refreshed. All formats which aren't originated from DataFormatter itself, i.e. all Formats added viaaddFormat(String, Format)
andsetDefaultNumberFormat(Format)
, need to be added again. To notify callers, the returnedObservable
should be used. The Object inObserver.update(Observable, Object)
is the new Locale.- Returns:
- the listener object, where callers can register themselves
-
update
Update formats when locale has been changed
-