vibssingh

How to Write Data to Excel File in Java using Apache POI

HOME

This tutorial describes the steps to write data in Excel file in Java.

I’m using Apache POI to write data to the excel file. To download and install Apache POI, refer here.

In the previous tutorial, I have explained How to read data from Excel in Java.

Dependency:

If you are using maven, then you need to add below dependency in pom.xml.

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

Here are few classes which you need to aware of.

  • Workbook : This is high level class for representing excel workbook.
  • Sheet : This is high level class for representing excel sheet.
  • Row : This is high level class for representing excel row. It has methods which are related to row.
  • Cell: This is high level class for representing individual excel cell. It has methods which are related to cell for example : getDataType().

The basic steps for writing data into an excel file using Apache POI API are given below:

Implementation Steps

Step 1 – Create a blank workbook.

XSSFWorkbook wb = new XSSFWorkbook();

Step 2 – Import XSSFWorkbook from package.

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Step 3 – Create a sheet and pass name of the sheet.

XSSFSheet sheet = workbook.createSheet("Write_TestData");

Step 4 – Import XSSFSheet from package.

import org.apache.poi.xssf.usermodel.XSSFSheet;

Step 5 – Prepare Data to Write

An ArrayList of Object[] arrays is created to hold the data. Each array represents a row in the sheet.

ArrayList<Object[]> data = new ArrayList<Object[]>();
		data.add(new String[] { "Name", "Id", "Salary" });
		data.add(new Object[] { "Jim", "001A", 10000 });
		data.add(new Object[] { "Jack", "1001B", 40000 });
		data.add(new Object[] { "Tim", "2001C", 20000 });
		data.add(new Object[] { "Gina", "1004S", 30000 });

Step 6 – Create a Row. A spreadsheet consists of rows and cells. It has a grid layout.

 XSSFRow row = sheet.createRow(rownum++);

Step 7 – Import XSSFRow from package

import org.apache.poi.xssf.usermodel.XSSFRow;

Step 8 – Create cells in a row. A row is a collection of cells. When you enter data in the sheet, it is always stored in the cell.

 XSSFCell cell = row.createCell(cellnum++);

Step 9 – Import XSSFCell from package.

 XSSFCell cell = row.createCell(cellnum++);

Step 10 – Iterate over Data and Write to the sheet

	int rownum = 0;
		for (Object[] employeeDetails : data) {

			// Create Row
			XSSFRow row = sheet.createRow(rownum++);

			int cellnum = 0;
			for (Object obj : employeeDetails) {

				// Create cell
				XSSFCell cell = row.createCell(cellnum++);

				// Set value to cell
				if (obj instanceof String)
					cell.setCellValue((String) obj);
				else if (obj instanceof Double)
					cell.setCellValue((Double) obj);
				else if (obj instanceof Integer)
					cell.setCellValue((Integer) obj);
			}
		}

Step 11 – Open a FileOutputStream to a new file named “EmployeeDetails.xlsx”. Write data to an OutputStream. Use the below code to write output stream.

FileOutputStream out = new FileOutputStream(new File("EmployeeDetails.xlsx"));
workbook.write(out);

Step 12 – Close the output stream.

out.close();

Below is the sample code for writing the data in an excel in Java.

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;

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 ReadWriteExcelFile {

	public static void main(String[] args) throws IOException {

		// create blank workbook
		XSSFWorkbook workbook = new XSSFWorkbook();

		// Create a blank sheet
		XSSFSheet sheet = workbook.createSheet("Write_TestData");

		ArrayList<Object[]> data = new ArrayList<Object[]>();
		data.add(new String[] { "Name", "Id", "Salary" });
		data.add(new Object[] { "Jim", "001A", 10000 });
		data.add(new Object[] { "Jack", "1001B", 40000 });
		data.add(new Object[] { "Tim", "2001C", 20000 });
		data.add(new Object[] { "Gina", "1004S", 30000 });

		// Iterate over data and write to sheet
		int rownum = 0;
		for (Object[] employeeDetails : data) {

			// Create Row
			XSSFRow row = sheet.createRow(rownum++);

			int cellnum = 0;
			for (Object obj : employeeDetails) {

				// Create cell
				XSSFCell cell = row.createCell(cellnum++);

				// Set value to cell
				if (obj instanceof String)
					cell.setCellValue((String) obj);
				else if (obj instanceof Double)
					cell.setCellValue((Double) obj);
				else if (obj instanceof Integer)
					cell.setCellValue((Integer) obj);
			}
		}
		try {

			// Write the workbook in file system
			FileOutputStream out = new FileOutputStream(new File("EmployeeDetails.xlsx"));
			workbook.write(out);
			out.close();
			System.out.println("EmployeeDetails.xlsx has been created successfully");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			workbook.close();
		}
	}

}

The output of the above program is

Summary:

  1. Create a WorkBook and Sheet
  2. Prepare the data. Use ArrayList to store the rows of data.
  3. Write Data to the Excel by iterating over each row and cell
  4. Save the Workbook
  5. Handle the exceptions appropriately

That’s it! We have written data in Excel.

Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!! Cheers!!

Posted in JavaTagged , , , , Leave a comment

Leave a comment