How to Write Data in Excel Using Selenium? – 2 Easy Methods

Introduction

Selenium is a widely used open-source framework that enables automated testing and interaction with web applications. While its primary use is in testing, Selenium can also be leveraged for web scraping and automation tasks.

Are you looking to automate the process of writing data into an Excel spreadsheet using Selenium? Well, you’re in luck! In this guide, we will walk you through the steps of how to write data in Excel using Selenium, a powerful browser automation tool.

Important Factors for Writing Data in Excel Using Selenium

When it comes to writing data to Excel files with Selenium, several crucial factors come into play. Let’s delve into each of these factors in detail:

1. java.io.File: The java.io.File class serves as a representation of file and directory pathnames. For Excel file manipulation, this class is essential in creating a reference to the target Excel file.
2. FileOutputStream: The FileOutputStream class is employed to write data to a file. This class helps in channeling data from the program into the Excel file.
3. Workbook: The Workbook interface, a part of the Apache POI library, takes center stage in Excel file operations. It acts as the container for sheets and data within the Excel file. These are the two main implementations of this interface:
   • XSSFWorkbook[.xlsx]: This class is designed for .xlsx files (Excel 2007 and later) and is tailored for the XML-based format.
   • HSSFWorkbook[.xls]: For .xls files (Excel 97-2003), this class is used. Similar to XSSFWorkbook, it interacts with the older binary .xls format.
4. Sheet: Representing an individual sheet within a workbook, the Sheet interface is crucial. Workbooks can encompass multiple sheets, each containing rows and cells. There are distinct classes for .xlsx and .xls files:
   • XSSFSheet[.xlsx]: Interacts with sheets in .xlsx workbooks, especially designed for the XML-based format.
   • HSSFSheet[.xls]: Used for .xls workbooks, functioning with the older binary format.
5. Row: The Row interface corresponds to a row within an Excel sheet. It allows horizontal iteration through cells within a row. For different file formats:
   • XSSFRow[.xlsx]: Interacts with rows in .xlsx sheets, catering to the XML-based format.
   • HSSFRow[.xls]: Works with rows in .xls sheets, catering to the older binary format.
6. Cell: The Cell interface represents an individual cell within an Excel sheet. It’s instrumental in retrieving and modifying data stored within cells. Different classes are used based on the file format:
   • XSSFCell[.xlsx]: Used for cells in .xlsx sheets, tailored for the XML-based format.
   • HSSFCell[.xls]: Interacts with cells in .xls sheets, catering to the older binary format.

How to Write Data in Excel Using Selenium?

We have two options for working with Excel. We can either add information to a file that’s already there, or we can make a new Excel file and put our information in it.

Let’s look at each method separately.

1. How to Write Data in Existing Excel Using Selenium?

When we have an existing Excel file, we can get information from it or add more data to it. In both situations, we can use a tool called Apache POI. In this case, we’ll use the same Excel file that we used to get information from before. If you don’t know how to get information from Excel, you can check out our previous tutorial on how to do that using Selenium.

To put new information in a specific cell, first, we’ll find the cell we want and then change its value to the new information.

Imagine we have some data already in our Excel file. We want to put a new column at the end, and we’ll call this column “ExecutionStatus“

Now, let’s take a look at the whole program to understand how to add information to an Excel file that already has data in it.

/*
 * How to Write Data in Excel Using Selenium?
 */
package auto.java.apachepoi;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class AddDataInExistingExcelFile {
	void writeData() {
		try {
			File file = new File("C:\\Satyajeet Nayak\\TestData.xlsx");

			FileInputStream fis = new FileInputStream(file);

			Workbook workbook = WorkbookFactory.create(fis);

			Sheet sheet = workbook.getSheet("Sheet1");

			// Write data
			sheet.getRow(0).createCell(5).setCellValue("ExecutionStatus");
			workbook.write(new FileOutputStream(file));

			workbook.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {

		AddDataInExistingExcelFile data = new AddDataInExistingExcelFile();
		data.writeData();

	}

}Code language: Java (java)

After putting more information into the Excel file that already existed, the file now appears as follows:

Explanation (1. How to Write Data in Existing Excel Using Selenium)

let’s break down the given Java code. This code demonstrates how to write data to an existing Excel file using Selenium and the Apache POI library. It focuses on updating a specific cell in an existing Excel file. Here is the step-by-step breakdown of the code:

• Import Statements:

   import java.io.File;
   import java.io.FileInputStream;
   import java.io.FileOutputStream;
   import org.apache.poi.ss.usermodel.Sheet;
   import org.apache.poi.ss.usermodel.Workbook;
   import org.apache.poi.ss.usermodel.WorkbookFactory;Code language: CSS (css)

These are the import statements that bring in the necessary classes and packages for working with Excel files using Apache POI.

• Class Declaration:

   public class AddDataInExistingExcelFile {Code language: PHP (php)

This code defines a class named AddDataInExistingExcelFile.

• Method Declaration – writeData():

   void writeData() {Code language: JavaScript (javascript)

This defines a method named writeData(), which is responsible for adding data to an existing Excel file.

The code enters a try block, where the main operations are performed. It attempts to execute the code within the try block and handles any exceptions that might occur using the catch block.

• File Initialization:

   File file = new File("C:\\Satyajeet Nayak\\TestData.xlsx");Code language: JavaScript (javascript)

A File object is created, pointing to an Excel file named “TestData.xlsx” located at the specified file path.

• FileInputStream Initialization:

   FileInputStream fis = new FileInputStream(file);Code language: JavaScript (javascript)

A FileInputStream is created using the previously defined File object. This stream is used to read data from the Excel file.

• Workbook Initialization:

   Workbook workbook = WorkbookFactory.create(fis);

A Workbook object is created using the WorkbookFactory class. This allows you to read and manipulate the Excel file.

• Sheet Retrieval:

   Sheet sheet = workbook.getSheet("Sheet1");Code language: JavaScript (javascript)

The Sheet object named “Sheet1” is retrieved from the workbook. The code assumes that there is a sheet named “Sheet1” in the Excel file.

• Write Data:

   sheet.getRow(0).createCell(5).setCellValue("ExecutionStatus");Code language: CSS (css)

The code gets the first row of the sheet, creates a new cell in the 6th column (index 5), and sets its value to “ExecutionStatus”. This effectively adds data to the Excel sheet.

• Save Changes: workbook.write(new FileOutputStream(file)); The changes made to the workbook are saved back to the Excel file using a FileOutputStream.
• Workbook Close: workbook.close(); The workbook is closed to release any resources associated with it.
• main() Method:
  java public static void main(String[] args) { AddDataInExistingExcelFile data = new AddDataInExistingExcelFile(); data.writeData(); }
  This is the entry point of the program. An instance of the AddDataInExistingExcelFile class is created, and the writeData() method is called to perform the data writing operation.

2. How to Write Data in Excel Using Selenium by Creating a New Excel File?

To write data in Excel using Selenium by creating a new Excel file, follow the below program. Here we have used Apache POI, WorkbookFactory, Workbook, Row, createRow(), createCell(), setCellValue(), and write().

Imagine we want to use Selenium to put this information into an Excel file.

Let’s go through the complete program now and understand each and every piece of code in detail.

/*
 * How to Write Data in Excel Using Selenium?
 */
package auto.java.apachepoi;

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

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.ss.usermodel.WorkbookFactory;

public class GenerateExcel {

	public static void main(String[] args) {
		try {
			// Create a workbook of .xlsx format
			Workbook workbook = WorkbookFactory.create(true);

			// Add a new sheet as Sheet 1
			Sheet sheet = workbook.createSheet("Sheet 1");

			// Create a new Row in Excel Sheet and add data
			Row row = sheet.createRow(0);
			row.createCell(0).setCellValue("TestScript");
			row.createCell(1).setCellValue("UserName");
			row.createCell(2).setCellValue("Password");

			// Create another Row and add data
			Row row1 = sheet.createRow(1);
			row1.createCell(0).setCellValue("TC_001");
			row1.createCell(1).setCellValue("Admin");
			row1.createCell(2).setCellValue("Admin@123");

			// Write data to TestData1.xlsx file
			workbook.write(new FileOutputStream(new File("C:\\\\Satyajeet Nayak\\\\TestData1.xlsx")));

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

}Code language: Java (java)

Explanation (How to Write Data in Excel Using Selenium by Creating a New Excel File)

Let’s break down the given Java code for writing data to an Excel file using the Apache POI library and Selenium. This code generates an Excel workbook and adds data to it. Let’s go through the code step by step:

1. Import Statements: Import necessary classes from the Apache POI library to work with Excel files and other required classes.

import java.io.File;
import java.io.FileOutputStream;
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.ss.usermodel.WorkbookFactory;Code language: CSS (css)

2. Main Method: The code starts executing from the main method.

public static void main(String[] args) {
    try {
        // Create a workbook of .xlsx format
        Workbook workbook = WorkbookFactory.create(true);Code language: JavaScript (javascript)

Here, a new workbook is created using the WorkbookFactory.create(true) method. The true argument indicates that the workbook will be created in memory.

3. Add a New Sheet:

// Add a new sheet as Sheet 1
Sheet sheet = workbook.createSheet("Sheet 1");Code language: JavaScript (javascript)

A new sheet “Sheet 1” is created in the workbook using the createSheet method.

4. Create Rows and Cells: Data is added to the sheet by creating rows and cells within those rows.

// Create a new Row in the Sheet and add data
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("TestScript");
row.createCell(1).setCellValue("UserName");
row.createCell(2).setCellValue("Password");Code language: JavaScript (javascript)

A new row is created using the createRow method, and then cells are added to that row using the createCell method. Data is set in the cells using setCellValue.

// Create another Row and add data
Row row1 = sheet.createRow(1);
row1.createCell(0).setCellValue("TC_001");
row1.createCell(1).setCellValue("Admin");
row1.createCell(2).setCellValue("Admin@123");Code language: JavaScript (javascript)

Another row is created similarly with different data.

5. Write to Excel File:

// Write data to TestData1.xlsx file
workbook.write(new FileOutputStream(new File("C:\\Satyajeet Nayak\\TestData1.xlsx")));Code language: JavaScript (javascript)

The workbook data is written to an Excel file named “TestData1.xlsx” using the FileOutputStream and write methods. The file will be created in the specified directory (C:\Satyajeet Nayak\ in this case).

6. Exception Handling:

} catch (Exception e) {
    e.printStackTrace();
}Code language: PHP (php)

If any exceptions occur during the process, they will be caught, and their stack traces will be printed.

In summary, this Java code uses the Apache POI library to create an Excel workbook, add sheets, rows, and cells to it, populate those cells with data, and then write the workbook data into an Excel file. It’s a basic example of how to write data in Excel using Selenium programmatically using Java where we have created a new Excel file.

Conclusion

Automating Excel tasks using Selenium is a game-changer, streamlining processes and freeing up valuable time. With the right tools, techniques, and a bit of creativity, you can transform how you interact with web data and Excel files. By following the tutorial on “How to write data in Excel using Selenium?“, you can be able to create and add data in Excel File.

Frequently Asked Questions (FAQs)

Recommended Articles

• How To Read Data From Excel In Selenium?
• How To Handle Multiple Windows In Selenium?
• How to check Selenium version?
• How To Handle CAPTCHA In Selenium?
• How to Achieve Synchronization in Selenium Webdriver?