With TX Text Control version 26, the TableCell.Formula property has been introduced to define formulas for table cells and to display the result of such calculations as cell text. By default (empty string), the rendered text represents the unformatted value of that calculation. The result without decimal places is displayed as an integer. On the other hand, a decimal number is rendered with all its decimal places.
To display such different results with a common format, TX Text Control provides the TableCellFormat.NumberFormat property. If the TableCellFormat.TextType property is set to Number, this interface can be used to set a number format string, that is a composition of placeholders ('#' or/and '0'), separators ('.' or/and ','), percentage signs ('%') and other single characters or text.
Placeholders represent digit positions inside the number to format. Two kind of characters are valid to be used as placeholders: '#' and '0', where the placeholder '0' fills those positions with zeros, if the corresponding position is not reserved by a digit of the calculation's result value.
Formatting 7 with # will be displayed as 7.
Formatting 789 with # will be displayed as 789.
Formatting 1234 with # will be displayed as 1234.
Formatting 7 with 000 will be displayed as 007.
Formatting 789 with 000 will be displayed as 789.
Two separators can be applied to display a number as a decimal number or/and to render with a thousands separator. Depending on the system language, the separator signs are rendered with the system's default digit or thousands separator signs (e.g. a German system uses comma as decimal sign and point as thousand separator).
Decimal Numbers
To specify a number format where all numbers are displayed with decimal places, the placeholder must be combined with a digit point ('.'). The number of placeholders following that separator determines the maximum number of decimal places to be shown. If no digit point is set, results are rounded to the next integer value.
Formatting 12.6 with #.## will be displayed as 12.6.
Formatting 5 with #.00 will be displayed as 5.00.
Formatting 12.6 with #.00 will be displayed as 12.60.
Formatting 0.543 with #.00 will be displayed as 0.54.
Formatting 17.548 with # will be displayed as 18.
Thousands Separator
Another separator is the thousands separator (','). Using this character in combination with appended placeholders, the displayed result of the calculation is rendered with a comma at each third digit. If no placeholder is set behind the comma, for each comma the number is trimmed by three pre-decimal point positions.
Formatting 9876543210.1234 with ,###.## will be displayed as 9,876,543,210.12.
Formatting 9876543210 with ,### will be displayed as 9,876,543,210.
Formatting 12200000 with #,, will be displayed as 12.
Formatting 12200000 with #.0,, will be displayed as 12.2.
A character with special meaning is the percentage symbol. In combination with one or more placeholders, the calculated formula value is multiplied by 100 and shown together with the percentage symbol.
Formatting 0.52 with #% will be displayed as 52%.
Formatting 1 with #% will be displayed as 100%.
Formatting 0.00321 with 0.##% will be displayed as 0.32%.
Formatting 0.52 with %# will be displayed as %52.
There are three different ways to combine the calculated value with a single character or text.
First, a list of predefined characters can be used. Each of these symbols can be combined with the number format string.
| Symbol | Example: |
|---|---|
| $ (Dollar sign) | Formatting 500 with #$ will be displayed as 500$. |
| - (Minus sign) | Formatting 1 with -#.00 will be displayed as -1.00. |
| + (Plus sign) | Formatting 12.345 with +.## will be displayed as +12.35. |
| / (Slash mark) | Formatting 4 with \1/# will be displayed as 1/4. |
| ( (Left parenthesis) | Formatting 7 with -(#+\3) will be displayed as -(7+3). |
| ) (Right parenthesis) | Formatting 3 with -(\7+#) will be displayed as -(7+3). |
| : (Colon) | Formatting 50 with #:"50" will be displayed as 50:50. |
| ! (Exclamation point) | Formatting 6 with #! will be displayed as 6!. |
| ^ (Circumflex accent (caret)) | Formatting 2.5 with #.00^\2 will be displayed as 2.50^2. |
| & (Ampersand) | Formatting 0 with #&\1 will be displayed as 0&1. |
| ' (Apostrophe) | Formatting 9 with '#' will be displayed as '9'. |
| ~ (Tilde) | Formatting 7.654 with ~#.0 will be displayed as ~7.7. |
| { (Left curly bracket) | Formatting 67 with {-#} will be displayed as {-67}. |
| } (Right curly bracket) | Formatting 17 with {+#} will be displayed as {+17}. |
| < (Less-than sign) | Formatting 5 with #<"10" will be displayed as 5<10. |
| > (Greater-than sign) | Formatting 7.4 with #.00>\6 will be displayed as 7.40>6. |
| = (Equal sign) | Formatting 5.6 with #>=\6 will be displayed as 6>=6. |
| (Space character) | Formatting 12 with #.00 $ will be displayed as 12.00 $. |
Other symbols (e.g. specific currency symbols), which are not specified in this list, can be rendered by adding a back slash right before the character to display.
Formatting 12.99 with #.00\€ will be displayed as 12.99€.
Formatting 42 with \"#\" will be displayed as "42".
Formatting 18 with #:\X will be displayed as 18:X.
If it is requested to integrate whole words or text, it is recommended to put such strings in quotation marks.
Formatting 29.99 with "EUR"#.00 will be displayed as EUR 29.99.
Formatting 4.50 with #.00"€" will be displayed as 4.50€.
Formatting 76 with # "Units" will be displayed as 76 Units.