Apache POI – Reading and Writing Excel file in Java

In this article, we will discuss about how to read and write an excel file using Apache POI

1. Basic definitions for Apache POI library

This section briefly describe about basic classes used during Excel Read and Write.

  1. HSSF is prefixed before the class name to indicate operations related to a Microsoft Excel 2003 file.
  2. XSSF is prefixed before the class name to indicate operations related to a Microsoft Excel 2007 file or later.
  3. XSSFWorkbook and HSSFWorkbook are classes which act as an Excel Workbook
  4. HSSFSheet and XSSFSheet are classes which act as an Excel Worksheet
  5. Row defines an Excel row
  6. Cell defines an Excel cell addressed in reference to a row.

2. Download Apache POI

Apache POI library is easily available using Maven Dependencies.

pom.xml

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

3. Apache POI library – Writing a Simple Excel

The below code shows how to write a simple Excel file using Apache POI libraries. The code uses a 2 dimensional data array to hold the data. The data is written to a XSSFWorkbook object. XSSFSheet is the work sheet being worked on. The code is as shown below:

ApachePOIExcelWrite.java

package com.mkyong;

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;

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

public class ApachePOIExcelWrite {

    private static final String FILE_NAME = "/tmp/MyFirstExcel.xlsx";

    public static void main(String[] args) {

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Datatypes in Java");
        Object[][] datatypes = {
                {"Datatype", "Type", "Size(in bytes)"},
                {"int", "Primitive", 2},
                {"float", "Primitive", 4},
                {"double", "Primitive", 8},
                {"char", "Primitive", 1},
                {"String", "Non-Primitive", "No fixed size"}
        };

        int rowNum = 0;
        System.out.println("Creating excel");

        for (Object[] datatype : datatypes) {
            Row row = sheet.createRow(rowNum++);
            int colNum = 0;
            for (Object field : datatype) {
                Cell cell = row.createCell(colNum++);
                if (field instanceof String) {
                    cell.setCellValue((String) field);
                } else if (field instanceof Integer) {
                    cell.setCellValue((Integer) field);
                }
            }
        }

        try {
            FileOutputStream outputStream = new FileOutputStream(FILE_NAME);
            workbook.write(outputStream);
            workbook.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        System.out.println("Done");
    }
}

On executing the above code, you get below excel as an output.

apache-poi-read-write-excel

4. Apache POI library – Reading an Excel file

The below code explains how to read an Excel file using Apache POI libraries. The function getCellTypeEnum is deprecated in version 3.15 and will be renamed to getCellType from version 4.0 onwards.

ApachePOIExcelRead.java

package com.mkyong;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;

public class ApachePOIExcelRead {

    private static final String FILE_NAME = "/tmp/MyFirstExcel.xlsx";

    public static void main(String[] args) {

        try {

            FileInputStream excelFile = new FileInputStream(new File(FILE_NAME));
            Workbook workbook = new XSSFWorkbook(excelFile);
            Sheet datatypeSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = datatypeSheet.iterator();

            while (iterator.hasNext()) {

                Row currentRow = iterator.next();
                Iterator<Cell> cellIterator = currentRow.iterator();

                while (cellIterator.hasNext()) {

                    Cell currentCell = cellIterator.next();
                    //getCellTypeEnum shown as deprecated for version 3.15
                    //getCellTypeEnum ill be renamed to getCellType starting from version 4.0
                    if (currentCell.getCellTypeEnum() == CellType.STRING) {
                        System.out.print(currentCell.getStringCellValue() + "--");
                    } else if (currentCell.getCellTypeEnum() == CellType.NUMERIC) {
                        System.out.print(currentCell.getNumericCellValue() + "--");
                    }

                }
                System.out.println();

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

    }
}

On executing the above code, you get the below output.


Datatype--Type--Size(in bytes)--
int--Primitive--2.0--
float--Primitive--4.0--
double--Primitive--8.0--
char--Primitive--1.0--
String--Non-Primitive--No fixed size--

References

  1. Details about deprecation of getCellTypeEnum
  2. Apache POI reference regarding Deprecation
  3. Apache POI Maven Repo
  4. Apache POI API docs

About the Author

author image
Datsabk
A technically sound person, oriented towards learning things logically rather than technically. It is my logical approach that has helped me learn and take up any programming language and start with coding. With a responsibility of Java based Web development professionally, I highly believe in imparting knowledge online for any technical subject that I can handle.

Comments

Leave a Reply

avatar
newest oldest most voted
Pv
Guest
Pv

What are the jars required?

Shubham Rathore
Guest
Shubham Rathore

This dependency is required for Apache POI.

org.apache.poi
poi
3.7

mehmet
Guest
mehmet

Could yo tell me how to import apache poi? I do not know exactly

Mike
Guest
Mike

Just FYI:

The type org.apache.poi.POIXMLDocument cannot be resolved. It is indirectly referenced from required .class files

flammable
Guest
flammable

In the version of 3.9 of poi dependency you have to use “Cell.CELL_TYPE_STRING” or “Cell.CELL_TYPE_x” instead of “CellType.x” in which x may equal to BLANK, BOOLEAN, ERROR, FORMULA, NUMERIC OR STRING

For the ones who didn’t understand here is the example;
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
// your code
}

Tahir Hussain Mir
Guest
Tahir Hussain Mir

Thanks Buddy

kentg03
Guest
kentg03

sorry, i found the answer

for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet datatypeSheet = workbook.getSheetAt(i);
}

kentg03
Guest
kentg03

how about the excel has multiple sheet?

rajesh
Guest
rajesh

I m getting all the data line by line in output. I need it to be printed in row and column wise as it is in excel

Deepak Salve
Guest
Deepak Salve

how to read date into excel file

Sindhura Panguluri
Guest
Sindhura Panguluri

How can we handle non english strings like hindi text or thai text while reading excel sheets?

Brian Siim Andersen
Guest
Brian Siim Andersen

When writing large Excel files there’s also SXSSFWorkbook. It’s a streaming version of XSSFWorkbook with a lower memory footprint.

mkyong
Guest
mkyong

Thanks for your SXSSFWorkbook suggestion.

Jigar
Guest
Jigar

SXSSFWorkbook stores in temporary file. Is there a solution to avoid this ? on server security is issue if we have temp files.

Suresh
Guest
Suresh

You need to update your code, I have taken and it is not working, throwing errors. It wasted my time.

Rama
Guest
Rama
Hi Sir/Madam xlsx(Excel) format file Read content issues that attaching file from Email and store into local drive, Please any help on this and that really appreciated. ———————————————————————————————————————————————————– Below java code now we are using ———————————- String contentType = message.getContentType(); String attachFiles = “”; // String saveDirectory = (String) resources.get(SystemRqstAppConstants.WebConstants.CUSTOMERITEMVENDORPO_PATH); String saveDirectory =”D:/ResumeFiles/”; List errorsList= null; String messageContent = “”; logger.info(“:::::Timecards Auto Update before Attchments:::::”); if (contentType.contains(“multipart”)) { // content may contain attachments String client=””; if(subject.contains(“PFIZER”) || subject.contains(“Pfizer”) || subject.contains(“pfizer”)) client=”Pfizer”; else if(subject.contains(“CSC”) || subject.contains(“Csc”) || subject.contains(“csc”)) client=”CSC”; logger.info(“Timecards Auto Update client name: “+client); Multipart multiPart = (Multipart) message.getContent(); int… Read more »
subbareddy
Guest
subbareddy

if .dot file convert html is it possible?