Easyexcel导入，导出多个sheet到多个excel文件并压缩到一个zip文件

Easyexcel导入导出，支持导出多个sheet到一个excel文件和导出多个sheet到多个excel文件并压缩到一个zip文件

本文地址：https://www.cnblogs.com/muphy/p/16254922.html

1. 可以为每个excel文件指定模板，如果模板不存在则使用默认导出
2. 支持导出多个sheet到一个excel文件
3. 导出多个sheet到多个excel文件并压缩到一个zip文件
4. ExcelData和ExcelShellData只是为了简化参数指定文件名、模板名和数据列表、sheet列表

maven导入Easyexcel依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
            <exclusions>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml-schemas</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- excel工具 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

例子代码结构

模板内容

导出效果

• 访问 http://localhost:8080/excel/export
  
• 访问 http://localhost:8080/excel/export/zip
  

创建三个实体类数据（Excel1Sheet1和Excel1Sheet2分别是excelA的两个sheet的数据，Excel2Sheet1是excelB的数据）

• Excel1Sheet1.java

package com.example.demo.excel.entity;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;

@Data
@AllArgsConstructor
@ExcelIgnoreUnannotated
public class Excel1Sheet1 {
    @ExcelProperty(value = "用户账号", order = 2)
    private String userName;
    @ExcelProperty(value = "用户密码", order = 3)
    private String password;
}

• Excel1Sheet2.java

package com.example.demo.excel.entity;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;

@Data
@AllArgsConstructor
@ExcelIgnoreUnannotated
public class Excel1Sheet2 {
    @ExcelProperty(value = "姓名", order = 1)
    private String realName;
    @ExcelProperty(value = "毕业学校", order = 6)
    private String schoolName;
}

• Excel2Sheet1.java

package com.example.demo.excel.entity;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;

@Data
@AllArgsConstructor
@ExcelIgnoreUnannotated
public class Excel2Sheet1 {
    @ExcelProperty(value = "姓名", order = 1)
    private String realName;
    @ExcelProperty(value = "政治面貌", order = 3)
    private String politicalStatus;
}

控制层ExcelController.java

package com.example.demo.excel.controller;

import com.example.demo.excel.entity.Excel1Sheet1;
import com.example.demo.excel.entity.Excel1Sheet2;
import com.example.demo.excel.entity.Excel2Sheet1;
import com.example.demo.excel.util.ExcelUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("/excel")
public class ExcelController {

    // 导出到一个压缩文件 包含两个excel，其中一个包含两个sheet，另一个使用模块
    @GetMapping("/export/zip")
    public void exportZip(HttpServletResponse response) {
        List<ExcelUtils.ExcelData> excelData = exportAllData();
        ExcelUtils.exportZip("压缩导出", excelData, response);
    }

    // 导出到一个excel文件 包含两个sheet
    @GetMapping("/export")
    public void exportExcel(HttpServletResponse response) {
        List<ExcelUtils.ExcelData> excelData = exportAllData();
        ExcelUtils.ExcelData excelA = excelData.get(0);//只要excelA
        ExcelUtils.exportExcel(excelA, response);
    }

    // 构建数据 Excel1Sheet1和Excel1Sheet2分别是excelA的两个sheet的数据，Excel2Sheet1是excelB的数据
    public List<ExcelUtils.ExcelData> exportAllData() {
        List<ExcelUtils.ExcelData> excelDataList = new ArrayList<>();
        //excelA
        ExcelUtils.ExcelData excelA = new ExcelUtils.ExcelData();
        excelA.setFilename("excelA");
        //excelA->Excel1Sheet1
        List<Excel1Sheet1> excel1Sheet1s = new ArrayList<>();
        excel1Sheet1s.add(new Excel1Sheet1("azi", "pwd123"));
        excel1Sheet1s.add(new Excel1Sheet1("ruphy", "123456"));
        excelA.addShellData(new ExcelUtils.ExcelShellData<>(excel1Sheet1s, "Excel1Sheet1", Excel1Sheet1.class));
        //excelA->Excel1Sheet2
        List<Excel1Sheet2> excel1Sheet2s = new ArrayList<>();
        excel1Sheet2s.add(new Excel1Sheet2("阿紫", "北大"));
        excel1Sheet2s.add(new Excel1Sheet2("子安", "清华"));
        excelA.addShellData(new ExcelUtils.ExcelShellData<>(excel1Sheet2s, "Excel1Sheet2", Excel1Sheet2.class));
        excelDataList.add(excelA);

        //excelB
        ExcelUtils.ExcelData excelB = new ExcelUtils.ExcelData();
        excelB.setFilename("excelB");//设置excelB的文件名
        excelB.setTemplateFilename("excel-template/excelB.xls");//设置excelB的模板
        //excelB-Excel2Sheet1
        List<Excel2Sheet1> excel2Sheet1s = new ArrayList<>();
        excel2Sheet1s.add(new Excel2Sheet1("阿紫", "群众"));
        excel2Sheet1s.add(new Excel2Sheet1("子安", "武当会员"));
        excelB.addShellData(new ExcelUtils.ExcelShellData<>(excel2Sheet1s, "Excel2Sheet1", Excel2Sheet1.class));//设置excelB的sheet数据
        excelDataList.add(excelB);
        return excelDataList;
    }
}

导出工具类ExcelUtils.java

点击查看代码

package com.example.demo.excel.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import lombok.Data;
import org.springframework.core.io.Resource;
import org.springframework.util.StringUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * Excel相关处理
 *
 * @author 子安
 */
public class ExcelUtils {

    /**
     * 同步导入(适用于小数据量)
     *
     * @param is 输入流
     * @return 转换后集合
     */
    public static <T> List<T> importExcel(InputStream is, Class<T> clazz) {
        return EasyExcel.read(is).head(clazz).autoCloseStream(false).sheet().doReadSync();
    }

    /**
     * 导出excel
     *
     * @param list     导出数据集合
     * @param filename 工作表的名称
     * @return 结果
     */
    public static <T> void exportExcel(List<T> list, String filename, Class<T> clazz, HttpServletResponse response) {
        if (list == null) {
            list = new ArrayList<>();
        }
        try {
            response.reset();
            setAttachmentResponseHeader(response, filename);
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
            ServletOutputStream os = response.getOutputStream();
            EasyExcel.write(os, clazz).autoCloseStream(false)
                    // 自动适配
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    // 大数值自动转换 防止失真
                    .registerConverter(new ExcelBigNumberConvert()).sheet(filename).doWrite(list);
        } catch (IOException e) {
            response.reset();
            throw new RuntimeException("导出Excel异常", e);
        }
    }

    public static void exportExcel(ExcelData excelData, OutputStream os) {
        if (excelData == null) {
            throw new RuntimeException("导出数据为空！");
        }
        ExcelWriter excelWriter = null;
        try {
            ExcelWriterBuilder writerBuilder = EasyExcel.write(os).autoCloseStream(false)
                    // 自动适配
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    // 大数值自动转换 防止失真
                    .registerConverter(new ExcelBigNumberConvert());
            if (excelData.getTemplateFile() != null) {
                writerBuilder.withTemplate(excelData.getTemplateFile());
            }
            excelWriter = writerBuilder.build();
            for (int i = 0; i < excelData.getSheetDataList().size(); i++) {
                ExcelSheetData<?> sheetData = excelData.getSheetDataList().get(i);
                ExcelWriterSheetBuilder sheetBuilder;
                String sheetName = sheetData.getKey();
                if (StringUtils.isEmpty(sheetName)) {
                    sheetBuilder = EasyExcel.writerSheet(i);
                } else {
                    sheetBuilder = EasyExcel.writerSheet(sheetName);
                }
                WriteSheet sheet = sheetBuilder.build();
                Object value = sheetData.getValue();
                FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                if (value instanceof Collection) {
                    if (excelData.getTemplateFile() == null) {
                        sheetBuilder.head(sheetData.getClazz());
                        excelWriter.write((Collection<?>) value, sheet);
                    } else {
                        // 多表导出必须使用 FillWrapper
                        excelWriter.fill(new FillWrapper(sheetData.getKey(), (Collection<?>) value), fillConfig, sheet);
                    }
                } else {
                    if (excelData.getTemplateFile() != null) {
                        excelWriter.fill(value, sheet);
                    }
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("导出Excel异常", e);
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    /**
     * 导出多个sheet到一个excel文件  支持模板和直接创建
     */
    public static void exportExcel(ExcelData excelData, HttpServletResponse response) {
        if (excelData == null) {
            throw new RuntimeException("导出数据为空！");
        }
        try {
            String filename = excelData.getFilename();
            response.reset();
            setAttachmentResponseHeader(response, filename);
            ServletOutputStream os = response.getOutputStream();
            exportExcel(excelData, os);
        } catch (Exception e) {
            response.reset();
            throw new RuntimeException("导出Excel异常", e);
        }
    }

    public static void exportZip(String zipFilename, ExcelData excelData, HttpServletResponse response) {
        exportZip(zipFilename, Collections.singletonList(excelData), response);
    }

    public static void exportZip(ExcelData excelData, OutputStream os) {
        exportZip(Collections.singletonList(excelData), os);
    }

    /**
     * 导出多个sheet到多个excel文件，并压缩到一个zip文件 支持模板和直接创建
     */
    public static void exportZip(String zipFilename, List<ExcelData> excelDataList, HttpServletResponse response) {
        if (zipFilename == null || zipFilename.isEmpty()) {
            zipFilename = "export";
        } else if (zipFilename.toLowerCase(Locale.ROOT).endsWith(".zip")) {
            zipFilename = zipFilename.substring(0, zipFilename.length() - 4);
        }
        if (excelDataList == null || excelDataList.isEmpty()) {
            throw new RuntimeException("导出数据为空！");
        }
        try {
            // 这里URLEncoder.encode可以防止中文乱码
            zipFilename = URLEncoder.encode(zipFilename, "utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + zipFilename + ".zip");
            response.setContentType("application/x-msdownload");
            response.setCharacterEncoding("utf-8");
            //开始存入
            ServletOutputStream os = response.getOutputStream();
            exportZip(excelDataList, os);
        } catch (Exception e) {
            response.reset();
            throw new RuntimeException("导出Excel异常", e);
        }
    }

    public static void exportZip(List<ExcelData> excelDataList, OutputStream os) {
        if (excelDataList == null || excelDataList.isEmpty()) {
            throw new RuntimeException("导出数据为空！");
        }
        try {
            //开始存入
            try (ZipOutputStream zipOut = new ZipOutputStream(os)) {
                try {
                    for (ExcelData excelData : excelDataList) {
                        zipOut.putNextEntry(new ZipEntry(excelData.getFilename()));
                        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
                        try {
                            exportExcel(excelData, outputStream);
                        } catch (Exception e) {
                            throw new RuntimeException("导出Excel异常", e);
                        }
                        outputStream.writeTo(zipOut);
                        zipOut.closeEntry();
                    }
                } catch (Exception e) {
                    throw new RuntimeException("导出Excel异常", e);
                }
            }
        } catch (IOException e) {
            throw new RuntimeException("导出Excel异常", e);
        }
    }

    /**
     * excel数据
     */
    @Data
    public static class ExcelData {
        private List<ExcelSheetData<?>> sheetDataList = new ArrayList<>();
        private String filename = "export.xlsx";
        private String templateFilename;

        public File getTemplateFile() {
            if (templateFilename == null) {
                return null;
            }
            File templateFile = new File(templateFilename);
            if (templateFile.exists() && templateFile.isFile()) {
                return templateFile;
            }
            Resource resource = new org.springframework.core.io.ClassPathResource(templateFilename);
            if (resource.exists()) {
                File file = null;
                try {
                    file = resource.getFile();
                } catch (IOException e) {
                    e.printStackTrace();
                }
                if (file != null) {
                    if (file.exists() && file.isFile()) {
                        return file;
                    }
                    return null;
                }
            }
            return null;
        }

        public void setSheetDataList(List<ExcelSheetData<?>> sheetDataList) {
            if (sheetDataList != null) {
                this.sheetDataList = sheetDataList;
            }
        }

        public String getFilename() {
            if (filename == null || filename.isEmpty()) {
                filename = "export.xlsx";
            } else {
                String fn = filename.toLowerCase(Locale.ROOT);
                if (!(fn.endsWith(".xlsx") || fn.endsWith(".xls"))) {
                    filename = filename + ".xlsx";
                }
            }
            return filename;
        }

        public void addSheetData(ExcelSheetData<?> excelSheetData) {
            this.sheetDataList.add(excelSheetData);
        }
    }

    /**
     * sheet数据
     */
    @Data
    public static class ExcelSheetData<T> {
        //        private List<T> list;
        private Object value;
        private String key;
        private Class<T> clazz;

        //public ExcelSheetData(List<T> list, String sheetName, Class<T> clazz) {
        public ExcelSheetData(Object value, String key, Class<T> clazz) {
            this.value = value;
            this.key = key;
            this.clazz = clazz;
        }

    }

    private static void setAttachmentResponseHeader(HttpServletResponse response, String filename) throws UnsupportedEncodingException {
        String encode = URLEncoder.encode(filename, StandardCharsets.UTF_8.toString());
        String percentEncodedFileName = encode.replaceAll("\\+", "%20");
        String contentDispositionValue = "attachment; filename=" +
                percentEncodedFileName + ";filename*=utf-8''" +
                percentEncodedFileName;

        response.addHeader("Access-Control-Expose-Headers", "Content-Disposition,download-filename");
        response.setHeader("Content-disposition", contentDispositionValue);
        response.setHeader("download-filename", percentEncodedFileName);
    }

    /**
     * Excel 数值长度位15位 大于15位的数值转换位字符串
     */
    public static class ExcelBigNumberConvert implements Converter<Long> {

        @Override
        public Class<Long> supportJavaTypeKey() {
            return Long.class;
        }

        @Override
        public CellDataTypeEnum supportExcelTypeKey() {
            return CellDataTypeEnum.STRING;
        }

        @Override
        public Long convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
            Object data = cellData.getData();
            if (data == null) {
                return null;
            }
            String s = String.valueOf(data);
            if (s.matches("^\\d+$")) {
                return Long.parseLong(s);
            }
            return null;
        }

        @Override
        public WriteCellData<Object> convertToExcelData(Long object, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
            if (object != null) {
                String str = object.toString();
                if (str.length() > 15) {
                    return new WriteCellData<>(str);
                }
            }
            WriteCellData<Object> cellData = new WriteCellData<>(new BigDecimal(object));
            cellData.setType(CellDataTypeEnum.NUMBER);
            return cellData;
        }

    }

}