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,
Table of Contents
Apache POI Envirnment setup
There are two ways for installing Apache POI in Eclipse, based on your project type:
- Maven Project
- 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:
- poi-4.0.1.jar
- poi-ooxml-4.0.1.jar
- poi-ooxml-schemas-4.0.1.jar
- xmlbeans-3.0.2.jar
- curvesapi-1.05.jar
- commons-codec-1.11.jar
- commons-collections4-4.2.jar
- commons-compress-1.18.jar
- 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 workbookHSSFSheet
&XSSFSheet
– used to create the sheet for the excel fileHSSFRow
&XSSFRow
– used to create the row in the sheetHSSFCell
&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:
- Create a
Workbook
. - Create a
Sheet
. - Create a
Row
. - Create a
Cell
. - Repeat the step-3 and step-4 until all data is processed.
- Write workbook to an
OutputStream
. - 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("[email protected]");
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("[email protected]");
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-
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("[email protected]");
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("[email protected]");
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("[email protected]");
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("[email protected]");
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("[email protected]");
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:
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:
- Apache POI – Overview
- Apache POI – Getting Started
- How to read Excel files in java using Apache POI?
- How to create password-protected Excel in java?
- How to write data to an existing Excel file in java?
- [Java code] Convert Excel file to CSV with Apache POI