Excel解析上传入库【POI 和 EasyPoi】

约定不等于承诺〃 2021-11-13 22:08 736阅读 0赞

**目录**

一、上传Excel至FastDFS

二、上传文件至FTP使用EasyPOI

三、上传文件至FTP使用POI。

四：补充一下FTP以及FastDFS配置

--------------------

目前的需求：

1、将前端上传的Excel文件上传至FTP 服务器。【也可以上传至FastDFS】

2、解析Excel文件并存入数据库。【需要统计文件大小以及内部总金额】

3、入库后定时根据数据库中的表数据进行支付打款。【可以通过分布式定时任务或者MQ】

我们这里主要说Excel的解析上传及入库。

### 一、上传Excel至FastDFS ###

由于是SpringBoot项目，我们要在yml文件中添加如下配置：

fdfs:
      confPath: /src/main/resources/fdfs/dev/fdfs_client.conf
      minPoolSize: 5
      maxPoolSize: 10
      waitTimes: 20
      fileServerHost: 10.1.1.1

pom.xml文件中添加：

<dependency>
                <groupId>org.csource</groupId>
                <artifactId>fastdfs-client-java</artifactId>
                <version>1.27</version>
            </dependency>

FastDFS连接池类：

/**
     * @author xuyang
     * @date 2019/07/18
     */
    public class FastDFSConnectionPool {
        private static final Logger log = LoggerFactory.getLogger(FastDFSConnectionPool.class);
        /**
         * 空闲的连接池
         */
        private LinkedBlockingQueue<TrackerServer> idleFastDFSConnectionPool = null;
        /**
         * 连接池默认最小连接数
         */
        private long minPoolSize;
        /**
         * 连接池默认最大连接数
         */
        private long maxPoolSize;
    
        /**
         * 默认等待时间（单位：秒）
         */
        private long waitTimes;
    
        /**
         * fastdfs客户端创建连接默认1次
         */
        private static final int COUNT = 3;
    
        /**
         * 重试次数
         */
        private static final int RE_TRIE_TIME = 5;
    
        /**
         * 配置文件的路径
         */
        private String confPath;
    
        /**
         * 当前创建的连接数
         */
        private volatile long nowPoolSize = 0;
    
        /**
         * 默认构造方法
         */
        public FastDFSConnectionPool(long minPoolSize, long maxPoolSize, long waitTimes) {
    
        }
    
        public FastDFSConnectionPool(){
    
        }
    
        /**
         * * @Description: 连接池初始化 (在加载当前FastDFSConnectionPool时执行) 1).加载配置文件 * 2).空闲连接池初始化； 3).创建最小连接数的连接，并放入到空闲连接池； *
         */
        public void poolInit() {
            try {
                /*加载配置文件 */
                initClientGlobal();
                /* 初始化空闲连接池 */
                idleFastDFSConnectionPool = new LinkedBlockingQueue<TrackerServer>();
                /* 往线程池中添加默认大小的线程 */
                for (int i = 0; i < minPoolSize; i++) {
                    createTrackerServer(COUNT);
                }
                log.info("[线程池初始化方法(FastDFSConnectionPool)][" + "][默认参数：minPoolSize=" + minPoolSize + ",maxPoolSize=" + maxPoolSize + ",waitTimes=" + waitTimes + "]成功");
            } catch (Exception e) {
                log.error("[线程池初始化方法(FastDFSConnectionPool)][" + "][默认参数：minPoolSize=" + minPoolSize + ",maxPoolSize=" + maxPoolSize + ",waitTimes=" + waitTimes + "]失败",e);
            }
        }
    
        /**
         * * @Description: 创建TrackerServer,并放入空闲连接池 *
         */
        protected void createTrackerServer(int flag) {
            TrackerServer trackerServer = null;
            try {
                TrackerClient trackerClient = new TrackerClient();
                trackerServer = trackerClient.getConnection();
                while (trackerServer == null && flag < RE_TRIE_TIME ) {
                    log.info("[创建TrackerServer(createTrackerServer)][第" + flag + "次重建]");
                    flag++;
                    initClientGlobal();
                    trackerServer = trackerClient.getConnection();
                }
    //            assert trackerServer != null;
                if(trackerServer!=null){
                    org.csource.fastdfs.ProtoCommon.activeTest(trackerServer.getSocket());
                    idleFastDFSConnectionPool.add(trackerServer);
                    // 同一时间只允许一个线程对nowPoolSize操作
                    synchronized (this) {
                        nowPoolSize++;
                    }
                }else{
                    throw new NullPointerException("trackerServer为空");
                }
            } catch (Exception e) {
                log.error("[创建TrackerServer(createTrackerServer)][异常：{}]", e);
            } finally {
                if (trackerServer != null) {
                    try {
                        trackerServer.close();
                    } catch (Exception e) {
                        log.error("[创建TrackerServer(createTrackerServer)--关闭trackerServer异常][异常：{}]", e);
                    }
                }
            }
        }
    
        /**
         * @Description: 获取空闲连接
         * 1).在空闲池（idleFastDFSConnectionPool)中弹出一个连接；
         * 2).把该连接放入忙碌池（busyFastDFSConnectionPool）中; 未实现
         * 3).返回 connection
         * 4).如果没有idle connection, 等待 wait_time秒, and check again
         * @throws FastException
         */
        public TrackerServer getTrackerServer() throws FastException {
            log.info("[获取空闲连接(checkout)]");
            TrackerServer trackerServer = idleFastDFSConnectionPool.poll();
            if (trackerServer == null) {
                if (nowPoolSize < maxPoolSize) {
                    createTrackerServer(COUNT);
                    try {
                        trackerServer = idleFastDFSConnectionPool.poll(waitTimes, TimeUnit.SECONDS);
                    } catch (Exception e) {
                        log.error("[获取空闲连接(checkout)-error][error:获取连接超时:{}]", e);
                        throw FastDFSERROR.WAIT_IDLECONNECTION_TIMEOUT.error();
                    }
                }
                if (trackerServer == null) {
                    log.error("[获取空闲连接(checkout)-error][error:获取连接超时（" + waitTimes + "s）]");
                    throw FastDFSERROR.WAIT_IDLECONNECTION_TIMEOUT.error();
                }
            }
            log.info("[获取空闲连接(checkout)][获取空闲连接成功]");
            return trackerServer;
        }
    
        /**
         * @Description: 释放繁忙连接
         * 1.如果空闲池的连接小于最小连接值，就把当前连接放入idleFastDFSConnectionPool
         * 2.如果空闲池的连接等于或大于最小连接值，就把当前释放连接丢弃
         * @param trackerServer  需释放的连接对象
         */
        public void free(TrackerServer trackerServer) {
            log.info("[释放当前连接(checkin)][prams:" + trackerServer + "] ");
            if (trackerServer != null) {
                if (idleFastDFSConnectionPool.size() < minPoolSize) {
                    idleFastDFSConnectionPool.add(trackerServer);
                } else {
                    synchronized (this) {
                        if (nowPoolSize != 0) {
                            nowPoolSize--;
                        }
                    }
                }
                try {
                    trackerServer.close();
                } catch (IOException e) {
                    log.error("[关闭trackerServer异常][异常：{}]",e);
                }
            }
        }
    
        /**
         *  @Description: 删除不可用的连接，并把当前连接数减一（调用过程中trackerServer报异常，调用一般在finally中
         *  @param trackerServer
         */
        public void drop(TrackerServer trackerServer) {
            log.info("[删除不可用连接方法(drop)][parms:" + trackerServer + "] ");
            if (trackerServer != null) {
                try {
                    trackerServer.close();
                    synchronized (this) {
                        if (nowPoolSize != 0) {
                            nowPoolSize--;
                        }
                    }
                } catch (IOException e) {
                    log.info("[删除不可用连接方法(drop)--关闭trackerServer异常][异常：{}]", e);
                }
            }
        }
    
        private void initClientGlobal() throws Exception {
            ApplicationHome home = new ApplicationHome(getClass());
    //        home.getDir().getAbsolutePath()+confPath;
            String absolutePath = home.getDir().getAbsolutePath()+confPath;
            log.info("absolutePath======="+absolutePath);
    
    //        String path = this.getClass().getClassLoader().getResource("/").getPath()+confPath;
    //        log.info("path="+path);
            // FastDFS客户端配置文件
            ClientGlobal.init(absolutePath);
        }
    
        public LinkedBlockingQueue<TrackerServer> getIdleFastDFSConnectionPool() {
            return idleFastDFSConnectionPool;
        }
    
        public long getMinPoolSize() {
            return minPoolSize;
        }
    
        public void setMinPoolSize(long minPoolSize) {
            if (minPoolSize != 0) {
                this.minPoolSize = minPoolSize;
            }
        }
    
        public long getMaxPoolSize() {
            return maxPoolSize;
        }
    
        public void setMaxPoolSize(long maxPoolSize) {
            if (maxPoolSize != 0) {
                this.maxPoolSize = maxPoolSize;
            }
        }
    
        public long getWaitTimes() {
            return waitTimes;
        }
    
        public void setWaitTimes(int waitTimes) {
            if (waitTimes != 0) {
                this.waitTimes = waitTimes;
            }
        }
    
        public String getConfPath() {
            return confPath;
        }
    
        public void setConfPath(String confPath) {
            this.confPath = confPath;
        }
    }

客户端工具类：

/**
     * @Author xuyang7
     * @Date 2018/11/14
     * @Description fdfs客户端工具类
     */
    @Component
    public class FastDfsClientUtil {
    
        private static final Logger logger= LoggerFactory.getLogger(FastDfsClientUtil.class);
    
        @Value("${fdfs.fileServerHost}")
        private String fileServerHost;
    
        private static final String URL_PREFIX= "http://";
    
        private static final String PATH_SEPARATE = "/";
    
        @Autowired
        private FastDFSConnectionPool fastDFSConnectionPool;
    
        public String uploadFile(String fileContent) throws IOException, MyException {
            TrackerServer trackerServer = null;
            StringBuilder url = new StringBuilder(getFileServerHost());
            try {
                trackerServer = getTrackerServer();
                StorageClient storageClient = new StorageClient(trackerServer,null);
    
                String[] uploadFile = storageClient.upload_file(fileContent.getBytes("GBK"), "txt", null);
                for (String path : uploadFile) {
                    url.append(PATH_SEPARATE).append(path);
                }
                if(url.toString().contains(URL_PREFIX)){
                    return url.toString();
                }else{
                    url.insert(0, URL_PREFIX);
                }
            } finally {
                free(trackerServer);
            }
            return url.toString();
        }
    
        public StorageClient getStorageClient(){
            StorageClient storageClient = null;
            try {
                TrackerServer trackerServer = fastDFSConnectionPool.getTrackerServer();
                storageClient = new StorageClient(trackerServer,null);
            } catch (Exception e) {
                logger.error("获取StorageClient失败",e);
            }
            return storageClient;
        }
        public TrackerServer getTrackerServer(){
            TrackerServer trackerServer = null;
            try {
                trackerServer = fastDFSConnectionPool.getTrackerServer();
            } catch (Exception e) {
                logger.error("获取trackerServer失败",e);
            }
            return trackerServer;
        }
    
        public String getFileServerHost() {
            return fileServerHost;
        }
    
        public void setFileServerHost(String fileServerUrl) {
            this.fileServerHost = fileServerHost;
        }
    
        public void free(TrackerServer trackerServer){
            fastDFSConnectionPool.free(trackerServer);
        }
    }

测试方法：

@Test
        public void FDFSTest() throws Exception{
            StringBuilder fileContent = new StringBuilder();
            fileContent.append("Text").append("hahaha");
            String url = fastDfsClientUtil.uploadFile(fileContent.toString());
            System.out.println(url);
    
        }

### 二、上传文件至FTP使用EasyPOI ###

首先在pom.xml文件中引入如下依赖：

<dependency>
                <groupId>org.jeecg</groupId>
                <artifactId>easypoi-base</artifactId>
                <version>2.3.1</version>
            </dependency>
            
            <dependency>
                <groupId>org.jeecg</groupId>
                <artifactId>easypoi-web</artifactId>
                <version>2.3.1</version>
            </dependency>
            
            <dependency>
                <groupId>org.jeecg</groupId>
                <artifactId>easypoi-annotation</artifactId>
                <version>2.3.1</version>
            </dependency>

建立和Excel文件对应的DTO类：

/**
     * @author xuyang
     * @date 2019/07/23
     */
     @Data
    public class ExcelVO {
        //主键ID
        @Excel(name = "序号")
        private Integer id;
        //用户名称- 收款方姓名
        @Excel(name = "收款名称")
        private String payeeName;
        //注册手机号
        @Excel(name = "注册手机号")
        private String registerPhone;
        //收款金额
        @Excel(name = "收款金额(元)")
        private String amountCollected;
        //用途
        @Excel(name = "用途")
        private String reason;
    
       
    }

使用EasyPOI进行文件上传至FTP服务器以及解析入库。

/**
         * 该方法功能如下：
         * 1、将文件上传至 ftp 服务器
         * 2、解析前端Excel文件，并入库。
         * 3、待定-- 如果文件入库成功之后将文件数据发送到MQ延迟队列。
         * 4、待定-- 如果文件入库成功即可结束，然后使用DTS开启定时任务
         *          将存入库中的代付文件调用支付系统就行支付。
         * @param request
         */
        @RequestMapping("/upload")
        @Transactional(rollbackFor = Exception.class)
        public void uploadFile1(MultipartFile file){
            List<ExcelVO> excelVOS = null;
            InputStream in = null;
            ImportParams params = new ImportParams();
            try {
                // 1、解析文件
                in = file.getInputStream();
                //上传至ftp服务器            
                ftpService.uploadTAFileSign(DateUtils.getTodayDate(),file.getOriginalFilename(),(FileInputStream)file.getInputStream());
                excelVOS = ExcelImportUtil.importExcel(in, ExcelVO.class, params);
                //获取此次操作之前的最大序列号
                WalletInsteadRemittanceSummary summaryMax = impInsteadSummaryService.getMaxBatchNUmber();
                //计算出此次操作的序列号
                String newMaxBatchNumber = Objects.isNull(summaryMax)? "1":analyzeExcelUtil.increaseBatchNumber(summaryMax.getBatchUmber());
                List<WalletInsteadRemittanceDetail> irLists = excelVOS.stream().map(excel -> {
                            WalletInsteadRemittanceDetail detail = new WalletInsteadRemittanceDetail();
                            BeanUtils.copyProperties(excel,detail);
                            detail.setSeqNo(detail.getId());
                            detail.setId(null);
                            detail.setAmountCollected(analyzeExcelUtil.changeY2F(excel.getAmountCollected()));
                            detail.setStatus(detail.getAmountCollected() == 0L ?"0":"2");
                            detail.setBatchNumber(newMaxBatchNumber);
                            detail.setCreateTime(new Date());
                            return detail;
                        }
                ).collect(Collectors.toList());
                logger.info("======解析后的最终List数据！====== insertList： "+ JSON.toJSONString(irLists));
    
            } catch (Exception e) {
                e.printStackTrace();
            }
            //2、校验文件格式和参数校验
            //3、组装数据库输入对象
            //4、插入数据库
    
        }

### 三、上传文件至FTP使用POI。 ###

pom.xml文件中引入：

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.15</version>
            </dependency>
            <dependency>
                <groupId>commons-io</groupId>
                <artifactId>commons-io</artifactId>
                <version>2.5</version>
            </dependency>
            <dependency>
                <groupId>commons-fileupload</groupId>
                <artifactId>commons-fileupload</artifactId>
                <version>1.3.1</version>
            </dependency>

使用poi进行文件解析上传：

@RequestMapping("/upload2")
        @Transactional(rollbackFor = Exception.class)
        public Message uploadFile(MultipartFile file, HttpServletRequest request){
            logger.info("=======================开始上传及解析代发Excel文件=====================");
            Boolean summaryFlag;
            Boolean detailFlag;
            //开始时间
            Long startTimeUpload = System.currentTimeMillis();
            //List<BgInsteadExcelDTO> bgIExcelDTOs = null;
            Workbook book = null;
    
            try {
                book = new XSSFWorkbook(file.getInputStream());
                //ftpService.uploadTAFileSign(DateUtils.getTodayDate(),file.getOriginalFilename(),(FileInputStream)file.getInputStream());
                logger.info("====================解析代付文件成功，上传代付文件完成！===============耗时：  "+ String.valueOf(System.currentTimeMillis()-startTimeUpload) );
            } catch (Exception e) {
                try {
                    book = new HSSFWorkbook(file.getInputStream());
                    //ftpService.uploadTAFileSign(DateUtils.getTodayDate(),file.getOriginalFilename(),(FileInputStream)file.getInputStream());
                    logger.info("====================解析代付文件成功，上传代付文件完成！===============耗时：  "+ String.valueOf(System.currentTimeMillis()-startTimeUpload) );
                } catch (IOException e1) {
                    logger.error("=====================解析Excel文件出错!=============================");
                }
            }
            Sheet sheet = book.getSheetAt(0);
            List<Map<String,Object>> lirDTO = new ArrayList<>();
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Map<String,Object> map = new HashMap<>();
                //遍历一行的各个列
                lirDTO.add(convertToInsteadRemittanceInfo(sheet.getRow(rowNum),map));
            }
            //获取此次操作之前的最大序列号
            WalletInsteadRemittanceSummary summaryMax = impInsteadSummaryService.getMaxBatchNUmber();
            //计算出此次操作的序列号
            String newMaxBatchNumber = Objects.isNull(summaryMax)? "1":increaseBatchNumber(summaryMax.getBatchUmber());
            //文件入库时间
            Date fileCreateTime = new Date();
            //将List<Map<String,Object>> 转换为List<InsteadRemittanceInfo>
            List<WalletInsteadRemittanceDetail> irLists = lirDTO.stream().map(excelDTO -> {
                        WalletInsteadRemittanceDetail detail = new WalletInsteadRemittanceDetail();
                        BgInsteadExcelDTO ir = JSON.parseObject(JSONObject.toJSON(excelDTO).toString(), BgInsteadExcelDTO.class);
                        BeanUtils.copyProperties(ir,detail);
                        detail.setSeqNo(detail.getId());
                        detail.setId(null);
                        detail.setAmountCollected(changeY2F(ir.getAmountCollected()));
                        detail.setStatus(detail.getAmountCollected() == 0L ?"0":"2");
                        detail.setBatchNumber(newMaxBatchNumber);
                        detail.setCreateTime(fileCreateTime);
                        return detail;
                    }
            ).collect(Collectors.toList());
            logger.info("======解析后的最终List数据！====== insertList： "+ JSON.toJSONString(irLists));
            Long totalAmount = getExcelTotalAmount(irLists);
            //需要计算总数据条数
            Integer totalRemittance = getExcelTotalRemittance(irLists);
            logger.info("======上传文件总金额: ======总金额:  "+ totalAmount+"=======上传文件总条数:  "+totalRemittance);
            //获取到将要插入数据库的数据1、将数据插入detail 表
            WalletInsteadRemittanceSummary summary = new WalletInsteadRemittanceSummary();
            summary.setTotalAmountCollected(totalAmount);
            summary.setCreateTime(new Date());
            summary.setTotalRemittance(totalRemittance);
            summary.setTotalSuccess(totalRemittance);
            summary.setTotalFailed(0);
            summary.setBatchUmber(newMaxBatchNumber);
            summaryFlag = summaryMapper.insert(summary) > 0 ? true: false;
            logger.info("======插入Excel文件汇总表数据成功!=======总金额： "+ totalAmount+" 分");
            //入库操作开始时间
            Long startTimeStore = System.currentTimeMillis();
            detailFlag = impInsteadDetailService.saveBatch(irLists);
            logger.info("======插入Excel文件详细数据成功!  =======共计 ： "+ totalRemittance+" 条======耗时： "+String.valueOf(System.currentTimeMillis()-startTimeStore));
    
            return Messages.success();
    
        }

### 四：补充一下FTP以及FastDFS配置 ###

我们可以使用基础的

![20190801103927826.jpg][]

也可以将相关配置配置在diamond中进行集中管理。

[20190801103927826.jpg]: /images/20211113/2f1b2f0bead84025bd86c5fb09e955ba.png