ContextQMD
Back to Devexpress

Use the Excel Export API to Specify Number Format for Cell Content

officefileapi-114575-excel-export-library-formatting-how-to-specify-number-format-for-cell-content.md

latest19.8 KB
Original Source

Use the Excel Export API to Specify Number Format for Cell Content

  • Sep 19, 2023
  • 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:

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.

View Example

csharp
// 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;
    }
}
vb
' 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
    Using cell As IXlCell = row.CreateCell()
        ' Display text value
        cell.Value = "test"
        cell.Formatting = New XlCellFormatting()
        cell.Formatting.NumberFormat = XlNumberFormat.Text
    End Using
End Using

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.

View Example

csharp
// 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);
    }
}
vb
' Create the header row for the ".NET number formats" category.
Using row As IXlRow = sheet.CreateRow()
    Using cell As IXlCell = row.CreateCell()
        ' Set the cell value.
        cell.Value = ".NET number formats"
        ' Apply the "Heading 4" predefined formatting to the cell.
        cell.Formatting = XlCellFormatting.Heading4
    End Using
End Using
' Use the standard .NET-style format strings to display data in cells.
Using row As IXlRow = sheet.CreateRow()
    Using cell As IXlCell = row.CreateCell()
        cell.Value = "Standard formats:"
    End Using
    Using cell As IXlCell = row.CreateCell()
        ' Display 123.45 as 123.
        cell.Value = 123.45
        cell.Formatting = XlCellFormatting.FromNetFormat("D", False)
    End Using
    Using cell As IXlCell = row.CreateCell()
        ' Display 12345 as 1.234500E+004.
        cell.Value = 12345
        cell.Formatting = XlCellFormatting.FromNetFormat("E", False)
    End Using
    Using cell As IXlCell = row.CreateCell()
        ' Display 0.33 as 33.00%.
        cell.Value = 0.33
        cell.Formatting = XlCellFormatting.FromNetFormat("P", False)
    End Using
    Using cell As IXlCell = row.CreateCell()
        ' Display the current date using the short date pattern.
        cell.Value = Date.Now
        cell.Formatting = XlCellFormatting.FromNetFormat("d", True)
    End Using
    Using cell As IXlCell = row.CreateCell()
        ' Display the current time using the short time pattern.
        cell.Value = Date.Now
        cell.Formatting = XlCellFormatting.FromNetFormat("t", True)
    End Using
End Using
' Use custom format strings 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 123.456 as 123.46. 
        cell.Value = 123.45
        cell.Formatting = XlCellFormatting.FromNetFormat("#0.00", False)
    End Using
    Using cell As IXlCell = row.CreateCell()
        ' Display 12345 as 1.235E+04.
        cell.Value = 12345
        cell.Formatting = XlCellFormatting.FromNetFormat("0.0##e+00", False)
    End Using
    Using cell As IXlCell = row.CreateCell()
        ' Display 0.333 as Max=33.3%.
        cell.Value = 0.333
        cell.Formatting = XlCellFormatting.FromNetFormat("Max={0:#.0%}", False)
    End Using
    Using cell As IXlCell = 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 = Date.Now
        cell.Formatting = XlCellFormatting.FromNetFormat("dd-MM-yyyy", True)
    End Using
    Using cell As IXlCell = 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 = Date.Now
        cell.Formatting = XlCellFormatting.FromNetFormat("hh:mm tt", True)
    End Using
End Using

See Also

Use the Excel Export API to Format a Cell