实现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文件中的数据。
```
java
public class User {
private Integer id;
private String name;
private String email;
private String phone;
// 省略 getter 和 setter 方法
}
```
3. 创建Mapper
创建一个Mapper，用于操作数据库。
```
java
@Mapper
public interface UserMapper {
List<User> findAll();
void insert(User user);
void batchInsert(List<User> userList);
}
```
4. 创建Service
创建一个Service，用于
导入和
导出Excel文件。
```
java
@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 = read
Excel(file);
userMapper.batchInsert(userList);
}
public void export
Excel(HttpServletResponse response) throws IOException {
List<User> userList = userMapper.findAll();
write
Excel(response, userList);
}
private List<User> read
Excel(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 write
Excel(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文件的请求。
```
java
@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 export
Excel(HttpServletResponse response) throws IOException {
userService.export
Excel(response);
}
}
```
至此，就完成了
SpringBoot集成MyBatis和
POI实现Excel导入导出的步骤。
215
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
