How to create an excel file with Apache POI

This tutorial shows how to create an excel file. There are other options to create excel files using a Java program but in this case we are going to speak about Apache POI which is probably one of the most important frameworks to manage office files. For example you can read and write powerpoint, word or excel files.

In order to compile the following code and in general any code with POI classes you will need to download the jars from the Apache POI website. Once you have already downloaded the classes don’t forget to add them to your classpath.

The code is quite straight forward however just notice the order we create the different elements.

First of all we have to create the book (HSSFWorkbook). This is the main element in our excel file and allow us to define the diferent styles or create new sheets among other things. This object is the base to create the different sheets. After we will create the sheet object from our book (HSSFSheet). The only thing we have to do is to set its name. After we have to create the row (HSSFRow) and establish which line. Bear in mind that either rows or columns start in 0. Now we create the cell (HSSFCell) and we set its value.

This step is optional and it is to set a style to our cell. To create a new style we have to use our book object. We can establish the background color or the font among other things. To define the style we have to use the HSSFFont object and after assign it to our style objectThe objects we are goiNotice that to create the colour we have to use the constant HSSFColor.XXXX.index

Lastly we are going to save our book in an output stream or update it in the case we have already one (in that case you should have previosuly loaded all the information in your book). We make sure we close the file before returning.

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

/**
 * 
 * This class shows how to create a basic file excel using the POI API
 * 
 * @author www.ProgrammingWorkshop.net
 * @version 1.0
 * 
 */
public class ExcelExample {

	public static final String FILE_NAME = "c:\\helloworld.xls";

	/**
	 * 
	 * This function creates a new style for a book
	 * 
	 * @param book
	 *            This is the book where we are going to create the style
	 * @return We return the style created for the book
	 * @throws Exception
	 * 
	 */
	public HSSFCellStyle getStyle(HSSFWorkbook book) throws Exception {
		// First of all we have to create the style for this book
		HSSFCellStyle style = book.createCellStyle();

		// We establish the background color
		style.setFillForegroundColor(HSSFColor.ORANGE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

		// We establish a new font for this book
		HSSFFont font = book.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);

		return style;
	}

	/**
	 * 
	 * This procedure create a book, sheet, row and cell in this order. Also it records the data
	 * into the FILE_NAME.
	 * 
	 */
	public void createExcel() {
		try {
			// We create the book. If we'd like to load a book from a file we should write something
			// like this: new HSSFWorkbook(new FileInputStream(file))
			HSSFWorkbook book = new HSSFWorkbook();

			// We create the sheet for this book
			HSSFSheet sheet = book.createSheet("My first sheet");

			// We create the first row. It starts in 0.
			HSSFRow row = sheet.createRow(0);

			// We create the first cell for the row. It starts in 0.
			HSSFCell cell = row.createCell(0);

			// We establish the value for that cell
			cell.setCellValue("Cell data");

			// Optional. We establish the cell style
			cell.setCellStyle(getStyle(book));

			// Optional. We establish the column wide. When I open an excel I don't like to click
			// over the column to see all the information inside the cell <img
			// src="http://localhost/programmingworkshop/wp-includes/images/smilies/icon_smile.gif"
			// alt=":)" class="wp-smiley">
			sheet.setColumnWidth(0, 20 * 256);

			// Now it's time to record the data in a file making sure we close the file
			FileOutputStream output = null;
			try {
				output = new FileOutputStream(FILE_NAME);
				book.write(output);
				output.close();
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				if (output != null) {
					output.close();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * Create an instance of ExcelExample object and call the createExcel procedure
	 * 
	 * @param args
	 *            Command line parameters. Not required in this example
	 * 
	 */
	public static void main(String args[]) {
		new ExcelExample().createExcel();
	}
}

Now, if we open our helloworld.xls file we will see this:

excel1

Tagged on: ,

Leave a Reply

Your email address will not be published. Required fields are marked *


6 × seven =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>