Excel导入完整案例

案例背景

Excel导入是一个非常常见，但是又比较考验细节处理的需求。由于Excel对数据格式没有约束，用户可能会输入各式各样的数据导致系统无法处理，所以如果细节处理不当，则非常容易引起用户体验问题，数据导入失败，丢失。

在阅读本案例前，应先熟悉Excel处理

1.需求描述

在某项目中，需要实现新闻数据导入，数据包括了新闻标题、发布时间、新闻链接、查看人次等字段。导入完成后，需要告诉用户导入的情况。以下是用户给的原始Excel

用户给的原始数据

2.需求要点分析

需要包含导入的字段：新闻标题、发布时间、新闻链接、查看人次
需要告诉用户导入的情况，比如成功了多少条数据、失败了哪些数据
导入数据时，需要对数据进行校验，防止不合法的数据导入，尤其是人次字段，应该是数字型
时间数据有非常多的格式，比如2025-03-05、2025/03/05、2025年03月05日等，在Excel中无法限定，所以需要能够兼容多种格式

3.解决方案

应该制作导入模板以供用户填写，从而规范用户的数据格式，减少90%的数据格式问题
导入时，对于特殊字段应该进行业务约束校验，如新闻链接是否为合法的URL，人次是否为数字，防止脏数据入库或者引起系统异常
系统应该分类筛选出成功数据，以及失败数据
如有数据导入失败，应该通过Excel导出这部分数据，方便用户查看并修改
如果都导入成功，则提示用户导入成功条数

4.实现步骤

1.前端导入页面
用户点击导入按钮，弹出文件选择框，并且应该有一个模板下载按钮，点击后下载模板

2.模板制作
顶部需要写上一些关键提示，如哪些字段必填，部分字段格式是什么，还有不要删除示例数据等。
同时，对于一些特殊字段，比如人次，应该在Excel中限定为数字型。

3.封装接口，让前端调用下载模板，给用户填写

@ApiOperation("下载模板")
@GetMapping("/downloadTemplate")
public void downloadTemplate() {
  SystemUtil.downloadClassPathFile("/data/excelTemplate/news_import_template.xlsx");
}

4.定义读取Excel的实体类

日期、人次字段，在Excel中无法限定格式，所以此处定义为String类型，防止出现转换异常
额外定义失败原因字段，用于记录导入失败的原因，用于失败时导出Excel
@ExcelProperty中value值应该与模板中的字段名一致，否则无法读取到对应的数据

/**
 * 新闻导入DTO
 */
@Data
public class NewsImportDTO {
    /**
     * 标题
     */
    @ExcelProperty(value = "标题")
    private String title;

    /**
     * 发文日期
     */
    @ExcelProperty(value = "发文日期")
    private String publishDate;


    /**
     * 文章链接
     */
    @ExcelProperty(value = "文章链接")
    private String urlLink;

   /**
    * 人次
    */
   @ExcelProperty(value = "人次")
   private String personNum;

   /**
    * 失败原因
    */
   @ExcelProperty(value = "失败原因")
   private String failureReason;
}

5.新增导入Excel接口

@PostMapping("/importExcel")
@ApiOperation("导入")
public JsonResult<ExcelImportResultDTO> importExcel(@RequestParam(name = "excel") MultipartFile excel) throws IOException {
  if (excel == null || excel.isEmpty()) {
      throw new BusinessException(ResultCode.BAD_REQUEST, "请选择要上传的文件");
  }
  ExcelImportResultDTO dto = baseService.excelImport(excel.getInputStream());
  return new JsonResult<>(dto);
}

6.新增导入Service层逻辑

 /**
  * excel导入
  *
  * @param excel 文件
  */
 ExcelImportResultDTO excelImport(InputStream excel);

@Override
public ExcelImportResultDTO excelImport(InputStream excel) {
   ExcelImportResultDTO result = new ExcelImportResultDTO();
   List<NewsImportDTO> dataList = ExcelUtil.readExcel(excel, NewsImportDTO.class, 0, 2);
   if (dataList.isEmpty() || dataList.size() == 1) {
      throw new BusinessException(ResultCode.BAD_REQUEST, "上传附件为空，请重新选择！");
   }
   //删除样例数据
   dataList.remove(0);
   List<NewsImportDTO> failureList = new ArrayList<>();
   List<News> saveList = new ArrayList<>();

   //URL正则校验表达式
   final String urlRegex = "^https?://[-A-Za-z0-9+&@#/%?=~_|!:,.;]+[-A-Za-z0-9+&@#/%=~_|]";
   final String[] supportedDateFormats = {"yyyy年MM月dd日", "yyyy年M月d日", "yyyy-M-d", "yyyy-MM-dd", "yyyy/M/d", "yyyy/MM/dd"};
   
   //处理数据
   dataList.forEach(data -> {

      if (StringUtil.isEmpty(data.getTitle())) {
         data.setFailureReason("标题不能为空，请检查");
         failureList.add(data);
         return;
      }
      
      // 其他空值校验...略

      //校验URL
      if (StringUtil.isEmpty(data.getUrlLink()) || !ReUtil.isMatch(urlRegex, data.getUrlLink())) {
         data.setFailureReason("链接格式不正确，请检查");
         failureList.add(data);
         return;
      }

      //兼容多种日期格式
      LocalDate publishDate = null;
      for (String format : supportedDateFormats) {
         try {
            publishDate = LocalDateTimeUtil.parseDate(data.getPublishDate(), format);
         } catch (Exception e) {
            //继续尝试下一个格式
         }
      }

      if (publishDate == null) {
         data.setFailureReason("发文日期格式不正确，样例为2025年05月06日或2025-05-06或2025/05/06，请检查");
         failureList.add(data);
         return;
      }
      
      Integer personNum;
      //人次处理
      try {
          personNum = Interger.parseInt(data.getPersonNum());
      } catch (Exception e) {
          //无法转换，说明人次不是数字
          data.setFailureReason("人次非数字格式，请检查");
          failureList.add(data);
          return;
      }
      news.setPersonNum(personNum);
       
      //处理完成一个，存入saveList，后面一把保存
      News news = new News();
      BeanUtil.copyProperties(data, news);
      saveList.add(news);
   });

   if (CollectionUtil.isNotEmpty(saveList)) {
      saveBatch(saveList);
   }

   //处理完成，反馈结果，ExcelImportResultDTO封装了结果，后续根据是否成功，返回不同内容。
   ExcelImportResultDTO result;
   //失败处理，根据模板生成失败Excel，上传到OSS后，返回文件地址，供前端下载查看。同时，返回失败条数和总条数
   if (!CollectionUtil.isEmpty(failureList)) {
       ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
       //news_import_failure.xlsx为导入失败数据的模板文件名
       ExcelUtil.fillExcel(failureList, "news_import_failure.xlsx", NewsImportDTO.class, outputStream);
       FilePathDto filePathDto = OssSaveUtil.save(IoUtil.toStream(outputStream), "新闻导入失败" + System.currentTimeMillis() + ".xlsx");
       result = ExcelImportResultDTO.fail(filePathDto.getUrl(), failureList.size(), dataList.size());
   }
   //成功
   else {
       result = ExcelImportResultDTO.success(dataList.size());
   }
   return result;
}

7.导入接口返回JSON内容展示

成功时，success标记为true，并返回条数

{
  "success": true,
  "successCount": 10
}

失败时，success标记为false，并返回条数和失败数据文件地址

{
  "success": false,
  "successCount": 5,
  "failCount": 5,
  "failFileUrl": "URL地址"
}

8.制作导入失败数据Excel模板

警告

请在导入模板的基础上修改，这样用户可以直接在失败数据Excel上修改，并再次导入

增加一列失败原因
按照FastExcel规范，填写占位符，和NewsImportDTO中的字段一致

9.最终效果