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.

Built in Data Format in Excel Using POI 3.0

Built in Data Format in Excel Using POI 3.0

Spring Framework Training

R Programming Training

Built in Data Format in Excel Using POI 3.0