Display formatting is specified with the same format code strings as Excel. V4’s CellDataFormatFlag and the *FormatArgs types are gone, which makes XLSX interop lossless.

Applying a format

using unvell.ReoGrid.Core;

ws.SetNumberFormat(0, 0, "#,##0");
ws.SetNumberFormat(RangePosition.Parse("B2:B100"), "#,##0.00");
ws.SetNumberFormat(1, 0, "yyyy/mm/dd");
ws.SetNumberFormat(2, 0, "0.00%");

string? code = ws.GetNumberFormat(0, 0);
ws.SetNumberFormat(0, 0, null);      // remove the format

The range overload writes to every cell in the range you name. SetSelectionNumberFormat on the control clamps to the used range, but the core API does not — passing a whole column (A:A) creates a million entries, so keep the range down to the data.

Reading the displayed text

string shown = ws.GetDisplayText(0, 0);

// the format code's color comes back too, if it has one ([Red] and friends)
string text = ws.GetFormattedText(0, 0, out uint? color);

Common format codes

Sections

A format code splits into up to four sections separated by ;.

positive ; negative ; zero ; text

// four sections: positive; negative; zero; text
ws.SetNumberFormat(0, 0, "#,##0;[Red]-#,##0;\"-\";@");

// conditional sections
ws.SetNumberFormat(1, 0, "[>=1000000]0.0,,\"M\";[>=1000]0.0,\"K\";0");

Omitting sections behaves as in Excel: one section applies to every number, two split positive and negative.

Colors

Color specifiers such as [Red] are supported. They override the cell’s text color at render time.

GetFormattedText(r, c, out uint? color) returns that color, or null when the format does not name one.

Conditions

A condition such as [>=1000] can lead a section. They are evaluated top to bottom and the first match is used.

Japanese eras (和暦)

The g tokens print the era name and the e tokens the year within that era, spelled exactly as in Excel’s Japanese era formats.

y stays Gregorian even next to era tokens — the era year is e. Era years advance by calendar year, as in Excel: 2019-04-30 is 平成31 and 2019-05-01 is 令和1.

The 元年 form

With the [$-ja-JP-x-gannen] tag an era’s first year prints as 元 rather than 1, at either token width. Without the tag it prints as 1.

The era table

明治, 大正, 昭和, 平成 and 令和 are built in. The OS calendar data (the Windows registry, ICU) is never consulted, so the same workbook renders the same text on every machine. When a new era is announced before this library ships an update, register it at startup with JapaneseEras.Register.

using unvell.ReoGrid.Core.Style;

ws.SetNumberFormat(0, 0, "[$-411]ggge\"年\"m\"月\"d\"日\"");

// a newly announced era can be registered at startup, ahead of a library update
JapaneseEras.Register(new JapaneseEra("X", "新", "新元", new DateTime(2035, 1, 1)));

XLSX files leave the locale-reserved numFmtIds (27–36, 50–58) without a format code; they are read as the same era formats Excel-JP resolves them to. The TEXT worksheet function goes through this engine too, so TEXT(A1,"ggge年m月d日") matches what the cell displays.

Current limitations

These are not supported yet.

• Fractions (# ?/?)
• Scientific notation (0.00E+00)

Setting one of these is not an error, but it will not display the way you expect.

Formatting a value directly

Useful when you want to apply a format without going through a sheet.

using unvell.ReoGrid.Core.Style;

if (NumberFormatter.TryFormat(1234.5, "#,##0.00", out string text, out uint? color))
	Console.WriteLine(text);      // "1,234.50"

It returns false when the format cannot be applied — both for codes it cannot interpret and for codes that do no formatting at all, such as General or an empty string.

Persistence

Number formats round-trip per cell through both reogrid-json and XLSX. In XLSX they are written as numFmt, so opening the file in Excel shows the same thing.

What to read next

• StyleRecord
• XLSX