AUTOPOI导入Excel文件（可获取表头数据）

Controller.java部分代码

		@Autowired
		private IAshContentService ashContentService;
		
	  public Result<?> importExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
	  MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
	  Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
	  for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
		  // 获取上传文件对象
		  MultipartFile file = entity.getValue();
		  // 1.创建workbook对象，读取整个文档
		  InputStream inputStream = file.getInputStream();
		  //POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
		  HSSFWorkbook wb = new HSSFWorkbook(inputStream);
		  // 2.读取页脚sheet
		  HSSFSheet sheetAt = wb.getSheetAt(0);
		  //获取 excel 第一行数据（表头）
		  Row row = sheetAt.getRow(0);
		  //获取之后可以根据自己想要什么数据，自己获取
		  //获取当前单元格当天日期
		  Date nowday = row.getCell(0).getDateCellValue();
		  ImportParams params = new ImportParams();
		  //表格标题所在行，计数从0开始
		  params.setTitleRows(1);
		  //head头部所在行，计数从0开始
		  params.setHeadRows(1);
		  //表格sheet数量
		  params.setSheetNum(1);
		  params.setNeedSave(true);
		  try {
		  //第一个参数设置文件流、第二个参数导入模板Excel实体类class文件，第三个参数去除表格标题和头部行
			  List<AshContent> list = ExcelImportUtil.importExcel(file.getInputStream(), AshContent.class, params);

			  //提交重复文件删除已经导入的数据再重新导入
			  //ashContentService.deleteByNowday(nowday);
			  //update-begin-author:taoyan date:20190528 for:批量插入数据
			  long start = System.currentTimeMillis();
			  //循环设置日期
			  list.stream().forEach(e->{
				  e.setNowday(nowday);
			  });
			  ashContentService.saveBatch(list);
			  log.info("消耗时间" + (System.currentTimeMillis() - start) + "毫秒");
			  //update-end-author:taoyan date:20190528 for:批量插入数据
			  return Result.ok("文件导入成功！数据行数：" + list.size());
		  } catch (Exception e) {
			  //update-begin-author:taoyan date:20211124 for: 导入数据重复增加提示
			  String msg = e.getMessage();
			  log.error(msg, e);
			  if(msg!=null && msg.indexOf("Duplicate entry")>=0){
				  return Result.error("文件导入失败:有重复数据！");
			  }else{
				  return Result.error("文件导入失败:" + e.getMessage());
			  }
			  //update-end-author:taoyan date:20211124 for: 导入数据重复增加提示
		  } finally {
			  try {
				  file.getInputStream().close();
			  } catch (IOException e) {
				  e.printStackTrace();
			  }
		  }
	  }
	  return Result.error("文件导入失败！");
  }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

Service.java部分代码

    Boolean saveBatch(List<AshContent> list);

    void deleteByNowday(Date nowday);
1
2
3

ServiceImpl.java部分代码

    @Resource
    private AshContentMapper mapper;

    @Override
    public Boolean saveBatch(List<AshContent> list) {
        Boolean response = mapper.insertBatch(list);
        return response;
    }

    @Override
    public void deleteByNowday(Date nowday) {
        mapper.deleteByNowday(nowday);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13

Mapper.java部分代码

    Boolean insertBatch(List<AshContent> list);

    void deleteByNowday(@Param("nowday") Date nowday);
1
2
3

mapper.xml部分代码

<insert id="insertBatch">
        INSERT INTO `table`(
		id
		)
        values
        <foreach collection="list" item="item" separator=",">
        (
		#{item.id}
		)
        foreach>
insert>
<delete id="deleteByNowday">
    delete from table where xxx.nowday=#{nowday}
delete>
1
2
3
4
5
6
7
8
9
10
11
12
13
14