正文
简单实现导入和导出
导入代码
POM
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>${easyexcel.version}</version>
</dependency>
FileUtil
/**
* 判断文件后缀是否为xls或xlsx
*
* @param file file
* @return boolean
*/
public static boolean isXls(MultipartFile file) {
if (null == file || StringUtil.isBlank(file.getOriginalFilename())) {
throw new RuntimeException("请上传文件!");
}
if ((!StringUtils.endsWithIgnoreCase(file.getOriginalFilename(), ".xls") &&
!StringUtils.endsWithIgnoreCase(file.getOriginalFilename(), ".xlsx"))) {
throw new RuntimeException("请上传正确的excel文件!");
}
return true;
}
/**
* 导出数据为xlsx文件
*
* @param dataList 数据源
* @param clazz 模板
* @param sheetName 标题名
* @param fileName 文件名
* @param response response
* @param <T> 数据源的class
* @param <E> 模板的class
* @throws IOException
*/
public static <T, E> void exportToExcel(List<T> dataList, Class<E> clazz, String sheetName, String fileName, HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding(org.apache.commons.codec.Charsets.UTF_8.name());
String encodedFileName = URLEncoder.encode(fileName, org.apache.commons.codec.Charsets.UTF_8.name());
response.setHeader("Content-disposition", "attachment;filename=" + encodedFileName + ".xlsx");
List<E> data = CollectionUtils.isEmpty(dataList) ? Lists.newArrayList() :
dataList.stream().map(e -> BeanUtil.copy(e, clazz)).collect(Collectors.toList());
EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(data);
}
/**
* 导出模板
*
* @param clazz 模板
* @param sheetName 标题名
* @param fileName 文件名
* @param response response
* @param <T> 数据源的class
* @throws IOException
*/
public static <T> void exportMould(Class<T> clazz, String sheetName, String fileName, HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding(org.apache.commons.codec.Charsets.UTF_8.name());
String encodedFileName = URLEncoder.encode(fileName, org.apache.commons.codec.Charsets.UTF_8.name());
response.setHeader("Content-disposition", "attachment;filename=" + encodedFileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(Lists.newArrayList());
}
/**
* 读取Excel文件内容，常用于导入
* @param file file
* @param clazz 模板
* @param listener listener
* @param <T> 数据源的class
* @throws IOException
*/
public static <T> void readExcel(MultipartFile file, Class<T> clazz, AnalysisEventListener<T> listener) throws IOException {
InputStream inputStream = new BufferedInputStream(file.getInputStream());
ExcelReaderBuilder builder = EasyExcel.read(inputStream, clazz, listener);
builder.doReadAll();
}
controller
@SneakyThrows
@PostMapping("/import")
@ApiOperation(value = "导入", notes = "导入")
public R<Map<String, Integer>> importData(MultipartFile file) {
return iExceptionPersonService.importData(file);
}
@SneakyThrows
@GetMapping("/exportMould")
@ApiOperation(value = "异常人员模板导出", notes = "")
public void exportMould(HttpServletResponse response) {
iExceptionPersonService.exportMould(response);
}
@SneakyThrows
@GetMapping("/export")
@ApiOperation(value = "导出", notes = "导出")
public void export(Query query, ExceptionPersonDTO exceptionPersonDTO, HttpServletResponse response) {
iExceptionPersonService.export(query, exceptionPersonDTO, response);
}
Service
/**
* 导入
*
* @param list 数据
*/
Map<String, Integer> importer(List<ExceptionPersonImportExcel> list);
/**
* 导入
*
* @param file
* @return R<Map < String, Integer>>
*/
R<Map<String, Integer>> importData(MultipartFile file) throws IOException;
/**
* 导出模板
*
* @param response response
*/
void exportMould(HttpServletResponse response) throws IOException;
/**
* 导出
*
* @param query 分页条件
* @param exceptionPersonDTO查询条件
* @param response response
*/
void export(Query query, ExceptionPersonDTO exceptionPersonDTO, HttpServletResponse response) throws IOException;
ServiceImpl
@Override
public Map<String, Integer> importer(List<ExceptionPersonImportExcel> list) {
Map<String, Integer> rstMap = new HashMap<>();
List<ExceptionPerson> exceptionPersonList = list.stream().map(e -> {
ExceptionPerson exceptionPerson = BeanUtil.copy(e, ExceptionPerson.class);
exceptionPerson.setExceptionType(ExceptionPerson.ExceptionTypeEnum.IMPORT.getKey());
return exceptionPerson;
}).collect(Collectors.toList());
rstMap.put("成功导入数量", exceptionPersonList.size());
this.saveBatch(exceptionPersonList);
return rstMap;
}
@Override
public R<Map<String, Integer>> importData(MultipartFile file) throws IOException {
if (FileUtil.isXls(file)) {
ExceptionPersonListener importListener = new ExceptionPersonListener(this);
FileUtil.readExcel(file,ExceptionPersonImportExcel.class,importListener);
return R.data(importListener.getRstMap(), "导入异常人员成功");
}
return R.data(null, "导入异常人员失败");
}
@Override
public void exportMould(HttpServletResponse response) throws IOException {
FileUtil.exportMould(ExceptionPersonImportExcel.class,"异常人员模板","异常人员模板",response);
}
@Override
public void export(Query query, ExceptionPersonDTO exceptionPersonDTO, HttpServletResponse response) throws IOException {
List<ExceptionPersonVO> list = exceptionPersonList(Condition.getPage(query), exceptionPersonDTO).getRecords();
FileUtil.exportToExcel(list, ExceptionPersonExcel.class, "导入异常人员列表", "导入异常人员列表", response);
}
ExceptionPersonListener文章来源：https://www.toymoban.com/news/detail-545072.html
/**
* 异常人员管理
*
* @author JunHao Huang
* @since 2023-06-13 14:33:50
*/
@Data
@RequiredArgsConstructor
@EqualsAndHashCode(callSuper = true)
public class ExceptionPersonListener extends AnalysisEventListener<ExceptionPersonImportExcel> {
/**
* 默认每隔1000条存储数据库
*/
private int batchCount = 1000;
/**
* 缓存的数据列表
*/
private List<ExceptionPersonImportExcel> list = new ArrayList<>();
private Map<String, Integer> rstMap = new HashMap<>();
private final IExceptionPersonService iExceptionPersonService;
@Override
public void invoke(ExceptionPersonImportExcel data, AnalysisContext analysisContext) {
list.add(data);
// 达到BATCH_COUNT，则调用importer方法入库，防止数据几万条数据在内存，容易OOM
if (list.size() >= batchCount) {
// 调用importer方法
if(ObjectUtil.isEmpty(rstMap) || rstMap.get("成功导入数量") == 0){
rstMap = iExceptionPersonService.importer(list);
}else {
iExceptionPersonService.importer(list);
}
// 存储完成清理list
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 调用importer方法
if(ObjectUtil.isEmpty(rstMap) || rstMap.get("成功导入数量") == 0){
rstMap = iExceptionPersonService.importer(list);
}else {
iExceptionPersonService.importer(list);
}
// 存储完成清理list
list.clear();
}
}
ExceptionPersonImportExcel文章来源地址https://www.toymoban.com/news/detail-545072.html
/**
* 异常人员管理
*
* @author JunHao Huang
* @since 2023-06-13 14:33:50
*/
@Data
@ColumnWidth(25)
@HeadRowHeight(20)
@ContentRowHeight(18)
@HeadFontStyle(fontName = "微软雅黑", fontHeightInPoints = 8)
public class ExceptionPersonImportExcel {
private static final long serialVersionUID = 1L;
@ColumnWidth(25)
@ExcelProperty(value = "员工编号")
private String personCode;
@ColumnWidth(25)
@ExcelProperty(value = "姓名")
private String personName;
@ColumnWidth(25)
@ExcelProperty(value = "身份证")
private String idCardNo;
@ColumnWidth(25)
@ExcelProperty(value = "手机号")
private String mobile;
@ColumnWidth(25)
@ExcelProperty(value = "所属渠道")
private String orgName;
@ColumnWidth(25)
@ExcelProperty(value = "所属大区")
private String areaName;
@ColumnWidth(25)
@ExcelProperty(value = "所属省区")
private String subAreaName;
@ColumnWidth(25)
@ExcelProperty(value = "城市")
private String miniAreaName;
@ColumnWidth(25)
@ExcelProperty(value = "负责人")
private String storeSupervisorName;
@ColumnWidth(25)
@ExcelProperty(value = "入职时间")
@JsonFormat(pattern = DateUtil.PATTERN_DATETIME)
private Date entryTime;
@ColumnWidth(25)
@ExcelProperty(value = "离职时间")
@JsonFormat(pattern = DateUtil.PATTERN_DATETIME)
private Date dimissionTime;
@ColumnWidth(25)
@ExcelProperty(value = "所属公司名称")
private String companyName;
@ColumnWidth(25)
@ExcelProperty(value = "纳入黑名单的原因")
private String remark;
}
到了这里，关于使用EasyExcel实现导入和导出的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
