Apache POI - Read and Write Excel files in java

This article shows you how to create and read excel files using the Apache POI. To Read and Write Excel files in Java, Apache provides a very popular library called Apache POI.

Microsoft Excel documents generally come in two different formats Excel 97(-2003) and Excel(2007+). ie XLS and XLSX formats. In this article, we will see the implementation of reading and writing for both Excel file formats.

Let’s begin now…

Table of Contents

Apache POI – API Basics to Read/Write Excel Files

The Apache POI library is capable to read and write both XLS and XLSX file formats of Excel. HSSF implementation is provided by the POI to read/write the XLS file. XSSF implementation is provided to read/write the XLSX file.

POI library provides various classes and interfaces for reading and writing Excel files. Let’s see the basic interfaces and classes here:

The basic interfaces are Workbook, Sheet, Row, Cell, CellStyle, Font, etc. And there are specific concrete classes for specific file formats (.xls and .xlsx).

For Excel file format 2003 (XLS): HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell, etc.

For Excel file format 2007 (XLSX): XSSFWorkbook, XSSFSheet, XSSFRow, XSSFCell, etc.

HSSFWorkbook & XSSFWorkbook – used to create the workbook
HSSFSheet & XSSFSheet – used to create the sheet for the excel file
HSSFRow & XSSFRow – used to create the row in the sheet
HSSFCell & XSSFCell – used to create the cell in the row

We recommend you use common interfaces to support both Excel format XLS and XLSX.

How to Write Excel file in java?

The Apache POI provides two major components to work with Excel files. HSSF component is provided to write the XLS file. XSSF component is provided to write the XLSX file.

Let’s see the implementations for both files format separately one by one and then we will see a generic implementation also for both formats.

Writing XLS file in java

Basic steps to writing data in an XLS file:

Create a HSSFWorkbook.
Create a HSSFSheet using createSheet().
Create a HSSFRow using createRow().
Create a HSSFCell using createCell().
Repeat the step-3 and step-4 until all data is processed.
Write workbook to an OutputStream.
Close the stream and workbook.

The below java code demonstrates writing data to an Excel(.xls) file:

package com.javacodepoint.excel;
 
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
 
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 
public class WriteExcelFile {
 
    // Main method
    public static void main(String[] args) {
 
        // Creating a xlsfile object with specific file path
        File xlsFile = new File("E:/Excel/employee.xls");
 
        // Writing the xls file
        writeXLSFile(xlsFile);
 
    }
 
    // Method to write xls file
    public static void writeXLSFile(File xlsFile) {
 
        try {
            // Creating workbook
            HSSFWorkbook workbook = new HSSFWorkbook();
 
            // Creating sheet
            HSSFSheet sheet = workbook.createSheet("Employee Records");
 
            // Creating header of the excel sheet
            HSSFRow header = sheet.createRow(0);
 
            // Creating cell and setting the cell value
            header.createCell(0).setCellValue("Employee Id");
            header.createCell(1).setCellValue("Employee Name");
            header.createCell(2).setCellValue("Age");
            header.createCell(3).setCellValue("Email ID");
            header.createCell(4).setCellValue("Salary");
 
            // Creating the 1st row to insert employee record
            HSSFRow row1 = sheet.createRow(1);
 
            // Inserting 1st employee record
            row1.createCell(0).setCellValue("101");
            row1.createCell(1).setCellValue("John william");
            row1.createCell(2).setCellValue("30");
            row1.createCell(3).setCellValue("[email protected]");
            row1.createCell(4).setCellValue("15000$");
 
            // Creating the 2nd row
            HSSFRow row2 = sheet.createRow(2);
 
            // Inserting 2nd employee record
            row2.createCell(0).setCellValue("102");
            row2.createCell(1).setCellValue("Harsh singh");
            row2.createCell(2).setCellValue("35");
            row2.createCell(3).setCellValue("[email protected]");
            row2.createCell(4).setCellValue("20000$");
 
            // Creating file output stream to write the workbook data in to the file
            FileOutputStream fos = new FileOutputStream(xlsFile);
 
            // Writing workbook
            workbook.write(fos);
 
            // Closing the output stream
            fos.close();
 
            // Closing the workbook
            workbook.close();
 
            // Printing the success message on the console
            System.out.println("Excel(.xls) file has been created successfully.");
 
        } catch (IOException e) {
            System.out.println("Exception while writting xls file");
            e.printStackTrace();
        }
    }
 
}

Below is a screenshot of the created Excel file-

Writing XLSX file in java

Basic steps to writing data in an XLSX file:

Create a XSSFWorkbook.
Create a XSSFSheet using createSheet().
Create a XSSFRow using createRow().
Create a XSSFCell using createCell().
Repeat the step-3 and step-4 until all data is processed.
Write workbook to an OutputStream.
Close the stream and workbook.

The below java code demonstrates writing data to an Excel(.xlsx) file:

package com.javacodepoint.excel;
 
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
 
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
public class WriteExcelFile {
 
    // Main method
    public static void main(String[] args) {
 
        // Creating a xlsxfile object with specific file path
        File xlsxFile = new File("E:/Excel/employee.xlsx");
         
        // Writing the xlsx file
        writeXLSXFile(xlsxFile);
    }
 
    // Method to write xlsx file
    public static void writeXLSXFile(File xlsxFile) {
 
        try {
            // Creating workbook
            XSSFWorkbook workbook = new XSSFWorkbook();
 
            // Creating sheet
            XSSFSheet sheet = workbook.createSheet("Employee Records");
 
            // Creating header of the excel sheet
            XSSFRow header = sheet.createRow(0);
 
            // Creating cell and setting the cell value
            header.createCell(0).setCellValue("Employee Id");
            header.createCell(1).setCellValue("Employee Name");
            header.createCell(2).setCellValue("Age");
            header.createCell(3).setCellValue("Email ID");
            header.createCell(4).setCellValue("Salary");
 
            // Creating the 1st row to insert employee record
            XSSFRow row1 = sheet.createRow(1);
 
            // Inserting 1st employee record
            row1.createCell(0).setCellValue("101");
            row1.createCell(1).setCellValue("John william");
            row1.createCell(2).setCellValue("30");
            row1.createCell(3).setCellValue("[email protected]");
            row1.createCell(4).setCellValue("15000$");
 
            // Creating the 2nd row
            XSSFRow row2 = sheet.createRow(2);
 
            // Inserting 2nd employee record
            row2.createCell(0).setCellValue("102");
            row2.createCell(1).setCellValue("Harsh singh");
            row2.createCell(2).setCellValue("35");
            row2.createCell(3).setCellValue("[email protected]");
            row2.createCell(4).setCellValue("20000$");
 
            // Creating file output stream to write the workbook data in to the file
            FileOutputStream fos = new FileOutputStream(xlsxFile);
 
            // Writing workbook
            workbook.write(fos);
 
            // Closing the output stream
            fos.close();
 
            // Closing the workbook
            workbook.close();
 
            // Printing the success message on the console
            System.out.println("Excel(.xlsx) file has been created successfully.");
 
        } catch (IOException e) {
            System.out.println("Exception while writting xlsx file");
            e.printStackTrace();
        }
    }
 
}

[Generic logic] Writing both(XLS and XLSX) file in java

The following java code demonstrates writing data to an Excel(.xls and .xlsx) file to support both formats:

package com.javacodepoint.excel;
 
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 WriteExcelFile {
 
    // Main method
    public static void main(String[] args) {
 
        // Creating a xlsxfile object with specific file path
        File xlsxFile = new File("E:/Excel/employee.xlsx");
         
        // Common method to write excel file based on file format
        writeExcelFile(xlsxFile);
    }
 
    // Method to write both excel file xls and xlsx format
    public static void writeExcelFile(File excelFile) {
 
        try {
            Workbook workbook = null;
 
            // Creating workbook based on file format
            if (excelFile.getName().endsWith(".xls")) {
                workbook = new HSSFWorkbook();
            } else if (excelFile.getName().endsWith(".xlsx")) {
                workbook = new XSSFWorkbook();
            } else {
                throw new IllegalArgumentException("The specified file is not supported");
            }
 
            // Creating sheet
            Sheet sheet = workbook.createSheet("Employee Records");
 
            // Creating header of the excel sheet
            Row header = sheet.createRow(0);
 
            // Creating cell and setting the cell value
            header.createCell(0).setCellValue("Employee Id");
            header.createCell(1).setCellValue("Employee Name");
            header.createCell(2).setCellValue("Age");
            header.createCell(3).setCellValue("Email ID");
            header.createCell(4).setCellValue("Salary");
 
            // Creating the 1st row to insert employee record
            Row row1 = sheet.createRow(1);
 
            // Inserting 1st employee record
            row1.createCell(0).setCellValue("101");
            row1.createCell(1).setCellValue("John william");
            row1.createCell(2).setCellValue("30");
            row1.createCell(3).setCellValue("[email protected]");
            row1.createCell(4).setCellValue("15000$");
 
            // Creating the 2nd row
            Row row2 = sheet.createRow(2);
 
            // Inserting 2nd employee record
            row2.createCell(0).setCellValue("102");
            row2.createCell(1).setCellValue("Harsh singh");
            row2.createCell(2).setCellValue("35");
            row2.createCell(3).setCellValue("[email protected]");
            row2.createCell(4).setCellValue("20000$");
 
            // Creating file output stream to write the workbook data in to the file
            FileOutputStream fos = new FileOutputStream(excelFile);
 
            // Writing workbook
            workbook.write(fos);
 
            // Closing the output stream
            fos.close();
 
            // Closing the workbook
            workbook.close();
 
            // Printing the success message on the console
            System.out.println("Excel file has been created successfully.");
 
        } catch (IOException e) {
            System.out.println("Exception while writting excel file");
            e.printStackTrace();
        }
    }
 
}

How to Read Excel file in java?

In order to read the Excel file, the above same components HSSF(denotes the API is for working with Excel 2003 and earlier) and XSSF(denotes the API is for working with Excel 2007 and later) of Apache POI will be used.

Let’s assume the sample excel file which we are going to read looks like the below screenshot:

The above excel sheet contains the information about Employees including the Employee ID, Employee Name, Age, Email ID, Salary.

Let’s see the separate implementation for both file formats.

Reading XLS file in java

Below is the java code example that read excel’s first sheet and cell value of each row one by one:

package com.javacodepoint.excel;
 
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
 
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.Row;
 
public class ReadXLSExcelFile {
 
    public static void main(String[] args) throws IOException {
 
        // Creating a xls file object with specific file path to read
        File xlsFile = new File("E:/Excel/employee.xls");
 
        // Creating input stream
        FileInputStream inputStream = new FileInputStream(xlsFile);
 
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
         
        // Reading the first sheet of the excel file
        HSSFSheet sheet = workbook.getSheetAt(0);
         
        Iterator<Row> iterator = sheet.iterator();
 
        // Iterating all the rows
        while (iterator.hasNext()) {
            Row nextRow = iterator.next();
            Iterator<Cell> cellIterator = nextRow.cellIterator();
 
            // Iterating all the columns in a row
            while (cellIterator.hasNext()) {
                 
                Cell cell = cellIterator.next();
 
                switch (cell.getCellType()) {
                case STRING:
                    System.out.print(cell.getStringCellValue());
                    break;
                case BOOLEAN:
                    System.out.print(cell.getBooleanCellValue());
                    break;
                case NUMERIC:
                    System.out.print(cell.getNumericCellValue());
                    break;
                default:
                    break;
                }
                System.out.print(" | ");
            }
            System.out.println();
        }
 
        // Closing the workbook and input stream
        workbook.close();
        inputStream.close();
    }
 
}

OUTPUT:

Employee Id | Employee Name | Age | Email ID | Salary |
101 | John william | 30 | [email protected] | 15000$ |
102 | Harsh singh | 35 | [email protected] | 20000$ |

Reading XLSX file in java

To read Excel 2007(.xlsx) file format, it is also almost similar to the Excel 2003(.xls). The only difference here is we have to use XSSFWorkbook and XSSFSheet classes instead of HSSFWorkbook and HSSFSheet.

See the below complete java code:

package com.javacodepoint.excel;
 
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
public class ReadXLSXExcelFile {
 
    public static void main(String[] args) throws IOException {
 
        // Creating a xlsx file object with specific file path to read
        File xlsxFile = new File("E:/Excel/employee.xlsx");
 
        // Creating input stream
        FileInputStream inputStream = new FileInputStream(xlsxFile);
 
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
         
        // Reading the first sheet of the excel file
        XSSFSheet sheet = workbook.getSheetAt(0);
         
        Iterator<Row> iterator = sheet.iterator();
 
        // Iterating all the rows
        while (iterator.hasNext()) {
            Row nextRow = iterator.next();
            Iterator<Cell> cellIterator = nextRow.cellIterator();
 
            // Iterating all the columns in a row
            while (cellIterator.hasNext()) {
                 
                Cell cell = cellIterator.next();
 
                switch (cell.getCellType()) {
                case STRING:
                    System.out.print(cell.getStringCellValue());
                    break;
                case BOOLEAN:
                    System.out.print(cell.getBooleanCellValue());
                    break;
                case NUMERIC:
                    System.out.print(cell.getNumericCellValue());
                    break;
                default:
                    break;
                }
                System.out.print(" | ");
            }
            System.out.println();
        }
 
        // Closing the workbook and input stream
        workbook.close();
        inputStream.close();
    }
 
}

[Generic logic] Reading both(XLS and XLSX) file in java

To read both file formats in a single program, we have to use common interfaces Workbook, Sheet instead of specific classes like HSSFWorkbook, XSSFWorkbook, HSSFSheet, XSSFSheet, etc.

Below is the java code example that read the first sheet and the cell value of each row one by one:

package com.javacodepoint.excel;
 
import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;
 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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 ReadBothFormatExcelFile {
 
    public static void main(String[] args) throws Exception {
 
        // Creating a xlsx file object with specific file path to read
        File xlsxFile = new File("E:/Excel/employee.xlsx");
 
        // Creating input stream
        FileInputStream inputStream = new FileInputStream(xlsxFile);
 
        Workbook workbook = null;
 
        // Based on type workbook will crate
        if (xlsxFile.getName().endsWith(".xlsx")) {
            workbook = new XSSFWorkbook(inputStream);
        } else if (xlsxFile.getName().endsWith(".xls")) {
            workbook = new HSSFWorkbook(inputStream);
        } else {
            inputStream.close();
            throw new Exception("File not supported!");
        }
 
        // Reading the first sheet of the excel file
        Sheet sheet = workbook.getSheetAt(0);
 
        Iterator<Row> iterator = sheet.iterator();
 
        // Iterating all the rows
        while (iterator.hasNext()) {
            Row nextRow = iterator.next();
            Iterator<Cell> cellIterator = nextRow.cellIterator();
 
            // Iterating all the columns in a row
            while (cellIterator.hasNext()) {
 
                Cell cell = cellIterator.next();
 
                switch (cell.getCellType()) {
                case STRING:
                    System.out.print(cell.getStringCellValue());
                    break;
                case BOOLEAN:
                    System.out.print(cell.getBooleanCellValue());
                    break;
                case NUMERIC:
                    System.out.print(cell.getNumericCellValue());
                    break;
                default:
                    break;
                }
                System.out.print(" | ");
            }
            System.out.println();
        }
 
        // Closing the workbook and input stream
        workbook.close();
        inputStream.close();
    }
 
}

Creating password-protected Excel file in java

Use Biff8EncryptionKey.setCurrentUserPassword(String password) to specify the decryption password before saving or writing the workbook of the excel file.

Let’s see the below java code snippet to protect XLS files. We are setting “javacodepoint” as a password here,

// Creating a xlsfile object with specific file path
File xlsFile = new File("E:/Excel/protected.xls");
 
// Setting the file password = javacodepoint
// It should be called before saving the workbook
Biff8EncryptionKey.setCurrentUserPassword("javacodepoint");
 
 // Creating workbook
HSSFWorkbook workbook = new HSSFWorkbook();

//Creating the sheet data....

// Creating file output stream to write the workbook data
FileOutputStream fos = new FileOutputStream(xlsFile);
 
// Writing workbook
workbook.write(fos);

See our complete guide to create a protected Excel file here: How to create password-protected Excel in java?

Reading password-protected Excel file in java

To read a password-protected XLSX file we need to follow simple steps. Below is the sample code snippet to understand this,

POIFSFileSystem fs = new POIFSFileSystem(xlsxFile);
EncryptionInfo info = new EncryptionInfo(fs);
Decryptor decryptor = Decryptor.getInstance(info);
              
 //Verifying the password
 if (!decryptor.verifyPassword("javacodepoint")) {
        throw new RuntimeException("Incorrect password: Unable to process");
 }
              
InputStream dataStream = decryptor.getDataStream(fs);
  
// Now parse dataStream

Let’s see another article for the complete guide of reading password-protected XLS, XLSX, and both here: How to Read password-protected Excel in java?

Apache POI – Environment setup in Eclipse

There are two ways for installing Apache POI in Eclipse, based on your project type:

Maven Project
Stand alone Java Project (Non Maven)

Note: In this article, we have used the Apache POI-4.0.1 version of the library to demonstrate.

Maven Project

If you are going to create a maven project then you have to add the following maven dependency in the pom.xml file of your project:

<!-- Used for Excel 2003 or before (xls) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
 
<!-- Used for Excel 2007 or later (xlsx)  -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>

Stand alone Java Project (Non Maven)

If you are going to create a standalone java project then you have to add the following jar files into your java build path:

poi-4.0.1.jar
poi-ooxml-4.0.1.jar
poi-ooxml-schemas-4.0.1.jar
xmlbeans-3.0.2.jar
curvesapi-1.05.jar
commons-codec-1.11.jar
commons-collections4-4.2.jar
commons-compress-1.18.jar
commons-math3-3.6.1.jar

You can easily download all the above jars in one place: Download Apache POI Jars

If you want to know the complete environment setup for Apache POI in Eclipse IDE, follow another article here: Apache POI – Getting Started

Conclusion

In this article, you have seen how to create, read and write an Excel document in Java using the Apache POI library. You have seen the HSSF and XSSF are two different components provided by POI used for specific Excel file formats(XLS and XLSX).

You might like this:

Share with friends

Apache POI – Read and Write Excel files in java