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.

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.

Built in Data Format in Excel Using POI 3.0

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();
cell.setCellStyle(cs);
hssfworkbook.write(out);
out.close();
}catch(Exception e){}
  }}

The output of the program is given below:

Download this example.