Java easypoi多sheet导入数据_java easypoi 读取 workbook 插入数据

1.引入easypoi依赖

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.2.0</version>
</dependency>
1
2
3
4
5

2.实体类

/**
 * 培训机构类
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName(value = "school", autoResultMap = true)
public class School implements Serializable {
    /**
     * 主键
     */
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    /**
     * 培训机构名称
     */
    @Excel(name = "培训机构名称")
    @TableField("name")
    private String name;

    /**
     * 培训机构简称
     */
    @Excel(name = "培训机构简称")
    @TableField("shortname")
    private String shortname;

    /**
     * 经营许可证编号
     */
    @Excel(name = "经营许可证编号")
    @TableField("licnum")
    private String licnum;

    /**
     * 经营许可起始日期
     */
    @Excel(name = "经营许可日期")
    @TableField("licetime")
    private String licetime;
}
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

3.controller

@RestController
@AllArgsConstructor
@RequestMapping("/school")
public class SchoolController {

    @Autowired
    private SchoolService schoolService;

    @PostMapping("/importData")
    public RequestResult importData(@RequestPart("file") MultipartFile file, String fileDiskPath, String relativePath) throws Exception {
        return schoolService.importData(file, fileDiskPath, relativePath);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13

4.service

public interface SchoolService extends IService<School> {

    RequestResult importData(MultipartFile file, String fileDiskPath, String relativePath) throws Exception;
}
1
2
3
4

5.service impl

@Service
public class SchoolServiceImpl extends ServiceImpl<SchoolMapper, School> implements SchoolService {

    @Override
    public RequestResult importData(MultipartFile file, String fileDiskPath, String relativePath) throws Exception {
        List<SchoolDTO> schoolDTOs = getExcelData(file);
        for (SchoolDTO schoolDTO : schoolDTOs) {
            schoolDTO.setName(StrUtil.trim(schoolDTO.getName()));
            baseMapper.insert(schoolDTO);
        }
        return RequestResult.success();
    }

	private List<SchoolDTO> getExcelData(MultipartFile file) throws Exception {
	    ImportParams importParams = new ImportParams();
	    // 表格标题行数，默认0
	    importParams.setTitleRows(0);
	    // 表头行数，默认1
	    importParams.setHeadRows(1);
	    // 字段真正值和列标题之间的距离，默认0
	    importParams.setStartRows(1);
	    // 开始读取的sheet位置，默认为0
	    importParams.setStartSheetIndex(0);
	
	    InputStream inputStream = file.getInputStream();
	    return ExcelImportUtil.importExcel(inputStream, SchoolDTO.class, importParams);
	}
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

参考博客
http://doc.wupaas.com/docs/easypoi