Number Cell

You can use a number cell to enter double-precision floating point numbers as well as fractions. The display can show decimal numbers, integers, or fractions.

To set up the number cell and its properties, you can use the NumberCellType class in the GrapeCity.Wpf.SpreadSheet.CellType namespace.

Basic Formatting

For number cells, you can separately set the format for both input and display. The input format is applied when the cell is in edit mode, and the display format is applied when the cell is not in edit mode. The input format for numeric cells restricts user input to prevent data that does not match the format. There are two ways to set the format: using keywords and using field objects.

Input Formatting

• Set the Input Format with Keywords: To define how numbers are entered, use the FieldSet property and the NumberFieldSet class. You can set the desired format using a pattern of keywords in the constructor of the NumberFieldSet class. The following example code sets the input format of numeric cells using keywords.

  C#

  NumberCellType num = new NumberCellType();
  num.FieldSet = new NumberFieldSet("#,##0,,,-,");
  spreadSheet1.Workbook.ActiveSheet.Columns[2].CellType = num;
  spreadSheet1.Workbook.ActiveSheet.Columns[2].ColumnWidth = 250;

  VB

  Dim num As NumberCellType = New NumberCellType()
  num.FieldSet = New NumberFieldSet("#,##0,,,-,")
  spreadSheet1.Workbook.ActiveSheet.Columns(2).CellType = num
  spreadSheet1.Workbook.ActiveSheet.Columns(2).ColumnWidth = 250

• Set the Input Format with Field Objects: You can also customize input formatting by referencing specific field objects available in the NumberFieldSet class. Use the FieldSet property to obtain the NumberFieldSet that represents the input format, and then reference the field object with the properties of the NumberFieldSet class. Field objects representing the prefix, integer part, decimal point, fractional part, and suffix are already created in NumberFieldSet. So you don't need to create or delete them manually.

  
  

  C#

  NumberCellType num = new NumberCellType();
  num.FieldSet.SignPrefix.NegativePattern = "-";
  num.FieldSet.IntegerPart.GroupSizes.Add(new IntegerItem(3));
  num.FieldSet.IntegerPart.MinDigits = 1;
  spreadSheet1.Workbook.ActiveSheet.Columns[3].CellType = num;
  spreadSheet1.Workbook.ActiveSheet.Columns[3].ColumnWidth = 300;

  VB

  Dim num As NumberCellType = New NumberCellType()
  num.FieldSet.SignPrefix.NegativePattern = "-"
  num.FieldSet.IntegerPart.GroupSizes.Add(New IntegerItem(3))
  num.FieldSet.IntegerPart.MinDigits = 1
  spreadSheet1.Workbook.ActiveSheet.Columns(3).CellType = num
  spreadSheet1.Workbook.ActiveSheet.Columns(3).ColumnWidth = 300

Display Formatting

• Setting the Display Format with Keywords: To control how numbers are displayed, use the DisplayFieldSet property and the NumberDisplayFieldSet class. The format is defined similarly to the input format using keywords in the constructor of the NumberDisplayFieldSet class. The following example code sets the display format of numeric cells using keywords.

  C#

  NumberCellType num = new NumberCellType();
  num.DisplayFieldSet = new NumberDisplayFieldSet("[###: 1,000 yen],,,▲,");
  spreadSheet1.Workbook.ActiveSheet.Columns[4].CellType = num;
  spreadSheet1.Workbook.ActiveSheet.Columns[4].ColumnWidth = 300;

  VB

  Dim num As NumberCellType = New NumberCellType()
  num.DisplayFieldSet = New NumberDisplayFieldSet("[###: 1,000 yen],,,▲,")
  spreadSheet1.Workbook.ActiveSheet.Columns(4).CellType = num
  spreadSheet1.Workbook.ActiveSheet.Columns(4).ColumnWidth = 300

Character Limit

Additionally, the MaxValue and MinValue properties allow you to define the maximum and minimum numeric values that can be entered. If you specify a maximum and minimum value, the spin button will only allow increasing or decreasing the value within that defined range.

By default, negative numbers in a number cell are shown in red text. You can change the color of the negative text using the NegativeForeground property.

Watermark

You can also specify the text to be displayed in an empty cell. The text for an input state cell is set in the WatermarkNull property, while the text for a non-editing state cell is specified in the WatermarkDisplayNull property.

You can also set the text to be displayed when the value is zero. The input state text for zero is set in the WatermarkZero property, and the non-edit state text for zero is set in the WatermarkDisplayZero property.

C#

NumberCellType num = new NumberCellType();
num.DisplayFieldSet = new NumberDisplayFieldSet("#,##0,,,-,");
num.WatermarkDisplayNullForeground = new SolidColorBrush(Colors.LightGray);
num.WatermarkDisplayNull = "＜Enter Quantity＞";
num.WatermarkDisplayZero = "(0)";
spreadSheet1.Workbook.ActiveSheet.Columns[0].CellType = num;
spreadSheet1.Workbook.ActiveSheet.Columns[0].ColumnWidth = 250;

VB

Dim num As NumberCellType = New NumberCellType()
num.DisplayFieldSet = New NumberDisplayFieldSet("#,##0,,,-,")
num.WatermarkDisplayNullForeground = New SolidColorBrush(Colors.LightGray)
num.WatermarkDisplayNull = "＜Enter Quantity＞"
num.WatermarkDisplayZero = "(0)"
spreadSheet1.Workbook.ActiveSheet.Columns(0).CellType = num
spreadSheet1.Workbook.ActiveSheet.Columns(0).ColumnWidth = 250

Spin Function

Number cells include a spin button for incrementing or decrementing values. By default, the spin button is displayed as shown in the following image.

Users can increase or decrease the value using the following methods:

• Click the spin button

• Press the up and down arrow keys

• Use the mouse wheel

To enable the spin feature, set the AllowSpin property. You can also adjust how the spin buttons are displayed using the SpinButtonVisibility property. Additionally, the SpinMode property determines whether the value is increased or decreased by the digit where the caret is located, by field unit, or by the entire cell value. The SpinAllowWrap property determines whether the spin behavior can wrap when reaches maximum or minimum limit.