Spread Windows Forms 13.0 Product Documentation
Spread Windows Forms 13.0 Product Documentation / Spread Designer Guide / Spread Designer Editors / Formatter and Editor
In This Topic
    Formatter and Editor
    In This Topic

    The Format Cells dialog of Spread Designer enables you to format the data displayed in a cell. Using the six tabs available in this dialog, users can modify number formatting, alignment, font, border, fill, and protection.

     In order to launch the Format Cells dialog from the Spread Designer, follow these steps:

    1. Select a cell or a cell range in the worksheet that you wish to format or edit.
    2. Right-click on the selected cell or the cell range.
    3. Choose the option Format Cells from the displayed menu.
    4. The Format Cells dialog will appear on the screen.
    Formatter and Editor

    Using the Number tab of Format Cells dialog, you can format the values in cell as fractions, currency, percentages, decimals, scientific data, date, time etc. Selecting the Custom category enables you to create custom code formats based on the available code formats. Following formats are supported by Spread for WinForms:

    You can also set conditional criteria for a cell or cell range to determine the formatting of the cell value based on the outcome of that criteria. In the following example, three criteria have been defined for range A1:C1; where the font color changes based on the cell value. 

    C#
    Copy Code
    fpSpread1.AsWorkbook().ActiveSheet.Range("A1:C1").FormattedValue = new int[,] { { 1, 2, 3 } };
    fpSpread1.AsWorkbook().ActiveSheet.Range("A1:C1").NumberFormat= "[Red][<2]0.00;[Blue][>2]0.0;[Green]0.000";

    VB
    Copy Code
    fpSpread1.AsWorkbook().ActiveSheet.Range("A1:C1").FormattedValue = New Integer(,) {{ 1, 2, 3 }}
    fpSpread1.AsWorkbook().ActiveSheet.Range("A1:C1").NumberFormat= "[Red][<2]0.00;[Blue][>2]0.0;[Green]0.000"

    Fraction Format

    This format displays numbers as fractions like mixed number fraction or other types of fractions in two different layouts - # ?/? and # ??/??. For this format, Spread for WinForms allows you to select either number of decimal places to display result in or the nearest place to round off the result. In case you enter data in improper fractions, it will be auto calculated to the integer part and proper fraction. To enter a negative fraction value, use (-) sign before entering the mixed number or input the mixed number into parenthesis "()".

    Code Format Input Value Display Value
    # ?/? 8.333 8 1/3
    # ??/?? 0.846 11/13

    Number Format

    This format displays data in Number format in two different layouts - #,##0 and #,##0.00. You can display data with thousand separator and decimal places by checking "Use 1000 Separator (,)" option and mentioning number of decimal places to display. To enter a negative number value, use (-) sign before or input number into parenthesis "()".

    Code Format Input Value Display Value
    #,##0 1231 1,231
    #,##0.00 4561 4,561.00

    Percentage Format

    This format displays data in Percentage format in two different layouts - 0% and 0.00%. This format multiplies the cell value by 100 and display the result with a % symbol. You can set number of decimal places to be display in the result. To enter a negative number value, use (-) sign before the number or input number into parenthesis "()".

    Code Format Input Value Display Value
    0% 0.05 5%
    0.00% 0.8 80.00%

    Scientific Format

    This format displays data in Scientific format in only one layout - 0.00E+00. The scientific format displays numbers in an exponential notation by replacing parts of the number with E+n; where E stands for the exponent that multiplies the preceding number by 10 to the nth power. In order to display a number in scientific (exponential) format, you need to enter numbers in the form "mEn where coefficient m refers to any real number, while the exponent n is an integer which corresponds to the number of places that the decimal point was moved. You can replace the character E by E+; e by e+ ; and E- by e-. To enter a negative number value, use (-) sign before the number or input number into parenthesis "()".

    Code Format Input Value Display Value
    0.00E+00 12345678901 1.23E+10

    Currency Format

    This format displays data in Currency format in two different layouts - $#,##0_);[Red]($#,##0) and $#,##0.00_);[Red]($#,##0.00). The position of the currency symbol is based on the default language of the MS Office programs. You need to enter the correct symbol position of the currency in order to format cells with the currency format. To enter a negative fraction value, use (-) sign before entering the mixed number or input the mixed number into parenthesis "()".

    Code Format Input Value Display Value
    $#,##0_);[Red]($#,##0) -1235 ($1,235)
    $#,##0.00_);[Red]($#,##0.00) 4597 $4,597.00

    Time Format

    This format displays data in Time format in six different layouts - h:mm tt, h:mm:ss tt, H:mm, H:mm:ss, [h]:mm:ss and mm:ss.0. Only integer values are accepted for the hours, minutes and seconds. To enter time value in either Japanese, Chinese or Korean language, combine the value with the time text like 時 and 分.

    Code Format Input Value Display Value
    h:mm tt 21:05 9:05 tt
    h:mm:ss tt 7:49:15 7:49:15 tt
    h:mm 17:25 17:25
    h:mm:ss 3:19 3:19:00
    [h]:mm:ss 236:34 20:34:00
    mm:ss.0 5:05 05:00.0

    Date Format

    This format displays data in Date format in four different layouts - m/d/yyyy, d-mmm-yy, d-mmm and mmm-yy. To use this format, you need to enter values in atleast one combination - date and month, date and year or complete value of date, month and year. When you enter values only for date and month, the value of year is automatically set to current year. As a separating character, Spread for WinForms supports both (-) sign and (/) sign. You can use these separating characters in any combination.

    The data values for date, month and year can be entered based on the following table:

    Months m 1-12
    Months mm 01-12
    Months mmm Jan-Dec
    Months mmmm January-December
    Days d 1-31
    Days dd 01-31
    Years yy 00-99
    Years yyyy 1900-9999

    If you enter any year from 0-29, the cell value is automatically formatted to 2000-2029. Similarly, if you enter any year from 30-99, the value is formatted to 1930-1999. You can enter text value for month in both upper case and low case. To enter date value in either Japanese, Chinese or Korean language, combine the value with the date text like 時 and 分.

    Spread for WinForms also supports the use of [DBNumX] modifier to display data in East Asia numeric format. For example, in Japanese locale, if you use "[DBNum1][$-411]d/mm/yyyy" format with the value 43413, the formatted text will be "九/十一/二〇一八".

    Code Format Input Value Display Value
    m/d/yyyy 12/10/2018 10/12/2018
    d-mmm-yy 14 Mar 2018 14-Mar-18
    d-mmm 3 June 3-Jun
    mmm-yy September 2018 Sep-18

    DateTime Format

    This format displays data in DateTime format, with only one layout - m/d/yyyy h:mm. When you enter date value combined with time value, the data is automatically formatted in DateTime format (m/d/yyyy h:mm). The value of date can be placed before or after the time value. To enter a value for time with format "hour:" or "hour:minute", the date value needs to be combined with day, month, and year. If the data only consists of date value, it will be formatted as Date format.

    Code Format Input Value Display Value
    m/d/yyyy h:mm 1:1 jan-01 1/1/2001 1:01

    Accounting Format

    This format displays data in Accounting format in two different layouts - $* #,##0 and $* #,##0.00. In this format, the currency symbols and decimal points are aligned in a column. This is implemented using asterisk (*) symbol to denote repeat character. By default, code formats use "* " (asterisk with a space after) to enter spaces in between the currency symbol and the value, but you can replace " " (space) with any other character. 

    Code Format Input Value Display Value
    $* #,##0 -1513 -$                                                                                   1,513
    $* #,##0.00 2583 $                                                                                2,583.00

    Text Format

    This format displays data in Text format. In this format, value of the cells is treated as text even when a number is entered. Result is displayed in the cell exactly as the data is entered.

    Special Format

    This format displays data in Special format in four different layouts - Zip Code, Zip Code + 4, Phone Number and Social Security Number.