Read, Write and Update XLSX Using POI in Java

By Arvind Rai, 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");
	}
} 
Output
After run of the above program, the excel sheet will be generated as below.
Read, Write and Update XLSX Using POI in Java

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();
	}
} 
Output
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");
	}
} 
Output
Find the output. Check that the values of second column have been changed for second and third row.
Read, Write and Update XLSX Using POI in Java

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> 
POSTED BY
ARVIND RAI
ARVIND RAI







©2024 concretepage.com | Privacy Policy | Contact Us