java查询数据库结果集动态合并excel
时间: 2023-09-17 08:14:56 浏览: 56
您可以使用Apache POI库来将结果集合并到Excel中。以下是一个示例代码,可以将查询结果集动态添加到Excel中:
```java
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
public class ExcelGenerator {
public static void main(String[] args) throws Exception {
//Connect to database
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "myuser", "mypassword");
Statement stmt = conn.createStatement();
//Execute query
String query = "SELECT * FROM mytable";
ResultSet rs = stmt.executeQuery(query);
//Create workbook and sheet
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Result Set");
//Create header row
HSSFRow header = sheet.createRow(0);
for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
HSSFCell cell = header.createCell(i - 1);
cell.setCellValue(rs.getMetaData().getColumnName(i));
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setBold(true);
style.setFont(font);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell.setCellStyle(style);
}
//Create data rows
int rowCount = 1;
while (rs.next()) {
HSSFRow row = sheet.createRow(rowCount++);
for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
HSSFCell cell = row.createCell(i - 1);
cell.setCellValue(rs.getString(i));
}
}
//Auto-size columns
for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
sheet.autoSizeColumn(i);
}
//Save workbook to file
FileOutputStream fos = new FileOutputStream("resultset.xls");
workbook.write(fos);
fos.close();
//Close connections
rs.close();
stmt.close();
conn.close();
}
}
```
这个示例代码将查询结果集动态添加到Excel中,并使用Apache POI库进行格式化和保存。您可以根据自己的需求进行修改。