Introduction
A very strong feature of Microsoft Excel is to allow users to set the display formats of numeric values and dates. We know that numeric data can be used to represent different kinds of values including decimal, currency, percentage, fraction or accounting values etc. All these numerical values are displayed in different formats to show different kinds of information. Similarly, there are many formats in which a date or time can be displayed. Aspose.Cells allows developers to set any display format for a number or date.
Setting Display Formats
Using Microsoft Excel, you can right click on any desired cell and select Format Cells... option then a dialog appears that allows to set the display formats of any kind of value as shown below:
|
Figure: Formatting Cells using Microsoft Excel
|
In the left side of the above figure, you can see that there are many categories of the values like General, Number, Currency, Accounting, Date, Time, Percentage etc. Aspose.Cells supports all of these categories to set the display format of numbers or dates.
Aspose.Cells provides a class, Workbook that represents an Excel file. Workbook class contains a Worksheets collection that allows to access each worksheet in the Excel file. A worksheet is represented by the Worksheet class. Worksheet class provides a Cells collection. Each item in the Cells collection represents an object of Cell class.
Aspose.Cells provides Style property in the Cell class that is used to set the formatting style of a cell. Style property is infact an object of Style class that further provides some useful properties to deal with the display formats of numbers and dates.
Using 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 Number property of the Style object. All built-in number formats are given unique numeric values. Developers can assign any desired numeric value to the Number property of Style object and hence the display format is applied. This approach is more faster. The built-in number formats supported by Aspose.Cells are given 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/yy
|
|
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
|