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

1.引入依赖

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

2.定义实体（实体value名字和Excel标题名必须一致）

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

3.定义监听类

@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("出票票据解析数据写入数据库异常");
}
}
}

4.定义抽象解析类

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

5.抽象解析实现类

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

6监听操作服务类

@Service
public class TicketingService {
@Autowired
private TicketingDao ticketingDao;
public void saveTicketing(List<TicketingModel> ticketingModels) throws Exception {
ticketingDao.saveTicketing(ticketingModels);
}
}

7.dao存储数据到数据库

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

最后

以上就是忧郁火龙果最近收集整理的关于以文件流的方式下载网络上的Excel文件并利用alibaba.easyexcel解析的全部内容，更多相关以文件流内容请搜索靠谱客的其他文章。