CODEWITHSUNDEEP
javaselenium-webdriverautomation
Harpreet
Harpreet

Reputation: 27

Read data from excel template and write to the same file

I am able to write automated data to an excel file. However, I am requesting help on how to read an excel template with a list of names in there, where each name carries out the corresponding method and thereon carries out writing the values of that in the row.

For example, here is the template:

Excel Template: Excel Template

Additionally, here is the method corresponding to the data in the excel file:

public void AbbeyNational(String AbbeyNationalURL, String AN_AccCookiesButton, String AN_MortgageTabButton, String AN_ExistingCustomerButton, String AN_FollowRateButton, String AN_SVRButton, String AN_RateFld) throws InterruptedException, IOException{
    driver.get(AbbeyNationalURL);
    driver.findElement(By.xpath(AN_AccCookiesButton)).click();
    driver.findElement(By.linkText(AN_MortgageTabButton)).click();
    driver.findElement(By.xpath(AN_ExistingCustomerButton)).click();
    driver.findElement(By.xpath(AN_FollowRateButton)).click();
    driver.findElement(By.xpath(AN_SVRButton)).click();

    String a = driver.findElement(By.cssSelector(AN_RateFld)).getText();
    AN_Rate = a.substring(54,58);
    T1 = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(Calendar.getInstance().getTime());
    data.put("2", new Object[]{"Abbey National", AN_Rate, T1});
    System.out.println(AN_Rate);
    
    TakesScreenshot scrShot = (TakesScreenshot)driver;
    File srcfile = scrShot.getScreenshotAs(OutputType.FILE);
    File DestFile = new File("C:\\Users\\harpreet dugh\\Desktop\\Screenshots\\AbbeyNational.png");
    FileUtils.copyFile(srcfile, DestFile);
}

And finally, here is the desired output:

Desired Output: Desired Output

To clarify, as seen in the output, I would like to read the lender 'Abbey National' from the excel file, carry out the corresponding method and write the rate and time to that lender in the excel file.

Could anyone help me with this please?

Upvotes: 1

Views: 946

Answers (1)

Subburaj
Subburaj

Reputation: 2334

I would suggest to use the Apache POI Libraries to perform read and write operation in excel.

You need to HSSF for the .xls extension file and XSSF for the .xlsx file extenstion.

I have given the sample ExcelDataConfig file.Please create a separate class as ExcelDataConfig.

You can simply call the required methods by creating an ExcelDataConfig object.

To Read and Write in your Code :

ExcelDataConfig config=new ExcelDataConfig(Excel Path);

//Arguments - Sheet Index, Row Number , column Number(All the index starts from 0.So, give the no of is correctly
config.getData(0,1,1)

// To write the Rate Details in Excel
config.writeData(0,1,2);

// To write the Time Details in Excel
config.writeData(0,1,3);

ExcelDataConfig class:

public class ExcelDataConfig {

    HSSFWorkbook wb;
    HSSFSheet sheet;
    File src;

    public ExcelDataConfig(String excelPath)
    {

        src=new File(excelPath);
        try {

            FileInputStream fis=new FileInputStream(src);
            wb=new HSSFWorkbook(fis);

        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

public String getData(int sheetIndex,int row,int column){

        sheet=wb.getSheetAt(sheetIndex);
        String data="";//sheet.getRow(row).getCell(column).getStringCellValue();
        Row r=sheet.getRow(row);
        Cell c=r.getCell(column, Row.RETURN_BLANK_AS_NULL);

        if(c==null){
            data="";
        }
        else{
            if(r.getCell(column).getCellType()==HSSFCell.CELL_TYPE_STRING){
                data=r.getCell(column).getStringCellValue();                
            }else if (r.getCell(column).getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
                int intData=(int) r.getCell(column).getNumericCellValue();
                data=Integer.toString(intData);
            }
        }
        return data;

    }

public void writeData(int sheetIndex,int row,int column,String content,String excelPath ) throws IOException{

        sheet=wb.getSheetAt(sheetIndex);

        sheet.getRow(row).getCell(column).setCellValue(content);
        FileOutputStream fout=new FileOutputStream(src);
        wb.write(fout);

        //wb.close();

    }

}

Upvotes: 1

Related Questions