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!!
QA Automation Expert 