How to read and write XLSX file in java

Published On: 1 April 2022.By bharat chhipa.

General

How to read and write XLSX file in java

In this article , we will learn how to read and write excel file (.xlsx) from java code. Since, JDK doesn’t provide direct API to read and write XLSX file, we would have to rely on third party library to do the job. Fortunately, there are bunch of library availabe for us to play with, the best is Apache POI.

Why Apache POI ?

Apache POI is a popular API that allows programmers to create, modify, and display MS Office files using Java programs. It is an open source library developed and distributed by Apache Software Foundation to design or modify Microsoft Office files using Java program.

Not only excel file, POI can do much:

XSSF (XML SpreadSheet Format) – Used to reading and writting Open Office XML (XLSX) format files.
HSSF (Horrible SpreadSheet Format) – Use to read and write Microsoft Excel (XLS) format files.
HWPF (Horrible Word Processor Format) – to read and write Microsoft Word 97 (DOC) format files.
HSMF (Horrible Stupid Mail Format) – pure Java implementation for Microsoft Outlook MSG files
HDGF (Horrible DiaGram Format) – One of the first pure Java implementation for Microsoft Visio binary files.
HPSF (Horrible Property Set Format) – For reading “Document Summary” information from Microsoft Office files.
HSLF (Horrible Slide Layout Format) – a pure Java implementation for Microsoft PowerPoint files.
HPBF (Horrible PuBlisher Format) – Apache’s pure Java implementation for Microsoft Publisher files.
DDF (Dreadful Drawing Format) – Apache POI package for decoding the Microsoft Office Drawing format.

Getting Started with Apache POI

The following steps will guide you through reading and writing on excel(.XLSX) files.

1. Create a Spring boot Application

You can either create it from https://start.spring.io/ or use an IDE to create application, we will be adding dependency on the go.

2. Add necessary dependecies

Open pom.xml, then add the following dependencies :

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.11-beta2</version>
</dependency>

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

<groupId>org.apache.poi</groupId>

</dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

</dependency>

if you are not using Maven, then add the following JAR files in your Java program’s classpath

poi-3.11-beta2.jar
commons-codec-1.9.jar
poi-ooxml-3.11-beta2.jar
poi-ooxml-schemas-3.11-beta2.jar
xmlbeans-2.6.0.jar
stax-api-1.0.1.jar

3. How to read Excel file (XLSX) in java

What we will be reading :

Method to read XLSX file in java :

public static void readXLSX() {
                File file  = new File("/home/auriga/Downloads/Read.xlsx");
                try {
                        FileInputStream fis = new FileInputStream(file);
                        // finds the workbook instance for xlsx file
                        XSSFWorkbook myWorkBook =  new XSSFWorkbook(fis);
                        // returns the first sheet 
                        XSSFSheet mysheet = myWorkBook.getSheetAt(0);
                        Iterator<Row> rowIterator = mysheet.iterator();
                  while(rowIterator.hasNext()) {
                           Row row  = rowIterator.next();
                           // for each row iterate through each column
                           Iterator<Cell> cellIterator = row.cellIterator();
                      while (cellIterator.hasNext()) {
                           Cell cell = cellIterator.next();
                           switch (cell.getCellType()) {
                              case Cell.CELL_TYPE_STRING:
                                System.out.print(cell.getStringCellValue() + "\t");
                                break;
                              case Cell.CELL_TYPE_NUMERIC:
                                System.out.print(cell.getNumericCellValue() + "\t");
                                break;
                            }
                      } 
                      System.out.println("");
                  }	
            }catch(Exception e) {
                 e.printStackTrace();
         }
}

public static void readXLSX() {

File file = new File("/home/auriga/Downloads/Read.xlsx");

try {

FileInputStream fis = new FileInputStream(file);

// finds the workbook instance for xlsx file

XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

// returns the first sheet

XSSFSheet mysheet = myWorkBook.getSheetAt(0);

Iterator<Row> rowIterator = mysheet.iterator();

while(rowIterator.hasNext()) {

Row row = rowIterator.next();

// for each row iterate through each column

Iterator<Cell> cellIterator = row.cellIterator();

while (cellIterator.hasNext()) {

Cell cell = cellIterator.next();

switch (cell.getCellType()) {

case Cell.CELL_TYPE_STRING:

System.out.print(cell.getStringCellValue() + "\t");

break;

case Cell.CELL_TYPE_NUMERIC:

System.out.print(cell.getNumericCellValue() + "\t");

break;

}

System.out.println("");

}

}catch(Exception e) {

e.printStackTrace();

}

The first two lines are very common to understand, they are to read file from the system, the main code starts from fourth line where we pass a binary Input Stream to create an instance of XSSFWorkBook class, which represents an Excel workbook.

The next line gives us a worksheet from where we start iterating each row and then each column. Cell represents each block in a sheet. This is where we read or write data.

A cell can be any type e.g String, numeric, boolean, etc, so we need to check the type of cell before reading, for that we have a switch case which checks the type before reading the specific cell value by calling getStringValue(), getNumericValue(), etc.

This is how exactly you can read XLSX file in java.

4. How to write to XLSX file

Writing is also similar to reading, I have created another method to write to XLSX file. The workbook and worksheet classes will remain same.

Method to write XLSX file :

public static void writeXLSX() {		
                File file  = new File("/home/auriga/Downloads/Read.xlsx");
                try {
                        FileInputStream fis = new FileInputStream(file);			
                        // finds the workbook instance for xlsx file
                        XSSFWorkbook myWorkBook =  new XSSFWorkbook(fis);			
                        // returns the first sheet 
                        XSSFSheet mysheet = myWorkBook.getSheetAt(0);						
                        Map<String, Object[]> data = new HashMap<String,Object[]>();
                        data.put("1", new Object[]{"User 4",13});
                        data.put("2", new Object[]{"User 5",14});
                        data.put("3", new Object[]{"USer 6",15});
                        Set<String> rows = data.keySet();
                        int rownum = mysheet.getLastRowNum();
                        for(String key : rows) {
                                  // Creating a new Row in existing XLSX sheet
                                  Row row = mysheet.createRow(++rownum);
                                  Object [] objArr = data.get(key);
                                  int cellnum = 0;
                                  for (Object obj : objArr) {
                                            Cell cell = row.createCell(cellnum++);
                                            if (obj instanceof String) {
                                                   cell.setCellValue((String) obj);
                                            } else if (obj instanceof Boolean) {
                                                   cell.setCellValue((Boolean) obj);
                                            } else if (obj instanceof Date) {
                                                   cell.setCellValue((Date) obj);
                                            } else if (obj instanceof Integer) {
                                                   cell.setCellValue((Integer) obj);
                                            }
	                                 }
                        }
                        // open an OutputStream to save written data into XLSX file
                        FileOutputStream os = new FileOutputStream(file);
                        myWorkBook.write(os);
                        System.out.println("Writing on XLSX file Finished ...");	
                }catch(Exception e) {
	             e.printStackTrace();
                }
}

public static void writeXLSX() {

File file = new File("/home/auriga/Downloads/Read.xlsx");

try {

FileInputStream fis = new FileInputStream(file);

// finds the workbook instance for xlsx file

XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

// returns the first sheet

XSSFSheet mysheet = myWorkBook.getSheetAt(0);

Map<String, Object[]> data = new HashMap<String,Object[]>();

data.put("1", new Object[]{"User 4",13});

data.put("2", new Object[]{"User 5",14});

data.put("3", new Object[]{"USer 6",15});

Set<String> rows = data.keySet();

int rownum = mysheet.getLastRowNum();

for(String key : rows) {

// Creating a new Row in existing XLSX sheet

Row row = mysheet.createRow(++rownum);

Object [] objArr = data.get(key);

int cellnum = 0;

for (Object obj : objArr) {

Cell cell = row.createCell(cellnum++);

if (obj instanceof String) {

cell.setCellValue((String) obj);

} else if (obj instanceof Boolean) {

cell.setCellValue((Boolean) obj);

} else if (obj instanceof Date) {

cell.setCellValue((Date) obj);

} else if (obj instanceof Integer) {

cell.setCellValue((Integer) obj);

}

// open an OutputStream to save written data into XLSX file

FileOutputStream os = new FileOutputStream(file);

myWorkBook.write(os);

System.out.println("Writing on XLSX file Finished ...");

}catch(Exception e) {

e.printStackTrace();

}

We have created a map, to store data, which we need to write on excel file. Then iterated the map, and started inserting the value with the help of setCellValue method , before that we need to know the last edited row number and type of the cell value.

Finally, we need to open an Output Stream to save written data into XLSX file.

Output: