Automation

Selenium WebDriver tutorial Step by Step

You are here: Home / Advance Selenium / How to Read and Write excel files in Selenium using Apache POI

How to Read and Write excel files in Selenium using Apache POI

by 160 Comments

Excel-Reading in Selenium

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 and Write excel files in Selenium
Read/Write Excel file in Selenium

 

Now Click on the below mention link

Read and Write excel files in Selenium
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 files in Selenium
Read/Write Excel file in Selenium

Add all jar files or below mention, jar files into Project.

 

Read/Write excel files in Selenium
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

Reader Interactions

Comments

  3. Disha says

    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.

    Reply

  4. Manmohan says

    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());

    Reply

  5. Leela says

    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

    Reply

    • Mukesh Otwani says

      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.

      Reply

      • Ketam Srinivas says

        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;

        }

        Reply

          • Ketam srinvias says

            Thank you for the Suggestion. I do go with that way, Once. But what is the main use of, checking the celltype.

          • Mukesh Otwani says

            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

          • Mukesh Otwani says

            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

  6. Sai says

    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

    Reply

  7. XBlade says

    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

    Reply

  8. swetha says

    HI Mukesh,

    I just want to know how to read row data and column data saparetley in excel file using selenium automation

    Reply

  10. Kiruthika says

    Hi,
    Can you plz suggest me how to read data from ods file.I tried installing Apache poi ods toolkit but didn’t workout.

    Reply

    • Mukesh Otwani says

      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.

      Reply

  11. Bittu says

    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

    Reply

    • Mukesh Otwani says

      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

      Reply

  13. suresh says

    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

    Reply

    • Mukesh Otwani says

      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

      Reply

      • suresh says

        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

        Reply

  14. deepak gupta says

    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

    Reply

    • Mukesh Otwani says

      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.

      Reply

  16. Nehal says

    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

    Reply

    • Mukesh Otwani says

      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)

      Reply

  17. sree says

    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?

    Reply

    • Mukesh Otwani says

      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.

      Reply

  18. sukhvir says

    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.

    Reply

  19. Naveen M says

    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

    Reply

  20. Rajnish says

    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.

    Reply

    • Mukesh Otwani says

      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.

      Reply

  22. Navneet says

    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

    Reply

    • Mukesh Otwani says

      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

      Reply

  24. Nikhil says

    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.

    Reply

  25. Dinesh Guptha Sivaguru says

    Hi Mukesh,

    Do you have any materials or videos available to write the code for to fetch the web table data?

    Reply

  26. Narayan Boolchandani says

    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.

    Reply

    • Mukesh Otwani says

      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.

      Reply

  27. Waheed Ahmed says

    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?

    Reply

  28. Dinesh Guptha Sivaguru says

    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?

    Reply

    • Mukesh Otwani says

      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.

      Reply

  29. Daisy G says

    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().

    Reply

    • Mukesh Otwani says

      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.

      Reply

  30. khushali says

    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.

    Reply

  31. Lavanya says

    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

    Reply

  32. Anjana says

    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.

    Reply

  33. bhavani says

    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

    Reply

  40. akash gupta says

    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.

    Reply

  41. satish says

    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)

    Reply

  42. Bjoern says

    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

    Reply

  43. ajit says

    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

    Reply

  44. Rahul says

    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 ?

    Reply

  45. Chino says

    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());
    }

    }

    }

    Reply

      • Chino says

        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());
        }

        }

        }

        Reply

  46. Anoop says

    Hello ,

    Can we Read/Write data from open office document having extension like “.ods”.
    Please advise how to do that.

    Thanks for good tutorial.

    Reply

    • Mukesh Otwani says

      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())
      }

      Reply

  50. shil says

    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?

    Reply

  51. zainkk says

    Dear Mukesh,
    i was trying to write comments on “For better Reporting Capture Screenshot Selenium Webdriver” URL “http://learn-automation.com/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.

    Reply

  52. Satyam Kumar says

    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.

    Reply

    • Mukesh Otwani says

      Hi Satyam,

      Use getNumericCellvalue to read data in case digit.

      Sample code

      double data=rest-of-the-code.getNumericCellvalue();

      String phoneNumber=Double.toString(data);

      Reply

  53. harsh says

    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

    Reply

  54. sunita says

    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.

    Reply

  56. Deepak says

    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.

    Reply

  57. Enamul Hoque says

    I like your video lectures. These are explained in such a way that the tutorials become very easy to understand.

    Reply

  58. Ranu says

    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.

    Reply

  59. sai says

    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

    Reply

  61. Raghavendra says

    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());
    }
    }
    }

    Reply

  62. Dhruti says

    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);

    }
    }

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.