官方文档
1、pom文件
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>2、相关代码：
public String BatchImport(MultipartFile file) {
InputStream is;
try {
is = file.getInputStream();
} catch (IOException e) {
throw new ServerException(“异常”);
}
EasyExcel.read(is, ExcelTest.class, new ReadExcelListener(this)).sheet().headRowNumber(2).doRead();
return null;
}ExcelTest:
@Data
@EqualsAndHashCode
public class ExcelTest {
/**
* 姓名
*/
// @ExcelProperty(value = "*身份证号", index = 0)
private String name;
/**
* 身份证号
*/
// @ExcelProperty(value = "*身份证号", index = 1 )
private String idNo;
/**
* 手机号
*/
// @ExcelProperty(value = "*手机号", index = 2 )
private String phone;
/**
* 邮箱
*/
// @ExcelProperty(value = "*邮箱", index = 3)
private String email;
}ReadExcelListener:
@Slf4j
public class ReadExcelTestListener implements ReadListener<ExcelTest> {
private IReadExcelTestService readExcelTestService;
// 读取的excel的最大行数
private static final int ROW_COUNT = 500;
// 是否存在错误
private Boolean isExitError = false;
// 解析的Excel数据
private List<ExcelTest> excelTests = new ArrayList<>();
// 校验的Excel数据的错误信息
private List<ExcelTestError> excelTestErrors = new ArrayList<>();
public ReadExcelTestListener(IReadExcelTestService readExcelTestService) {
this.readExcelTestService = readExcelTestService;
}
@Override
public void onException(Exception e, AnalysisContext analysisContext) throws Exception {
}
@Override
public void invokeHead(Map<Integer, CellData> headMap, AnalysisContext context) {
Integer rowIndex = context.readRowHolder().getRowIndex();
log.info("ReadExcelTestListener-invokeHead-校验行数【{}】",rowIndex);
if (rowIndex == 1) {
String[] headList = {"*姓名", "*身份证号", "*手机号", "*邮箱"};
// 校验表头的数量
if (headMap.size() != headList.length) {
throw new ServerException("异常");
}
for (int i = 0 ; i < headList.length ; i++) {
if (!headList[i].equals(headMap.get(i).getStringValue())) {
throw new ServerException("异常");
}
}
}
}
@Override
public void invoke(ExcelTest excelTest, AnalysisContext analysisContext) {
// excel读取到哪一行了
Integer rowIndex = analysisContext.readRowHolder().getRowIndex();
log.info("ReadExcelTestListener-invoke-读取数据的行数【{}】",rowIndex);
if (!"示例：张三".equals(excelTest.getName())) {
if (rowIndex < 2) {
throw new ServerException("异常");
}
if (rowIndex > ROW_COUNT) {
throw new ServerException("异常");
}
readExcelTestService.checkImportExcelTest(excelUser,excelTestErrors);
excelTests.add(excelTest);
}
}
@Override
public void extra(CellExtra cellExtra, AnalysisContext analysisContext) {
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
List<String> errorDescList = excelUserErrors.stream().map(ExcelTestError::getErrorDesc).collect(Collectors.toList());
if (errorDescList.stream().anyMatch(StringUtil::isNotBlank)){
isExitError = true;
}
log.info("ReadExcelTestListener-doAfterAllAnalysed-是否有错误【{}】",isExitError);
if (isExitError) {
// 存在错误，生成错误报告
log.info("ReadExcelTestListener-doAfterAllAnalysed-错误报告【{}】",excelTestErrors);
readExcelTestService.uploadErrorReport(excelTestErrors);
} else {
// 不存在错误，入库
excelTests = excelTests.stream().distinct().collect(Collectors.toList());
readExcelTestService.saveExcelTest(excelTests);
}
}
@Override
public boolean hasNext(AnalysisContext analysisContext) {
return true;
}
}
IReadExcelService:
public interface IReadExcelUserService {
/**
* 保存批量导入
* @param excelTests
* @return
*/
boolean saveExcelTest(List<ExcelTest> excelTests);
/**
* 校验批量导入信息
* @param excelTest 当前行的信息
* @param excelTestErrors excel解析的错误信息
*/
void checkImportExcelUser(ExcelTest excelTest , List<ExcelTestError> excelTestErrors);
/**
* 上传错误报告
* @param excelTestErrors 错误信息集合
*/
void uploadErrorReport(List<ExcelTestError> excelTestErrors );
}IReadExcelService实现：
@Override
public String downloadErrorReport(String ticket) {
List<Long> attachIdList = new ArrayList<>();
Long attachId = redis.get(ticket);
attachIdList.add(attachId);
Map<Long, String> map = getDownloadUrlMap(attachIdList);
return map.get(attachId);
}
@Override
@Transactional(rollbackFor = Exception.class)
public boolean saveExcelTest(List<ExcelUser> excelTests) {
log.info("OperateUserServiceImpl-saveExcelUser-导入员工的数量【{}】",excelTests.size());
if (CollectionUtil.isEmpty(excelUsers)) {
throw new ServerException("异常");
}
List<ExcelTestDTO> excelTestDTOList = BeanUtil.copyProperties(excelTests, ExcelTestDTO.class);
List<UserDept> userDepts = new ArrayList<>();
ticket = "";
excelUserDTOList.forEach(test->{
this.save(test);
});
return userDeptService.saveBatch(userDepts);
}
@Override
public void checkImportExcelTest(ExcelTest excelTest, List<ExcelTestError> excelTestErrors) {
// 校验批量导入的员工信息
StringBuilder errorDesc = new StringBuilder();
ExcelTestError excelTestError = BeanUtil.copyProperties(excelTest, ExcelTestError.class);
if (StringUtil.isBlank(excelTestError.getName())) {
errorDesc.append("姓名不能为空#");
}
if (StringUtil.isBlank(excelTestError.getPhone())) {
errorDesc.append("手机号不能为空#");
}
if (StringUtil.isBlank(excelTestError.getEmail())) {
errorDesc.append("邮箱不能为空#");
}
if (StringUtil.isBlank(excelTestError.getIdNo())) {
errorDesc.append("身份证号不能为空#");
}
String error = errorDesc.toString();
excelTestError.setErrorDesc(error);
excelTestErrors.add(excelTestError);
}
@Override
public void uploadErrorReport(List<ExcelTestError> excelTestErrors) {
String fileName = "errorReport" + System.currentTimeMillis()+ ".xlsx";
String filePath = alignmentProperties.getTemplatePath() + File.separator + fileName;
String errorTemplatePath = alignmentProperties.getTemplatePath()+ File.separator + ERROR_TEMPLATE_NAME;
EasyExcel.write(filePath, ExcelUserError.class)
.withTemplate(errorTemplatePath)
.excelType(ExcelTypeEnum.XLSX)
.sheet("批量导入员工信息模板")
.doFill(excelUserErrors);
File f = new File(filePath);
byte[] bytes = FileUtil.File2byte(f);
MultipartFile file = new SicpayMultipartFile(fileName,fileName,bytes);
Attach attach = uploadFile(fileName, file);
log.info("OperateUserServiceImpl-uploadErrorReport-上传错误报告de文件【{}】",attach);
// 删除本地临时文件
FileUtil.deleteTempFile(f);
// 将错误报告保存到redis，并设置超时时间为两小时
ticket = UUID.randomUUID().toString();
redis.setEx(ticket,attach.getId(), 2);
}2024-11-11 start：
导出：
@PostMapping("/export")
public Result export(HttpServletResponse response) throws IOException {
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
String fileName = URLEncoder.encode("导出的名称"+".", StandardCharsets.UTF_8);
response.setHeader(“xlsx”, "attachment;filename=" + fileName + “xlsx”);
// TODO 查询的数据 假设返回为list
ExcelWriter writer = EasyExcel.write(response.getOutputStream()).build();
WriteSheet sheet = EasyExcel.writerSheet(0).head(TestVo.class).build();
writer.write(list, sheet).finish();
return Result.success();
}TestVo:
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class TestVo {
/**
*名称
*/
@ColumnWidth(10)
@ExcelProperty("名称")
private String name;
/**
* 时间
*/
@ColumnWidth(20)
@ExcelProperty("时间")
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
private Date time;
}end