EasyExcel 三分钟搞定导入导出

最新推荐文章于 2024-08-07 21:19:40 发布

java后生

最新推荐文章于 2024-08-07 21:19:40 发布

阅读量5.9k

点赞数 10

分类专栏：每日学习一个小技术文章标签： java

本文链接：https://blog.csdn.net/weixin_44840242/article/details/127555784

版权

每日学习一个小技术专栏收录该内容

32 篇文章 0 订阅

订阅专栏

前言：本文章教你从零开始，三分钟搞定excel单sheet导出、导入、多sheet导出、导入、excel模板导入单个sheet、多个sheet，废话不多说，直接上代码

1.引入依赖

<!--excel-->
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>2.2.10</version>
</dependency>

<!--fastjson-->
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>fastjson</artifactId>
   <version>1.2.72</version>
</dependency>

2.工具类-ExcelHandler

package io.renren.handler;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import io.renren.common.exception.RenException;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * @author wy
 * @description: Excel处理
 * @date 2022/10/27 10:00
 */
@Slf4j
@Component
public class ExcelHandler {

    /**
     * 导入简单excel数据
     * @param file ：文件流
     * @param clazz：数据对象
     * @param sheetName：要读取的sheet [不传：默认读取第一个sheet]
     * @throws Exception
     */
    public <T> List<T> importExcel(MultipartFile file, Class<T> clazz, String sheetName) throws Exception{
        this.checkFile(file);
        UploadDataListener<T> uploadDataListener = new UploadDataListener<>();
        ExcelReaderBuilder builder = EasyExcelFactory.read(file.getInputStream(), clazz, uploadDataListener);
        if (StringUtils.isEmpty(sheetName)) {
            builder.sheet().doRead();
        } else {
            builder.sheet(sheetName).doRead();
        }
        return uploadDataListener.getList();
    }
    /**
     * 指定sheet页导入通用方法
     * @param multipartFile 传入文件
     * @param objList 需要导入的sheet页实体类型集合
     * @param index sheet页个数
     * @param indexList 需要导入sheet页下标集合
     * @param <T>
     * @return <T> List<List<T>>
     * @throws Exception
     */
    public <T> List<List<T>> importExcelsByIndex(MultipartFile multipartFile, List<T> objList, int index,List<Integer> indexList) throws Exception {
        if (multipartFile == null) {
            throw new RenException("文件为空");
        }
        List<List<T>> resultList = new LinkedList<>();
        //初始化导入sheet页实体类型下标
        int objListClass = 0;
        for (int i = 0; i < index; i++) {
            if(indexList.contains(i)){
                UploadDataListener<T> uploadDataListener = new UploadDataListener<>();
                List<T> excels;
                EasyExcelFactory.read(multipartFile.getInputStream(), objList.get(objListClass).getClass(), uploadDataListener).sheet(i).doRead();
                excels = uploadDataListener.getList();
                resultList.add(excels);
                objListClass++;
            }
        }
        return resultList;
    }
    /**
     * 读取多个sheet
     * @param file：文件流
     * @param index：需要读取的sheet个数 [默认0开始，如果传入3，则读取0 1 2]
     * @param params：每个sheet里面需要封装的对象[如果index为3，则需要传入对应的3个对象]
     * @param <T>
     * @return
     */
    public <T> List<List<T>> importExcels(MultipartFile file, int index, List<Object> params) throws Exception {
        this.checkFile(file);
        List<List<T>> resultList = new LinkedList<>();
        for (int i=0; i<index; i++) {
            UploadDataListener<T> uploadDataListener = new UploadDataListener<>();
            ExcelReaderBuilder builder = EasyExcelFactory.read(file.getInputStream(), params.get(i).getClass(), uploadDataListener);
            builder.sheet(i).doRead();
            List<T> list = uploadDataListener.getList();
            resultList.add(list);
        }
        return resultList;
    }

    /**
     * 导出excel表格
     * @param response ：
     * @param dataList ：数据列表
     * @param clazz    ：数据对象
     * @param fileName ：文件名称
     * @param sheetName：sheet名称
     * @throws Exception
     */
    public <T> void exportExcel(HttpServletResponse response, List<T> dataList, Class<T> clazz, String fileName, String sheetName) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ExcelTemplateEnum.TEMPLATE_SUFFIX.getDesc());
        EasyExcelFactory.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(dataList);
    }

    /**
     * 导出多个sheet
     * @param response：
     * @param dataList：多个数据列表
     * @param clazzMap：对应每个列表里面的数据对应的sheet名称
     * @param fileName：文件名
     * @param <T>
     * @throws Exception
     */
    public <T> void exportExcels(HttpServletResponse response, List<List<?>> dataList, Map<Integer, String> clazzMap, String fileName) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ExcelTemplateEnum.TEMPLATE_SUFFIX.getDesc());
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        int len = dataList.get(0).size();
        for (int i=0; i<len; i++) {
            List<?> objects = (List<?>) dataList.get(0).get(i);
            Class<?> aClass = objects.get(0).getClass();
            WriteSheet writeSheet0 = EasyExcel.writerSheet(i, clazzMap.get(i)).head(aClass).build();
            excelWriter.write(objects, writeSheet0);
        }
        excelWriter.finish();
    }

    /**
     * 根据模板将集合对象填充表格-单个sheet
     * @param list：填充对象集合
     * @param object ：填充对象
     * @param fileName：文件名称
     * @param templateName：模板名称
     * @throws Exception
     */
    public <T> void exportTemplateExcels(HttpServletResponse response, List<T> list, Object object, String fileName, String templateName) throws Exception{
        String template = ExcelTemplateEnum.TEMPLATE_PATH.getDesc() + File.separator + templateName + ExcelTemplateEnum.TEMPLATE_SUFFIX.getDesc();
        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(template);
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        ExcelWriter excelWriter = EasyExcelFactory.write(getOutputStream(fileName, response)).withTemplate(inputStream).build();
        WriteSheet writeSheet0 = EasyExcelFactory.writerSheet(0).build();
        excelWriter.fill(object, fillConfig, writeSheet0);
        excelWriter.fill(list, fillConfig, writeSheet0);
        excelWriter.finish();
    }

    /**
     * 根据模板将集合对象填充表格-多个sheet
     * @param list1：填充对象集合
     * @param list2：填充对象集合
     * @param object1 ：填充对象
     * @param object2 ：填充对象
     * @param fileName：文件名称
     * @param templateName：模板名称
     * @throws Exception
     */
    public <T> void exportSheetTemplateExcels(HttpServletResponse response, List<T> list1,List<T> list2, Object object1,Object object2, String fileName, String templateName) throws Exception{
        String template = ExcelTemplateEnum.TEMPLATE_PATH.getDesc() + File.separator + templateName + ExcelTemplateEnum.TEMPLATE_SUFFIX.getDesc();
        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(template);
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        ExcelWriter excelWriter = EasyExcelFactory.write(getOutputStream(fileName, response)).withTemplate(inputStream).build();
        WriteSheet writeSheet0 = EasyExcelFactory.writerSheet(0).build();
        WriteSheet writeSheet1 = EasyExcelFactory.writerSheet(1).build();
        excelWriter.fill(object1, fillConfig, writeSheet0);
        excelWriter.fill(list1, fillConfig, writeSheet0);
        excelWriter.fill(object2, fillConfig, writeSheet1);
        excelWriter.fill(list2, fillConfig, writeSheet1);
        excelWriter.finish();
    }

    /**
     * 根据模板将单个对象填充表格
     * @param object       ：填充对象
     * @param templateName：模板名称
     * @param fileName    ：文件名称
     * @param sheetName   ：需要写入的sheet名称 [不传：填充到第一个sheet]
     * @throws Exception
     */
    public void exportTemplateExcel(HttpServletResponse response, Object object, String templateName, String fileName, String sheetName) throws Exception{
        String template = ExcelTemplateEnum.TEMPLATE_PATH.getDesc() + File.separator + templateName + ExcelTemplateEnum.TEMPLATE_SUFFIX.getDesc();
        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(template);
        if (StringUtils.isEmpty(sheetName)) {
            EasyExcelFactory.write(getOutputStream(fileName, response)).withTemplate(inputStream).sheet().doFill(object);
        } else {
            EasyExcelFactory.write(getOutputStream(fileName, response)).withTemplate(inputStream).sheet(sheetName).doFill(object);
        }
    }

    /**
     * 根据模板将集合对象填充表格
     * @param list：填充对象集合
     * @param fileName：文件名称
     * @param templateName：模板名称
     * @param sheetName：需要写入的sheet [不传：填充到第一个sheet]
     * @throws Exception
     */
    public <T> void exportTemplateExcelList(HttpServletResponse response, List<T> list, String fileName, String templateName, String sheetName) throws Exception{
        log.info("模板名称：{}", templateName);
        String template = ExcelTemplateEnum.TEMPLATE_PATH.getDesc() + File.separator + templateName + ExcelTemplateEnum.TEMPLATE_SUFFIX.getDesc();
        log.info("模板路径：{}", template);
        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(template);
        // 全部填充：全部加载到内存中一次填充
        if (StringUtils.isEmpty(sheetName)) {
            EasyExcelFactory.write(getOutputStream(fileName, response)).withTemplate(inputStream).sheet().doFill(list);
        } else {
            EasyExcelFactory.write(getOutputStream(fileName, response)).withTemplate(inputStream).sheet(sheetName).doFill(list);
        }
    }
    /**
     * 根据模板将集合对象填充表格
     * @param list：填充对象集合
     * @param fileName：文件名称
     * @param templateName：模板名称
     * @throws Exception
     */
    public <T> void exportTemplateExcel2(HttpServletResponse response, List<T> list, String fileName, String templateName) throws Exception{
        String template = ExcelTemplateEnum.TEMPLATE_PATH.getDesc() + File.separator + templateName + ExcelTemplateEnum.TEMPLATE_SUFFIX.getDesc();
        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream(template);
        ExcelWriter excelWriter = EasyExcelFactory.write(getOutputStream(fileName, response)).withTemplate(inputStream).build();
        WriteSheet writeSheet = EasyExcelFactory.writerSheet().build();
        excelWriter.fill(list, writeSheet);
        excelWriter.finish();
    }
    /**
     * 构建输出流
     * @param fileName：文件名称
     * @param response：
     * @return
     * @throws Exception
     */
    private OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ExcelTemplateEnum.TEMPLATE_SUFFIX.getDesc());
        return response.getOutputStream();
    }

    /**
     * 文件格式校验
     * @param file：
     */
    private void checkFile(MultipartFile file) {
        if (file == null) {
            throw new RenException("文件不能为空");
        }
        String fileName = file.getOriginalFilename();
        if (StringUtils.isEmpty(fileName)) {
            throw new RenException("文件不能为空");
        }
        if (!fileName.endsWith(ExcelTemplateEnum.TEMPLATE_SUFFIX.getDesc())
                && !fileName.endsWith(ExcelTemplateEnum.TEMPLATE_SUFFIX_XLS.getDesc())) {
            throw new RenException("请上传.xlsx或.xls文件");
        }
    }

}

3.工具类-ExcelTemplateEnum

package io.renren.handler;

import lombok.Getter;

/**
 * @author wy
 * @description: 模板枚举
 * @date 2022/10/27 15:40
 */
@Getter
public enum ExcelTemplateEnum {
    /**单sheet导出*/
    TEMPLATE_1("1","complex"),

    /**模板格式*/
    TEMPLATE_SUFFIX("xlsx",".xlsx"),
    TEMPLATE_SUFFIX_XLS("xls",".xls"),
    TEMPLATE_SUFFIX_DOCX("docx",".docx"),
    /**模板路径*/
    TEMPLATE_PATH("path","excel"),
    ;

    private final String code;
    private final String desc;

    ExcelTemplateEnum(String code, String desc) {
        this.code = code;
        this.desc = desc;
    }

    /**
     * 通过code获取msg
     *
     * @param code 枚举值
     * @return
     */
    public static String getMsgByCode(String code) {
        if (code == null) {
            return null;
        }
        ExcelTemplateEnum enumList = getByCode(code);
        if (enumList == null) {
            return null;
        }
        return enumList.getDesc();
    }

    public static String getCode(ExcelTemplateEnum enumList) {
        if (enumList == null) {
            return null;
        }
        return enumList.getCode();
    }

    public static ExcelTemplateEnum getByCode(String code) {
        for (ExcelTemplateEnum enumList : values()) {
            if (enumList.getCode().equals(code)) {
                return enumList;
            }
        }
        return null;
    }

}

4.工具类-UploadDataListener

package io.renren.handler;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import io.renren.common.exception.RenException;

import java.util.ArrayList;
import java.util.List;

/**
 * @author wy
 * @description：导入模板监听器
 * @date 2022/10/27 09:48
 */
public class UploadDataListener<T> extends AnalysisEventListener<T> {

    /**数据集*/
    private final List<T> list = new ArrayList<>();

    public List<T> getList(){
        return this.list;
    }

    /**
     * 每条数据都会进入
     * @param object：
     * @param analysisContext：
     */
    @Override
    public void invoke(T object, AnalysisContext analysisContext) {
        this.list.add(object);
    }

    /**
     * 数据解析完调用
     * @param analysisContext：
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

    /**
     * 异常时调用
     * @param exception：
     * @param context：
     * @throws Exception
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        // 数据解析异常
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
            throw new RenException("第" + excelDataConvertException.getRowIndex() + "行" + excelDataConvertException.getColumnIndex() + "列" + "数据解析异常");
        }
        // 其他异常...
    }
}

5.实体类-ExcelVO

package io.renren.service.impl;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * 导出实体
 */
@Data
@ApiModel(value = "导出实体")
public class ExcelVO {

    @ColumnWidth(80)
    @ExcelProperty("编号")
    @ApiModelProperty(value = "编号")
    private Integer id;

    @ColumnWidth(80)
    @ExcelProperty("年龄")
    @ApiModelProperty(value = "年龄")
    private Integer age;

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

    @ColumnWidth(80)
    @ExcelProperty("语文")
    @ApiModelProperty(value = "语文")
    private Integer wen;

    @ColumnWidth(80)
    @ExcelProperty("数学")
    @ApiModelProperty(value = "数学")
    private Integer richard;

    @ColumnWidth(80)
    @ExcelProperty("总分")
    @ApiModelProperty(value = "总分")
    private Integer sum;

    //无需导出字段使用此注解
    //@JsonSerialize(using = ToStringSerializer.class)
}

6.业务层-ExcelService

package io.renren.service;

import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;

/**
 * 导出业务接口
 */
public interface ExcelService {

    /**
     * excel导入-单个sheet
     *
     * @param multipartFile 文件流
     */
    void excelImport(MultipartFile multipartFile);

    /**
     * excel导出-单个sheet
     *
     * @param response 响应体
     */
    void excelExport(HttpServletResponse response);

    /**
     * excel多入-多个sheet
     *
     * @param multipartFile 文件流
     */
    void excelSheetImport(MultipartFile multipartFile);

    /**
     * excel导出-多个sheet
     *
     * @param response 响应体
     */
    void excelSheetExport(HttpServletResponse response);

    /**
     * excel模板导出-单个sheet
     *
     * @param response 响应流
     */
    void excelTemplate(HttpServletResponse response);

    /**
     * excel模板导出-多个sheet
     *
     * @param response 响应流
     */
    void excelSheetTemplate(HttpServletResponse response) throws Exception;
}

7.实现层-ExcelServiceImpl

package io.renren.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import io.renren.common.exception.RenException;
import io.renren.handler.ExcelHandler;
import io.renren.handler.ExcelTemplateEnum;
import io.renren.service.ExcelService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.util.*;

/**
 * 导出业务实现
 */
@Slf4j
@Service
public class ExcelServiceImpl implements ExcelService {

    @Resource
    ExcelHandler excelHandler;

    /**
     * excel导入-单个sheet
     *
     * @param multipartFile 文件流
     */
    @Override
    public void excelImport(MultipartFile multipartFile) {
        try {
            List<ExcelVO> voList = excelHandler.importExcel(multipartFile, ExcelVO.class, null);
            if (CollectionUtils.isNotEmpty(voList)) {
                System.out.println("本次成功导出：" + voList.size() + "条,数据如下---------------");
                voList.forEach(vo -> {
                    System.out.println("vo=" + vo.toString());
                });

            }
        } catch (Exception e) {
            throw new RenException("导入失败");
        }
    }

    /**
     * excel导出-单个sheet
     *
     * @param response 响应体
     */
    @Override
    public void excelExport(HttpServletResponse response) {
        try {
            List<ExcelVO> excelVOS = structureDate(20);
            excelHandler.exportExcel(response, excelVOS, ExcelVO.class, "excel导出-单sheet", "excel导出-单sheet");
        } catch (Exception e) {
            throw new RenException("导出失败");
        }
    }

    /**
     * excel导入-多个sheet
     *
     * @param multipartFile 文件流
     */
    @Override
    public void excelSheetImport(MultipartFile multipartFile) {
        try {
            List<Integer> indexList = new ArrayList<>();
            indexList.add(0);
            indexList.add(1);
            indexList.add(2);
            indexList.add(3);
            indexList.add(4);
            indexList.add(5);
            List<Object> objList = new ArrayList<>();
            objList.add(new ExcelVO());
            objList.add(new ExcelVO());
            objList.add(new ExcelVO());
            objList.add(new ExcelVO());
            objList.add(new ExcelVO());
            objList.add(new ExcelVO());
            List<List<Object>> resultList = excelHandler.importExcelsByIndex(multipartFile, objList, 6, indexList);
            System.out.println("resultList="+resultList.size());
            resultList.forEach(vo->{
                List<ExcelVO> voList = JSON.parseArray(JSON.toJSONString(vo), ExcelVO.class);
                System.out.println("voList="+voList.size()+"条数据");
            });
        } catch (Exception e) {
            throw new RenException("导入失败");
        }
    }

    /**
     * excel导出-多个sheet
     *
     * @param response 响应体
     */
    @Override
    public void excelSheetExport(HttpServletResponse response) {
        try {
            //构建多集合集合
            List<List<?>> lists = new ArrayList<>();
            lists.add(structureDate(10));
            lists.add(structureDate(20));
            lists.add(structureDate(30));
            lists.add(structureDate(40));
            lists.add(structureDate(50));
            lists.add(structureDate(60));
            //Sheet页初始化
            Map<Integer, String> clazzMap = new HashMap<>();
            clazzMap.put(0, "一年级成绩表");
            clazzMap.put(1, "二年级成绩表");
            clazzMap.put(2, "三年级成绩表");
            clazzMap.put(3, "四年级成绩表");
            clazzMap.put(4, "五年级成绩表");
            clazzMap.put(5, "六年级成绩表");
            excelHandler.exportExcels(response, Collections.singletonList(lists), clazzMap, "excel导出-单sheet");
        } catch (Exception e) {
            throw new RenException("导出失败");
        }
    }

    /**
     * excel模板导出-单个sheet
     *
     * @param response 响应流
     */
    @Override
    public void excelTemplate(HttpServletResponse response) {
        try {
            //准备数据
            JSONObject obj = new JSONObject();
            obj.put("date","2022年");
            obj.put("season","秋季");
            obj.put("user","王远");
            obj.put("userDate",new Date());
            List<ExcelVO> list = structureDate(20);
            excelHandler.exportTemplateExcels(response,list,obj, ExcelTemplateEnum.TEMPLATE_1.getDesc(),ExcelTemplateEnum.TEMPLATE_1.getDesc());
        }catch (Exception e){
            log.error("导出错误：{}", e.getMessage());
            throw new RenException("导出错误");
        }
    }

    /**
     * excel模板导出-多个sheet
     *
     * @param response 响应流
     */
    @Override
    public void excelSheetTemplate(HttpServletResponse response) throws Exception {
        //准备数据
        JSONObject obj = new JSONObject();
        obj.put("date","2022年");
        obj.put("season","秋季");
        obj.put("user","王远");
        obj.put("userDate",new Date());
        List<ExcelVO> list = structureDate(20);
        excelHandler.exportSheetTemplateExcels(response,list,list,obj,obj, ExcelTemplateEnum.TEMPLATE_1.getDesc(),ExcelTemplateEnum.TEMPLATE_1.getDesc());
    }

    /**
     * 构造数据
     *
     * @return 数据集合
     */
    private List<ExcelVO> structureDate(Integer size) {
        List<ExcelVO> list = new ArrayList<>();
        for (int i = 0; i < size; i++) {
            ExcelVO vo = new ExcelVO();
            vo.setId(i);
            vo.setAge(i);
            vo.setName("张三" + i);
            vo.setWen(new Random().nextInt(99));
            vo.setRichard(new Random().nextInt(99));
            vo.setSum(vo.getWen() + vo.getRichard());
            list.add(vo);
        }
        return list;
    }
}

8.控制层-ApiExcelController

package io.renren.controller;

import io.renren.common.utils.Result;
import io.renren.service.ExcelService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

/**
 * 注册接口
 *
 * @author wy
 */
@RestController
@RequestMapping("/api")
@Api(tags = "文件接口")
public class ApiExcelController {

    @Resource
    private ExcelService excelService;

    /**
     * excel导入-单个sheet
     *
     * @param multipartFile 文件流
     * @return
     * @throws Exception
     */
    @PostMapping("/excelImport")
    @ApiOperation(value = "excel导入")
    public Result<Object> excelImport(@RequestParam("file") MultipartFile multipartFile){
        excelService.excelImport(multipartFile);
        return new Result<>();
    }

    /**
     * excel导出-单个sheet
     *
     * @param response 响应流
     */
    @ApiOperation(value = "excel导出", httpMethod = "GET")
    @GetMapping("/excelExport")
    public void export(HttpServletResponse response) {
        excelService.excelExport(response);
    }

    /**
     * excel导入-多个sheet
     *
     * @param multipartFile 文件流
     * @return 响应体
     */
    @PostMapping("/excelSheetImport")
    @ApiOperation(value = "excel导入-多个sheet")
    public Result<Object> excelSheetImport(@RequestParam("file") MultipartFile multipartFile){
        excelService.excelSheetImport(multipartFile);
        return new Result<>();
    }

    /**
     * excel导出-多个sheet
     *
     * @param response 响应流
     */
    @ApiOperation(value = "excel导出-多个sheet", httpMethod = "GET")
    @GetMapping("/excelSheetExport")
    public void excelSheetExport(HttpServletResponse response) {
        excelService.excelSheetExport(response);
    }

    /**
     * excel模板导出-单个sheet
     *
     * @param response 响应流
     */
    @ApiOperation(value = "excel模板导出", httpMethod = "GET")
    @GetMapping("/excelTemplate")
    public void excelTemplate(HttpServletResponse response) {
        excelService.excelTemplate(response);
    }

    /**
     * excel模板导出-多个sheet
     *
     * @param response 响应流
     */
    @ApiOperation(value = "excel模板导出-多个sheet", httpMethod = "GET")
    @GetMapping("/excelSheetTemplate")
    public void excelSheetTemplate(HttpServletResponse response) throws Exception {
        excelService.excelSheetTemplate(response);
    }

}

9.模板