The RadSpreadsheet's cell-formatting options dialog can be customized. The existing options divided into three categories Number, Currency and Date and Time can be replaced by or complemented with additional formatting options. This can be achieved by adding a user control to the RadSpreadsheet's CustomFormatTemplate:
<telerik:RadSpreadsheet runat="server" ID="RadSpreadsheet1"> <CustomFormatTemplate> <uc1:SpreadsheetCustomFormat runat="server" ID="SpreadsheetCustomFormat" /> </CustomFormatTemplate> </telerik:RadSpreadsheet>
A sample code library project demostrating this approach can be downloaded here.
The following screenshot shows the result of modifying the user control and including two additional formatting options ("100.00 euro" and "€100") in the Currency tab.
The format strings are compatible with the Excel number formats, with some exceptions:
Exponent (scientific) notation (E+, E- Excel formats)
Filling cell width (
A format string consists of one or more sections, separated by semicolons. A section optionally specifies a color and a condition.
Excel documentation mentions that a maximum of four sections are supported, and if all are present, they are interpreted in the following order:
Positive numbers format
Negative numbers format
Format for zero
Format for text
Excel also supports a more flexible conditional formatting.
In this case it is not clear whether only up to four sections are allowed, of which the last one must be text, while the Spreadsheet formatter allows for any number of conditional sections.
|0||Digit placeholder. Use this to display insignificant zeroes. For example, 8.9 with the format 00.000 displays as 08.900.|
||Digit placeholder. This does not display insignificant zeroes. For example, 12.34 in the
|?||Digit placeholder. It is similar to 0, but displays a space character instead of a zero. You can use this to align numbers by decimal point, but you should use a fixed-width font for this to be effective.|
|,||Thousands separator, or scale (see below).|
|\||Escape the next character (display literally).|
|_||Skip the width of the next character.|
|"text"||Includes a piece of text in the format. Characters inside will not be interpreted in any way, they will just output literally.|
|@||Text placeholder. Will be replaced with the text in the cell.|
|m||Display month number without leading zero|
|mm||Display month number with leading zero|
|mmm||Display short month name in current culture|
|mmmm||Display full month name in current culture|
|d||Display date number without leading zero|
|dd||Display date number with leading zero|
|ddd||Display the abbreviated week day name|
|dddd||Display the full week day name|
|yy||Display the year as two digit number|
|YYYY||Display the full year number|
|h||Display hour without leading zero|
|hh||Display hour including leading zero|
|m||Display minute without leading zero|
|mm||Display minute including leading zero|
|s||Display second without leading zero|
|ss||Display second including leading zero|
|[h]||Elapsed time in hours|
|[m]||Elapsed time in minutes|
|[s]||Elapsed time in seconds|
|AM/PM||Forces hours to display in 12-hours clock, and displays AM or PM as necessary|
|am/pm||Same as above, but displays lower-case am or pm|
|A/P||Same as above, but displays A or P|
|a/p||Same, but displays a or p|
Note that the month and minute specifiers are ambiguous (m or mm). These strings are interpreted as a month number, unless preceded by an hour part (h or hh) in which case it displays minutes.