Managing Data Types

Data types define the type of information that appears in a cell, how that information is displayed, and how the user can interact with it.

These tasks of working with cell types are organized into these broad categories:

Currency

DateTime

Factor

General

Null

Number

Percent

Text

 

 

Currency

This data type formats currency values and allows you to set the currency symbol, the decimal character, the number of decimal places, and the group separator (if you wish to override the Windows Regional Settings and Regional Options). You can also display leading zeros and set the minimum and maximum values allowed. A currency column displays the numeric currency values with formatting that you can customize including currency symbol, separator character and others.

celltype-currency

 

By default, VisualStat uses the regional Windows settings (or options) for the formatting of currency. You can specify these in a currency cell. These settings are:

currency symbol (and whether to display it)

separator character (and whether to display it)

decimal symbol

whether to display a leading zero

positive currency format

negative currency format

 

 

DateTime

This type can be used to display Date or Time or both. In MS Excel, the Date cell type and the Time cell type are different cell types; but in VisualStat, the DateTime data type takes care of both. You determine the format of the date and time to display.

celltype-datetime

 

The default values use the Regional Settings or Regional Options in the Windows environment. You can set several format options for date and time using the properties in this data type.

When the selected column is in DateTime format, use the Property Page and select Column Format from the combo box list to set properties you want. The DateTimeFormat property specifies the formatting for date and time.

Member

Description

ShortDate

Uses the abbreviated form for the date

LongDate

Uses the long form for the date

ShortDateWithTime

Uses the abbreviated form for the date and time

LongDateWithTime

Uses the long form for the date and time

TimeOnly

Uses the time only (no date)

UserDefined

Uses the user-defined date and time format

 

By default, in a date-time cell, if you double-click on the cell in edit mode, a pop-up calendar (or clock) appears.

 

Pop-Up Calendar Control


Pop-Up Clock Control

celltype-datetime-popup-date


celltype-datetime-popup-time

 

 

 

Factor

A factor column displays an editable drop-down list, allowing user to type in values as well as choosing from a displayed list. You can specify the list of items, the number that is displayed at any time, and whether the cell is editable by the user.

celltype-factor

 

Here is a summary of the appearance properties that you can use to customize the combo box.

Member

Description

BackgroundImage

This property lets you set an image to paint in the background of the edit portion of the combo box.

ButtonAlign

This property lets you set where buttons are displayed.

ItemData

This property lets you set item data, which is different from the items that are displayed, for the drop-down list in the combo box.

Items

This property lets you set items for the drop-down list in the combo box.

ListAlignment

This property lets you set the side of the cell on which the list aligns.

ListOffset

This property lets you set how many pixels to offset the list from the aligned edge of the cell.

ListWidth

This property lets you set the width (in pixels) of the drop-down list.

MaxDrop

This property lets you set the number of items to display at one time in the list portion. If there are more items than are displayed, a vertical scroll bar is displayed.

MaxLength

This property lets you set the maximum number of characters allowed in the combo box cell.

 

 

General

The general cell is the default cell type for the cells in the sheets. Unless you specify another cell type, it is the general cell type that is assigned to the cells. The general cell can be used as is for entering text or numbers where formatting is not critical or the type of data is not tied to a specific data type. For specific cell types where formatting is important, see the Currency, DateTime, Number, and Percent data type.

With the general cell you can format the displayed values regardless of the user put. The general cell type includes a formatter that takes the data entered by the user and coerces it into one of the known formats and data types. This frees the user from worrying about setting cell types because the general cell type handle inputs of many kinds.

If you want to allow the user to enter data in any acceptable format, but want it to be formatted and displayed in a specific way, you can adjust the formatter for the general cell type. To do this, specify a format string for the general cell and the general formatter parse the user-entered data, but when the data is displayed, your custom format is used rather than the format used by the end user. If you want to customize how the general cell displays the data, you can work with the FormatString property.

Example:

FormatString = "#,###.00"

 

 

Null

This type is used when cell type is nothing.

 

 

Number

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

You use the Property Page to set the number cell and its properties. Use the Currency cell type to set the currency cell and its properties.

Setting the number cell type includes these tasks:

Setting Precision

Formatting Numbers

Displaying Fractions

Using the Spin Buttons

Using the Pop-up Calendar

 

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.

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

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.

 

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

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

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.

 

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 by using the the SpinButton property.

 

Using the Pop-Up Calculator

By default, in a number cell, if you double-click or presses F4 on the cell in edit mode, a pop-up calculator appears, as shown below.

Pop-Up Calculator Control

celltype-popup-calculator

 

You can use the calculator to type a number or to perform a calculation. The result from the calculator is placed in the numeric cell. The cell types that allow a calculator pop-up are:

Currency

Number

Percent

 

 

Percent

This data type formats values as percentages and restricting inputs to percentage numeric values. It allows you to set the percent symbol and the percent format (if you wish to override the Windows Regional Settings and Regional Options).

By default, in a percent cell, if you double-click on the cell in edit mode, a pop-up calculator appears.

 

 

Text

You can create a text column that allows only text to be displayed or treats the contents of a cell as only text.

You can also specify if the text shows up as all lower case, upper case, or normal with the CharacterCasing property. The CharacterSet property allows you to specify numbers only, letters only, numbers and letters, or any ASCII characters.

 

 

Mask

You can use a mask column for masking characters to limit user entry. You specify which subsets of characters are allowed for each item in the mask.

You can define how the mask appears, with literals displayed exactly as typed and placeholders showing the places for user entry. To create a mask, set the Mask property to a string of mask characters.

Each mask character represents a position in which the user can type a character.

 

 

 

CheckBox

You can display a check box in a column using the check box cell type.

A check box column displays, by default, a small check box that can have one of three states, checked, unchecked, or grayed. You can customize the check box by setting the text, determining the operation of the check box, and setting pictures in place of the standard check box pictures.