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 desired. 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 round 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 given below.
|
Property |
Description |
|---|---|
|
DecimalPlaces |
Set the number of decimal places in the display of the number, for a decimal number. |
|
DecimalSeparator |
Set the decimal character for the display of a decimal number. |
|
FixedPoint |
Set whether to display zeros as placeholders in the decimal portion of the number for a fixed-point numeric display. |
|
LeadingZero |
Set whether leading zeros are displayed. |
|
MaximumValue |
Set the maximum value allowed for user input. |
|
MinimumValue |
Set the minimum value allowed for user input. |
|
NegativeFormat |
Set how the value is formatted for negative values. |
|
NegativeRed |
Set whether negative numeric values are displayed in red. |
|
OverflowCharacter |
Set the character to use to replace the value if it does not fit the width of the display. |
|
Separator |
Set the string used to separate thousands in a numeric value. |
|
ShowSeparator |
Set whether to display the thousands separator string. |
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.
Displaying Fractions
The number cell can display values in a fraction format, so 0.01 could 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.
|
Property |
Description |
|---|---|
|
FractionMode |
Set whether values are represented as fractions. |
|
FractionConvertWholeNumbers |
Set whether to convert whole numbers to fractions when values are displayed as fractions. |
|
FractionCustomFormat |
Set how values are displayed as fractions with custom formatting. To use the custom format, set the FractionDenominatorPrecision to Custom. |
|
FractionDenominatorDigits |
Set the number of digits when values are displayed as fractions. |
|
FractionDenominatorPrecision |
Set the precision when values are displayed as fractions. |
|
FractionRenderOnly |
Set whether to allow fractions in edit mode when values are displayed as fractions. |
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 FractionConvertWholeNumber 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 whatever alignment properties 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 in the side of the cell when the cell is in edit mode. You can set various spin functions using the properties of the NumberCellType 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 on 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.
See Also
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.
For more information on other editable cell types, refer to Working with Editable Cell Types.
Using the Properties Window
- At design time, in the Properties window, select the Spread component.
- Select the Sheets property.
- Click the button to display the SheetView Collection Editor.
- In the Members list, select the sheet in which the cells appear.
- In the property list, select the Cells property and then click the button to display the Cell, Column, and Row Editor.
- Select the cells for which you want to set the cell type.
- In the property list, select the CellType property and choose the Number cell type.
- Expand the list of properties under the CellType property. Select and set these specific properties as needed.
- Click OK to close the Cell, Column, and Row Editor.
- Click OK to close the SheetView Collection Editor.
Using Code for Formatting Numbers
- Define the number cell by creating an instance of the NumberCellType class.
- 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.
| C# |  Copy Code |
|---|---|
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; |
|
| VB | Copy Code |
|---|---|
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
- Define the number cell by creating an instance of the NumberCellType class.
- Set the FractionMode property to true and other fraction properties as needed.
- 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.
| C# | Copy Code |
|---|---|
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; |
|
| VB | Copy Code |
|---|---|
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
- Select the cell or cells in the work area.
- 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.
- From the File menu choose Apply and Exit to apply your changes to the component and exit Spread Designer.