How to Read and Write excel files in Selenium using Apache POI
Hello, Welcome to Selenium tutorial, in this post, we will see how to Read and Write excel files in Selenium
Selenium support only Web browser automation so for Read and Write excel files in Selenium we have to take help of third party API like JExcel and Apache POI
Apache POI is an API, which is freeware and written in Java and gives so much flexibility to read/write files it has so many predefined methods, classes, and interfaces.
Once you move to next step like designing framework then you must be familiar with excel file. We have to use this concept while designing Data Driven Framework as well.
Another point this is one of the most important questions in interviews as well and you should be having complete knowledge of it.
Read and Write excel files in Selenium
Step 1- Download apache poi jar file as below
Go to Official website of Apache POI and Click on the download section
http://poi.apache.org/download.html
Read/Write Excel file in Selenium
Now Click on the below mention link
Read/Write Excel file in Selenium
All jar files will come in zip files, Extract it and you will get final jar folder looks like this
Read/Write Excel file in Selenium
Add all jar files or below mention, jar files into Project.
Read/Write Excel file in Selenium
Step 2- How to add Jar files
Select project then Right click on project > Build path > Configure build path > Click on lib section > Add external jar
Precondition- Create a xlsx file and enter some data to read and save file at particular location.
Read Excel file using Apache POI
In below example, I am reading simple .xlsx file
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.Test;
public class ReadandWriteExcel {
public static void main(String []args){
try {
// Specify the path of file
File src=new File("filepath/excelsheetname.xlsx");
// load file
FileInputStream fis=new FileInputStream(src);
// Load workbook
XSSFWorkbook wb=new XSSFWorkbook(fis);
// Load sheet- Here we are loading first sheetonly
XSSFSheet sh1= wb.getSheetAt(0);
// getRow() specify which row we want to read.
// and getCell() specify which column to read.
// getStringCellValue() specify that we are reading String data.
System.out.println(sh1.getRow(0).getCell(0).getStringCellValue());
System.out.println(sh1.getRow(0).getCell(1).getStringCellValue());
System.out.println(sh1.getRow(1).getCell(0).getStringCellValue());
System.out.println(sh1.getRow(1).getCell(1).getStringCellValue());
System.out.println(sh1.getRow(2).getCell(0).getStringCellValue());
System.out.println(sh1.getRow(2).getCell(1).getStringCellValue());
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}
Write Excel file Selenium Webdriver
Now if you are familiar with reading excel then it is just a cup of tea now
In below example, I am writing .xlsx file
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.Test;
public class ReadandWriteExcel {
public static void main(String []args){
try {
// Specify the file path which you want to create or write
File src=new File("./testdata/test.xlsx");
// Load the file
FileInputStream fis=new FileInputStream(src);
// load the workbook
XSSFWorkbook wb=new XSSFWorkbook(fis);
// get the sheet which you want to modify or create
XSSFSheet sh1= wb.getSheetAt(0);
// getRow specify which row we want to read and getCell which column
System.out.println(sh1.getRow(0).getCell(0).getStringCellValue());
System.out.println(sh1.getRow(0).getCell(1).getStringCellValue());
System.out.println(sh1.getRow(1).getCell(0).getStringCellValue());
System.out.println(sh1.getRow(1).getCell(1).getStringCellValue());
System.out.println(sh1.getRow(2).getCell(0).getStringCellValue());
System.out.println(sh1.getRow(2).getCell(1).getStringCellValue());
// here createCell will create column
// and setCellvalue will set the value
sh1.getRow(0).createCell(2).setCellValue("2.41.0");
sh1.getRow(1).createCell(2).setCellValue("2.5");
sh1.getRow(2).createCell(2).setCellValue("2.39");
// here we need to specify where you want to save file
FileOutputStream fout=new FileOutputStream(new File("location of file/filename.xlsx"));
// finally write content
wb.write(fout);
// close the file
fout.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}
Some companies also use CSV files to store the data and some also use Databases to store the data so based on your requirement you can select any data source.
I hope you have enjoyed the article if yes then share with your friends and colleagues as well.
Please comment in below section if you are facing any issue. Thanks For visiting my blog keep in touch
Nicely Explained))))
HI ,Please may i know how to write data to excel which is read
from the webtable?
Hi Akshata, what is the issue here?
Hi,
getRow() and getCell() are non static methods that belongs to XSSFSheet class, as per java rule to invoke a non static method we need to create an object right but we are invoking getRow and get Cell() without creating object of XSSFSheet class.
Can you please clear this doubt of mine?? Thank you in advance.
Hi Disha,
This is because of method chaining, we get the reference of class/interface and with that reference, we can call the methods.
Good evening sir
help me for the below code
using any loop to read all data from excel file instead of this much of line
System.out.println(sh1.getRow(0).getCell(0).getStringCellValue());
System.out.println(sh1.getRow(0).getCell(1).getStringCellValue());
System.out.println(sh1.getRow(1).getCell(0).getStringCellValue());
System.out.println(sh1.getRow(1).getCell(1).getStringCellValue());
System.out.println(sh1.getRow(2).getCell(0).getStringCellValue());
System.out.println(sh1.getRow(2).getCell(1).getStringCellValue());
Hi Manmohan,
Use 2 for loops. Outer for loop goes with row number and inner for loop does with column number
Hi MUkesh,
Can you please share if you have article for reading the data from excel and passing it to my TestNG scripts. instead of hard coding the values like in case of username, password etc.
Thanks in advance
Leela
Hi Leela,
Apologies, I don’t have such post as it is custom requirement which is part of framework design. The framework could be of many ways because it depends business requirements. You can create some utility methods which can read required data from excel file and feed them into test class/methods.
This is the Utility method, Reading data from excel dynamically hope this helps you.
public String getRowAndCoulmn(int row, int column) {
File src = new File(filePath);
FileInputStream fis = new FileInputStream(src);
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet sheet = wb.getSheetAt(0);
String getRowAndColumn = sheet.getRow(row).getCell(column).getStringCellValue();
fis.close();
return getRowAndColumn;
}
Hi,
Before calling String getRowAndColumn = sheet.getRow(row).getCell(column).getStringCellValue(); statement, check for Cell type
Thank you for the Suggestion. I do go with that way, Once. But what is the main use of, checking the celltype.
Hi Ketam,
Based on Cell type only, you can call which method to read what type of data from the corresponding cell. Because excel cell type could of type date, numeral, general…etc
Hi Ketam,
Based on Celltype only, you can call which method to read what type data from corresponding cell. Because excel cell type could of type date, numeral, general…etc
Hi Mukesh,
I have test case in one test package, login page in another package, test utilities in another package.
What should I write under @dataProvider in my test case ? I am calling my login page method from my test case, and I am calling data provider method from login page.
In this case, what should I mention (@dataProvider = “?” ) in test case and @dataProvider (name = “?”) in my login page method?
I have 2 classes in utilities package.
1. ExcelUtils – Contains getRowCount, getColCount, getCellData methods
2. ExcelDataProvider – testData method to call ExcelUtils methods
Sorry, I am confused in what to write and where . I am using hybrid framework.
Please help
Hi Sai,
Check this link from official TestNG Documentation https://testng.org/doc/documentation-main.html#parameters-dataproviders
HEY MUKESH
CAN YOU MAKE A VIDEO OR ARTICLE ON, HOW TO READ AND WRITE THE EXCEL FILE
OF EVERY DATA TYPE IN EXCEL SHEET FOR EXAMPLE(DATE, INT, STRING, DOUBLE ETC).
YOU ARE JUST MAKING A READ EXCEL OF STRING TYPE, BUT I WANT TO KNOW HOW U CAN READ EVERY TYPE OF DATA FROM EXCEL…
PLEASE REVERT
NEED HELP
Hi Paras,
I’ll post it soon…:)
HI Mukesh,
I just want to know how to read row data and column data saparetley in excel file using selenium automation
Hi Swetha,
The way you traverse in Matrix using row following by column the same way you need to mention till which column need to address.
Hi Mukesh,
Can you please suggest me how to read data from excel sheet and write it in xml file.
Hi Chaitanya,
Since you already visited my blogpost, for xml part you can refer this link https://www.mkyong.com/java/how-to-create-xml-file-in-java-dom/
Hi,
Can you plz suggest me how to read data from ods file.I tried installing Apache poi ods toolkit but didn’t workout.
Hi Kiruthika,
Apache POI Toolkits are already outdated for Open Office/Libre Office file formats. You can try with jOpenDocument library but I am not sure fully about it.
Hi Mukesh, i am still getting error while writing in excel sheet. The file gets corrupted
Error
DOMSource cannot be processed: check that saxon8-dom.jar is on the classpath
Fail to save: an error occurs while saving the package : The part /docProps/core.xml failed to be saved in the stream with marshaller org.apache.poi.openxml4j.opc.internal.marshallers.ZipPackagePropertiesMarshaller@657c8ad9
Hi Bittu,
I hope that you are using Apache POI 3.17 version with Java 8.
Hi Mukesh,
I used apache poi 4.0 and 3.4 both, added in build path ..external jars
Thanks.
Hi Bittu,
Use only 3.7 only.
Hi Mukesh,
Can you tell me how to write a long data in excel sheet.
Hi Niketan,
Excel itself will except max of 15 digits numerical value which it can keep without changing to another format. Open excel file -> right click on any cell -> Format Cells -> select Number with Decimal places as ‘0’ -> Ok
hi,
Through excel file username is fetching as integer…for ex: if username is 123456789 in excel sheet but while running script it coming as “Cannot get a STRING value from a NUMERIC cell”
driver.findElement(By.xpath(“//input[@id=’ctl00_ContentPlaceHolder1_UserName’]”)).sendKeys(cell.getStringCellValue());
Please give solution for this
Hi Sureshh,
You have to use cell.getNumeriellValue because Apache POI reads always data as per cell type. Once you get double value, convert it to String type like
String s = String.valueOf(double) and proceed with sendKeys
Hi Mukesh,
in excel integer value : 917232345267
WebElement str=driver.findElement(By.xpath(“//input[@id=’ctl00_ContentPlaceHolder1_UserName’]”));
str.sendKeys(cell.getNumericCellValue()+”(Integer)”);
after executing getting value as 9.17232345267E1
pls suggest
Hi Suresh,
Use this new java.text.DecimalFormat(“0”).format(cell.getNumericCellValue()) where cell is of type Cell
hi i m having excel sheet with three rows,
1st and 2nd with incorrect user name and password
3rd with correct username n password
want to use this excel data into web application trying all the data with result pass and failed updated excl
Hi Deepak,
This link will help you.
Hi mukesh..
Why you use try catch in every program
Hi Suraj,
Try Catch block is used to handle exception as situation may come in future if not present, where Exception might be thrown which you need to handle to avoid unnecessary termination of code.
Through excel file username is fetching as decimal…for ex: if username is 1234 in excel sheet but while running script it coming as 1234.0
Please give solution for this
Hi Nehal,
Apache POI reads numerical data as double so in your case convert it into int like
double data = 1234.0
int value = (int)data this will make it to 1234 so you will get rid of decimal part
later on convert this into String(if it is required)
Hi Mukesh,
I need to search for a particular string in entire excel sheet and get the value from other column from the same row. I have 5-6 rows with that particular string. And compare the data in another sheet which is also having large data and matched with multiple rows. How an I do this? Can you please help?
Hi Sree,
If you have big amount of data into excel then it is nor recommendable to use excel because it slows whole process. But still you proceed with excel file and you can observe how much considerable time it takes to complete your verification step. Mean while you can also try Fillo library. Because in future if your data moves into database then this library will help you alot.
hi
i am trying to write a excel file. but it does not write if the file is empty means dont have any data. it returns null.if it has data then it writes. whats the reason.
Hi Sukhvir,
What exception message are you getting ?
Hi Sukhvir,
You need to write if condition for that, means if your row or column is empty then perform your respective actions.
Hi Mukesh,
I’m trying to read the data using jexcel by following one of your video in youtube. But i’m getting the following error in the console. ‘
Command format: Demo [-unicode] [-csv] [-hide] excelfile
Demo -xml [-format] excelfile
Demo -readwrite|-rw excelfile output
Demo -biffdump | -bd | -wa | -write | -formulas | -features | -escher | -escherdg excelfile
Demo -ps excelfile [property] [output]
Demo -version | -logtest | -h | -help
Please help me in solving the issue
Hi Naveen, Please use Apache POI for reading excel now.
Hi Mukesh, please help me to import the links on excel sheet. actually, I am working on the program where I get all links on my console of selenium but I want to write these links import on the excel sheet.
Hi Rajnish,
As you already mentioned that you are getting all links on console so you can redirect same to write into excel file. Only thing which you need to ensure is, keep on incrementing row number after each iteration.
Hi Mukesh,
Is there a way of renaming the same Excel file after editing?
Hi Ghouse,
Java provides built in method for renaming file from file explorer. Please check this link https://www.journaldev.com/836/java-rename-file-jave-move-file
Hi Ghouse,
Java provides built in method for renaming file from file explorer
Hi I am trying to load properties file in selenium like below:
prop = new Properties();
File src=new File(“C:\\Users\\Dell\\eclipse-workspace\\ProjectTestPack\\src\\main\\java\\com\\auto\\qa\\config”);
FileInputStream ip = new FileInputStream(src);
prop.load(ip);
Getting below error please help on this
java.io.FileNotFoundException: C:\Users\Dell\eclipse-workspace\ProjectTestPack\src\main\java\com\auto\qa\config (Access is denied
Hi Navneet,
Please provide full path of config.properties file like C:\\Users\\Dell\\eclipse-workspace\\ProjectTestPack\\src\\main\\java\\com\\auto\\qa\\config.properties
Appreciate your effort.Clear explanation.Easy to follow.Thanks
Thanks Nithya..:)
Hello Mukesh,
You have shared pretty much important stuff.
I have question while reading data from excel sheet. How to handle blank cells for xSSF workbook. I am searching a alot for this. But cudnt get anything helpful.
Thanks in advance.
Hi Nikhil,
Please check this link http://poi.apache.org/spreadsheet/quick-guide.html#Iterator
Hi Mukesh,
Do you have any materials or videos available to write the code for to fetch the web table data?
Hi Dinesh,
Web Table html design differs in every web applications. First of all you need to find out the total number of rows available in web table. Then based on tr iterate through td. Finally do getText() or getAttribute(“innerHTML”) to get actual content from web table.
Ok, Mukesh. I will try and let you know and thanks for your immediate responses.
Sure…!!!
Hi Mukesh
Very useful and informative content you share in Learn-automation portal. One thing I am facing problem that i want to fetch the xpath and text values from UI and write it to in excel sheet. Please suggest how should i do this, and if you may provide framework than it will help me.
Thanks a lot.
Hi Narayan,
Xpath for you can find using firepath plugin in firefox and to get text values in those fields, you can make use of getAttribute() where you can provide property name against which required value exists.
The User name and password for login is stored in excel sheet in two columns. I want to read the username/password from the excel sheet. If it’s a valid user name/pwd I just wanted to Write “Pass” in new column else I need to write it as “Fail” . Write pass and fail based on succeful login into excel file not hardcoding values. How to do it?
Hi Ahmed,
This is what you are looking https://vistasadprojects.com/mukeshotwani-blogs-v2/data-driven-framework-in-selenium-webdriver/
Thank you Mukesh for your kind pointer.. I have watched that video .. What I am looking for is once first UserName and Password combination is validated successfully in excel file in the corresponding column it should show PASS else show Failed…
Yes for that you need to write logic 😉
Hi Mukesh,
First, I would like to say thanks for doing this great video presentation and these videos will be very useful for beginners like me.
My Q is: I want to write the web table data into an excel sheet as it is on the web page.Could you please let me know how to proceed on this?
Hi Dinesh,
For reading data from WebTable, you need to use two loops(one nested inside another) in which outer iterates row and inner one for column. You can use same loop for iterating through excel row followed by column number.
Hi Mukesh, nice tutorial. Is there a way to directly read a column value from excel without iterating through all the columns or hard coding the column index in getcell().
Hi Daisy,
Reading a cell value from an excel file is just accessing a cell from a two dimensional matrix. In 2D matrix, you can iterate based on row number followed by column number. Similarly, if you want to access any cell then obviously row number followed by column number is must. One more thing, without creating workbook object, it is not possible to access any cell inside excel file and this is what Apache POI defined.
i m fetching the value from the excel but it comes into the float.and i want value in int. so i used getnumericalvalue(). but its gives me an error. tell me how to convert it.
Try this https://vistasadprojects.com/mukeshotwani-blogs-v2/read-numeric-data-excel-using-apache-poi-selenium-webdriver/
Hi Mukesh,
with this blog and the videos you have created, i have learned much about selenium .
I need a help for the below code:
if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
String str = NumberToTextConverter.toText(cell.getNumericCellValue())
}
Error is:
1.cell.getCellType() : it shows as deprecated.
how to fix this?
2.Cell.CELL_TYPE_NUMERIC)
Not found Exception displayed
please help me to fix these two!!
Regards
Lavanya
Hi Lavanya,
this will help https://vistasadprojects.com/mukeshotwani-blogs-v2/read-numeric-data-excel-using-apache-poi-selenium-webdriver/
Hi Mukesh
My code for reading and writing on excel works fine with try and catch exception included but my code shows error without it.
Can you help me to figure out the reason behind it.
Hi Anjana,
It is because it will throw checked exception so if we dont handle then we can’t run our scripts.
Hi,This site is very useful,
i need to read multiple data from excel sheet(apache poi) ,m unable to write.ex:
login application->open some link->fill employee form->close->again login with other employee->open link->fill form->aftre filling form ->again fill other details
Hi Bhavani,
You have to follow below link for above task
https://vistasadprojects.com/mukeshotwani-blogs-v2/data-driven-framework-in-selenium-webdriver/
hi mukesh,
1) is this possible to automate any type of captcha??
2) Is this possible to Read and write excel simentenously??
Hi Harshal,
1- No
2- No, both can be done individually.
Very Nice tutorial Mukesh sir
I used CreateRow instead of getRow, now its working fine.
Cool
Hi Mukesh-
I already figured out the solution. Thanks.
cheers
Hi Mukes h
How can automate captcha??
No Bhushan
How to refresh an Excel file Using Apache POI
It refresh excel automatically
hi mukesh,
actually, I am a manual tester having 2 years of experience. Now, I am learning selenium from your videos and literally saying, these are fantastic.
But I have a question:
I am not good in fetching data from excel through apache poi. can u please give mail a complete video of how to read multiple sets of data for a test case from excel sheet.
Hi Akash,
You can follow below link for advance data fetching https://vistasadprojects.com/mukeshotwani-blogs-v2/data-driven-framework-in-selenium-webdriver/
When i am reading data from excel. I am getting this error message. for loop was not running second time.
Exception in thread “main” java.lang.ArrayIndexOutOfBoundsException: 2
at jxl.read.biff.SheetImpl.getCell(SheetImpl.java:318)
at testpackage.excelwebrerad.main(excelwebrerad.java:40)
Hi Satish,
It seems you have used packages of JXL jars too. Make sure only POI jars added and then run the script.
Dear Mukesh,
i am looking for a way to get the results from a test in selenium ide out to an excel sheet. i found your video instructions on Jxcl and Apache POI but am missing the link between the excel issue and the results i got from the echo command in the selenium ide shell.
This is the data i am looking for to export:
[info] Playing test case
[info] Executing: |echo | ${Nettopreis} | |
[info] echo: € 817,68*
[info] Executing: |echo | ${EAN} | |
[info] echo: 4035694005136
[info] Executing: |echo | ${Versand} | |
[info] echo: Frei Haus
[info] Executing: |echo | ${Lieferzeit} | |
[info] echo: 38 Tage
what is the recommended procedure? thank you for help
regards
Björn
Hi Bjoern,
I am not good in Selenium IDE so not sure on this 🙁 Just a quick question why you are still trying with Selenium IDE? You can switch back to webdriver and you can play with all features.
Hi Mukesh,
thanks for answering quickly. I liked the easy way to use the selenium ide, it is very visual and you can see the steps/performance at once.
you mean using webdriver in java? with your videos i now know to read and write excel sheets in java, but i am not clear about how the connection to selenium works. do you have a hint, pls?
Yes sure I have shared some videos with you. Kindly check ur email 🙂 If you did not get any email then let me know.
thank you Mukesh, received.
Cheers
Got an error message: Unable to connect to host 127.0.0.1 on port 7055 after 45000 ms
I am using Eclipse Neon Release (4.6.0).
Hi Bjoern,
Here is the solution https://vistasadprojects.com/mukeshotwani-blogs-v2/use-firefox-selenium-using-geckodriver-selenium-3/
Thanks again, unfirtunately i still have the same error:
org.openqa.selenium.firefox.NotConnectedException: Unable to connect to host 127.0.0.1 on port 7055 after 45000 ms
it further states:
Firefox console output:
— see http://www.mozilla.com/en-US/blocklist/
LdrLoadDll: Blocking load of ‘pmls.dll’ — see http://www.mozilla.com/en-US/blocklist/
thank you for assisting.
It seems FIrefox port is blocking. Can you try the same in Chrome and IE
https://vistasadprojects.com/mukeshotwani-blogs-v2/launch-chrome-browser-using-selenium-webdriver/
That was the solution! Thank you! ist this a temporary issue of FF? what do you think?
Yes I think so. I am using 46 version of FF and its working smoothly.
Yes, i have read that in other forums, too. Consider to downgrade, is that recommendable?
other issue: i was thinking about exporting my test from Selenium IDE to Eclipse and then append the code with functions to print data to an excel sheet. might that work?
Selenium IDE offers different export options to java. which one should i use?
Java/JUnit4/WebDriver
Java/JUnit4/WebDriver Backend
Java/JUnit4/Remote Control
Java/JUnit3/Remote Control
Java/TestNG/WebDriver
Java/TestNG/Remote Control
Thanks for helping.
Select Java/TestNG/WebDriver only for converting.
Hello, again,
i chose the first option called “JUnit4/WebDriver”, imported and opened it with Eclipse and run. Produced the result:
“No test found with test runner JUnit4”
am i missing the point here? how do i get the results?
k.r. Björn
It seems you have used JUNIT, Kindly select TestNG and then run the test.
Note- Make sure you have installed TestNG in your eclipse if not then use below link to install https://vistasadprojects.com/mukeshotwani-blogs-v2/how-to-install-testng-in-eclipse/
what a nice discussion between Mukesh and his reader,,,, Hats off !!!
Thanks Gaurav
Hi Mukesh ,
I am trying to to fetch the single web Element and store it into Excel file
so far i got success up to print the output on the console but i need to write into the Excel file.
any suggestions will be great help
Yes Ajit it quite easy. What issues you are getting?
How to fetch multiple data types from excel .For ex. In your blog it is mentioned as “getRow(0).getCell(0).getStringCellValue()); ” .If in the excel there are string,Int ,DOuble etc how can we fetch it in for loop ?
Hi Rahul,
to get numeric value use getNumericCellvalue method
Hi Mukesh. It’s my first time in reading and writing excel file in Selenium. So for practice purposes, I just copied your example code. I have already setup and installed the POI jars. I wonder why the code below returned null. Thanks
package files;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class WriteExcel {
public static void main(String[] args) {
try{
File excel = new File(// excel path in (.xlsx) file format));
FileInputStream fis = new FileInputStream(excel);
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet sh = wb.getSheetAt(0);
sh.getRow(1).createCell(0).setCellValue(“user_101”);
FileOutputStream fos = new FileOutputStream(excel);
wb.write(fos);
wb.close();
}
catch(Exception e){
System.out.println(e.getMessage());
}
}
}
Hi Chino,
Have you changed the excel file path in below link File excel = new File(// excel path in (.xlsx) file format));
Take a look at this one, surrounded it with TRY..CATCH block.
a. The e.getMessage returns null
b. Should we close the workbook, or the fileoutputstream?
package files;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class WriteExcel {
public static void main(String[] args) {
try{
File excel = new File(“D:\\NINZO\\RC_automation_tests\\GigMeOnline_PageFactory\\Gigme_UserCredentials.xlsx”);
FileInputStream fis = new FileInputStream(excel);
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet sh = wb.getSheetAt(0);
sh.getRow(1).createCell(0).setCellValue(“user_101”);
FileOutputStream fos = new FileOutputStream(excel);
wb.write(fos);
fos.close();
}
catch(Exception e){
System.out.println(e.getMessage());
}
}
}
Hi,
make below changes
old code fos.close();
new code wb.close();
Hello ,
Can we Read/Write data from open office document having extension like “.ods”.
Please advise how to do that.
Thanks for good tutorial.
yes anoop we can do that. You can use HSSFWOrkbook class for this.
11.Suppose you have 3000 test data, can you automate using Excel sheet.How ? are there any drawbacks
I used excel and I don’t find any drawbacks. You can create some reusable methods which will accept Sheet name and row,column as parameter and will return the date,.
Hi Mukesh, How can we insert a column at the start in the existing excel sheet?
Hi Jayesh,
You can use createCell to create column in excel
how to get all the values from excel sheets and all types(string,int…) of values
you can create custom method which will do this. COde will be something like this.
if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
String str = NumberToTextConverter.toText(cell.getNumericCellValue())
}
Hi Mukesh,
I have a query.
I need to fetch the data from an excel as dump. but i wanted to ignore/skip one specific column. How to do this?
Hi Shilpa,
I did not understand by work dump 🙁 What is the exact requirement.
Dear Mukesh,
i was trying to write comments on “For better Reporting Capture Screenshot Selenium Webdriver” URL “https://vistasadprojects.com/mukeshotwani-blogs-v2/how-to-capture-screenshot-in-selenium-webdriver/”
i am facing the problem on FileUtils class is not showing me and ever its methods, pic 1 and if i am trying to selecting FileUtils (org.apachecommons.io) its writing script pic 2 “org.apache.commons.io.FileUtils” then showing all methods but not taking screen shots.
i drooped an email to you also, thanks in advance.
Hi Mukesh,
I tried to read data from Excel sheet, which is working fine.
While I tried to get Numeric data like phone number and use it in sendKeys, I am getting “Exception in thread “main” java.lang.IllegalStateException: Cannot get a text value from a numeric cell” error.
Please help me in resolving the issue.
Thanks.
Hi Satyam,
Use getNumericCellvalue to read data in case digit.
Sample code
double data=rest-of-the-code.getNumericCellvalue();
String phoneNumber=Double.toString(data);
hi mukesh want to know how to read from excel and the data in excel is a user input and perform operation and print the output in that same spreadsheet
Hi Harsh,
Have u tried the code from u your side? What issue you are facing ?
hii mukesh…thanks for sharing such a nice tutorial…
i m getting error while writing in excel file its saying Can’t open the specified file: ‘C:\sunita_java\ta.xlsx
and when i try to open my excel file its giving file is corrupt or file format is incorrect.
Hi Sunita,
sorry for late reply. is this fixed?
hi, i want to know How can i parameter login info & screenshot taken in POM
Hi,
Below link will help you
Hi Mukesh, I created excel sheet using Selenium webdriver script, but when I try to attach it, it is saying that the format is incorrect. My logic is to create a xlsx using code, do the read/write operation and later delete it. So, each time, I want to create a new xlsx file. Do you have any soultion for this ?
Hi Geetha,
Check the version and check manually are you able to open the file
Nice content Mukesh, I can read/write excel sheets using these tutorials. Great work. Keep it up..
Hi Vijay,
Cheers. Please explore other topics as well.
Actually i’m getting an error “Error: Could not find or load main class ReadExccel.ReadExcelData”, although i have written the code same as your’s…there is no compilation issue.
Hi Deepak,
Please visit https://www.youtube.com/watch?v=sbBdj4zIMqY
I like your video lectures. These are explained in such a way that the tutorials become very easy to understand.
Thanks Enamul, Glad to know that you Liked it 🙂 keep visiting.
Hi Mukesh,
I have few doubt. Can we open excel file and write whatever we want?
Through Selenium webdriver.
Thanks,
Malaisamy J
Hi Malaisamy J,
You can do in diff sheet.
Hi Mukesh,
As i seen you created a library for read excel and than explained how to use it in any program but same i am not getting for write excel. Can you please provide any reference.
Hi Ranu,
Have you tried the code from your end if yes then please send me your code I will correct it and will revert back.
hii can u please give me code to validate the login functionality with different usernames and passwords from xlsx file
and it should store the result in the same file
Hi Sai,
THis is called DDF and I have covered the same in https://www.youtube.com/watch?v=ORscNOhgW4w
Writing result in the same file will upload by this weekend.
Hello Mukesh
when i run the result is not passed to the excel sheet
Hi Mahesh,
Sorry I did not get your doubt. Can you explain in detail?
Script to copy all sheets from a excel to single sheet in new excel file
package ExcelAutomation;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.testng.annotations.Test;
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;
import testBase.PhyWebTestBase;
import utility.PhyWebUtil;
public class CopyExcel extends PhyWebTestBase
{
public CopyExcel() throws Exception
{
super();
}
@Test
//String sheetName1=”1 New and Changed Brands,Form or Strength and Manufacturers”;
public void CopyFile() throws Exception
{
try
{
String sheetName1=”1 New and Changed Brands,Form or Strength and Manufacturers”;
String srcFilePath = “/Users/raghavendra/Automation/Scripts/4 Weekly Database Changes/Old Databases/885/2015_09_29_Changed_Data_For_Release_885.xlsx”;
String destFilePath = “/Users/raghavendra/Automation/Scripts/4 Weekly Database Changes/Old Databases/885/1 New and Changed Brands,Form or Strength and Manufacturers1.xls”;
String sheetName,cellData = null;
int a,row = 0,row1=0,colCount = 0,newRowCount;
double cellData1 = 0;
XSSFSheet sheet1 = null;
XSSFWorkbook workbook1 = null;
XSSFRow rowData1 = null;
FileInputStream inputStream = new FileInputStream(new File(srcFilePath));
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
FileOutputStream destFile;
destFile=new FileOutputStream(new File(destFilePath));
workbook1 = new XSSFWorkbook();
for(sheetnum=0;sheetnum<=6;sheetnum++)
{
XSSFSheet sheet=workbook.getSheetAt(sheetnum);
sheetName=sheet.getSheetName();
if(sheetName.equals("New Brands") || sheetName.equals("Changed Brands") || sheetName.equals("New Form Strength") || sheetName.equals("Changed Form Strength") || sheetName.equals("New Manufacturers") || sheetName.equals("Changed Manufacturers"))
{
sheet=PhyWebUtil.getSheetFromExcel(srcFilePath, sheetnum);
rowCount=sheet.getLastRowNum();
rowCount=rowCount+1;
newRowCount = row+rowCount;
if(sheetName.equals("New Brands"))
sheet1 = workbook1.createSheet(sheetName1);
if(sheetName.equals("New Brands")|| sheetName.equals("Changed Brands") || sheetName.equals("New Form Strength") || sheetName.equals("Changed Form Strength"))
colCount=5;
else if(sheetName.equals("New Manufacturers") || sheetName.equals("Changed Manufacturers"))
colCount=3;
for(row = row1;row<=newRowCount-1;row++)
{
rowData1 = sheet1.createRow(row1);
row=0;
for(col=0;col1 && row==row1)
rowData1.createCell(row+1).setCellValue(sheetName);
else
rowData1.createCell(col+1).setCellValue(cellData);
}
if(row==newRowCount)
a=1;
}
}
}
row1=row+1;
}//End for1
inputStream.close();
workbook1.write(destFile);
}//try end
catch (Exception e)
{
System.out.println(e.getMessage());
}
}
}
What is the issue in this?
I am trying to to cell and getting error. can I g help here please.
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FilterInputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
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;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
public class excelPOIcode
{
public static FileInputStream excelFile;
public static XSSFWorkbook wb;
public static XSSFSheet sheet;
public static XSSFRow row;
public static XSSFCell cell;
public static void main(String[] args) throws IOException
{
String value = getCellData(3,1);
System.out.println(value);
String value1 = getCellData(4,1);
System.out.println(value1);
String value2 = getCellData(4,1);
String writeData = setCellData(“Paryushan”,1,1);
System.out.println(writeData);
}
// creating common method to call it pass row column values and use it thru’out the project
public static String getCellData(int rowNum, int col) throws IOException
{
excelFile = new FileInputStream(“C:\Users\Dhruti\Desktop\autotest suite\Homedepotsearch.xlsx”);
wb = new XSSFWorkbook(excelFile);
sheet = wb.getSheet(“search”);
row = sheet.getRow(rowNum);
cell = row.getCell(col);
return cell.getStringCellValue();
}
public static String setCellData(String colData, int rowNum1, int col1) throws IOException
{
excelFile = new FileInputStream(“C:\Users\Dhruti\Desktop\autotest suite\Homedepotsearch.xlsx”);
wb = new XSSFWorkbook(excelFile);
sheet = wb.getSheet(“type”);
row = sheet.getRow(rowNum1);
cell = row.getCell(col1);
//String tcell = cell.setCellValue(colData);
return setCellData(colData, rowNum1, col1);
}
}
Hi Dhruti,
I have created 2 video’s tutorials for this.
Please watch and let me know if you still finding some issues while reading and writing excel.
Read Excel
Write Excel