Easypoi 解析 FTP 文件服务器指定目录下的 Excel 文件,并将解析的数据入库。
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.4.0</version>
</dependency>
<!--主要实现ftp文件的相关操作-->
<dependency>
<groupId>commons-net</groupId>
<artifactId>commons-net</artifactId>
<version>3.6</version>
</dependency>
<dependency>
<groupId>org.bouncycastle</groupId>
<artifactId>bcprov-jdk16</artifactId>
<version>1.46</version>
</dependency>
package com.demo.utils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPReply;
import java.io.IOException;
/**
* @ClassName:FtpUtils.java
* @ClassPath:com.demo.utils.FtpUtils.java
* @Description:ftp链接
* @Author:tanyp
* @Date:2023/1/6 9:36
**/
@Slf4j
public class FtpUtils {
/**
* ftp链接
*
* @return
* @throws IOException
*/
public static FTPClient ftpConnection(String ip, int port, String username, String password) throws IOException {
FTPClient ftpClient = new FTPClient();
try {
ftpClient.connect(ip, port);
ftpClient.login(username, password);
// 是否成功登录服务器
int replyCode = ftpClient.getReplyCode();
if (!FTPReply.isPositiveCompletion(replyCode)) {
ftpClient.disconnect();
log.info("--ftp连接失败--");
System.exit(1);
} else {
log.info("--ftp连接成功--");
}
} catch (Exception e) {
log.error("ftp连接异常:{}", e);
}
return ftpClient;
}
/**
* 断开FTP连接
*
* @param ftpClient 初始化的对象
* @throws IOException
*/
public static void close(FTPClient ftpClient) {
try {
if (ftpClient != null && ftpClient.isConnected()) {
ftpClient.logout();
ftpClient.disconnect();
}
} catch (IOException e) {
log.error("断开FTP连接异常:{}", e);
}
}
}
package com.demo.vo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.time.LocalDateTime;
/**
* @ClassName:TestVo.java
* @ClassPath:com.demo.vo.TestVo.java
* @Description: 测试
* @Author:tanyp
* @Date:2023/1/6 09:24
**/
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class TestVo implements Serializable {
@Excel(name = "手机号", width = 30, orderNum = "0")
private String phone;
@Excel(name = "名称", width = 30, orderNum = "0")
private String name;
@Excel(name = "时间", width = 30, importFormat ="yyyy-MM-dd HH:mm:ss", exportFormat = "yyyy-MM-dd HH:mm:ss", orderNum = "0")
private LocalDateTime dateTime;
@Excel(name = "数据", width = 50, orderNum = "0")
private String data;
}
package com.demo.service.impl;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.demo.utils.FtpUtils;
import com.demo.entity.TestFtp;
import com.demo.vo.TestVo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Objects;
/**
* @ClassName:TestService.java
* @ClassPath:com.demo.service.impl.TestService.java
* @Description: 解析数据
* @Author:tanyp
* @Date:2023/1/6 11:13
**/
@Slf4j
@Service
public class TestService {
@Autowired
private TestFtpService testFtpService;
@Autowired
private TestDataService testDataService;
@Override
public void test() {
log.info("FTP拉取数据....开始.....");
FTPClient ftpClient = null;
try {
// FTP信息
ftpClient = FtpUtils.ftpConnection("127.0.0.1", 21, "ftp_test", "ftp_test");
// 按天创建文件夹,获取目录下的所有文件
String remotePath = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
boolean isExist = ftpClient.changeWorkingDirectory(remotePath);
if (!isExist) {
log.info("{} 该目录不存在!", remotePath);
return;
}
// 不加此句获取不到文件
ftpClient.enterLocalPassiveMode();
// 设置文件类型
ftpClient.setFileType(FTPClient.BINARY_FILE_TYPE);
FTPFile[] files = ftpClient.listFiles();
if (files != null && files.length > 0) {
log.info("文件数:{}", files.length);
for (FTPFile file : files) {
log.info("开始检查当前文件是否已被解析过:{}", file.getName());
/**
* TODO
* 保存解析记录,防止重复解析
*/
Integer count = testFtpService.count(
Wrappers.<TestFtp>query().lambda()
.eq(TestFtp::getFileName, file.getName())
.eq(TestFtp::getFilePath, remotePath + "/" + file.getName())
);
if (Objects.nonNull(count) && count > 0) {
log.info("此文件已经解析过:{}", file.getName());
continue;
}
log.info("解析数据===开始===:{}", file.getName());
InputStream inputStream = ftpClient.retrieveFileStream(file.getName());
// 解析数据
ImportParams params = new ImportParams();
List<TestVo> result = ExcelImportUtil.importExcel(inputStream, TestVo.class, params);
inputStream.close();
if (Objects.nonNull(result) && result.size() > 0) {
log.info("保存已解析的数据,条数:{}", result.size());
testDataService.saveBatch(result);
log.info("保存文件记录:{}", file.getName());
testFtpService.save(
TestFtp.builder()
.fileName(file.getName())
.filePath(remotePath + "/" + file.getName())
.rowCount(result.size())
.updateTime(LocalDateTime.now())
.createTime(LocalDateTime.now())
.build()
);
}
log.info("解析数据===结束===:{}", file.getName());
}
} else {
log.info("FTP拉取数据中...{}..该目录下未检测到文件....", remotePath);
}
log.info("FTP拉取数据...结束......");
} catch (Exception e) {
log.error("FTP拉取数据{}", e);
} finally {
FtpUtils.close(ftpClient);
}
}
}
示例使用 mybatisplus 的持久层框架,缺失类文件可自行补全。