EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。 他能让你在不用考虑性能、内存的等因素的情况下,快速完成Excel的读、写等功能。
他能让你在不用考虑性能、内存的等因素的情况下,快速完成Excel的读、写等功能。)
官网地址
https://easyexcel.opensource.alibaba.com/
maven
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.6</version>
</dependency>
EasyExcel导入方法
try {
File filePath = new File(savePath + File.separator + name);
//filePath:服务器上获取的流转换的,也可以直接使用传输MultipartFile file,
// controller传的MultipartFile file,就这么搞InputStream inputStream = multipartFile.getInputStream();
InputStream inputStream = new BufferedInputStream(new FileInputStream(filePath));
Throwable var22 = null;
try {
// 调用监听器 通过.headRowNumber(0)从表头开始读取TempRateListValueUploadListener监听器方法(里面可以写构造方法接收传给监听器的值,什么map
list 什么的,实体类TempRateValueUploadModel,实体类里的字段注解@ExcelProperty
// 如果监听器里需要用到dao,mapper,service都需要放到构造方法里才可以使用
EasyExcel.read(inputStream, TempRateValueUploadModel.class,
new TempRateListValueUploadListener(newParameterMap,
efTransportChargeItemDao))
.sheet().doRead();
map.put("code", "200");
map.put("msg", "导入成功");
/*json.put("body", map);*/
} catch (Throwable var19) {
var22 = var19;
throw var19;
} finally {
if (inputStream != null) {
if (var22 != null) {
try {
inputStream.close();
} catch (Throwable var18) {
var22.addSuppressed(var18);
}
} else {
inputStream.close();
}
}
}
} catch (Throwable var21) {
// 监听器里返回的错误信息
if (!(var21 instanceof EasyExcelException)) {
log.error("parse [" + name + "] excel failed...chargeItemNo[" + chargeItemNo + "]", var21);
}
if (var21 instanceof NumberFormatException) {
String prefix = "For input string: ";
if (var21.getMessage().indexOf(prefix) == -1) {
map.put("code", "101");
map.put("msg", "类型转换失败");
} else {
map.put("code", "1");
map.put("msg", var21.getMessage());
}
} else {
map.put("code", "1");
map.put("msg", var21.getMessage());
}
} ```
### 监听器
```java package com.maintain.data.excel.listener;
import com.alibaba.excel.context.AnalysisContext; import
com.alibaba.excel.event.AnalysisEventListener; import
com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject; import
com.fasterxml.jackson.databind.ObjectMapper; import
com.maintain.data.excel.bean.*; import
com.maintain.data.excel.dao.EfTransportChargeItemDao; import
com.maintain.data.excel.vo.OperatorInfoVo; import
com.maintain.data.excel.vo.TempRateValueUploadModel; import
com.maintain.data.excel.vo.TempRateValueVo; import
com.sinoservices.core.easyexcel.exception.EasyExcelException; import
com.sinoservices.core.util.EmptyUtils; import
com.sinoservices.core.util.Validator; import
org.apache.commons.lang3.StringUtils; import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFDateUtil; import
org.springframework.beans.BeanUtils;
import java.beans.PropertyDescriptor; import java.lang.reflect.Field;
import java.lang.reflect.Method; import java.text.ParseException;
import java.text.SimpleDateFormat; import java.util.*; import
java.util.concurrent.ConcurrentHashMap;
public class TempRateListValueUploadListener extends
AnalysisEventListener<TempRateValueUploadModel> {
private static Logger log = Logger.getLogger(TempRateListValueUploadListener.class);
private List<TempRateValueUploadModel> templateListValueModel = new ArrayList<>();
private Map<String, ResultCodeMasterBean> codeMasterCache = new ConcurrentHashMap<>();
private JSON json = new JSONArray();
private Map<String, String> map = new HashMap<>();
private Map<String, String> maps = new HashMap<>();
private EfTransportChargeItemDao efTransportChargeItemDao;
/**
* 调用dao 或者service需要手动传
*
*
* @param map
* @param
* @param efTransportChargeItemDao
*/
public TempRateListValueUploadListener(Map<String, String> map, EfTransportChargeItemDao efTransportChargeItemDao) {
this.map = map;
this.efTransportChargeItemDao = efTransportChargeItemDao;
}
/**
* 文件导入唯一标识
*/
private String fileUUID = null; // 此方法每一条数据解析都会来调用
@Override
public void invoke(TempRateValueUploadModel object, AnalysisContext context) {
Integer currentRowNum = context.getCurrentRowNum();
object.setChargeItemNo(map.get("chargeItemNo"));
object.setRequestId(map.get("requestId"));
object.setUserId(map.get("userId"));
object.setApproverId(map.get("approverId"));
object.setLastname(map.get("lastname"));
object.setCreateDate(new Date().toString());
object.setStatus("0");
if (currentRowNum == 1) {
Date date = new Date(System.currentTimeMillis());
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyMMddHHmmss");
String timeString = simpleDateFormat.format(date);
UUID uuid = UUID.randomUUID();
String uuidStr = uuid.toString().replace("-", "").toUpperCase().substring(0, 7);
fileUUID = timeString + "-" + map.get("chargeItemNo") + "-" + uuidStr;
}
//保存UUID
object.setRemark(fileUUID);
String message = Validator.validate(object);
if (message != null) {
maps.put("code", "101");
maps.put("msg", "第 " + context.getCurrentRowNum() + " 行: " + message);
} else {
templateListValueModel.add(object);
}
} // 所有数据解析完成都会来调用
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
if (EmptyUtils.isNotEmpty(templateListValueModel)) {
if (templateListValueModel.size() > 3000) {
maps.put("code","101");
maps.put("msg","Excel 导入不能超过3000条");
throw new EasyExcelException(maps.get("msg"));
}
// 实现导入逻辑方法
maps = save(templateListValueModel);
}
templateListValueModel.clear();
if (!maps.isEmpty() && !"200".equals(maps.get("code"))) {
throw new EasyExcelException(maps.get("msg"));
}
}
/**
* 将数据保存到中间表中
*
* @param customersdModel
* @return
*/
private Map<String, String> save(List<TempRateValueUploadModel> customersdModel) {
// 写插入自己表的逻辑方法
}
// 错误信息
private void insertErrormsg(EfTransportChargeItemDao efTransportChargeItemDao, List<TempRateValueUploadModel>
customersdModel) {
efTransportChargeItemDao.insertErrormsg(customersdModel);
}
private Map<String, String> getColumnValue(List<EfCrTemplateListColBean> colBeans,
List<EfCrTemplateListValueModelBean> beans) {
Map<String, String> map = new HashMap<>();
String startDate = "";
String endDate = "";
for (EfCrTemplateListColBean colBean : colBeans) {
String colName = colBean.getMclcColumnName();
for (EfCrTemplateListValueModelBean colValBean : beans) {
if (colName.equals(colValBean.getColumnName())) {
if (colName.contains("从")) {
startDate = colValBean.getColumnValue();
} else if (colName.contains("到")) {
endDate = colValBean.getColumnValue();
}
}
}
}
map.put("startDate", startDate);
map.put("endDate", endDate);
return map;
}
private void changeTime(List<EfCrTemplateListValueModelBean> beans, String changeDate, boolean flag) {
for (EfCrTemplateListValueModelBean colValBean : beans) {
if (flag && colValBean.getColumnName().contains("从")) {
colValBean.setColumnValue(changeDate);
} else if (!flag && colValBean.getColumnName().contains("到")) {
colValBean.setColumnValue(changeDate);
}
}
}
/**
* Description :判断是不是数字
*
* @param :str 1
* @return: boolean
* @Author: Neil
* @Create Date: 2019-4-11
*/
public boolean isNumber(String numberValue) {
boolean convertSuccess = true;
try {
Double.parseDouble(numberValue);
} catch (Exception e) {
convertSuccess = false;
}
return convertSuccess;
}
/**
* Description :通过反射获取字段值
*
* @param :fieldName 1
* @param :object 2
* @return: java.lang.String
* @Author: Neil
* @Create Date: 2019-4-10
*/
private String getFieldValueByFieldName(String fieldName, Object object) {
try {
PropertyDescriptor propertyDescriptor = new PropertyDescriptor(fieldName, object.getClass());
Method getMethod = propertyDescriptor.getReadMethod();//获得get方法
Object value = getMethod.invoke(object);//执行get方法返回一个Object
System.out.print(fieldName + "---" + value.toString());
return value.toString();
} catch (Exception e) {
log.error(e.getMessage(), e);
}
return null;
}
/**
* Description :通过反射获取列个数
*
* @param :object 1
* @return: int
* @Author: Neil
* @Create Date: 2019-4-10
*/
private int getFieldNumValueByClazz(Object object) {
try {
Class clazz = object.getClass();
Field[] fields = object.getClass().getDeclaredFields();//获得属性
int notNullNum = 0;
for (Field field : fields) {
PropertyDescriptor propertyDescriptor = new PropertyDescriptor(field.getName(), clazz);
Method getMethod = propertyDescriptor.getReadMethod();//获得get方法
Object value = getMethod.invoke(object);//执行get方法返回一个Object
if (value != null && !value.equals("")) {
notNullNum++;
}
} // log.info(notNullNum);
//bean对象中字段pmCode没值,chargeItemNo 必有值。
// 而且要忽略,所以从原来所以需要-3 改为-2
return notNullNum;
} catch (Exception e) {
log.error(e.getMessage(), e);
}
return 0;
} }
EasyExcel导出方法
File file = new File(fileName);
// 第一种方法
EasyExcel.write(file.getAbsolutePath(), TempRateValueExcel.class).sheet("excel").doWrite(modelList);
// 上传华为云/服务器
url = oaRequestUtil.getUploadFile(file);
log.info("生成excel上传华为云,返回Url:{}", url);
try {
// 第二种方法
WriteSheet sheet0 = EasyExcel.writerSheet(0, "导入费率错误信息").head(TempRateValueExcel.class).build();
excelWriter = EasyExcel.write(file).build();
excelWriter
.write(modelList, sheet0);
} catch (Exception e) {
// 错误信息 e.getMsg
e.getMessage();
e.printStackTrace();
} finally {
if (excelWriter != null) {
// 关闭流
excelWriter.finish();
}
}