Data Format
Data format feature requires the following namespace.
using unvell.ReoGrid.DataFormat;
To specify data format, use SetRangeDataFormat
method of worksheet.
var sheet = reoGridControl.CurrentWorksheet;
sheet.SetRangeDataFormat(range, formatFlag, [Optional] object argument = null)
For example:
sheet.SetRangeDataFormat("A1:B2", CellDataFormatFlag.Number,
new NumberDataFormatter.NumberFormatArgs()
{
// decimal digit places, e.g. 0.1234
DecimalPlaces = 4,
// negative number style, e.g. -123 -> (123)
NegativeStyle = NumberDataFormatter.NumberNegativeStyle.RedBrackets,
// use separator, e.g.123,456
UseSeparator = true,
});
Or use action. Operation done by using action can be undone by call Undo method or press Ctrl+Z:
reoGridControl.DoAction(new SetRangeDataFormatAction(range, formatFlag, [Optional] object dataFormatArgs = null));
Test:
sheet["B2"] = 12345;
sheet["C2"] = 12345.67890;
sheet["B3"] = -1234;
sheet["C3"] = -1234.56789;
Available Data Format
Type | CellDataFormatFlag | Argument |
---|---|---|
Number | CellDataFormatFlag.Number | NumberDataFormatter.NumberFormatArgs |
DateTime | CellDataFormatFlag.DateTime | DateTimeDataFormatter.DateTimeFormatArgs |
Percent | CellDataFormatFlag.Percent | PercentDataFormatter.PercentFormatArgs |
Currency | CellDataFormatFlag.Currency | CurrencyDataFormatter.CurrencyFormatArgs |
Text | CellDataFormatFlag.Text | None |
Custom Extension | CellDataFormatFlag.Custom | Any object |
Number
Number format provides the generic number format with specifiable separator, number of digits for decimal place and negative number styles.
Example:
Code to set cell as number format:
sheet.SetRangeDataFormat("A1:E7", CellDataFormatFlag.Number,
new NumberDataFormatter.NumberFormatArgs()
{
// decimal digit places 0.1234
DecimalPlaces = 4,
// use separator: 123,456
UseSeparator = true,
});
Negative number style
Negative number styles defined in enum NumberNegativeStyle
.
ReoGrid provides the following styles to format a negative number.
Negative number style | Example | Enum Value |
---|---|---|
No style | -123,456.789 | Minus |
Red | 123,456.789 | Red |
Red Minus | -123,456.789 | Red |Minus |
Bracketed | (123,456.789) | Brackets |
Bracketed Red | (123,456.789) | Brackets |Red |
Bracketed Red Minus | (-123,456.789) | Brackets |Red |Minus |
Japanese Prefix | ▲ 123,456,789 | Prefix_Sankaku |
Japanese Prefix (ReoGrid Pro) | ▲ 123,456,789 | Prefix_Sankaku |Red |
Example:
var sheet = grid.CurrentWorksheet;
sheet.SetRangeDataFormat("B2", CellDataFormatFlag.Number,
new NumberDataFormatter.NumberFormatArgs
{
DecimalPlaces = 3,
NegativeStyle = NumberDataFormatter.NumberNegativeStyle.RedBrackets,
UseSeparator = true,
});
sheet["B2"] = -12345.67;
DateTime
sheet.SetRangeDataFormat(RangePosition.EntireRange, CellDataFormatFlag.DateTime,
new DateTimeDataFormatter.DateTimeFormatArgs
{
// culture
CultureName = "en-US",
// pattern
Format = "yyyy/MM/dd",
});
Percent
sheet.SetRangeDataFormat(RangePosition.EntireRange, CellDataFormatFlag.Percent,
new NumberDataFormatter.NumberFormatArgs
{
// decimal digit places
DecimalPlaces = 2,
});
Currency
Currency format extended from number format, it is possible to specify the currency symbol before or after currency number. For example:
var sheet = grid.CurrentWorksheet;
// add test code here
sheet.SetRangeDataFormat("B2", CellDataFormatFlag.Currency,
new CurrencyDataFormatter.CurrencyFormatArgs
{
CultureEnglishName = "en-US",
DecimalPlaces = 2,
UseSeparator = true,
PrefixSymbol = "$"
});
sheet["B2"] = 1234;
Put space before symbol and number
It is possible to put a space after dollar symbol, for example:
sheet.SetRangeDataFormat("B2", CellDataFormatFlag.Currency,
new CurrencyDataFormatter.CurrencyFormatArgs
{
CultureEnglishName = "en-US",
DecimalPlaces = 2,
UseSeparator = true,
PrefixSymbol = **"$ "**
});
Example: Set postfix symbol
sheet.SetRangeDataFormat("B2", CellDataFormatFlag.Currency,
new CurrencyDataFormatter.CurrencyFormatArgs
{
CultureEnglishName = "en-US",
DecimalPlaces = 2,
UseSeparator = true,
PostfixSymbol = " USD"
});
Text
Set data format as Text
to prevent data format.
sheet.SetRangeDataFormat(RangePosition.EntireRange, CellDataFormatFlag.Text, null);
Custom cell data formatter
To add custom cell data formatter, see Custom Data Format.