esayExcel导入导出

一、导入

1.引入esayExcel JAR包

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.7</version>
</dependency>

2.创建Model类

package com.bjsasc.avmom.listener;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class ZytzModel {
    // 资源名称
    @ExcelProperty(value = "资源名称")
    private String res_name;
    // 资源类型
    @ExcelProperty(value = "资源类型")
    private String res_classname;
    // 岗位呼号
    @ExcelProperty(value = "岗位呼号")
    private String callSign;
    // 型号
    @ExcelProperty(value = "型号")
    private String modelNum;
    // 出厂编号
    @ExcelProperty(value = "出厂编号")
    private String factoryId;
    // 有效期
    @ExcelProperty(value = "有效期")
    private String validityInspTime;
    // 检定日期
    @ExcelProperty(value = "检定日期")
    private String docimasyTime;
    // 参数
    @ExcelProperty(value = "参数")
    private String parameter;
    // 精度
    @ExcelProperty(value = "精度")
    private String accuracy;
    // 量程
    @ExcelProperty(value = "量程")
    private String capacity;
    // 图号
    @ExcelProperty(value = "图号")
    private String drawNum;
    // 位置
    @ExcelProperty(value = "位置")
    private String location;
    // 资源状态
    @ExcelProperty(value = "资源状态")
    private String status;
}

3.创建Listener

package com.bjsasc.avmom.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.listener.ReadListener;
import com.bjsasc.adp.datastorage.entity.PtObject;
import com.bjsasc.asp.dev.common.utils.IdUtils;
import com.bjsasc.avmom.core.service.emsExtended.IEmsBaseService;
import com.bjsasc.avmom.util.Code165Utils;
import lombok.SneakyThrows;
import org.apache.commons.beanutils.BeanUtils;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class ZytzReadListener implements ReadListener<ZytzModel> {

    private final List<PtObject> errorList;
    private final IEmsBaseService emsBaseService;
    private final Code165Utils code165Utils;

    private static final int BATCH_COUNT = 100;
    /**
     * 缓存的数据
     */
    private List<PtObject> cachedDataList = new ArrayList<>(BATCH_COUNT);


    public ZytzReadListener(List<PtObject> errorList, IEmsBaseService emsBaseService, Code165Utils code165Utils) {
        this.errorList = errorList;
        this.emsBaseService = emsBaseService;
        this.code165Utils = code165Utils;
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {

    }

    @SneakyThrows
    @Override
    public void invoke(ZytzModel data, AnalysisContext context) {
        boolean flag = true;
        Integer rowNum = context.readRowHolder().getRowIndex() + 1;
        StringBuilder stringBuilder = new StringBuilder();
        PtObject ptObject = new PtObject();
        PtObject errorPtObject = new PtObject();
        Map<String, String> describe = BeanUtils.describe(data);
        ptObject.putAll(describe);
        // 查询对应的资源分类classId与treeInnerId
        if (data.getRes_classname() != null && !data.getRes_classname().equals("")){
            this.findResClassId(data.getRes_classname(), ptObject);
            // 如果为""字符串表示为查找到
            if (ptObject.get("classId").toString().equals("")){
                flag = false;
                stringBuilder.append(String.format("第%s行资源分类名称错误请检查;", rowNum));
            }
        } else {
            flag = false;
            stringBuilder.append(String.format("第%s行资源分类名称为空请检查;", rowNum));
        }
        // 如果出厂编号不为空查询数据库是否已存在
        if (data.getFactoryId() != null && !data.getFactoryId().equals("")){
            String factoryId = data.getFactoryId();
            String sql = "select * from prod_base where factoryId = '" + factoryId +"' and treeInnerId = '" + ptObject.get("treeInnerId")+"'";
            List<Map<String, Object>> maps = emsBaseService.selectDataBySql(sql);
            if (!maps.isEmpty()){
                flag = false;
                stringBuilder.append(String.format("第%s行资源出厂编号已存在请检查;", rowNum));
            }
        }
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd");
        String validityInspTime = data.getValidityInspTime();
        String docimasyTime = data.getDocimasyTime();
        if (docimasyTime != null && !Objects.equals(docimasyTime, "")){
            try {
                ptObject.put("docimasyTime", simpleDateFormat.parse(docimasyTime).getTime());
            } catch (ParseException e) {
                flag = false;
                stringBuilder.append(String.format("第%s行检定日期格式不正确请检查;", rowNum));
            }
        }
        if (validityInspTime != null && !Objects.equals(validityInspTime, "")){
            try {
                ptObject.put("validityInspTime", simpleDateFormat.parse(validityInspTime).getTime());
            } catch (ParseException e) {
                flag = false;
                stringBuilder.append(String.format("第%s行有效期格式不正确请检查;", rowNum));
            }
        }
        String status = data.getStatus();
        if (status != null && !status.equals("")){
            switch (status){
                case "正常":
                    ptObject.put("status", "0");
                    break;
                case "已使用":
                    ptObject.put("status", "1");
                    break;
                case "检定中":
                    ptObject.put("status", "2");
                    break;
                case "报废":
                    ptObject.put("status", "3");
                    break;
                default:
                    ptObject.put("status", "");
            }
        } else {
            flag = false;
            stringBuilder.append(String.format("第%s行资源状态为空请检查;", rowNum));
        }

        if (flag){
            ptObject.setInnerId(IdUtils.randomUUID().replace("-", ""));
            ptObject.put("createTime", new Date().getTime());
            String classId = ptObject.get("classId").toString();
            classId=classId.replace("prod_base_","");
            classId=classId.split("_")[0].toUpperCase();
            String code = code165Utils.getRunningValue("prod_base", "物资编码", classId+"-");
            ptObject.put("code", code);
            ptObject.put("useNumber", 0);
            ptObject.put("useTime", 0);
            ptObject.put("varyTime", 0);
            ptObject.put("varyNumber", 0);
            cachedDataList.add(ptObject);
            if (cachedDataList.size() >= BATCH_COUNT) {
                emsBaseService.saveBatch(cachedDataList);
                // 存储完成清理 list
                cachedDataList = new ArrayList<>(BATCH_COUNT);
            }
        } else {
            errorPtObject.put("rowNum", rowNum);
            errorPtObject.put("msg", stringBuilder.toString());
            errorList.add(errorPtObject);
        }
    }

    @Override
    public void extra(CellExtra extra, AnalysisContext context) {

    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        emsBaseService.saveBatch(cachedDataList);
    }

    @Override
    public boolean hasNext(AnalysisContext context) {
        return true;
    }

    @Override
    public void invokeHead(Map headMap, AnalysisContext context) {

    }

    private void findResClassId(String resClassName, PtObject ptObject){
        switch (resClassName){
            case "安全阀":
                ptObject.put("classId", "prod_base_aqfj_aqf");
                ptObject.put("treeInnerId", "d742aae3471409f936b3f719ccddfece");
                break;
            case "爆破膜片":
                ptObject.put("classId", "prod_base_aqfj_bpmp");
                ptObject.put("treeInnerId", "0faa67fba8af86d3b69ba227e9431ab3");
                break;
            case "煤油泵前短管":
                ptObject.put("classId", "prod_base_bqdg_my");
                ptObject.put("treeInnerId", "38ec8c63ed6800d9275954b191ebc235");
                break;
            case "液氧泵前短管":
                ptObject.put("classId", "prod_base_bqdg_yy");
                ptObject.put("treeInnerId", "f8a6cbc241ca2ffc0afef6bdfceab52a");
                break;
            case "推力传感器":
                ptObject.put("classId", "prod_base_cgq_bjlcgq");
                ptObject.put("treeInnerId", "0a08c925e86b3b3931ef25e1d6a27b5b");
                break;
            case "流量传感器":
                ptObject.put("classId", "prod_base_cgq_llcgq");
                ptObject.put("treeInnerId", "203413527e1fde553e3606545b51e347");
                break;
            case "温度传感器":
                ptObject.put("classId", "prod_base_cgq_wdcgq");
                ptObject.put("treeInnerId", "1f20a8835afa9d42bab3f2d924d844d8");
                break;
            case "压力传感器":
                ptObject.put("classId", "prod_base_cgq_ylcgq");
                ptObject.put("treeInnerId", "c37ed98839e906da973d1204610f41d5");
                break;
            case "振动传感器":
                ptObject.put("classId", "prod_base_cgq_zdcgq");
                ptObject.put("treeInnerId", "270803326e77bf55a94fdb25e22e9258");
                break;
            case "工艺验收电缆":
                ptObject.put("classId", "prod_base_cldl_gyys");
                ptObject.put("treeInnerId", "548f6a8c281092b36e2b2b28559c245b");
                break;
            case "汇总电缆":
                ptObject.put("classId", "prod_base_cldl_hzdl");
                ptObject.put("treeInnerId", "3d86f693db59bfbc47aee4638425802b");
                break;
            case "遥测电缆":
                ptObject.put("classId", "prod_base_cldl_ycdl");
                ptObject.put("treeInnerId", "7beece1a83e251bb124ca5c185826fa5");
                break;
            case "振动电缆":
                ptObject.put("classId", "prod_base_cldl_zd");
                ptObject.put("treeInnerId", "369e4d504ae9c30192772a2a85cda498");
                break;
            case "直流电源":
                ptObject.put("classId", "prod_base_dy");
                ptObject.put("treeInnerId", "d5644c7b072c9edff67d881a1a31a38b");
                break;
            case "阀门":
                ptObject.put("classId", "prod_base_fm");
                ptObject.put("treeInnerId", "58d25260b525fe70bc1090c3ecb8c997");
                break;
            case "辅助管道":
                ptObject.put("classId", "prod_base_fzgd");
                ptObject.put("treeInnerId", "79fdbc322d71d8a528ceb9595d0d8a3e");
                break;
            case "煤油泵前过滤器":
                ptObject.put("classId", "prod_base_glq_myjzglq");
                ptObject.put("treeInnerId", "a423c0a34e78824624e459411ae20476");
                break;
            case "气路系统过滤器":
                ptObject.put("classId", "prod_base_glq_qlxtglq");
                ptObject.put("treeInnerId", "fc62b373ce65ae940b369599a9b0bfb3");
                break;
            case "液氧泵前过滤器":
                ptObject.put("classId", "prod_base_glq_yyjzglq");
                ptObject.put("treeInnerId", "1e75f9e4154fbe8710f51552ba19221b");
                break;
            case "金属软管":
                ptObject.put("classId", "prod_base_jsrg");
                ptObject.put("treeInnerId", "744da2253d94dc493062e53244c9b371");
                break;
            case "晶振板":
                ptObject.put("classId", "prod_base_jzg");
                ptObject.put("treeInnerId", "8cd2ff71038aec212a3e67a6a8ab4038");
                break;
            case "控制电缆":
                ptObject.put("classId", "prod_base_kzdl");
                ptObject.put("treeInnerId", "d5b49a075f131244f2f0948b29420dfc");
                break;
            case "气瓶":
                ptObject.put("classId", "prod_base_qp");
                ptObject.put("treeInnerId", "aabb3ad2d955264a5d7529ddc5f134ef");
                break;
            case "仪表":
                ptObject.put("classId", "prod_base_yb");
                ptObject.put("treeInnerId", "a63f4bd75015d3b73674daeffb58eff6");
                break;
            case "压力表":
                ptObject.put("classId", "prod_base_ylb");
                ptObject.put("treeInnerId", "f291332eb8711662bd97e703a4bbee2f");
                break;
            case "压力管道":
                ptObject.put("classId", "prod_base_ylgd");
                ptObject.put("treeInnerId", "d67365c26a7b7c3c90077fa7fefc220d");
                break;
            case "引压管":
                ptObject.put("classId", "prod_base_yyg");
                ptObject.put("treeInnerId", "4f6e8fa2447e3f0be01e5f132f0f64ab");
                break;
            case "转接管":
                ptObject.put("classId", "prod_base_zjg");
                ptObject.put("treeInnerId", "fcf4e27a20f16776a027398885e1f2a2");
                break;
            default:
                ptObject.put("classId", "");
                ptObject.put("treeInnerId", "");
        }
    }
}

4.创建Controller接口和实现

    /**
     * 导入资源数据
     *
     * @param file
     * @return
     */
    @Override
    public List<PtObject> impordProd(MultipartFile file) throws IOException {
        String fileName = file.getOriginalFilename();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new RuntimeException("上传文件格式不正确");
        }
        List<PtObject> errorList = new ArrayList<>();
        ZytzReadListener zytzReadListener = new ZytzReadListener(errorList, emsBaseService, code165Utils);
        EasyExcel.read(file.getInputStream(), ZytzModel.class, zytzReadListener).sheet().doRead();
        return errorList;
    }

二、导出

1.引入esayExcel JAR包

    /**
     * 导出资源excel
     * @param response
     * @param innerIds
     */
    @Override
    public void exportProd(HttpServletResponse response, String innerIds) {
        String[] split = innerIds.split(",");
        String sql = "select * from prod_base ";
        if (!innerIds.equals("") && split.length > 0){
            sql += "where INNERID in ('" + innerIds.replaceAll(",","','") +"')";
        }
        List<Map<String, Object>> maps = emsBaseService.selectDataBySql(sql);
        String[] head = {"资源名称", "资源类型", "岗位呼号", "型号", "资源状态", "出厂编号", "有效期","检定日期", "参数", "精度", "量程", "图号", "位置", "制造单位", "公称直径", "整定压力","累计使用时间", "累计使用次数", "单次使用时间", "单次使用次数", "备注"};
        List<List<String>> headList = new ArrayList<>();
        for (String s : head) {
            List<String> list = new ArrayList<>();
            list.add(s);
            headList.add(list);
        }
        List<List<Object>> dataList = new ArrayList<>();
        for (Map<String, Object> map : maps) {
            List<Object> data = new ArrayList<>();
            data.add(map.get("res_name") != null ? map.get("res_name").toString() : "");
            data.add(map.get("res_classname") != null ? map.get("res_classname").toString() : "");
            data.add(map.get("callSign") != null ? map.get("callSign").toString() : "");
            data.add(map.get("modelNum") != null ? map.get("modelNum").toString() : "");
            if (map.get("status") != null){
                switch (map.get("status").toString()){
                    case "0": data.add("正常"); break;
                    case "1": data.add("已使用"); break;
                    case "2": data.add("检定中"); break;
                    case "3": data.add("报废"); break;
                    default: data.add("");
                }
            } else {
                data.add("");
            }
            data.add(map.get("factoryId") != null ? map.get("factoryId").toString() : "");
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            // 有效期
            if (map.get("validityInspTime") != null){
                Date date = new Date(Long.parseLong(map.get("validityInspTime").toString()));
                String format = simpleDateFormat.format(date);
                data.add(format);
            } else {
                data.add("");
            }
            // 检定日期
            if (map.get("docimasyTime") != null){
                Date date = new Date(Long.parseLong(map.get("docimasyTime").toString()));
                String format = simpleDateFormat.format(date);
                data.add(format);
            } else {
                data.add("");
            }
            data.add(map.get("parameter") != null ? map.get("parameter").toString() : "");
            data.add(map.get("accuracy") != null ? map.get("accuracy").toString() : "");
            data.add(map.get("capacity") != null ? map.get("capacity").toString() : "");
            data.add(map.get("drawNum") != null ? map.get("drawNum").toString() : "");
            data.add(map.get("location") != null ? map.get("location").toString() : "");
            data.add(map.get("zzdw") != null ? map.get("zzdw").toString() : "");
            data.add(map.get("gczj") != null ? map.get("gczj").toString() : "");
            data.add(map.get("zdyl") != null ? map.get("zdyl").toString() : "");
            data.add(map.get("useTime") != null ? map.get("useTime").toString() : "");
            data.add(map.get("useNumber") != null ? map.get("useNumber").toString() : "");
            data.add(map.get("varyTime") != null ? map.get("varyTime").toString() : "");
            data.add(map.get("varyNumber") != null ? map.get("varyNumber").toString() : "");
            data.add(map.get("remark") != null ? map.get("remark").toString() : "");
            dataList.add(data);
        } 
        try {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("试验资源", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + System.currentTimeMillis() + ".xlsx");
        EasyExcel.write(response.getOutputStream())
                .registerConverter(new LongStringConverter())
                .head(headList)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet("模板")
                .doWrite(dataList);
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}
posted @ 2024-11-05 19:53  chahune  阅读(114)  评论(0)    收藏  举报