Java : Create xls or xlsx files using Apache's POI

Apache's POI-HSSF and POI-XSSF is a Java API to access Microsoft Excel Format Files.

HSSF is used to create Excel '97(-2007) file format and XSSF is used to create Excel 2007 OOXML (.xlsx) file format.

HSSF and XSSF provides ways to read spreadsheets create, modify, read and write XLS spreadsheets. They provide:

• low level structures for those with special needs
• an eventmodel api for efficient read-only access
• a full usermodel api for creating, reading and modifying XLS files

To know more, click here

To run below programs you will need to add POI jar file i.e. poi-bin-3.8-20120326.zip. To download latest version of jar files, click here

For running below programs i have used apache poi-3.8. First we will create .xls file then we will see how to create .xlsx file


Java program to create .xls file

To run this program you will need to add below mentioned jar file in your classpath. To download latest version of jar files, click here

• poi-3.8-20120326.jar  

/* POIExample.java, Java program to create .xls file*/

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class POIExample {
 
 public static void main(String[] args) throws IOException {
  
  FileOutputStream fileOut = new FileOutputStream("c:/TestFile.xls");
  HSSFWorkbook wb = new HSSFWorkbook();
  HSSFSheet worksheet = wb.createSheet("Worksheet");

  int rows = 5;
  int column = 4;
  
  for (int i=0;i< rows;i++) {
   
   HSSFRow row = worksheet.createRow(i);
   
   for(int j=0;j< column;j++) {
    
    HSSFCell cell = row.createCell(j);
    cell.setCellValue("Cell "+i+" "+j);
   }
  }
  
  wb.write(fileOut);
  fileOut.flush();
  fileOut.close();

  System.out.println("File created Successfully!!");
 }
}

Java program to create .xlsx file

To run this program you will need to add below mentioned jar files in your classpath. To download latest version of jar files, click here

• poi-3.8-20120326.jar
• poi-ooxml-3.8-20120326.jar
• xmlbeans-2.3.0.jar
• poi-ooxml-schemas-3.8-20120326.jar
• dom4j-1.6.1.jar

/* POIExample.java, Java program to create .xlsx file*/

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class POIExample {
 
 public static void main(String[] args) throws IOException 
 {
  FileOutputStream fileOut = new FileOutputStream("c:/TestFile.xlsx");  
  XSSFWorkbook wb = new XSSFWorkbook();
  
  XSSFSheet worksheet = wb.createSheet("XWorksheet");
  
  int rows = 5;
  int column = 4;
  
  for (int i=0;i< rows;i++) {
   
   XSSFRow row = worksheet.createRow(i);
   
   for(int j=0;j< column;j++) {
    
    XSSFCell cell = row.createCell(j);
    cell.setCellValue("Cell "+i+" "+j);
   }
  }
  
  wb.write(fileOut);
  fileOut.flush();
  fileOut.close();

  System.out.println("File created Successfully!!");
 }

}

Note : To avoid below exceptions, please add said JAR file(s) in your classpath. To download latest version of jar files, click here

1. xmlbeans-2.3.0.jar

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlObject
 at com.javaxp.POIExcelExample1.main(POIExcelExample1.java:16)
Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlObject
 at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
 at java.security.AccessController.doPrivileged(Native Method)
 at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
 at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
 at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
 at java.lang.ClassLoader.loadClass(ClassLoader.java:248)
 ... 1 more

2. poi-ooxml-schemas-3.8-20120326.jar

Exception in thread "main" java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTSheet
 at com.javaxp.POIExcelExample1.main(POIExcelExample1.java:16)
Caused by: java.lang.ClassNotFoundException: org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet
 at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
 at java.security.AccessController.doPrivileged(Native Method)
 at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
 at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
 at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
 at java.lang.ClassLoader.loadClass(ClassLoader.java:248)
 ... 1 more

3. dom4j-1.6.1.jar

Exception in thread "main" java.lang.NoClassDefFoundError: org/dom4j/DocumentException
 at org.apache.poi.openxml4j.opc.OPCPackage.init(OPCPackage.java:154)
 at org.apache.poi.openxml4j.opc.OPCPackage.(OPCPackage.java:141)
 at org.apache.poi.openxml4j.opc.Package.(Package.java:54)
 at org.apache.poi.openxml4j.opc.ZipPackage.(ZipPackage.java:67)
 at org.apache.poi.openxml4j.opc.OPCPackage.create(OPCPackage.java:293)
 at org.apache.poi.xssf.usermodel.XSSFWorkbook.newPackage(XSSFWorkbook.java:316)
 at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:169)
 at com.javaxp.POIExcelExample1.main(POIExcelExample1.java:16)
Caused by: java.lang.ClassNotFoundException: org.dom4j.DocumentException
 at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
 at java.security.AccessController.doPrivileged(Native Method)
 at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
 at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
 at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
 at java.lang.ClassLoader.loadClass(ClassLoader.java:248)
 ... 8 more