以文件流的方式下载网络上的Excel文件并利用alibaba.easyexcel解析

1
2
3
4
5
6
7

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
@Data
public class TicketingModel implements Serializable {
//唯一主键
private String fGuid;
@ExcelProperty(value = "日期")
private Date date;
@ExcelProperty(value = "出票人全称")
private String fullNameOfDrawer;
@ExcelProperty(value="出票人开户银行")
private String openingBankOfDrawer;
@ExcelProperty(value="出票人社会信用代码")
private String drawerSocialCreditCode;
@ExcelProperty(value="票据类别")
private String billCategory;
@ExcelProperty(value="票据号码")
private String billNo;
@ExcelProperty(value="出票日")
private Date dateOfIssue;
@ExcelProperty(value="承兑日")
private Date acceptanceDate;
@ExcelProperty(value="到期日")
private Date DueDate;
@ExcelProperty(value = "票面金额")
private String billAmount;
@ExcelProperty(value = "收款人全称")
private String payeeAllName;
@ExcelProperty(value = "收款人开户银行")
private String payeeOpenBank;
@ExcelProperty(value = "承兑人全称")
private String acceptorAllName;
@ExcelProperty(value = "承兑人开户银行")
private String acceptorOpenBank;
@ExcelProperty(value = "是否已收票")
private String isTicketsReceived;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
@Slf4j
public class TicketingListener extends AnalysisEventListener<TicketingModel> {
// 成功量
private int successCount = 0;
// 异常量
private int exceptionCount = 0;
// 异常数据
private List<TicketingModel> exceptionList = new ArrayList<>();
List<TicketingModel> list = new ArrayList<>();
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
log.error("出票该条数据解析失败，但是继续解析下一行", exception);
TicketingModel ticketingModel
= (TicketingModel)context.readRowHolder().getCurrentRowAnalysisResult();
log.error("出票解析失败的数据为：{}", ticketingModel.toString());
exceptionList.add(ticketingModel);
exceptionCount++;
}
@Override
public void invoke(TicketingModel ticketingModel, AnalysisContext analysisContext) {
successCount++;
log.info("解析到一条出票数据:{}",ticketingModel);
list.add(ticketingModel);
PathUrlConfig constantConfig = SpringApplicationContextHolder.getBean(PathUrlConfig.class);
if (list.size() >= constantConfig.getExcel()) {
saveData();
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();
log.info("出票所有数据解析完成！！");
log.info("出票解析成功条数为：{}",successCount);
log.info("出票解析失败条数为：{}",exceptionCount);
}
public void saveData(){
try {
log.info("出票票据解析数据写入数据库开始.....");
TicketingService ticketingService = SpringApplicationContextHolder.getBean(TicketingService.class);
long startTime = System.currentTimeMillis();
ticketingService.saveTicketing(list);
long endTime = System.currentTimeMillis();
log.info("出票票据解析数据写入数据库结束，花费时间：{}", endTime - startTime);
}catch (Exception e) {
e.printStackTrace();
log.info("出票票据解析数据写入数据库异常");
}
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
@Slf4j
public abstract class AbstractExcelParser {
/**
* @Author lw
* @Description 票据Excel解析的公共抽象方法
* @Date 10:38 上午 2022/9/30
* @Param []
* @return void
**/
public final void excelParseMethod() {
//解析方法前的处理
processorBeforeParse();
//解析方法
parseMethod();
//解析方法后的处理
processorAfterParse();
}
/**
* @Author lw
* @Description 具体解析方法
* @Date 1:58 下午 2022/9/30
* @Param []
* @return void
**/
public abstract void parseMethod() ;
/**
* @Author lw
* @Description //解析报文之前验证
* @Date 1:54 下午 2022/9/30
* @Param []
* @return void
**/
public void processorBeforeParse(){
log.info("解析报文开始时间：{}", DateUtil.getCurrentDateStr());
}
/**
* @Author lw
* @Description //解析报文之后验证
* @Date 1:55 下午 2022/9/30
* @Param []
* @return void
**/
public void processorAfterParse(){
log.info("解析报文结束时间：{}", DateUtil.getCurrentDateStr());
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
@Slf4j
public class TicketingParser extends AbstractExcelParser{
@Override
public void parseMethod()
{
InputStream ins=null;
try {
String filename="http://192.168.5.219:8086/excelFile/出票.xlsx";
String docname = filename.substring(filename.lastIndexOf("/") + 1);
String urls=filename.substring(0,filename .lastIndexOf("/"))+"/"+URLEncoder.encode(docname,"utf-8");
URL url = new URL(urls);
HttpURLConnection con = (HttpURLConnection) url.openConnection();
ins = con.getInputStream();
EasyExcel.read(ins, TicketingModel.class, new TicketingListener()).sheet().headRowNumber(3).doRead();
}catch (Exception e) {
e.printStackTrace();
}finally {
try {
if (ins != null) {
ins.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
}

1
2
3
4
5
6
7
8
9
@Service
public class TicketingService {
@Autowired
private TicketingDao ticketingDao;
public void saveTicketing(List<TicketingModel> ticketingModels) throws Exception {
ticketingDao.saveTicketing(ticketingModels);
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
@Slf4j
@Repository
public class TicketingDao {
public int saveTicketing(List<TicketingModel> ticketingModels) throws Exception {
if(EmptyUtil.isEmpty(ticketingModels)){
log.info("没有需要保存的出票票据信息！！");
return 0;
}
String sql = "INSERT INTO `ZJ_TICKET` (`F_UNIQUE_ID`,`F_TIC_DATE`,`F_TIC_CPRQC`," +
"`F_TIC_CPRKHH`,`F_TIC_CPRXYDM`,`F_TIC_PJLB`,`F_TIC_PJHM`,`F_TIC_CPR`,`F_TIC_CDR`,`F_TIC_DQR`,`F_TIC_PMJE`,`F_TIC_SKRQC`,`F_TIC_SKUKHH`," +
"`F_TIC_CDRQC`,`F_TIC_QDRKHH`,`F_TIC_SFSP`,`F_TIC_CREATEDATE`,`F_TIC_MODDATE`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
JParamObject paramObject = JParamObject.getInstance();
JConnection connection = DBUtils.getConnection(paramObject);
PreparedStatement preparedStatement = null;
try {
connection = DBUtils.getConnection(paramObject);
preparedStatement = connection.prepareStatement(sql);
for(TicketingModel ticketingModel : ticketingModels){
preparedStatement.setObject(1, SnowFlakeUtil.generateKey());
preparedStatement.setObject(2, DateUtil.getDate(ticketingModel.getAcceptanceDate()));
preparedStatement.setObject(3, ticketingModel.getFullNameOfDrawer());
preparedStatement.setObject(4, ticketingModel.getOpeningBankOfDrawer());
preparedStatement.setObject(5,ticketingModel.getDrawerSocialCreditCode());
preparedStatement.setObject(6,ticketingModel.getBillCategory());
preparedStatement.setObject(7,ticketingModel.getBillNo());
preparedStatement.setObject(8,DateUtil.getDate(ticketingModel.getDateOfIssue()));
preparedStatement.setObject(9,DateUtil.getDate(ticketingModel.getAcceptanceDate()));
preparedStatement.setObject(10,DateUtil.getDate(ticketingModel.getDueDate()));
preparedStatement.setObject(11,ticketingModel.getBillAmount());
preparedStatement.setObject(12,ticketingModel.getPayeeAllName());
preparedStatement.setObject(13,ticketingModel.getPayeeOpenBank());
preparedStatement.setObject(14,ticketingModel.getAcceptorAllName());
preparedStatement.setObject(15,ticketingModel.getAcceptorOpenBank());
preparedStatement.setObject(16,ticketingModel.getIsTicketsReceived());
preparedStatement.setObject(17,DateUtil.getCurrentDateStr());
preparedStatement.setObject(18,DateUtil.getCurrentDateStr());
preparedStatement.addBatch();
}
//执行SQL语句
int[] ints = preparedStatement.executeBatch();
//清空SQL语句
preparedStatement.clearBatch();
if(EmptyUtil.isNotEmpty(ints)){
return ints.length;
}
return 0;
} catch (SQLException e) {
e.printStackTrace();
log.error("背书票据信息保存失败，sql错误：{}",e.getMessage());
throw e;
} catch (Exception e) {
e.printStackTrace();
log.error("背书票据信息保存失败，错误信息：{}",e.getMessage());
throw e;
} finally {
JConnection.closeConnection(connection);
}
}
}