java实现利用阿里巴巴开源的easyexcel进行对excel表格的导入和导出[附完整代码]
目录
前言
平常的功能大家应该都会用到导入导出excel的功能，比如通过读excel的方式将excel的数据导入到数据库中。当然实现的方式有很多，今天我介绍的是利用阿里开源的easyexcel项目来完成功能。大家也可以自己看easyexcel的文档进行开发。当然这里因为刚好工作的时候用到了，所以将自己写的demo分享出来，大家就可以更快节省时间完成功能，大家可以参考，也可以直接拿来用，实际在你们自己的开发过程当中适当修改。
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。
github地址:https://github.com/alibaba/easyexcel
一、引入easyexcel的maven
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>首先springBoot项目结构大家看一下,除了最简单的三层架构，dao、service和controller，这里还需要用到listener
二、读取excel代码示例
1、bean需要和excel的列对应
可以通过index和name将bean和excel的列对应起来，但是官方不建议index和name同时夹杂在一起用，顾名思义，在一个bean中要么就是用index，要么就是用name对应
eg:@ExcelProperty(index = 2)
eg:@ExcelProperty("日期标题")
demo
package com.xxx.xxxx.xxxx;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
/**
* Created by yjl on 2019/12/25.
*/
@Data
public class RoomMonitorCoverDataBean {
private String TIME_ID; //时间
@ExcelProperty(index = 0)
private String PROV_NAME; //省
@ExcelProperty(index = 1)
private String AREA_NAME; //地市
@DateTimeFormat("yyyy年MM月")
@ExcelProperty(index = 2)
private String MONTH_DESC; //月份，DateTimeFormat表示对日期进行格式化，不要的可以去掉
public String getTIME_ID() {
return TIME_ID;
}
public void setTIME_ID(String TIME_ID) {
this.TIME_ID = TIME_ID;
}
public String getPROV_NAME() {
return PROV_NAME;
}
public void setPROV_NAME(String PROV_NAME) {
this.PROV_NAME = PROV_NAME;
}
public String getAREA_NAME() {
return AREA_NAME;
}
public void setAREA_NAME(String AREA_NAME) {
this.AREA_NAME = AREA_NAME;
}
public String getMONTH_DESC() {
return MONTH_DESC;
}
public void setMONTH_DESC(String MONTH_DESC) {
this.MONTH_DESC = MONTH_DESC;
}
}
2、Controller层
很简单，就注入service，引用service的方法，我们在service里进行入参的判断或者其他业务处理等，这里我就直接放一个impl的了，interface是一样的名字，放一个没有实现的方法就可以了，当然你也可以不要那个interface
demo
@RequestMapping("/mrePortController")
public class MrePortControllerCSVImpl implements IMrePortControllerCSV {
@Autowired
private MrePortServiceCSV mrePortServiceCSV;
@RequestMapping(value = "/saveRoomMonitorCoverData", method = RequestMethod.POST)
@Override
public JSONObject saveRoomMonitorCoverData(MultipartFile file, String timeType, String userArea) {
return mrePortServiceCSV.saveRoomMonitorCoverData(file, timeType,userArea);
}
}3、service层
这里我们进行逻辑判断，可以对入参或者其他一些东西进行判断，
主要功能是
// 将excel表中的数据入库
// 有个很重要的点 xxxxxListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
// 这里 需要指定读用哪个class去读，默认读取第一个sheet 文件流会自动关闭
EasyExcel.read(file.getInputStream(), RoomMonitorCoverDataBean.class, new RoomMonitorCoverDataListener(mrePortDao,tableName,timeType)).sheet().doRead();
demo
@Component
public class MrePortServiceCSVImpl implements MrePortServiceCSV {
@Autowired
private MrePortDao mrePortDao;
private Logger logger = LoggerFactory.getLogger(this.getClass());
@Override
public JSONObject saveRoomMonitorCoverData(MultipartFile file, String timeType,String userArea) {
JSONObject jo = new JSONObject();
try {
//可以进行必传参数校验等
//这里省略不写了
//导入示例：excel名称：小小鱼儿小小林_浙江201912.xlsx
String fileName = file.getOriginalFilename().replaceAll("\\.xlsx|\\.xls","");
System.out.println("file.getName():"+file.getName()+",file.getOriginalFilename():"+file.getOriginalFilename());
String[] split = fileName.split("_");
String file_tableName = split[0]; //文件名称表名称
String file_areaNameAndDate = split[1]; //文件名称地市和月份 eg:浙江201912
//String file_version = split[2]; //文件名称版本号
if ("小小鱼儿小小林".equals(file_tableName)){ //只能上传该名称的文件
String file_areaName = file_areaNameAndDate.substring(0, 2); //文件名称地市
String file_date = file_areaNameAndDate.substring(2); //文件名称月份
if (userArea.equals(file_areaName)){ //只能上传自己权限范围内的地市数据
//Long currentDate = Long.parseLong(getCurrentDate("yyyyMM"));
String currentDate = getCurrentDate("yyyyMM");
if (currentDate.equals(file_date)){ //只能上传当月的数据
String tableName = "xxxxxxxxx";
// 将excel表中的数据入库
// 有个很重要的点 xxxxxListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
// 这里 需要指定读用哪个class去读，默认读取第一个sheet 文件流会自动关闭
EasyExcel.read(file.getInputStream(), RoomMonitorCoverDataBean.class, new RoomMonitorCoverDataListener(mrePortDao,tableName,timeType)).sheet().doRead();
jo.put("msg","导入成功");
jo.put("resultCode",0);
jo.put("response","success");
}else {
jo.put("msg","您只能上传当月"+currentDate+"的数据");
jo.put("resultCode",-1);
jo.put("response","");
return jo;
}
}else {
jo.put("msg","您暂时没有权限上传"+file_areaName+"的数据");
jo.put("resultCode",-1);
jo.put("response","");
return jo;
}
}else {
jo.put("msg","请选择《小小鱼儿小小林》文件再上传");
jo.put("resultCode",-1);
jo.put("response","");
return jo;
}
}catch (IOException i){
i.printStackTrace();
jo.put("msg","IOException，请重试");
jo.put("resultCode",-1);
jo.put("response","");
} catch (Exception e){
e.printStackTrace();
jo.put("msg","导入异常，请重试");
jo.put("resultCode",-1);
jo.put("response","");
}
return jo;
}
}4、listener层
注意这个不是mvc的三层架构，这里加listener是因为easyexcel需要用到，实际上解析excel的过程就是在这里实行的，如果要对解析出来的每一行数据或者其中的一列进行另外的判断或者做其他处理，是要到这个类里面写逻辑
RoomMonitorCoverDataListener.java
demo
/**
* Created by yjl on 2019/12/27.公众号：zygxsq
*/
public class RoomMonitorCoverDataListener extends AnalysisEventListener<RoomMonitorCoverDataBean> {
private Logger LOGGER = LoggerFactory.getLogger(this.getClass());
private static final int BATCH_COUNT = 1000; //每隔1000条存数据库，然后清理list ，方便内存回收
List<RoomMonitorCoverDataBean> list = new ArrayList<RoomMonitorCoverDataBean>();
MrePortDao mrePortDao;
String timeType;
String tableName;
/**
* 如果使用了spring,请使用有参构造方法。每次创建Listener的时候需要把spring管理的类传进来
*/
public RoomMonitorCoverDataListener(){}
public RoomMonitorCoverDataListener(MrePortDao mrePortDao) {
this.mrePortDao = mrePortDao;
}
public RoomMonitorCoverDataListener(MrePortDao mrePortDao,String timeType) {
this.mrePortDao = mrePortDao;
this.timeType = timeType;
}
public RoomMonitorCoverDataListener(MrePortDao mrePortDao,String tableName,String timeType) {
this.mrePortDao = mrePortDao;
this.tableName = tableName;
this.timeType = timeType;
}
/**
* 这个每一条数据解析都会来调用
* @param data
* @param analysisContext
*/
@Override
public void invoke(RoomMonitorCoverDataBean data, AnalysisContext analysisContext) {
LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
//------------------begin------------------------
// 这里对excel表里的数据进行逻辑处理、筛选等，如果不需要对表里的数据进行处理，这里可以删除
String area_name = data.getAREA_NAME();
String month_desc = data.getMONTH_DESC();
//先删除表里已经存在的，再保存新的
mrePortDao.deleteTableData(tableName,timeType," AREA_NAME='"+area_name+"' AND MONTH_DESC='"+month_desc+"' ");
//-------------------end-----------------------
list.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
* @param analysisContext
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据，开始存储数据库！", list.size());
mrePortDao.saveRoomMonitorCoverData(list,timeType);
LOGGER.info("存储数据库成功！");
}
}
5、dao层
因为我们这里还是用的springJPA，所以大家可以看到上面我处理excel中的数据，要对原来数据库中的数据进行删除的时候，还是用拼接的那种格式，这里大家要结合自己的框架进行修改，相信作为技术人，你们改改还是可以的，如果有遇到不会改的，可以关注我的公众号：zygxsq，公众号里有我的微信方式，可以联系我
demo
/**
* Created by yjl on 2019/12/20.
*/
@Component
public class MrePortDaoImpl implements MrePortDao {
@PersistenceContext
private EntityManager em;
@Autowired
protected JdbcTemplate jdbcTemplate;
private Logger logger = LoggerFactory.getLogger(this.getClass());
@Transactional
@Override
public Integer saveRoomMonitorCoverData(List<RoomMonitorCoverDataBean> params, String timeType) {
String tableName = "xxxxxxx";
StringBuilder sql = new StringBuilder();
sql.append("INSERT INTO ")
.append(tableName)
.append("(")
.append("TIME_ID,") //时间
.append("PROV_NAME,") //省
.append("AREA_NAME,") //地市
.append("MONTH_DESC,") //月份
.append(")VALUES(")
.append("?,?,?,?")
.append(")")
;
int[] len = jdbcTemplate.batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
RoomMonitorCoverDataBean pojo = params.get(i);
String month_desc = pojo.getMONTH_DESC();
String time_id = month_desc.replaceAll("\\D","")+"000000";
ps.setString(1,time_id); //时间
ps.setString(2,pojo.getPROV_NAME()); //省
ps.setString(3,pojo.getAREA_NAME()); //地市
ps.setString(4,month_desc); //月份
}
@Override
public int getBatchSize() {
return params.size();
}
});
return len.length;
}
@Transactional
@Modifying
@Query
@Override
public Integer deleteTableData(String tableName, String timeType, String whereInfo) {
String sql="DELETE FROM "+tableName+" WHERE 1=1 AND "+whereInfo;
logger.info("删除表"+tableName+"里"+whereInfo+"的数据sql："+sql);
int deleteCnt = em.createNativeQuery(sql).executeUpdate();
logger.info("成功删除"+tableName+"表中"+whereInfo+"的"+deleteCnt+"条数据");
return deleteCnt;
}
}
二、导出excel代码示例
待更新
