MySQL 数据库导出 Excel 表的方法详解
在日常开发中，我们经常需要将数据库中的数据导出为 Excel 文件，以便进行数据分析或分享给其他同事。本文将详细介绍如何从 MySQL 数据库导出数据并生成 Excel 文件，具体实现将基于 Java 语言和 Spring Boot 框架。
环境准备
在开始之前，请确保你已经安装了以下工具和库：
- Java Development Kit (JDK): 用于编写和运行 Java 应用程序。
- Spring Boot: 一个快速构建独立的、生产级别的基于 Spring 框架的应用程序的框架。
- Apache POI: 一个用于读取和写入 Microsoft Office 格式文件的 Java 库，特别适合处理 Excel 文件。
- MySQL: 一个广泛使用的开源关系型数据库管理系统。
项目结构
假设我们的项目结构如下：
productmanage
├── src
│ ├── main
│ │ ├── java
│ │ │ └── com
│ │ │ └── ts
│ │ │ ├── controller
│ │ │ │ └── ProductAdInfoController.java
│ │ │ ├── service
│ │ │ │ └── ProductService.java
│ │ │ └── utils
│ │ │ └── ExcelUtils.java
│ │ └── resources
│ │ └── application.properties
└── pom.xml
依赖配置
在 pom.xml 文件中添加 Apache POI 的依赖：
<dependencies><!-- 其他依赖 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency>
</dependencies>
服务层实现
在 ProductService 中定义一个方法来查询数据库中的活动广告数据：
package com.ts.service;import com.ts.dto.ProductAdInfoDTO;
import java.util.List;public interface ProductService {List<ProductAdInfoDTO> listActiveProductAdInfos();
}
实用工具类
创建一个 ExcelUtils 类来处理 Excel 文件的创建和写入操作：
package com.ts.utils;import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;public class ExcelUtils {public static void createTableHeader(Sheet sheet, int rowIndex, String[] headers) {Row row = sheet.createRow(rowIndex);CellStyle style = sheet.getWorkbook().createCellStyle();style.setAlignment(HorizontalAlignment.CENTER);for (int i = 0; i < headers.length; i++) {Cell cell = row.createCell(i);cell.setCellValue(headers[i]);cell.setCellStyle(style);}}public static void setCell(Row row, int cellIndex, String value) {Cell cell = row.createCell(cellIndex);cell.setCellValue(value);}public static void setResHeader(String fileName, HttpServletResponse response) throws IOException {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");String encodedFileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-Disposition", "attachment;filename=" + encodedFileName + ".xlsx");}public static void returnWorkbook(Workbook workbook, HttpServletResponse response) throws IOException {workbook.write(response.getOutputStream());workbook.close();}
}
控制器实现
在 ProductAdInfoController 中实现导出 Excel 的接口：
package com.ts.controller;import com.ts.dto.ProductAdInfoDTO;
import com.ts.service.ProductService;
import com.ts.utils.ExcelUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletResponse;
import java.util.List;@RestController
public class ProductAdInfoController {@Autowiredprivate ProductService service;@GetMapping("file-export")public void exportExcels(HttpServletResponse resp) {Workbook wb = new SXSSFWorkbook();CellStyle style = wb.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER);Sheet sheet = wb.createSheet("数据");int index = 0;// 创建表头ExcelUtils.createTableHeader(sheet, index++, new String[]{"AID", "BID", "PName"});// 查询数据List<ProductAdInfoDTO> productAdInfos = service.listActiveProductAdInfos();// 写入数据for (ProductAdInfoDTO adInfoDTO : productAdInfos) {Row row = sheet.createRow(index++);int i = 0;ExcelUtils.setCell(row, i++, String.valueOf(adInfoDTO.getFacebookId()));ExcelUtils.setCell(row, i++, String.valueOf(adInfoDTO.getAccountId()));ExcelUtils.setCell(row, i++, String.valueOf(adInfoDTO.getPid()));}// 设置响应头并返回工作簿try {ExcelUtils.setResHeader("数据", resp);ExcelUtils.returnWorkbook(wb, resp);} catch (IOException e) {e.printStackTrace();}}
}
配置文件
在 application.properties 文件中配置数据库连接信息：
spring.datasource.url=jdbc:mysql://localhost:3306/your_database
spring.datasource.username=your_username
spring.datasource.password=your_password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
测试
启动应用后，访问 /file-export 接口，即可下载包含广告数据的 Excel 文件。
总结
通过本文的介绍，你应该能够轻松地将 MySQL 数据库中的数据导出为 Excel 文件。主要步骤包括：
- 添加必要的依赖。
- 创建服务层方法查询数据。
- 编写实用工具类处理 Excel 文件的创建和写入。
- 在控制器中实现导出接口。
- 配置数据库连接信息。
希望本文对你有所帮助！如果有任何问题或建议，欢迎留言交流。