Excel导出导入

1，导入依赖

        <!--文件导出导入依赖-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.3.0</version>
            <exclusions>
                <exclusion>
                    <artifactId>commons-lang3</artifactId>
                    <groupId>org.apache.commons</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>

2.文件导出导入工具类

package com.sybd.ilmp.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.sybd.ilmp.core.constant.ErrorConstants;
import com.sybd.ilmp.core.exception.IlmpRuntimeException;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * @Author user
 */
public class ExcelUtil {

    /**
     * Excel导出
     * @param list 需要导出的集合
     * @param title 表头
     * @param sheetName 表名
     * @param pojoClass 映射的实体类
     * @param fileName 文件名
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,
                                   String fileName, HttpServletResponse response){

        //添加表头信息
        ExportParams params = new ExportParams(title, sheetName);
        //是否创建表头
        params.setCreateHeadRows(true);
        //导出表格
        Workbook workbook = ExcelExportUtil.exportExcel(params, pojoClass, list);
        //浏览器输出表格
        try {
            response.setContentType("UF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName+".xls", "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            //文件导出失败
            throw new IlmpRuntimeException(ErrorConstants.FILE_EXPORT_FAIL);
        }
    }

    /**
     * Excel导入
     * @param file excel表格文件
     * @param titleRows 表标题行数
     * @param headerRows 表头的行数（不正确会影响导入list的条数）
     * @param pojoClass 映射实体类
     */
    public static <T> List<T> importExcel(MultipartFile file,Integer titleRows,Integer headerRows, Class<T> pojoClass){
        if (file == null){
            throw new IlmpRuntimeException("请传入文件！");
        }
        //设置表头信息
        ImportParams params = new ImportParams();
        params.setHeadRows(headerRows);
        params.setTitleRows(titleRows);
        Map<String, Object> msgMap = new HashMap<>();
        msgMap.put("title","设备名称,单灯编号,网关类型,绑定灯杆编号,IMEI,IMSI,协议版本号,单灯型号");
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(),pojoClass,params);
        } catch (NoSuchElementException e ) {
            throw new IlmpRuntimeException("Excel文件不能为空！");
        }catch (Exception e){
            throw new IlmpRuntimeException("请严格参考<title>检查导入Excel文件是否存在空值或格式错误或表头名称错误！",msgMap);
        }
        return list;
    }
}

3.实体类

导出实体类VM

package com.sybd.ilmp.controller.vm;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import lombok.ToString;

import java.util.List;

/**
 * @Author 
 */
@Data
@ToString
public class LightExcleVM {

    private Long id;
    private Long lightId;
    private Integer deviceId;
    private Long lampId; //灯杆id
    private String lampName; //灯杆名称
    @Excel(name = "灯杆编号",orderNum = "3")
    private String lampCode; //灯杆编号
    @Excel(name = "所属区域",orderNum = "10")
    private String areaName;
    @Excel(name = "所属项目",orderNum = "9")
    private String projectName;
    private Integer onlineStatus;
    @Excel(name = "使用状态",orderNum = "4",replace = {"使用中_1","停用中_0"})
    private Integer useStatus; //使用状态灯杆的
    private Integer lockStatus;
    private Integer lightStatus;
    private Integer positionStatus;
    @Excel(name = "维护公司",orderNum = "16")
    private String companyName;
    private List<Long> list;
    private Long projectId; //项目Id
    @Excel(name = "创建时间",orderNum = "11")
    private String createTime; //创建时间
    @Excel(name = "单灯编号",orderNum = "1")
    private String code; // 单灯编号
    @Excel(name = "单灯型号",orderNum = "8")
    private String lightModel; //型号
    @Excel(name = "设备名称",orderNum = "2")
    private String name;//单灯名称
    @Excel(name = "IMEI",orderNum = "6")
    private String imei; //imei编号
    @Excel(name = "IMSI",orderNum = "7")
    private String imsi; //imsi编号
    @Excel(name = "网关类型",orderNum = "5",replace = {"网关_0","非网关_1"})
    private Integer gatewayType;
    @Excel(name = "安装时间",orderNum = "12")
    private String installTime; //安装时间
    @Excel(name = "安装地点",orderNum = "13")
    private String installPlace; //安装地点
    private String version; //版本协议号
    private Integer resourceStatus;
    private Long maintenanceUserId;
    /**
     * 经度
     */
    @Excel(name = "经度",orderNum = "14")
    private Double lon;
    /**
     * 纬度
     */
    @Excel(name = "纬度",orderNum = "15")
    private Double lat;
    private String lampInstallAddress;
}

导入实体类DTO

package com.sybd.ilmp.service.dto;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.*;

import java.io.Serializable;


/**
 * 只用于excel导入
 *
 * @Author USER
 */
@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class LightExcelDto implements Serializable {

    /**
     * 单灯名称
     */
    @Excel(name = "设备名称")
    @NonNull
    private String name;
    /**
     * 单灯编号
     */
    @Excel(name = "单灯编号")
    @NonNull
    private String code;

    /**
     * 类型
     */
    @Excel(name = "网关类型",replace = {"网关_0","非网关_1"})
    @NonNull
    private Integer gatewayType;
    /**
     *灯杆编号
     * 可以不绑定灯杆编号
     */
    @Excel(name = "绑定灯杆编号")
    private String  lampCode;
    private Long lampId;

    @Excel(name = "IMEI")
    private String imei;

    @Excel(name = "协议版本号")
    private String version; //版本协议号

    @Excel(name = "IMSI")
    private String imsi; //imsi编号

    @Excel(name = "单灯型号")
    @NonNull
    private String model;

    private String failMsg1;
    private String failMsg2;
}

4.逻辑处理类

package com.sybd.ilmp.service;

import com.sybd.ilmp.controller.command.LightExcelCommand;
import com.sybd.ilmp.controller.vm.LightExcleVM;
import com.sybd.ilmp.core.beans.sync.Lamp;
import com.sybd.ilmp.core.exception.IlmpRuntimeException;
import com.sybd.ilmp.core.util.StampToTimeUtil;
import com.sybd.ilmp.dao.LampDao;
import com.sybd.ilmp.dao.LightDao;
import com.sybd.ilmp.dao.domain.Light;
import com.sybd.ilmp.dto.LightDto;
import com.sybd.ilmp.mapper.LightMapper;
import com.sybd.ilmp.service.dto.LightExcelDto;
import com.sybd.ilmp.utils.ExcelUtil;
import com.vip.vjtools.vjkit.collection.CollectionUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author user
 */
@Service
public class LightExcelService {
    @Autowired
    private LightDao lightDao;
    @Autowired
    private LightService lightService;
    @Autowired
    private LightMapper lightMapper;
    @Autowired
    private LampDao lampDao;

    /**
     * 单灯导出Excel表格
     * @param command
     * @param resp
     */
    public void LightExportExcel(LightExcelCommand command, HttpServletResponse resp){
        String title = "单灯信息";
        String sheetName = "单灯信息";
        String fileName = "单灯信息";
        //全选
        if (command.getSelectMode() == 0){
            List<LightExcleVM> lightExcleVMS = lightDao.selectByCondition(command);
            //时间转换，数据库是时间戳
            for (LightExcleVM vm: lightExcleVMS){
                vm.setCreateTime(StampToTimeUtil.stampToTime(vm.getCreateTime()));
                vm.setInstallTime(StampToTimeUtil.stampToTime(vm.getInstallTime()));
            }
            ExcelUtil.exportExcel(lightExcleVMS,title,sheetName,LightExcleVM.class,fileName,resp);
        }
        if (command.getSelectMode() == 1){
            ArrayList<LightExcleVM> list = new ArrayList<>();
            //切割字符串
            String idsLightCode = command.getIdsLightCode();
            String[] split = idsLightCode.split(",");
            for (String lightCode: split){
                command.setLightCode(lightCode);
                LightExcleVM vm = lightDao.selectByCode(command);
                list.add(vm);
            }
            for (LightExcleVM vm: list){
                vm.setCreateTime(StampToTimeUtil.stampToTime(vm.getCreateTime()));
                vm.setInstallTime(StampToTimeUtil.stampToTime(vm.getInstallTime()));
            }
            ExcelUtil.exportExcel(list,title,sheetName,LightExcleVM.class,fileName,resp);
        }
    }

    /**
     * 单灯导入Excel表格
     * @param file
     */
    public Map lightImportExcel(MultipartFile file){
        //创建返回类
        HashMap<Object, Object> resultMap = new HashMap<>();
        ArrayList<Object> resultList = new ArrayList<>();
        int succCount = 0;
        int failCount = 0;
        //没有表标题，行数为0，导入表格不能设置表标题
        Integer titleRows = 0;
        Integer headerRows = 1;
        //获取文件list
        List<LightExcelDto> dtos = ExcelUtil.importExcel(file, titleRows, headerRows, LightExcelDto.class);
        //判断是否为空
        if (CollectionUtil.isNotEmpty(dtos)){
            //判断必填表头信息是否正确，不正确的话 低下字段所有都会为null.先取一个值验证
            //重要说明：此处的判断为表头字段是否正确。但《绑定灯杆编号》又可以为空。当遇到灯杆编号有值却无法绑定时，请检查表头是否为《绑定灯杆编号》；
            Map<String, Object> msgMap = new HashMap<>();
            msgMap.put("title","设备名称,单灯编号,网关类型,绑定灯杆编号,IMEI,IMSI,协议版本号,单灯型号");
            LightExcelDto dto = dtos.get(0);
            if (dto.getCode() == null || dto.getGatewayType() == null || dto.getModel() == null || dto.getName() == null ){
                throw new IlmpRuntimeException("表头数据名称错误，请重新核对！",msgMap);
            }
            //开始新增到系统内部
            for (LightExcelDto DTO: dtos) {
                //单灯去重,如果为空
                Light single = lightDao.createLambdaQuery().andEq(Light::getCode, DTO.getCode()).single();
                if (StringUtils.isEmpty(single)) {
                    try {
                        //如果绑定灯杆
                        if (DTO.getLampCode() != null) {
                            //查询LampId,表格中传入的是lampCode
                            Lamp lamp = lampDao.createLambdaQuery().andEq(Lamp::getCode, DTO.getLampCode()).single();
                            if (lamp != null) {
                                DTO.setLampId(lamp.getId());
                            } else {
                                DTO.setFailMsg1(DTO.getLampCode() + "--->灯杆编号不存在！");
                                throw new IlmpRuntimeException(DTO.getLampCode() + "--->灯杆编号不存在！");
                            }
                        }
                        //映射一下
                        LightDto lightDto = lightMapper.LightExcelDtoToDto(DTO);
                        //访问新增单灯接口
                        Light light = null;

                        light = lightService.createLight(lightDto);
                        if (light != null) {
                            succCount++;
                        }
                    } catch (Exception e) {
                        failCount++;
                        DTO.setFailMsg2("访问新增单灯接口新增失败！," + e.getMessage());
                        resultList.add(DTO);
                    }
                }
            }
            //总条数
            resultMap.put("count", dtos.size());
            resultMap.put("succCount", succCount);
            resultMap.put("failCount", failCount);
            resultMap.put("repeatCount", dtos.size() - succCount - failCount);
            resultMap.put("failList", resultList);
            resultMap.put("title","设备名称,单灯编号,网关类型,绑定灯杆编号,IMEI,IMSI,协议版本号,单灯型号");
            resultMap.put("seeMe","当遇到灯杆编号有值却无法绑定时，请检查表头是否为《绑定灯杆编号》");
        }else {
            throw new IlmpRuntimeException("表格内容为空，并检查表格导入模版是否正确，重新导入！");
        }
        return resultMap;
    }
}