SpringBoot EasyExcel导入与导出

easyExcel 导入与导出详

环境：SpringBoot 2.+

1 依赖包

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.4</version>
        </dependency>

2 导入

excel数据如下

2.1 无模板

    /**
     * easyExcel 无模板导入解析
     *
     * @param excelFile
     * @return
     * @throws IOException
     */
    @PostMapping("/readNoEntity")
    public ResponseEntity noEntity(@RequestParam("file") MultipartFile excelFile) throws IOException {
        ResponseEntity responseEntity = new ResponseEntity();
         // sheet 读取第几个sheet的数据,索引从0开始
        List<Object> list = EasyExcel.read(excelFile.getInputStream()).sheet(0).doReadSync();
        responseEntity.setData(list);
        return responseEntity;
    }

结果

2.2 有模板

• 模板 ExcelProperty注解对应excel第一列名

@Data
public class EasyExcelTestDto {

    @ExcelProperty(value = "姓名")
    private String name;

    @ExcelProperty(value = "性别")
    private String sex;

    @ExcelProperty(value = "年龄")
    private int age;
}

    /**
     * easyExcel 有模板导入解析
     *
     * @param excelFile
     * @return
     * @throws IOException
     */
    @PostMapping("/readToEntity")
    public ResponseEntity toEntity(@RequestParam("file") MultipartFile excelFile) throws IOException {
        ResponseEntity responseEntity = new ResponseEntity();
        List<EasyExcelTestDto> list = EasyExcel.read(excelFile.getInputStream(), EasyExcelTestDto.class, null).sheet(0).doReadSync();
        responseEntity.setData(list);
        return responseEntity;
    }

• 结果
• 

2.3 自定义监听

• 继承AnalysisEventListener

    public class ExcelListener extends AnalysisEventListener<EasyExcelTestDto> {

        public List<EasyExcelTestDto> dataList = new ArrayList<>();

        // 每读取一行回调当前方法
        @Override
        public void invoke(EasyExcelTestDto easyExcelTestDto, AnalysisContext analysisContext) {
            logger.info("read item: {}", easyExcelTestDto);
            dataList.add(easyExcelTestDto);
        }

        // excel数据读取完毕回调
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            logger.info("{}条数据，开始存储数据库！", dataList.size());
            logger.info("所有数据解析完成！");
        }

        public List<EasyExcelTestDto> getDataList() {
            return dataList;
        }
    }

    @PostMapping("/readToEntityListener")
    public ResponseEntity toEntityListener(@RequestParam("file") MultipartFile excelFile) throws IOException {
        ResponseEntity responseEntity = new ResponseEntity();
        // 每次执行,listener必须重新定义new
        ExcelListener listener = new ExcelListener();
        EasyExcel.read(excelFile.getInputStream(), EasyExcelTestDto.class, listener).sheet(0).doRead();
        responseEntity.setData(listener.getDataList());
        return responseEntity;
    }

这里使用的是ExcelReaderSheetBuilder类的doRead()方法。doRead()和doReadSync()区别在于doReadSync()里面配了一个自定义的监听，并且返回读取到excel数据的List集合

• 结果
  
  

2.4 多个sheet的读取方法

    @RequestMapping("/readSheetToEntity")
    public ResponseEntity readSheetToEntity(@RequestParam("file") MultipartFile excelFile) throws IOException {
        ResponseEntity responseEntity = new ResponseEntity();
        List<List<EasyExcelTestDto>> resultList = new ArrayList<>();
        responseEntity.setData(resultList);
        ExcelListener listener = new ExcelListener();
        ExcelReaderBuilder builder = EasyExcel.read(excelFile.getInputStream(), EasyExcelTestDto.class, listener);
        ExcelReader reader = builder.build();
        //sheet集合
        List<ReadSheet> sheets = reader.excelExecutor().sheetList();
        for (ReadSheet sheet : sheets) {
            // 共用监听器,解析之前需要清空
            listener.getDataList().clear();
            //读取每一个sheet的内容
            logger.info("sheet name:{}", sheet.getSheetName());
            reader.read(sheet);
            List<EasyExcelTestDto> current = listener.getDataList();
            resultList.add(current);
            logger.info("content:{}", current);
        }
        reader.finish();
        return responseEntity;
    }

结果

3 导出

3.1 无模板

• 代码

 @RequestMapping("/download")
    public void download(HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 随机生成文件名
        String fileName = UUID.randomUUID().toString();
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        // sheet(0) 参数表示sheet名称
        EasyExcel.write(response.getOutputStream()).sheet(0).doWrite(data());
    }

    private List<?> data() {
        List<List<String>> rowList = new ArrayList<>();
        List<String> cell = new ArrayList<>();
        cell.add("cell1");
        cell.add("cell2");
        cell.add("cell3");
        List<String> cell2 = new ArrayList<>();
        cell2.add("中文");
        cell2.add("大哥");
        cell2.add("cell23");
        cell2.add("cell24");
        rowList.add(cell);
        rowList.add(cell2);
        return rowList;
    }

• 结果
  

3.2 有模板

• 代码

 /**
     * 有模板
     *
     * @param response
     * @throws Exception
     */
    @RequestMapping("/downloadTemplate")
    public void downloadTemplate(HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = UUID.randomUUID().toString();
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 头部背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(response.getOutputStream(), EasyExcelTestExportDto.class).sheet("sheet1").registerWriteHandler(horizontalCellStyleStrategy).doWrite(dataTemplate());
    }

    protected List<?> dataTemplate() {
        List<EasyExcelTestExportDto> rowList = new ArrayList<>();
        EasyExcelTestExportDto cell = new EasyExcelTestExportDto();
        EasyExcelTestExportDto cell2 = new EasyExcelTestExportDto();
        cell.setName("name1");
        cell.setSex("sex1");
        cell.setAge(1);
        cell2.setName("name2");
        cell2.setSex("sex");
        cell2.setAge(2);

        rowList.add(cell);
        rowList.add(cell2);
        return rowList;
    }

@Data
public class EasyExcelTestExportDto {

    @ExcelProperty(value = "姓名")
    private String name;

    @ExcelProperty(value = "性别")
    private String sex;

    @ExcelProperty(value = "年龄")
    private int age;
}

• 结果
  

4 复杂模板导出

4.1 模板样式

注意：模板放在resources资源文件下

4.2 单元测试

4.3 导出结果

4.4 工具类-EasyExcelUtil

本工具类可根据自身需求进行调整

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.map.LinkedMap;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

public class EasyExcelUtil {

    public static final Logger logger = LoggerFactory.getLogger(EasyExcelUtil.class);

    /**
     * @param cellMap  LinkedMap<String, String>  key List存储对象成员变量,value excel头部
     * @param dataList List excel 内容体
     * @throws Exception
     */
    public static void dataExport(LinkedMap<String, String> cellMap, List dataList, HttpServletResponse response) throws Exception {

        try {
            // 获取excel翻译文件内的Map数据
            List<List<String>> cellList = new ArrayList<>();
            // excel头部数据
            cellList.add(handleHeaderData(cellMap));
            // excel 内容数据
            handleContent(cellList, cellMap, dataList);
            download(cellList, response);
        } catch (Exception e) {
            throw e;
        }
    }

    /**
     * @param cellMap
     * @param dataList
     * @param fileName
     * @throws Exception
     */
    public static void dataExport(LinkedMap<String, String> cellMap, List dataList, String fileName, HttpServletResponse response) throws Exception {

        try {
            // 获取excel翻译文件内的Map数据
            List<List<String>> cellList = new ArrayList<>();
            // excel头部数据
            cellList.add(handleHeaderData(cellMap));
            // excel 内容数据
            handleContent(cellList, cellMap, dataList);
            download(cellList, fileName, response);
        } catch (Exception e) {
            throw e;
        }
    }

    /**
     * excel头部数据
     *
     * @param cellMap
     * @return
     */
    private static List<String> handleHeaderData(LinkedMap<String, String> cellMap) {
        List<String> resultList = new ArrayList<>();
        for (String key : cellMap.keySet()) {
            resultList.add(cellMap.get(key));
        }
        return resultList;
    }

    /**
     * excel 内容数据封装
     *
     * @param cellList
     * @param cellMap
     * @param dataList
     * @return
     */
    private static List<List<String>> handleContent(List<List<String>> cellList, LinkedMap<String, String> cellMap, List<?> dataList) {
        if (CollectionUtils.isEmpty(dataList)) {
            return cellList;
        }
        cellList.addAll(
                dataList.stream().map(dto -> {
                    List<String> resultList = new ArrayList<>();
                    for (String key : cellMap.keySet()) {
                        Class clazz = dto.getClass();
                        // 无该方法,赋空值
                        try {
                            Method method = clazz.getDeclaredMethod("get" + key.substring(0, 1).toUpperCase() + key.substring(1));
                            resultList.add(method.invoke(dto).toString());
                        } catch (Exception e) {
                            resultList.add("");
                        }
                    }
                    return resultList;
                }).collect(Collectors.toList()));

        return cellList;

    }

    // 下载随机文件名
    private static void download(List<List<String>> cellData, HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 随机生成文件名
        String fileName = UUID.randomUUID().toString();
        response.setHeader("Content-Disposition", "attachment;filename*= UTF-8''" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 头部背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(response.getOutputStream()).sheet("sheet").registerWriteHandler(horizontalCellStyleStrategy).doWrite(cellData);

    }

    // 下载设置文件名
    private static void download(List<List<String>> cellData, String fileName, HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 随机生成文件名
        if (StringUtils.isEmpty(fileName)) {
            fileName = UUID.randomUUID().toString();
        }
        response.setHeader("Content-Disposition", "attachment;filename*= UTF-8''" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));

        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 头部背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(response.getOutputStream()).sheet("sheet").registerWriteHandler(horizontalCellStyleStrategy).doWrite(cellData);
    }

    /**
     * 获取excel导出模板文件
     *
     * @param excelTemplatePath resources目录下路径
     * @return
     */
    public static InputStream getResourcesFileByPath(String excelTemplatePath) {
        ClassPathResource resource = new ClassPathResource(excelTemplatePath);
        try {
            return resource.getInputStream();
        } catch (Exception e) {
            logger.error("error {}", e);
        }
        return null;
    }

    /**
     * @param templatePath 导出模板路径,必须在resource路径下
     * @param mapData      map
     * @param listData     list
     * @param response
     * @throws Exception
     */
    public static void dataExportByTemplate(String templatePath, Map<String, String> mapData, List listData, HttpServletResponse response) throws Exception {

        InputStream templateInputStream = getResourcesFileByPath(templatePath);
        if (templateInputStream == null) {
            throw new Exception("excel模板不存在:" + templatePath);
        }
        try {
            // 下载,数据填充
            httpDownloadByTemplate(mapData, listData, templateInputStream, null, response);
        } catch (Exception e) {
            throw e;
        } finally {
            templateInputStream.close();
        }

    }

    /**
     * @param templatePath 导出模板路径,必须在resource路径下
     * @param mapData      map
     * @param listData     list
     * @param fileName     导出文件名
     * @param response
     * @throws Exception
     */
    public static void dataExportByTemplate(String templatePath, Map<String, String> mapData, List listData, String fileName, HttpServletResponse response) throws Exception {

        InputStream templateInputStream = getResourcesFileByPath(templatePath);
        if (templateInputStream == null) {
            throw new Exception("excel模板不存在:" + templatePath);
        }
        // 下载,数据填充
        httpDownloadByTemplate(mapData, listData, templateInputStream, fileName, response);
    }


    /**
     * @param mapData     其他填充数据
     * @param listData    列表数据
     * @param inputStream 模板文件路径
     * @param fileName    文件名称
     * @param response    输出六
     * @throws Exception
     */
    public static void httpDownloadByTemplate(Map<String, String> mapData, List listData, InputStream inputStream, String fileName, HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 随机生成文件名
        if (StringUtils.isEmpty(fileName)) {
            fileName = UUID.randomUUID().toString();
        }
        response.setHeader("Content-Disposition", "attachment;filename*= UTF-8''" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
        //读取excel
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(inputStream).build();
        // 第一个sheet
        WriteSheet writeSheet = EasyExcel.writerSheet(0).build();

        if (Objects.nonNull(mapData)) {
            excelWriter.fill(mapData, writeSheet);
        }
        if (CollectionUtils.isNotEmpty(listData)) {
            excelWriter.fill(listData, writeSheet);
        }
        excelWriter.finish();

    }

}