Create Date in Excel File using Apache POI in Java

Introduction

In this example I will show you how to create date and put it in an excel file using Apache POI in Java language. I will show you how to build the project using both maven and gradle build tools. I will also show you how to create date using Java 8 or prior to Java 8 on java.util.Date and java.util.Calendar.

The Apache POI is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2). You can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is the Java Excel solution (for Excel 97-2008).

Prerequisites

At least Java 8, Eclipse 4.12, Apache POI – 4.1.1, Gradle 5.6, Maven – 3.6.1

Create Project

Create a standalone maven project or gradle based project in Eclipse. The name or artifact id of the project is apache-poi-excel-date.

If you are creating maven based project then you can use the below pom.xml file:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
	
    <groupId>com.roytuts</groupId>
    <artifactId>apache-poi-excel-date</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
	
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <poi.version>4.1.1</poi.version>
    </properties>
	
    <dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.version}</version>
        </dependency>
    </dependencies>
	
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
                <configuration>
                    <source>at least 1.8</source>
                    <target>at least 1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

If you are creating gradle based project then you can use below build.gradle script for creating date in excel file:

plugins {
    id 'java-library'
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    jcenter()
}

dependencies {
    implementation 'org.apache.poi:poi-ooxml:4.1.1'
    //required only for jdk 9 or above
    implementation('com.fasterxml.jackson.core:jackson-databind:2.10.1')
}

Create Date in Excel File

Now we will create date in excel file using Apache POI in Java technology. We will put four different kind of date values in excel file – default date, java.util.Date, java.util.Calendar and Java 8’s LocalDateTime.

package com.roytuts.apache.poi.excel.date;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.time.LocalDateTime;
import java.util.Calendar;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelDate {

	public static void main(String[] args) {
		final String fileName = "excel-date.xlsx";//"excel-date.xls";
		createExcel(fileName);
	}

	public static void createExcel(final String fileName) {
		// get the file extension
		String ext = ".xlsx";
		if (fileName != null) {
			int len = fileName.trim().lastIndexOf(".");
			ext = fileName.trim().substring(len);
		}

		Workbook workbook = null;

		// based on file extension create Workbook object
		if (".xls".equalsIgnoreCase(ext)) {
			workbook = new HSSFWorkbook();
		} else if (".xlsx".equalsIgnoreCase(ext)) {
			workbook = new XSSFWorkbook();
		}

		// create Sheet object
		// sheet name must not exceed 31 characters
		// the name must not contain 0x0000, 0x0003, colon(:), backslash(\),
		// asterisk(*), question mark(?), forward slash(/), opening square
		// bracket([), closing square bracket(])
		Sheet sheet = workbook.createSheet("my_sheet");

		// Create first row. Rows are 0 based.
		Row row = sheet.createRow((short) 0);

		// Create a cell
		// put a value in cell.
		// default Date
		row.createCell(0).setCellValue(new Date());

		// style Date
		CreationHelper createHelper = workbook.getCreationHelper();
		CellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("MM/dd/yyyy hh:mm:ss"));

		Cell cell = row.createCell(1);
		cell.setCellValue(new Date());
		cell.setCellStyle(cellStyle);

		// set date as java.util.Calendar
		CellStyle cellStyle2 = workbook.createCellStyle();
		cellStyle2.setDataFormat(createHelper.createDataFormat().getFormat("MMM/dd/yyyy hh:mm:ss"));
		cell = row.createCell(2);
		cell.setCellValue(Calendar.getInstance());
		cell.setCellStyle(cellStyle2);

		// set date as Java 8
		CellStyle cellStyle3 = workbook.createCellStyle();
		cellStyle3.setDataFormat(createHelper.createDataFormat().getFormat("MMM/dd/yyyy hh:mm:ss"));
		cell = row.createCell(3);
		cell.setCellValue(LocalDateTime.now());
		cell.setCellStyle(cellStyle3);

		FileOutputStream fileOut = null;
		try {
			fileOut = new FileOutputStream(fileName);
			workbook.write(fileOut);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				fileOut.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

}

Testing the Program

Run the above class, you will get excel-date.xlsx or excel-date.xls file created under the project root directory. Open the file you will get the output in sheet “my_sheet”.

create date in excel file using apache poi in java

Source Code

Download

That’s all. Thanks for reading.

Related posts

2 Thoughts to “Create Date in Excel File using Apache POI in Java”

  1. Retiro lo dicho anteriormente. Estaba con una version 3, cambie a la versión que tienes acá (4.1.1) y todo muy bien.
    Muchas gracias.

  2. Bueno amigo, a mi me ha salido que usted no puede asignar un LocalDateTime a Cell con setCellValue

Leave a Comment