Spring Boot 数据导入导出与报表生成实战

配置文件 application.properties 中设置 H2 内存数据库及 JPA 相关参数：

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
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

2. 实体层设计

定义一个 Product 实体类，对应数据库中的产品表。注意使用 JPA 注解映射字段。

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; }
}

配合标准的 Repository 接口：

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {}

3. 业务逻辑实现

Service 层是核心，负责处理文件流与数据库的交互。这里以导入 Excel 为例，利用 XSSFWorkbook 读取单元格数据。

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();
    }
}

4. 控制器暴露接口

Controller 层负责接收 HTTP 请求并返回响应。导入接口需处理 MultipartFile，导出接口则返回二进制流。

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();
    }
}

二、报表生成方案

除了简单的 Excel 导出，企业往往需要更复杂的报表格式，例如 PDF 格式的月度销售报告。JasperReports 是业界成熟的报表引擎，支持模板化设计。

1. 集成 JasperReports

同样需要在 pom.xml 中添加 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>

2. 报表服务实现

Service 层加载预编译的 .jasper 模板文件，填充数据源后导出为 PDF。

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();
        
        // 加载 Jasper 模板
        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);
        
        // 导出为 PDF
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        JasperExportManager.exportReportToPdfStream(jasperPrint, outputStream);
        return outputStream.toByteArray();
    }
}

3. 报表接口

在 Controller 中增加报表下载接口，注意设置正确的 Content-Type。

// 在 ProductController 中注入 ReportService
@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);
    }
}

三、实际应用场景

在实际项目中，这套模式可以灵活复用：

• 产品信息管理：批量导入商品清单，定期导出库存报表。
• 用户信息迁移：旧系统数据清洗后导入新平台。
• 订单统计：生成财务对账单或销售趋势图。

通过上述代码结构，你可以快速搭建起一套完整的数据交互模块。访问 http://localhost:8080/api/products/export 即可下载 Excel，访问 /api/products/report 则获取 PDF 报表。

四、总结

本章我们深入探讨了 Spring Boot 中的数据导入导出与报表生成。核心要点包括：

1. 依赖选型：Apache POI 适合 Excel 操作，JasperReports 适合复杂报表。
2. 架构分层：Controller 处理 IO，Service 处理业务逻辑，Repository 负责持久化。
3. 流式处理：注意关闭 Workbook 资源，避免内存溢出。

掌握这些技术点后，你就能从容应对大多数企业级数据交换需求了。