当前位置: 首页 > 知识库问答 >
问题:

使用selenium通过apache poi写入excel文件

闾丘文昌
2023-03-14

请在你投反对票之前,我找不到一个读取web表格并将其写入Excel文件的例子。如果你碰巧发现这个链接,请提供给我。我发现了很多关于如何写一个Excel文件的例子,但是没有从web表格部分读取。

这是我的代码:

public class WebTable1 {

    public static void main(String[] args) throws IOException {
        System.setProperty("webdriver.chrome.driver", "C:\\Users\\chromedriver_win32\\chromedriver.exe");
        WebDriver driver = new ChromeDriver();

        driver.get("https://www.w3schools.com/html/html_tables.asp");

        //tr means Row, this table has 7 rows including Header
        ///td means Column, this table has 3 columns

        //*[@id="customers"]/tbody/tr[2]/td[1]
        //*[@id="customers"]/tbody/tr[7]/td[1]
        //Notice above the pattern, only the values are changing for tr[??]- which is why we will break it down into 2 String
        //below and then concatinate them as String

        String beforeXpath_Company = "//*[@id='customers']/tbody/tr["; // changed customer to single quote
        String aferXpath_Company = "]/td[1]";  //Company is column 1

        String beforeXpath_Contact = "//*[@id='customers']/tbody/tr[";
        String aferXpath_Contact = "]/td[2]";  // Contact is column 2

        String beforeXpath_Country = "//*[@id='customers']/tbody/tr[";
        String aferXpath_Country = "]/td[3]";  // Country is column 3

        //Find number of rows so that we do not use hard coded values
        List<WebElement> totalRows = driver.findElements(By.xpath("//table[@id='customers']//tr"));
        int rows=totalRows.size();



        for (int i = 2; i <rows; i++) {  //we start from 2 because  1 is column name
            String actualXpath = beforeXpath_Company + i + aferXpath_Company;
            String companyName = driver.findElement(By.xpath(actualXpath)).getText();
            System.out.println(companyName);

            String actualXpath_Contact = beforeXpath_Contact + i + aferXpath_Contact;
            String contactName = driver.findElement(By.xpath(actualXpath_Contact)).getText();
            System.out.println(contactName);

            String actualXpath_Country = beforeXpath_Country + i + aferXpath_Country;
            String countryName = driver.findElement(By.xpath(actualXpath_Country)).getText();
            System.out.println(countryName);

           //Try to following to write to an Excel file in C drive
            Workbook wb = new HSSFWorkbook();
            CreationHelper createHelper = wb.getCreationHelper();
            Sheet sheet1 = wb.createSheet("Sheet1");

            Row row = sheet1.createRow(0);
            Cell cell = row.createCell(0);
            cell.setCellValue(createHelper.createRichTextString(companyName));

            FileOutputStream fileOut = new FileOutputStream("C:\\MyTemp\\Test.xls");
            wb.write(fileOut);
            fileOut.close();
        }
    }
}

提前感谢。

共有1个答案

顾鸣
2023-03-14

利用您现有的表提取代码,您可以这样做:

public class WebTable1 {

  public static void main(String[] args) throws IOException {
    System.setProperty("webdriver.chrome.driver", "C:\\Users\\chromedriver_win32\\chromedriver.exe");
    WebDriver driver = new ChromeDriver();

    driver.get("https://www.w3schools.com/html/html_tables.asp");

    String beforeXpath_Company = "//*[@id='customers']/tbody/tr["; // changed customer to single quote
    String aferXpath_Company = "]/td[1]";  //Company is column 1

    String beforeXpath_Contact = "//*[@id='customers']/tbody/tr[";
    String aferXpath_Contact = "]/td[2]";  // Contact is column 2

    String beforeXpath_Country = "//*[@id='customers']/tbody/tr[";
    String aferXpath_Country = "]/td[3]";  // Country is column 3

    //Find number of rows so that we do not use hard coded values
    List<WebElement> totalRows = driver.findElements(By.xpath("//table[@id='customers']//tr"));
    int rows=totalRows.size();


    // Create a workbook and a sheet in it
    Workbook wb = new HSSFWorkbook();
    Sheet sheet1 = wb.createSheet("Sheet1");

    // Create a table header
    Row row = sheet.createRow(0);
    row.createCell(0).setCellValue("Company name");
    row.createCell(1).setCellValue("Contact name");
    row.createCell(2).setCellValue("Country");


    for (int i = 2; i <rows; i++) {  //we start from 2 because  1 is column name
        String actualXpath = beforeXpath_Company + i + aferXpath_Company;
        String companyName = driver.findElement(By.xpath(actualXpath)).getText();

        String actualXpath_Contact = beforeXpath_Contact + i + aferXpath_Contact;
        String contactName = driver.findElement(By.xpath(actualXpath_Contact)).getText();

        String actualXpath_Country = beforeXpath_Country + i + aferXpath_Country;
        String countryName = driver.findElement(By.xpath(actualXpath_Country)).getText();

        Row row = sheet1.createRow(i - 1);
        row.createCell(0).setCellValue(companyName);
        row.createCell(1).setCellValue(contactName);
        row.createCell(2).setCellValue(countryName);
    }
    FileOutputStream fileOut = new FileOutputStream("C:\\MyTemp\\Test.xls");
    wb.write(fileOut);
    fileOut.close();
  }
}
 类似资料:
  • 我正在写一个程序,它需要从excel文件中读取和写入数据,而不考虑格式(xls或xlsx)。 我知道ApachePOI,但它似乎有不同的类来处理xls文件(HSSF)和xlsx(XSSF)文件。 任何人都知道我将如何实现我在这里的目标。(也欢迎使用POI以外的API的想法)。

  • 我已经编写了用于编写xlsm(Excel2007)的java文件。 使用ApachePOI库,编写xlsx文件是成功的。编写xlsm文件是成功的。但我无法打开xlsm文件,因为打开xlsm文件时出错。 使用ApachePOI库编写xlsm文件可行吗? 如果可以编写xlsm,请提供如何使用ApachePOI库编写xlsm文件的指南。 谢谢

  • 用户可以将Excel文档保存为. xls或xml-table。结果是一个具有固定结构的xml-file,Excel可以用它纠正工作。 可以用java中的ApachePOI打开这种类型的excel文件吗? 事先谢谢你,安德烈。

  • 请帮我解决这个问题。 提前谢谢你。

  • 问题内容: 我正在尝试使用Java的Google Sheet API将值写入单元格。为了阅读,我使用了Java Quickstart的指南 ,该指南对我来说效果很好。 要写入Google表格,我使用: 该函数在运行时输出以下错误: 我正在使用的身份验证范围 问题答案: 显然有几个问题在一起: 删除存储在/Users/XXX/.credentials中的凭据。 将范围更改为SheetsScopes.

  • 我有保存在表中的数据(我已经从一个文件中收集了它),我想用java把它写在Excel xls文件中 我只共享主类,因为它显示了我如何将数据保存在表中