Apache POI - Common 5.3.0 · org.apache.poi.ss.format.CellFormat

Format a value according to the standard Excel behavior. This "standard" is not explicitly documented by Microsoft, so the behavior is determined by experimentation; see the tests.

An Excel format has up to four parts, separated by semicolons. Each part specifies what to do with particular kinds of values, depending on the number of parts given:

One part (example: [Green]#.##): If the value is a number, display according to this one part (example: green text, with up to two decimal points). If the value is text, display it as is.
Two parts (example: [Green]#.##;[Red]#.##): If the value is a positive number or zero, display according to the first part (example: green text, with up to two decimal points); if it is a negative number, display according to the second part (example: red text, with up to two decimal points). If the value is text, display it as is.
Three parts (example: [Green]#.##;[Black]#.##;[Red]#.##): If the value is a positive number, display according to the first part (example: green text, with up to two decimal points); if it is zero, display according to the second part (example: black text, with up to two decimal points); if it is a negative number, display according to the third part (example: red text, with up to two decimal points). If the value is text, display it as is.
Four parts (example: [Green]#.##;[Black]#.##;[Red]#.##;[@]): If the value is a positive number, display according to the first part (example: green text, with up to two decimal points); if it is zero, display according to the second part (example: black text, with up to two decimal points); if it is a negative number, display according to the third part (example: red text, with up to two decimal points). If the value is text, display according to the fourth part (example: text in the cell's usual color, with the text value surround by brackets).

A given format part may specify a given Locale, by including something like [$$-409] or [$£-809] or [$-40C]. These are (currently) largely ignored. You can use DateFormatConverter to look these up into Java Locales if desired.

In addition to these, there is a general format that is used when no format is specified. TODO Merge this with DataFormatter so we only have one set of code for formatting numbers. TODO Re-use parts of this logic with ConditionalFormatting / ConditionalFormattingRule for reporting stylings which do/don't apply TODO Support the full set of modifiers, including alternate calendars and native character numbers, as documented at https://help.libreoffice.org/Common/Number_Format_Codes

Field Summary

Modifier and Type	Field and Description
private final String	format
private static final Map<Locale, Map<String, CellFormat>>	formatCache Maps a format string to its parsed version for efficiencies sake.
private final int	formatPartCount
private static final String	INVALID_VALUE_FOR_FORMAT
private final Locale	locale
private static final Logger	LOG The logger to use in the formatting code.
private final CellFormatPart	negNumFmt
private static final Pattern	ONE_PART
private final CellFormatPart	posNumFmt
private static final String	QUOTE
private final CellFormatPart	textFmt
private final CellFormatPart	zeroNumFmt

Constructor Summary

Access	Constructor and Description
private	CellFormat(Locale locale, String The format. format) Creates a new object.

Method Summary

Modifier and Type	Method and Description
public CellFormatResult	Returns: The result, in a `CellFormatResult`. apply(Object The value value) Returns the result of applying the format to the given value.
private CellFormatResult	Returns: The result, in a `CellFormatResult`. apply(Date The date. date, double The numeric value for the date. numericValue) Returns the result of applying the format to the given date.
public CellFormatResult	Returns: The result, in a `CellFormatResult`. apply(Cell The cell. c) Fetches the appropriate value from the cell, and returns the result of applying it to the appropriate format.
public CellFormatResult	Returns: The result, in a `CellFormatResult`. apply(JLabel The label to apply to. label, Object The value to process. value) Uses the result of applying this format to the value, setting the text and color of a label before returning the result.
private CellFormatResult	Returns: The result, in a `CellFormatResult`. apply(JLabel The label to apply to. label, Date The date. date, double The numeric value for the date. numericValue) Uses the result of applying this format to the given date, setting the text and color of a label before returning the result.
public CellFormatResult	Returns: The result, in a `CellFormatResult`. apply(JLabel The label to apply to. label, Cell The cell. c) Fetches the appropriate value from the cell, and uses the result, setting the text and color of a label before returning the result.
private static CellFormat	createGeneralFormat(final Locale locale)
public boolean	Returns: `true` if the two objects are equal. equals(Object The other object. obj) Overrides java.lang.Object.equals. Returns `true` if the other object is a CellFormat object with the same format.
private CellFormatPart	Returns: The `CellFormatPart` that applies to the value. getApplicableFormatPart(Object The value. value) Returns the `CellFormatPart` that applies to the value.
public static CellFormat	Returns: A CellFormat that applies the given format. getInstance(String The format. format) Returns a CellFormat that applies the given format.
public static synchronized CellFormat	Returns: A CellFormat that applies the given format. getInstance(Locale The locale. locale, String The format. format) Returns a CellFormat that applies the given format.
public int	Returns: A hash code for the format. hashCode() Overrides java.lang.Object.hashCode. Returns a hash code for the format.
public static CellType	Returns: The ultimate type of this cell. ultimateType(Cell The cell. cell) Returns the ultimate cell type, following the results of formulas.

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

Field Detail

format	back to summary
private final String format

formatCache	back to summary
private static final Map<Locale, Map<String, CellFormat>> formatCache Maps a format string to its parsed version for efficiencies sake.

formatPartCount	back to summary
private final int formatPartCount

INVALID_VALUE_FOR_FORMAT	back to summary
private static final String INVALID_VALUE_FOR_FORMAT

locale	back to summary
private final Locale locale

LOG	back to summary
private static final Logger LOG The logger to use in the formatting code.

negNumFmt	back to summary
private final CellFormatPart negNumFmt

ONE_PART	back to summary
private static final Pattern ONE_PART

posNumFmt	back to summary
private final CellFormatPart posNumFmt

QUOTE	back to summary
private static final String QUOTE

textFmt	back to summary
private final CellFormatPart textFmt

zeroNumFmt	back to summary
private final CellFormatPart zeroNumFmt

Constructor Detail

CellFormat	back to summary
private CellFormat(Locale locale, String format) Creates a new object. Parameters format:String The format.

CellFormat

back to summary

private CellFormat(Locale locale, String format)

Creates a new object.

Parameters

format:String: The format.

Method Detail

apply back to summary

apply	back to summary
public CellFormatResult apply(Object value) Returns the result of applying the format to the given value. If the value is a number (a type of `Number` object), the correct number format type is chosen; otherwise it is considered a text object. Parameters value:Object The value Returns:CellFormatResult The result, in a `CellFormatResult`.

public CellFormatResult apply(Object value)

Returns the result of applying the format to the given value. If the value is a number (a type of Number object), the correct number format type is chosen; otherwise it is considered a text object.

Parameters

value:Object: The value

Returns:CellFormatResult

The result, in a CellFormatResult.

apply back to summary

apply	back to summary
private CellFormatResult apply(Date date, double numericValue) Returns the result of applying the format to the given date. Parameters date:Date The date. numericValue:double The numeric value for the date. Returns:CellFormatResult The result, in a `CellFormatResult`.

private CellFormatResult apply(Date date, double numericValue)

Returns the result of applying the format to the given date.

Parameters

date:Date: The date.
numericValue:double: The numeric value for the date.

Returns:CellFormatResult

The result, in a CellFormatResult.

apply back to summary

apply	back to summary
public CellFormatResult apply(Cell c) Fetches the appropriate value from the cell, and returns the result of applying it to the appropriate format. For formula cells, the computed value is what is used. Parameters c:Cell The cell. Returns:CellFormatResult The result, in a `CellFormatResult`.

public CellFormatResult apply(Cell c)

Fetches the appropriate value from the cell, and returns the result of applying it to the appropriate format. For formula cells, the computed value is what is used.

Parameters

c:Cell: The cell.

Returns:CellFormatResult

The result, in a CellFormatResult.

apply back to summary

apply	back to summary
public CellFormatResult apply(JLabel label, Object value) Uses the result of applying this format to the value, setting the text and color of a label before returning the result. Parameters label:JLabel The label to apply to. value:Object The value to process. Returns:CellFormatResult The result, in a `CellFormatResult`.

public CellFormatResult apply(JLabel label, Object value)

Uses the result of applying this format to the value, setting the text and color of a label before returning the result.

Parameters

label:JLabel: The label to apply to.
value:Object: The value to process.

Returns:CellFormatResult

The result, in a CellFormatResult.

apply back to summary

apply	back to summary
private CellFormatResult apply(JLabel label, Date date, double numericValue) Uses the result of applying this format to the given date, setting the text and color of a label before returning the result. Parameters label:JLabel The label to apply to. date:Date The date. numericValue:double The numeric value for the date. Returns:CellFormatResult The result, in a `CellFormatResult`.

private CellFormatResult apply(JLabel label, Date date, double numericValue)

Uses the result of applying this format to the given date, setting the text and color of a label before returning the result.

Parameters

label:JLabel: The label to apply to.
date:Date: The date.
numericValue:double: The numeric value for the date.

Returns:CellFormatResult

The result, in a CellFormatResult.

apply back to summary

apply	back to summary
public CellFormatResult apply(JLabel label, Cell c) Fetches the appropriate value from the cell, and uses the result, setting the text and color of a label before returning the result. Parameters label:JLabel The label to apply to. c:Cell The cell. Returns:CellFormatResult The result, in a `CellFormatResult`.

public CellFormatResult apply(JLabel label, Cell c)

Fetches the appropriate value from the cell, and uses the result, setting the text and color of a label before returning the result.

Parameters

label:JLabel: The label to apply to.
c:Cell: The cell.

Returns:CellFormatResult

The result, in a CellFormatResult.

createGeneralFormat	back to summary
private static CellFormat createGeneralFormat(final Locale locale)

equals back to summary

equals	back to summary
public boolean equals(Object obj) Overrides java.lang.Object.equals. Returns `true` if the other object is a CellFormat object with the same format. Parameters obj:Object The other object. Returns:boolean `true` if the two objects are equal. Annotations @Override

public boolean equals(Object obj)

Overrides java.lang.Object.equals.

Returns true if the other object is a CellFormat object with the same format.

Parameters

obj:Object: The other object.

Returns:boolean

true if the two objects are equal.

Annotations

@Override

getApplicableFormatPart back to summary

getApplicableFormatPart	back to summary
private CellFormatPart getApplicableFormatPart(Object value) Returns the `CellFormatPart` that applies to the value. Result depends on how many parts the cell format has, the cell value and any conditions. The value must be a `Number`. Parameters value:Object The value. Returns:CellFormatPart The `CellFormatPart` that applies to the value.

private CellFormatPart getApplicableFormatPart(Object value)

Returns the CellFormatPart that applies to the value. Result depends on how many parts the cell format has, the cell value and any conditions. The value must be a Number.

Parameters

value:Object: The value.

Returns:CellFormatPart

The CellFormatPart that applies to the value.

getInstance	back to summary
public static CellFormat getInstance(String format) Returns a CellFormat that applies the given format. Two calls with the same format may or may not return the same object. Parameters format:String The format. Returns:CellFormat A CellFormat that applies the given format.

getInstance

back to summary

public static CellFormat getInstance(String format)

Returns a CellFormat that applies the given format. Two calls with the same format may or may not return the same object.

Parameters

format:String: The format.

Returns:CellFormat

A CellFormat that applies the given format.

getInstance	back to summary
public static synchronized CellFormat getInstance(Locale locale, String format) Returns a CellFormat that applies the given format. Two calls with the same format may or may not return the same object. Parameters locale:Locale The locale. format:String The format. Returns:CellFormat A CellFormat that applies the given format.

getInstance

back to summary

public static synchronized CellFormat getInstance(Locale locale, String format)

Returns a CellFormat that applies the given format. Two calls with the same format may or may not return the same object.

Parameters

locale:Locale: The locale.
format:String: The format.

Returns:CellFormat

A CellFormat that applies the given format.

hashCode	back to summary
public int hashCode() Overrides java.lang.Object.hashCode. Returns a hash code for the format. Returns:int A hash code for the format. Annotations @Override

hashCode

back to summary

public int hashCode()

Overrides java.lang.Object.hashCode.

Returns a hash code for the format.

Returns:int

A hash code for the format.

Annotations

@Override

ultimateType back to summary

ultimateType	back to summary
public static CellType ultimateType(Cell cell) Returns the ultimate cell type, following the results of formulas. If the cell is a `CellType#FORMULA`, this returns the result of `Cell#getCachedFormulaResultType()`. Otherwise this returns the result of `Cell#getCellType()`. Parameters cell:Cell The cell. Returns:CellType The ultimate type of this cell.

public static CellType ultimateType(Cell cell)

Returns the ultimate cell type, following the results of formulas. If the cell is a CellType#FORMULA, this returns the result of Cell#getCachedFormulaResultType(). Otherwise this returns the result of Cell#getCellType().

Parameters

cell:Cell: The cell.

Returns:CellType

The ultimate type of this cell.

public Class CellFormat

Field Summary

Constructor Summary

Method Summary

Field Detail

Constructor Detail

Method Detail