java导出Excel文件

最新推荐文章于 2024-07-12 13:37:03 发布

闫小生

最新推荐文章于 2024-07-12 13:37:03 发布

阅读量478

点赞数 15

文章标签： java excel 开发语言

本文链接：https://blog.csdn.net/m0_68207804/article/details/140181444

版权

一：集成POI

1、Apache POI

版本建议4.1.2及以上。

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

由于poi高版本对低版本并不兼容，所以可能会出现低版本中的某些类，在升级后的高版本中找不到，所以低版本升级时，需考虑对系统的影响面。

2、easyexcel

<!-- 阿里巴巴读取excel工具包 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.0-beta1</version>
    <exclusions>
        <exclusion>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
        </exclusion>
        <exclusion>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
        </exclusion>
        <exclusion>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
        </exclusion>
    </exclusions>
</dependency>

<!--easypoi-base 导入导出的工具包 -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.1.3</version>
    <exclusions>
        <exclusion>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
        </exclusion>
        <exclusion>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
        </exclusion>
        <exclusion>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
        </exclusion>
    </exclusions>
</dependency>
<dependency>
    <groupId>org.jsoup</groupId>
    <artifactId>jsoup</artifactId>
    <version>1.8.3</version>
</dependency>

如果本身pom文件中，有引入poi，需要使用exclusion，排除easyExcel自身引入的poi，防止冲突。更习惯于手动引入一下poi，这样可以更加清晰的看到对应的版本。

二：Excel代码

测试工具类：ExcelUtil.java

package com.util;

import cn.afterturn.easypoi.excel.ExcelXorHtmlUtil;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.security.SecureRandom;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.concurrent.ArrayBlockingQueue;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

@Slf4j
public class ExcelUtil {

    public static final String INCLINED_ROD = "/";

    private static Sheet initSheet;

    static {
        initSheet = new Sheet(1, 0);
        initSheet.setSheetName("sheet");
        //设置自适应宽度
        initSheet.setAutoWidth(Boolean.TRUE);
    }

    public static class SheetBean<T> {
        private Map<String, Object> beanMap;

        private List<T> beanList;

        public Map<String, Object> getBeanMap() {
            return beanMap;
        }

        public void setBeanMap(Map<String, Object> beanMap) {
            this.beanMap = beanMap;
        }

        public List<T> getBeanList() {
            return beanList;
        }

        public void setBeanList(List<T> beanList) {
            this.beanList = beanList;
        }
    }

    public static class ExcelListener extends AnalysisEventListener {
        private List<Object> data = new ArrayList<>();
        //逐行解析，object : 当前行的数据
        @Override
        public void invoke(Object object, AnalysisContext context) {
            //当前行 context.getCurrentRowNum()
            if (object != null) {
                data.add(object);
            }
        }
        //解析完所有数据后会调用该方法
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            //解析结束销毁不用的资源
        }
        public List<Object> getData() {
            return data;
        }
        public void setData(List<Object> data) {
            this.data = data;
        }
    }

    /**
     * 设置返回前端文件名
     * @param response response
     * @param fileName 文件名,包含后缀
     * @return OutputStream
     * @throws Exception Exception
     */
    public static OutputStream getOutputStreamFileName(HttpServletResponse response, String fileName) throws Exception{
        response.reset();
        String fileType = fileName.split("\\.")[1].toLowerCase();
        switch (fileType){
            case "doc":
                response.setContentType("application/msword");//设置生成的文件类型
                break;
            case "docx":
                response.setContentType("application/msword");//设置生成的文件类型
                break;
            case "xls":
                response.setContentType("application/vnd.ms-excel");//设置生成的文件类型
                break;
            case "xlsx":
                response.setContentType("application/vnd.ms-excel");//设置生成的文件类型
                break;
            case "pdf":
                response.setContentType("application/pdf");//设置生成的文件类型
                break;
            case "zip":
                response.setContentType("application/zip");//设置生成的文件类型
                break;
            case "dbf":
                response.setContentType("application/x-dbf");//设置生成的文件类型
                break;
            default:
                return response.getOutputStream();
        }
        response.setCharacterEncoding("UTF-8");//设置文件头编码方式和文件名
        response.setHeader("Content-Disposition", "attachment;filename=" +
                new String(URLEncoder.encode(fileName, "UTF-8").getBytes("utf-8"), "ISO8859-1"));
        return response.getOutputStream();
    }

    /**
     * 按指定大小，分隔集合，将集合按规定个数分为n个部分
     * @param list 集合
     * @param len 拆分个数
     * @param <T> 泛型
     * @return List<List<T>>
     */
    public static <T> List<List<T>> splitList(List<T> list, int len) {
        if (list == null || list.isEmpty() || len < 1) {
            return Collections.emptyList();
        }
        List<List<T>> result = new ArrayList<>();
        int size = list.size();
        int count = (size + len - 1) / len;
        for (int i = 0; i < count; i++) {
            List<T> subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));
            result.add(subList);
        }
        return result;
    }

    /**
     * 获取文件输出流
     * @param filePath 文件路径，不需加/
     * @param fileName 文件名称
     * @return 输出流
     * @throws FileNotFoundException file not found
     */
    public static OutputStream getFileOutputStream(String filePath, String fileName) throws FileNotFoundException {
        return new FileOutputStream(filePath + INCLINED_ROD + fileName);
    }

    /**
     * 获取文件输出流
     * @param filePathAndName 文件路径+文件名称
     * @return 输出流
     * @throws FileNotFoundException file not found
     */
    public static OutputStream getFileOutputStream(String filePathAndName) throws FileNotFoundException {
        return new FileOutputStream(filePathAndName);
    }

    /**
     * 生成Excel表格
     * @param outputStream 流
     * @param data 数据
     * @param head 表头
     */
    public static void writeExcelByList(OutputStream outputStream, List<List<Object>> data, List<String> head){
        writeExcelByList(outputStream, data, head,null);
    }

    /**
     * 生成Excel表格，生成文件
     * @param filePath 文件路径
     * @param fileName 文件名称
     * @param data 数据
     * @param head 表头
     */
    public static void writeExcelByList(String filePath, String fileName, List<List<Object>> data, List<String> head) throws Exception{
        writeExcelByList(getFileOutputStream(filePath, fileName), data, head,null);
    }

    /**
     * 生成Excel表格
     * @param outputStream 流
     * @param data 数据
     * @param head 表头
     * @param sheet sheet
     */
    public static void writeExcelByList(OutputStream outputStream, List<List<Object>> data, List<String> head, Sheet sheet){
        sheet = (sheet != null) ? sheet : initSheet;
        if(head != null){
            List<List<String>> list = new ArrayList<>();
            head.forEach(h -> list.add(Collections.singletonList(h)));
            sheet.setHead(list);
        }
        ExcelWriter writer = null;
        try {
            writer = EasyExcelFactory.getWriter(outputStream);
            writer.write1(data,sheet);
        } catch (Exception e) {
            log.error("Excel File Generation Failed", e);
        } finally {
            try {
                if(writer != null){
                    writer.finish();
                }
                if(outputStream != null){
                    outputStream.close();
                }
            } catch (IOException e) {
                log.error("Stream Close Failed", e);
            }
        }
    }

    /**
     * 生成压缩后的Excel压缩包，单线程拆分压缩导出
     * @param response 用于输入zip输出流
     * @param filePath 文件在服务器生成的路径，临时路径，文件会删除
     * @param fileName 文件生成的名称
     * @param data excel文件的表格内容
     * @param head excel文件的表头内容
     * @param splitCount 列表切分阈值
     * @throws Exception 异常
     */
    public static void writeExcelZipByList(HttpServletResponse response, String filePath, String fileName, String fileType,
                                           List<List<Object>> data, List<String> head, Integer splitCount) throws Exception{
        List<List<List<Object>>> lists = splitList(data, splitCount);
        filePath = filePath + INCLINED_ROD + DateFormatUtils.format(new Date(), "yyyyMMddHHmmssSSS") +
                (new SecureRandom().nextInt(1000));
        for(int i = 0; i < lists.size(); i++){
            createFilePath(filePath);
            OutputStream fileOutputStream = getFileOutputStream(filePath, (fileName + "(" + i + ")" + "." + fileType));
            writeExcelByList(fileOutputStream, lists.get(i), head, null);
        }
        zipDateFile(response, filePath, fileName + ".zip", true);
        deleteFilePath(new File(filePath));
    }

    /**
     * 生成压缩后的Excel压缩包，使用多线程拆分压缩导出
     * @param response 用于输入zip输出流
     * @param filePath 文件在服务器生成的路径，临时路径，文件会删除
     * @param fileName 文件生成的名称
     * @param data excel文件的表格内容
     * @param head excel文件的表头内容
     * @param splitCount 列表切分阈值
     * @throws Exception 异常
     */
    public static void writeExcelZipByListThread(HttpServletResponse response, String filePath, String fileName, String fileType,
                                           List<List<Object>> data, List<String> head, Integer splitCount) throws Exception {
        List<List<List<Object>>> lists = splitList(data, splitCount);
        final String actualFilePath = filePath + INCLINED_ROD + DateFormatUtils.format(new Date(), "yyyyMMddHHmmssSSS") +
                (new SecureRandom().nextInt(1000));
        Thread thread = new Thread(() -> {
            new ExcelUtil().writeExcelPool(actualFilePath, fileName, fileType, lists, head);
        });
        thread.start();
        thread.join();
        zipDateFile(response, actualFilePath, fileName + ".zip", true);
        deleteFilePath(new File(actualFilePath));
    }

    //批量生成文件线程池
    private void writeExcelPool(String filePath, String fileName, String fileType, List<List<List<Object>>> lists, List<String> head) {
        Integer basePoolSize = lists.size();
        ExecutorService pool = new ThreadPoolExecutor(basePoolSize, 2 * basePoolSize, basePoolSize, TimeUnit.MILLISECONDS,
                new ArrayBlockingQueue<Runnable>(1), Executors.defaultThreadFactory(), new ThreadPoolExecutor.CallerRunsPolicy());
        for(int i = 0; i < lists.size(); i++){
            pool.execute(new writeExcelThread(i, filePath, fileName, fileType, lists.get(i), head));
        }
        try {
            pool.shutdown();
            boolean loop = true;
            do {
                loop = !pool.awaitTermination(200, TimeUnit.MILLISECONDS);
            } while (loop);
        } catch (InterruptedException e) {
            log.error("Current Thread Pool Error", e);
        }
    }

    //生成文件线程
    private class writeExcelThread implements Runnable {
        private Integer index;
        private String filePath;
        private String fileName;
        private String fileType;
        private List<List<Object>> list;
        private List<String> head;

        private writeExcelThread(Integer index, String filePath, String fileName, String fileType, List<List<Object>> list, List<String> head) {
            this.index = index;
            this.filePath = filePath;
            this.fileName = fileName;
            this.fileType = fileType;
            this.list = list;
            this.head = head;
        }

        @Override
        public void run() {
            try {
                createFilePath(filePath);
                OutputStream fileOutputStream = getFileOutputStream(filePath, (fileName + "(" + index + ")" + "." + fileType));
                writeExcelByList(fileOutputStream, list, head, null);
            } catch (Exception e){
                log.error("Current Thread Write Excel Error", e);
            }
        }
    }

    /**
     * 生成错误Excel表格
     * @param outputStream 流
     * @param data 数据
     * @param head 表头
     * @throws IOException 文件生成IO异常
     */
    @SuppressWarnings("unchecked")
    public static void writeExcelByError(OutputStream outputStream, List<List<Object>> data, List<Object> head) throws IOException{
        Workbook workbook = new XSSFWorkbook();
        org.apache.poi.ss.usermodel.Sheet sheet = (org.apache.poi.ss.usermodel.Sheet) workbook.createSheet("sheet");
        if (data != null) {
            CreationHelper factory = workbook.getCreationHelper();
            data.add(0, head);
            //遍历行数据
            for (int i = 0; i < data.size(); i++) {
                Row row = sheet.createRow(i);
                List<Object> columns = data.get(i);
                Integer columnsSize = columns.size();
                Map<Integer,String> map = null;
                if(i > 0){
                    columnsSize = columnsSize - 1;
                    map = (Map<Integer, String>) columns.get(columns.size() - 1);
                }
                //遍历列数据
                for (int j = 0; j < columnsSize; j++) {
                    Cell cell = row.createCell(j);
                    Object columnsObject = columns.get(j);
                    XSSFCellStyle bodyStyle = (XSSFCellStyle) workbook.createCellStyle();
                    bodyStyle.setBorderBottom(BorderStyle.THIN);//下边框
                    bodyStyle.setBorderLeft(BorderStyle.THIN);//左边框
                    bodyStyle.setBorderRight(BorderStyle.THIN);//右边框
                    bodyStyle.setBorderTop(BorderStyle.THIN);//上边框
                    bodyStyle.setWrapText(true);//自动换行
                    bodyStyle.setAlignment(HorizontalAlignment.CENTER); // 垂直居中
                    bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);//水平居中
                    if(i == 0){
                        org.apache.poi.ss.usermodel.Font font = workbook.createFont();
                        font.setBold(true);
                        font.setFontHeightInPoints((short) 14);
                        bodyStyle.setFont(font);//设置标题字体放大
                    }
                    if(null != map && null != map.get(j)){//当前列有错误信息
                        bodyStyle.setFillBackgroundColor(IndexedColors.YELLOW.index);//设置背景颜色
                        bodyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置前景填充样式
                        bodyStyle.setFillForegroundColor(IndexedColors.RED.getIndex());//前景填充
                        Comment comment = sheet.createDrawingPatriarch().createCellComment(factory.createClientAnchor());
                        RichTextString richTextString = factory.createRichTextString(map.get(j));
                        comment.setString(richTextString);//添加批注
                        comment.setAuthor("admin");//添加作者
                        cell.setCellComment(comment);
                    }
                    cell.setCellStyle(bodyStyle);
                    if(null == columnsObject || "".equals(columnsObject)) {
                        continue;
                    }
                    cell.setCellValue(columnsObject.toString());//设置单元格的值
                    byte[] columnsByte = columnsObject.toString().getBytes();
                    if(columnsByte.length > 8 && columnsByte.length < 255){// 设置单元格宽度
                        if(((columnsByte.length) * 256) > sheet.getColumnWidth(j)){
                            sheet.setColumnWidth(j, (columnsByte.length) * 256);
                        }
                    } else if(columnsByte.length >= 255){
                        sheet.setColumnWidth(j, 255 * 256);
                    }
                }
            }
        }
        workbook.write(outputStream);
    }

    /**
     * 根据html元素导出文件
     * 注意事项：
     * 1、使用该方法需要引入pom。groupId：org.jsoup；artifactId：jsoup；version:1.8.3或以上版本
     * 2、html字符串表格需要配置属性。sheetname=""，配置的内容，即导出的Excel中sheet名称
     * html 参考参数
     <table sheetname="sheet名称" style="table-layout:fixed; word-wrap:break-word; word-break:break-all">
     <thead> <tr>
     <th style="text-align: center; width:33%;">序号</th>
     <th style="text-align: center; width:33%;">编号</th>
     <th style="text-align: center; width:34%;">名称</th>
     </tr> </thead>
     <tbody> <tr> <td style="text-align: center; width:33%;">id</td>
     <td style="text-align: center; width:33%;">code</td>
     <td style="text-align: center; width:34%;">name</td>
     </tr> </tbody>
     </table>
     * @param response response
     * @param fileName 文件名
     * @param html html元素
     * @throws Exception Exception
     */
    public static void writeExcelByHtml(HttpServletResponse response, String fileName, String html) throws Exception{
        OutputStream outputStream = getOutputStreamFileName(response, fileName);
        Workbook workbook = ExcelXorHtmlUtil.htmlToExcel(html, ExcelType.HSSF);
        workbook.write(outputStream);
    }

    /**
     * 导出Excel单模板单元素填充
     * @param templateFile 模板地址加模板名称
     * @param out 流
     * @param data 单数据
     */
    public static void writeExcelByTemplate(String templateFile, OutputStream out, Object data){
        EasyExcel.write(out).withTemplate(templateFile).sheet().doFill(data);
    }

    /**
     * 导出Excel单模板单元素和多列混合
     * @param templateFile 模板地址加模板名称
     * @param out 流
     * @param data Object 对象值，字段名与模板匹配
     * @param dataList 列表集合
     */
    public static <T> void writeExcelByTemplate(String templateFile, OutputStream out, Object data, List<T> dataList){
        ExcelWriter build = EasyExcel.write(out).withTemplate(templateFile).build();
        WriteSheet sheet = EasyExcel.writerSheet().build();
        build.fill(dataList, sheet).fill(data, sheet).finish();
    }

    /**
     * 导出Excel单模板单元素和多列混合
     * @param templateFile 模板地址加模板名称
     * @param out 流
     * @param data 单元素，Key：配置的元素，Object：数据值
     * @param dataList 列表集合
     */
    public static <T> void writeExcelByTemplate(String templateFile, OutputStream out, Map<String, Object> data, List<T> dataList){
        ExcelWriter build = EasyExcel.write(out).withTemplate(templateFile).build();
        WriteSheet sheet = EasyExcel.writerSheet().build();
        build.fill(dataList, sheet).fill(data, sheet).finish();
    }

    /**
     * 导出Excel单个和多列混合,移动行填充
     * （模板附带表头表尾，中间列表自动扩充）
     * @param templateFile 模板地址加模板名称
     * @param out 流
     * @param data 单元素，Key：配置的元素，Object：数据值
     * @param dataList 列表集合
     */
    public static <T> void writeExcelByTemplateRows(String templateFile, OutputStream out, Map<String,Object> data, List<T> dataList){
        ExcelWriter build = EasyExcel.write(out).withTemplate(templateFile).build();
        WriteSheet sheet = EasyExcel.writerSheet().build();
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        build.fill(dataList, fillConfig, sheet).fill(data,sheet).finish();
    }

    /**
     * 根据模板导出多个sheet页面
     * 说明：该方法，data和dataList，元素的list下标需对应。即大小需相等
     * @param templateFile 模板地址
     * @param out 输出流
     * @param data 单个填充数据源
     * @param dataList 便利填充数据源
     * @param sheetNum sheet页数量
     * @param flag 是否复制填充
     */
    public static void writeExcelByTemplate(String templateFile, OutputStream out, List<Map<String,Object>> data,
                                                      List<List<Object>> dataList, int sheetNum, boolean flag){
        //根据模板构造输出Excel
        ExcelWriter build = EasyExcel.write(out).withTemplate(templateFile).build();
        // 通过上下文获取excel对象,不能使用原始对象，SXSSF poi中没有实现copy方法
        WriteWorkbookHolder writeWorkbookHolder = build.writeContext().writeWorkbookHolder();
        Workbook workbook = build.writeContext().writeWorkbookHolder().getCachedWorkbook();
        //copy sheet页,排除本模板中已有的页码
        for(int i = 0; i < (sheetNum -1); i++){
            workbook.cloneSheet(i);
        }
        //更新workbook
        writeWorkbookHolder.setWorkbook(workbook);
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        for(int i = 0; i < sheetNum; i++){
            Map<String, Object> cellMap = data.get(i);
            List<Object> fillList = dataList.get(i);
            WriteSheet sheet = EasyExcel.writerSheet(i).build();
            sheet.setSheetName(String.valueOf(i + 1));
            if(flag) {
                build.fill(fillList, fillConfig, sheet);
            } else {
                build.fill(fillList, sheet);
            }
            build.fill(cellMap, sheet);
        }
        build.finish();
    }

    /**
     * 根据模板导出多个sheet页面
     * @param templateFile 模板地址
     * @param out 输出流
     * @param map 单个填充数据源 Key从0开始
     * @param flag 是否复制填充
     */
    public static void writeExcelByTemplate(String templateFile, OutputStream out, Map<Integer, SheetBean> map, boolean flag){
        //根据模板构造输出Excel
        ExcelWriter build = EasyExcel.write(out).withTemplate(templateFile).build();
        // 通过上下文获取excel对象,不能使用原始对象，SXSSF poi中没有实现copy方法
        WriteWorkbookHolder writeWorkbookHolder = build.writeContext().writeWorkbookHolder();
        Workbook workbook = build.writeContext().writeWorkbookHolder().getCachedWorkbook();
        //copy sheet页,排除本模板中已有的页码
        if(null == map || map.size() == 0) return;
        for(int i = 0; i < (map.size() -1); i++){
            workbook.cloneSheet(i);
        }
        //更新workbook
        writeWorkbookHolder.setWorkbook(workbook);
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        int sheetNo = 0;
        for (Map.Entry<Integer, SheetBean> entry : map.entrySet()) {
            SheetBean sheetBean = entry.getValue();
            Map cellMap = sheetBean.getBeanMap();
            List fillList = sheetBean.getBeanList();
            WriteSheet sheet = EasyExcel.writerSheet(sheetNo).build();
            sheet.setSheetName(entry.getKey().toString());
            if(flag) {
                build.fill(fillList, fillConfig, sheet);
            } else {
                build.fill(fillList, sheet);
            }
            build.fill(cellMap, sheet);
            sheetNo++;
        }
        build.finish();
    }

    /**
     * 读取少于1000行数据
     * @param inputStream 流
     * @return List<Object>
     */
    public static List<Object> readLessThan1000Row(InputStream inputStream){
        return readLessThan1000Row(inputStream, null);
    }

    /**
     * 读小于1000行数据, 带样式
     * @param inputStream 流
     * @param sheet initSheet ：
     *      sheetNo: sheet页码，默认为1
     *      headLineMun: 从第几行开始读取数据，默认为0, 表示从第一行开始读取
     *      clazz: 返回数据List<Object> 中Object的类名
     * @return List<Object>
     */
    public static List<Object> readLessThan1000Row(InputStream inputStream, Sheet sheet){
        sheet = sheet != null ? sheet : initSheet;
        InputStream fileStream = null;
        try {
            fileStream = inputStream;
            return EasyExcelFactory.read(fileStream, sheet);
        } catch (Exception e) {
            log.error("File Read Failed", e);
        } finally {
            try {
                if(fileStream != null){
                    fileStream.close();
                }
            } catch (IOException e) {
                log.error("Stream Close Failed", e);
            }
        }
        return null;
    }

    /**
     * 读大于1000行数据
     * @param inputStream 流
     * @return List<Object>
     */
    public static List<Object> readMoreThan1000Row(InputStream inputStream){
        return readMoreThan1000Row(inputStream, null);
    }

    /**
     * 读大于1000行数据, 带样式
     * @param inputStream 流
     * @return List<Object>
     */
    public static List<Object> readMoreThan1000Row(InputStream inputStream, Sheet sheet){
        sheet = sheet != null ? sheet : initSheet;
        InputStream fileStream = null;
        try {
            fileStream = inputStream;
            ExcelListener excelListener = new ExcelUtil.ExcelListener();
            EasyExcelFactory.readBySax(fileStream, sheet, excelListener);
            return excelListener.getData();
        } catch (Exception e) {
            log.error("File Read Failed", e);
        } finally {
            try {
                if(fileStream != null){
                    fileStream.close();
                }
            } catch (IOException e) {
                log.error("Stream Close Failed", e);
            }
        }
        return null;
    }

    /**
     * List转String数组
     * @param list list集合
     * @return 数组
     */
    public static String[] getStringArray(List<Object> list){
        if(null == list || list.size() == 0){
            return new String []{};
        }
        List<String> array = new ArrayList<>();
        for(int i = 0; i < list.size(); i++){
            Object obj = list.get(i);
            if(StringUtils.isEmpty(obj)){
                array.add("");
            } else {
                array.add(obj.toString());
            }
        }
        return array.toArray(new String[list.size()]);
    }

    /**
     * 压缩文件
     * @param response response
     * @param filePath 文件路径
     * @param fileName 压缩生成文件名
     * @param deleteSourceFile 是否删除原文件
     * @throws IOException IOException
     */
    public static void zipDateFile(HttpServletResponse response, String filePath, String fileName, boolean deleteSourceFile) throws Exception {
        if (StringUtils.isEmpty(filePath) || !new File(filePath).exists()) return;
        zipDateFile(response, getAllFile(filePath), fileName, deleteSourceFile);
    }

    /**
     * 压缩文件
     * @param response response
     * @param fileList 文件集合
     * @param fileName 压缩生成文件名
     * @param deleteSourceFile 是否删除原文件
     * @throws IOException IOException
     */
    public static void zipDateFile(HttpServletResponse response, List<File> fileList, String fileName, boolean deleteSourceFile) throws Exception {
        getOutputStreamFileName(response, fileName);
        ServletOutputStream servletOutputStream = response.getOutputStream();
        ZipOutputStream zipOutputStream = new ZipOutputStream(servletOutputStream);
        zipFile(fileList, zipOutputStream, deleteSourceFile);
        try {
            zipOutputStream.close();
        } catch (IOException e) {
            log.error("Stream Close Failed", e);
        }
    }

    /**
     * 压缩导出
     * @param fileList 文件列表
     * @param zipOutputStream zip流
     * @param deleteSourceFile 是否删除原文件
     * @throws IOException IOException
     */
    public static void zipFile(List<File> fileList, ZipOutputStream zipOutputStream, boolean deleteSourceFile) throws IOException {
        byte[] buffer = new byte[1024];
        for (File file : fileList) {
            if (file.exists()) {
                if (file.isFile()) {
                    try (BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));) {
                        zipOutputStream.putNextEntry(new ZipEntry(file.getName()));
                        int size = 0;
                        while ((size = bis.read(buffer)) > 0) {
                            zipOutputStream.write(buffer, 0, size);
                        }
                        zipOutputStream.closeEntry();
                    } finally {
                        if(deleteSourceFile) file.delete();
                    }
                } else {
                    File[] files = file.listFiles();
                    if(null == files) continue;
                    List<File> childrenFileList = Arrays.asList(files);
                    zipFile(childrenFileList, zipOutputStream, deleteSourceFile);
                }
            }
        }
    }

    /**
     * 获取指定文件夹下所有文件，不含文件夹里的文件
     * @param filePath 文件路径
     * @return fileList
     */
    public static List<File> getAllFile(String filePath) {
        if (StringUtils.isEmpty(filePath)) return null;
        return getAllFile(new File(filePath));
    }

    /**
     * 获取指定文件夹下所有文件，不含文件夹里的文件
     * @param dirFile 文件夹
     * @return fileList
     */
    public static List<File> getAllFile(File dirFile) {
        // 如果文件夹不存在或着不是文件夹，则返回 null
        if (Objects.isNull(dirFile) || !dirFile.exists() || dirFile.isFile()){
            return null;
        }
        File[] childrenFiles = dirFile.listFiles();
        if (Objects.isNull(childrenFiles) || childrenFiles.length == 0){
            return null;
        }
        List<File> files = new ArrayList<>();
        for (File childFile : childrenFiles) {
            // 如果是文件，直接添加到结果集合
            if (childFile.isFile()) {
                files.add(childFile);
            }
            //以下几行代码取消注释后可以将所有子文件夹里的文件也获取到列表里
//            else {
//                // 如果是文件夹，则将其内部文件添加进结果集合
//                List<File> cFiles = getAllFile(childFile);
//                if (Objects.isNull(cFiles) || cFiles.isEmpty()) continue;
//                files.addAll(cFiles);
//            }
        }
        return files;
    }

    public static boolean createFilePath(String path){
        try {
            File filePath = new File(path);
            if (!filePath.exists()) {
                if(!filePath.mkdirs()){
                    return false;
                }
            }
        } catch (Exception e) {
            log.error("Error Creating Folder On Server", e);
            return false;
        }
        return true;
    }

    public static boolean deleteFilePath(File file) {
        if (file.isDirectory()) {
            String[] children = file.list();
            if(null != children && children.length > 0){
                File file1 = null;
                //递归删除目录中的子目录下
                for(String str : children){
                    file1 = new File(file, str);
                    log.info(file1.getPath());
                    deleteFilePath(file1);
                }
            }
        }
        return file.delete();
    }

}

2、测试Bean

测试实体类：ExcelVO.java

package com.bean.vo;

import java.util.List;
import java.util.Map;

public class ExcelVO {

    private Integer id;

    private String code;

    private String name;

    private Map<Integer, String> errorMap;

    private Map<String, Object> templateMap;

    private List<Object> templateList;

    private String html;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Map<Integer, String> getErrorMap() {
        return errorMap;
    }

    public void setErrorMap(Map<Integer, String> errorMap) {
        this.errorMap = errorMap;
    }

    public Map<String, Object> getTemplateMap() {
        return templateMap;
    }

    public void setTemplateMap(Map<String, Object> templateMap) {
        this.templateMap = templateMap;
    }

    public List<Object> getTemplateList() {
        return templateList;
    }

    public void setTemplateList(List<Object> templateList) {
        this.templateList = templateList;
    }

    public String getHtml() {
        return html;
    }

    public void setHtml(String html) {
        this.html = html;
    }

    public ExcelVO() {

    }
    public ExcelVO(Integer id, String code, String name) {
        this.id = id;
        this.code = code;
        this.name = name;
    }
}

3、测试Service

测试接口类：IExcelService.java

package com.service;

import com.bean.Result;
import com.web.excel.vo.ExcelVO;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;

public interface IExcelService {

    void exportExcel(HttpServletResponse response, ExcelVO excelVO, boolean zip) throws Exception;

    void exportExcelError(HttpServletResponse response, ExcelVO excelVO) throws Exception;

    void exportExcelByHtml(HttpServletResponse response, ExcelVO excelVO) throws Exception;

    void exportExcelByTemplate(HttpServletResponse response, ExcelVO excelVO) throws Exception;

    void exportExcelByTemplateAndList(HttpServletResponse response, ExcelVO excelVO) throws Exception;

    void exportExcelByTemplateAndExtendList(HttpServletResponse response, ExcelVO excelVO) throws Exception;

    void exportExcelByTemplateAndSheet(HttpServletResponse response, ExcelVO excelVO) throws Exception;

    void exportExcelByTemplateAndSheetMap(HttpServletResponse response, ExcelVO excelVO) throws Exception;

    Result importExcel(MultipartFile multipartFile, HttpServletResponse response) throws Exception;

}

4、测试ServiceImpl

测试接口实现类：ExcelServiceImpl.java

package com.service.impl;

import com.bean.Result;
import com.util.ExcelUtil;
import com.web.excel.service.IExcelService;
import com.web.excel.vo.ExcelDTO;
import com.web.excel.vo.ExcelVO;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.UUID;

@Service
public class ExcelServiceImpl implements IExcelService {

    public static Log log = LogFactory.getLog(ExcelServiceImpl.class);

    /**
     * 导出Excel基础方法
     * @param response response
     * @param excelVO 接口查询参数，用于查询业务数据
     * @throws Exception Exception
     */
    @Override
    public void exportExcel(HttpServletResponse response, ExcelVO excelVO, boolean zip) throws Exception {
        //查询数据
        List<ExcelDTO> exportResult = this.getResultList(excelVO);
        //导出数据
        this.exportExcel(response, exportResult, zip);
    }

    private List<ExcelDTO> getResultList(ExcelVO excelVO) {
        List<ExcelDTO> excelDTOList = new ArrayList<>();
        ExcelDTO excelDTO;
        Integer count = StringUtils.isEmpty(excelVO.getId()) ? 10 : excelVO.getId();
        for(int i = 0; i < count; i++){
            excelDTO = new ExcelDTO(i + 1, getValue(), getValue());
            excelDTOList.add(excelDTO);
        }
        return excelDTOList;
    }

    private void exportExcel(HttpServletResponse response, List<ExcelDTO> excelDTOList, boolean zip) throws Exception {
        if (!Objects.isNull(excelDTOList) && excelDTOList.size() > 0) {
            List<List<Object>> data = new ArrayList<>();
            List<String> head = Arrays.asList("序号", "编码", "名称");
            List<Object> list;
            for (ExcelDTO excel : excelDTOList) {
                list = new ArrayList<>();
                list.add(!StringUtils.isEmpty(excel.getId()) ? excel.getId() : "");
                list.add(!StringUtils.isEmpty(excel.getCode()) ? excel.getCode() : "");
                list.add(!StringUtils.isEmpty(excel.getName()) ? excel.getName() : "");
                data.add(list);
            }
            if(zip){
                //单线程拆分压缩导出
//                ExcelUtil.writeExcelZipByList(response, "E:\\war\\a", "a", "xlsx", data, head, 1000);
                //多线程拆分压缩导出
                ExcelUtil.writeExcelZipByListThread(response, "E:\\war\\a", "a", "xlsx", data, head, 100);
            } else {
//                ExcelUtil.writeExcelByList(ExcelUtil.getOutputStreamFileName(response, "exportExcel.xlsx"), data, head);
                ExcelUtil.writeExcelByList("E:\\war\\a", "exportExcel.xlsx", data, head);
            }
        }
    }

    /**
     * 导出Excel方法，含错误Map，自动标注
     * @param response response
     * @param excelVO 接口查询参数，用于查询业务数据
     * @throws Exception Exception
     */
    @Override
    public void exportExcelError(HttpServletResponse response, ExcelVO excelVO) throws Exception {
        //查询数据
        List<ExcelDTO> exportResult = this.getResultListError(excelVO);
        //设置响应文件类型
        OutputStream outputStream = ExcelUtil.getOutputStreamFileName(response, "exportExcelError.xlsx");
        //导出数据
        this.exportExcelError(outputStream, exportResult);
    }

    private List<ExcelDTO> getResultListError(ExcelVO excelVO) {
        List<ExcelDTO> excelDTOList = new ArrayList<>();
        ExcelDTO excelDTO;
        Map<Integer,String> resultMap;
        Integer count = StringUtils.isEmpty(excelVO.getId()) ? 10 : excelVO.getId();
        for(int i = 0; i < count; i++){
            excelDTO = new ExcelDTO(i + 1, getValue(), getValue());
            resultMap = new HashMap<Integer,String>();//错误信息map集合
            if(excelDTO.getId() > 10){
                resultMap.put(0, "大于10");
            }
            if(excelDTO.getCode().contains("5")){
                resultMap.put(1, "包含5");
            }
            if(excelDTO.getName().contains("a")){
                resultMap.put(2, "包含a");
            }
            excelDTO.setErrorMap(resultMap);
            excelDTOList.add(excelDTO);
        }
        return excelDTOList;
    }

    private void exportExcelError(OutputStream outputStream, List<ExcelDTO> excelDTOList) throws Exception {
        if (!Objects.isNull(excelDTOList) && excelDTOList.size() > 0) {
            List<List<Object>> data = new ArrayList<>();
            List<Object> head = Arrays.asList("序号", "编码", "名称");
            List<Object> list;
            for (ExcelDTO excel : excelDTOList) {
                list = new ArrayList<>();
                list.add(!StringUtils.isEmpty(excel.getId()) ? excel.getId() : "");
                list.add(!StringUtils.isEmpty(excel.getCode()) ? excel.getCode() : "");
                list.add(!StringUtils.isEmpty(excel.getName()) ? excel.getName() : "");
                list.add(excel.getErrorMap());
                data.add(list);
            }
            ExcelUtil.writeExcelByError(outputStream, data, head);
        }
    }

    /**
     * 根据html元素导出文件
     * @param response response
     * @param excelVO 参数html
     * @throws Exception Exception
     */
    @Override
    public void exportExcelByHtml(HttpServletResponse response, ExcelVO excelVO) throws Exception {
        ExcelUtil.writeExcelByHtml(response, "excelByHtml.xlsx", excelVO.getHtml());
    }

    /**
     * 根据模板导出表格
     * @param response response
     * @param excelVO 接口查询参数，用于查询业务数据
     * @throws Exception Exception
     */
    @Override
    public void exportExcelByTemplate(HttpServletResponse response, ExcelVO excelVO) throws Exception {
        ExcelDTO excelDTO = new ExcelDTO(StringUtils.isEmpty(excelVO.getId()) ? 10 : excelVO.getId(), getValue(), getValue());
        OutputStream outputStream = ExcelUtil.getOutputStreamFileName(response, "simpleTemplateExcel.xlsx");
        ExcelUtil.writeExcelByTemplate("/app/template/simpleTemplate.xlsx", outputStream, excelDTO);
    }

    /**
     * 根据模板导出表格
     * @param response response
     * @param excelVO 接口查询参数，用于查询业务数据
     * @throws Exception Exception
     */
    @Override
    public void exportExcelByTemplateAndList(HttpServletResponse response, ExcelVO excelVO) throws Exception {
        List<ExcelDTO> exportResult = this.getResultList(excelVO);
        Map<String,Object> data = new HashMap<>();
        data.put("id", StringUtils.isEmpty(excelVO.getId()) ? 10 : excelVO.getId());
        data.put("code", getValue());
        data.put("name", getValue());
        OutputStream outputStream = ExcelUtil.getOutputStreamFileName(response, "exportExcelByTemplateAndList.xlsx");
        ExcelUtil.writeExcelByTemplate("/app/template/simpleTemplateList.xlsx", outputStream, data, exportResult);
        /*ExcelDTO excelDTO = new ExcelDTO();
        excelDTO.setId(StringUtils.isEmpty(excelVO.getId()) ? 10 : excelVO.getId());
        excelDTO.setCode(getValue());
        excelDTO.setName(getValue());
        ExcelUtil.writeExcelByTemplate("/app/template/simpleTemplateList.xlsx", outputStream, excelDTO, exportResult);*/
    }

    /**
     * 根据模板导出表格（模板附带表头表尾，中间列表自动扩充）
     * @param response response
     * @param excelVO 接口查询参数，用于查询业务数据
     * @throws Exception Exception
     */
    @Override
    public void exportExcelByTemplateAndExtendList(HttpServletResponse response, ExcelVO excelVO) throws Exception {
        List<ExcelDTO> exportResult = this.getResultList(excelVO);
        Map<String,Object> data = new HashMap<>();
        data.put("id", StringUtils.isEmpty(excelVO.getId()) ? 10 : excelVO.getId());
        data.put("code", getValue());
        data.put("name", getValue());
        OutputStream outputStream = ExcelUtil.getOutputStreamFileName(response, "exportExcelByTemplateAndExtendList.xlsx");
        ExcelUtil.writeExcelByTemplateRows("/app/template/simpleTemplateExtendList.xlsx", outputStream, data, exportResult);
    }

    /**
     * 根据模板导出表格,分多个sheet页面
     * @param response
     * @param excelVO
     * @throws Exception
     */
    @Override
    public void exportExcelByTemplateAndSheet(HttpServletResponse response, ExcelVO excelVO) throws Exception {
        List<ExcelDTO> exportResult = this.getResultList(excelVO);
        OutputStream outputStream = ExcelUtil.getOutputStreamFileName(response, "exportExcelByTemplateAndSheet.xlsx");
        List<Map<String,Object>> data = new ArrayList<>();
        List<List<Object>> dataList = new ArrayList<>();
        List<List<ExcelDTO>> splitList = ExcelUtil.splitList(exportResult, 10);
        for (List<ExcelDTO> line : splitList) {
            // 重新放入序号
            for(int i = 0 ; i < line.size(); i++){
                ExcelDTO excel = line.get(i);
                excel.setId(i + 1);
            }
            Map<String,Object> dataMap = new HashMap<>();
            dataMap.put("id", StringUtils.isEmpty(excelVO.getId()) ? 10 : excelVO.getId());
            dataMap.put("code", getValue());
            dataMap.put("name", getValue());
            line.get(0).setTemplateMap(dataMap);

            data.add(line.get(0).getTemplateMap());
            dataList.add(new ArrayList<>(line));
        }
        ExcelUtil.writeExcelByTemplate("/app/template/simpleTemplateSheet.xlsx",
                outputStream, data, dataList, data.size(),true);
    }

    /**
     * 根据模板导出表格,分多个sheet页面
     * @param response response
     * @param excelVO excelVO
     * @throws Exception Exception
     */
    @Override
    public void exportExcelByTemplateAndSheetMap(HttpServletResponse response, ExcelVO excelVO) throws Exception {
        List<ExcelDTO> exportResult = this.getResultList(excelVO);
        Map<String, Object> data = new HashMap<>();
        data.put("code", getValue());
        data.put("name", getValue());
        OutputStream outputStream = ExcelUtil.getOutputStreamFileName(response, "exportExcelByTemplateAndSheet.xlsx");
        Map<Integer, ExcelUtil.SheetBean> map = new HashMap<>();
        ExcelUtil.SheetBean<ExcelDTO> sheetBean;
        List<List<ExcelDTO>> lists = ExcelUtil.splitList(exportResult, 1000);
        for (int i = 0; i < lists.size(); i++) {
            // 重新放入序号
            for (int num = 0; num < lists.get(i).size(); num++) {
                ExcelDTO excel = (ExcelDTO) lists.get(i).get(num);
                excel.setId(num + 1);
            }
            sheetBean = new ExcelUtil.SheetBean<ExcelDTO>();
            sheetBean.setBeanMap(data);
            sheetBean.setBeanList(lists.get(i));
            map.put(i, sheetBean);
        }
        ExcelUtil.writeExcelByTemplate("/app/template/simpleTemplateSheetMap.xlsx", outputStream, map,true);
    }

    /**
     * 导入excel表格
     * @param multipartFile 文件
     * @param response response
     * @return 导入结果
     * @throws Exception Exception
     */
    @SuppressWarnings("unchecked")
    @Override
    public Result importExcel(MultipartFile multipartFile, HttpServletResponse response) throws Exception {
        InputStream inputStream = new BufferedInputStream(multipartFile.getInputStream());
        List<Object> objects = ExcelUtil.readLessThan1000Row(inputStream);
        //排除第一行
        for(int i = 1; i < objects.size(); i++){
            String[] split = ExcelUtil.getStringArray((List<Object>) objects.get(i));
            log.info(Arrays.toString(split));
        }
        return null;
    }

    private String getValue(){
        return UUID.randomUUID().toString().substring(0, 20);
    }
}

5、测试Controller

测试Controller类：ExcelController.java

package com.controller;

import com.alibaba.fastjson.JSON;
import com.bean.Result;
import com.web.excel.service.IExcelService;
import com.web.excel.vo.ExcelVO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

@Slf4j
@Controller
public class ExcelController {

    @Resource
    private IExcelService excelService;

    @RequestMapping("/exportExcel")
    @ResponseBody
    public void exportExcel(HttpServletResponse response, @RequestBody String json) throws Exception {
        ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
        excelService.exportExcel(response, excelVO, false);
    }

    @RequestMapping("/exportExcelZip")
    @ResponseBody
    public void exportExcelZip(HttpServletResponse response, @RequestBody String json) throws Exception {
        ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
        excelService.exportExcel(response, excelVO, true);
    }

    @RequestMapping("/exportExcelError")
    @ResponseBody
    public void exportExcelError(HttpServletResponse response, @RequestBody String json) throws Exception {
        ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
        excelService.exportExcelError(response, excelVO);
    }

    @RequestMapping("/exportExcelByHtml")
    @ResponseBody
    public void exportExcelByHtml(HttpServletResponse response, @RequestBody String json) throws Exception {
        ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
        excelService.exportExcelByHtml(response, excelVO);
    }

    @RequestMapping("/exportExcelByTemplate")
    @ResponseBody
    public void exportExcelByTemplate(HttpServletResponse response, @RequestBody String json) throws Exception {
        ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
        excelService.exportExcelByTemplate(response, excelVO);
    }

    @RequestMapping("/exportExcelByTemplateAndList")
    @ResponseBody
    public void exportExcelByTemplateAndList(HttpServletResponse response, @RequestBody String json) throws Exception {
        ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
        excelService.exportExcelByTemplateAndList(response, excelVO);
    }

    @RequestMapping("/exportExcelByTemplateAndExtendList")
    @ResponseBody
    public void exportExcelByTemplateAndExtendList(HttpServletResponse response, @RequestBody String json) throws Exception {
        ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
        excelService.exportExcelByTemplateAndExtendList(response, excelVO);
    }

    @RequestMapping("/exportExcelByTemplateAndSheet")
    @ResponseBody
    public void exportExcelByTemplateAndSheet(HttpServletResponse response, @RequestBody String json) throws Exception {
        ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
        excelService.exportExcelByTemplateAndSheet(response, excelVO);
    }

    @RequestMapping("/exportExcelByTemplateAndSheetMap")
    @ResponseBody
    public void exportExcelByTemplateAndSheetMap(HttpServletResponse response, @RequestBody String json) throws Exception {
        ExcelVO excelVO = JSON.parseObject(json, ExcelVO.class);
        excelService.exportExcelByTemplateAndSheetMap(response, excelVO);
    }

    @RequestMapping("/importExcel")
    @ResponseBody
    public Result importExcel(MultipartFile multipartFile, HttpServletResponse response) throws Exception{
        return excelService.importExcel(multipartFile, response);
    }

}