Spread Windows Forms 15.0
Spread Windows Forms 15.0 Product Documentation / Developer's Guide / Cell Types / Working with Editable Cell Types / Setting a Number Cell
In This Topic
    Setting a Number Cell
    In This Topic

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

    Copy Code
    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.

    Property Description
    DecimalPlaces Sets the number of decimal places in the display of the number, for a decimal number.
    DecimalSeparator Sets the decimal character for the display of a decimal number.
    FixedPoint Sets whether to display zeros as placeholders in the decimal portion of the number for a fixed-point numeric display.
    LeadingZero Sets whether leading zeros are displayed.
    MaximumValue Sets the maximum value allowed for user input.
    MinimumValue Sets the minimum value allowed for user input.
    NegativeFormat Sets how the value is formatted for negative values.
    NegativeRed Sets whether negative numeric values are displayed in red.
    OverflowCharacter Sets the character to use to replace the value if it does not fit the width of the display.
    Separator Sets the string used to separate thousands in a numeric value.
    ShowSeparator Sets whether to display the thousands separator string.

    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.

    Property Description
    FractionMode Sets whether values are represented as fractions.
    FractionConvertWholeNumbers Sets whether to convert whole numbers to fractions when values are displayed as fractions.
    FractionCustomFormat Sets how values are displayed as fractions with custom formatting. To use the custom format, set the FractionDenominatorPrecision property to Custom.
    FractionDenominatorDigits Sets the number of digits when values are displayed as fractions.
    FractionDenominatorPrecision Sets the precision when values are displayed as fractions.
    FractionRenderOnly Sets 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 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.


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

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

    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.


    This example sets a cell to display numbers as fractions.

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

    example of fraction cells

    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.
    See Also