java条件查询excel_【Java EE 学习 17 下】【数据库导出到Excel】【多条件查询方法】...

packageday17.kdyzm.exportToExcel;importjava.io.FileOutputStream;importjava.sql.Connection;importjava.sql.DatabaseMetaData;importjava.sql.ResultSet;importjava.sql.ResultSetMetaData;importjava.sql.SQLException;importjava.sql.Statement;importjava.util.ArrayList;importjava.util.List;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importday17.regular.utils.DataSourceUtils_C3P0;/*** 将数据库中的表数据导出到Excel表格中

*@authorkdyzm

**/

public classExportDataToExcel {private static String dbname="bookstore";public static void main(String[] args) throwsException {

Listtablenames=getAllTableNames(dbname);

backupToXls(tablenames);

}//通过所有的表名将数据被分到xls文件中

private static void backupToXls(List tablenames) throwsException {

Connection conn=DataSourceUtils_C3P0.getConnection();

Workbook wb=newHSSFWorkbook();

FileOutputStream fos=new FileOutputStream(dbname+".xls");

Statement st=conn.createStatement();for(String tablename:tablenames)

{

Sheet sheet=wb.createSheet(tablename);

String sql="select * from "+dbname+"."+tablename;

ResultSet rs=st.executeQuery(sql);

ResultSetMetaData rsmt=rs.getMetaData();int columns=rsmt.getColumnCount();//写入第一行tablehead

Row tablehead=sheet.createRow(0);for(int i=0;i

{

String columnName=rsmt.getColumnName(i+1);

Cell cell=tablehead.createCell(i);

cell.setCellValue(columnName);

}//写入数据

int index=1;while(rs.next())

{

Row row=sheet.createRow(index++);for(int i=0;i

{

String columnName=rsmt.getColumnName(i+1);

String value=rs.getString(columnName);

Cell cell=row.createCell(i);

cell.setCellValue(value);

}

}

}

wb.write(fos);

wb.close();

fos.close();

conn.close();

}//首先获得所有的表名列表

private static List getAllTableNames(String dbname) throwsSQLException {

Connection conn=DataSourceUtils_C3P0.getConnection();

DatabaseMetaData dmd=conn.getMetaData();

ResultSet rs=dmd.getTables(dbname, dbname, null, new String[]{"TABLE"});

Listtablenames=new ArrayList();while(rs.next())

{

tablenames.add(rs.getString("TABLE_NAME"));

}

conn.close();returntablenames;

}

}