Core Java

Write/Read Excel files in Java Example

In this example we will see how we can write and read excel files in Java.

For writing/reading Excel Files we will use the library Apache POI

Apache POI provides Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2).

Using Apache POI HSSF and XSSF APIs we can read/write both xls and xlsx formats respectively.

In our examples we will use xlsx files and hence use XSSF APIs.

Now, lets see the examples:

1. Read Excel file in Java

In order to use Apache POI we need to add its dependencies, if you are using ANT you can download JARs from here and add it to your classpath.
If you are using MAVEN you can add these dependencies into your POM.XML file :

pom.xml:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.10-FINAL</version>
</dependency>
    <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.10-FINAL</version>
</dependency>

Once the dependencies are satisfied and project is build, lets create a test xlsx file which we will read from our Java code.

Below is the excel file :

This Excel file contains data for Students and their marks
This Excel file contains data for Students and their marks

This Excel file contains data for Students and their marks. We will read this data and store it in a list of Java Objects.
This Java class used here is Student :

Student.java

package com.jcg.example;

/**
 * Created by anirudh on 20/10/14.
 */
public class Student {

    private String name;
    private String maths;
    private String science;
    private String english;

    public Student(){}

    public Student(String name, String maths, String science, String english) {
        this.name = name;
        this.maths = maths;
        this.science = science;
        this.english = english;
    }

   //getters and setter..

    @Override
    public String toString() {
        return name+ ": Maths "+maths+ " Science "+science+" English "+english;
    }
}

Now, lets see the Java Code to read this Excel file:

ReadExcelFileExample.java

package com.jcg.example;

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;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * Created by anirudh on 20/10/14.
 */
public class ReadExcelFileExample {

    private static final String FILE_PATH = "/Users/anirudh/Projects/JCGExamples/JavaWriteReadExcelFileExample/testReadStudents.xlsx";

    public static void main(String args[]) {

        List studentList = getStudentsListFromExcel();

        System.out.println(studentList);
    }

    private static List getStudentsListFromExcel() {
        List studentList = new ArrayList();
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(FILE_PATH);

            // Using XSSF for xlsx format, for xls use HSSF
            Workbook workbook = new XSSFWorkbook(fis);

            int numberOfSheets = workbook.getNumberOfSheets();

            //looping over each workbook sheet
            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheet = workbook.getSheetAt(i);
                Iterator rowIterator = sheet.iterator();

                //iterating over each row
                while (rowIterator.hasNext()) {

                    Student student = new Student();
                    Row row = rowIterator.next();
                    Iterator cellIterator = row.cellIterator();

                    //Iterating over each cell (column wise)  in a particular row.
                    while (cellIterator.hasNext()) {

                        Cell cell = cellIterator.next();
                        //The Cell Containing String will is name.
                        if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                            student.setName(cell.getStringCellValue());

                            //The Cell Containing numeric value will contain marks
                        } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {

                            //Cell with index 1 contains marks in Maths
                            if (cell.getColumnIndex() == 1) {
                                student.setMaths(String.valueOf(cell.getNumericCellValue()));
                            }
                            //Cell with index 2 contains marks in Science
                            else if (cell.getColumnIndex() == 2) {
                                student.setScience(String.valueOf(cell.getNumericCellValue()));
                            }
                            //Cell with index 3 contains marks in English
                            else if (cell.getColumnIndex() == 3) {
                                student.setEnglish(String.valueOf(cell.getNumericCellValue()));
                            }
                        }
                    }
                    //end iterating a row, add all the elements of a row in list
                    studentList.add(student);
                }
            }

            fis.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return studentList;
    }


}

Output:

[Anirudh: Maths 100.0 Science 98.0 English 89.0, Rahul: Maths 80.0 Science 80.0 English 80.0, Priya: Maths 90.0 Science 90.0 English 90.0]

In the above example we have read the excel file contacting students marks data and stored it in a StudentList. Now lets see how to write excel files.

2. Write Excel File In Java

Now, lets see how to write an excel file in Java, we will use the same Student Class for this.

WriteExcelFileExample.java

package com.jcg.example;

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;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by anirudh on 23/10/14.
 */
public class WriteExcelFileExample {
    private static final String FILE_PATH = "/Users/anirudh/Projects/JCGExamples/JavaWriteReadExcelFileExample/testWriteStudents.xlsx";
    //We are making use of a single instance to prevent multiple write access to same file.
    private static final WriteExcelFileExample INSTANCE = new WriteExcelFileExample();

    public static WriteExcelFileExample getInstance() {
        return INSTANCE;
    }

    private WriteExcelFileExample() {
    }

    public static void main(String args[]){

        List studentList = new ArrayList();
        studentList.add(new Student("Magneto","90","100","80"));
        studentList.add(new Student("Wolverine","60","60","90"));
        studentList.add(new Student("ProfX","100","100","100"));

        writeStudentsListToExcel(studentList);

    }

    public static void writeStudentsListToExcel(List studentList){

        // Using XSSF for xlsx format, for xls use HSSF
        Workbook workbook = new XSSFWorkbook();

        Sheet studentsSheet = workbook.createSheet("Students");

        int rowIndex = 0;
        for(Student student : studentList){
            Row row = studentsSheet.createRow(rowIndex++);
            int cellIndex = 0;
            //first place in row is name
            row.createCell(cellIndex++).setCellValue(student.getName());

            //second place in row is marks in maths
            row.createCell(cellIndex++).setCellValue(student.getMaths());

            //third place in row is marks in Science
            row.createCell(cellIndex++).setCellValue(student.getScience());

            //fourth place in row is marks in English
            row.createCell(cellIndex++).setCellValue(student.getEnglish());

        }

        //write this workbook in excel file.
        try {
            FileOutputStream fos = new FileOutputStream(FILE_PATH);
            workbook.write(fos);
            fos.close();

            System.out.println(FILE_PATH + " is successfully written");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }


    }
}

Output :

/Users/anirudh/Projects/JCGExamples/JavaWriteReadExcelFileExample/testWriteStudents.xlsx is successfully written

Lets see the Excel File Generated out of this :

Figure 2: Excel File Generated with Student name and marks
Figure 2: Excel File Generated with Student name and marks

So, We see in the above picture how the excel file was written with the Student data.

In this example we saw how we can read and write excel files in Java.

Download
You can download the full source code of this example here : JavaWriteReadExcelFileExample.zip

Anirudh Bhatnagar

Anirudh is a Java programmer with extensive experience in building Java/J2EE applications. He has always been fascinated by the new technologies and emerging trends in software development. He has been involved in propagating these changes and new technologies in his projects. He is an avid blogger and agile enthusiast who believes in writing clean and well tested code.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Vanitha Rathinam
Vanitha Rathinam
6 years ago

Hi, How to store the value user input data for excel file

SONAM AGRAWAL
SONAM AGRAWAL
5 years ago

Can this library work on xml excel files?

Swaran
Swaran
4 years ago

Hi,
Could you explain how to iterate the data in column wise manner ?

prashant
prashant
3 years ago

i need a java code to convert excel file into yaml

Back to top button