How to Write Data into Excel Sheet using Selenium Webdriver

We had learned how to read data from an Excel Sheet, now we are going to see how to write data into excel sheet using Selenium Webdriver.

If reading data from excel was simple for you, writing in one will be no different. The difference being just an Output Stream instead of an Input Stream, and maybe one write method that signals the JVM to start writing the data.

Let’s see how it is done.

How to Write Data into Excel Sheet using Selenium Webdriver

The first thing we gotta do while learning How to Write Data into Excel Sheet using Selenium Webdriver is to download and integrate Apache POI library files. Of course, if you haven’t already done it.

How to do that? You can check out the previous chapter for it.

Once that part is dealt with let’s focus on the coding bit.

So now we have a method ready to take inputs in a class called Testing.

Step 1: Specify the file path where you wish the Excel Sheet to show. I am hoping it to show in my D: drive in a file called output. Type the following:

File file = new File("D:\\output.xlsx");

You need to specify the file format too, and hence the suffix of .xlsx.

Step 2: Time to create a Workbook now. We will use the Apache POI class called XSSFWorkbook for that as we had seen in the previous tutorial. Type the following:

 XSSFWorkbook wb = new XSSFWorkbook();

You might have to import the following package if not already imported:

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

Step 3: Time to create a sheet. Type the following for creating a sheet:

XSSFSheet sh = wb.createSheet();

You might have to import the following package if not already imported:

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

You can choose to provide a name for the Sheet by simply typing the name of it in the parameter using double inverted commas like this:

XSSFSheet sh = wb.createSheet("First Sheet");

Not writing anything will default it to Sheet0.

Here’s the code so far:

Creating a Cell and Entering Values

Now we would be wanting create a cell. How is a cell made? By rows and columns right? Time to use the above declared variable ‘sh’ for row creation first.

We do that by using createRow() method. Follow it up by createCell() method which identifies the column and locks a cell. Then you can use any of the setCellValue methods to enter any data of your choice.

Step 4: Type the following to enter a String value in the first row and first column (A1) of your excel sheet:

sh.createRow(0).createCell(0).setCellValue("Age");

As you can see the parameters of createRow and createCell will be integers where you specify the row and column numbers. We have put 0, 0 in them so it will populate the first cell.

Step 5: Time to use an Output Stream. Type the following:

FileOutputStream fos = new FileOutputStream(file);

You might have to import the following package for your JVM to understand the FileOutputStream Class:

import java.io.FileOutputStream;

An error still might flare towards the right. You might have to handle the exception:

Step 6: Now we will use the write method of the XSSFWorkbook instance to actually start writing what we intended to write. Type the following for that in the try block itself:

 wb.write(fos);

When you will do so another red error underline might show up like this:

It is for another exception that you might encounter.

Step 7: You can either handle that too or simply replace the FileNotFoundException with its superset Exception like this:

So now your entire code might look something like this:

Step 8:  That’s the whole code there. Just run the program now.

Result

Step 9: Let’s navigate to the D: drive folder to check if the excel file has been created or not:

Voila!

Step 10: Let’s open it to check if the String value “Age” has been punched into it or not:

Sweet mother of Jesus! It is there.

I just realized I absolutely love coding. 🙂

Overwriting an Existing XLSX File

What if we wanted to populate the cell next to it? Let’s put in a number there:

All we have to do is append our existing code with this line:

 sh.getRow(0).createCell(1).setCellValue(69);

We didn’t use createRow again or it would have deleted the previous data by overwriting another row atop. Hence used getRow() method.

So the whole code would look like:

Running the code again will now simply overwrite the output.xlsx file (recreate it), and print the new output by appending the new line that we had entered.

There it is, the age!

Well I am not 69. I don’t know why I wrote that.

That concludes our chapter on How to Write Data into Excel Sheet using Selenium Webdriver.

Remember the above code has overwritten the xlsx file. It will delete the existing one, hence use it with care. If you want to append something to an existing excel file, the method is a tad different.

Appending Data to an Existing Excel File

Well in many cases you would be required to open an existing Excel file and enter your data in it in a said cell.

To do that we will need to first use FileInputStream to read the excel file, and then use FileOutputStream to enter our data. We will make changes in the existing output.xlsx that we had created above.

Here are the steps for it:

Step 1: After you have provided the location of the excel file that you wish to append data to using:

 File file = new File("D:\\output.xlsx");

type the following immediately after:

 FileInputStream fis = new FileInputStream(file);

You might be required to surround it in a try catch block:

NOTE: If you wish to read from some other Excel file you can specify that as well in the file location. Just change the path and put it as a parameter for FileInputStream class. Resuming the tutorial on How to Write Data into Excel Sheet using Selenium Webdriver:

Using XSSFWorkbook and XSSFSheet

Step 2: We will use the XSSFWorkbook and XSSFSheet again to retrieve the workbook and sheet. Type the following right after the FileInputStream initialization.

XSSFWorkbook wb = new XSSFWorkbook(fis); 
XSSFSheet sh = wb.getSheetAt(0);

So we have taken control of Sheet0. Now we want to type in what needs to be appended.

Step 3: Let’s put some value in the third column and maybe something in the first row so both of the scenarios are covered.

sh.getRow(0).createCell(2).setCellValue(100);

Okay, so as you can see we are trying to enter 100 in C1. We are not done yet.

Where’s that FileOutputStream, huh?

Step 4: Type the following code immediately after:

FileOutputStream fos = new FileOutputStream(file);

Now the actually writing of data happens when you use the write method. We will do that now:

Step 5: Type the following now:

wb.write(fos);

That’s it.

I will just quickly change the FileNotFoundException to Exception, so that the error message disappears.

The whole code might appear like this now:

I have just taken the liberty of initializing variables outside the method declaring them globally, since it is a good coding practice. Rids you of all the warning messages that you get too.

Step 6: Run the above program to see the result.

I will just quickly open the excel file to check whether it has updated:

Ooh yeah!

Will quickly add something in A2 now.

Just add this code in your existing one:

sh.createRow(1).createCell(0).setCellValue("ruck fules");

And running it again:

Sweet!

I am on fire!

So now that you know How to Write Data into Excel Sheet using Selenium Webdriver excel in Excel. Break a leg!