Reading Writing Excel File in JAVA

You might aware that Excel file now comes with two formats, XLS file which is an OLE format and XLSX format, which is also known as OpenXML format. Apache POI supports both format but you would need different JAR files to read/write XLS and XLSX files. You need poi-3.12.jar to read XLS file and poi-ooxml-3.12.jar to read XLSX file in Java.

You can write different OLE formats using  poi-3.12.jar for example you can also use this JAR to read Microsoft Word files witch .DOC extension and Microsoft PowerPoint files with .PPT extension in Java. Similarly you can read other OpenXML format e.g. DOCX and PPTX using poi-ooxml-3.12.jar file. It's very important to understand which JAR files you need to read which kind of Excel files in Java, because classes used to read different Excel file format are different e.g. to read old Excel file format i.e. XLS files you need HSSFWorkbook class, which is inside poi-XX.jar, while class used to read current Excel file format i.e. XLSX file  is XSSFWorkbook, which is inside poi-ooxml.jar library.

Apache POI JARs to Read/Write Excel File in Java

Though there are couple of open source library available to read and write from Excel file in Java e.g. JXL, the most feature rich and most popular one is Apache POI library. You can read both types of Excel file format using this library. In order to use this library either you need to download POI JAR files and add into your Eclipse's build path manually or you can use Maven to download dependency for you.

If you are using Maven then include following two dependencies to use Apache POI in your Java program :

<dependencies>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.12</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.12</version>
    </dependency>
  </dependencies>

Main advantage of using Maven is that it not only downloads direct dependency e.g.  poi.jar and poi-ooxml.jar but also download transitive dependency e.g. JARS on which POI library is internally dependent.  For example, I have just specified Apache POI JAR files but Maven will also download xmlbeans-2.6.0.jar, stax-api-1.0.1.jar, poi-ooxml-schemas-3.12.jar and commons-codec-1.9.jar. 

JAR Dependencies :

If you are more comfortable by downloading JAR files by yourself, you can download Apache POI JARS  from here . This will download whole bundle so you don't need to worry, but make sure it contains following JAR files if your application is going to support both XLS and XLSX format.

• poi-3.12.jar
• commons-codec-1.9.jar
• poi-ooxml-3.12.jar
• poi-ooxml-schemas-3.12.jar
• xmlbeans-2.6.0.jar
• stax-api-1.0.1.jar

POI is for reading OLE format e.g. XLS, DOC and .PPT format, while poi-ooxml.jar is to read XLSX, DOCX and .PPTX format. Don't download just POI jar, always include transitive dependency. For example, if you include just poi-3.12.jar then your program will compile fine because you are not using transitive dependency e.g. xmlbeans directly but it will fail at runtime with error like java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlObject because of missing xmlbeans.jar dependency.

How to read from Excel File in Java

Suppose you have a cell in your excel file which contains a date e.g. birthdate? how do you read it? Most of you will say that you will read that cell by first creating a Workbook, then getting a sheet from that workbook, then getting the cell from that sheet which is containing date value and finally getting cell value from that cell. Cool, these are the steps to read data from Excel file in Java, but you forgot one thing you need to find the cell type before getting cell value, otherwise you will be get error reading that cell. Reading date values are even more tricky. To your surprise, there is no date cell type in Excel (both XLS and XLSX),  instead Excel stores date as numeric type. So you need to compare the cell type with HSSFCell.CELL_TYPE_NUMERIC if you are reading XLS file and XSSFCell.CELL_TYPE_NUMERIC if you reading XLSX file, but story doesn't end here, if you just print the cell value by using getNumericCellValue(), you will not get any error but you will see an arbitrary number. In order to print the actual date value you need to use method getDateCellValue(), which will return an object of java.util.Date, if you want to display a formatted date, then you need to format date using SimpleDateFormat or by using Joda Date and Time library.

In our example, we will create an excel file which contains one row and two columns. First column will contain a String type, where we will store name and second column will be of date type, where we will date of birth. Later, we will read the same excel file in our Java program to display name and date values in to console. In order to read an excel file in Java, it must be in classpath. In order to avoid issues, I will use Eclipse IDE to write this program and it will create excel file in Eclipse's project directly, which always remain in classpath.

How to read/write from XLS file in Java

This is our first example to read String and date values from Excel file in Java. In this example, we are first creating old Excel file format i.e. XLS file birthdays.xls and later we will read from the same file. Once we run our program, you can see this excel file created in your Eclipse project directory, as shown below.

Steps to write Data into XLS file in Java

• Include poi-3.12.jar in your Java program's classpath
• Create an object of HSSFWorkBook
• Create a Sheet on that workbook by calling createSheet() method 
• Create a Row on that sheet by calling createRow() method
• Create a Cell by calling createCell() method
• Set value to that cell by calling setCellValue() method.
• Write workbook content into File using FileOutputStream object.
• Close the workbook object by calling close() method

These steps are fine for writing String and Numeric values but in order to write date values into Excel file, you need to follow following more steps :

• Create a DataFormat
• Create a CellStyle
• Set format into CellStyle
• Set CellStyle into Cell
• Write java.util.Date into Cell

Step to read data from XLS file in Java

• Include poi-3.12.jar in your Java program's classpath
• Create an object of HSSFWorkBook by opening excel file using FileInputStream
• Get a Sheet from workbook by calling getSheet() method, you can pass name or sheet index
• Get a Row from that sheet by calling getRow() method, you can pass index
• Get a Cell by calling getCell() method
• Get the Cell type by calling getCellType() method.
• Depending upon Cell type, call getStringCellValue(), getNumericCellValue() or getDateCellValue() method to get value.
• Close the workbook object by calling close() method

If you are reading date values then just one more thing to remember that there is no cell with date type and Excel stores date as numeric type. So always compare type of a cell with date value to a numeric cell type.

In this program, reading and writing logic are encapsulated into two static utility method readFromExcel() and writeIntoExcel(), so you can also take a look at them for exact code for reading writing XLS file in Java.

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

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;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * Simple Java Program to read and write dates from Excel file in Java.
 * This example particularly read Excel file in OLE format i.e.
 * Excel file with extension .xls, also known as XLS files.
 * 
 * @author WINDOWS 8
 *
 */
public class ExcelDateReader {

    public static void main(String[] args) throws FileNotFoundException, IOException {
        writeIntoExcel("birthdays.xls");
        readFromExcel("birthdays.xls");
    }
    
    /**
     * Java method to read dates from Excel file in Java.
     * This method read value from .XLS file, which is an OLE
     * format. 
     * 
     * @param file
     * @throws IOException 
     */
    public static void readFromExcel(String file) throws IOException{
        HSSFWorkbook myExcelBook = new HSSFWorkbook(new FileInputStream(file));
        HSSFSheet myExcelSheet = myExcelBook.getSheet("Birthdays");
        HSSFRow row = myExcelSheet.getRow(0);
        
        if(row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_STRING){
            String name = row.getCell(0).getStringCellValue();
            System.out.println("name : " + name);
        }
        
        if(row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
            Date birthdate = row.getCell(1).getDateCellValue();
            System.out.println("birthdate :" + birthdate);
        }
        
        myExcelBook.close();
        
    }
    
    /**
     * Java method to write dates from Excel file in Java.
     * This method write value into .XLS file in Java.
     * @param file, name of excel file to write.
     * @throws IOException 
     * @throws FileNotFoundException 
     */
    @SuppressWarnings("deprecation")
    public static void writeIntoExcel(String file) throws FileNotFoundException, IOException{
        Workbook book = new HSSFWorkbook();
        Sheet sheet = book.createSheet("Birthdays");

        // first row start with zero
        Row row = sheet.createRow(0); 
        
        // we will write name and birthdates in two columns
        // name will be String and birthday would be Date
        // formatted as dd.mm.yyyy
        Cell name = row.createCell(0);
        name.setCellValue("John");
        
        Cell birthdate = row.createCell(1);
        
        // steps to format a cell to display date value in Excel
        // 1. Create a DataFormat
        // 2. Create a CellStyle
        // 3. Set format into CellStyle
        // 4. Set CellStyle into Cell
        // 5. Write java.util.Date into Cell
        DataFormat format = book.createDataFormat();
        CellStyle dateStyle = book.createCellStyle();
        dateStyle.setDataFormat(format.getFormat("dd.mm.yyyy"));
        birthdate.setCellStyle(dateStyle);
        
        // It's very trick method, deprecated, don't use
        // year is from 1900, month starts with zero
        birthdate.setCellValue(new Date(110, 10, 10));
        
        // auto-resizing columns
        sheet.autoSizeColumn(1);
        
        // Now, its time to write content of Excel into File
        book.write(new FileOutputStream(file));
        book.close();
    }
}


Output
name : John
birthdate :Wed Nov 10 00:00:00 GMT+08:00 2010

In our program, we have first created excel file with String and date columns and later read from the same file and displayed the values into console. Now let's verify output of this program. It's correctly display the date value, though not formatted, which means excel file was created successfully and later Java was able to read from it. If you look at your Eclipse project directory, you will find birthdays.xls file created there, if you open that with Microsoft Excel or any Open Office editor, you will see following output.

This is because I haven't included sheet.autoSizeColumn(1) method call in first run and since column width is not enough to display the date in requested format e.g. dd.mm.yyyy it just displays ######. In order to solve this problem of date not displaying properly, all you need to do is enable autosizing of columns in Excel by calling sheet.autoSizeColumn(1) method, where column index is the column you want to resize automatically. If you run the program again with that code, you can see the date values properly formatted and fitted in requested column, as shown below

Apache POI Example to read XLSX file in Java

Reading and writing into new excel file format XLSX is also same, all you need to do is include poi-ooxml.jar and replace all HSFF classes with XSSF classes e.g. instead of using HSSFWorkbook, use XSSFWorkbook, instead of using HSFFSheet use XSSFSheet, instead of using HSSFRow use XSSFRow and instead of using HSSFCell just use XSSFCell class. Rest of the code and steps will be same. In following Java program, I will show you how to read XLSX file in Java. In this program also we are first creating an excel file and writing string and date values into it and later reading from same excel file and displaying data into console, only difference this time would be instead of creating an XLS file, our program will create an XLSX file. Once you run this program in your Eclipse IDE, you can see the birthdays.xlsx file created in your Eclipse Project directory, as shown below :

here is our java program to read XLSX files using Apache POI library.

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

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
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.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * This program read date values from XLSX file in Java using Apache POI.
 * 
 * @author WINDOWS 8
 *
 */
public class ExcelDateReader {

    public static void main(String[] args) throws FileNotFoundException, IOException {
        writeIntoExcel("birthdays.xlsx");
        readFromExcel("birthdays.xlsx");
    }
    
    public static void readFromExcel(String file) throws IOException{
        XSSFWorkbook myExcelBook = new XSSFWorkbook(new FileInputStream(file));
        XSSFSheet myExcelSheet = myExcelBook.getSheet("Birthdays");
        XSSFRow row = myExcelSheet.getRow(0);
        
        if(row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_STRING){
            String name = row.getCell(0).getStringCellValue();
            System.out.println("NAME : " + name);
        }
        
        if(row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
            Date birthdate = row.getCell(1).getDateCellValue();
            System.out.println("DOB :" + birthdate);
        }
        
        myExcelBook.close();
        
    }

    @SuppressWarnings("deprecation")
    public static void writeIntoExcel(String file) throws FileNotFoundException, IOException{
        Workbook book = new XSSFWorkbook();
        Sheet sheet = book.createSheet("Birthdays");
        Row row = sheet.createRow(0); 

        Cell name = row.createCell(0);
        name.setCellValue("Gokul");
        
        Cell birthdate = row.createCell(1);
        DataFormat format = book.createDataFormat();
        CellStyle dateStyle = book.createCellStyle();
        dateStyle.setDataFormat(format.getFormat("dd.mm.yyyy"));
        birthdate.setCellStyle(dateStyle);

        birthdate.setCellValue(new Date(115, 10, 10));
        
        sheet.autoSizeColumn(1);
        
        book.write(new FileOutputStream(file));
        book.close();
    }
}

NAME : Gokul
DOB :Tue Nov 10 00:00:00 GMT+08:00 2015

That's all about how to read and write from Excel file in Java. You have now learned  how to read and write both String and Date from XLS as well as XLSX file in Java. You can do a lot more using Apache POI library but this guide will help you learn and quickly use this library. Once again I suggest to use Maven for including POI dependency and if you are downloading JAR, make sure you download transitive dependency e.g. xmlbeans.