Spread Windows Forms 15.0
Spread Windows Forms 15.0 Product Documentation / Developer's Guide / Cells / Working with Cell Formats
In This Topic
    Working with Cell Formats
    In This Topic

    Spread for Winforms enables you to format the data displayed in a cell. You can format the values in cells as fractions, currency, percentages, decimals, scientific data, date, time, and so on.

    The following formats are supported by Spread for Winforms:

    General Format

    This format does not have any specific number format. Numbers that are formatted with the General format are displayed just the way they are typed.

    However, this format automatically rounds the numbers with decimals if it exceeds a specific character limit in the cell or if the cell is not wide enough to show the entire number.

    One of the following scenarios can be observed when inputting long numbers with General format:

    Users can also observe the following behavior when reducing column widths of cells with General format applied:

    Note: This behavior can be disabled by using formats other than "General" like Number and Accounting.

    Fraction Format

    This format displays numbers as fractions like mixed number fractions 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 the 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 the (-) 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

    The following code example shows how to set fraction format to a cell containing a value.

    C#
    Copy Code
    var worksheet = fpSpread1_Sheet1.AsWorksheet();
    
    worksheet.SetValue(0, 0, 8.333);
    worksheet.Cells["A1"].NumberFormat = "# ?/?";
    
    Visual Basic
    Copy Code
    Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = FpSpread1_Sheet1.AsWorksheet()
    
    worksheet.SetValue(0, 0, 8.333)
    worksheet.Cells("A1").NumberFormat = "# ?/?"
    

    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 the "Use 1000 Separator (,)" option and mentioning the number of decimal places to display. To enter a negative number value, use the (-) sign before or input the number into parenthesis "()".

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

    The following code example shows how to set the number format to a cell containing a value.

    C#
    Copy Code
    var worksheet = fpSpread1_Sheet1.AsWorksheet();
    
    worksheet.SetValue(0, 0, 1234);
    worksheet.Cells["A1"].NumberFormat = "#,##0";
    
    Visual Basic
    Copy Code
    Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = FpSpread1_Sheet1.AsWorksheet()
    
    worksheet.SetValue(0, 0, 1234)
    worksheet.Cells("A1").NumberFormat = "#,##0"
    

    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 displays the result with a symbol. You can set the number of decimal places to be displayed in the result. To enter a negative number value, use the (-) sign before the number or input the number into parenthesis "()".

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

    The following code example shows how to set percentage format to a cell containing a value.

    C#
    Copy Code
    var worksheet = fpSpread1_Sheet1.AsWorksheet();
    
    worksheet.SetValue(0, 0, 0.05);
    worksheet.Cells["A1"].NumberFormat = "0%";
    
    Visual Basic
    Copy Code
    Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = FpSpread1_Sheet1.AsWorksheet()
    
    worksheet.SetValue(0, 0, 0.05)
    worksheet.Cells("A1").NumberFormat = "0%"
    

    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. 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 that 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 the (-) sign before the number or input the number into parenthesis "()".

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

    The following code example shows how to set scientific format to a cell containing a value.

    C#
    Copy Code
    var worksheet = fpSpread1_Sheet1.AsWorksheet();
    
    worksheet.SetValue(0, 0, 12345678901);
    worksheet.Cells["A1"].NumberFormat = "0.00E+00";
    
    Visual Basic
    Copy Code
    Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = FpSpread1_Sheet1.AsWorksheet()
    
    worksheet.SetValue(0, 0, 12345678901)
    worksheet.Cells("A1").NumberFormat = "0.00E+00"
    

    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 to format cells with the currency format. To enter a negative fraction value, use the (-) 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

    The following code example shows how to set currency format to a cell containing a value.

    C#
    Copy Code
    var worksheet = fpSpread1_Sheet1.AsWorksheet();
    
    worksheet.SetValue(0, 0, -1235);
    worksheet.Cells["A1"].NumberFormat = "$#,##0_);[Red]($#,##0)";
    
    Visual Basic
    Copy Code
    Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = FpSpread1_Sheet1.AsWorksheet()
    
    worksheet.SetValue(0, 0, -1235)
    worksheet.Cells("A1").NumberFormat = "$#,##0_);[Red]($#,##0)"
    

    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 a 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

    The following code example shows how to set the time format to a cell containing a value.

    C#
    Copy Code
    var worksheet = fpSpread1.ActiveSheet.AsWorksheet();
    worksheet.SetValue(0, 0, DateTime.Now);
    worksheet.Cells["A1"].NumberFormat = "h:mm:ss";
    
    Visual Basic
    Copy Code
    Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = FpSpread1_Sheet1.AsWorksheet()
    
    worksheet.SetValue(0, 0, DateTime.Now)
    worksheet.Cells("A1").NumberFormat = "h:mm:ss"
    

    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 at least 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 the year is automatically set to the 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 the 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 a 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
    yyyy/m/d 2019/02/20 2019/2/20
    yyyy"年"m"月" 2019/02/20 2019年2月
    m"月"d"日" 2019/02/20 2月20日

    The following code example shows how to set the date format to a cell containing a value.

    C#
    Copy Code
    var worksheet = fpSpread1_Sheet1.AsWorksheet();
    
    worksheet.Cells["A1"].Text = "1/1/2021";
    worksheet.Cells["A1"].NumberFormat = "dd-mm-yyyy";
    
    Visual Basic
    Copy Code
    Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = FpSpread1_Sheet1.AsWorksheet()
    
    worksheet.Cells("A1").Text = "1/1/2021"
    worksheet.Cells("A1").NumberFormat = "dd-mm-yyyy"
    

    DateTime Format

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

    Code Format Input Value Display Value
    yyyy/m/d h:mm 2019/02/20 12:30:00 2019/2/20 12:30

    The following code example shows how to set the datetime format to a cell containing a value.

    C#
    Copy Code
    var worksheet = fpSpread1_Sheet1.AsWorksheet();
    
    worksheet.Cells["A1"].Text = "2019/02/20 12:30:00";
    worksheet.Cells["A1"].NumberFormat = "yyyy/m/d h:mm";
    
    Visual Basic
    Copy Code
    Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = FpSpread1_Sheet1.AsWorksheet()
    
    worksheet.Cells("A1").Text = "2019/02/20 12:30:00"
    worksheet.Cells("A1").NumberFormat = "yyyy/m/d h:mm"
    

    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 an asterisk (*) symbol to denote repeat characters. 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

    The following code example shows how to set the accounting format to a cell containing a value.

    C#
    Copy Code
    var worksheet = fpSpread1_Sheet1.AsWorksheet();
    
    worksheet.SetValue(0, 0, -1513);
    worksheet.Cells["A1"].NumberFormat = "$* #,##0";
    
    Visual Basic
    Copy Code
    Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = FpSpread1_Sheet1.AsWorksheet()
    
    worksheet.SetValue(0, 0, -1513)
    worksheet.Cells("A1").NumberFormat = "$* #,##0"
    

    Color Format

    This format displays data based on color criteria that affect the foreColor. It supports color string names as well as color index, ranging from color 1 to color 56.

    Color formatting in cell

    The following code example shows how to set color formatting by index to change the color of the cells according to the value range.

    C#
    Copy Code
    var worksheet = fpSpread1_Sheet1.AsWorksheet();
    
    worksheet.SetValue(0, 0, 100);
    worksheet.SetValue(0, 1, 200);
    worksheet.SetValue(0, 2, 300);
    worksheet.SetValue(0, 3, 400);
    worksheet.SetValue(0, 4, 500);
    worksheet.SetValue(0, 5, 600);
    
    worksheet.Range("A1:F1").NumberFormat = "[color44][<300]0.0;[color3][>400]0.0;[color45]0.0";
    
    Visual Basic
    Copy Code
    Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = FpSpread1_Sheet1.AsWorksheet()
    
    worksheet.SetValue(0, 0, 100)
    worksheet.SetValue(0, 1, 200)
    worksheet.SetValue(0, 2, 300)
    worksheet.SetValue(0, 3, 400)
    worksheet.SetValue(0, 4, 500)
    worksheet.SetValue(0, 5, 600)
    
    worksheet.Range("A1:F1").NumberFormat = "[color44][<300]0.0;[color3][>400]0.0;[color45]0.0"
    

    Text Format

    This format displays data in Text format. In this format, the value of the cells is treated as text even when a number is entered. The 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.