springboot mybatis poi 实现excel导入导出

实现Excel导入导出，需要用到POI库。下面介绍一下使用SpringBoot集成MyBatis和POI实现Excel导入导出的步骤。

1. 引入依赖

在pom.xml文件中添加以下依赖：

<!-- SpringBoot MyBatis 依赖 -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.4</version>
</dependency>

<!-- POI 依赖 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>

2. 创建实体类

创建一个实体类，用于映射Excel文件中的数据。

public class User {
    
    private Integer id;
    private String name;
    private String email;
    private String phone;

    // 省略 getter 和 setter 方法
}

3. 创建Mapper

创建一个Mapper，用于操作数据库。

@Mapper
public interface UserMapper {
    
    List<User> findAll();

    void insert(User user);

    void batchInsert(List<User> userList);

}

4. 创建Service

创建一个Service，用于导入和导出Excel文件。

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    public List<User> findAll() {
        return userMapper.findAll();
    }

    public void insert(User user) {
        userMapper.insert(user);
    }

    public void batchInsert(MultipartFile file) throws IOException {
        List<User> userList = readExcel(file);
        userMapper.batchInsert(userList);
    }

    public void exportExcel(HttpServletResponse response) throws IOException {
        List<User> userList = userMapper.findAll();
        writeExcel(response, userList);
    }

    private List<User> readExcel(MultipartFile file) throws IOException {
        List<User> userList = new ArrayList<>();

        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);

        for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);

            User user = new User();
            user.setName(row.getCell(0).getStringCellValue());
            user.setEmail(row.getCell(1).getStringCellValue());
            user.setPhone(row.getCell(2).getStringCellValue());

            userList.add(user);
        }

        return userList;
    }

    private void writeExcel(HttpServletResponse response, List<User> userList) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Users");

        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("Name");
        headerRow.createCell(1).setCellValue("Email");
        headerRow.createCell(2).setCellValue("Phone");

        for (int i = 0; i < userList.size(); i++) {
            Row row = sheet.createRow(i + 1);

            User user = userList.get(i);

            row.createCell(0).setCellValue(user.getName());
            row.createCell(1).setCellValue(user.getEmail());
            row.createCell(2).setCellValue(user.getPhone());
        }

        response.setHeader("Content-Disposition", "attachment; filename=users.xlsx");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        workbook.write(response.getOutputStream());
    }
}

5. 创建Controller

创建一个Controller，用于接收导入和导出Excel文件的请求。

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/users")
    public List<User> findAll() {
        return userService.findAll();
    }

    @PostMapping("/users")
    public void insert(@RequestBody User user) {
        userService.insert(user);
    }

    @PostMapping("/users/import")
    public void batchInsert(@RequestParam("file") MultipartFile file) throws IOException {
        userService.batchInsert(file);
    }

    @GetMapping("/users/export")
    public void exportExcel(HttpServletResponse response) throws IOException {
        userService.exportExcel(response);
    }
}

至此，就完成了SpringBoot集成MyBatis和POI实现Excel导入导出的步骤。