java - 使用硒使用apache poi写入excel文件
问题描述
请在您对此投反对票之前,我找不到读取 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();
}
}
}
提前致谢。
解决方案
使用现有的表提取代码,您可以执行以下操作:
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();
}
}
推荐阅读
- javascript - 从子构造函数调用父方法
- javascript - React 安装中的 events.js 167 错误
- sql - 为什么 json_value 返回 null?
- javascript - 将 className,props,attributes 从父组件传递给所有子组件,无需重复代码
- node.js - 文件上传到 Azure Data Lake Storage
- java - QN 是 ether 'n' 或 'k' 是负数方法应该返回 'null' 值。我错过了什么?
- android - livedata可以用作presentor中的监听器吗
- c# - 如何获得我的标签的价值?我正在使用 C#
- javascript - 在 React 组件中组合已定义道具的最佳方法
- javascript - 如何检测哪个元素正在触发滚动事件