java读取Excel表格数据

环境说明

jdk1.8，springboot2.5.3

项目结构

1.controller层

package com.example.pdf.controller;

import com.example.pdf.service.ReadExcelFileService;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;

@RestController
@RequestMapping(value = "read")
public class ReadExcelFileController {

    @Resource
    private ReadExcelFileService readExcelFileService;

    @PostMapping("excelFile")
    public void excelFile(@RequestBody MultipartFile file) {
        readExcelFileService.excelFile(file);

    }
}

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

2.service层

package com.example.pdf.service;

import org.springframework.web.multipart.MultipartFile;

public interface ReadExcelFileService {

    /**
     * 读excel表格数据
     * */
    void excelFile(MultipartFile file);
}

1
2
3
4
5
6
7
8
9
10
11
12

实现层

package com.example.pdf.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.example.pdf.model.StudentModel;
import com.example.pdf.service.ReadExcelFileService;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

@Service
public class ReadExcelFileServiceImpl implements ReadExcelFileService {
    /**
     * 读excel表格数据
     *
     * @param file
     */
    @Override
    public void excelFile(MultipartFile file) {
        try {
            // 最高缓存量，放满后存储一次数据库，然后清空缓存
            int batchCount = 100;
            EasyExcel.read(file.getInputStream(), StudentModel.class, new PageReadListener<StudentModel>(list -> {
                System.out.println("已完成" + list.size() + "条车辆数据读取...");
                for (StudentModel studentModel : list) {
                    System.out.println("学生姓名：" + studentModel.getName() + ",学生年龄：" + studentModel.getAge());
                }
            }, batchCount)).sheet(0).doRead();
        } catch (Exception e) {
            System.out.println("读取数据异常！");
        }
    }
}

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

StudentModel.java类

package com.example.pdf.model;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class StudentModel {

    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("年龄")
    private Integer age;

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

使用的Maven依赖

<!-- lombok插件 -->
        <dependency>
            <artifactId>lombok</artifactId>
            <groupId>org.projectlombok</groupId>
            <scope>provided</scope>
            <version>1.18.10</version>
        </dependency>
        
<!-- alibaba Excel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.2</version>
        </dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

效果示例一

效果示例二

文档截图

第一页

第二页

postman请求说明

其他说明

异常可根据具体业务进行补充，这里只是简单的演示