Format Date Excel Cell - Java POI Example Program

Formatting Dates Using Apache POI - Introduction

In this tutorial, let us examine how to use built in formatters available in Apache POI, to format your date cells in Excel to the pattern you want, in Java, with an example program. Formatting a cell to a specific date value, requires a style (HSSFCellStyle) to be attached to it. In the style, you can use the method setDataFormat method and specify the formatter you want to use. Let us see a step by step guide in POI to attach built in date formatters to XLS documents in this example.

createCellStyle - Specify Date Format

The first step is to create your workbook and add a worksheet. We have done this many times now. After this, you should use workbook.createCellStyle() method and define styles in your workbook. Later, we will attach a date pattern to these styles. Java code snippet for this step is provided below:

                /* Create Workbook and Worksheet */
                HSSFWorkbook my_workbook = new HSSFWorkbook();
                HSSFSheet my_sheet = my_workbook.createSheet("Cell Date");              
                /* Create two styles that take different formats */
                HSSFCellStyle my_style_0 = my_workbook.createCellStyle();
                HSSFCellStyle my_style_1 = my_workbook.createCellStyle();

setDataFormat - Attach Date formats to Style

In this step, we use HSSFDataFormat class to retrieve some built in date formats that is available in POI. This is done using getBuiltinFormat method. The value retrieved is then passed to setDataFormat method, which attaches a date format to the styles created in step - 1. We use two styles in this example. Refer below for a code sample:

                /* Define date formats with the style */                        
                my_style_0.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
                my_style_1.setDataFormat(HSSFDataFormat.getBuiltinFormat("d-mmm-yy"));
                /* Define data using java.util.Calendar */

Attach Cells with Date Formats

We will now create some data in the worksheet and attach the styles to it. We use "Row" and "Cell" objects to define data. As this is a date based cell, we use java.util.Calendar and java.util.GregorianCalendar to define a date and attach it to the cell. This step is the key as it defines the cell value and attaches the format we created earlier to the cell. A code snippet to do this is provided below:

                /* Define data using java.util.Calendar */
                Calendar calendar = new GregorianCalendar();
                calendar.set(1982,Calendar.NOVEMBER,25);
                /* Create Cell Data and Attach Date formats */          
                Row row = my_sheet.createRow(0);                
                Cell cell = row.createCell(0);
                cell.setCellValue(calendar);            
                cell.setCellStyle(my_style_0); //style 1                
                row = my_sheet.createRow(1);            
                cell = row.createCell(1);
                cell.setCellValue(calendar);            
                cell.setCellStyle(my_style_1); //style 2                                        

Finally, we write the workbook to a file to see the formats we applied.

Date Format to Excel Cells - Apache POI - Complete Java Program Example

The full Java code that explains how to use built in formatters to format date cell data in a XLS workbook is provided below:

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
import java.util.Calendar;
import java.util.GregorianCalendar;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
public class FormatCellData {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet */
                HSSFWorkbook my_workbook = new HSSFWorkbook();
                HSSFSheet my_sheet = my_workbook.createSheet("Cell Date");              
                /* Create two styles that take different formats */
                HSSFCellStyle my_style_0 = my_workbook.createCellStyle();
                HSSFCellStyle my_style_1 = my_workbook.createCellStyle();
                /* Define date formats with the style */                        
                my_style_0.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
                my_style_1.setDataFormat(HSSFDataFormat.getBuiltinFormat("d-mmm-yy"));
                /* Define data using java.util.Calendar */
                Calendar calendar = new GregorianCalendar();
                calendar.set(1982,Calendar.NOVEMBER,25);
                /* Create Cell Data and Attach Date formats */          
                Row row = my_sheet.createRow(0);                
                Cell cell = row.createCell(0);
                cell.setCellValue(calendar);            
                cell.setCellStyle(my_style_0); //style 1                
                row = my_sheet.createRow(1);            
                cell = row.createCell(1);
                cell.setCellValue(calendar);            
                cell.setCellStyle(my_style_1); //style 2                                        
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\cell_date_format.xls"));
                my_workbook.write(out);
                out.close();
        }
}

A screenshot of the cell data produced in the output is provided below: (output cell highlighted in yellow, with the format provided against it)

Excel POI - Format Cell Date Example - Java Program - Output

That completes a quick tutorial to use built in date formatters in POI to format your date data in Excel. In the next article, we will see how to write a custom formatter, instead of using built in formatters.