easyExcels实现导入导出excel

easyExcel

详细学习请看官网 https://www.yuque.com/easyexcel/doc/easyexcel

1、文件批量导出excel

导入maven

   <!-- easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.2-beat1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
       
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.28</version>
        </dependency>

实体类

package com.wzw.pojo.manage;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.sql.rowset.BaseRowSet;
import java.io.Serializable;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel("仓库表实体类")  //要使用easyExcel必须继承BaseRowModel
public class WareHouse  extends BaseRowModel implements Serializable {
    @ExcelProperty(value = "id",index = 0)
    @ApiModelProperty(value = "仓库存货id")
    private int id;

    @ExcelProperty(value = "物资编号",index = 1)
    @ApiModelProperty(value = "物资编号")
    private String materialNumber;

    @ExcelProperty(value = "物资类别",index = 2)
    @ApiModelProperty(value = "物资类别")
    private String materialCategory;

    @ExcelProperty(value = "物资名称",index = 3)
    @ApiModelProperty(value = "物资名称")
    private String materialName;

    @ExcelProperty(value = "物资数量",index = 4)
    @ApiModelProperty(value = "物资数量")
    private int materialNum;

    @ExcelProperty(value = "计量单位",index = 5)
    @ApiModelProperty(value = "计量单位")
    private String unit;

    @ExcelProperty(value = "参数备注",index = 6)
    @ApiModelProperty(value = "参数备注")
    private String parameter;


}

controller层

@RequestMapping("/warehouseExcelOut")
public void warehouseExcelOut(HttpServletResponse response){
    ExcelWriter writer = null;
    OutputStream out = null;
    try{
        //通过数据库查出你要批量导出的数据，这里要用List
        List wareHouseList = wareHouseService.findAll();
        out = response.getOutputStream();
        writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
        //设置文件名
        String fileName = "仓库信息表格";
        //WareHouse.class 实体类的class对象
        Sheet sheet = new Sheet(1, 0, WareHouse.class);
        //表名
        sheet.setSheetName("仓库信息");
        //把wareHouseList数据写入sheet中
        writer.write(wareHouseList, sheet);
        //设置响应头
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "ISO8859-1"));
        out.flush();
    } catch (Exception  e) {
        e.printStackTrace();
    }  finally {
        if (writer != null) {
            writer.finish();
        }
        if (out != null) {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

1、文件批量导入excel

controller

    @Autowired
    private WareHouseServiceImpl wareHouseService;    
    @RequestMapping("/warehouseExcelIn")
    public ResultVo warehouseExcelOut(@RequestParam MultipartFile file) throws IOException {
//        if(!file.getOriginalFilename().equals("仓库信息表格.xls") && !file.getOriginalFilename().equals("仓库            信息表格.xlsx") ){
//            return;
//        }
        InputStream inputStream = new BufferedInputStream(file.getInputStream());
        //实例化实现了AnalysisEventListener接口的类
        
        DemoDataListener excelListener = new DemoDataListener(wareHouseService);
        ExcelReader reader = new ExcelReader(inputStream,null,excelListener);
        //读取信息
        reader.read(new Sheet(1,1,WareHouse.class));
        return ResultVo.success("上传成功");

    }

DemoDataListener

package com.wzw.untils;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.wzw.pojo.manage.WareHouse;
import com.wzw.service.manage.WareHouseServiceImpl;
import org.mybatis.logging.Logger;
import org.mybatis.logging.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import springfox.documentation.spring.web.json.Json;


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

// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<WareHouse> {
    //WareHouse实体类
    private List<WareHouse> datas = new ArrayList<>();
    private static final int BATCH_COUNT = 3000;
    
    //注入service层
    @Autowired
    private WareHouseServiceImpl wareHouseService;
    //有参构造
    public DemoDataListener(WareHouseServiceImpl wareHouseService) {
        this.wareHouseService=wareHouseService;
    }


    @Override
    public void invoke(WareHouse wareHouse, AnalysisContext analysisContext) {
        System.out.println("warehouse:         "+wareHouse);
        //数据存储到datas，供批量处理，或后续自己业务逻辑处理。
        datas.add(wareHouse);
        //达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if(datas.size() >= BATCH_COUNT){
            saveData();
            // 存储完成清理datas
            datas.clear();
        }
    }
	//使用add对导入数据进行入库
    private void saveData() {
        for(WareHouse wareHouse : datas){
            System.out.println("saveData:   "+wareHouse);
            wareHouseService.addWareHourse(wareHouse);
        }
    }

    public List<WareHouse> getDatas() {
        return datas;
    }
	
    public void setDatas(List<WareHouse> datas) {
        this.datas = datas;
    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();//确保所有数据都能入库
    }
}

前端

//批量上传
$(function () {
    upload.render({
        elem: '#inputBtn'
        , url: 'warehouseExcelIn'
        , accept: 'file'
        , auto: true
        , exts: 'xlsx|xls'
        , before: function (obj) {
        }
        , done: function (res) {
            if (res.success){
                layer.msg(res.data, {icon: 6, time: 1000});
            }else {
                layer.msg(data.data, {icon: 5, time: 1000});
            }
        }
    });
});

<button type="button" class="layui-btn layui-btn-primary layui-btn-sm" lay-data="{url:'warehouseExcelIn'}"
        id="inputBtn"><i class="layui-icon">&#xe67c;</i>批量导入
    <!--        <button type="button" class="layui-btn test333" lay-data="{size:10,url:'warehouseExcelIn'}" id="test3"><i class="layui-icon">&#xe67c;</i>上传文件-->
</button>