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 Spread API uses Excel's number formats to display various cell number formats.

The following formats are supported by Spread for WinForms:

• General Format
• Fraction Format
• Number Format
• Percentage Format
• Scientific Format
• Currency Format
• Time Format
• Date Format
• DateTime Format
• Accounting Format
• Color Format
• Text Format
• Special Format

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 in the cell.

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:

• With Decimals: If a number is longer than 11 characters including the decimal place, then the decimals are rounded to show a maximum of 11 characters.

  For example, 123456.7891234 is rounded off to 123456.7891

• Without Decimals: If a number is longer than 11 characters and there are no decimals, the number is changed to Scientific format.

  For example, 123451234512 is rounded off to 1.23451E+11

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

• Decimals are rounded to show only the number that will fit the column.
• The number will be rounded to an integer if there is no room for decimals.
• The number will change to Scientific format if the rounded integer does not fit.
• The cell will display number signs if the number can't be displayed in Scientific format.

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 "()".

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

var worksheet = fpSpread1_Sheet1.AsWorksheet();

worksheet.SetValue(0, 0, 8.333);
worksheet.Cells["A1"].NumberFormat = "# ?/?";

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 "()".

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

var worksheet = fpSpread1_Sheet1.AsWorksheet();

worksheet.SetValue(0, 0, 1234);
worksheet.Cells["A1"].NumberFormat = "#,##0";

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 "()".

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

var worksheet = fpSpread1_Sheet1.AsWorksheet();

worksheet.SetValue(0, 0, 0.05);
worksheet.Cells["A1"].NumberFormat = "0%";

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 "()".

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

var worksheet = fpSpread1_Sheet1.AsWorksheet();

worksheet.SetValue(0, 0, 12345678901);
worksheet.Cells["A1"].NumberFormat = "0.00E+00";

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 "()".

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

var worksheet = fpSpread1_Sheet1.AsWorksheet();

worksheet.SetValue(0, 0, -1235);
worksheet.Cells["A1"].NumberFormat = "$#,##0_);[Red]($#,##0)";

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

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

var worksheet = fpSpread1.ActiveSheet.AsWorksheet();
worksheet.SetValue(0, 0, DateTime.Now);
worksheet.Cells["A1"].NumberFormat = "h:mm:ss";

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:

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 "九/十一/二〇一八".

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

var worksheet = fpSpread1_Sheet1.AsWorksheet();

worksheet.Cells["A1"].Text = "1/1/2021";
worksheet.Cells["A1"].NumberFormat = "dd-mm-yyyy";

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.

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

var worksheet = fpSpread1_Sheet1.AsWorksheet();

worksheet.Cells["A1"].Text = "2019/02/20 12:30:00";
worksheet.Cells["A1"].NumberFormat = "yyyy/m/d h:mm";

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.

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

var worksheet = fpSpread1_Sheet1.AsWorksheet();

worksheet.SetValue(0, 0, -1513);
worksheet.Cells["A1"].NumberFormat = "$* #,##0";

Dim worksheet As GrapeCity.Spreadsheet.IWorksheet = FpSpread1_Sheet1.AsWorksheet()

worksheet.SetValue(0, 0, -1513)
worksheet.Cells("A1").NumberFormat = "$* #,##0"

Color Format

The Color Format displays data based on the color criteria that affects the foreColor.

It supports color string names as well as color indices:

• 8 Named colors: [Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]
• 56 Indexed colors: [Color1][Color2]….[Color56]

While specifying color formats in code, the name of the color comes first in the code and is enclosed in square brackets. Also, to show that the number format will be applied only if a specified condition is met, the criteria is enclosed in square brackets.

The condition will consist of a comparison operator and a value. For instance, the following number format will display numbers that are less than 50 in Yellow font and numbers that are greater than or equal to 50 in Magenta font.

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

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";

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.