Javajava
Spring Boot 数据导入导出与报表生成
在 Spring Boot 中实现数据导入导出与报表生成的方法。主要涵盖使用 Apache POI 处理 Excel 文件的导入导出，以及利用 JasperReports 生成 PDF 报表。内容包括项目依赖配置、实体类与 Repository 定义、Service 层业务逻辑实现、Controller 接口暴露及测试验证。通过具体示例展示了如何集成第三方库完成文件操作与报表输出，适用于产品信息、用户信息及订单数据的处理场景。
在 Spring Boot 中实现数据导入导出与报表生成的方法。主要涵盖使用 Apache POI 处理 Excel 文件的导入导出，以及利用 JasperReports 生成 PDF 报表。内容包括项目依赖配置、实体类与 Repository 定义、Service 层业务逻辑实现、Controller 接口暴露及测试验证。通过具体示例展示了如何集成第三方库完成文件操作与报表输出，适用于产品信息、用户信息及订单数据的处理场景。
学习目标：掌握 Spring Boot 数据导入导出与报表生成的核心概念与使用方法，包括数据导入导出的定义与特点、Spring Boot 与数据导入导出的集成、配置及报表生成的基本方法。
重点：数据导入导出的定义与特点、Spring Boot 与数据导入导出的集成、配置、报表生成的基本方法、实际应用场景。
数据导入导出是 Java 开发中的重要组件。
定义：将数据从一个系统导入到另一个系统，或从一个系统导出到另一个系统的过程。
作用：
常见的数据导入导出格式：
特点：
示例： pom.xml 文件中的依赖：
<dependencies>
<!-- Web 依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- Data JPA 依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- H2 数据库依赖 -->
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<!-- Apache POI 依赖 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!-- 测试依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
application.properties 文件中的配置：
# 服务器端口
server.port=8080
# 数据库连接信息
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
# JPA 配置
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
# H2 数据库控制台
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console
实体类：
import javax.persistence.*;
@Entity
@Table(name = "product")
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String productId;
private String productName;
private double price;
private int sales;
public Product() {}
public Product(String productId, String productName, double price, int sales) {
this.productId = productId;
this.productName = productName;
this.price = price;
this.sales = sales;
}
// Getter 和 Setter 方法
public Long getId() { return id; }
public void setId(Long id) { this.id = id; }
public String getProductId() { return productId; }
public void setProductId(String productId) { this.productId = productId; }
public String getProductName() { return productName; }
public void setProductName(String productName) { this.productName = productName; }
public double getPrice() { return price; }
public void setPrice(double price) { this.price = price; }
public int getSales() { return sales; }
public void setSales(int sales) { this.sales = sales; }
@Override
public String toString() {
return "Product{" +
"id=" + id +
", productId='" + productId + '\'' +
", productName='" + productName + '\'' +
", price=" + price +
", sales=" + sales +
'}';
}
}
Repository 接口：
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {}
Service 类：
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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 java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
@Service
public class ProductService {
@Autowired
private ProductRepository productRepository;
@Transactional
public void importProducts(MultipartFile file) throws IOException {
List<Product> products = new ArrayList<>();
Workbook workbook = new XSSFWorkbook(file.getInputStream());
Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> iterator = sheet.iterator();
if (iterator.hasNext()) {
iterator.next(); // 跳过标题行
}
while (iterator.hasNext()) {
Row currentRow = iterator.next();
Product product = new Product();
product.setProductId(currentRow.getCell(0).getStringCellValue());
product.setProductName(currentRow.getCell(1).getStringCellValue());
product.setPrice(currentRow.getCell(2).getNumericCellValue());
product.setSales((int) currentRow.getCell(3).getNumericCellValue());
products.add(product);
}
productRepository.saveAll(products);
workbook.close();
}
@Transactional(readOnly = true)
public byte[] exportProducts() throws IOException {
List<Product> products = productRepository.findAll();
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Products");
Row headerRow = sheet.createRow(0);
Cell headerCell0 = headerRow.createCell(0); headerCell0.setCellValue("Product ID");
Cell headerCell1 = headerRow.createCell(1); headerCell1.setCellValue("Product Name");
Cell headerCell2 = headerRow.createCell(2); headerCell2.setCellValue("Price");
Cell headerCell3 = headerRow.createCell(3); headerCell3.setCellValue("Sales");
int rowNum = 1;
for (Product product : products) {
Row row = sheet.createRow(rowNum++);
row.createCell(0).setCellValue(product.getProductId());
row.createCell(1).setCellValue(product.getProductName());
row.createCell(2).setCellValue(product.getPrice());
row.createCell(3).setCellValue(product.getSales());
}
for (int i = 0; i < 4; i++) {
sheet.autoSizeColumn(i);
}
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workbook.write(outputStream);
workbook.close();
return outputStream.toByteArray();
}
@Transactional
public Product addProduct(Product product) {
return productRepository.save(product);
}
@Transactional(readOnly = true)
public List<Product> getAllProducts() {
return productRepository.findAll();
}
}
控制器类：
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.util.List;
@RestController
@RequestMapping("/api/products")
public class ProductController {
@Autowired
private ProductService productService;
@PostMapping("/import")
public ResponseEntity<String> importProducts(@RequestParam("file") MultipartFile file) {
try {
productService.importProducts(file);
return ResponseEntity.ok("数据导入成功");
} catch (IOException e) {
e.printStackTrace();
return ResponseEntity.status(500).body("数据导入失败：" + e.getMessage());
}
}
@GetMapping("/export")
public ResponseEntity<byte[]> exportProducts() {
try {
byte[] bytes = productService.exportProducts();
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
headers.setContentDispositionFormData("attachment", "products.xlsx");
headers.setCacheControl("must-revalidate, post-check=0, pre-check=0");
return ResponseEntity.ok().headers(headers).body(bytes);
} catch (IOException e) {
e.printStackTrace();
return ResponseEntity.status(500).body(null);
}
}
@PostMapping("/")
public Product addProduct(@RequestBody Product product) {
return productService.addProduct(product);
}
@GetMapping("/")
public List<Product> getAllProducts() {
return productService.getAllProducts();
}
}
应用启动类：
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class ProductApplication {
public static void main(String[] args) {
SpringApplication.run(ProductApplication.class, args);
}
@Autowired
private ProductService productService;
public void run(String... args) {
productService.addProduct(new Product("P001", "手机", 1000.0, 100));
productService.addProduct(new Product("P002", "电脑", 5000.0, 50));
productService.addProduct(new Product("P003", "电视", 3000.0, 80));
productService.addProduct(new Product("P004", "手表", 500.0, 200));
productService.addProduct(new Product("P005", "耳机", 300.0, 150));
}
}
测试类：
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.web.client.TestRestTemplate;
import org.springframework.boot.web.server.LocalServerPort;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpMethod;
import org.springframework.http.ResponseEntity;
import org.springframework.util.LinkedMultiValueMap;
import org.springframework.util.MultiValueMap;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import static org.assertj.core.api.Assertions.assertThat;
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
class ProductApplicationTests {
@LocalServerPort
private int port;
@Autowired
private TestRestTemplate restTemplate;
@Test
void contextLoads() {}
@Test
void testAddProduct() {
Product product = new Product("P006", "平板", 2000.0, 70);
Product savedProduct = restTemplate.postForObject("http://localhost:" + port + "/api/products/", product, Product.class);
assertThat(savedProduct).isNotNull();
assertThat(savedProduct.getProductId()).isEqualTo("P006");
}
@Test
void testGetAllProducts() {
List<Product> products = restTemplate.getForObject("http://localhost:" + port + "/api/products/", List.class);
assertThat(products).isNotNull();
assertThat(products.size()).isGreaterThanOrEqualTo(5);
}
@Test
void testImportProducts() throws IOException {
File file = new File("src/test/resources/products.xlsx");
InputStream inputStream = new FileInputStream(file);
MultipartFile multipartFile = new CommonsMultipartFile(inputStream);
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.MULTIPART_FORM_DATA);
MultiValueMap<String, Object> body = new LinkedMultiValueMap<>();
body.add("file", multipartFile);
HttpEntity<MultiValueMap<String, Object>> requestEntity = new HttpEntity<>(body, headers);
ResponseEntity<String> response = restTemplate.exchange("http://localhost:" + port + "/api/products/import", HttpMethod.POST, requestEntity, String.class);
assertThat(response.getStatusCodeValue()).isEqualTo(200);
assertThat(response.getBody()).contains("数据导入成功");
}
@Test
void testExportProducts() {
ResponseEntity<byte[]> response = restTemplate.getForEntity("http://localhost:" + port + "/api/products/export", byte[].class);
assertThat(response.getStatusCodeValue()).isEqualTo(200);
assertThat(response.getBody()).isNotNull();
assertThat(response.getBody().length).isGreaterThan(0);
}
}
Spring Boot 与报表生成的基本方法包括使用 Apache POI、JasperReports、Excel 模板。
作用：实现报表生成，提高应用程序的性能。
示例： pom.xml 文件中的依赖：
<dependencies>
<!-- Web 依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- Data JPA 依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- H2 数据库依赖 -->
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<!-- JasperReports 依赖 -->
<dependency>
<groupId>net.sf.jasperreports</groupId>
<artifactId>jasperreports</artifactId>
<version>6.17.0</version>
</dependency>
<dependency>
<groupId>net.sf.jasperreports</groupId>
<artifactId>jasperreports-fonts</artifactId>
<version>6.17.0</version>
</dependency>
<!-- 测试依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
Service 类：
import net.sf.jasperreports.engine.*;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Service
public class ReportService {
@Autowired
private ProductRepository productRepository;
@Transactional(readOnly = true)
public byte[] generateProductReport() throws JRException {
List<Product> products = productRepository.findAll();
InputStream templateStream = getClass().getResourceAsStream("/reports/product-report.jasper");
JasperReport jasperReport = (JasperReport) JRLoader.loadObject(templateStream);
Map<String, Object> parameters = new HashMap<>();
parameters.put("Title", "产品销售报表");
JRBeanCollectionDataSource dataSource = new JRBeanCollectionDataSource(products);
JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, dataSource);
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
JasperExportManager.exportReportToPdfStream(jasperPrint, outputStream);
return outputStream.toByteArray();
}
}
控制器类：
import net.sf.jasperreports.engine.JRException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import java.io.IOException;
import java.util.List;
@RestController
@RequestMapping("/api/products")
public class ProductController {
@Autowired
private ProductService productService;
@Autowired
private ReportService reportService;
@GetMapping("/report")
public ResponseEntity<byte[]> generateProductReport() {
try {
byte[] bytes = reportService.generateProductReport();
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.parseMediaType("application/pdf"));
headers.setContentDispositionFormData("attachment", "product-report.pdf");
headers.setCacheControl("must-revalidate, post-check=0, pre-check=0");
return ResponseEntity.ok().headers(headers).body(bytes);
} catch (JRException e) {
e.printStackTrace();
return ResponseEntity.status(500).body(null);
}
}
@PostMapping("/import")
public ResponseEntity<String> importProducts(@RequestParam("file") MultipartFile file) {
try {
productService.importProducts(file);
return ResponseEntity.ok("数据导入成功");
} catch (IOException e) {
e.printStackTrace();
return ResponseEntity.status(500).body("数据导入失败：" + e.getMessage());
}
}
@GetMapping("/export")
public ResponseEntity<byte[]> exportProducts() {
try {
byte[] bytes = productService.exportProducts();
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
headers.setContentDispositionFormData("attachment", "products.xlsx");
headers.setCacheControl("must-revalidate, post-check=0, pre-check=0");
return ResponseEntity.ok().headers(headers).body(bytes);
} catch (IOException e) {
e.printStackTrace();
return ResponseEntity.status(500).body(null);
}
}
@PostMapping("/")
public Product addProduct(@RequestBody Product product) {
return productService.addProduct(product);
}
@GetMapping("/")
public List<Product> getAllProducts() {
return productService.getAllProducts();
}
}
测试类：
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.web.client.TestRestTemplate;
import org.springframework.boot.web.server.LocalServerPort;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpMethod;
import org.springframework.http.ResponseEntity;
import org.springframework.util.LinkedMultiValueMap;
import org.springframework.util.MultiValueMap;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import static org.assertj.core.api.Assertions.assertThat;
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
class ProductApplicationTests {
@LocalServerPort
private int port;
@Autowired
private TestRestTemplate restTemplate;
@Test
void contextLoads() {}
@Test
void testAddProduct() {
Product product = new Product("P006", "平板", 2000.0, 70);
Product savedProduct = restTemplate.postForObject("http://localhost:" + port + "/api/products/", product, Product.class);
assertThat(savedProduct).isNotNull();
assertThat(savedProduct.getProductId()).isEqualTo("P006");
}
@Test
void testGetAllProducts() {
List<Product> products = restTemplate.getForObject("http://localhost:" + port + "/api/products/", List.class);
assertThat(products).isNotNull();
assertThat(products.size()).isGreaterThanOrEqualTo(5);
}
@Test
void testImportProducts() throws IOException {
File file = new File("src/test/resources/products.xlsx");
InputStream inputStream = new FileInputStream(file);
MultipartFile multipartFile = new CommonsMultipartFile(inputStream);
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.MULTIPART_FORM_DATA);
MultiValueMap<String, Object> body = new LinkedMultiValueMap<>();
body.add("file", multipartFile);
HttpEntity<MultiValueMap<String, Object>> requestEntity = new HttpEntity<>(body, headers);
ResponseEntity<String> response = restTemplate.exchange("http://localhost:" + port + "/api/products/import", HttpMethod.POST, requestEntity, String.class);
assertThat(response.getStatusCodeValue()).isEqualTo(200);
assertThat(response.getBody()).contains("数据导入成功");
}
@Test
void testExportProducts() {
ResponseEntity<byte[]> response = restTemplate.getForEntity("http://localhost:" + port + "/api/products/export", byte[].class);
assertThat(response.getStatusCodeValue()).isEqualTo(200);
assertThat(response.getBody()).isNotNull();
assertThat(response.getBody().length).isGreaterThan(0);
}
@Test
void testGenerateProductReport() {
ResponseEntity<byte[]> response = restTemplate.getForEntity("http://localhost:" + port + "/api/products/report", byte[].class);
assertThat(response.getStatusCodeValue()).isEqualTo(200);
assertThat(response.getBody()).isNotNull();
assertThat(response.getBody().length).isGreaterThan(0);
}
}
在实际开发中，Spring Boot 数据导入导出与报表生成的应用场景非常广泛，如：
输出结果：
本章学习了 Spring Boot 数据导入导出与报表生成，包括数据导入导出的定义与特点、集成、配置、报表生成的基本方法及实际应用场景。其中，数据导入导出的定义与特点、集成、配置、报表生成的基本方法、实际应用场景是本章的重点内容。从下一章开始，将学习 Spring Boot 的其他组件、微服务等内容。
微信公众号「极客日志」，在微信中扫描左侧二维码关注。展示文案：极客日志 zeeklog
查找任何按下的键的javascript键代码、代码、位置和修饰符。 在线工具，Keycode 信息在线工具，online
JavaScript 字符串转义/反转义；Java 风格 \uXXXX（Native2Ascii）编码与解码。 在线工具，Escape 与 Native 编解码在线工具，online
使用 Prettier 在浏览器内格式化 JavaScript 或 HTML 片段。 在线工具，JavaScript / HTML 格式化在线工具，online
Terser 压缩、变量名混淆，或 javascript-obfuscator 高强度混淆（体积会增大）。 在线工具，JavaScript 压缩与混淆在线工具，online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具，Base64 字符串编码/解码在线工具，online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具，Base64 文件转换器在线工具，online