Number Settings

How to Set Display Formats of Numbers and Dates

A key feature of Microsoft Excel is that it allows users to set the display formats of numeric values and dates. Numeric data can 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 for Python via .NET 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 such as General, Number, Currency, Accounting, Date, Time, Percentage, etc. Aspose.Cells for Python via .NET supports all of these display formats.

Aspose.Cells for Python via .NET provides a class, Workbook that represents a Microsoft Excel file. The Workbook class contains a worksheets collection that allows access to each worksheet in the Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a cells collection. Each item in the cells collection represents an object of the Cell class.

Aspose.Cells for Python via .NET provides get_style and set_style methods for the Cell class. These methods are used to get and set a cell’s formatting. The Style class provides useful properties for dealing with the display formats of numbers and dates.

How to Use Built‑in Number Formats

Aspose.Cells for Python via .NET offers built‑in number formats to configure the display formats of numbers and dates. These built‑in formats can be applied by using the Number property of the Style object. All built‑in number formats are assigned unique numeric values. Developers can assign any desired numeric value to the Number property to apply the corresponding 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 custom property. This approach is not as fast as using pre‑set formats, but it is more flexible.

Advanced topics

Alignment Settings