How to write Excel files in java using Apache POI?

February 23, 2022 / , / By

In this article, you will learn how to write Excel(XLS & XLSX) files in java using the Apache POI library. If you are new to the Apache POI library then we recommend you to learn the basics of Apache POI.

If you know the fundamentals of Apache POI already then directly move to follow this article now,

Apache POI Envirnment setup

There are two ways for installing Apache POI in Eclipse, based on your project type:

  1. Maven Project
  2. Stand alone Java Project (Non Maven)

Note: In this article, we have used the Apache POI-4.0.1 version of the library to demonstrate.

Maven Project

If you are going to create a maven project then you have to add the following maven dependency in the pom.xml file of your project:

<!-- Used for Excel 2003 or before (xls) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>

<!-- Used for Excel 2007 or later (xlsx)  -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>

Stand alone Java Project (Non Maven)

If you are going to create a standalone java project then you have to add the following jar files into your java build path:

  1. poi-4.0.1.jar
  2. poi-ooxml-4.0.1.jar
  3. poi-ooxml-schemas-4.0.1.jar
  4. xmlbeans-3.0.2.jar
  5. curvesapi-1.05.jar
  6. commons-codec-1.11.jar
  7. commons-collections4-4.2.jar
  8. commons-compress-1.18.jar
  9. commons-math3-3.6.1.jar

You can easily download all the above jars in one place: Download Apache POI Jars

If you want to know the complete environment setup for Apache POI in Eclipse IDE, follow another article here: Apache POI – Getting Started

Basics of Apache POI to Write Excel Files

In the Apache POI, there are multiple interfaces and classes which are used to write the Excel files. Let’s see the basic interfaces and classes here:

The basic interfaces are Workbook, Sheet, Row, Cell, CellStyle, Font, etc. And there are specific concrete classes for specific file formats (.xls and .xlsx).

For Excel file format 2003 (XLS):  HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell, etc.

For Excel file format 2007 (XLSX): XSSFWorkbook, XSSFSheet, XSSFRow, XSSFCell, etc.

  • HSSFWorkbook & XSSFWorkbook – used to create the workbook
  • HSSFSheet & XSSFSheet – used to create the sheet for the excel file
  • HSSFRow & XSSFRow – used to create the row in the sheet
  • HSSFCell & XSSFCell – used to create the cell in the row

We recommend you use common interfaces to support both Excel format XLS and XLSX.

The followings are the basic steps to write data in an Excel file:

  1. Create a Workbook.
  2. Create a Sheet.
  3. Create a Row.
  4. Create a Cell.
  5. Repeat the step-3 and step-4 until all data is processed.
  6. Write workbook to an OutputStream.
  7. Close the stream and workbook.

A Sample Example to write Excel 2003 format(xls)

The below java code demonstrates writing data to an Excel(.xls) file:

package com.javacodepoint.excel;

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

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

public class WriteExcelFile {

	// Main method
	public static void main(String[] args) {

		// Creating a xlsfile object with specific file path
		File xlsFile = new File("E:/Excel/employee.xls");

		// Writing the xls file
		writeXLSFile(xlsFile);

	}

	// Method to write xls file
	public static void writeXLSFile(File xlsFile) {

		try {
			// Creating workbook
			HSSFWorkbook workbook = new HSSFWorkbook();

			// Creating sheet
			HSSFSheet sheet = workbook.createSheet("Employee Records");

			// Creating header of the excel sheet
			HSSFRow header = sheet.createRow(0);

			// Creating cell and setting the cell value
			header.createCell(0).setCellValue("Employee Id");
			header.createCell(1).setCellValue("Employee Name");
			header.createCell(2).setCellValue("Age");
			header.createCell(3).setCellValue("Email ID");
			header.createCell(4).setCellValue("Salary");

			// Creating the 1st row to insert employee record
			HSSFRow row1 = sheet.createRow(1);

			// Inserting 1st employee record
			row1.createCell(0).setCellValue("101");
			row1.createCell(1).setCellValue("John william");
			row1.createCell(2).setCellValue("30");
			row1.createCell(3).setCellValue("william.john@gmail.com");
			row1.createCell(4).setCellValue("15000$");

			// Creating the 2nd row
			HSSFRow row2 = sheet.createRow(2);

			// Inserting 2nd employee record
			row2.createCell(0).setCellValue("102");
			row2.createCell(1).setCellValue("Harsh singh");
			row2.createCell(2).setCellValue("35");
			row2.createCell(3).setCellValue("harsh.singh@gmail.com");
			row2.createCell(4).setCellValue("20000$");

			// Creating file output stream to write the workbook data in to the file
			FileOutputStream fos = new FileOutputStream(xlsFile);

			// Writing workbook
			workbook.write(fos);

			// Closing the output stream
			fos.close();

			// Closing the workbook
			workbook.close();

			// Printing the success message on the console
			System.out.println("Excel(.xls) file has been created successfully.");

		} catch (IOException e) {
			System.out.println("Exception while writting xls file");
			e.printStackTrace();
		}
	}

}

Below is a screenshot of the created Excel file-

Write Excel using Apache POI

A Sample Example to write Excel 2007 format(xlsx)

The below java code demonstrates writing data to an Excel(.xlsx) file:

package com.javacodepoint.excel;

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

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

public class WriteExcelFile {

	// Main method
	public static void main(String[] args) {

		// Creating a xlsxfile object with specific file path
		File xlsxFile = new File("E:/Excel/employee.xlsx");
		
		// Writing the xlsx file
		writeXLSXFile(xlsxFile);
	}

	// Method to write xlsx file
	public static void writeXLSXFile(File xlsxFile) {

		try {
			// Creating workbook
			XSSFWorkbook workbook = new XSSFWorkbook();

			// Creating sheet
			XSSFSheet sheet = workbook.createSheet("Employee Records");

			// Creating header of the excel sheet
			XSSFRow header = sheet.createRow(0);

			// Creating cell and setting the cell value
			header.createCell(0).setCellValue("Employee Id");
			header.createCell(1).setCellValue("Employee Name");
			header.createCell(2).setCellValue("Age");
			header.createCell(3).setCellValue("Email ID");
			header.createCell(4).setCellValue("Salary");

			// Creating the 1st row to insert employee record
			XSSFRow row1 = sheet.createRow(1);

			// Inserting 1st employee record
			row1.createCell(0).setCellValue("101");
			row1.createCell(1).setCellValue("John william");
			row1.createCell(2).setCellValue("30");
			row1.createCell(3).setCellValue("william.john@gmail.com");
			row1.createCell(4).setCellValue("15000$");

			// Creating the 2nd row
			XSSFRow row2 = sheet.createRow(2);

			// Inserting 2nd employee record
			row2.createCell(0).setCellValue("102");
			row2.createCell(1).setCellValue("Harsh singh");
			row2.createCell(2).setCellValue("35");
			row2.createCell(3).setCellValue("harsh.singh@gmail.com");
			row2.createCell(4).setCellValue("20000$");

			// Creating file output stream to write the workbook data in to the file
			FileOutputStream fos = new FileOutputStream(xlsxFile);

			// Writing workbook
			workbook.write(fos);

			// Closing the output stream
			fos.close();

			// Closing the workbook
			workbook.close();

			// Printing the success message on the console
			System.out.println("Excel(.xlsx) file has been created successfully.");

		} catch (IOException e) {
			System.out.println("Exception while writting xlsx file");
			e.printStackTrace();
		}
	}

}

A Generic Example to write both Excel format(.xls and .xlsx)

The following java code demonstrates writing data to an Excel(.xls and .xlsx) file to support both formats:

package com.javacodepoint.excel;

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

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteExcelFile {

	// Main method
	public static void main(String[] args) {

		// Creating a xlsxfile object with specific file path
		File xlsxFile = new File("E:/Excel/employee.xlsx");
		
		// Common method to write excel file based on file format
		writeExcelFile(xlsxFile);
	}

	// Method to write both excel file xls & xlsx format
	public static void writeExcelFile(File excelFile) {

		try {
			Workbook workbook = null;

			// Creating workbook based on file format
			if (excelFile.getName().endsWith(".xls")) {
				workbook = new HSSFWorkbook();
			} else if (excelFile.getName().endsWith(".xlsx")) {
				workbook = new XSSFWorkbook();
			} else {
				throw new IllegalArgumentException("The specified file is not supported");
			}

			// Creating sheet
			Sheet sheet = workbook.createSheet("Employee Records");

			// Creating header of the excel sheet
			Row header = sheet.createRow(0);

			// Creating cell and setting the cell value
			header.createCell(0).setCellValue("Employee Id");
			header.createCell(1).setCellValue("Employee Name");
			header.createCell(2).setCellValue("Age");
			header.createCell(3).setCellValue("Email ID");
			header.createCell(4).setCellValue("Salary");

			// Creating the 1st row to insert employee record
			Row row1 = sheet.createRow(1);

			// Inserting 1st employee record
			row1.createCell(0).setCellValue("101");
			row1.createCell(1).setCellValue("John william");
			row1.createCell(2).setCellValue("30");
			row1.createCell(3).setCellValue("william.john@gmail.com");
			row1.createCell(4).setCellValue("15000$");

			// Creating the 2nd row
			Row row2 = sheet.createRow(2);

			// Inserting 2nd employee record
			row2.createCell(0).setCellValue("102");
			row2.createCell(1).setCellValue("Harsh singh");
			row2.createCell(2).setCellValue("35");
			row2.createCell(3).setCellValue("harsh.singh@gmail.com");
			row2.createCell(4).setCellValue("20000$");

			// Creating file output stream to write the workbook data in to the file
			FileOutputStream fos = new FileOutputStream(excelFile);

			// Writing workbook
			workbook.write(fos);

			// Closing the output stream
			fos.close();

			// Closing the workbook
			workbook.close();

			// Printing the success message on the console
			System.out.println("Excel file has been created successfully.");

		} catch (IOException e) {
			System.out.println("Exception while writting excel file");
			e.printStackTrace();
		}
	}

}

Formatting Cells of the Excel sheet

To make the look and feel of an Excel sheet attractive, you may require to format the cells of the Excel file. Here we will show you the basic formatting ideas that help you to understand the fundamental of formatting the cells.

Let’s understand the following steps:

Step-1: Create a CellStyle object to holds formatting information.

CellStyle cellStyle = workbook.createCellStyle();

Step-2: Now call the appropriate setters to apply the formatting you want.

cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setFont(font);
cellStyle.setWrapText(true);

Let’s see the complete example below-

CellStyleInExcelFile.java

package com.javacodepoint.excel;

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

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CellStyleInExcelFile {

	public static void main(String[] args) {

		// Creating a xlsxfile object with specific file path
		File xlsxFile = new File("E:/Excel/employee.xlsx");

		// Writing the xlsx file
		writeFormattedXLSXFile(xlsxFile);
		
	}

	// Method to write formatted xlsx file
	public static void writeFormattedXLSXFile(File xlsxFile) {

		try {
			// Creating workbook
			XSSFWorkbook workbook = new XSSFWorkbook();

			// Creating sheet
			XSSFSheet sheet = workbook.createSheet("Employee Records");
			
			// Creating cell style for header
			CellStyle headerStyle = workbook.createCellStyle();
			Font font1 = workbook.createFont();
			font1.setFontHeightInPoints((short)16);
			font1.setBold(true);
			headerStyle.setFont(font1);
			headerStyle.setAlignment(HorizontalAlignment.CENTER);

			XSSFRow header = sheet.createRow(0);
			Cell cell= header.createCell(0);
			cell.setCellValue("Employee Id");
			cell.setCellStyle(headerStyle);	// applying the cell style					
			
			cell= header.createCell(1);
			cell.setCellValue("Employee Name");
			cell.setCellStyle(headerStyle);	// applying the cell style
			
			cell= header.createCell(2);
			cell.setCellValue("Age");
			cell.setCellStyle(headerStyle);	// applying the cell style
			
			cell= header.createCell(3);
			cell.setCellValue("Email ID");
			cell.setCellStyle(headerStyle);	// applying the cell style
			
			cell= header.createCell(4);
			cell.setCellValue("Salary");
			cell.setCellStyle(headerStyle);	// applying the cell style

			// Creating a row to insert employee record
			XSSFRow row = sheet.createRow(1);
			
			//Creating cell style for row data
			CellStyle dataCellStyle = workbook.createCellStyle();
			Font font2=workbook.createFont();
			font2.setFontHeightInPoints((short)14);
			font2.setColor((short)4);//blue color
			dataCellStyle.setFont(font2);
			dataCellStyle.setAlignment(HorizontalAlignment.CENTER);

			cell= row.createCell(0);
			cell.setCellValue("101");
			cell.setCellStyle(dataCellStyle);	// applying the cell style
			
			cell= row.createCell(1);
			cell.setCellValue("John william");
			cell.setCellStyle(dataCellStyle);	// applying the cell style
			
			cell= row.createCell(2);
			cell.setCellValue("30");
			cell.setCellStyle(dataCellStyle);	// applying the cell style
			
			cell= row.createCell(3);
			cell.setCellValue("william.john@gmail.com");
			cell.setCellStyle(dataCellStyle);	// applying the cell style
			
			cell= row.createCell(4);
			cell.setCellValue("15000$");
			cell.setCellStyle(dataCellStyle);	// applying the cell style
			
			/* automatic adjust data in column using autoSizeColumn,
			 * autoSizeColumn should be made after populating the data into the excel. 
			 * Calling before populating data will not have any effect.
			 */
			sheet.autoSizeColumn(0);
			sheet.autoSizeColumn(1);
			sheet.autoSizeColumn(2);
			sheet.autoSizeColumn(3);
			sheet.autoSizeColumn(4);


			// Creating file output stream to write the workbook data in to the file
			FileOutputStream fos = new FileOutputStream(xlsxFile);

			// Writing workbook
			workbook.write(fos);

			// Closing the output stream & workbook
			fos.close();
			workbook.close();

			// Printing the success message on the console
			System.out.println("Formatted Excel(.xlsx) file has been created successfully.");

		} catch (IOException e) {
			System.out.println("Exception while writting formatted xlsx file");
			e.printStackTrace();
		}
	}

}

The below image is a screenshot of a formatted excel file:

formatted excel file | Write Excel using Apache POI

Conclusion

In this article, you have seen how to write an Excel file using Apache POI API library in java. You have also seen how to format cells of the Excel file.

If you want to download the above example programs developed using maven and eclipse, download them from the below link.

Related Articles:

You may also like:

Share with friends

Leave a Comment

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