How to read and write XLSX File in Java

Posted By : Amit Patel | 29-Jan-2019

Java

Quick Summary:-

In this blog, we will learn about reading and writing the current popular Excel file format i.e. the file with.XLSX extension. XLSX this means XML spreadsheet format. If we read Excel file, we need to first download Apache POI Jar files, without these jars, our code will neither compile nor execute. If we hate to maintain JARs by itself, use Maven. In Eclipse IDE, we can download M2Eclipse plug-in to setup Maven project. Once we did that, add the following dependencies in our pom.xml (project object model) file.

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

Reading .xlsx format the classes for OOXML format (such as XSSF for reading .xlsx format) are in a different Jar file. We need to include the poi-ooxml jar in our project, along with the dependencies for it. When we add poi-ooxml JAR as a dependency via Maven, it will also add other required dependencies by itself. For example, adding below XML snippet in our pom.xml will download four JAR files-

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

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

If we are not using Maven then add 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

Read Excel File (XLSX)-
Excel file cell represents a block in Excel, known as a cell. And cell can be any type e.g. String, numeric or boolean. Before reading cell value we must ascertain the correct type of cell. After that just call corresponding value method e.g. getStringValue() or getNumericValue() to read data from cell.

Write XLSX File-
Writing data into Excel file is also similar to reading, we will do is to create new rows, columns, and cells. Once we are done creating new rows in our Excel file in memory, we need to open an output stream to write that data into our Excel File. This will save all update we made in an existing file or in a new file which is created by Java's File class.

Here is Java program to read/write from existing Excel file in Java-

import java.io.File; 
import java.io.FileInputStream; 
import java.io.FileNotFoundException; 
import java.io.FileOutputStream; 
import java.io.IOException; 
import java.sql.Date; 
import java.util.HashMap; 
import java.util.Iterator; 
import java.util.Map; 
import java.util.Set; 
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 ExampleXLSXReaderWriter { 
public static void main(String[] args) { 
try { 
File excel = new File("C://temp/Employee.xlsx"); 
FileInputStream fis = new FileInputStream(excel); 
XSSFWorkbook book = new XSSFWorkbook(fis); 
XSSFSheet sheet = book.getSheetAt(0); 
Iterator<Row> itr = sheet.iterator();

// Iterate Excel file 
while (itr.hasNext()) { 
Row row = itr.next(); 
// Iterate each column of Excel file 
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; 
case Cell.CELL_TYPE_BOOLEAN: 
System.out.print(cell.getBooleanCellValue() + "\t"); 
break; 
default:
} 
} 
System.out.println(""); 
} 
// writing data into .XLSX file 

Map<String, Object[]> newXLSXData = new HashMap<String, Object[]>(); 
newXLSXData.put("5", new Object[] { 5d, "Aman", "100K", "Monday", "Delhi" }); 
newXLSXData.put("6", new Object[] { 6d, "Ankit", "100K", "Sunday", "Ghaziabad" }); 
newXLSXData.put("7", new Object[] { 7d, "Mohan", "100K", "Wednesday", "Noida" });

Set<String> newXLSXRows = newXLSXData.keySet(); 
int rownum = sheet.getLastRowNum();

for (String key : newXLSXRows) { 
Row row = sheet.createRow(rownum++); 
Object[] objArr = newData.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 Double) { 
cell.setCellValue((Double) obj); 
}
} 
}

// Here use FileOutputStream to save written data into .xlsx file 
FileOutputStream os = new FileOutputStream(excel); 
book.write(os); 
os.close(); 
book.close(); 
fis.close();
} catch (FileNotFoundException fne) { 
fne.printStackTrace(); 
} catch (IOException ie) { 
ie.printStackTrace(); 
} 
} 
}