spring easyexcel web实现excel的导入和导出

package com.code.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.baomidou.mybatisplus.annotation.*;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

import java.util.Date;

/**
 * @author Herat
 */
@Data
@TableName("t_user")
public class User {

    @TableId(value = "id", type = IdType.AUTO)
    @ExcelProperty(value = "ID")
    @ColumnWidth(value = 10)
    private Integer id;

    @TableField(value = "name", insertStrategy = FieldStrategy.NOT_NULL)
    @ExcelProperty(value = "用户名")
    @ColumnWidth(value = 25)
    private String name;

    @TableField(value = "age")
    @ExcelProperty(value = "年龄")
    @ColumnWidth(value = 10)
    private Integer age;

    @TableField(value = "create_time", updateStrategy = FieldStrategy.NEVER)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "Asia/Shanghai")
    @ExcelProperty(value = "创建时间")
    @ColumnWidth(value = 40)
    private Date createTime;
}

package com.code.controller;

import com.code.service.ExcelService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * @author Herat
 */
@RestController
@RequestMapping("/excel")
public class ExcelHuToolController {

    @Resource
    private ExcelService excelService;

    @GetMapping("export")
    public void export(HttpServletResponse response) throws IOException {
        // excelService.export(response);

        excelService.exportExcel(response);
    }

    @PostMapping("upload")
    public String upload(MultipartFile file) throws IOException {
        System.out.println(file.getName());
        excelService.imports(file);
        return "SUCCESS";
    }
}

package com.code.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.code.entity.User;
import com.code.service.UserService;
import lombok.extern.slf4j.Slf4j;

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

/**
 * 不能由spring进行管理
 * @author Herat
 */
@Slf4j
public class DataListener extends AnalysisEventListener<User> {

    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    /**
     * 缓存的数据
     */
    private List<User> cachedDataList = new ArrayList<>(BATCH_COUNT);

    private UserService userService;

    public DataListener(UserService userService) {
        this.userService = userService;
    }

    @Override
    public void invoke(User data, AnalysisContext context) {
        cachedDataList.add(data);
        if(cachedDataList.size() > BATCH_COUNT) {
            userService.saveBatch(cachedDataList);
            log.info("保存数据");
            cachedDataList.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        userService.saveBatch(cachedDataList);
        log.info("import success");
    }
}

package com.code.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.code.entity.User;
import com.code.listener.DataListener;
import com.code.listener.UserDataListener;
import com.code.mapper.db1.UserMapper;
import org.apache.commons.collections.MapUtils;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;

/**
 * @author Herat
 */
@Service
public class ExcelService {

    @Resource
    private UserMapper userMapper;

    @Resource
    private UserService userService;

    public void exportExcel(HttpServletResponse response) throws IOException {
        List<User> users = userMapper.selectList(new QueryWrapper<User>());
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("中奖名单", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), User.class).autoCloseStream(Boolean.FALSE)
                    .sheet(1)
                    .doWrite(users);
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

    public void imports(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), User.class, new DataListener(userService)).sheet().doRead();
    }

}

package com.code.mapper.db1;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.code.entity.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

public interface UserMapper extends BaseMapper<User> {

}

package com.code.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.code.entity.User;

public interface UserService extends IService<User> {

}

package com.code.service.imp;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.code.entity.User;
import com.code.mapper.db1.UserMapper;
import com.code.service.UserService;
import org.springframework.stereotype.Service;

import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;

@Service
public class UserServiceImp extends ServiceImpl<UserMapper, User> implements UserService {
}