Read, Write and Update XLSX Using POI in Java
April 16, 2014
To read, write and update XLSX, we can use Apache POI API. Apache POI is efficient to handle excel file. There are methods and classes in POI that makes excel processing very easy. Find some classes description which belongs to org.apache.poi.xssf.usermodel package. XSSFWorkbook: This is the root class to handle XLSX. It reads excel file from a file input stream.
XSSFSheet: An excel file can have more than one sheet. This class owns an excel sheet which is obtained by XSSFWorkbook.
XSSFRow: The object of this class owns a row of excel sheet which is obtained by XSSFSheet .
XSSFCell: The object of XSSFCell represents a cell to the corresponding row. This is instantiated by XSSFRow.
In this page, we will see the example for all the three process read, write and update in excel file.
How to Write XLSX File
To write an XLSX, start reading XLSX file using FileInputStream. Pass this stream to XSSFWorkbook and get XSSFSheet. Now to create row, call XSSFSheet.createRow() method. For corresponding cells, we need to call XSSFRow.createCell() and set the values. Now close the FileInputStream and fetch the excel file using FileOutputStream. Finally write the data using XSSFWorkbook.write().WriteXLSX.java
package com.concretepage.poi;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class WriteXLSX {
public static void main(String[] args) throws IOException {
FileInputStream fis = new FileInputStream(new File("D:\\xlsx\\test.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook (fis);
XSSFSheet sheet = workbook.getSheetAt(0);
//Create First Row
XSSFRow row1 = sheet.createRow(0);
XSSFCell r1c1 = row1.createCell(0);
r1c1.setCellValue("Emd Id");
XSSFCell r1c2 = row1.createCell(1);
r1c2.setCellValue("NAME");
XSSFCell r1c3 = row1.createCell(2);
r1c3.setCellValue("AGE");
//Create Second Row
XSSFRow row2 = sheet.createRow(1);
XSSFCell r2c1 = row2.createCell(0);
r2c1.setCellValue("1");
XSSFCell r2c2 = row2.createCell(1);
r2c2.setCellValue("Ram");
XSSFCell r2c3 = row2.createCell(2);
r2c3.setCellValue("20");
//Create Third Row
XSSFRow row3 = sheet.createRow(2);
XSSFCell r3c1 = row3.createCell(0);
r3c1.setCellValue("2");
XSSFCell r3c2 = row3.createCell(1);
r3c2.setCellValue("Shyam");
XSSFCell r3c3 = row3.createCell(2);
r3c3.setCellValue("25");
fis.close();
FileOutputStream fos =new FileOutputStream(new File("D:\\xlsx\\test.xlsx"));
workbook.write(fos);
fos.close();
System.out.println("Done");
}
}
After run of the above program, the excel sheet will be generated as below.
How to Read XLSX file
Now we will read the excel which has been created by above example. Fetch the given excel using FileInputStream. Get the workbook using XSSFWorkbook and fetch the required sheet as XSSFSheet object. Iterator the row with XSSFSheet.rowIterator() and then iterate the corresponding cells with Row.cellIterator().ReadXLSX.java
package com.concretepage.poi;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadXLSX {
public static void main(String[] args) throws IOException {
FileInputStream fis = new FileInputStream(new File("D:\\xlsx\\test.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook (fis);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator ite = sheet.rowIterator();
while(ite.hasNext()){
Row row = ite.next();
Iterator<Cell> cite = row.cellIterator();
while(cite.hasNext()){
Cell c = cite.next();
System.out.print(c.toString() +" ");
}
System.out.println();
}
fis.close();
}
}
Emd Id NAME AGE 1 Ram 20 2 Shyam 25
How to Update XLSX file
We will update an excel file which has been created by above example. We will change the second column values. Reach to the cell index for a given row and then set the values. Like creating excel sheet we need to use FileOutputStream to update our excel.UpdateXLSX.java
package com.concretepage.poi;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class UpdateXLSX {
public static void main(String[] args) throws IOException {
FileInputStream fis = new FileInputStream(new File("D:\\xlsx\\test.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook (fis);
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row1 = sheet.getRow(1);
XSSFCell cell1 = row1.getCell(1);
cell1.setCellValue("Mahesh");
XSSFRow row2 = sheet.getRow(2);
XSSFCell cell2 = row2.getCell(1);
cell2.setCellValue("Ramesh");
fis.close();
FileOutputStream fos =new FileOutputStream(new File("D:\\xlsx\\test.xlsx"));
workbook.write(fos);
fos.close();
System.out.println("Done");
}
}
Find the output. Check that the values of second column have been changed for second and third row.
Dependencies of POI API
Find the dependencies which are needed to run all above program.<dependencies> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.9</version> </dependency> <dependency> <groupId>org.apache.xmlbeans</groupId> <artifactId>xmlbeans</artifactId> <version>2.6.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.10-FINAL</version> </dependency> <dependency> <groupId>dom4j</groupId> <artifactId>dom4j</artifactId> <version>1.6.1</version> </dependency> </dependencies>
