Set Data Format in Excel Using POI 3.0

In this program we are setting data format in excel file using Java.

In this program we are setting data format in excel file using Java.

POI version 3.0 provides 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 for "(#,##0_);(#,##0)".

In this example we are going to set the format as "#,##0.0".We are passing value in cell and the output of this will be 11,111.1 .To set the data format we are using setDataFormat() method .In this method we are creating the object of data format. After that we are setting is into hssf cell style sheet.

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 setDataFormat

  {

public static void main(String s[])

  {

  try{

  FileOutputStream out = new FileOutputStream

("dateFormat.xls");

  HSSFWorkbook hssfworkbook = new HSSFWorkbook();

  HSSFSheet sheet = hssfworkbook.createSheet

("new sheet");

  HSSFCellStyle cs = hssfworkbook.createCellStyle();

  HSSFDataFormat df = hssfworkbook.

createDataFormat();

  cs.setDataFormat(df.getFormat("#,##0.0"));

  HSSFRow row = sheet.createRow((short)0);

  HSSFCell cell = row.createCell((short)0);

  cell.setCellValue(11111.1);

  cell.setCellStyle(cs);

  hssfworkbook.write(out);

  out.close();

 }catch(Exception e){}

 }

}

The output of the program is given below:

Download this example.

Set Data Format in Excel Using POI 3.0

Set Data Format in Excel Using POI 3.0

Set Data Format in Excel Using POI 3.0

Tutorials