Number Settings

How to Set Display Formats of Numbers and Dates

A very strong feature of Microsoft Excel is that it allows users to set the display formats of numeric values and dates. We know that numeric data can be used to represent different values including decimal, currency, percentage, fraction or accounting values, etc. All these numerical values are displayed in different formats depending on the type of information they represent. Similarly, there are many formats in which a date or time can be displayed.
Aspose.Cells supports this functionality and allows developers to set any display format for a number or date.

How to Set Display Formats in Microsoft Excel

To set display formats in Microsoft Excel:

  1. Right-click any cell.
  2. Select Format Cells. A dialog will appear that is used to set the display formats of any kind of value.

On the left side of the dialog, there are many categories of values like General, Number, Currency, Accounting, Date, Time, Percentage, etc. Aspose.Cells supports all of these display formats.

Aspose.Cells provides a module, Workbook that represents an Excel file. The Workbook module contains a Worksheets collection that allows access to each worksheet in the Excel file. A worksheet is represented by the Worksheet module. The Worksheet module provides a Cells collection. Each item in the Cells collection represents an object of the Cell module.

Aspose.Cells provides getStyle() and setStyle(Style) methods for the Cell module. These methods are used to get and set a cell’s formatting. The Style module provides some useful properties for dealing with the display formats of numbers and dates.

How to Use Built-in Number Formats

Aspose.Cells offers some built-in number formats to configure the display formats of the numbers and dates. These built-in number formats can be applied by using the setNumber(number) method of the Style object. All built-in number formats are given unique numeric values. Developers can assign any desired numeric value to the setNumber(number) method of the Style object to apply the display format. This approach is fast. The built-in number formats supported by Aspose.Cells are listed below.

Value Type Format String
0 General General
1 Decimal 0
2 Decimal 0.00
3 Decimal #,##0
4 Decimal #,##0.00
5 Currency $#,##0;$-#,##0
6 Currency $#,##0;[Red]$-#,##0
7 Currency $#,##0.00;$-#,##0.00
8 Currency $#,##0.00;[Red]$-#,##0.00
9 Percentage 0%
10 Percentage 0.00%
11 Scientific 0.00E+00
12 Fraction # ?/?
13 Fraction # /
14 Date m/d/yyyy
15 Date d-mmm-yy
16 Date d-mmm
17 Date mmm-yy
18 Time h:mm AM/PM
19 Time h:mm:ss AM/PM
20 Time h:mm
21 Time h:mm:ss
22 Time m/d/yy h:mm
37 Currency #,##0;-#,##0
38 Currency #,##0;[Red]-#,##0
39 Currency #,##0.00;-#,##0.00
40 Currency #,##0.00;[Red]-#,##0.00
41 Accounting _ * #,##0_ ;_ * “_ ;_ @_
42 Accounting _ $* #,##0_ ;_ $* “_ ;_ @_
43 Accounting _ * #,##0.00_ ;_ * “??_ ;_ @_
44 Accounting _ $* #,##0.00_ ;_ $* “??_ ;_ @_
45 Time mm:ss
46 Time h:mm:ss
47 Time mm:ss.0
48 Scientific ##0.0E+00
49 Text @

How to Use Custom Number Formats

To define your own customized format string for setting the display format, use the Style object’s setCustom(string) method. This approach is not as fast as using pre-set formats but it is more flexible.

Advance topics

  • Check Custom Number Format when Setting Style.Custom Property
  • List of Supported Number Formats
  • Render Custom Date Format Pattern g and ge mm dd
  • Specify Custom Number Decimal and Group Separators for Workbook
  • Specifying DBNum Custom Pattern Formatting
Alignment Settings