首页 > 其他分享 >esayExcel导入导出

esayExcel导入导出

时间：2024-11-05 19:41:24浏览次数：4  
标签：classId map get 导出 esayExcel ptObject 导入 put data

一、导入

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);
    }
}

标签：classId,map,get,导出,esayExcel,ptObject,导入,put,data
From： https://www.cnblogs.com/chahune/p/18528664

相关文章

  • powerdesigner导出pdf
    设置合适页面大小：当图比较多的时候，默认是无法在一个页面（默认为A4页面尺寸）展示所有的，这是有两种方式：方式一(推荐)：自适应即单击Fittopage或者Centertopage导出PDF:默认打开很小（A4页面）：但是放大后，清晰度杠杠的：......
  • MySQL导入sql文件报错：2006 - MySQL server has gone away(转载)
    今天在在MySQL导入sql文件，导入失败，出现如下错误：2006-MySQLserverhasgoneaway，之前也遇到过，又一次遇到，还是记录一下吧！【问题】导入的sql文件大概有15M，导入过程中报错：2006-MySQLserverhasgoneaway  【解决办法】1、找到MySQL安装目录下的my.ini文件，修改max_allo......
  • Java实现导出多个excel表打包到zip文件中，供客户端另存为窗口下载
    @目录一、业务背景二、实现思路二、准备工作1.准备data模板.xlsx2.引入poi相关依赖，用于操作excel3.针对WorkBook+ZIP压缩输入/输出流，相关方法知识点要有所了解三、完整的项目代码四、可能遇到的问题错误场景1：java.io.IOException:Streamclosed错误场景2：调用接口没有另存为弹窗，......
  • scala的包及其导入
    Scala的包的定义：Scala的包用于解决类的命名冲突和类的文件管理。在引入类时加上包名可以区分不同的类，解决类名冲突的问题。 Scala的包创建步骤： 1.创建包:在src上右键，新建软件包2.填写包名:小写3.在包上右键，创建类。自动加入包名4.导入:import包名.类名  导入......
  • blender导入的图片渲染看不见，图片预览正常，但渲染不出
    在使用Blender时，我们经常会遇到导入图片后在预览渲染中显示，但在实际渲染时图片消失的问题。本文将提供详细的解决方法，帮助大家解决“Blender导入的图片渲染图像不显示”的问题。问题原因导入的图片在Blender中只是一张图，并非网格，因此需要将其设置成网格才能进行渲染。解决方案......
  • dubbo3.0 服务导入导出原理
    不管是服务导出还是服务引入，都发生在应用启动过程中，比如：在启动类上加上@EnableDubbo时，该注解上有一个@DubboComponentScan注解，@DubboComponentScan注解Import了一个DubboComponentScanRegistrar，DubboComponentScanRegistrar中会调用DubboSpringInitializer.initialize......
  • blender动画导出Unity
    在Blender构建一个带有骨骼的模型为根骨骼（Root）构建动画导出FBX文件这里我是用了个插件，BetterFBXImport注意导出时，要将动画设置在第1帧，即小球在原点位置，不然导出后的FBX的原点会跑歪导入进Unity，设置一下根骨参数动画参数调整注意这3个选项是询问你，是否将旋转，X/Y/Z......
  • jpeg,png,bmp转换成icon,windows本地导出icon文件
    前言通过软件icon-maker实现将文件格式转换目前主流皆为网页在线转换,有时迫于环境原因,需要离线状态的转换工作于是有了本篇文章目录前言安装链接安装移动软件位置(可选)输入注册码安装链接官方链接:https://zh.softorbits.net/icon-maker/软件分享链接(百度网盘):......
  • 修改PE导入表注入DLL——实例图文教程——让你看的明明白白
    一、前言其实通过修改PE导入表注入DLL的教程很多，本文也只是其中的沧海一粟而已，但既然写出来，自我感觉应该还是有一点自我的东西的，至少自认为做到了思路清晰，每步都有据可依，让看客应该能做到“看的明明白白”！本贴以《英雄无敌》1游戏程序为例子，向其添加一个DLL，调用其中的导出函数可......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99