springboot 导入excel表格读取数据转实体类数据插入

编程社区管理员

已于 2024-02-08 10:02:50 修改

阅读量444

点赞数 5

文章标签： spring boot excel java

于 2024-02-01 18:01:12 首次发布

本文链接：https://blog.csdn.net/qq_53722480/article/details/135979681

版权

1.导入pol.xml依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

2.Maper实体类

@Data
@TableName("train.tab_passenger")
public class Passenger implements Serializable {
    @ExcelIgnore
    @TableId(type = IdType.AUTO)
    private Integer id;
    @ExcelIgnore
    @ApiModelProperty("批次号")
    private String batchNo;
    @ExcelIgnore
    private String departId;
    @ExcelIgnore
    private String userId;
    @ExcelIgnore
    private String userName;
    @ExcelProperty("姓名")
    private String passName;

    @ExcelProperty("性别")
    private String pasaSex;

    @ExcelProperty("联系电话")
    private String passTelephone;

    @ExcelProperty("证件类型")
    private String documentType;

    @ExcelProperty("证件号码")
    private String documentNumber;

    @ExcelProperty("乘客类型")
    private String passengerType;

    @ExcelIgnore
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date createTime;

    @ExcelIgnore
    private Integer status;
    @ExcelIgnore
    @TableField(exist = false)
    private Integer page;//第几页
    @ExcelIgnore
    @TableField(exist = false)
    private Integer limit;//限制第几条

}

3.PassengerController

   /**
     * 上传导入乘客excel
     * @param file
     * @return
     */
    @PostMapping("/import")
    public Result passengerExcel(@RequestParam("file") MultipartFile file) throws IOException {
        Result<Object> result = new Result();
        //获取文件名
        String fileName = file.getOriginalFilename();
        //获取文件的后缀名为xlsx
        String fileXlsx = fileName.substring(fileName.length()-5);
        String fileXls = fileName.substring(fileName.length()-4);
        //如果不是excel文件
        if(!(fileXlsx.equals(".xlsx") || fileXls.equals(".xls"))) {
            result.setCode(400);
            result.setMsg("文件格式错误");
            return result;
        }
        //业务操作
        try {
            List<Passenger> list = EasyExcel.read(file.getInputStream())
                    .head(Passenger.class)
                    .sheet()
                    .doReadSync();
            result.setData(list);
            return result;
        } catch (IOException e) {
            e.printStackTrace();
            result.setCode(500);
            result.setMsg(String.valueOf(e));
            return result;
        }

    }

4.返回信息信息

{
	"code": 200,
	"msg": null,
	"data": [
		{
			"id": null,
			"batchNo": null,
			"departId": null,
			"userId": null,
			"userName": null,
			"passName": "张三",XXXXX
			"pasaSex": "男",
			"passTelephone": "19948XXXXX",
			"documentType": "JM",
			"documentNumber": "53262628200107202612",
			"passengerType": "ADULT",
			"createTime": null,
			"status": null,
			"page": null,
			"limit": null
		}
	]
}