How to write data in Excel in Java

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.

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.0.0</version>
</dependency>
 
 
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.0.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:

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 – Create a Row. A spreadsheet consists of rows and cells. It has a grid layout.

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

Step 6 – Import XSSFRow from package

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

Step 7 – 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 8 – Import XSSFCell from package.

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

Step 9 – 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 10 – Close the output stream.

out.close();

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

package com.example.seleniumdemo;

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();
		}
	}

}

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!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s