New to Telerik UI for ASP.NET AJAXStart a free 30-day trial

Custom Cell Format

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:

ASPNET
<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.

Custom Format

Formatting options

Strings

The format strings are compatible with the Excel number formats, with some exceptions:

  • Exponent (scientific) notation (E+, E- Excel formats)

  • Filling cell width (* Excel format)

A format string consists of one or more sections, separated by semicolons. A section optionally specifies a color and a condition.

The following example demonstrates how to display a number with up to three decimals.

#.###

The following example demonstrates how to display positive numbers, or zero, in green, and negative numbers in red.

[Green]#.###;[Red]#.###

The following example demonstrates how to display positive numbers in green, negative numbers in red, and the the "Zero" text in blue if the number is zero.

[Green]#.###;[Red]#.###;[Blue]"Zero"

This example is the same as the previous one, with the difference to display any possible text in the cell in magenta.

[Green]#.###;[Red]#.###;[Blue]"Zero";[Magenta]@

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.

The following example demonstrates how to display numbers greater than 100 in green, numbers less than minus 100 in yellow, and other numbers in cyan.

[>100][GREEN]#,##0;[<=-100][YELLOW]#,##0;[CYAN]#,##0

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.

Text and Numbers

CharacterMeaning
0Digit 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 ###.### format displays as 12.34.
?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.
.Decimal point.
,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.

Dates and Time

Format StringMeaning
mDisplay month number without leading zero
mmDisplay month number with leading zero
mmmDisplay short month name in current culture
mmmmDisplay full month name in current culture
dDisplay date number without leading zero
ddDisplay date number with leading zero
dddDisplay the abbreviated week day name
ddddDisplay the full week day name
yyDisplay the year as two digit number
YYYYDisplay the full year number
hDisplay hour without leading zero
hhDisplay hour including leading zero
mDisplay minute without leading zero
mmDisplay minute including leading zero
sDisplay second without leading zero
ssDisplay second including leading zero
[h]Elapsed time in hours
[m]Elapsed time in minutes
[s]Elapsed time in seconds
AM/PMForces hours to display in 12-hours clock, and displays AM or PM as necessary
am/pmSame as above, but displays lower-case am or pm
A/PSame as above, but displays A or P
a/pSame, 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.

See Also