Setting Display Formats
Using Microsoft Excel:
- Right-click on any desired cell and select Format Cells option.
A dialog appears that allows your to set the display formats of any kind of value.
Formatting Cells using Microsoft Excel
In the left side of the above figure, you can see that there is many categories of the values like general, number, Curre Ncy, 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,WorkbookThat's represents an Excel file.Workbook class contains A  Worksheetcollection that allows to access all 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 the SetStyle method in the Cell class which is used to set the form Atting style of a cell. Also, style object of style class can used that further provides some useful prope Rties to configure font settings.
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  setnumber Span class= "Apple-converted-space" > method of The  Style Span class= "Apple-converted-space" > object. All built-in number formats is given unique numeric values. Developers can provide any desired numeric value to The  setnumber Span class= "Apple-converted-space" > method of  Style object and hence the display format is applied. This approach was more faster. The built-in number formats supported by Aspose.cells is 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. XX |
9 |
Percentage |
0% |
10 |
Percentage |
0% |
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 |
21st |
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. XX |
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 |
@ |
The following output is the result of executing the code below.
Formatting data using built-in number formats
Java
Instantiating a Workbook objectworkbook Workbook =New Workbook ();Accessing the added worksheet in the Excel fileint sheetindex = Workbook.getworksheets (). Add (); Worksheet Worksheet = Workbook.getworksheets (). get (Sheetindex); Cells cells = Worksheet.getcells ();Adding the current system date to"A1" Cellcell cell = Cells.get ("A1"); Cell.setvalue (Calendar.getinstance ());Setting the display format of the date to number all to show date as"D-mmm-yy" Style style = Cell.getstyle (); Style.setnumber (); Cell.setstyle (Style);//adding A numeric value to "A2" Cellcell = Cells.get ("A2"); Cell.setvalue ; Setting the display format of the value to number 9 to show value as Percentagestyle = Cell.getstyle (); Style.setnumber (9 ); Cell.setstyle (style); //adding A numeric value to "A3" Cellcell = Cells.get ("A3"); Cell.setvalue (1546) ; Setting the display format of the value to number 6 to show value as Currencystyle = Cell.getstyle (); Style.setnumber (6); Cell.setstyle (style); //saving the modified Excel file in default Formatworkbook.save ("C:\\output.xls");
Using Custom Number Formats
To define your own customized format string to set data display format, use the setcustom method of The Style object. This approach was not as much faster as the first approach discussed above but it was more flexible.
The following output is the result of executing the code below.
Formatting data using custom number format string
Java
Instantiating a Workbook objectworkbook Workbook =New Workbook ();Accessing the added worksheet in the Excel fileint sheetindex = Workbook.getworksheets (). Add (); Worksheet Worksheet = Workbook.getworksheets (). get (Sheetindex); Cells cells = Worksheet.getcells ();Adding the current system date to"A1" Cellcell cell = Cells.get ("A1"); Cell.setvalue (Calendar.getinstance ());Setting the display format of the date to number all to show date as"D-mmm-yy" Style style = Cell.getstyle (); Style.setcustom ("D-mmm-yy"); Cell.setstyle (style);Adding a numeric value to"A2" Cellcell = Cells.get (//setting the display format of the value to number 9 to show value as Percentagestyle = CELL.G Etstyle (); Style.setcustom ( "A3" Cellcell = Cells.get ( "A3"); Cell.setvalue (1546); //setting the display format of the value to number 6 to show value as Currencystyle = Cell.get Style (); Style.setcustom ( "$#,# #0; [ red]$-#,# #0 "); Cell.setstyle (style); //saving the modified Excel file in default formatworkbook.save ( "C:\\output.xls");
From:http://www.aspose.com/docs/display/cellsjava/setting+display+formats+of+numbers+and+dates
(GO) aspone.cells set cell data format Setting Display Formats of Numbers and Dates