XLSX - Format Dates in Apache POI - Java Example

Formatting dates in XLSX workbook - Introduction


In this tutorial, we will explain how to format date cells in XLSX workbook types, by using built in formatters, available in Apache POI, with example Java programs. This is an extension of our XLS version, so we will be providing only the full Java program with comments where applicable. You should read the XLS version of this tutorial for a base understanding of how styles / formatters work in POI. 

Java Program - Format date - XLSX - Example


The complete Java program to format dates in XLSX workbooks with built in formatters is provided below:

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import java.util.Calendar;
import java.util.GregorianCalendar;
import org.apache.poi.ss.usermodel.BuiltinFormats;
public class FormatDateXLSX {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet */
                XSSFWorkbook my_workbook = new XSSFWorkbook();
                XSSFSheet my_sheet = my_workbook.createSheet("Format Date");            
                /* Create two styles that take different formats */
                XSSFCellStyle my_style_0 = my_workbook.createCellStyle();
                XSSFCellStyle my_style_1 = my_workbook.createCellStyle();
                /* We use BuiltinFormats directly in case of XLSX */
                /* Note that we are converting Integer to Short */
                my_style_0.setDataFormat((short)BuiltinFormats.getBuiltinFormat("m/d/yy"));
                my_style_1.setDataFormat((short)BuiltinFormats.getBuiltinFormat("d-mmm-yy"));
                /* Define data using java.util.Calendar */
                Calendar calendar = new GregorianCalendar();
                calendar.set(1990,Calendar.APRIL,2);
                /* 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:\\xlsx_date_formatting.xlsx"));
                my_workbook.write(out);
                out.close();
        }
}

Couple of things to note about the program. We are using BuiltinFormats class directly to get the built in format, and in the process we have to convert Integer to short in Java, to pass it to setDataFormat method. Other than this, this example is a direct replica of the XLS example.

The output of this program, formats the dates as per the masks provided, on the expected lines. A screenshot of the output is provided below:

XLSX - Format Date - Apache POI Example Program - Java Output
XLSX - Format Date - Apache POI Example Program - Java Output 
That completes the tutorial to use Built in formatters to create different date formats in XLSX workbooks. We will now discuss how to use custom date formatters in Excel with examples.

1 comment:

  1. Its show me in custom and then in format shown me the date which I was given format I need directly date instead of custom. I use wps for excel.

    ReplyDelete