Java 在 Selenium WebDriver 中读写 excel

Read and write excel in Selenium WebDriver

提问by Kalai

I'm new to Selenium WebDriver and still learning. I just want to read the data from the excel and write the data either in a same excel sheet or different excel sheet.

我是 Selenium WebDriver 的新手，仍在学习。我只想从excel中读取数据并将数据写入同一个excel表或不同的excel表中。

User name and passwor 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"

用于登录的用户名和密码以两列存储在 Excel 表中。我想从 Excel 表中读取用户名/密码。如果它是一个有效的用户名/密码，我只想在新列中写“通过”，否则我需要将其写为“失败”

My code works successfully for reading data from excel sheet. However i'm stuck with writing data in excel sheet.

我的代码可以成功地从 Excel 工作表中读取数据。但是我坚持在excel表中写入数据。

Thanks for reading and coming forward to clear my doubt.

感谢您阅读并挺身而出以消除我的疑虑。

回答by KDP

Consider using Apache POI for reading and writing the excel. May be you can write a simple Utilclass which reads and writes excel .

考虑使用 Apache POI 来读写 excel。也许你可以写一个简单的Util类来读写 excel 。

1. To get the user id and password the util class should have a method to read excel and pass it to your selenium script.

2. Once the credentials are validated util class should have a method to write pass/fail to your excel.

1. 要获取用户 ID 和密码，util 类应该有一个方法来读取 excel 并将其传递给您的 selenium 脚本。

2. 一旦凭证被验证，util 类应该有一个方法来将通过/失败写入您的 excel。

Sample code to create row and write to a cell using POI

使用 POI 创建行并写入单元格的示例代码

    Sheet sheet = wb.createSheet("Selenium Results");
    Row titleRow = sheet.createRow(0)
    row = sheet.createRow(11);
    cell = row.createCell(2);
    cell.setCellValue("Total cost of loan");

Refer this Example

参考这个例子

In your case you may just need to iterate the existing excel then read/write.

在您的情况下，您可能只需要迭代现有的 excel 然后读/写。

回答by Stephen George

The following Selenium Java code using Apache POI should work:

以下使用 Apache POI 的 Selenium Java 代码应该可以工作：

public String readDataFromExcel(int rowcount,int columncount,String filepath,String Sheetname )
    {
        String data=null;
        try
        {
            FileInputStream input= new FileInputStream(filepath);
            XSSFWorkbook wb=new XSSFWorkbook(input);
            XSSFSheet sh=wb.getSheet(Sheetname);
            XSSFRow row=sh.getRow(rowcount);
            row.getCell(columncount).toString();
        }
        catch(Exception e)
        {
            System.out.println(e);
        }
        return data;
     }

public void writeDataFromExcel(int rowcount,int columncount,String filepath,String Sheetname,String value)
{
    try
    {
        FileInputStream input=new FileInputStream(filepath);
        XSSFWorkbook wb=new XSSFWorkbook(input);
        XSSFSheet sh=wb.getSheet(Sheetname);
        XSSFRow row=sh.getRow(rowcount);
        FileOutputStream webdata=new FileOutputStream(filepath);
        row.createCell(columncount).setCellValue(value);
        wb.write(webdata);

    }
    catch(Exception e)
    {

    }
}

回答by Pranali Patil

public class Xls_Reader 
{
    public String path="";
    private Workbook workbook=null;
    private Sheet sheet=null;

    public Xls_Reader(String filePath)
    {
        path=filePath;
        workbook=getWorkBook(path); 
        sheet=workbook.getSheetAt(0);
    }

    /**
     * This function returns workbook object of excel file
     * @param path is location of file in file System
     * @return object of Type XSSFWorkbook
     */
    public Workbook getWorkBook(String path)
    {
        Workbook workbook=null;
        File file=new File(path);
        if(file.exists())
        {
            try 
            {
                FileInputStream input= new FileInputStream(path);
                String extension=FilenameUtils.getExtension(path);
                workbook= extension.equalsIgnoreCase("xls")?new HSSFWorkbook(input):new XSSFWorkbook(input);
                input.close();
            } 
            catch (IOException e) 
            {
                throw new TARuntimeException(String.format("Problem While opening the file(%s)", path), e);
            }
        }
        else
        {
            throw new NotFoundException(String.format("File(%s) is not exist..!!", path));
        }

        return workbook;
    }

    /**
     * Depending upon sheet name it returns the sheet object
     * @param sheetName is name of sheet
     * @return if sheet exist returns XSSFSheet object else returns null. 
     */
    public Sheet getSheet(String sheetName)
    {
        if(workbook.getSheetName(workbook.getSheetIndex(sheet)).equals(sheetName))
        {
            return sheet;
        }
        if(sheetName==null || sheetName.isEmpty())
        {

        }
        else
        {
            int index=workbook.getSheetIndex(sheetName);
            if(index==-1)
            {
                throw new NotFoundException(String.format("Sheet(%s) is not found in Excel Workbook(%s)",sheetName,path));
            }
            else
            {
                sheet=workbook.getSheetAt(index);
            }
        }
        return sheet;
    }

    /**
     * Depending upon index it returns the sheet object
     * @param index - is index of sheet
     * @return if sheet exist returns XSSFSheet object else returns null. 
     */
    public Sheet getSheetAt(int index)
    {
        if(index<0)
        {
            throw new NotFoundException(String.format("Sheet is not found @ index = %s", index));
        }
        else
        {
            sheet=workbook.getSheetAt(index);
        }
        return sheet;
    }

    /**
     * This function returns cell contents as string
     * @param sheetName name of the sheet
     * @param columnNumber 
     * @param rowNumber
     * @return returns cell contents as string
     */
    public String getCellData(String sheetName,int rowNumber,int columnNumber)
    {
        String celldata="";
        if(columnNumber>=0 || rowNumber >=0)
        {
            try
            {
                sheet=getSheet(sheetName);
                Row row=sheet.getRow(rowNumber);
                Cell cell= row.getCell(columnNumber);
                celldata = getCellContentAsString(cell);
            }
            catch(NullPointerException e)
            {
                TALogger.logError("Geting NullPointerException while reading cell => Sheet_Name="+sheetName+" column="+columnNumber+"  rownumber="+rowNumber);
                return "";
            }
            catch(Exception ex)
            {
                TALogger.logError("Geting exception while reading cell => Sheet_Name="+sheetName+" column="+columnNumber+"  rownumber="+rowNumber,ex);
                return "";
            }

        }
        else
        {
            throw new TARuntimeException("Invalid index..!! rowIndex= " + rowNumber +"  columnIndex="+columnNumber);
        }
        return celldata;
    }


    /**
     * This function returns cell contents as string
     * @param sheetName
     * @param columnName
     * @param rowNumber
     * @return returns cell contents as string
     */
    public String getCellData(String sheetName,int rowNumber,String columnName)
    {
        String celldata="";
        sheet=getSheet(sheetName);
        int columnNumber=getColumnNumber(0, columnName);
        if(columnNumber>=0 || rowNumber >=0)
        {
            try
            {
                Row row=sheet.getRow(rowNumber);
                Cell cell= row.getCell(columnNumber);
                celldata = getCellContentAsString(cell);
            }
            catch(NullPointerException e)
            {
                TALogger.logError("Geting NullPointerException while reading cell => Sheet_Name="+sheetName+" column="+columnNumber+"  rownumber="+rowNumber);
                return "";
            }
            catch(Exception ex)
            {
                //This Log Error Should be here. Sometimes we get exception while reading the cell data if the cell is blank.
                TALogger.logError("Geting exception while reading cell => Sheet_Name="+sheetName+" column="+columnNumber+"  rownumber="+rowNumber,ex);
                return "";
            }
        }
        else
        {
            throw new TARuntimeException("Invalid index..!! rowIndex= " + rowNumber +"  columnIndex="+columnNumber);
        }
        return celldata;
    }


    public boolean setCellData(String sheetName,int rowNum,String colName,int headerColumnNumber, String data)
    {
        int columnNumber=getColumnNumber(headerColumnNumber, colName);
        boolean result= setCellData(sheetName, rowNum, columnNumber, headerColumnNumber, data);
        return result;
    }


    public boolean setCellData(String sheetName,int rowNum,int columnNum,int headerColumnNumber, String data)
    {
        try
        {
            sheet=getSheet(sheetName);
            Row row=sheet.getRow(rowNum);
            if(row==null)
                row=sheet.createRow(rowNum);

            sheet.autoSizeColumn(columnNum);
            Cell cell=row.getCell(columnNum);

            if(cell==null)
                cell=row.createCell(columnNum);

            cell.setCellValue(data);
            writeToExcel(workbook, path);
        }
        catch(Exception e)
        {
            throw new TARuntimeException("Problem While setting data @rowNum="+rowNum+ " ColumnNum= "+columnNum,e);
        }
        return true;
    }

    /**
     * 
     * @param rowNum
     * @param columnName
     * @return
     */
    public int getColumnNumber(int rowNum, String columnName)
    {
        int colNum=-1;
        if(rowNum>=0 && (!columnName.isEmpty()))
        {
            Row row=sheet.getRow(rowNum);
            for(int i=0;i<row.getLastCellNum();i++)
            {
                Cell cell = null;
                try
                {
                    cell = row.getCell(i);
                }
                catch(NullPointerException e)
                {
                    TALogger.logError(String.format("Cell number %s is not defined @rowNum = %s", i, rowNum));
                }

                if( cell != null && cell.getStringCellValue().trim().equalsIgnoreCase(columnName))
                {
                    colNum=i;
                    break;
                }
            }
        }
        if(colNum==-1)
        {
            TALogger.logDebug("Enable to find column " + columnName + " at row number "+ rowNum);
        }
        return colNum;
    }


    /**
     * This function returns the total number of column exist in sheet.
     * This function consider the first row of the sheet as the column row
     * @param sheetName is the name of the sheet
     * @return returns the column count
     */
    public int getColumnCount(String sheetName)
    {   
        sheet = getSheet(sheetName);
        Row row = sheet.getRow(0);
        if(row==null)
            return -1;
        return row.getLastCellNum();
    }


    /**
     * This function returns the total number of columns depending upon columnEndKeyWord.
     * E.g It will increase the counter for the columns until it find the columnEndKeyWord in the passed rowNumber.
     * MaxColumn count is 200 to avoid the infinite loop 
     * @param sheetName is the name of the sheet
     * @return returns the column count
     */
    public int getColumnCount(String sheetName,int rowNumber,String columnEndKeyWord)
    {
        int MaxColumnCount=200;
        int columnCount=0;
        int currentColumn=1;
        while(currentColumn<=MaxColumnCount)
        {
            if(getCellData(sheetName,rowNumber,currentColumn).equalsIgnoreCase(columnEndKeyWord))
            {
                break;          
            }
            else
            {
                columnCount=columnCount+1;
                currentColumn=currentColumn+1;
            }
        }
        return columnCount;
    }


    /**
     * 
     * @param sheetName
     * @return
     */
    public int getRowCount(String sheetName)
    {
        sheet = getSheet(sheetName);
        int number=sheet.getLastRowNum();
        return number+1;
    }


    /**
     * 
     * @param cell
     * @return
     */
    private String getCellContentAsString(Cell cell) throws Exception
    {
        String celldata="";
        switch (cell.getCellType())
        {

        case Cell.CELL_TYPE_BLANK:
            celldata="";
            break;

        case Cell.CELL_TYPE_STRING:
            celldata=cell.getStringCellValue();
            break;

        case Cell.CELL_TYPE_NUMERIC:
            DataFormatter df=new DataFormatter();
            celldata=df.formatCellValue(cell);
            break;

        case Cell.CELL_TYPE_FORMULA:
            celldata=String.valueOf(cell.getNumericCellValue());
            break;

        case Cell.CELL_TYPE_BOOLEAN:
            celldata=String.valueOf(cell.getBooleanCellValue());
            break;

        default:
            celldata=cell.getStringCellValue();
            break;
        }
        return celldata;
    }

    public synchronized static void writeToExcel(Workbook workbook,String filePath)
    {
        FileOutputStream fileOut;
        try 
        {
            fileOut = new FileOutputStream(filePath);
            workbook.write(fileOut);
            fileOut.close();    
        } 
        catch (IOException e) 
        {
            throw new TARuntimeException(String.format("Problem while writting into the file(%s)",filePath),e);

        }

    }   
}

回答by Nitin Singh

# Read data from excel in #selenium

#从#selenium中的excel读取数据

package readdata;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openqa.selenium.By;
import org.openqa.selenium.JavascriptException;
import org.openqa.selenium.JavascriptExecutor;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
import java.util.concurrent.TimeUnit;

public class ReadURL {
    public static WebDriver driver;
    private static final String FILE_NAME = "Enter Excel path here;
    @SuppressWarnings("unused")
    private static final boolean String = false;

    public static void main(String[] args) throws InterruptedException {
        File file = new File(".\driver\geckodriver.exe");
        System.setProperty("webdriver.gecko.driver", file.getAbsolutePath());
        driver = new FirefoxDriver();
        String URL = "URL HERE";
        driver.get(URL);
        System.out.println("URL ENTERED");
        driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);

        try {

            FileInputStream excelFile = new FileInputStream(new File(FILE_NAME));
            @SuppressWarnings("resource")
            Workbook workbook = new XSSFWorkbook(excelFile);
            Sheet datatypeSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = datatypeSheet.iterator();

            while (iterator.hasNext()) {

                Row currentRow = iterator.next();
                Iterator<Cell> cellIterator = currentRow.iterator();

                while (cellIterator.hasNext()) {

                    Cell currentCell = cellIterator.next();
// convert current cell into string because cell value can't enter into text box
                    String value = currentCell.getStringCellValue();
                    // System.out.println(value);
                    driver.findElement(By.id("enter_website_field")).sendKeys(value);
                    driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
                    driver.findElement(By.id("starttest_butoon")).click();
                    driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);

                    try {
// This is special code to read pseudo element alert 
                        String script = "return window.getComputedStyle(document.querySelector('.analyzer_search_inner.tooltipstered'),':after').getPropertyValue('content')";
                        Thread.sleep(3000);
                        JavascriptExecutor js = (JavascriptExecutor) driver;
                        String content = (String) js.executeScript(script);
                        System.out.println(content);

                        if (content.contains("\"Enter url to analyze\"")) {
                            System.out.println("\nInvalid URL\n" + value);
                        }
                    } catch (JavascriptException e) {
                        System.out.println("\nValid URL\n" + value);
                    }

                    driver.findElement(By.id("enter_website_field")).clear();
                    driver.manage().timeouts().implicitlyWait(10, TimeUnit.SECONDS);

                }
                System.out.println();

            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}