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

avatar
20 Comment threads
8 Thread replies
5 Followers
 
Most reacted comment
Hottest comment thread
26 Comment authors
S. JrTarunLuís Ângelo Rodrigues Jr.Praveenaby Recent comment authors
newest oldest most voted
Mike
Guest
Mike

Just FYI:

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

aby
Guest
aby

i am also getting this problem

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

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.

Praveen
Guest
Praveen

I downloaded the report form SQL into a CSV file that contains string fields a BLOB message and that has XML. I tried to write this to Excel but ht BLOB message got splitted into many lines, i could not set it into a single cell. is there possibility for this in java?

aby
Guest
aby

i am getting below error

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

Please helped on my issues. Thanks in advance

S. Jr
Guest
S. Jr

Wow!! Sir my Great and Sincere regard to you, indeed for impacting knowledge online…..!
you have just save my life in adding excel to my java application! after tireless research, indeed nothing impossible
as you have done for me.

Gratefully Yours,

Tarun
Guest
Tarun

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

+++++++++++++++++++ RESOLVED ++++++++++++++++
Add this library –

org.apache.poi
poi
3.17

Thanks

caveBatman
Guest
caveBatman

what is the way to make the header column {“Datatype”, “Type”, “Size(in bytes)”} alone BOLD while writing to it

Ozgur
Guest
Ozgur

Hi i am looking for an example code about writing to excel from jtable. Can you help me.

chose
Guest
chose

package LeProjetExcel; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; This code contains 2 class one that reads and puts my data into an arrayList secon that transforms my arraylist in jTable main in secon class The place where you indicate the root is in the main only works with .xls extension because HSSF package LeProjetExcel; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.util.ArrayList; import java.util.Iterator; 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.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.CellType; public class ExcelReading2 { ExcelReading2(){ } public ArrayList ConvertionFichierExelEnArrayList(String racineDeFichierExcel) { ArrayList TableauDeRetour = new ArrayList(); ArrayList values = new ArrayList(); Connection… Read more »

Antony Paul Raj
Guest
Antony Paul Raj

Writing to excel works fine. thank you so much.

Manu Juma
Guest
Manu Juma

Thanks you so much; the logical flow has worked.

subbareddy
Guest
subbareddy

if .dot file convert html is it possible?

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

Luís Ângelo Rodrigues Jr.
Guest
Luís Ângelo Rodrigues Jr.

You have a pom.xml in your project?
Add the code below:

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>

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?

Sindhura Panguluri
Guest
Sindhura Panguluri

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

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

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 »

Suresh
Guest
Suresh

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