easyexcel导入
一、依赖
<!--阿里巴巴EasyExcel依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
参考文档:easyexcel文档
二、web中导入
controller层
@GetMapping("/importModel")
public HttpResponseTemp<?> importModel(@RequestParam("file") MultipartFile file) throws IOException {
return assetModelService.importModel(file);
}
service实现类
@Override
public HttpResponseTemp<?> importModel(MultipartFile file) throws IOException {
Optional.ofNullable(file).orElseThrow(() -> ApiException.wrapMessage(ResultStat.PARAM_ERROR, "参数为空"));
EasyExcel.read(file.getInputStream(),
ImportAssetModel.class,
new ImportAssetModelListener(assetModelBiz,assetClassificationBiz,assetManufacturerBiz))
// 若全局使用自定义转换可以用registerConverter,所有java为string,excel为string的都会用这个转换器。
// 如果就想单个字段使用请使用@ExcelProperty 指定converter
// .registerConverter(new CustomStringStringConverter())
.sheet("设备")
//如果多行头,可以设置其他值。默认第一行为行头。
.headRowNumber(2)
.doRead();
return ResultStat.OK.wrap("", "导入型号库成功");
}
实体类
这里通过@ExcelProperty的参数index来跟excel中的数据进行映射
package org.mye.framework.api.dto.dcim.assetmodel;
import com.alibaba.excel.annotation.ExcelProperty;
public class ImportAssetModel {
@ExcelProperty(index = 0)
private String manufacturer;
@ExcelProperty(index = 1)
private String model;
@ExcelProperty(index = 2)
private String deviceTypeStr;
@ExcelProperty(index = 3)
private String status;
@ExcelProperty(index = 4)
private String remark;
@ExcelProperty(index = 5)
private String height;
@ExcelProperty(index = 6)
private String weight;
@ExcelProperty(index = 7)
private String ratedPower;
@ExcelProperty(index = 8)
private String distributionPort;
@ExcelProperty(index = 9)
private String networkPort;
}
监听器
/**
* 监听器
* 有个很重要的点 ImportAssetModelListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
*/
public class ImportAssetModelListener extends AnalysisEventListener<ImportAssetModel> {
private static final Logger LOGGER = LoggerFactory.getLogger(ImportAssetModelListener.class);
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 10;
List<ImportAssetModel> list = new ArrayList<>();
private final AssetModelBiz assetModelBiz;
private final AssetClassificationBiz assetClassificationBiz;
private final AssetManufacturerBiz assetManufacturerBiz;
public ImportAssetModelListener(AssetModelBiz assetModelBiz, AssetClassificationBiz assetClassificationBiz, AssetManufacturerBiz assetManufacturerBiz) {
this.assetModelBiz = assetModelBiz;
this.assetClassificationBiz = assetClassificationBiz;
this.assetManufacturerBiz = assetManufacturerBiz;
}
/**
* 读取时,每条数据都会从这里解析
*/
@Override
public void invoke(ImportAssetModel data, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", JSONUtil.toJsonStr(data));
list.add(data);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (list.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
list.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
LOGGER.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
if (CollectionUtil.isNotEmpty(list)) {
LOGGER.info("{}条数据,开始存储数据库!", list.size());
List<AssetModel> assetModelList = new ArrayList<>();
for (ImportAssetModel importAssetModel : list) {
String error = checkLegality(importAssetModel);
if (StringUtils.isNotBlank(error)) {
throw ApiException.wrapMessage(ResultStat.PARAM_ERROR, "型号为" + importAssetModel.getModel() + "的这一列出现错误:" + error);
}
AssetManufacturer assetManufacturer = checkManufacturer(importAssetModel.getManufacturer());
AssetClassification assetClassification = checkClassification(importAssetModel.getDeviceTypeStr());
checkModel(assetClassification.getId(), assetManufacturer.getId(), importAssetModel.getModel());
AssetModel assetModel = convertOfImportAssetModel(importAssetModel, assetClassification, assetManufacturer);
assetModelList.add(assetModel);
}
assetModelList = assetModelList.stream().collect(Collectors.collectingAndThen(
Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing
(o -> o.getAssetClassificationId() + ";" + o.getAssetManufacturerId() + ";" + o.getModel()))), ArrayList::new));
assetModelBiz.insertByList(assetModelList);
//这里真实的保存数据。
LOGGER.info("存储数据库成功!");
}
}
private AssetModel convertOfImportAssetModel(ImportAssetModel importAssetModel, AssetClassification assetClassification, AssetManufacturer assetManufacturer) {
AssetModel assetModel = new AssetModel();
assetModel.setAssetClassificationId(assetClassification.getId());
assetModel.setAssetManufacturerId(assetManufacturer.getId());
if (StringUtils.isNotBlank(importAssetModel.getModel())){
assetModel.setModel(importAssetModel.getModel());
}
if (StringUtils.isNotBlank(importAssetModel.getRemark())){
assetModel.setRemark(importAssetModel.getRemark());
}
if (StringUtils.isNotBlank(importAssetModel.getStatus()) && importAssetModel.getStatus().equals("使用中")) {
assetModel.setStatus(0);
} else if (StringUtils.isNotBlank(importAssetModel.getStatus()) && importAssetModel.getStatus().equals("未使用")) {
assetModel.setStatus(1);
}else if (StringUtils.isBlank(importAssetModel.getStatus())){
assetModel.setStatus(1);
}
if (StringUtils.isNotBlank(importAssetModel.getHeight())){
assetModel.setHeight(Convert.toInt(importAssetModel.getHeight()));
}
if (StringUtils.isNotBlank(importAssetModel.getWeight())){
assetModel.setWeight(Convert.toDouble(importAssetModel.getHeight()));
}
if (StringUtils.isNotBlank(importAssetModel.getRatedPower())){
assetModel.setRatedPower(Convert.toDouble(importAssetModel.getRatedPower()));
}
if (StringUtils.isNotBlank(importAssetModel.getDistributionPort())){
assetModel.setDistributionPort(Convert.toInt(importAssetModel.getDistributionPort()));
}
if (StringUtils.isNotBlank(importAssetModel.getNetworkPort())){
assetModel.setNetworkPort(Convert.toInt(importAssetModel.getNetworkPort()));
}
assetModel.setCreateTime(System.currentTimeMillis());
assetModel.setRemoved(0);
return assetModel;
}
private void checkModel(Integer classificationId, Integer manufacturerId, String model) {
AssetModel assetModel = assetModelBiz.selectByClassificationIdAndManufacturerIdAndModel(classificationId, manufacturerId, model);
if (ObjectUtil.isNotNull(assetModel)) {
throw ApiException.wrapMessage(ResultStat.PARAM_ERROR, "型号" + model + "已存在");
}
}
private AssetClassification checkClassification(String deviceTypeStr) {
AssetClassification assetClassification = assetClassificationBiz.selectByName(deviceTypeStr);
Optional.ofNullable(assetClassification).orElseThrow(() -> ApiException.wrapMessage(ResultStat.PARAM_ERROR, "设备类型" + deviceTypeStr + "不存在"));
return assetClassification;
}
private AssetManufacturer checkManufacturer(String manufacturer) {
AssetManufacturer assetManufacturer = assetManufacturerBiz.selectByName(manufacturer);
Optional.ofNullable(assetManufacturer).orElseThrow(() -> ApiException.wrapMessage(ResultStat.PARAM_ERROR, "制造商" + manufacturer + "不存在"));
return assetManufacturer;
}
public String checkLegality(ImportAssetModel assetModel) {
String error = null;
if (ObjectUtil.isNull(assetModel.getManufacturer())) {
error = "制造商不能为空";
} else if (!StringUtil.checkManufacturerName(assetModel.getManufacturer())) {
error = "制造商名称不合法";
} else if (ObjectUtil.isNull(assetModel.getModel())) {
error = "型号不能为空";
} else if (!StringUtil.checkClassificationName(assetModel.getModel())) {
error = "型号名称不合法";
} else if (!StringUtil.checkDeviceTypeName(assetModel.getDeviceTypeStr())) {
error = "设备类型名称不合法";
} else if (ObjectUtil.isNotNull(assetModel.getStatus()) && (!assetModel.getStatus().equals("使用中") && !assetModel.getStatus().equals("未使用"))) {
error = "状态不合法";
} else if (ObjectUtil.isNotNull(assetModel.getRemark()) && !StringUtil.checkDeviceTypeName(assetModel.getRemark())) {
error = "备注不合法";
} else if (ObjectUtil.isNotNull(assetModel.getWeight()) && !StringUtil.checkWeight(assetModel.getWeight())) {
error = "重量不合法";
} else if (ObjectUtil.isNotNull(assetModel.getDistributionPort())) {
if (!NumberUtil.isInteger(assetModel.getDistributionPort())) {
error = "配电端口数不是整数不合法";
}
Integer integer = Convert.toInt(assetModel.getDistributionPort());
if (integer < 0 || integer > 2048) {
error = "配电端口数不合法,该数字为" + integer;
}
} else if (ObjectUtil.isNotNull(assetModel.getNetworkPort())) {
if (!NumberUtil.isInteger(assetModel.getDistributionPort())) {
error = "网络端口数不是整数不合法";
}
Integer integer = Convert.toInt(assetModel.getDistributionPort());
if (integer < 0 || integer > 2048) {
error = "网络端口数不合法,该数字为" + integer;
}
} else if (assetModel.getDeviceTypeStr().equals("IT设备") || assetModel.getDeviceTypeStr().equals("通信设备")) {
if (ObjectUtil.isNull(assetModel.getHeight())) {
error = "高度为空";
}
if (ObjectUtil.isNotNull(assetModel.getHeight()) && !StringUtil.checkModelHeight(assetModel.getHeight())) {
error = "高度不合法";
}
}else if (ObjectUtil.isNotNull(assetModel.getHeight()) && !StringUtil.checkModelHeight(assetModel.getHeight())){
error = "高度不合法";
}else if (ObjectUtil.isNotNull(assetModel.getRatedPower()) && !StringUtil.checkContractPower(assetModel.getRatedPower())){
error = "额定功率不合法";
}
return error;
}
/**
* 读取表头数据
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
LOGGER.info("解析到一条头数据:{}", JSONUtil.toJsonStr(headMap));
}
/**
* 读取额外信息
*
* @param extra
* @param context
*/
@Override
public void extra(CellExtra extra, AnalysisContext context) {
LOGGER.info("读取到了一条额外信息:{}", JSONUtil.toJsonStr(extra));
switch (extra.getType()) {
case COMMENT:
LOGGER.info("额外信息是批注,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(), extra.getColumnIndex(),
extra.getText());
break;
case HYPERLINK:
if ("Sheet1!A1".equals(extra.getText())) {
LOGGER.info("额外信息是超链接,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(),
extra.getColumnIndex(), extra.getText());
} else if ("Sheet2!A1".equals(extra.getText())) {
LOGGER.info(
"额外信息是超链接,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{},"
+ "内容是:{}",
extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
extra.getLastColumnIndex(), extra.getText());
} else {
LOGGER.info("Unknown hyperlink!");
}
break;
case MERGE:
LOGGER.info(
"额外信息是超链接,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{}",
extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
extra.getLastColumnIndex());
break;
default:
}
}
/**
* 用日期去接字符串 肯定报错,此时需要用到异常处理
* 在转换异常获取其他异常下会调用本接口。
* 抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
*/
@Override
public void onException(Exception exception, AnalysisContext context) {
LOGGER.error("解析失败,但是继续解析下一行:{}", exception.getMessage());
// 如果是某一个单元格的转换异常 能获取到具体行号
// 如果要获取头的信息 配合invokeHeadMap使用
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
LOGGER.error("第{}行,第{}列解析异常", excelDataConvertException.getRowIndex(),
excelDataConvertException.getColumnIndex());
}
}
}