easyExcel实现excel文件复杂导入（数据验证）与导出（数据有效性校验）操作
最近在做一个项目，用到了excel文件的复杂导入与导出，刚开始确实挺头疼的尤其是数据有效性校验。网上基本找不到方法，后面在easyExcel官网的开发文档中，看到了类似操作，但个人感觉官网讲的不太详细，各位可以看看 easyExcel官网开发文档
https://easyexcel.opensource.alibaba.com/docs/current/quickstart/read
今天闲下来，就写此记录一下。。
数据校验导入实际测试
将excel文档导入，后台将获取数据同时进行数据校验，最终无误后，存入数据库
导入的模板：
校验不通过：
校验通过：
excel文件复杂导入（数据验证）代码实现
引入依赖：
<!--easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.3</version>
<exclusions>
<exclusion>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
</exclusion>
</exclusions>
</dependency>
实体类：
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import lombok.Data;
@Data
@HeadRowHeight(25)//设置行高
public class ConvertstandardExcelDto {
@ExcelProperty(value = "序号", index = 0)
private Integer id;
@ColumnWidth(15)//列宽
@ExcelProperty(value = "产品分类", index = 1)
private String productsort;
@ColumnWidth(40)
@ExcelProperty(value = "产品名称", index = 2)
private String productname;
@ColumnWidth(15)
@ExcelProperty(value = "长短险标志", index = 3)
private String longriskflag;
@ColumnWidth(15)
@ExcelProperty(value = "交费年期", index = 4)
private String payyearterm;
@ExcelProperty(value = "渠道", index = 5)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, dataFormat = 49)
private String channel;
@ColumnWidth(15)
@ExcelProperty(value = "机构", index = 6)
private String managecom;
@ColumnWidth(15)
@ExcelProperty(value = "折算系数", index = 7)
private String convertstandard;
@ColumnWidth(20)
@DateTimeFormat("yyyy-MM-dd")
@ExcelProperty(value = "规则启用时间", index = 8)
private String rulestarttime;
@ColumnWidth(20)
@ExcelProperty(value = "规则设置日期", index = 9)
private String rulesetdate;
@ColumnWidth(15)
@ExcelProperty(value = "规则设置人", index = 10)
private String rulesetperson;
}
自定义异常类：
package com.vip.lis.vipConfig.config;
import lombok.Data;
import lombok.EqualsAndHashCode;
/**
* 自定义注解异常
*/
@EqualsAndHashCode(callSuper = true)
@Data
public class ExceptionCustom extends RuntimeException {
private static final long serialVersionUID = 1L;
public ExceptionCustom() {
}
/**
* 错误编码
*/
private String errorCode;
/**
* 消息是否为属性文件中的Key
*/
private boolean propertiesKey = true;
/**
* 构造一个基本异常.
*
* @param message 信息描述
*/
public ExceptionCustom(String message) {
super(message);
}
/**
* 构造一个基本异常.
*
* @param errorCode 错误编码
* @param message 信息描述
*/
public ExceptionCustom(String errorCode, String message) {
this(errorCode, message, true);
}
/**
* 构造一个基本异常.
*
* @param errorCode 错误编码
* @param message 信息描述
*/
public ExceptionCustom(String errorCode, String message, Throwable cause) {
this(errorCode, message, cause, true);
}
/**
* 构造一个基本异常.
*
* @param errorCode 错误编码
* @param message 信息描述
* @param propertiesKey 消息是否为属性文件中的Key
*/
private ExceptionCustom(String errorCode, String message, boolean propertiesKey) {
super(message);
this.setErrorCode(errorCode);
this.setPropertiesKey(propertiesKey);
}
/**
* 构造一个基本异常.
*
* @param errorCode 错误编码
* @param message 信息描述
*/
public ExceptionCustom(String errorCode, String message, Throwable cause, boolean propertiesKey) {
super(message, cause);
this.setErrorCode(errorCode);
this.setPropertiesKey(propertiesKey);
}
/**
* 构造一个基本异常.
*
* @param message 信息描述
* @param cause 根异常类（可以存入任何异常）
*/
public ExceptionCustom(String message, Throwable cause) {
super(message, cause);
}
}
Controller层：
@RestController
@Slf4j
@RequestMapping(value = "/config")
public class configController {
@Resource
private ConvertstandardConfigService convertstandardConfigService;
/**
* 产品配置 导入
*/
@PostMapping("/upLoad")
public BaseResponse upLoad(@RequestParam("file") MultipartFile file, @RequestParam("operator") String operator) {
if (file == null) {
// .....报错略过
}
if (StringUtils.isBlank(operator)) {
// .....报错略过
}
String originalFilename = file.getOriginalFilename();
String s = originalFilename.toLowerCase();
String s1 = s.substring(s.indexOf('.') + 1).toLowerCase();
if (!"xlsx".equals(s1) && !"xls".equals(s1)) {
// .....报错略过
}
return convertstandardConfigService.importExcel(file, operator);
}
}
service层：
@Service
@Slf4j
public class ConvertstandardConfigServiceImpl implements ConvertstandardConfigService {
@Resource
private ConvertstandardConfigMapper convertstandardConfigMapper;
/**
* 产品折标配置 导入
*
* @param
* @return
*/
@Transactional
@Override
public ReturnMessage importExcel(MultipartFile file, String operator) {
ReturnMessage returnMessage = new ReturnMessage();
List<ConvertstandardExcelDto> list = new ArrayList<>();
try {
log.info("文件开始导入。。。 ");
//获取excel文件数据并校验
EasyExcel.read(file.getInputStream(),
ConvertstandardExcelDto.class, new ConvertstandardExcelListener(list)).sheet().doRead();
Date date = DateUtil.parseDate(DateUtil.getCurrentDate(), "yyyy-MM-dd");
String time = DateUtil.getCurrentDate("HH:mm:ss");
for (ConvertstandardExcelDto temp : list) {
//插入数据库
Convertstandard convertstand = new Convertstandard();
BeanUtils.copyProperties(temp, convertstand);
convertstand.setRulestarttime(DateUtil.getDate(temp.getRulestarttime()));
convertstand.setRulesetdate(DateUtil.getDate(temp.getRulesetdate()));
if (StringUtils.isBlank(temp.getRulesetdate())) {
convertstand.setRulesetdate(DateUtil.parseDate(DateUtil.getCurrentDate(), "yyyy-MM-dd"));
}
if (StringUtils.isBlank(temp.getRulesetperson())) {
convertstand.setRulesetperson(operator);
}
convertstand.setConvertstandardstatus("0");
convertstand.setMakedate(date);
convertstand.setMaketime(time);
convertstand.setOperator(operator);
convertstand.setModifydate(date);
convertstand.setModifytime(time);
convertstand.setModifyoperator(operator);
convertstandardConfigMapper.insert(convertstand);
}
log.info("产品折标配置导入成功。。。");
returnMessage.setReturnMessage(true, "产品折标配置导入成功");
} catch (Exception e) {
log.error("产品折标配置导入出错：" + e.getMessage());
returnMessage.setReturnMessage(false, "产品折标配置导入出错：" + e.getMessage());
}
return returnMessage;
}
}
easyExcel 核心代码 数据校验Listener
package com.vip.lis.vipConfig.listener;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.fastjson.JSON;
import com.vip.lis.utils.pubfun.DateUtil;
import com.vip.lis.vipConfig.config.ExceptionCustom;
import com.vip.lis.vipConfig.mapper.ConvertstandardConfigMapper;
import com.vip.lis.vipConfig.pojo.Convertstandard;
import com.vip.lis.vipConfig.pojo.ConvertstandardExcelDto;
import com.vip.lis.vipConfig.pojo.Convertstandardb;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@Slf4j
@Component
public class ConvertstandardExcelListener extends AnalysisEventListener<ConvertstandardExcelDto> {
List<ConvertstandardExcelDto> res;
public ConvertstandardExcelListener(List<ConvertstandardExcelDto> res) {
this.res = res;
}
//一行一行读取
@Override
public void invoke(ConvertstandardExcelDto temp, AnalysisContext analysisContext) {
log.info("新的一行开始");
try {
//数据校验
if (StringUtils.isBlank(temp.getProductsort())) {
throw new ExceptionCustom("1", "产品分类不能为空");
}
if (StringUtils.isBlank(temp.getConvertstandard())) {
throw new ExceptionCustom("1", "折算系数不能为空");
}
if (StringUtils.isBlank(temp.getRulestarttime())) {
throw new ExceptionCustom("1", "规则启用日期不能为空");
}
if(temp.getRulestarttime().length() != 10){
log.info("规则启用日期格式不正确，应为yyyy-mm-dd”或“yyyy/mm/dd格式，请调整");
throw new ExceptionCustom("1", "规则启用日期格式不正确，应为yyyy-mm-dd”或“yyyy/mm/dd格式，请调整");
}
String startTime = temp.getRulestarttime().substring(0, 10);
String PT4 = "((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})/(((0[13578]|1[02])/(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)/(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|((0[48]|[2468][048]|[3579][26])00))-02-29))";
String PT5 = "((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|((0[48]|[2468][048]|[3579][26])00))-02-29))";
Pattern p4 = Pattern.compile(PT4);
Pattern p5 = Pattern.compile(PT5);
Matcher matcher = p4.matcher(startTime);
Matcher matcher1 = p5.matcher(startTime);
if (!matcher.matches() && !matcher1.matches()) {
log.info("规则启用日期格式不正确，应为yyyy-mm-dd”或“yyyy/mm/dd格式，请调整");
throw new ExceptionCustom("1", "规则启用日期格式不正确，应为yyyy-mm-dd”或“yyyy/mm/dd格式，请调整");
}
// //规则设置时间
if (StringUtils.isNotBlank(temp.getRulesetdate())) {
if(temp.getRulesetdate().length() != 10){
log.info("规则设置时间格式不正确，应为yyyy-mm-dd”或“yyyy/mm/dd格式，请调整");
throw new ExceptionCustom("1", "规则设置时间格式不正确，应为yyyy-mm-dd”或“yyyy/mm/dd格式，请调整");
}
String setTime = temp.getRulesetdate().substring(0, 10);
matcher = p4.matcher(setTime);
matcher1 = p5.matcher(setTime);
if (!matcher.matches() && !matcher1.matches()) {
log.info("规则设置时间格式不正确，应为yyyy-mm-dd”或“yyyy/mm/dd格式，请调整");
throw new ExceptionCustom("1", "规则设置时间格式不正确，应为yyyy-mm-dd”或“yyyy/mm/dd格式，请调整");
}
}
if (!checkMore(temp, res)) {
throw new ExceptionCustom("1", "产品分类、产品名称、长短险标记、交费年期、渠道、机构信息组合不可重复");
}
} catch (ExceptionCustom e) {
log.error("数据校验失败：" + e.getMessage());
//在easyExcel监听器中抛出业务异常
throw new ExcelAnalysisException(e.getMessage());
}
log.info("数据校验通过");
res.add(temp);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.info("所有数据解析完成！");
log.info("解析数据为:{}", JSON.toJSONString(res));
}
private boolean checkMore(ConvertstandardExcelDto dto, List<ConvertstandardExcelDto> list) {
String match = toString(dto);
for (ConvertstandardExcelDto temp : list) {
String str = toString(temp);
if (match.equals(str)) {
return false;
}
}
return true;
}
private String toString(ConvertstandardExcelDto dto) {
String match = dto.getProductsort().trim();
if (StringUtils.isNotBlank(dto.getProductname())) {
match = match + dto.getProductname().trim();
}
if (StringUtils.isNotBlank(dto.getLongriskflag())) {
match = match + dto.getLongriskflag().trim();
}
if (StringUtils.isNotBlank(dto.getPayyearterm())) {
match = match + dto.getPayyearterm().trim();
}
if (StringUtils.isNotBlank(dto.getChannel())) {
match = match + dto.getChannel().trim();
}
if (StringUtils.isNotBlank(dto.getManagecom())) {
match = match + dto.getManagecom().trim();
}
return match;
}
}
导出时数据有效性测试
加了数据有效性校验的数据只能通过下拉框选择，输入其他数据会报错。
excel文件复杂导出（数据有效性校验）代码实现
Controller层：
@RestController
@Slf4j
@RequestMapping(value = "/config")
public class configController {
/**
* 产品配置 导出
*/
@GetMapping("/download")
ResponseEntity<byte[]> exportData(){
try {
log.info("********进行 产品折标配置 导出处理********");
String fileName = "产品折标配置.xlsx";
QueryWrapper<Convertstandard> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByAsc("(productsort+0)").orderByAsc("productname").orderByAsc("(payyearterm+0)");
List<Convertstandard> currentList = convertstandardConfigMapper.selectList(queryWrapper);
List<ConvertstandardExcelDto> excelList = new ArrayList<>(currentList.size());
int i = 1;
for (Convertstandard temp : currentList) {
ConvertstandardExcelDto excelDto = new ConvertstandardExcelDto();
excelDto.setId(i++);
BeanUtils.copyProperties(temp, excelDto);
if (temp.getRulesetdate() != null) {
excelDto.setRulesetdate(DateUtil.getDateStr(temp.getRulesetdate(), "yyyy-MM-dd"));
}
if (temp.getRulestarttime() != null) {
excelDto.setRulestarttime(DateUtil.getDateStr(temp.getRulestarttime(), "yyyy-MM-dd"));
}
excelList.add(excelDto);
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
EasyExcel.write(os, ConvertstandardExcelDto.class).sheet("产品折标配置").registerWriteHandler(new ProductConfigSheetWriteHandler(vipConfigMapper)).doWrite(excelList);
ByteArrayInputStream is = new ByteArrayInputStream(os.toByteArray());
//创建字节数组
byte[] bytes = new byte[is.available()];
//将流读到字节数组中
is.read(bytes);
//创建HttpHeaders对象设置响应头信息
MultiValueMap<String, String> headers = new HttpHeaders();
//设置要下载方式以及下载文件的名字
headers.add("Content-Disposition", "attachment;filename=" + fileName);
//设置响应状态码
HttpStatus statusCode = HttpStatus.OK;
//创建ResponseEntity对象
ResponseEntity<byte[]> responseEntity = new ResponseEntity<>(bytes, headers, statusCode);
//关闭输入流
is.close();
return responseEntity;
} catch (Exception e) {
logger.error("下载照片失败", e.getMessage());
return null;
}
}
}
导出数据有效性校验
核心代码handler
package com.vip.lis.vipConfig.handler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import com.vip.lis.vipConfig.mapper.CustomerLevelMapper;
import com.vip.lis.vipConfig.pojo.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.util.List;
@Slf4j
public class ProductConfigSheetWriteHandler implements SheetWriteHandler {
private CustomerLevelMapper vipConfigMapper;
//mapper这里是拿不到的，需要通过构造器将mapper传入操作
public ProductConfigSheetWriteHandler(CustomerLevelMapper vipConfigMapper) {
this.vipConfigMapper = vipConfigMapper;
}
@Override
public void afterSheetCreate(SheetWriteHandlerContext context) {
try{
log.info("{}Sheet写入成功。", context.getWriteSheetHolder().getSheetName());
/**
* 产品分类数据有效性校验
*/
List<ProductsortList> productSortList = vipConfigMapper.queryProductsort();
String[] productSort = new String[productSortList.size()];
int i = 0;
for (ProductsortList temp : productSortList) {
productSort[i++] = temp.getProductsort();
}
// 区间设置 比如 第一列第一行和第二行的数据。由于第一行是头，所以第一、二行的数据实际上是第二三行
// * @param firstRow 开始行 从0开始
// * @param endRow 结束行 从0开始
// * @param firstCol 开始列 从0开始
// * @param endCol 结束列 从0开始
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 10000, 1, 1);
DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(productSort);
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
/**
* 产品名称数据有效性校验
*/
List<ProductnameList> productNameLists = vipConfigMapper.queryProductname();
String[] productName = new String[productNameLists.size()];
i = 0;
for (ProductnameList temp : productNameLists) {
int index = temp.getProductname().indexOf("-");
productName[i++] = temp.getProductname().substring(index + 1);
}
cellRangeAddressList = new CellRangeAddressList(1, 10000, 2, 2);
helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
constraint = helper.createExplicitListConstraint(productName);
dataValidation = helper.createValidation(constraint, cellRangeAddressList);
context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
/**
* 长短险标记数据有效性校验
*/
cellRangeAddressList = new CellRangeAddressList(1, 10000, 3, 3);
helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
constraint = helper.createExplicitListConstraint(new String[]{"L", "D"});
dataValidation = helper.createValidation(constraint, cellRangeAddressList);
context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
/**
* 缴费年期数据有效性校验
*/
List<PayyeartermList> payYearList = vipConfigMapper.queryPayyearterm();
String[] payYear = new String[payYearList.size()];
i = 0;
for (PayyeartermList temp : payYearList) {
payYear[i++] = temp.getPayyearterm();
}
cellRangeAddressList = new CellRangeAddressList(1, 10000, 4, 4);
helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
constraint = helper.createExplicitListConstraint(payYear);
dataValidation = helper.createValidation(constraint, cellRangeAddressList);
context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
/**
* 渠道数据有效性校验
*/
List<ChannelList> channelList = vipConfigMapper.queryChannel();
String[] channel = new String[channelList.size()];
i = 0;
for (ChannelList temp : channelList) {
channel[i++] = temp.getChannelcode();
}
cellRangeAddressList = new CellRangeAddressList(1, 10000, 5, 5);
helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
constraint = helper.createExplicitListConstraint(channel);
dataValidation = helper.createValidation(constraint, cellRangeAddressList);
context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
/**
* 机构数据有效性校验
*/
List<ManagecomList> manageComLists = vipConfigMapper.queryManagecom();
String[] manageCom = new String[manageComLists.size()];
i = 0;
for (ManagecomList temp : manageComLists) {
manageCom[i++] = temp.getComcode();
}
cellRangeAddressList = new CellRangeAddressList(1, 10000, 6, 6);
helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
constraint = helper.createExplicitListConstraint(manageCom);
dataValidation = helper.createValidation(constraint, cellRangeAddressList);
context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
}catch (Exception e){
log.error("产品折标配置数据有效性校验出错：{}",e.getMessage());
throw e;
}
}
}
以上就是全部代码实现步骤，非常感谢各位的观看！！
觉得有帮助的记得一键三连 点赞关注收藏哦！！
让我康康
某些人不会看了不点赞吧！！！
1367
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
