How to: Specify Number or Date Format for Cell Content

• Jun 07, 2019
• 3 minutes to read

Date and Time Formats

This example demonstrates how to specify different display formats for date and time values in cells.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/spreadsheet-document-server-api-e4339..

FormattingActions.cs

worksheet.Range["A1:F1"].Formula = "= Now()";

// Apply different date display formats.
worksheet.Cells["A1"].NumberFormat = "m/d/yy";

worksheet.Cells["B1"].NumberFormat = "d-mmm-yy";

worksheet.Cells["C1"].NumberFormat = "dddd";

// Apply different time display formats.
worksheet.Cells["D1"].NumberFormat = "m/d/yy h:mm";

worksheet.Cells["E1"].NumberFormat = "h:mm AM/PM";

worksheet.Cells["F1"].NumberFormat = "h:mm:ss";

FormattingActions.vb

worksheet.Range("A1:F1").Formula = "= Now()"

' Apply different date display formats.
worksheet.Cells("A1").NumberFormat = "m/d/yy"

worksheet.Cells("B1").NumberFormat = "d-mmm-yy"

worksheet.Cells("C1").NumberFormat = "dddd"

' Apply different time display formats.
worksheet.Cells("D1").NumberFormat = "m/d/yy h:mm"

worksheet.Cells("E1").NumberFormat = "h:mm AM/PM"

worksheet.Cells("F1").NumberFormat = "h:mm:ss"

Number Formats

This example demonstrates how to use different number formats to display numeric data using the most appropriate formats.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/spreadsheet-document-server-api-e4339.

FormattingActions.cs

// Display 111 as 111.
worksheet.Cells["A1"].Value = 111;
worksheet.Cells["A1"].NumberFormat = "#####";

// Display 222 as 00222.
worksheet.Cells["B1"].Value = 222;
worksheet.Cells["B1"].NumberFormat = "00000";

// Display 12345678 as 12,345,678.
worksheet.Cells["C1"].Value = 12345678;
worksheet.Cells["C1"].NumberFormat = "#,#";

// Display .126 as 0.13.
worksheet.Cells["D1"].Value = .126;
worksheet.Cells["D1"].NumberFormat = "0.##";

// Display 74.4 as 74.400.
worksheet.Cells["E1"].Value = 74.4;
worksheet.Cells["E1"].NumberFormat = "##.000";

// Display 1.6 as 160.0%.
worksheet.Cells["F1"].Value = 1.6;
worksheet.Cells["F1"].NumberFormat = "0.0%";

// Display 4321 as $4,321.00.
worksheet.Cells["G1"].Value = 4321;
worksheet.Cells["G1"].NumberFormat = "$#,##0.00";

// Display 8.75 as 8 3/4.
worksheet.Cells["H1"].Value = 8.75;
worksheet.Cells["H1"].NumberFormat = "# ?/?";

FormattingActions.vb

' Display 111 as 111.
worksheet.Cells("A1").Value = 111
worksheet.Cells("A1").NumberFormat = "#####"

' Display 222 as 00222.
worksheet.Cells("B1").Value = 222
worksheet.Cells("B1").NumberFormat = "00000"

' Display 12345678 as 12,345,678.
worksheet.Cells("C1").Value = 12345678
worksheet.Cells("C1").NumberFormat = "#,#"

' Display .126 as 0.13.
worksheet.Cells("D1").Value =.126
worksheet.Cells("D1").NumberFormat = "0.##"

' Display 74.4 as 74.400.
worksheet.Cells("E1").Value = 74.4
worksheet.Cells("E1").NumberFormat = "##.000"

' Display 1.6 as 160.0%.
worksheet.Cells("F1").Value = 1.6
worksheet.Cells("F1").NumberFormat = "0.0%"

' Display 4321 as $4,321.00.
worksheet.Cells("G1").Value = 4321
worksheet.Cells("G1").NumberFormat = "$#,##0.00"

' Display 8.75 as 8 3/4.
worksheet.Cells("H1").Value = 8.75
worksheet.Cells("H1").NumberFormat = "# ?/?"

Custom Number Formats

This example demonstrates how to create a custom number format to display positive numbers, negative numbers, zero and text values using different formats and colors. To do this, specify the appropriate code sections of the number format as required. These code sections are separated by semicolons and used in the following order.

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

C#

// Set cell values.
worksheet["A2:B2"].Value = -15.50;
worksheet["A3:B3"].Value = 555;
worksheet["A4:B4"].Value = 0;
worksheet["A5:B5"].Value = "Name";

//Apply custom number format.
worksheet["B2:B5"].NumberFormat = "[Green]#.00;[Red]#.00;[Blue]0.00;[Cyan]\"product: \"@";

VB.NET

' Set cell values.
worksheet("A2:B2").Value = -15.50
worksheet("A3:B3").Value = 555
worksheet("A4:B4").Value = 0
worksheet("A5:B5").Value = "Name"

'Apply custom number format.
worksheet("B2:B5").NumberFormat = "[Green]#.00;[Red]#.00;[Blue]0.00;[Cyan]""product: ""@"