xlsgen > overview > Number format

Number formatting is the ability to control how a number is displayed by taking advantage of the culture-specific thousand and decimal separators, the amount of decimals as well as a currency symbol. Number format can also be used to format dates, percentages, and even custom formats.

A short listing of the great formatting capabilities is provided below, but let's not regard it as a replacement for the online reference Excel help on that matter.

• To escape text, enclose it in quotes, like in $0.00" Surplus";$-0.00" Shortage"
• To add a space character, include an _ (underscore).
• Use # for insignificant digits. Using ####.#, 1234.59 displays as 1234.6
• Use 0 for significant digits (or for padding). Using #.0#, 12 displays as 12.0
• Use ? for alignment
• Use an expression such as ??/?? in order to display fractions (numerator/denominator) instead of decimals
• Use , as thousand separator
• Use . as decimal separator
• Use ; to separate the formatting when the number is positive, and the formatting when the number is not
• Use [Red] to add color to a cell, as in [Red]###,###.00. Among known colors are [Red], [Black], [Cyan], [Green], [Magenta], [Blue], [White] and [Yellow], as well as colors from the 56 predefined palette of the form [Color___] where ___ is a number between 1 and 56.
• Use closed brackets to enclose conditions, or use the conditional formatting object. Example using closed brackets : [Red][<=100];[Blue][>100]
• Use % to display a number as a percentage. Please note that Excel will automatically perform a 100 time multiplication.
• Use the typical date symbols to format dates. Symbols can be combined as below :

In Excel, number formatting options appear after right-click a cell or group of cells and choosing Format Cell as in :

Some often used number formatting options are also available in the formatting toolbar, as showed below :

Reading and writing number formats

Using number formatting options from xlsgen is a breeze. Just pass the definition in the formatting style, and you are done. Below is a sample code that specifies separators and 2 decimals for the number 5 in a cell :

Dim style As IXlsStyle
Set style = wksht.NewStyle

style.Format = "###,###.00 F"
style.Apply

wksht.Float(3, 2) = 5 ' put 5.00 in row 3, column 2

IXlsStyle style = wksht.NewStyle();

style.Format = "###,###.00 F";
style.Apply();

wksht.set_Float(3, 2, 5.00); // put 5.00 in row 3, column 2

xlsgen::IXlsStylePtr style = wksht->NewStyle();

style->Format = L"###,###.00 F";
style->Apply();

wksht->Float[3][2] = 5.00; // put 5.00 in row 3, column 2

Please note custom formatting can be used for instance to take advantage of a particular formatting when the number is positive, and another one when the number is negative. Each of those formattings need to be separated by a semi-colon, as in :

Dim style As IXlsStyle
Set style = wksht.NewStyle

style.Format = "[Black]###,###.00 F;[Red]###,###.00 F"
style.Apply

wksht.Float(3, 2) = 5 ' put 5.00 in row 3, column 2
wksht.Float(4, 2) = -5 ' put -5.00 in row 4, column 2

IXlsStyle style = wksht.NewStyle();

style.Format = "[Black]###,###.00 F;[Red]###,###.00 F";
style.Apply();

wksht.set_Float(3, 2, 5.00); // put 5.00 in row 3, column 2
wksht.set_Float(4, 2, -5.00); // put -5.00 in row 4, column 2

xlsgen::IXlsStylePtr style = wksht->NewStyle();

style->Format = L"[Black]###,###.00 F;[Red]###,###.00 F";
style->Apply();

wksht->Float[3][2] = 5.00; // put 5.00 in row 3, column 2
wksht->Float[4][2] = -5.00; // put -5.00 in row 4, column 2

The xlsgen object model lets you access data and its formatting separately, but you can also evaluate the formatted data means by projecting the number format to the data in a cell. Because number formats may include conditional colors, such as [Red]###,###.00 F, a style instance reflects that color wherever it applies. Here is a source code showing how it works :

Dim style As IXlsStyle
Set style = wksht.NewStyle

style.Format = "[Black]###,###.00 F;[Red]###,###.00 F"
style.Apply

wksht.Float(3, 2) = 5 ' put 5.00 in row 3, column 2
wksht.Float(4, 2) = -5 ' put -5.00 in row 4, column 2

Dim formatted_positive As String
Set formatted_positive = wksht.FormattedLabel(3,2) ' returns "5.00 F"
Dim formatted_negative As String
Set formatted_negative = wksht.FormattedLabel(4,2) ' returns "-5.00 F"

Dim fontColor_positive
fontColor_positive = wksht.StyleComposedFromLocation(3,2).Font.Color ' returns &H000000 (black)
Dim fontColor_negative
fontColor_negative = wksht.StyleComposedFromLocation(4,2).Font.Color ' returns &HFF0000 (red)

IXlsStyle style = wksht.NewStyle();

style.Format = "[Black]###,###.00 F;[Red]###,###.00 F";
style.Apply();

wksht.set_Float(3, 2, 5.00); // put 5.00 in row 3, column 2
wksht.set_Float(4, 2, -5.00); // put -5.00 in row 4, column 2

String formatted_positive = wksht.get_FormattedLabel(3,2); // returns "5.00 F"
String formatted_negative = wksht.get_FormattedLabel(4,2); // returns "-5.00 F"

int fontColor_positive = wksht.get_StyleComposedFromLocation(3,2).Font.Color; // returns 0x000000 (black)
int fontColor_negative = wksht.get_StyleComposedFromLocation(4,2).Font.Color; // returns 0xFF0000 (red)

xlsgen::IXlsStylePtr style = wksht->NewStyle();

style->Format = L"[Black]###,###.00 F;[Red]###,###.00 F";
style->Apply();

wksht->Float[3][2] = 5.00; // put 5.00 in row 3, column 2
wksht->Float[4][2] = -5.00; // put -5.00 in row 4, column 2

_bstr_t formatted_positive = wksht->FormattedLabel[3][2]; // returns "5.00 F"
_bstr_t formatted_negative = wksht->FormattedLabel[4][2]; // returns "-5.00 F"

int fontColor_positive = wksht->StyleComposedFromLocation[3][2]->Font->Color; // returns 0x000000 (black)
int fontColor_negative = wksht->StyleComposedFromLocation[4][2]->Font->Color; // returns 0xFF0000 (red)

In addition to colors, number format parts can hold conditions such as [>100]. When no condition is defined, the parts read as follow : part 1 is for positive numbers, part 2 is for negative numbers, part 3 is for zeros, part 4 applies to generic text. By defining conditions, it is possible to redefine the meaning of parts, therefore apply complicated alert-type formatting.