Writing to Excel File using POI API

In the previous post, we have given the overview of Apache POI API like what are these, how to download them etc.In this post, we will learn how to use POI API to write the data into Excel Files.

We will be using (.xlsx) format, for which we require implementation of XSSF API. Let’s understand the procedure step by step.

Step#1 It is assumed that you have downloaded required jars and uploaded to your class ( as shown in previous post). As we know, an excel workbook contains the components like workbook, sheet, rows and cells. In Selenium also , we need to do the same with the help of coding. First we will create the objects like  XSSFWorkbook, XSSFSheet.XSSFRow,XSSFCell etc.

	private static XSSFWorkbook workbook;
	private static XSSFSheet sheet;
	private static XSSFCell cell;
	private static XSSFRow row;

Step#2 First , we will create one Excel file instance and a sheet under this named as ‘Data’.

		workbook = new XSSFWorkbook();
		sheet = workbook.createSheet("Data");

Step#3 We need some data which will be written into the excel sheet. We have created some dummy data with the help of  2 dimensional array of String type. This data , we will write into the Excel sheet. (You can have data according to your requirement).

		String[][] data = new String[3][2];
		data[0][0] = "Name";
		data[0][1] = "City";
		data[1][0] = "Shekhar";
		data[1][1] = "Bangalore";
		data[2][0] = "Manan";
		data[2][1] = "Delhi";

Step#4 We will count the no. of rows and columns need to be filled up with the data. For that, we will count the length of array.

		int dataRows = data.length;
		int dataColumn = data[1].length;

Step#5 Next, we need to create the row and column to set the value into it. For that , we will use the methods mentioned below.

Syntax:

//Create row
Row row = sheet.createRow(RowNumber) 
//Create Cell
cell cell row.createCell(CellNumber)
//Set value into the cell
cell.setCellValue(DataValue);

Step#6 We will use FileOutputStream method to flush the data to the Excel file and File path will be provided in the arguments. We use the write method to write the data into Excel file mentioned in FileOutputStream.

After that close the file output steam.

	FileOutputStream fileout = new FileOutputStream(new File("E:\\Excel Files\\Sample.xlsx"));
	workbook.write(fileout);
	fileout.close();

Full Code to write the data into Excel Files:

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

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
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 WritingExcelExample {
	private static XSSFWorkbook workbook;
	private static XSSFSheet sheet;
	private static XSSFCell cell;
	private static XSSFRow row;

	public static void main(String[] args) throws InvalidFormatException, IOException {
		//Create Excel Instance
		 workbook = new XSSFWorkbook();
		 //Create Sheet named as Data
		 sheet = workbook.createSheet("Data");
		 //String array
		String[][] data = new String[3][2];
		data[0][0] = "Name";
		data[0][1] = "City";
		data[1][0] = "Shekhar";
		data[1][1] = "Bangalore";
		data[2][0] = "Manan";
		data[2][1] = "Delhi";
		//Row count array
		int dataRows = data.length;
		//Column count
		int dataColumn = data[1].length;
		
		//for loop for iterating over the data 
		for(int i=0;i<dataRows;i++){
			 row =sheet.createRow(i);
			for(int j=0;j<dataColumn;j++){
				String fillData = data[i][j];
				System.out.println(fillData+"--");
		    	 cell = row.createCell(j);
		    	 //Set value into cell
				cell.setCellValue(fillData); 
			}
		}
		
		//Writing data to created excel instance		
		FileOutputStream fileout = new FileOutputStream(new File("C:\\Users\\Shekar Sharma\\Documents\\Sample1.xlsx"));
		workbook.write(fileout);
		fileout.close();
	}

}

Output: