Excel模板导入、导出工具类

1.引入maven依赖，利用hutool的excel读取

Hutool-poi对excel读取、写入

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.16</version>
        </dependency>        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>net.sf.jxls</groupId>
            <artifactId>jxls-core</artifactId>
            <version>1.0.6</version>
        </dependency>
        <dependency>
            <groupId>net.sf.jxls</groupId>
            <artifactId>jxls-reader</artifactId>
            <version>1.0.3</version>
        </dependency>

注意：说明 hutool-4.x的poi-ooxml 版本需高于 3.17（别问我3.8版本为啥不行，因为3.17 > 3.8 ） hutool-5.x的poi-ooxml 版本需高于 4.1.2 hutool-5.6.x支持poi-ooxml 版本高于 5.0.0 xercesImpl版本高于2.12.0（非必须）

2.导出模板创建，内容如下，也可以参考最上面上传资源

3.excel导入、导出工具类

package com.demo.utils;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import lombok.extern.log4j.Log4j2;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.StopWatch;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description: 导出工具类
 */
@Log4j2
public class ExportUtil {

    /**
     * 导出excel
     *
     * @param templatePath excel模板路径
     * @param listData     导出数据
     * @param fileName     导出文件名
     * @param request
     * @param response
     * @throws IOException
     */
    public static void exportExcel(String templatePath,
                                   List<?> listData,
                                   String fileName,
                                   HttpServletRequest request,
                                   HttpServletResponse response) throws IOException {
        Map<String, Object> map = new HashMap<>();
        map.put("dataList", listData);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Set-Cookie", "fileDownload=true; path=/");
        response.setHeader("Content-Disposition", "attachment;filename=" +
                new String(fileName.getBytes("utf-8"), "iso8859-1"));
        StopWatch watch = new StopWatch();
        watch.start();
        ClassPathResource classPathResource = new ClassPathResource(templatePath);
        try (InputStream inputStream = classPathResource.getInputStream();
             ServletOutputStream out = response.getOutputStream()) {
            XLSTransformer xlsTransformer = new XLSTransformer();
            Workbook workbook = xlsTransformer.transformXLS(inputStream, map);
            workbook.write(out);
            workbook.close();
            watch.stop();
            log.info("导出excel共耗时：{}毫秒！", watch.getTotalTimeMillis());
        } catch (InvalidFormatException e) {
            e.printStackTrace();
            log.error("导出失败：{}", e.getMessage());
        }
    }

    /**
     * 获取导入excel文件的数据
     *
     * @param templatePath  模板地址
     * @param file          导入的excel文件
     * @return
     */
    public static List<Map<String, Object>> getImportExcelData(String templatePath,
                                                               MultipartFile file) {
        // 判断是否上传的是excel
        if ("xlsx".equals(file.getContentType()) || "xls".equals(file.getContentType())) {
            try (ExcelReader reader = ExcelUtil.getReader(file.getInputStream())) {
                // 获取模板excel表头
                List<Object> excelTitleList = getExcelTitle(templatePath);
                // 导入的文件excel表头
                List<Object> importTitleList = reader.readRow(0);
                boolean exist = importTitleList.containsAll(excelTitleList);
                // 判断导入的excel与模板的excel标题是否一致，即判断上传的模板是否正确
                if (!exist) {
                    throw new RuntimeException("模板不匹配，请下载正确模板！");
                }

                // 读取excel数据
                StopWatch stopWatch = new StopWatch();
                stopWatch.start();
                List<Map<String, Object>> dataList = reader.readAll();
                if (CollUtil.isEmpty(dataList)) {
                    throw new RuntimeException("导入文件的内容不能为空！");
                }
                stopWatch.stop();
                log.info("一共{}条数据，耗时{}毫秒！", dataList.size(), stopWatch.getTotalTimeMillis());
                return dataList;
            } catch (IOException e) {
                e.printStackTrace();
                log.error("导入的excel文件出错：{}", e.getMessage());
                throw new RuntimeException("导入的excel文件有问题！");
            }
        } else {
            throw new RuntimeException("请上传xls或xlsx格式文件！");
        }
    }

    /**
     * 根据模板excel获取excel表头
     *
     * @param excelFilePath 模板地址
     * @return
     */
    public static List<Object> getExcelTitle(String excelFilePath) {
        ClassPathResource classPathResource = new ClassPathResource(excelFilePath);
        try (InputStream in = classPathResource.getInputStream();
        ) {
            ExcelReader reader = ExcelUtil.getReader(in, 0);
            return reader.readRow(0);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("Excel读取失败:" + e.getMessage());
            throw new RuntimeException("Excel读取失败:" + e.getMessage());
        }
    }

    public static void main(String[] args) {
        List<Object> excelTitle = getExcelTitle("templates/excel/exports/test.xlsx");
        excelTitle.stream().forEach(x -> {
            System.out.println(x.toString());
        });
    }
}