Built in Data Format
in Excel
Using POI 3.0
In this program we are using the built in format for formatting
our data in excel sheet.
POI version 3.0 provides the a new feature for manipulating various file formats based upon
Microsoft's OLE 2 Compound Document format using Java .POI version 3.0 APIs provides user
defined formatting facility and also provides a list of build-in data format
(for String,
Integer etc. values).
The org.apache.poi.hssf.usermodel.HSSFDataFormat class extends java.lang.Object
class. This class provides utility to identify built-in formats. There is facility in POI 3.0
to define the user define formats.
The following is a list of built-in formats provided is POI 3.0:
|
S/No.
|
Format
|
Vlaue |
S/No |
Format |
Value |
| 1 |
General |
0 |
12 |
h:mm AM/PM |
0x12 |
| 2 |
0 |
1 |
13 |
h:mm:ss AM/PM |
0x13 |
| 3 |
0.00 |
2 |
14 |
h:mm |
0x14 |
| 4 |
#,##0 |
3 |
15 |
h:mm:ss |
0x15 |
| 5 |
#,##0.00 |
4 |
16 |
m/d/yy h:mm |
0x16 |
| 6 |
($#,##0_);($#,##0) |
5 |
17 |
(#,##0_);[Red](#,##0) |
0x26 |
| 7 |
($#,##0_);[Red]($#,##0) |
6 |
18 |
(#,##0.00_);(#,##0.00) |
0x27 |
| 8 |
($#,##0.00);($#,##0.00) |
7 |
19 |
(#,##0.00_);[Red](#,##0.00) |
0x28 |
| 9 |
($#,##0.00_);[Red]($#,##0.00) |
8 |
20 |
_(*#,##0_);_(*(#,##0);_(* \"-\"_);_(@_) |
0x29 |
| 10 |
0% |
9 |
21 |
_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_) |
0x2a |
| 11 |
0.00% |
0xa |
22 |
_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_) |
0x2b |
| 12 |
0.00E+00 |
0xb |
23 |
_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_) |
0x2c |
| 13 |
# ?/? |
0xc |
24 |
mm:ss |
0x2d |
| 14 |
# ??/?? |
0xd |
25 |
[h]:mm:ss |
0x2e |
| 15 |
m/d/yy |
0xe |
26 |
mm:ss.0 |
0x2f |
| 16 |
d-mmm-yy |
0xf |
27 |
##0.0E+0 |
0x30 |
| 17 |
d-mmm |
0x10 |
28 |
@-This is text format. |
0x31 |
| 18 |
mmm-yy |
0x11 |
29 |
text-Alias for "@" |
0x31 |
Note:-The values from 0x17 to 0x24 are reserved for international and undocumented
and 0x25 is used for "(#,##0_);(#,##0)".
The Constructor:
public HSSFDataFormat(Workbook workbook):
To constructs a new data formatter we can use . It takes a workbook to have access to the workbooks format records.
The methods can be used for built in data format:
getBuiltinFormat(short index):
This is a static method. The return type of this method is String. This
method is used to get the format string that matches the given format index.
getBuiltinFormat(String format):
The return type of this method is short and this method is define as
static This method is used to get the format index that matches the given format string
getBuiltinFormats():
This method is used to get the list of formats.
getFormat(short index):This method is used to get the format string that matches the given format index
getFormat(String format):
This method is used to get the format index that matches the given format string.
getNumberOfBuiltinBuiltinFormats():
This method is used to get the number of builtin and reserved builtinFormats.
The code of the program is given below:
import java.io.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.usermodel.HSSFRow;
class setDataFormatForFullList
{
public static void main(String arg[]) {
try{
FileOutputStream out = new FileOutputStream
("dateFormatForList.xls");
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet = hssfworkbook.createSheet("new sheet");
String s="Rajesh Kumar Verma";
HSSFCellStyle cs = hssfworkbook.createCellStyle();
HSSFDataFormat df = hssfworkbook.createDataFormat();
HSSFFont f = hssfworkbook.createFont();
HSSFFont f2 = hssfworkbook.createFont();
cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
HSSFRow row = sheet.createRow((short)0);
HSSFCell cell = row.createCell((short)0);
cell.setCellValue(s );
cell.setCellStyle(cs);
hssfworkbook.write(out);
out.close();
}catch(Exception e){}
}}
|
The output of the program is given below:
Download this example.
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
