本文目录导读：
我将为您提供一个完整的Spring Boot导入导出案例,涵盖Excel文件的导入和导出功能。
项目依赖配置
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.example</groupId>
<artifactId>import-export-demo</artifactId>
<version>1.0.0</version>
<packaging>jar</packaging>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.14</version>
<relativePath/>
</parent>
<properties>
<java.version>1.8</java.version>
<easyexcel.version>3.3.2</easyexcel.version>
</properties>
<dependencies>
<!-- Spring Boot Web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- MyBatis Plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.3.1</version>
</dependency>
<!-- MySQL -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
<!-- EasyExcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>${easyexcel.version}</version>
</dependency>
<!-- Lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!-- Validation -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-validation</artifactId>
</dependency>
<!-- Test -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
实体类
package com.example.demo.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import java.time.LocalDateTime;
@Data
@TableName("user_info")
public class User {
@TableId(type = IdType.AUTO)
private Long id;
private String name;
private String username;
private String email;
private String phone;
private Integer age;
private String address;
private String status;
private LocalDateTime createTime;
private LocalDateTime updateTime;
}
Excel导入导出DTO
package com.example.demo.model;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
import javax.validation.constraints.Email;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Pattern;
import java.time.LocalDateTime;
@Data
@ContentRowHeight(20)
@HeadRowHeight(25)
@ColumnWidth(20)
public class UserExcelDTO {
@ExcelProperty(value = "用户ID", index = 0)
private Long id;
@ExcelProperty(value = "姓名", index = 1)
@NotBlank(message = "姓名不能为空")
private String name;
@ExcelProperty(value = "用户名", index = 2)
@NotBlank(message = "用户名不能为空")
private String username;
@ExcelProperty(value = "邮箱", index = 3)
@Email(message = "邮箱格式不正确")
private String email;
@ExcelProperty(value = "手机号", index = 4)
@Pattern(regexp = "^1[3-9]\\d{9}$", message = "手机号格式不正确")
private String phone;
@ExcelProperty(value = "年龄", index = 5)
private Integer age;
@ExcelProperty(value = "地址", index = 6)
private String address;
@ExcelProperty(value = "状态", index = 7)
private String status;
@ExcelProperty(value = "创建时间", index = 8)
private LocalDateTime createTime;
}
Mapper接口
package com.example.demo.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
Service层
package com.example.demo.service;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.example.demo.entity.User;
import com.example.demo.mapper.UserMapper;
import com.example.demo.model.UserExcelDTO;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
@Service
public class UserService {
@Autowired
private UserMapper userMapper;
/**
* 导出用户数据
*/
public void exportUsers(HttpServletResponse response, String name) {
try {
// 设置响应头
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("用户数据_" + System.currentTimeMillis(), "UTF-8");
response.setHeader("Content-disposition",
"attachment;filename=" + fileName + ".xlsx");
// 查询数据
List<User> users = getUserList(name);
// 转换为DTO
List<UserExcelDTO> dataList = new ArrayList<>();
for (User user : users) {
UserExcelDTO dto = new UserExcelDTO();
BeanUtils.copyProperties(user, dto);
dataList.add(dto);
}
// 写入Excel
EasyExcel.write(response.getOutputStream(), UserExcelDTO.class)
.sheet("用户数据")
.doWrite(dataList);
} catch (IOException e) {
throw new RuntimeException("导出Excel失败", e);
}
}
/**
* 导入用户数据
*/
@Transactional(rollbackFor = Exception.class)
public List<UserExcelDTO> importUsers(MultipartFile file) {
List<UserExcelDTO> errorList = new ArrayList<>();
try {
EasyExcel.read(file.getInputStream(), UserExcelDTO.class,
new AnalysisEventListener<UserExcelDTO>() {
private List<UserExcelDTO> batchList = new ArrayList<>();
private static final int BATCH_SIZE = 100;
@Override
public void invoke(UserExcelDTO data, AnalysisContext context) {
// 数据验证
if (validateData(data)) {
batchList.add(data);
if (batchList.size() >= BATCH_SIZE) {
saveBatch(batchList);
batchList.clear();
}
} else {
errorList.add(data);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
if (!batchList.isEmpty()) {
saveBatch(batchList);
batchList.clear();
}
}
}).sheet().doRead();
} catch (IOException e) {
throw new RuntimeException("导入Excel失败", e);
}
return errorList;
}
/**
* 批量保存用户
*/
private void saveBatch(List<UserExcelDTO> dtoList) {
List<User> userList = new ArrayList<>();
LocalDateTime now = LocalDateTime.now();
for (UserExcelDTO dto : dtoList) {
User user = new User();
BeanUtils.copyProperties(dto, user);
// 检查用户是否存在
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(User::getUsername, dto.getUsername());
User existUser = userMapper.selectOne(wrapper);
if (existUser != null) {
user.setId(existUser.getId());
user.setUpdateTime(now);
userMapper.updateById(user);
} else {
user.setCreateTime(now);
user.setUpdateTime(now);
userMapper.insert(user);
}
}
}
/**
* 数据验证
*/
private boolean validateData(UserExcelDTO data) {
// 姓名和用户名不能为空
if (data.getName() == null || data.getName().isEmpty()) {
return false;
}
if (data.getUsername() == null || data.getUsername().isEmpty()) {
return false;
}
// 邮箱格式验证
if (data.getEmail() != null && !data.getEmail().isEmpty()) {
if (!data.getEmail().matches("^[A-Za-z0-9+_.-]+@(.+)$")) {
return false;
}
}
// 手机号格式验证
if (data.getPhone() != null && !data.getPhone().isEmpty()) {
if (!data.getPhone().matches("^1[3-9]\\d{9}$")) {
return false;
}
}
return true;
}
/**
* 查询用户列表
*/
private List<User> getUserList(String name) {
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
if (name != null && !name.isEmpty()) {
wrapper.like(User::getName, name);
}
wrapper.orderByDesc(User::getCreateTime);
return userMapper.selectList(wrapper);
}
}
Controller层
package com.example.demo.controller;
import com.example.demo.model.UserExcelDTO;
import com.example.demo.service.UserService;
import com.example.demo.util.ResponseResult;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
@RestController
@RequestMapping("/api/user")
public class UserController {
@Autowired
private UserService userService;
/**
* 导出用户数据
*/
@GetMapping("/export")
public void exportUsers(HttpServletResponse response,
@RequestParam(required = false) String name) {
userService.exportUsers(response, name);
}
/**
* 导入用户数据
*/
@PostMapping("/import")
public ResponseResult<List<UserExcelDTO>> importUsers(
@RequestParam("file") MultipartFile file) {
if (file.isEmpty()) {
return ResponseResult.error("上传文件不能为空");
}
// 检查文件格式
String filename = file.getOriginalFilename();
if (filename != null && !filename.endsWith(".xlsx") && !filename.endsWith(".xls")) {
return ResponseResult.error("文件格式不正确，请上传Excel文件");
}
try {
List<UserExcelDTO> errorList = userService.importUsers(file);
return ResponseResult.success(errorList);
} catch (Exception e) {
return ResponseResult.error("导入失败：" + e.getMessage());
}
}
/**
* 下载导入模板
*/
@GetMapping("/template")
public void downloadTemplate(HttpServletResponse response) {
userService.downloadTemplate(response);
}
}
通用响应类
package com.example.demo.util;
import lombok.Data;
@Data
public class ResponseResult<T> {
private Integer code;
private String message;
private T data;
public static <T> ResponseResult<T> success(T data) {
ResponseResult<T> result = new ResponseResult<>();
result.setCode(200);
result.setMessage("操作成功");
result.setData(data);
return result;
}
public static <T> ResponseResult<T> success(String message, T data) {
ResponseResult<T> result = new ResponseResult<>();
result.setCode(200);
result.setMessage(message);
result.setData(data);
return result;
}
public static <T> ResponseResult<T> error(String message) {
ResponseResult<T> result = new ResponseResult<>();
result.setCode(500);
result.setMessage(message);
return result;
}
public static <T> ResponseResult<T> error(Integer code, String message) {
ResponseResult<T> result = new ResponseResult<>();
result.setCode(code);
result.setMessage(message);
return result;
}
}
运行配置
# application.yml
server:
port: 8080
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/import_export_demo?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
username: root
password: root
mybatis-plus:
configuration:
map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
global-config:
db-config:
id-type: auto
前端测试页面
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">导入导出测试</title>
<link href="https://cdn.bootcdn.net/ajax/libs/twitter-bootstrap/4.6.2/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container mt-5">
<h2>用户数据导入导出</h2>
<div class="mt-4">
<!-- 导出功能 -->
<div class="card mb-4">
<div class="card-header">
<h5>导出用户数据</h5>
</div>
<div class="card-body">
<div class="form-group">
<label>按姓名筛选（可选）</label>
<input type="text" id="exportName" class="form-control"
style="max-width: 300px;" placeholder="请输入用户姓名">
</div>
<button class="btn btn-primary" onclick="exportUsers()">导出Excel</button>
</div>
</div>
<!-- 导入功能 -->
<div class="card">
<div class="card-header">
<h5>导入用户数据</h5>
</div>
<div class="card-body">
<div class="form-group">
<input type="file" id="importFile" class="form-control-file"
accept=".xlsx,.xls" style="max-width: 300px;">
</div>
<button class="btn btn-success" onclick="importUsers()">导入Excel</button>
<button class="btn btn-info" onclick="downloadTemplate()" style="margin-left: 10px;">
下载模板
</button>
</div>
</div>
<!-- 错误数据显示 -->
<div id="errorInfo" class="mt-4"></div>
</div>
</div>
<script>
// 导出用户数据
function exportUsers() {
const name = document.getElementById('exportName').value;
const params = new URLSearchParams();
if (name) {
params.append('name', name);
}
window.location.href = `/api/user/export?${params.toString()}`;
}
// 导入用户数据
function importUsers() {
const fileInput = document.getElementById('importFile');
const file = fileInput.files[0];
if (!file) {
alert('请选择要导入的Excel文件');
return;
}
const formData = new FormData();
formData.append('file', file);
fetch('/api/user/import', {
method: 'POST',
body: formData
})
.then(response => response.json())
.then(data => {
if (data.code === 200) {
alert('导入成功');
if (data.data && data.data.length > 0) {
displayErrors(data.data);
}
} else {
alert(data.message);
}
})
.catch(error => {
console.error('导入失败:', error);
alert('导入失败');
});
}
// 下载模板
function downloadTemplate() {
window.location.href = '/api/user/template';
}
// 显示错误数据
function displayErrors(errors) {
let html = '<h5>导入失败的数据：</h5>';
html += '<table class="table table-bordered">';
html += '<thead><tr><th>姓名</th><th>用户名</th><th>邮箱</th><th>错误信息</th></tr></thead>';
html += '<tbody>';
errors.forEach(error => {
const errorMsg = validateError(error);
html += `<tr>
<td>${error.name || '-'}</td>
<td>${error.username || '-'}</td>
<td>${error.email || '-'}</td>
<td class="text-danger">${errorMsg}</td>
</tr>`;
});
html += '</tbody></table>';
document.getElementById('errorInfo').innerHTML = html;
}
// 简单验证错误信息
function validateError(data) {
if (!data.name) return '姓名不能为空';
if (!data.username) return '用户名不能为空';
if (data.email && !isEmail(data.email)) return '邮箱格式不正确';
if (data.phone && !isPhone(data.phone)) return '手机号格式不正确';
return '其他错误';
}
function isEmail(email) {
return /^[^@]+@[^@]+\.[^@]+$/.test(email);
}
function isPhone(phone) {
return /^1[3-9]\d{9}$/.test(phone);
}
</script>
</body>
</html>
数据库建表SQL
CREATE DATABASE IF NOT EXISTS import_export_demo DEFAULT CHARACTER SET utf8mb4;
USE import_export_demo;
CREATE TABLE IF NOT EXISTS `user_info` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL COMMENT '姓名',
`username` VARCHAR(50) NOT NULL COMMENT '用户名',
`email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
`phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号',
`age` INT DEFAULT NULL COMMENT '年龄',
`address` VARCHAR(200) DEFAULT NULL COMMENT '地址',
`status` VARCHAR(20) DEFAULT 'active' COMMENT '状态',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_username` (`username`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';
测试用例
package com.example.demo;
import com.example.demo.service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
class ImportExportDemoApplicationTests {
@Autowired
private UserService userService;
@Test
void contextLoads() {
// 测试数据导出逻辑
System.out.println("测试用例执行成功");
}
}
使用说明
-
启动项目：运行
ImportExportDemoApplication主类 -
API测试：
- 导出：
GET /api/user/export - 导入：
POST /api/user/import(上传Excel文件) - 模板下载：
GET /api/user/template
- 导出：
-
Excel文件格式：
- 第一行为列头（自动识别）
- 支持字段：用户ID、姓名、用户名、邮箱、手机号、年龄、地址、状态、创建时间
-
功能特点：
- 支持大数据量分批处理
- 导入时自动去重（按用户名）
- 数据验证和错误反馈
- 支持模板下载
这个案例提供了完整的导入导出功能,您可以根据实际需求进行调整和扩展。
