以下是一个完整的示例,包括查询数据库并将结果导出为Excel文件。
首先,确保你的`pom.xml`中有以下依赖:
```xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
```
这里假设我们使用H2数据库进行演示。首先,定义一个实体类和对应的JPA仓库接口:
### 实体类
```java
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
@Entity
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private Integer age;
private String email;
// Getters and setters
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
```
### 仓库接口
```java
import org.springframework.data.jpa.repository.JpaRepository;
public interface UserRepository extends JpaRepository<User, Long> {
List<User> findByNameContaining(String name);
}
```
接下来,创建一个服务类来处理业务逻辑:
### 服务类
```java
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserService {
@Autowired
private UserRepository userRepository;
public List<User> searchUsers(String name) {
return userRepository.findByNameContaining(name);
}
}
```
然后,修改控制器类以根据前台传来的数据查询数据库并导出结果:
### 控制器类
```java
import com.alibaba.excel.EasyExcel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.InputStreamResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;
@RestController
@RequestMapping("/api/excel")
public class ExcelController {
@Autowired
private UserService userService;
@GetMapping("/export")
public ResponseEntity<InputStreamResource> exportData(@RequestParam String name) throws IOException {
// 根据名字查询用户
List<User> users = userService.searchUsers(name);
// 创建一个输出流,用于写入Excel数据
ByteArrayOutputStream out = new ByteArrayOutputStream();
// 使用EasyExcel将数据写入输出流
EasyExcel.write(out, User.class).sheet("Sheet1").doWrite(users);
// 将输出流转换为输入流资源
ByteArrayInputStream in = new ByteArrayInputStream(out.toByteArray());
// 设置响应头信息
HttpHeaders headers = new HttpHeaders();
headers.add("Content-Disposition", "attachment; filename=data.xlsx");
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
// 返回包含Excel文件的响应实体
return ResponseEntity.ok()
.headers(headers)
.body(new InputStreamResource(in));
}
}
```
### 配置文件
确保你有一个`application.properties`文件来配置数据库连接:
```properties
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true
```
### 测试数据
你可以在`data.sql`文件中插入一些测试数据:
```sql
INSERT INTO USER (name, age, email) VALUES ('Alice', 25, 'alice@example.com');
INSERT INTO USER (name, age, email) VALUES ('Bob', 30, 'bob@example.com');
INSERT INTO USER (name, age, email) VALUES ('Charlie', 35, 'charlie@example.com');
```
通过这个设置,你可以启动Spring Boot应用程序,并访问`/api/excel/export?name=A`来下载包含名字中带有"A"的用户的Excel文件。