corelibraries-devexpress-dot-export-dot-xl-d13ce8e4.md
Represents the object that specifies a cell number format.
Namespace : DevExpress.Export.Xl
Assembly : DevExpress.Printing.v25.2.Core.dll
NuGet Package : DevExpress.Printing.Core
public class XlNumberFormat
Public Class XlNumberFormat
The following members return XlNumberFormat objects:
Show 30 links
The XlNumberFormat class provides a set of static properties you can use to apply the predefined Excel number format to 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. To get the format code used by the XlNumberFormat object to display a cell value, use the XlNumberFormat.FormatCode property.
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 XlFormatting.NumberFormat property of the XlCellFormatting object that defines common format settings of a cell. The specified format string will be implicitly converted to the XlNumberFormat object.
To apply the specified number formatting options to a cell, pass the XlNumberFormat object to the IXlCell.ApplyFormatting method as a parameter, or assign it to the IXlCell.Formatting property. For more information on how to apply a number format to a cell value, refer to the How to: Specify Number Format for Cell Content document.
Note
A complete sample project is available at https://github.com/DevExpress-Examples/excel-export-api-examples
// 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 = "# ???/???";
}
}
' Create the header row for the "Excel number formats" category.
Using row As IXlRow = sheet.CreateRow()
Using cell As IXlCell = row.CreateCell()
' Set the cell value.
cell.Value = "Excel number formats"
' Apply the "Heading 4" predefined formatting to the cell.
cell.Formatting = XlCellFormatting.Heading4
End Using
End Using
' Use the predefined Excel number formats to display data in cells.
Using row As IXlRow = sheet.CreateRow()
Using cell As IXlCell = row.CreateCell()
cell.Value = "Predefined formats:"
End Using
Using cell As IXlCell = row.CreateCell()
' Display 123.456 as 123.46.
cell.Value = 123.456
cell.Formatting = XlNumberFormat.Number2
End Using
Using cell As IXlCell = row.CreateCell()
' Display 12345 as 12,345.
cell.Value = 12345
cell.Formatting = XlNumberFormat.NumberWithThousandSeparator
End Using
Using cell As IXlCell = row.CreateCell()
' Display 0.33 as 33%.
cell.Value = 0.33
cell.Formatting = XlNumberFormat.Percentage
End Using
Using cell As IXlCell = row.CreateCell()
' Display the current date as "mm-dd-yy".
cell.Value = Date.Now
cell.Formatting = XlNumberFormat.ShortDate
End Using
Using cell As IXlCell = row.CreateCell()
' Display the current time as "h:mm AM/PM".
cell.Value = Date.Now
cell.Formatting = XlNumberFormat.ShortTime12
End Using
End Using
' Use custom number formats to display data in cells.
Using row As IXlRow = sheet.CreateRow()
Using cell As IXlCell = row.CreateCell()
cell.Value = "Custom formats:"
End Using
Using cell As IXlCell = 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]* ""-""??_);_(@_)"
End Using
Using cell As IXlCell = 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] * "" - ""??_-;_-@_-"
End Using
Using cell As IXlCell = row.CreateCell()
' Display 0.333 as 33.3%.
cell.Value = 0.333
cell.Formatting = New XlCellFormatting()
cell.Formatting.NumberFormat = "0.0%"
End Using
Using cell As IXlCell = 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 = Date.Now
cell.Formatting = New XlCellFormatting()
cell.Formatting.NumberFormat = "dddd, mmmm d, yyyy"
End Using
Using cell As IXlCell = row.CreateCell()
' Display 0.6234 as 341/547.
cell.Value = 0.6234
cell.Formatting = New XlCellFormatting()
cell.Formatting.NumberFormat = "# ???/???"
End Using
End Using
Object XlNumberFormat
See Also
Use the Excel Export API to Specify Number Format for Cell Content