Snippet: Modifying Excel Files, Adding Formulas with Apache POI

September 29th, 2014 by

Recently I needed to modify some excel files and to add some aggregated formula fields to a sheet and the following snippet did the work for me.

Modified Excel File with Formulas

Modified Excel File with Formulas

 

Dependencies

Just two dependencies needed  here: One for Apache POI and one for the Office Open XML Documents API.

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

Sample Excel File

Our sample excel file is quite empty just some empty customer information and a chart that is bound to the data grid.

We won’t create the charts with Apache POI because its charts API is still experimental.

Excel Sample File

Excel Sample File

The Program

The following program fills the customer data from the three double arrays into the corresponding cells.

For each customer we’re memorizing the first and last data cell and use this information to fill the last cell with a formula to sum all fields.

package com.hascode.tutorial;
 
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
public class ManipulateExcelFile {
 
	static double[] customer1 = { 12, 44.2, 32 };
	static double[] customer2 = { 24, 3.5, 11 };
	static double[] customer3 = { 17, 33.25, 42 };
 
	public static void main(final String[] args) throws IOException {
		FileInputStream file = new FileInputStream(new File(ManipulateExcelFile.class.getClassLoader().getResource("sample.xlsx").getFile()));
 
		XSSFWorkbook workbook = new XSSFWorkbook(file);
		XSSFSheet sheet = workbook.getSheetAt(0);
		System.out.println("adding customer statistics to sheet '" + sheet.getSheetName() + "'");
 
		int startRow = 2;
		int startCol = 1;
 
		fillCustomerData(customer1, startRow, startCol, sheet);
		fillCustomerData(customer2, startRow, ++startCol, sheet);
		fillCustomerData(customer3, startRow, ++startCol, sheet);
 
		file.close();
 
		FileOutputStream outFile = new FileOutputStream(new File("/tmp/updated.xlsx"));
		workbook.write(outFile);
		outFile.close();
 
	}
 
	private static void fillCustomerData(final double[] data, int row, final int col, final XSSFSheet sheet) {
		String colName = CellReference.convertNumToColString(col);
		String startCell = colName + (row + 1);
		String stopCell = colName + (row + data.length);
		String sumFormula = String.format("SUM(%s:%s)", startCell, stopCell);
		for (int i = 0; i < data.length; i++) {
			Cell cell = sheet.getRow(row).getCell(col);
			cell.setCellValue(data[i]);
			System.out.println("row: " + row + ", col:" + col + ", cell: " + colName + row + ", data: " + data[i]);
			row++;
		}
		System.out.println("adding sum-formula: " + sumFormula);
		Cell sumCell = sheet.getRow(row).getCell(col);
		sumCell.setCellFormula(sumFormula);
	}
}

Generated File

Running the program produces the following output:

adding customer statistics to sheet 'Customer Statistics'
row: 2, col:1, cell: B2, data: 12.0
row: 3, col:1, cell: B3, data: 44.2
row: 4, col:1, cell: B4, data: 32.0
adding sum-formula: SUM(B3:B5)
row: 2, col:2, cell: C2, data: 24.0
row: 3, col:2, cell: C3, data: 3.5
row: 4, col:2, cell: C4, data: 11.0
adding sum-formula: SUM(C3:C5)
row: 2, col:3, cell: D2, data: 17.0
row: 3, col:3, cell: D3, data: 33.25
row: 4, col:3, cell: D4, data: 42.0
adding sum-formula: SUM(D3:D5)

The generated file should look similar to this one:

Modified Excel File with Formulas

Modified Excel File with Formulas

Tutorial Sources

Please feel free to download the tutorial sources from my Bitbucket repository, fork it there or clone it using Git:

git clone https://bitbucket.org/hascode/apache-poi-excel-example.git

Resources

Search
Categories