HttpClient+Jsoup抓取页面下载表格文件，fastjson和WorkBook处理表格数据，Spring Schedule和cron表达式开启定时任务

这次在SpringBoot项目中使用Schedule注解实现定时任务

需求：每月初下载http://www.safe.gov.cn/safe/gzhbdmyzslb/index.html中上个月的表格进行解析。

导入依赖

<dependencies>
		<!-- https://mvnrepository.com/artifact/org.apache.httpcomponents/httpclient -->
		<dependency>
			<groupId>org.apache.httpcomponents</groupId>
			<artifactId>httpclient</artifactId>
			<version>4.5.5</version>
		</dependency>
		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>2.5</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.jsoup/jsoup -->
		<dependency>
			<groupId>org.jsoup</groupId>
			<artifactId>jsoup</artifactId>
			<version>1.11.3</version>
		</dependency>
		<dependency>
			<groupId>org.jsoup</groupId>
			<artifactId>jsoup</artifactId>
			<version>1.11.3</version>
		</dependency>
		<dependency>
			<groupId>org.seleniumhq.selenium</groupId>
			<artifactId>selenium-java</artifactId>
			<version>3.3.1</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.17</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.17</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-examples</artifactId>
			<version>3.17</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-excelant</artifactId>
			<version>3.17</version>
		</dependency>
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>1.2.46</version>
		</dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
            <version>2.1.5.RELEASE</version>
        </dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>5.1.7.RELEASE</version>
		</dependency>
	</dependencies>

解决思路

列表类型，每月初获取第一条中的超链接，打开下载页面

                //根据唯一标识获得className为"list_conr"的标签
                Elements listConr = document.getElementsByClass("list_conr");

                //主页按照不同月份有不同链接，是一个列表，我们需要获得最新的月份，即第一个列表里的链接，该链接能打开表格下载的网页
                Element ahref=listConr.select("ul").get(0).select("li").get(0).selectFirst("dt").selectFirst("a");
                String hrefDetail=ahref.attr("href");

在新的页面要用同样的方法获得xlsx的下载地址

                        //获得xlsx的下载链接url
                        Element axlsxhref=document1.getElementsByClass("detail_content").first().select("div").first().select("p").get(2).select("a").first();
                        String axlsxhrefDetail=axlsxhref.attr("href");

下载表格，加了定时任务为了区分，在表格文件名上包括当前的时间

                                SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy年MM月dd日HH_mm_ss");
                                String date=dateFormat.format(new Date());

                                //文件复制
                                InputStream inputStream = entity3.getContent();
                                FileUtils.copyToFile(inputStream,new File("E:\\rmbrate\\rate"+date+fileType));

处理表格数据

根据表格内容设计POJO

public class ConversionRateToDollar {
   
    private String month;
    private String currency;
    private String currencyCN;
    private String unit;
    private double rate;

    public String getMonth() {
   
        return month;
    }

    public void setMonth(String month) {
   
        this.month = month;
    }

    public String getCurrency() {
   
        return currency;
    }

    public void setCurrency(String currency) {
   
        this.currency = currency;
    }

    public String getCurrencyCN() {
   
        return currencyCN;
    }

    public void setCurrencyCN(String currencyCN) {
   
        this.currencyCN = currencyCN;
    }

    public String getUnit() {
   
        return unit;
    }

    public void setUnit(String unit) {
   
        this.unit = unit;
    }

    public double getRate() {
   
        return rate;
    }

    public void setRate(double rate) {
   
        this.rate = rate;
    }
}

我写了一个处理表格的ExcelUtil类，分别对.xls和.xlsx文件进行处理，返回JavaBean的集合对象

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.alibaba.fastjson.JSON;
public class ExcelUtil {
   

    //导入Excel数据
    public List<ConversionRateToDollar> importExcelActionForXLSX(String filePath) throws Exception {
   

        //打开工作簿
        XSSFWorkbook wookbook = new XSSFWorkbook(new FileInputStream(filePath));

        //获得第一张表
        XSSFSheet sheet =