基于Java实现UI数据导出至xlsx并对比新增数据方案咨询

一、依赖配置（Maven） #

先在pom.xml中添加必要依赖，避免手动导包出错：

<!-- Selenium WebDriver -->
<dependency>
    <groupId>org.seleniumhq.selenium</groupId>
    <artifactId>selenium-java</artifactId>
    <version>4.20.0</version>
</dependency>
<!-- Apache POI 处理Excel -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.5</version>
</dependency>
<!-- 日志框架（可选，方便排查问题） -->
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>2.0.9</version>
</dependency>
<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>1.4.8</version>
</dependency>

二、Selenium抓取表格数据 #

1. 封装数据实体类
   别直接用ArrayList<String>存行数据，封装成实体类更易维护：

import java.util.Objects;

public class DataRecord {
    private String id; // 唯一标识，用于数据对比
    private String name;
    private String value;
    // 根据表格结构添加其他字段

    // 重写equals和hashCode，基于唯一ID字段
    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        DataRecord that = (DataRecord) o;
        return Objects.equals(id, that.id);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id);
    }

    // getter、setter、构造方法
}

2. 核心抓取逻辑

• 初始化WebDriver（推荐ChromeDriver，需下载对应版本驱动）
• 完成门户登录，定位表格并遍历数据：

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.chrome.ChromeDriver;
import org.openqa.selenium.support.ui.ExpectedConditions;
import org.openqa.selenium.support.ui.WebDriverWait;

import java.time.Duration;
import java.util.ArrayList;
import java.util.List;

public class DataCrawler {
    public List<DataRecord> crawlData(String portalUrl) {
        WebDriver driver = new ChromeDriver();
        driver.get(portalUrl);
        
        // 登录逻辑：替换成实际的元素定位和操作
        driver.findElement(By.id("username")).sendKeys("你的用户名");
        driver.findElement(By.id("password")).sendKeys("你的密码");
        driver.findElement(By.id("login-btn")).click();

        // 等待表格加载完成，定位表格tbody
        WebDriverWait wait = new WebDriverWait(driver, Duration.ofSeconds(10));
        WebElement tableBody = wait.until(ExpectedConditions.presenceOfElementLocated(By.xpath("//table[@id='data-grid']/tbody")));
        
        List<DataRecord> todayData = new ArrayList<>();
        List<WebElement> rows = tableBody.findElements(By.tagName("tr"));
        
        // 遍历表格行
        for (WebElement row : rows) {
            List<WebElement> cells = row.findElements(By.tagName("td"));
            DataRecord record = new DataRecord();
            record.setId(cells.get(0).getText()); // 假设第一列为唯一ID
            record.setName(cells.get(1).getText());
            record.setValue(cells.get(2).getText());
            todayData.add(record);
        }

        // 处理分页：如果有下一页按钮，循环抓取
        while (driver.findElement(By.id("next-page")).isEnabled()) {
            driver.findElement(By.id("next-page")).click();
            wait.until(ExpectedConditions.stalenessOf(tableBody));
            tableBody = driver.findElement(By.xpath("//table[@id='data-grid']/tbody"));
            rows = tableBody.findElements(By.tagName("tr"));
            // 重复行遍历逻辑，追加数据到todayData
        }

        driver.quit();
        return todayData;
    }
}

三、Apache POI 导出/读取Excel #

1. 导出今日数据
   封装工具类ExcelUtil实现导出：

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

public class ExcelUtil {
    public static void exportData(List<DataRecord> data, String filePath) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("数据");
        
        // 创建表头
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("ID");
        headerRow.createCell(1).setCellValue("名称");
        headerRow.createCell(2).setCellValue("数值");
        
        // 填充数据行
        int rowNum = 1;
        for (DataRecord record : data) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(record.getId());
            row.createCell(1).setCellValue(record.getName());
            row.createCell(2).setCellValue(record.getValue());
        }
        
        // 自动调整列宽
        for (int i = 0; i < 3; i++) {
            sheet.autoSizeColumn(i);
        }
        
        // 写入文件
        try (FileOutputStream fos = new FileOutputStream(filePath)) {
            workbook.write(fos);
        }
        workbook.close();
    }
}

调用时用日期命名文件：

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;

public class Main {
    public static void main(String[] args) throws IOException {
        DataCrawler crawler = new DataCrawler();
        List<DataRecord> todayData = crawler.crawlData("你的门户URL");
        
        String today = LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
        ExcelUtil.exportData(todayData, "data_" + today + ".xlsx");
    }
}

2. 读取前一日数据
   在ExcelUtil中添加读取方法：

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtil {
    // ... 导出方法
    
    public static List<DataRecord> readData(String filePath) throws IOException {
        List<DataRecord> data = new ArrayList<>();
        Workbook workbook = WorkbookFactory.create(new File(filePath));
        Sheet sheet = workbook.getSheetAt(0);
        
        // 跳过表头，从第二行读取
        for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) continue;
            
            DataRecord record = new DataRecord();
            record.setId(row.getCell(0).getStringCellValue());
            record.setName(row.getCell(1).getStringCellValue());
            record.setValue(row.getCell(2).getStringCellValue());
            data.add(record);
        }
        
        workbook.close();
        return data;
    }
}

四、数据对比逻辑 #

利用HashSet快速判断新增数据：

import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class DataComparator {
    public List<DataRecord> findNewData(List<DataRecord> todayData, List<DataRecord> yesterdayData) {
        Set<DataRecord> yesterdaySet = new HashSet<>(yesterdayData);
        List<DataRecord> newData = new ArrayList<>();
        
        for (DataRecord record : todayData) {
            if (!yesterdaySet.contains(record)) {
                newData.add(record);
            }
        }
        
        return newData;
    }
}

在Main中整合对比逻辑：

public class Main {
    public static void main(String[] args) throws IOException {
        DataCrawler crawler = new DataCrawler();
        List<DataRecord> todayData = crawler.crawlData("你的门户URL");
        
        String today = LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
        ExcelUtil.exportData(todayData, "data_" + today + ".xlsx");
        
        // 读取前一日数据并对比
        String yesterday = LocalDate.now().minusDays(1).format(DateTimeFormatter.ofPattern("yyyyMMdd"));
        String yesterdayFilePath = "data_" + yesterday + ".xlsx";
        List<DataRecord> yesterdayData = new ArrayList<>();
        File yesterdayFile = new File(yesterdayFilePath);
        
        if (yesterdayFile.exists()) {
            yesterdayData = ExcelUtil.readData(yesterdayFilePath);
            DataComparator comparator = new DataComparator();
            List<DataRecord> newData = comparator.findNewData(todayData, yesterdayData);
            
            if (!newData.isEmpty()) {
                ExcelUtil.exportData(newData, "new_data_" + today + ".xlsx");
            }
        }
    }
}

五、新手避坑提示 #

• 元素定位：优先用id、name等稳定属性，避免绝对XPath，防止页面结构变化导致定位失效。
• 等待机制：别用Thread.sleep()，改用WebDriverWait等待元素加载，解决页面加载慢的问题。
• 异常处理：添加try-catch块处理登录失败、文件不存在（首次执行无历史文件）、网络中断等场景。
• 自动化调度：将代码打包成jar包，用Windows任务计划或Linux crontab定时运行，比如Linux下配置0 9 * * * java -jar /path/to/your/jar/file.jar（每天9点执行）。

内容的提问来源于stack exchange，提问作者JackofallTrade