Spring Boot项目的导入、导出、下载模板、Excel转PDF、网络路径文件的数据读取功能

前言

1、新增依赖

<!--导出-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>
<!--导入-->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.7.22</version>
</dependency>

2、引入第三方Spire.Xls.jar包（excel转pdf）

① 下载地址：Spaire.Xls.jar

② 依赖添加

<!--表格转pdf-->
<dependency>
    <groupId>e-iceblue</groupId>
    <artifactId>spire.xls</artifactId>
    <version>5.1.0</version>
    <scope>system</scope>
    <systemPath>${project.basedir}/src/main/resources/lib/Spire.Xls.jar</systemPath>
</dependency>

一、Excel导入

1、工具类方法

    /**
     * 解析excel中的数据
     *
     * @param file 文件
     * @param rowNames excel中的列名称
     * @param rowNamesEn class对象中的属性
     * @param headerRowIndex 头部所在行数
     * @param startRowIndex excel主数据的开始行数
     * @param clazz 解析返回的对象类型
     * @return
     */
public static <T> List<T> resolveFileToExcel(MultipartFile file, String[] rowNames, String[] rowNamesEn, int headerRowIndex, int startRowIndex, Class<T> clazz) {
    //解析文件为workbook
    XSSFWorkbook workbook;
    try {
        workbook = new XSSFWorkbook(file.getInputStream());
    } catch (IOException e) {
        e.printStackTrace();
        throw new ErrorForWebException(ErrorCode.PARAM_EXCEPTION);
    }

    //获取excel中所有的sheet页数据
    List<T> voList = new ArrayList<>();
    try {
        //解析excel中的头映射关系（即excel中的列映射到class的对象属性）
        Map<String, String> map = new HashMap<>(32);
        for (int i = 0; i < rowNamesEn.length; i++) {
            map.put(rowNames[i], rowNamesEn[i]);
        }
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            ExcelReader reader = ExcelUtil.getReader(file.getInputStream(), i);
            reader.setHeaderAlias(map);

            voList.addAll(reader.read(headerRowIndex, startRowIndex, clazz));
        }
    } catch (IOException e) {
        e.printStackTrace();
        throw new ErrorForWebException(ErrorCode.PARAM_EXCEPTION);
    }

    return voList;
}

2、方法测试

public static void main(String[] args) throws IOException {
    String[] rowNames = {"姓名", "性别", "班级", "成绩", "学号"};
    String[] rowNamesEn = {"name", "sex", "grade", "score", "sno"};
    MultipartFile file = new MockMultipartFile("测试报告.xlsx", new FileInputStream(new File("d:/file/test.xlsx")));
    List<Test> tests = resolveFileToExcel(file, rowNames, rowNamesEn, 0, 1, Test.class);
}

二、Excel导出

1、工具类方法

public static void export(List<Test> voList, HttpServletResponse response) {
    Map<String, Object> map = new HashMap<>(2);
    map.put("list", voList);
    Workbook workbook = null;
    try {
        TemplateExportParams params = new TemplateExportParams("templates/测试模板.xlsx", true);
        workbook = ExcelExportUtil.exportExcel(params, map);
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("测试.xlsx", "UTF-8"));
        workbook.write(response.getOutputStream());
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (workbook != null) {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

2、模板内容

三、下载Excel模板

1、工具类方法

public static void downloadTemplate(HttpServletResponse response) throws IOException {
    try {
        byte[] bytes = IoUtils.toByteArray(resourceLoader.getResource("classpath:templates/测试.xlsx").getInputStream());
        Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bytes));
        ByteArrayOutputStream bo = new ByteArrayOutputStream();
        workbook.write(bo);
        bo.flush();
        byte[] array = bo.toByteArray();
        response.setHeader("Content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String("测试.xlsx".getBytes(StandardCharsets.UTF_8), "ISO8859-1"));
        response.setCharacterEncoding("UTF-8");
        response.getOutputStream().write(array);
        response.setContentLength(array.length);
    } catch (Exception e) {
        log.error("downloadTemplate --> download excel template failed, errorMsg: {}", e.getMessage(), e);
        throw new BusinessException("获取模板失败");
    } finally {
        if (response.getOutputStream() != null) {
            response.getOutputStream().flush();
        }
    }
}

四、Excel转PDF

public static File translateExcelToPdf() {
    File file = new File("d:/file/test.excel");

    //转pdf
    com.spire.xls.Workbook wb = new com.spire.xls.Workbook();
    wb.loadFromFile(file.getAbsolutePath());
    String outputFilePath = file.getParentFile().getPath() + File.separator + file.getName().split(".xlsx")[0] + ".pdf";
    wb.saveToFile(outputFilePath, FileFormat.PDF);

    //删除本地缓存excel文件
    boolean delete = file.delete();

    return new File(outputFilePath);
}

五、网络路径读取Excel中的数据

public static List<String> resolveNetworkFileUrl(String url) {
    List<String> voList = new ArrayList<>();
    List<Map<Integer, String>> infoList = new ArrayList<>();
    try {
        //读取网络路径文件数据
        EasyExcel.read(new URL(url).openConnection().getInputStream())
                .headRowNumber(1)
                .registerReadListener(new AnalysisEventListener<Map<Integer, String>>() {
                    @Override
                    public void invoke(Map<Integer, String> o, AnalysisContext analysisContext) {
                        infoList.add(o);
                    }

                    @Override
                    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                    }
                }).doReadAll();
    } catch (IOException e) {
        throw new ErrorForWebException(ErrorCode.AMS_SN_CODE_ERROR);
    }
    infoList.forEach(map -> voList.addAll(map.values()));

    return voList;
}