XlFormatting.NumberFormat Property
Gets or sets an MS Excel-style number format that specifies how a numeric value should be displayed in a cell.
Namespace: DevExpress.Export.Xl
Assembly: DevExpress.Printing.v19.2.Core.dll
Declaration
Property Value
| Type | Description |
|---|---|
| XlNumberFormat | An XlNumberFormat object that specifies a cell number format. |
Remarks
The XlNumberFormat class provides a set of static properties you can use to specify the predefined Excel number format for a cell value. These properties allow you to display a numeric value in a cell as a number (XlNumberFormat.Number, XlNumberFormat.Number2, XlNumberFormat.NumberWithThousandSeparator, etc. ), percentage (XlNumberFormat.Percentage, XlNumberFormat.Percentage2), fraction (XlNumberFormat.Fraction, XlNumberFormat.Fraction2), date (XlNumberFormat.ShortDate, XlNumberFormat.LongDate, XlNumberFormat.MonthYear, etc.), time (XlNumberFormat.ShortTime12, XlNumberFormat.LongTime12, XlNumberFormat.MinuteSeconds, etc.), text (XlNumberFormat.Text) and so on.
If the predefined formats do not meet your demands, you can create a custom number format. To do this, assign the corresponding format string to the NumberFormat property of the XlCellFormatting object that defines common format settings for a cell. Note that the specified format string will be implicitly converted to the XlNumberFormat object.
To apply the specified number formatting options to a cell, pass the appropriate XlCellFormatting or XlNumberFormat object to the IXlCell.ApplyFormatting method as a parameter, or assign it to the IXlCell.Formatting property.
You can also use the XlFormatting.NetFormatString and XlFormatting.IsDateTimeFormatString properties of the XlCellFormatting object to specify the .NET numeric format for a cell value. For details on how to apply different number formats to worksheet cells, refer to the How to: Specify Number Format for Cell Content example.
Example
Note
A complete sample project is available at https://github.com/DevExpress-Examples/xl-export-api-examples-t253492
// Create the header row for the "Excel number formats" category.
using (IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = "Excel number formats";
// Apply the "Heading 4" predefined formatting to the cell.
cell.Formatting = XlCellFormatting.Heading4;
}
}
// Use the predefined Excel number formats to display data in cells.
using(IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Predefined formats:";
}
using(IXlCell cell = row.CreateCell()) {
// Display 123.456 as 123.46.
cell.Value = 123.456;
cell.Formatting = XlNumberFormat.Number2;
}
using(IXlCell cell = row.CreateCell()) {
// Display 12345 as 12,345.
cell.Value = 12345;
cell.Formatting = XlNumberFormat.NumberWithThousandSeparator;
}
using(IXlCell cell = row.CreateCell()) {
// Display 0.33 as 33%.
cell.Value = 0.33;
cell.Formatting = XlNumberFormat.Percentage;
}
using(IXlCell cell = row.CreateCell()) {
// Display the current date as "mm-dd-yy".
cell.Value = DateTime.Now;
cell.Formatting = XlNumberFormat.ShortDate;
}
using(IXlCell cell = row.CreateCell()) {
// Display the current time as "h:mm AM/PM".
cell.Value = DateTime.Now;
cell.Formatting = XlNumberFormat.ShortTime12;
}
}
// Use custom number formats to display data in cells.
using (IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Custom formats:";
}
using(IXlCell cell = row.CreateCell()) {
// Display 4310.45 as $4,310.45.
cell.Value = 4310.45;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
}
using(IXlCell cell = row.CreateCell()) {
// Display 3426.75 as €3,426.75.
cell.Value = 3426.75;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = @"_-[$€-2] * #,##0.00_-;-[$€-2] * #,##0.00_-;_-[$€-2] * "" - ""??_-;_-@_-";
}
using(IXlCell cell = row.CreateCell()) {
// Display 0.333 as 33.3%.
cell.Value = 0.333;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = "0.0%";
}
using(IXlCell cell = row.CreateCell()) {
// Apply the custom number format to the date value.
// Display days as Sunday–Saturday, months as January–December, days as 1–31 and years as 1900–9999.
cell.Value = DateTime.Now;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = "dddd, mmmm d, yyyy";
}
using(IXlCell cell = row.CreateCell()) {
// Display 0.6234 as 341/547.
cell.Value = 0.6234;
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = "# ???/???";
}
}