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).

For more information please go through https://poi.apache.org/

If you already have an idea on how to create a maven project in Eclipse will be great otherwise I will tell you here how to create a maven project in Eclipse.

Prerequisites

The following things are required in order to run the application

Eclipse Kepler
JDK 1.8
Have maven 3 installed and configured
Apache POI dependencies in pom.xml

Now we will see the below steps how to create a maven based spring project in Eclipse

Step 1. Create a standalone maven project in Eclipse

Go to File -> New -> Other. On popup window under Maven select Maven Project. Then click on Next. Select the workspace location – either default or browse the location. Click on Next. Now in next window select the row as highlighted from the below list of archtypes and click on Next button.

maven-arctype-quickstart

Now enter the required fields (Group Id, Artifact Id) as shown below

Group Id : com.roytuts
Artifact Id : apache-poi

Step 2. Modify the pom.xml file as shown below.

<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</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
 
    <name>apache-poi</name>
    <url>http://maven.apache.org</url>
 
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <poi.version>3.12</poi.version>
        <junit.version>4.11</junit.version>
    </properties>
 
    <dependencies>
        <!-- apache poi for xls, doc etc reading/writing -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <!-- apache poi for xlsx, docx etc reading/writing -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>${junit.version}</version>
            <scope>test</scope>
        </dependency>
    </dependencies>
 
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Step 3. If you see JRE System Library[J2SE-1.5] then change the version by below process

Do right-click on the project and go to Build -> Configure build path, under Libraries tab click on JRE System Library[J2SE-1.5], click on Edit button and select the appropriate jdk 1.8 from the next window. Click on Finish then Ok.

Step 4. Create a class for creating and writing to the excel file

package com.roytuts.poi.excel;

import static com.roytuts.common.PoiConstants.EXCEL_EXT_XLS;
import static com.roytuts.common.PoiConstants.EXCEL_EXT_XLSX;

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

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 com.roytuts.common.PoiUtils;

public class ExcelDate {

  /**
   * @param args
   */
  public static void main(String[] args) {
    final String fileName = "workbook.xlsx";
    createExcel(fileName);
  }

  public static void createExcel(final String fileName) {
    // get the file extension
    String fileExt = PoiUtils.getFileExtension(fileName);
    Workbook workbook = null;
    // based on file extension create Workbook object
    if (EXCEL_EXT_XLS.equalsIgnoreCase(fileExt)) {
      workbook = PoiUtils.getHSSFWorkbook();
    } else if (EXCEL_EXT_XLSX.equalsIgnoreCase(fileExt)) {
      workbook = PoiUtils.getXSSFWorkbook();
    }
    // 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);

    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();
      }
    }
  }

}

Step 5. Create a utility class

package com.roytuts.common;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
public final class PoiUtils {
    private PoiUtils() {
    }
 
    public static String getFileExtension(final String fileName) {
        if (fileName != null) {
            int len = fileName.trim().lastIndexOf(".");
            String ext = fileName.trim().substring(len);
            return ext;
        }
        return "";
    }
 
    public static Workbook getHSSFWorkbook() {
        return new HSSFWorkbook();
    }
 
    public static Workbook getXSSFWorkbook() {
        return new XSSFWorkbook();
    }
}

Step 6. Create a class which holds constants

package com.roytuts.common;
 
public final class PoiConstants {
    private PoiConstants() {
    }
 
    public static final String EXCEL_EXT_XLS = ".xls";
    public static final String EXCEL_EXT_XLSX = ".xlsx";
}

Step 7. Run the above test class, you will get workbook.xlsx file created. Open the file you will get the output in sheet “my_sheet”.

That’s all. Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on Roy Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

Leave a Reply

Your email address will not be published. Required fields are marked *