Thursday, May 3, 2012

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

4 comments:

  1. Great job... I came across another great Java Excel component by the name of Aspose.Cells for Java. It is not open source, but contains very powerful features. It allows Java developers to embed the ability to read, write and manipulate Excel® spreadsheets (XLS, XLSX, XLSM, SpreadsheetML, CSV) EML, HTML, PDF and image file formats into their own Java applications. Some Examples with code are:
    http://www.aspose.com/docs/display/cellsjava/Opening+Files
    http://www.aspose.com/docs/display/cellsjava/Copying+and+Moving+Worksheets
    http://www.aspose.com/docs/display/cellsjava/Save+Entire+Workbook+into+Text+or+CSV+Format
    I am sure it can help

    ReplyDelete
  2. Successfully file is created ,but unable to open file

    ReplyDelete
  3. Code
    String excelFileName = "/tmp/TestFile.xlsx";//name of excel file

    String sheetName = "Sheet1";//name of sheet

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet(sheetName) ;

    //iterating r number of rows
    for (int r=0;r < 5; r++ )
    {
    XSSFRow row = sheet.createRow(r);

    //iterating c number of columns
    for (int c=0;c < 5; c++ )
    {
    XSSFCell cell = row.createCell(c);
    cell.setCellValue("Cell "+r+" "+c);
    }
    }

    FileOutputStream fileOut = new FileOutputStream(excelFileName);

    //write this workbook to an Outputstream.
    wb.write(fileOut);
    fileOut.flush();
    fileOut.close();

    }

    ReplyDelete
  4. See very simple example here http://www.javaproficiency.com/2015/03/create-xls-file-in-java.html

    ReplyDelete