在Spring Boot中,我们可以使用Apache POI库来实现将数据库表中的数据导出为Excel表格。可以根据条件从数据库中查询数据并将其导出为Excel:如下
准备工作:首先,确保在你的项目中引入Apache POI依赖。在pom.xml文件中添加以下依赖项:
<dependencies>
...
<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>
...
</dependencies>
创建一个Java类,例如"ExcelExporter.java",并编写导出方法:
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class ExcelExporter {
public static void exportDataToExcel(String condition) {
String url = "jdbc:mysql://your_database_host:port/database_name";
String username = "your_username";
String password = "your_password";
try (Connection conn = DriverManager.getConnection(url, username, password)) {
String sql = "SELECT * FROM your_table WHERE " + condition;
PreparedStatement statement = conn.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();
Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet("Sheet1");
int rowCount = 0;
while (resultSet.next()) {
Row row = sheet.createRow(rowCount);
for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
Cell cell = row.createCell(i - 1);
cell.setCellValue(resultSet.getString(i));
}
rowCount++;
}
FileOutputStream outputStream = new FileOutputStream("output.xlsx");
workbook.write(outputStream);
workbook.close();
outputStream.close();
System.out.println("Excel exported successfully!");
} catch (SQLException | IOException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
exportDataToExcel("your_condition");
}
}
备注:
在上面的代码中,你需要替换以下占位符:
"your_database_host:port" - 数据库主机和端口号
"database_name" - 数据库名称
"your_username" - 数据库用户名
"your_password" - 数据库密码
"your_table" - 要导出数据的表名
"condition" - 查询数据的条件
运行"ExcelExporter.java"类的 main方法,它将执行导出操作并生成一个名为"output.xlsx"的Excel文件。