Use the Excel Export API to Specify Number Format for Cell Content
- 10 minutes to read
You can specify how to display a numeric value in a cell by applying number formats. For example, a number can appear in a cell as a percentage, decimal, currency, accounting, date or time value. This document provides examples on how to apply different number formats to cell values.
Excel Number and Date Formats
Predefined Formats
Use the XlFormatting.NumberFormat property to apply the predefined Excel number format to a cell value. The following formats are available:
- 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)
Custom Formats
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 formatting 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 XlNumberFormat or XlCellFormatting object to the IXlCell.ApplyFormatting method as a parameter, or assign it to the IXlCell.Formatting property.
To share number formatting settings with multiple cells in a row at once, use the IXlRow.BulkCells method.
To specify a number format for the entire row or column, use the IXlRow.ApplyFormatting and IXlColumn.ApplyFormatting methods, or IXlRow.Formatting and IXlColumn.Formatting properties, respectively.
// 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 = "# ???/???";
}
using (IXlCell cell = row.CreateCell()) {
// Display text value
cell.Value = "test";
cell.Formatting = new XlCellFormatting();
cell.Formatting.NumberFormat = XlNumberFormat.Text;
}
}
NET Number and Date Formats
To specify the .NET numeric format for a cell value, use the static XlCellFormatting.FromNetFormat method with the following parameters:
- A string value that specifies the format string used to format a numeric value in a cell. You can use both standard and custom numeric or date and time format strings.
- A boolean value indicating whether the passed format string is a date and time format string. This parameter is required to distinguish between the standard number and date and time format specifiers. For example, the "d" format specifier can be interpreted as the decimal format specifier or as the short date format specifier depending on the isDateTimeFormat parameter value.
You can also use the XlFormatting.NetFormatString and XlFormatting.IsDateTimeFormatString properties of the XlCellFormatting object to specify number formatting options. Note that the XlCellFormatting.FromNetFormat method automatically sets these properties according to the values of its parameters.
To apply the specified number formatting options to a cell, pass the XlCellFormatting object to the IXlCell.ApplyFormatting method as a parameter, or assign it to the IXlCell.Formatting property.
To share number formatting settings with multiple cells in a row at once, use the IXlRow.BulkCells method.
To specify a number format for the entire row or column, use the IXlRow.ApplyFormatting and IXlColumn.ApplyFormatting methods, or IXlRow.Formatting and IXlColumn.Formatting properties, respectively.
Note
Microsoft® Excel® uses number formats similar, but not identical to those of the .NET Framework. To provide compatibility, the Excel Export implicitly converts the specified .NET-style format strings to the native Excel formats. But take special note that if the .NET number format is already specified and you try to override it by the native Excel number format all your settings will be ignored. To remove .NET number format settings, assign an empy string to the XlFormatting.NetFormatString property or set it to null.
// Create the header row for the ".NET number formats" category.
using (IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
// Set the cell value.
cell.Value = ".NET number formats";
// Apply the "Heading 4" predefined formatting to the cell.
cell.Formatting = XlCellFormatting.Heading4;
}
}
// Use the standard .NET-style format strings to display data in cells.
using (IXlRow row = sheet.CreateRow()) {
using(IXlCell cell = row.CreateCell()) {
cell.Value = "Standard formats:";
}
using(IXlCell cell = row.CreateCell()) {
// Display 123.45 as 123.
cell.Value = 123.45;
cell.Formatting = XlCellFormatting.FromNetFormat("D", false);
}
using(IXlCell cell = row.CreateCell()) {
// Display 12345 as 1.234500E+004.
cell.Value = 12345;
cell.Formatting = XlCellFormatting.FromNetFormat("E", false);
}
using(IXlCell cell = row.CreateCell()) {
// Display 0.33 as 33.00%.
cell.Value = 0.33;
cell.Formatting = XlCellFormatting.FromNetFormat("P", false);
}
using(IXlCell cell = row.CreateCell()) {
// Display the current date using the short date pattern.
cell.Value = DateTime.Now;
cell.Formatting = XlCellFormatting.FromNetFormat("d", true);
}
using(IXlCell cell = row.CreateCell()) {
// Display the current time using the short time pattern.
cell.Value = DateTime.Now;
cell.Formatting = XlCellFormatting.FromNetFormat("t", true);
}
}
// Use custom format strings 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 123.456 as 123.46.
cell.Value = 123.45;
cell.Formatting = XlCellFormatting.FromNetFormat("#0.00", false);
}
using(IXlCell cell = row.CreateCell()) {
// Display 12345 as 1.235E+04.
cell.Value = 12345;
cell.Formatting = XlCellFormatting.FromNetFormat("0.0##e+00", false);
}
using(IXlCell cell = row.CreateCell()) {
// Display 0.333 as Max=33.3%.
cell.Value = 0.333;
cell.Formatting = XlCellFormatting.FromNetFormat("Max={0:#.0%}", false);
}
using(IXlCell cell = row.CreateCell()) {
// Apply the custom format string to the current date.
// Display days as 01–31, months as 01-12 and years as a four-digit number.
cell.Value = DateTime.Now;
cell.Formatting = XlCellFormatting.FromNetFormat("dd-MM-yyyy", true);
}
using(IXlCell cell = row.CreateCell()) {
// Apply the custom format string to the current time.
// Display hours as 01-12, minutes as 00-59, and add the AM/PM designator.
cell.Value = DateTime.Now;
cell.Formatting = XlCellFormatting.FromNetFormat("hh:mm tt", true);
}
}