Excel导入导出工具——easyExcel

一：创建两个工具类 

package ***.support.util;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

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

/**
 * @author Ls.
 * @date 2020/3/4 10:24
 */
public class ExcelListener extends AnalysisEventListener {
    //可以通过实例获取该值
    private List<Object> datas = new ArrayList<Object>();

    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        datas.add(o);//数据存储到list，供批量处理，或后续自己业务逻辑处理。
//        doSomething(o);//根据自己业务做处理
    }

    private void doSomething(Object object) {
        //1、入库调用接口
    }

    public List<Object> getDatas() {
        return datas;
    }

    public void setDatas(List<Object> datas) {
        this.datas = datas;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // datas.clear();//解析结束销毁不用的资源
    }
}

package com.wangyuan.stumgr.common.utils;

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.wangyuan.stumgr.common.response.JsonResult;
import com.wangyuan.stumgr.common.vo.qgzx.QgzxKqhzDto;
import org.apache.poi.ss.formula.functions.T;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Class T 映射的实体类，实体类必须继承BaseRowModel
 * List<? extends BaseRowModel> list   返回值类型为上面的类
 *
 * @author Ls.
 * @date 2020/6/19.
 */
public class EasyExcel {

    public static void exporExcel(HttpServletResponse response,
                                  String fileName,
                                  Class T,
                                  List<? extends BaseRowModel> list) throws IOException {
        String filename = fileName;
        ExcelWriter writer = null;
        OutputStream outputStream = response.getOutputStream();
        // OutputStream out = new FileOutputStream("/Users/jipengfei/78.xlsx"); //指定位置
        try {
            //添加响应头信息
            response.setHeader("Content-disposition", "attachment; filename=" + new String(filename.getBytes("UTF-8"), "ISO8859-1") + ".xlsx");
            response.setContentType("application/msexcel;charset=UTF-8");//设置类型
            response.setHeader("Pragma", "No-cache");//设置头
            response.setHeader("Cache-Control", "no-cache");//设置头
            response.setDateHeader("Expires", 0);//设置日期头
            //实例化 ExcelWriter
            writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);
            //实例化表单
            Sheet sheet = new Sheet(1, 0, T);
            sheet.setAutoWidth(Boolean.TRUE);
            sheet.setSheetName(filename);
            //获取数据
            //输出
            writer.write(list, sheet);
            writer.finish();
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                response.getOutputStream().close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

package com.*.common.vo.qgzx;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.wangyuan.stumgr.modules.qgzxgl.model.QgzxKqhz;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import javax.persistence.Column;
import java.io.Serializable;

/**
 *  考勤汇总VO
 * @author Ls.
 * @date 2020/3/17 11:56
 */
@Data
public class QgzxKqhzDto extends BaseRowModel implements Serializable {
    private static final long serialVersionUID = 1L;

    @ExcelProperty(value = "勤工助学活动名称",index = 0)
    @ApiModelProperty(value = "勤工助学活动名称")
    private String hdmc;
    @ExcelProperty(value = "岗位名称",index = 1)
    @ApiModelProperty(value = "岗位名称")
    private String gwmc;
    @ExcelProperty(value = "学号",index = 2)
    @ApiModelProperty(value = "学号")
    private Integer xh;
    @ExcelProperty(value = "学生姓名",index = 3)
    @ApiModelProperty(value = "学生姓名")
    private String xm;
    @ExcelProperty(value = "有效考勤天数",index = 4)
    @ApiModelProperty(value = "有效考勤天数,默认为0")
    private Integer yxkqts;
}

导出：

 @ApiOperation("")
    @GetMapping("/exportExcel")
    public JsonResult exporExcel(HttpServletResponse response) throws IOException {

        List<QgzxKqhzDto> list = new ArrayList<>();
        QgzxKqhzDto qgzxKqhzDto = new QgzxKqhzDto();
        qgzxKqhzDto.setHdmc("阳光行动");
        qgzxKqhzDto.setGwmc("爱心助学岗");
        qgzxKqhzDto.setXh(011111);
        qgzxKqhzDto.setXm("张三");
        qgzxKqhzDto.setYxkqts(20);
        list.add(qgzxKqhzDto);
        String filename = "勤工助学考勤导入模板";
        EasyExcel.exporExcel(response, filename, QgzxKqhzDto.class, list);
        return JsonResult.success();
    }

导入：

 public JsonResult importZy(@RequestParam("file") MultipartFile file, HttpServletRequest request, HttpServletResponse response) {
        if (file.isEmpty()) {
            return JsonResult.error(SystemErrorCode.READ_NO_DATA_IN_EXCEL);
        }
        InputStream is = null;
        try {
            is = file.getInputStream();
        } catch (IOException e) {
            return JsonResult.error(SystemErrorCode.FILE_READING_ERROR);
        }
        ArrayList<JwZyxxVo> errorList = new ArrayList<>();//导入失败数据
        //实例化实现了AnalysisEventListener接口的类
        ExcelListener listener = new ExcelListener();
        //传入参数
        ExcelReader excelReader = new ExcelReader(is, ExcelTypeEnum.XLSX, null, listener);
        //读取信息
        excelReader.read(new Sheet(1, 1, QgzxKqhzDto.class));
        //获取数据
        SimpleDateFormat sDateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        List<Object> list = listener.getDatas();    //所有的数据均在list里
        QgzxKqhzDto qgzxKqhzDto = new QgzxKqhzDto();
        //转换数据类型,并插入到数据库
        for (int i = 0; i < list.size(); i++) {
            qgzxKqhzDto=(QgzxKqhzDto)list.get(i);  
            //开始入库操作
        }
        return JsonResult.success();
    }

//浏览器下载   
 private void downloadExcel(String filename, Workbook workbook, HttpServletResponse response) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8"));
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }