1.引入依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>${easyexcel.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel-core</artifactId>
<version>3.3.4</version>
<scope>compile</scope>
</dependency>
2.监听器(用于解析和存储到数据库):
@Slf4j
public class DemoDataListener<T> extends AnalysisEventListener<T> {
private static final int BATCH_COUNT = 100;
private List<T> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
private BaseService demoDAO;
public DemoDataListener(BaseService demoDAO) {
this.demoDAO = demoDAO;
}
@Override
public void invoke(T data, AnalysisContext context) {
log.info("解析到一条数据:{}", JSON.toJSONString(data));
cachedDataList.add(data);
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
log.info("所有数据解析完成!");
}
private void saveData() {
log.info("{}条数据,开始存储数据库!", cachedDataList.size());
demoDAO.insertBatch(cachedDataList);
log.info("存储数据库成功!");
}
}
3.excel工具类:
public class ExcelUtils {
public static void exportExcel(HttpServletResponse response, String fileName, String sheetName, List<?> list,
Class<?> pojoClass) throws IOException {
if (StrUtil.isBlank(fileName)) {
fileName = DateUtils.format(new Date());
}
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
fileName = URLUtil.encode(fileName, StandardCharsets.UTF_8);
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), pojoClass).registerConverter(new LongStringConverter()).registerConverter(new DateDateConverter()).sheet(sheetName).doWrite(list);
}
public static void exportExcelToTarget(HttpServletResponse response, String fileName, String sheetName, List<?> sourceList,
Class<?> targetClass) throws Exception {
List targetList = new ArrayList<>(sourceList.size());
for (Object source : sourceList) {
Object target = targetClass.newInstance();
BeanUtils.copyProperties(source, target);
targetList.add(target);
}
exportExcel(response, fileName, sheetName, targetList, targetClass);
}
public static void importExcel(MultipartFile excel, Class<?> pojoClass, BaseService baseService) throws IOException {
EasyExcel.read(excel.getInputStream(), pojoClass, new DemoDataListener<>(baseService))
.sheet()
.headRowNumber(1)
.doRead();
}
public static <T> List<T> readExcel(MultipartFile excel, Class<?> pojoClass) throws IOException {
return EasyExcelFactory
.read(excel.getInputStream())
.sheet()
.head(pojoClass)
.doReadSync();
}
}
4.实例;
@PostMapping("/export")
@ApiOperation("导出")
public void exportExcel(HttpServletResponse response, @RequestBody List<BaseContentVo> baseContentVos) {
try {
ExcelUtils.exportExcelToTarget(response, "基础数据", "", baseContentVos, BaseContent.class);
} catch (Exception e) {
throw new GeneralException("导出失败");
}
}
@GetMapping("/export/template")
@ApiOperation("导出模版")
public void exportExcelTemplate(HttpServletResponse response) {
try {
ExcelUtils.exportExcelToTarget(response, "基础数据", "", Collections.emptyList(), BaseTechnologyDto.class);
} catch (Exception e) {
throw new GeneralException("导出模版失败");
}
}
@PostMapping("/import")
@ApiOperation("导入")
public Result importExcel(MultipartFile file) {
try {
List<BaseContentDto> dtoList = ExcelUtils.readExcel(file, BaseContentDto.class);
for (BaseContentDto dto : dtoList) {
DefaultValueUtils.applyDefaultValues(dto);
baseContentService.add(dto);
}
} catch (IOException e) {
throw new GeneralException("导入失败");
}
return new Result<>();
}