Useful Operations in Excel Using POI API

In the previous post, we have learnt how to Read data from Excel file. Apart from usual reading and writing , we will perform some useful operations in Excel using POI API.

In Selenium, if we are creating reporting using excel file then we might need to format excel files according to our need. Let’s see different kind of operations on Excel files.

1. Insert the comment into the desired Cell:

We have Create a reusable function as given below.

The function will be called in the following way.

Insert comment into excel POI

 


2. Format the cell (change the foreground color and font type): 

We will show you how easy it is to format the cell like changing foreground color, background color,font type,font color etc. bu using POI API.

Color change in excel POI

This can be very useful when creating a test report and highlighting  passed and failed test with specific color.

Reusable function for cell formatting:

Calling above function:

Output:

Color change using POI API


3. Creating Hyperlink in the Excel File:

We can create hyperlinks in the excel files , that makes it very handy. Suppose, you need to add some website links, making reference from one sheet to another sheet etc. The same file can be sent to intended users containing all hyperlinks and details.

Let’s see its 2 types of implementations.

  • Creating a hyperlink which points to a Website In out example, we have created the hyperlink in second row which is the name ‘Shekhar’. This will be linked to the website ‘www.testingpool.com’. Once you click on the hyperlink, intended site should open.
Calling to the above function:
Output:

Hyperlink in excel using POI

 

  • Creating hyperlink with reference to another sheet: we will create the Hyperlink to cell ‘Bangalore’, where it will point to another sheet ‘Sheet1′ in the same workbook. When click on ‘Bangalore’, it will link to another sheet named as ‘Sheet1’ and cell named as ‘India’ in that sheet1.
Calling the function:
Hyperlink click in poi

 


Ask Question
Have any question or suggestion for us?Please feel free to post in Q&A Forum
Print Friendly, PDF & Email
Reading from Excel using POI API
Firefox Profiling in Selenium
Shekhar Sharma

Shekhar Sharma

Shekhar Sharma is founder of testingpool.com. He is an automation engineer having more than 8 years of experience who loves troubleshooting in automation and finding innovative ways to solve the problems. His other leisurely activities includes playing harmonica, paintings, sketching portrait, watching movies and travelling etc.

You may also like...