【Java】下载excel文件并解析excel数据

1、maven依赖

　　　　  <!--    下载文件    -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>4.1.2</version>
        </dependency>
        <!--    操作excel    -->
        <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>

2、代码

import cn.hutool.http.HttpUtil;
import cn.hutool.json.JSONObject;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;

public class aa {
    /**
     * 下载文件
     *
     * @param url
     * @param outFilePath
     */
    public void downloadFile(String url, String outFilePath) {
        HttpUtil.downloadFile(url, outFilePath);
    }

    /**
     * 解析Excel
     *
     * @param excelPath          excel路径
     * @param isReadDefaultSheet true-默认读取第0个sheet表
     * @param sheetName          读取指定名称得sheet表（当isReadDefaultSheet=false时生效）
     * @return [
     * {"0":"第0列第0行","1":"第0列第1行"},
     * {"0":"第1列第0行","1":"第1列第1行"}
     * ]
     */
    public ArrayList<JSONObject> analysisExcel(String excelPath, boolean isReadDefaultSheet, String sheetName) {
        XSSFWorkbook wb = null;
        try {
            wb = new XSSFWorkbook(new FileInputStream(excelPath));
        } catch (IOException e) {
            e.printStackTrace();
        }
        XSSFSheet sheet = null;
        if (isReadDefaultSheet) {
            sheet = wb.getSheetAt(0);
        } else {
            sheet = wb.getSheet(sheetName);
        }

        int lastRowNum = sheet.getLastRowNum() + 1; // 总行数
        int lastCellNum = sheet.getRow(0).getPhysicalNumberOfCells(); // 总列数

        ArrayList<JSONObject> excelDataList = new ArrayList<>();
        // 循环行
        for (int rowIndex = 0; rowIndex < lastRowNum; rowIndex++) {
            XSSFRow row = sheet.getRow(rowIndex);
            JSONObject rowDataJson = new JSONObject();
            // 循环列
            for (int colIndex = 0; colIndex < lastCellNum; colIndex++) {
                CellType cellTypeEnum = row.getCell(colIndex).getCellTypeEnum();
                switch (cellTypeEnum.name()) {
                    case "_NONE":
                        break;
                    case "NUMERIC":
                        rowDataJson.put(String.valueOf(colIndex), String.valueOf(row.getCell(colIndex).getNumericCellValue()));
                        break;
                    case "STRING":
                        rowDataJson.put(String.valueOf(colIndex), String.valueOf(row.getCell(colIndex).getStringCellValue()));
                        break;
                    case "FORMULA":
                        rowDataJson.put(String.valueOf(colIndex), String.valueOf(row.getCell(colIndex).getCellFormula()));
                        break;
                    case "BLANK":
                        rowDataJson.put(String.valueOf(colIndex), "");
                        break;
                    case "BOOLEAN":
                        rowDataJson.put(String.valueOf(colIndex), String.valueOf(row.getCell(colIndex).getBooleanCellValue()));
                        break;
                    case "ERROR":
                        break;
                }
            }
            excelDataList.add(rowDataJson);
        }
        for (Object a : excelDataList) {
            System.out.println(a);
        }
        return excelDataList;
    }

    public static void main(String[] args) throws IOException {
        // 下载文件
        String url = "http://www.chengdu.gov.cn/chengdu/c105449f//2020-04/15/63c83141a30244e5b340265ecad395e7/files/c44606a20b694c22aa6ddf63530e863d.pdf";
        new aa().downloadFile(url,"e:/abc/test02.pdf");

        // 解析excel
        String exP = "E:\\aaa.xlsx";
        new aa().analysisExcel(exP, true, "");
    }
}

3、效果

3.1、下载文件

 

 3.2、解析excel

 4、备注

get

HttpRquest.get(url).header("XXX", "XXX").execute().writeBody("d:/downloadFile")；

post

import cn.hutool.http.HttpRequest;
import cn.hutool.json.JSONObject;

/**
 * @Author:
 * @Date: 2022/1/6 20:52
 * @Description:
 * @Version: v1.0
 */
public class aaa {
    public static void main(String[] args) {
        String url = "https://xxx.xxx.xxx.xxx/admin/statistics?";

        JSONObject jsonObject = new JSONObject();
        jsonObject.put("startTime",1641398400000l);
        jsonObject.put("endTime",1641431562957l);

        long l = HttpRequest.post(url)
                .header("Authorization", "e4f9c9db-ae6a-450b-a808-2256800fcba7")
                .body(jsonObject)
                .execute().writeBody("E:\\abc\\666.xlsx");
        System.out.println(l);
    }
}