Java easypoi多sheet导入数据

编程课堂

已于 2023-08-24 15:45:20 修改

阅读量582

点赞数

CC 4.0 BY-SA版权

文章标签： java easypoi

于 2023-08-24 15:17:33 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_44917045/article/details/132473437

本文介绍了如何在SpringBoot项目中引入easypoi库，处理Excel文件导入，包括创建实体类、定义Controller、Service和ServiceImpl，以及实现数据导入功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.引入easypoi依赖

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.2.0</version>
</dependency>

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;
}

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);
    }
}

4.service

public interface SchoolService extends IService<School> {

    RequestResult importData(MultipartFile file, String fileDiskPath, String relativePath) throws Exception;
}

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);
	}