使用阿里开源的EasyExcel导入导出EXCEL——工具类

工具类

package com.example.demo.util.excel;

import cn.hutool.core.collection.IterUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.StringUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.HttpHeaders;
import org.springframework.util.Assert;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * 导入导出excel
 */
@Slf4j
public class EasyExcelUtil {
	
	/**
     * 创建对象读取excel
     *
     * @param excel excel文件
     * @param clazz 自定义对象
     * @return 数据集合
     */
    public static <T> List<T> readExcel(MultipartFile excel, Class<T> clazz) {
        checkExcel(excel);
        try (InputStream fileStream = new BufferedInputStream(excel.getInputStream())) {
            List<T> excelData = new ArrayList<>();
            EasyExcel.read(fileStream, clazz, readListener(excelData)).autoTrim(true).sheet().doRead();
            return excelData;
        } catch (Exception e) {
            log.error("导入失败, 请检查excel文件!", e);
            throw new RuntimeException("导入失败, 请检查excel文件!");
        }
    }

    /**
     * 创建对象并指定起始行读取Excel
     *
     * @param excel       excel文件
     * @param clazz       自定义对象
     * @param startRowNum 起始行
     * @return 数据集合
     */
    public static <T> List<T> readExcelWithStartRowNum(MultipartFile excel, Class<T> clazz, Integer startRowNum) {
        checkExcel(excel);
        try (InputStream fileStream = new BufferedInputStream(excel.getInputStream())) {
            List<T> excelData = new ArrayList<>();
            if (startRowNum < 1) {
                EasyExcel.read(fileStream, clazz, readListener(excelData)).autoTrim(true).sheet().doRead();
            } else {
                EasyExcel.read(fileStream, clazz, readListener(excelData)).autoTrim(true).headRowNumber(startRowNum).sheet().doRead();
            }
            return excelData;
        } catch (Exception e) {
            log.error("导入失败, 请检查excel文件!", e);
            throw new RuntimeException("导入失败, 请检查excel文件!");
        }
    }

    /**
     * 创建对象并指定sheet页读取excel
     *
     * @param excel     excel文件
     * @param clazz     自定义对象
     * @param sheetNums 指定的sheet列表
     * @return 数据集合
     */
    public static <T> List<T> readExcelWithSheetNums(MultipartFile excel, Class<T> clazz, List<Integer> sheetNums) {
        checkExcel(excel);
        try (InputStream fileStream = new BufferedInputStream(excel.getInputStream())) {
            List<T> excelData = new ArrayList<>();
            ExcelReader excelReader = EasyExcel.read(fileStream, clazz, readListener(excelData)).autoTrim(true).build();
            List<ReadSheet> readSheets = sheetNums.stream().map(sheetNum -> EasyExcel.readSheet(sheetNum).build()).collect(Collectors.toList());
            excelReader.read(readSheets);
            excelReader.finish();
            return excelData;
        } catch (Exception e) {
            log.error("导入失败, 请检查excel文件!", e);
            throw new RuntimeException("导入失败, 请检查excel文件!");
        }
    }

    /**
     * 创建对象并指定起始行和sheet页读取Excel
     *
     * @param excel       excel文件
     * @param clazz       自定义对象
     * @param startRowNum 起始行
     * @param sheetNums   指定的sheet列表
     * @return 数据集合
     */
    public static <T> List<T> readExcelWithStartRowNumAndSheetNums(MultipartFile excel, Class<T> clazz, Integer startRowNum, List<Integer> sheetNums) {
        checkExcel(excel);
        try (InputStream fileStream = new BufferedInputStream(excel.getInputStream())) {
            List<T> excelData = new ArrayList<>();
            ExcelReader excelReader;
            if (startRowNum < 1) {
                excelReader = EasyExcel.read(fileStream, clazz, readListener(excelData)).autoTrim(true).build();
            } else {
                excelReader = EasyExcel.read(fileStream, clazz, readListener(excelData)).autoTrim(true).headRowNumber(startRowNum).build();
            }
            List<ReadSheet> readSheets = sheetNums.stream().map(sheetNum -> EasyExcel.readSheet(sheetNum).build()).collect(Collectors.toList());
            excelReader.read(readSheets);
            excelReader.finish();
            return excelData;
        } catch (Exception e) {
            log.error("导入失败, 请检查excel文件!", e);
            throw new RuntimeException("导入失败, 请检查excel文件!");
        }
    }

    /**
     * 导出单sheet页的excel文件
     *
     * @param response  HttpServletResponse
     * @param fileName  文件名
     * @param sheetName sheet页名称
     * @param data      导出数据
     */
    public static <T> void writeExcel(HttpServletResponse response, String fileName, String sheetName, List<T> data) {
        try {
            encodeFileName(response, fileName);
            EasyExcel.write(response.getOutputStream(), IterUtil.getElementType(data))
                    // 设置列宽自适应
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .sheet(StringUtils.isEmpty(sheetName) ? "Sheet1" : sheetName)
                    .doWrite(data);
        } catch (UnsupportedEncodingException e) {
            log.info("导出excel编码异常", e);
        } catch (IOException e) {
            log.error("导出excel文件异常", e);
        }
    }

    /**
     * 导出多sheet页的excel文件
     *
     * @param response  HttpServletResponse
     * @param fileName  文件名
     * @param sheetData sheet页名称和与之对应的数据
     */
    public static void writeExcel(HttpServletResponse response, String fileName, Map<String, List<?>> sheetData) {
        try {
            encodeFileName(response, fileName);
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            sheetData.forEach((sheetName, data) -> {
                WriteSheet writeSheet = EasyExcel.writerSheet(sheetName)
                        .head(IterUtil.getElementType(data))
                        // 设置单元格宽度自适应
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                        .build();
                excelWriter.write(data, writeSheet);
            });
            excelWriter.finish();
        } catch (UnsupportedEncodingException e) {
            log.info("导出excel编码异常", e);
        } catch (IOException e) {
            log.error("导出excel文件异常", e);
        }
    }

    /**
     * 导出服务器中模板的文件
     *
     * @param response     HttpServletResponse
     * @param fileName     文件名
     * @param sheetName    sheet页名称
     * @param data         导出数据
     * @param templatePath 模板路径
     */
    public static <T> void writeExcelByTemplate(HttpServletResponse response, String fileName, String sheetName, List<T> data, String templatePath) {
        try {
            encodeFileName(response, fileName);
            EasyExcel.write(response.getOutputStream(), IterUtil.getElementType(data))
                    .withTemplate(templatePath)
                    .sheet(StringUtils.isEmpty(sheetName) ? "Sheet1" : sheetName)
                    .doFill(data);
        } catch (UnsupportedEncodingException e) {
            log.info("导出excel编码异常", e);
        } catch (IOException e) {
            log.error("导出excel文件异常", e);
        }
    }

    /**
     * 导出模板信息（自定义表头）
     * @param response  响应
     * @param fileName  导出文件名
     * @param sheetName sheet页名称
     * @param heads     表头信息
     */
    public static void writerExcelWithHead(HttpServletResponse response, String fileName, String sheetName, List<String> heads) {
        try {
            encodeFileName(response, fileName);
            EasyExcel.write(response.getOutputStream())
                    .head(head(heads))
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .sheet(sheetName)
                    .doWrite(new ArrayList<>());
        } catch (UnsupportedEncodingException e1) {
            log.error("导出excel未知编码异常", e1);
        } catch (IOException e) {
            log.error("导出excel文件异常", e);
        }
    }

    /**
     * 获取表头
     * @param strings 表头信息
     * @return 表头
     */
    private static List<List<String>> head(List<String> strings) {
        List<List<String>> list = new ArrayList<>();
        for (String string : strings) {
            List<String> head = new ArrayList<>();
            head.add(string);
            list.add(head);
        }
        return list;
    }
	
	/**
     * 校验excel文件是否合格
     *
     * @param excel excel文件
     */
    private static void checkExcel(MultipartFile excel) {
        Assert.isTrue(!excel.isEmpty(), "请选择excel文件");
        String fileName = excel.getOriginalFilename();
        Assert.isTrue(!StringUtils.isEmpty(fileName), "文件名为空");
        log.info("Excel文件解析：文件名：{}", fileName);
        if (!fileName.toLowerCase().endsWith(ExcelTypeEnum.XLS.getValue())
                && !fileName.toLowerCase().endsWith(ExcelTypeEnum.XLSX.getValue())
                && !fileName.toLowerCase().endsWith(ExcelTypeEnum.CSV.getValue())) {
            throw new RuntimeException("文件格式错误，请检查是否是excel文件或csv文件");
        }
    }

    /**
     * Excel读取器
     *
     * @param excelData Excel数据
     * @return Interface to listen for read results
     */
    private static <T> ReadListener<T> readListener(List<T> excelData) {
        return new ReadListener<T>() {
            @Override
            public void invoke(T data, AnalysisContext context) {
                log.info("解析到一条数据：{}", JSONObject.toJSONString(data));
                excelData.add(data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("Excel数据解析完成");
            }
        };
    }

    /**
     * 设置文件名
     *
     * @param response HttpServletResponse
     * @param fileName 文件名
     */
    private static void encodeFileName(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, String.format("attachment;filename=\"%s\"", fileName + ".xlsx"));
        response.setHeader(HttpHeaders.CACHE_CONTROL, "no-cache");
        response.setHeader(HttpHeaders.PRAGMA, "no-cache");
        response.setDateHeader(HttpHeaders.EXPIRES, -1);
    }
}

测试说明

通用

使用POSTMAN测试导入导出
导出

controller

@RequestMapping(value = "/export", method = RequestMethod.GET)
    public void export(HttpServletResponse response, HttpServletRequest request){
        UserDTO userDTO1 = new UserDTO(1L,"name1",20,"m",new Date(),false);
        UserDTO userDTO2 = new UserDTO(2L,"name2",20,"f",new Date(),false);
        List<UserDTO> list = new ArrayList<>();
        list.add(userDTO1);
        list.add(userDTO2);

        EasyExcelUtil.writerExcel("11",response,request,list,UserDTO.class);
    }

导入

参考