SpringBoot项目Mysql查询结果导出Excel文件
时间: 2024-03-05 12:50:12 浏览: 19
可以使用 Apache POI 库实现将 MySQL 查询结果导出为 Excel 文件。以下是一个简单的示例代码,可以将查询结果导出为 Excel 文件:
```java
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;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExportExcel {
public static void main(String[] args) {
String jdbcUrl = "jdbc:mysql://localhost:3306/test?useSSL=false";
String username = "root";
String password = "root";
String query = "SELECT * FROM users";
String fileName = "users.xlsx";
try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
PreparedStatement statement = connection.prepareStatement(query);
ResultSet resultSet = statement.executeQuery();
Workbook workbook = new XSSFWorkbook();
FileOutputStream outputStream = new FileOutputStream(fileName)) {
Sheet sheet = workbook.createSheet("Users");
Row headerRow = sheet.createRow(0);
// Create header cells
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
Cell cell = headerRow.createCell(i - 1);
cell.setCellValue(metaData.getColumnName(i));
}
// Create data rows
int rowNum = 1;
while (resultSet.next()) {
Row row = sheet.createRow(rowNum++);
for (int i = 1; i <= columnCount; i++) {
Cell cell = row.createCell(i - 1);
cell.setCellValue(resultSet.getString(i));
}
}
// Resize columns to fit content
for (int i = 0; i < columnCount; i++) {
sheet.autoSizeColumn(i);
}
// Write workbook to output stream
workbook.write(outputStream);
} catch (SQLException | IOException e) {
e.printStackTrace();
}
}
}
```
这里使用了 Apache POI 的 XSSFWorkbook 类来创建 Excel 文件,并使用 FileOutputStream 将文件写入磁盘。该示例代码将查询结果导出为一个名为 "users.xlsx" 的 Excel 文件。你可以根据实际需求修改代码。