How to read and write Excel files in Java

There are several use cases where you want to manipulate office documents from your Java application, such as generating a report in Word or Excel format with data obtained from a database, extracting text/images from a Power point presentation and so on. Apache POI provides APIs for manipulating files in Microsoft office formats like Word, Excel, PowerPoint, Visio etc., Apache POI contains several subcomponents such as HSSF and XSSF for Excel .xls and .xlsx files respectively and HWPF and XWPF for.doc and .docx type Word files respectively. Here is a Java program that demonstrates how to read and write Excel files using the Apache POI HSSF library.

Before you start, get Apache POI and configure it for your Java environment.

How to add Apache POI in Eclipse Java Project

Download binary version of Apache POI from the POI download page

Extract downloaded file to a suitable location.

To add Apache POI to your Java Project in Eclipse:

• Right click on the project and select Build Path → Configure Build Path
• Click Add External Jars and select the poi-x.xx.yyyymmdd.jar from the extracted files.

What this program does

This program reads an Excel file (let's call it template file) that contains some data, formulas and formatting. It then updates the values of certain cells and saves it as a new excel file with current date and time added to its filename.

The template file used in this example (price-list.xls) is shown below. This file has formulas from column E3 to E7 to calculate the total price, which is quantity multiplied by price. Also, column E8 has formula to calculate the sum of columns E3 to E7.

Every time we run our Java program, it will increase the price for each fruit by 5 percent. The totals are then recalculated and a new file is created with the updated values while the data on other cells and the cell formatting are kept intact.

The excel file generated after program execution is shown below

Program Source

/** 
 * Java Program to Read and Write Excel file 
 * using Apache POI HSSF component
 * @author OpenTechGuides.com
 * @version 1.0
 * */

package CreateExcel; 

import java.text.*;
import java.util.*;
import java.io.*;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;

public class CreateExcel {
	
public static void main(String[] args) {

	// Define the template file
	String xlsTemplate = "price-list.xls";

	try {
		// Read spreadsheet template file to input stream
		FileInputStream fis = new FileInputStream(new File(xlsTemplate));

		// Create workbook object using file input stream
		HSSFWorkbook wbTemplate = new HSSFWorkbook(fis);

		// Get the first sheet from the workbook object
		HSSFSheet sheet1 = wbTemplate.getSheetAt(0);

		// Set column index for Price column
		int PriceColIdx = 3;

		// Repeat for Cells D3 to D7
		for(int RowIdx=2; RowIdx < 7; RowIdx++) {
			HSSFRow row = sheet1.getRow(RowIdx);
			HSSFCell cellPrice= row.getCell(PriceColIdx);

			//Increment value in cell by 5 percent
			double value = cellPrice.getNumericCellValue() +
				(cellPrice.getNumericCellValue() * 0.05);

			// Set new cell value	
			cellPrice.setCellValue(value);
		}
		// recalculate all formulas in the workbook
		wbTemplate.setForceFormulaRecalculation(true);

		// Define data format
		DateFormat dtFormat = new SimpleDateFormat("yyyymmdd-HHmmss");

		//Get Current timestamp in the format specified
		Date dt = new Date();
		String ts = dtFormat.format(dt);

		//Get filename without extension
		String fn = xlsTemplate.substring(0, xlsTemplate.length() - 4);

		//Create output filename - For ex: sharereport-20160107-2132216.xls
		String OutFile = fn + "-" + ts + ".xls";

		//write changes to file
		FileOutputStream fos = new FileOutputStream(new File(OutFile));
		wbTemplate.write(fos);

		//Close input and output file streams
		fos.close();
		fis.close();

	}
	//Catch file not found exception
	catch (FileNotFoundException e) {
	    System.err.println("File Not Found: " + System.getProperty("user.dir") 
	    		+ "\" + e.getMessage());
	}
	//Catch all other IO exceptions
	catch (IOException e) {
	    System.err.println("Caught IOException" + e.getMessage());
	}
	}

		
}

Apache POI provides different packages to access the HSSF API. The above example use the User API or usermodel package which is the simplest method of accessing HSSF API. For Advanced users there are also ways of access HSSF using Event API and Low level APIs.

This is a simple Java program to help you get started with reading and writing office documents from Java applications.

• Rate it
• 
• 
• 
• 

Related Articles