Spread Windows Forms and Currency Cells

Currency cells are useful if you want to display numbers as monetary values. You can display currency values in Spread Windows Forms with a currency cell. You can customize formatting in the currency cell such as the currency symbol, separator character, decimal separator, and so on. By default, Spread uses the regional Windows settings (or options) of the machine on which it runs for the currency formatting. You can customize any of these currency formatting properties:

  • currency symbol (and whether to display it)
  • separator character (and whether to display it)
  • decimal symbol
  • whether to display a leading zero
  • positive value indicator (and whether to display it)
  • negative value indicator (and whether to display it)

Use the CurrencyCellType class to set the currency cell properties. The following table lists the properties for the currency cell:

ButtonAlign

Gets or sets where the buttons are displayed in the cell. (Inherited from FarPoint.Win.Spread.CellType.EditBaseCellType)

CurrencySymbol

Gets or sets the string for the currency symbol when displaying currency values.

DecimalPlaces

Gets or sets the number of decimal places. The maximum number is 16.

DecimalSeparator

Gets or sets the decimal character.

FixedPoint

Gets or sets whether to display zeros as placeholders in the decimal portion of the number for a fixed-point numeric display.

FocusPosition

Gets or sets the initial cursor position. (Inherited from FarPoint.Win.Spread.CellType.EditBaseCellType)

LeadingZero

Gets or sets whether leading zeros are displayed.

MaximumValue

Gets or sets the maximum value allowed for user entry.

MinimumValue

Gets or sets the minimum value allowed for user entry.

NegativeFormat

Gets or sets the format for displaying a negative value.

NegativeRed

Gets or sets whether negative numeric values are displayed in red.

NullDisplay

Gets or sets the text to display for null values. (Inherited from FarPoint.Win.Spread.CellType.EditBaseCellType)

OverflowCharacter

Gets or sets the character for replacing the value if it does not fit the width of the display.

PositiveFormat

Gets or sets the format for displaying a positive value.

ReadOnly

Gets or sets whether the cell is read-only (and thus cannot be modified). (Inherited from FarPoint.Win.Spread.CellType.EditBaseCellType)

Separator

Gets or sets the string used to separate thousands in a numeric value.

ShowCurrencySymbol

Gets or sets whether to display the currency symbol.

ShowSeparator

Gets or sets whether to display the thousands separator string.

ShrinkToFit

Gets or sets whether to shrink the text to fit the cell. (Inherited from FarPoint.Win.Spread.CellType.EditBaseCellType)

SpinButton

Gets or sets whether a spin button is displayed when editing.

SpinDecimalIncrement

Gets or sets the amount by which the value increments when using the spin buttons and the cursor is in the decimal portion.

SpinIntegerIncrement

Gets or sets the amount by which the value increments when using the spin buttons and the cursor is in the integer portion.

SpinWrap

Gets or sets whether the value wraps when the minimum or maximum is reached.

Static

Gets or sets whether the cell is static, which prohibits user interaction. (Inherited from FarPoint.Win.Spread.CellType.EditBaseCellType)

StringTrim

Gets or sets how to trim characters that do not fit in the cell. (Inherited from FarPoint.Win.Spread.CellType.EditBaseCellType)

TextOrientation

Gets or sets how text orients itself when painting the cell. (Inherited from FarPoint.Win.Spread.CellType.EditBaseCellType)

TextRotationAngle

Gets or sets the rotation angle of the text for the cell. (Inherited from FarPoint.Win.Spread.CellType.EditBaseCellType)

WordWrap

Gets or sets whether text that is too long to fit in the cell wraps to additional lines. (Inherited from FarPoint.Win.Spread.CellType.EditBaseCellType)

The MinimumValue and MaximumValue properties limit the value that the user enters when editing the cell. It does not affect the data model and does not the limit the cell getting a value by other means, for example, by means of a formula. The built-in operators and built-in functions for use in formulas return results as a Double (15 digits). Use the MinimumValue and MaximumValue properties to place range restrictions on user entry. For example, the following code limits user input to values between 0 and 100. C# 

FarPoint.Win.Spread.CellType.CurrencyCellType currencycell = new FarPoint.Win.Spread.CellType.CurrencyCellType();  
currencycell.DecimalPlaces = 2;  
currencycell.ShowCurrencySymbol = true;  
currencycell.MinimumValue = 0;  
currencycell.MaximumValue = 100;  
fpSpread1.Sheets[0].Cells[0, 0, 2, 2].CellType = currencycell;

VB 

Dim currencycell As New FarPoint.Win.Spread.CellType.CurrencyCellType()  
currencycell.DecimalPlaces = 2  
currencycell.ShowCurrencySymbol = True  
currencycell.MinimumValue = 0  
currencycell.MaximumValue = 100  
FpSpread1.Sheets(0).Cells(0, 0, 2, 2).CellType = currencycell

Use the MIN and MAX functions to place range restrictions on formula calculations. For example, the following code limits the summation calculation to values between 0 and 100. Type values in A1 and A2 to see the result. C# 

fpSpread1.Sheets[0].Cells[2, 4].Formula = "MAX(0,MIN(SUM(A1:A2), 100))";

VB 

FpSpread1.Sheets(0).Cells(2, 4).Formula = "MAX(0,MIN(SUM(A1:A2), 100))"

This example loads data from a data source and creates a column of currency cells. SpreadWinCurrency Column of Currency Cells C# 

//Add sample data  
string conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source= C:\\\Program Files (x86)\\\GrapeCity\\\Spread Studio 8\\\Common\\\nwind.mdb";  
//string sqlStr = "Select CompanyName, ContactName, ContactTitle, Country from Customers";  
string sqlStr = "Select OrderID, CustomerID, ShipName, Freight from Orders";  
//string sqlStr = "Select * from Orders";  
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(conStr);  
DataSet ds = new DataSet();  
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sqlStr, conn);  
da.Fill(ds);  
fpSpread1.ActiveSheet.DataAutoSizeColumns = true;              
fpSpread1.ActiveSheet.DataSource = ds;  

FarPoint.Win.Spread.CellType.CurrencyCellType currencycell = new FarPoint.Win.Spread.CellType.CurrencyCellType();  
currencycell.DecimalPlaces = 2;  
currencycell.ShowCurrencySymbol = true;  
fpSpread1.ActiveSheet.Columns[3].CellType = currencycell;  
fpSpread1.ActiveSheet.Columns[3].BackColor = Color.Beige;  
fpSpread1.Font = new Font("Calibri", 10);

VB 

'Add sample data  
Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source= C:\\Program Files (x86)\\GrapeCity\\Spread Studio 8\\Common\\nwind.mdb"  
Dim sqlStr As String = "Select OrderID, CustomerID, ShipName, Freight from Orders"  
Dim conn As New System.Data.OleDb.OleDbConnection(conStr)  
Dim ds As New DataSet()  
Dim da As New System.Data.OleDb.OleDbDataAdapter(sqlStr, conn)  
da.Fill(ds)  
FpSpread1.ActiveSheet.DataAutoSizeColumns = True  
FpSpread1.ActiveSheet.DataSource = ds  

Dim currencycell As New FarPoint.Win.Spread.CellType.CurrencyCellType()  
currencycell.DecimalPlaces = 2  
currencycell.ShowCurrencySymbol = True  
FpSpread1.ActiveSheet.Columns(3).CellType = currencycell  
FpSpread1.ActiveSheet.Columns(3).BackColor = Color.Beige  
FpSpread1.Font = New Font("Calibri", 10)

If you double-click on the currency cell in edit mode at run-time or press F4, a pop-up calculator appears by default. You can prevent the pop-up calculator by setting e.Cancel to True in the SubEditorOpening event. You can specify the text that displays on the OK and Cancel buttons with the SetCalculatorText method. This example displays the pop-up calculator with custom button text. SpreadWinSetCalc Pop-up Calculator C# 

FarPoint.Win.Spread.CellType.CurrencyCellType currencycell = new FarPoint.Win.Spread.CellType.CurrencyCellType();  
currencycell.DecimalPlaces = 2;  
currencycell.ShowCurrencySymbol = true;  
currencycell.SetCalculatorText("Accept", "Cancel");  
fpSpread1.Sheets[0].Cells[0, 0, 2, 2].CellType = currencycell;

VB 

Dim currencycell As New FarPoint.Win.Spread.CellType.CurrencyCellType()  
currencycell.DecimalPlaces = 2  
currencycell.ShowCurrencySymbol = True  
currencycell.SetCalculatorText("Accept", "Cancel")  
FpSpread1.Sheets(0).Cells(0, 0, 2, 2).CellType = currencycell

You can display spin buttons on the side of the cell when the cell is in edit mode. This example displays spin buttons and sets the decimal and integer increments. SpreadWinCurWrap Spin Buttons in Cell C# 

FarPoint.Win.Spread.CellType.CurrencyCellType currencycell = new FarPoint.Win.Spread.CellType.CurrencyCellType();  
currencycell.DecimalPlaces = 2;  
currencycell.ShowCurrencySymbol = true;  
currencycell.SpinButton = true;  
currencycell.SpinDecimalIncrement = 0.5F;  
currencycell.SpinIntegerIncrement = 5;  
currencycell.SpinWrap = true;  
fpSpread1.Sheets[0].Cells[1, 1].CellType = currencycell;  
fpSpread1.Sheets[0].Rows[1].Height = 40;  
fpSpread1.Sheets[0].Columns[1].Width = 100;

VB 

Dim currencycell As New FarPoint.Win.Spread.CellType.CurrencyCellType()  
currencycell.DecimalPlaces = 2  
currencycell.ShowCurrencySymbol = True  
currencycell.SpinButton = True  
currencycell.SpinDecimalIncrement = 0.5F  
currencycell.SpinIntegerIncrement = 5  
currencycell.SpinWrap = True  
FpSpread1.Sheets(0).Cells(1, 1).CellType = currencycell  
FpSpread1.Sheets(0).Rows(1).Height = 40  
FpSpread1.Sheets(0).Columns(1).Width = 100

This example sets a negative color and the positive and negative format for a currency cell. C# 

FarPoint.Win.Spread.CellType.CurrencyCellType currencycell = new FarPoint.Win.Spread.CellType.CurrencyCellType();  
currencycell.DecimalPlaces = 2;  
currencycell.ShowCurrencySymbol = true;  
currencycell.NegativeRed = true;  
currencycell.NegativeFormat = FarPoint.Win.Spread.CellType.CurrencyNegativeFormat.SignSymbolBefore;  
currencycell.PositiveFormat = FarPoint.Win.Spread.CellType.CurrencyPositiveFormat.CurrencySymbolBefore;  
fpSpread1.Sheets[0].Cells[1, 1].CellType = currencycell;

VB 

Dim currencycell As New FarPoint.Win.Spread.CellType.CurrencyCellType()  
currencycell.DecimalPlaces = 2  
currencycell.ShowCurrencySymbol = True  
currencycell.NegativeRed = True  
currencycell.NegativeFormat = FarPoint.Win.Spread.CellType.CurrencyNegativeFormat.SignSymbolBefore  
currencycell.PositiveFormat = FarPoint.Win.Spread.CellType.CurrencyPositiveFormat.CurrencySymbolBefore  
FpSpread1.Sheets(0).Cells(1, 1).CellType = currencycell

You can also create currency cells in the Spread Designer. Select the cell or cells, right-click on the cells, and then select Cell Types. Another way to set the cell type in the designer is to use the Set CellType option in the Cell Type section of the Home menu. SpreadWinDesCell Cell Type Dialog in Designer SpreadWinDesCell1 Currency Cell Menu

MESCIUS inc.

comments powered by Disqus