每次都要去看easy excel的导入的例子，回回都没记性这次记录一下。
将个人荣誉信息导入到数据库中，并且将对应字段查询出来方便后续查询使用。
@Override
public Map<String, Object> importHonorData(MultipartFile multipartFile) throws Exception {
Map<String, Object> map = new HashMap();
EasyExcel.read(multipartFile.getInputStream(), HonorData.class, new HonorDataListener(honorWallMapper)).sheet().headRowNumber(2).doRead();
return map;
}
@Slf4j
public class HonorDataListener implements ReadListener<HonorData> {
/**
* 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 100;
/**
* 缓存的数据
*/
private List<HonorData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
private List<Map<String,Object>> cachedDataWrongList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
/**
* 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private HonorWallMapper demoDAO;
public HonorDataListener() {
// 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
// honorWallMapper = new HonorWallMapper();
}
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param demoDAO
*/
public HonorDataListener(HonorWallMapper demoDAO) {
this.demoDAO = demoDAO;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(HonorData data, AnalysisContext context) {
List<Map<String, Object>> maps = employeeCheck(data.getEmployeeNumber());
String employeeName = data.getEmployeeName();
String employeeNameTrue = employeeName.replace(" ", ""); // 去除所有空格
data.setEmployeeName(employeeNameTrue);
if(maps.size() ==1){
Map<String, Object> employee = maps.get(0);
String lastName = String.valueOf(employee.get("last_name"));
if(lastName.equals(data.getEmployeeName())){
data.setOrgId(Integer.valueOf(String.valueOf(employee.get("organization_id"))));
data.setOrganizationName(String.valueOf(employee.get("organization_name")));
}
}else{
data.setOrganizationName(null);
}
data = updateHonorDataTime(data);
data = updateHonorDatadimension(data);
log.info(data.toString());
log.info("data.toString()");
cachedDataList.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
log.info("{}条数据，开始存储数据库！", cachedDataList.size());
demoDAO.batchsImportExcel(cachedDataList);
log.info("存储数据库成功！");
}
public List<Map<String, Object>> employeeCheck(String employeeNumber){
List<Map<String, Object>> maps = demoDAO.selectEmployeeNameOrgByEmployeeNumber(employeeNumber);
return maps;
}
/**
* 年份字段判断
* @param data
* @return
*/
public HonorData updateHonorDataTime (HonorData data){
data.setYear("2023");
String yearMonthDate = data.getYearMonthDate();
if (yearMonthDate.contains("/")) {
String[] split = yearMonthDate.split("/");
if (split.length == 3) {
LocalDate localDate = LocalDate.of(Integer.valueOf(split[0]), Integer.valueOf(split[1]), Integer.valueOf(split[2]));
LocalDateTime localDateTime = localDate.atStartOfDay();
java.util.Date date = java.util.Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant());
data.setGetDate(new Date(date.getTime()));
}
}
return data;
}
public HonorData updateHonorDatadimension (HonorData data){
String dimensionName = data.getDimensionName();
String dimensionOneName = data.getDimensionOneName();
List<Map<String, Object>> maps = demoDAO.selectHonorDimensionByDimension(dimensionName);
if(maps.size()>0){
List<Map<String, Object>> dimensionNamemaps = demoDAO.selectHonorDimensionByDimension(dimensionOneName);
if(dimensionNamemaps.size()>0){
Map<String, Object> map = dimensionNamemaps.get(0);
data.setDimensionId(Integer.valueOf(String.valueOf(map.get("id"))));
return data;
}
Map<String, Object> map = maps.get(0);
data.setDimensionId(Integer.valueOf(String.valueOf(map.get("id"))));
return data;
}
return data;
}
}
excel多个sheet导出
将通知以及具体的通知明细进行导出
public void exportExcel(HttpServletResponse response, SdssNotificationNoticeInfoQueryVo sdssNotificationBaseTableInfoQueryVo) {
String fileName = "通知导出.xlsx";
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write(response.getOutputStream()).build();
WriteSheet writeSheet1 = EasyExcel.writerSheet(1, "通知").head(SdssNotificationNoticeExcel.class).build();
List<SdssNotificationNoticeExcel> sdssNotificationBaseTableInfoQueryVos = socialSecurityBaseMapper.noticeListNotPage(sdssNotificationBaseTableInfoQueryVo);
excelWriter.write(sdssNotificationBaseTableInfoQueryVos, writeSheet1);
WriteSheet writeSheet2 = EasyExcel.writerSheet(2, "通知详情").head(SdssNotificationBaseTableExcel.class).build();
List<SdssNotificationBaseTableExcel> noticeInfo = socialSecurityBaseMapper.findNoticeInfoByNoticeInfo(sdssNotificationBaseTableInfoQueryVo);
excelWriter.write(noticeInfo, writeSheet2);
} catch (Exception e) {
System.err.println("操作 Excel 文件时出错：" + e.getMessage());
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
// 设置响应头
try {
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
} catch (UnsupportedEncodingException e) {
System.err.println("不支持的字符编码：" + e.getMessage());
}
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
try {
response.flushBuffer();
} catch (IOException e) {
System.err.println("刷新输出流时出错：" + e.getMessage());
}
}
@Data
public class SdssNotificationNoticeExcel {
private static final long serialVersionUID = 1L;
@ExcelIgnore
private int id;
/**
* 通知标题
*/
@ExcelProperty("通知标题")
private String notificationTitle;
/**
* 年份
*/
@ExcelProperty("年份")
private int year;
/**
* 地市
*/
@ExcelIgnore
private String cityId;
@ExcelProperty("地市")
private String cityName;
/**
* 通知内容
*/
@ExcelIgnore
private String notificationContent;
/**
* 通知人
*/
@ExcelProperty("通知人")
private String notifierStaffNo;
/**
* 通知时间
*/
@ExcelIgnore
private Timestamp notificationTime;
/**
* 是否发送1已发送0未发送
*/
@ExcelIgnore
private String ifNotified;
@ExcelIgnore
private String employeeNameOrNumber;
@ExcelIgnore
private String taskDefinitionKey;
@ExcelIgnore
private String sendnoticeemployeecode;
@ExcelProperty("发送人数")
private int notifiedCount;
@ExcelProperty("已读人数")
private int readCount;
@ExcelProperty("发送总人数")
private int sumPerson;
@ExcelIgnore
private List<SdssNotificationBaseTableInfoQueryVo> sdssNotificationBaseTableInfoQueryVos;
}