You can use a number cell for entering double-precision floating point numbers as well as fractions. You can display decimal numbers, integers, or fractions. The topics below discuss the various aspects of number cell formatting and calculation.

You use the NumberCellType class to set the number cell and its properties. Use the CurrencyCellType class to set the currency cell and its properties.

Setting Precision

Numbers are typically calculated and stored using the Double data type which provides an accuracy of about 15 digits. The cell can be formatted to display as many or as few digits as you want. For example, the following code would sum the values in the cell range A1:A5 and place the result in cell A6. The value stored in cell A6 would have full accuracy (up to the limits of the Double data type), but the text displayed in cell A6 would show the value rounded to the nearest tenths place (one decimal place).

NumberCellType ct = new NumberCellType();
ct.DecimalPlaces = 1;
spread.Sheets[0].Cells[5,0].CellType = ct;
spread.Sheets[0].Cells[5,0].Formula = "SUM(A1:A5)";

Number cells supports 15 significant digits of precision. This is a total of all digits, integral and fractional. For example, when you have 10 fractional digits, you limit the number of integer digits to the left of the decimal to 5 digits. Also, there is the possibility of floating point errors with the Double data type. For more accurate precision of large numbers or numbers with large fractional portions, consider using a currency cell which uses the Decimal data type and is not prone to floating point errors.

Formatting Numbers

You can customize the number cell to display the number as an integer or decimal with several formatting features as summarized in this table of properties. An example of the use of these properties is provided after the table.

A complete list of formatting properties can be found in the NumberCellType class. You can use code, the Properties Window, or the Spread Designer to set these properties.

Displaying Fractions

The number cell can display values in a fraction format, so 0.01 can be displayed as 1/100. Set the FractionMode property of the number cell to display values in the fraction format. You can type values in the cell as 0.01 or you can type 1/100 in the cell; both display as 1/100. The precision of the fraction can be set using the FractionDenominatorPrecision enumeration (such as to display fractions as quarters, 1/4, etc.) or the FractionDenominatorDigits to set the number of digits in the denominator, for 10s, 100s or 1000s or more. This table lists the fraction-related properties of the number cell.

Another way to set the fraction display is to set a value for the fraction custom format (using the FractionCustomFormat property). The default value is "# ???/???" which formats the number as an integer (#) followed by a three-digit fraction (???/???). The question marks after the slash determine the number of digits of denominator precision of which there can be from one to fifteen (because 15-digit precision is the maximum). With the custom format, you can also specify the denominator, such as "# ???/100" or "# ??/64". If FractionConvertWholeNumbers is set to true, then there is no integer to display and the entire number is displayed as a fraction.

The alignment of the display is determined by the alignment properties that are set for the cell. The number is not aligned based on the fraction display. (In the example below, the numbers are right aligned regardless of whether there is a fractional part or not.)

A complete list of fraction properties can be found in the NumberCellType class. You can use code, the Properties Window, or the Spread Designer to set these properties.

Using Spin Buttons

By default, no spin buttons are shown, but you can display spin buttons on the side of the cell when the cell is in edit mode. You can set various spin functions using the properties of the NumberCellType class that begin with the word Spin. Refer to Displaying Spin Buttons.

Using the Pop-Up Calculator

By default, in a number cell, if you double-click on the cell in edit mode at run-time, a pop-up calculator appears. You can specify the text that displays in the OK and Cancel buttons. For more information, refer to Customizing the Pop-Up Calculator Control. To prohibit the popping up of the calculator, cancel the FpSpread SubEditorOpening event. Handle this event and set the Cancel argument of the SubEditorOpeningEventArgs to True.

For more information on the properties and methods of the number cell type, refer to the NumberCellType class.

For more information on the currency cell type, refer to the Setting a Currency Cell.

Using the Properties Window

1. At design time, in the Properties window, select the Spread component.
2. Select the Sheets property.
3. Click the button to display the SheetView Collection Editor.
4. In the Members list, select the sheet in which the cells appear.
5. In the property list, select the Cells property and then click the button to display the Cell, Column, and Row Editor.
6. Select the cells for which you want to set the cell type.
7. In the property list, select the CellType property and choose the Number cell type.
8. Expand the list of properties under the CellType property. Select and set these specific properties as needed.
9. Click OK to close the Cell, Column, and Row Editor.
10. Click OK to close the SheetView Collection Editor.

Using Code for Formatting Numbers

1. Define the number cell by creating an instance of the NumberCellType class.
2. Set properties for the class.
3. Assign the number cell type to a cell or range of cells by setting the CellType property for a cell, column, row, or style to the NumberCellType object.

Example

This example sets a cell to be a numeric cell with certain formatting by assigning the NumberCellType object with defined formatting properties.

FarPoint.Win.Spread.CellType.NumberCellType nmbrcell = new FarPoint.Win.Spread.CellType.NumberCellType();
nmbrcell.DecimalSeparator = ",";
nmbrcell.DecimalPlaces = 5;
nmbrcell.LeadingZero = FarPoint.Win.Spread.CellType.LeadingZero.UseRegional;
nmbrcell.MaximumValue = 500.000;
nmbrcell.MinimumValue = -10.000;
fpSpread1.ActiveSheet.Cells[1, 1].CellType = nmbrcell;

Dim nmbrcell As New FarPoint.Win.Spread.CellType.NumberCellType()
nmbrcell.DecimalSeparator = ","
nmbrcell.DecimalPlaces = 5
nmbrcell.LeadingZero = FarPoint.Win.Spread.CellType.LeadingZero.UseRegional
nmbrcell.MaximumValue = 500.000
nmbrcell.MinimumValue = -10.000
FpSpread1.ActiveSheet.Cells(1, 1).CellType = nmbrcell

Using Code for Formatting Fractions

1. Define the number cell by creating an instance of the NumberCellType class.
2. Set the FractionMode property to true and other fraction properties as needed.
3. Assign the number cell type to a cell or range of cells by setting the CellType property for a cell, column, row, or style to the NumberCellType object.

Example

This example sets a cell to display numbers as fractions.

fpSpread1.ActiveSheet.Columns[0, 9].Width = 120;
FarPoint.Win.Spread.CellType.NumberCellType frac = new FarPoint.Win.Spread.CellType.NumberCellType();
frac.FractionMode = true;
frac.FractionConvertWholeNumbers = false;
frac.FractionDenominatorPrecision = FarPoint.Win.Spread.CellType.FractionDenominatorPrecision.Custom;
frac.FractionCustomFormat = "## ???/???";
frac.FractionDenominatorDigits = 3;
fpSpread1.ActiveSheet.Columns[0].CellType = frac;
fpSpread1.ActiveSheet.Columns[1].CellType = frac;
fpSpread1.ActiveSheet.Cells[0, 0].Value = 5.00;
fpSpread1.ActiveSheet.Cells[1, 0].Value = 5.01;
fpSpread1.ActiveSheet.Cells[2, 0].Value = 5.02;
fpSpread1.ActiveSheet.Cells[3, 0].Value = 5.03;
fpSpread1.ActiveSheet.Cells[4, 0].Value = 5.04;
fpSpread1.ActiveSheet.Cells[5, 0].Value = 5.05;
fpSpread1.ActiveSheet.Cells[6, 0].Value = 5.06;
fpSpread1.ActiveSheet.Cells[7, 0].Value = 5.07;
fpSpread1.ActiveSheet.Cells[8, 0].Value = 5.08;
fpSpread1.ActiveSheet.Cells[9, 0].Value = 5.09;
fpSpread1.ActiveSheet.Cells[0, 1].Value = 25.000;
fpSpread1.ActiveSheet.Cells[1, 1].Value = 25.011;
fpSpread1.ActiveSheet.Cells[2, 1].Value = 25.021;
fpSpread1.ActiveSheet.Cells[3, 1].Value = 25.031;
fpSpread1.ActiveSheet.Cells[4, 1].Value = 25.041;
fpSpread1.ActiveSheet.Cells[5, 1].Value = 25.051;
fpSpread1.ActiveSheet.Cells[6, 1].Value = 25.061;
fpSpread1.ActiveSheet.Cells[7, 1].Value = 25.071;
fpSpread1.ActiveSheet.Cells[8, 1].Value = 25.081;
fpSpread1.ActiveSheet.Cells[9, 1].Value = 25.091;

FpSpread1.ActiveSheet.Columns(0, 9).Width = 120
Dim frac As New FarPoint.Win.Spread.CellType.NumberCellType
frac.FractionMode = True
frac.FractionConvertWholeNumbers = False
frac.FractionDenominatorPrecision = FarPoint.Win.Spread.CellType.FractionDenominatorPrecision.Custom
frac.FractionCustomFormat = "# ???/???"
frac.FractionDenominatorDigits = 3
FpSpread1.ActiveSheet.Columns(0).CellType = frac
FpSpread1.ActiveSheet.Columns(1).CellType = frac
FpSpread1.ActiveSheet.Cells(0, 0).CellType = frac
FpSpread1.ActiveSheet.Cells(0, 0).Value = 5.00
FpSpread1.ActiveSheet.Cells(1, 0).Value = 5.01
FpSpread1.ActiveSheet.Cells(2, 0).Value = 5.02
FpSpread1.ActiveSheet.Cells(3, 0).Value = 5.03
FpSpread1.ActiveSheet.Cells(4, 0).Value = 5.04
FpSpread1.ActiveSheet.Cells(5, 0).Value = 5.05
FpSpread1.ActiveSheet.Cells(6, 0).Value = 5.06
FpSpread1.ActiveSheet.Cells(7, 0).Value = 5.07
FpSpread1.ActiveSheet.Cells(8, 0).Value = 5.08
FpSpread1.ActiveSheet.Cells(9, 0).Value = 5.09
FpSpread1.ActiveSheet.Cells(0, 1).Value = 25.000
FpSpread1.ActiveSheet.Cells(1, 1).Value = 25.011
FpSpread1.ActiveSheet.Cells(2, 1).Value = 25.021
FpSpread1.ActiveSheet.Cells(3, 1).Value = 25.031
FpSpread1.ActiveSheet.Cells(4, 1).Value = 25.041
FpSpread1.ActiveSheet.Cells(5, 1).Value = 25.051
FpSpread1.ActiveSheet.Cells(6, 1).Value = 25.061
FpSpread1.ActiveSheet.Cells(7, 1).Value = 25.071
FpSpread1.ActiveSheet.Cells(8, 1).Value = 25.081
FpSpread1.ActiveSheet.Cells(9, 1).Value = 25.091

This is what the result looks like in Spread.

Using the Spread Designer

1. Select the cell or cells in the work area.
2. In the property list, in the Misc category, select CellType. From the drop-down list, choose the Number cell type. Now expand the CellType property and various properties are available that are specific to this cell type. Select and set those properties as needed. The fraction properties are under the fraction tab.

   Or right-click on the cell or cells and select Cell Type. From the list, select Number. In the CellType editor, set the properties you need. Click Apply.

3. From the File menu choose Apply and Exit to apply your changes to the component and exit Spread Designer.

Copyright © GrapeCity, inc. All rights reserved.