引入高版本的jar包
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.4</version>
</dependency>
同时引入easyPoi和poi是因为我这边还需要处理动态导出excel功能,同时引入他们需要高版本,如果poi引入3.x版本,读取excel会出现错误
java.lang.NoClassDefFoundError: org/apache/poi/poifs/filesystem/FileMagic报错
poi3.7以后请使用ooxml-schemas-1.1.jar或以上的版本,否则报以下错误
POI异常:java.lang.NoClassDefFoundError: org.openxmlformats.schemas.spreadsheetml.x2
主要实现
public R<Object> excelSheetImport(MultipartFile multipartFile) {
List<SourceCategory> sourceCategories = sourceCategoryMapper.selectList(new LambdaQueryWrapper<>());
Map<String, String> sourceMap = sourceCategories.stream().collect(Collectors.toMap(SourceCategory::getName, SourceCategory::getCode));
try {
List<Object> objList = new ArrayList<>();
objList.add(new ImportIndex1DataDTO());
objList.add(new ImportIndex2DataDTO());
objList.add(new ImportIndex2DataDTO());
List<List<Object>> resultList = excelHandler.importExcels(multipartFile, 3, objList);
for (int i = 0; i < resultList.size(); i++) {
List<Object> objects = resultList.get(i);
if (i == 0) {
List<ImportIndex1DataDTO> voList = JSON.parseArray(JSON.toJSONString(objects), ImportIndex1DataDTO.class);
for (ImportIndex1DataDTO importIndex1DataDTO : voList) {
List<ImportIndex1DataDTO.FactorContentDTO> factorContentDTOS = new ArrayList<>();
factorContentDTOS.add(ImportIndex1DataDTO.FactorContentDTO
.builder().code(importIndex1DataDTO.getCode1())
.name(importIndex1DataDTO.getName1())
.value(importIndex1DataDTO.getValue1())
.unit(importIndex1DataDTO.getUnit1())
.build());
factorContentDTOS.add(ImportIndex1DataDTO.FactorContentDTO
.builder().code(importIndex1DataDTO.getCode2())
.name(importIndex1DataDTO.getName2())
.value(importIndex1DataDTO.getValue2())
.unit(importIndex1DataDTO.getUnit2())
.build());
factorContentDTOS.add(ImportIndex1DataDTO.FactorContentDTO
.builder().code(importIndex1DataDTO.getCode3())
.name(importIndex1DataDTO.getName3())
.value(importIndex1DataDTO.getValue3())
.unit(importIndex1DataDTO.getUnit3())
.build());
EmissionFactor emissionFactor = new EmissionFactor();
emissionFactor.setFuelType(importIndex1DataDTO.getFuelType());
emissionFactor.setAlias(importIndex1DataDTO.getAlias());
emissionFactor.setMeasUnit(importIndex1DataDTO.getMeasUnit());
emissionFactor.setEnergyForm(importIndex1DataDTO.getEnergyForm());
emissionFactor.setEnergyClass(importIndex1DataDTO.getEnergyClass());
emissionFactor.setEnergyType(importIndex1DataDTO.getEnergyType());
emissionFactor.setEnergyDesc(importIndex1DataDTO.getEnergyDesc());
emissionFactor.setDataContent(JSONObject.toJSONString(factorContentDTOS));
emissionFactor.setDataSources(importIndex1DataDTO.getDataSource());
emissionFactor.setSourceCategoryCode(sourceMap.get(importIndex1DataDTO.getSourceCategoryName()));
emissionFactor.setGasType(importIndex1DataDTO.getGasType());
emissionFactor.setCreateTime(LocalDateTime.now());
emissionFactor.setIsDeleted(0);
EmissionFactor factor = emissionFactorMapper.selectOne(new LambdaQueryWrapper<>(EmissionFactor.class)
.eq(EmissionFactor::getFuelType, emissionFactor.getFuelType())
.eq(EmissionFactor::getGasType, emissionFactor.getGasType())
.eq(EmissionFactor::getSourceCategoryCode, emissionFactor.getSourceCategoryCode())
);
if (Func.isEmpty(factor)) {
emissionFactorMapper.insert(emissionFactor);
} else {
emissionFactor.setId(factor.getId());
emissionFactorMapper.updateById(emissionFactor);
}
}
} else {
List<ImportIndex2DataDTO> voList = JSON.parseArray(JSON.toJSONString(objects), ImportIndex2DataDTO.class);
for (ImportIndex2DataDTO importIndex2DataDTO : voList) {
List<ImportIndex1DataDTO.FactorContentDTO> factorContentDTOS = new ArrayList<>();
factorContentDTOS.add(ImportIndex1DataDTO.FactorContentDTO
.builder().code(importIndex2DataDTO.getCode())
.name(importIndex2DataDTO.getName())
.value(importIndex2DataDTO.getValue())
.unit(importIndex2DataDTO.getUnit())
.build());
EmissionFactor emissionFactor = new EmissionFactor();
emissionFactor.setFuelType(importIndex2DataDTO.getFuelType());
emissionFactor.setAlias(importIndex2DataDTO.getAlias());
emissionFactor.setMeasUnit(importIndex2DataDTO.getMeasUnit());
emissionFactor.setEnergyForm(importIndex2DataDTO.getEnergyForm());
emissionFactor.setEnergyClass(importIndex2DataDTO.getEnergyClass());
emissionFactor.setEnergyType(importIndex2DataDTO.getEnergyType());
emissionFactor.setEnergyDesc(importIndex2DataDTO.getEnergyDesc());
emissionFactor.setDataContent(JSONObject.toJSONString(factorContentDTOS));
emissionFactor.setDataSources(importIndex2DataDTO.getDataSource());
emissionFactor.setSourceCategoryCode(sourceMap.get(importIndex2DataDTO.getSourceCategoryName()));
emissionFactor.setGasType(importIndex2DataDTO.getGasType());
emissionFactor.setCreateTime(LocalDateTime.now());
emissionFactor.setIsDeleted(0);
EmissionFactor factor = emissionFactorMapper.selectOne(new LambdaQueryWrapper<>(EmissionFactor.class)
.eq(EmissionFactor::getFuelType, emissionFactor.getFuelType())
.eq(EmissionFactor::getGasType, emissionFactor.getGasType())
.eq(EmissionFactor::getSourceCategoryCode, emissionFactor.getSourceCategoryCode())
);
if (Func.isEmpty(factor)) {
emissionFactorMapper.insert(emissionFactor);
} else {
emissionFactor.setId(factor.getId());
emissionFactorMapper.updateById(emissionFactor);
}
}
}
}
return R.success("导入excel成功");
} catch (Exception e) {
log.error("导入失败", e);
return R.fail("导入excel失败");
}
}
相关util方法
public class ExcelHandler {
/**
* 读取多个sheet
*
* @param file:文件流
* @param index:需要读取的sheet个数 [默认0开始,如果传入3,则读取0 1 2]
* @param params:每个sheet里面需要封装的对象[如果index为3,则需要传入对应的3个对象]
* @param <T>
* @return
*/
public <T> List<List<T>> importExcels(MultipartFile file, int index, List<Object> params) throws Exception {
this.checkFile(file);
List<List<T>> resultList = new LinkedList<>();
for (int i = 0; i < index; i++) {
UploadDataListener<T> uploadDataListener = new UploadDataListener<>();
ExcelReaderBuilder builder = EasyExcelFactory.read(file.getInputStream(), params.get(i).getClass(), uploadDataListener);
// 从第5行开始读取数据
builder.sheet(i).headRowNumber(4).doRead();
List<T> list = uploadDataListener.getList();
resultList.add(list);
}
return resultList;
}
/**
* 文件格式校验
*
* @param file:
*/
private void checkFile(MultipartFile file) {
if (file == null) {
throw new RuntimeException("文件不能为空");
}
String fileName = file.getOriginalFilename();
if (StringUtils.isEmpty(fileName)) {
throw new RuntimeException("文件不能为空");
}
if (!fileName.endsWith(ExcelTemplateEnum.TEMPLATE_SUFFIX.getDesc())
&& !fileName.endsWith(ExcelTemplateEnum.TEMPLATE_SUFFIX_XLS.getDesc())) {
throw new RuntimeException("请上传.xlsx或.xls文件");
}
}
@Getter
static enum ExcelTemplateEnum {
/**
* 单sheet导出
*/
TEMPLATE_1("1", "complex"),
/**
* 模板格式
*/
TEMPLATE_SUFFIX("xlsx", ".xlsx"),
TEMPLATE_SUFFIX_XLS("xls", ".xls"),
TEMPLATE_SUFFIX_DOCX("docx", ".docx"),
/**
* 模板路径
*/
TEMPLATE_PATH("path", "excel"),
;
private final String code;
private final String desc;
ExcelTemplateEnum(String code, String desc) {
this.code = code;
this.desc = desc;
}
/**
* 通过code获取msg
*
* @param code 枚举值
* @return
*/
public static String getMsgByCode(String code) {
if (code == null) {
return null;
}
ExcelTemplateEnum enumList = getByCode(code);
if (enumList == null) {
return null;
}
return enumList.getDesc();
}
public static String getCode(ExcelTemplateEnum enumList) {
if (enumList == null) {
return null;
}
return enumList.getCode();
}
public static ExcelTemplateEnum getByCode(String code) {
for (ExcelTemplateEnum enumList : values()) {
if (enumList.getCode().equals(code)) {
return enumList;
}
}
return null;
}
}
}
public class UploadDataListener<T> extends AnalysisEventListener<T> {
/**数据集*/
private final List<T> list = new ArrayList<>();
public List<T> getList(){
return this.list;
}
/**
* 每条数据都会进入
* @param object:
* @param analysisContext:
*/
@Override
public void invoke(T object, AnalysisContext analysisContext) {
this.list.add(object);
}
/**
* 数据解析完调用
* @param analysisContext:
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
/**
* 异常时调用
* @param exception:
* @param context:
* @throws Exception
*/
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
// 数据解析异常
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
throw new RuntimeException("第" + excelDataConvertException.getRowIndex() + "行" + excelDataConvertException.getColumnIndex() + "列" + "数据解析异常");
}
}
}