java 如何在java，selenium测试中读取excel数据？

How to read excel data in java, selenium testing?

提问by

I have a selenium webdriver backed code in java for testing an web application. My code is given below:

我在 java 中有一个 selenium webdriver 支持的代码，用于测试 web 应用程序。我的代码如下：

package testcases;

import com.thoughtworks.selenium.Selenium;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebDriverBackedSelenium;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import static org.junit.Assert.*;
import java.util.regex.Pattern;

public class Untitled {
private Selenium selenium;

@Before
public void setUp() throws Exception {
        WebDriver driver = new FirefoxDriver();
        String baseUrl = "http://www.himalayanpalmistry.com/";
        selenium = new WebDriverBackedSelenium(driver, baseUrl);
}

@Test
public void testUntitled() throws Exception {
        selenium.open("/mabiz/");
        selenium.select("id=register_type", "label=für Unternehmen");
        selenium.click("id=rgst");
        selenium.waitForPageToLoad("30000");
        selenium.type("id=username", "javatesting1");
        selenium.type("id=password", "12345678");
        selenium.type("id=confirm_password", "12345678");
        selenium.type("id=name", "java testing");
        selenium.type("id=position", "java testing");
        selenium.type("id=company", "java testing");
        selenium.type("id=address", "java testing");
        selenium.type("id=zipcode", "12345");
        selenium.type("id=city", "safdj");
        selenium.type("id=phone", "kfajs");
        selenium.type("id=email", "[email protected]");
        selenium.click("id=show_contact_info");
        selenium.click("id=product_select3");
        selenium.click("id=term_condition1");
        selenium.click("name=submit_one");
        selenium.waitForPageToLoad("30000");
}

@After
public void tearDown() throws Exception {
        selenium.stop();
}
}

I want to modify this code for reading microsoft excel data and do many tests by this code. My excel file contains various test data. Thanx in advance!!

我想修改此代码以读取 Microsoft excel 数据并通过此代码进行许多测试。我的 excel 文件包含各种测试数据。提前谢谢！！

采纳答案by Santiago Hernandez

You can use this api to read excel rows from java: http://jexcelapi.sourceforge.net/The tutorial is great and can be found here: http://www.andykhan.com/jexcelapi/tutorial.html

您可以使用此 api 从 java 读取 excel 行：http: //jexcelapi.sourceforge.net/教程很棒，可以在这里找到：http: //www.andykhan.com/jexcelapi/tutorial.html

Also, be careful about posting usernames and passwords in your scripts to stackoverflow ;)

另外，请注意将脚本中的用户名和密码发布到 stackoverflow ;)

回答by Ran Adler

   1.Read excel example


    InputStream stream = new FileInputStream(projectFile.getPath());
    ExcelWorkbookContainer container = new ExcelWorkbookContainer(stream);
    Iterator<ExcelRecord> recordIterator = container.getRecordIterator(0);
    //NO TITLE
    recordIterator.next();

    while(recordIterator.hasNext()) {
        ExcelRecord record = recordIterator.next();
         clusterObj.setBrand(record.getValue(ClusterColum.BRAND.getColumIndex()));
    }
    stream.close();

///// poi example

public class ExcelWorkbookContainer {

    private String filename;
    private XSSFWorkbook workbook;

    /**
     * @param filename
     * @throws FileNotFoundException, IOException
     */
    public ExcelWorkbookContainer(String filename)
            throws FileNotFoundException, IOException {
        this(new FileInputStream(filename));
        this.filename = filename;
    }

    /**
     * @param inputStream
     * @throws FileNotFoundException, IOException
     */
    public ExcelWorkbookContainer(InputStream inputStream)
            throws IOException {
        if (inputStream == null) {
            throw new NullPointerException("Input Stream is null");
        }

        workbook = new XSSFWorkbook(inputStream);
    }

    /**
     * Create a composite iterator on the excel records
     * 
     * @param sheetId the id of the sheet for data extraction
     * @return composite iterator on the excel records
     */
    public Iterator<ExcelRecord> getRecordIterator(int sheetId) {
        return new ExcelRecordIterator(workbook.getSheetAt(sheetId));
    }

    /**
     * Create a composite iterator on the excel records
     * 
     * @param sheetId the id of the sheet for data extraction
     * @return composite iterator on the excel records
     */
    public Iterator<ExcelRecord> getRecordIterator(int sheetId, String[] headers) {
        return new ExcelRecordIterator(workbook.getSheetAt(sheetId), headers);
    }

    public String getFilename() {
        return filename;
    }

    public XSSFWorkbook getWorkbook() {
        return workbook;
    }
}

回答by Ssire

For reading and writing Excel I created an open source library called PtheitroadE which includes many user-friendly methods.

为了读写 Excel，我创建了一个名为 PtheitroadE 的开源库，其中包含许多用户友好的方法。

https://github.com/ssirekumar/PtheitroadE

https://github.com/ssirekumar/PtheitroadE

https://github.com/ssirekumar/PtheitroadE/releases

https://github.com/ssirekumar/PtheitroadE/releases

PtheitroadE

普尔

It does Microsoft Ms-office(Excel,Ms Word,PowerPoint) file handling with Java using the POI classes. This API was developed on the top of the POI library. It has many methods for working with the Ms-office files(Excel,Ms Word,PowerPoint). These methods will work on the both the binary(.xls, .doc, .ppt) and XML(.xlsx, .docx, .pptx) with the help of the NPOIFSFileSystem & OPCPackage classes in the POI. With these classes the reading and writing into a file would be faster.

它使用 POI 类使用 Java 处理 Microsoft Ms-office（Excel、Ms Word、PowerPoint）文件。这个 API 是在 POI 库的顶部开发的。它有许多处理 Ms-office 文件（Excel、Ms Word、PowerPoint）的方法。在 POI 中的 NPOIFSFileSystem 和 OPCPackage 类的帮助下，这些方法将适用于二进制文件（.xls、.doc、.ppt）和 XML（.xlsx、.docx、.pptx）。使用这些类，读取和写入文件会更快。

回答by Zaid Aquel

You can use Apache POI to access Excel http://poi.apache.org/spreadsheet/index.html

您可以使用 Apache POI 访问 Excel http://poi.apache.org/spreadsheet/index.html

回答by Anuragh27crony

There are many API's in java available to read data from Excel.

java中有许多API可用于从Excel读取数据。

1. JExcelApi.
2. POI

1. JExcelApi。
2. 兴趣点

Work Around

解决

Save Excel as CSV files (in case you don't want to use any external API)and read them normally as a text file. But use Comma as Separator.

将 Excel 保存为 CSV 文件（以防您不想使用任何外部 API）并将它们作为文本文件正常读取。但使用逗号作为分隔符。

Check this link for more information: How to read and write excel file in java

检查此链接以获取更多信息： How to read and write excel file in java

I hope this helps :-)

我希望这有帮助 ：-）