【poi】sqlserver 数据写入到 excel

来源：互联网发布：孕36周b超正常数据编辑：程序博客网时间：2024/06/08 04:52

使用 c3p0获取 DataSource

public class DataSourceUtils {private static DataSource ds;static{//默认的读取c3p0-config.xml中默认配置ds = new ComboPooledDataSource();}public static DataSource getDatasSource(){return ds;}public static Connection getConn(){Connection con = null;try{con = ds.getConnection();//每一次从ds中获取一个新的连接}catch(Exception e){e.printStackTrace();}return con;}}

使用 poi 将数据写入到excel中

public void createXls() throws Exception{//声明一个工作薄HSSFWorkbook wb = new HSSFWorkbook();   //声明表HSSFSheet sheet = wb.createSheet("第一个表");//声明行HSSFRow row = sheet.createRow(7);//声明列HSSFCell cel = row.createCell(3);//写入数据cel.setCellValue("你也好");FileOutputStream fileOut = new FileOutputStream("d:/a/b.xls");    wb.write(fileOut);        fileOut.close();}@Testpublic void export() throws Exception{//声明需要导出的数据库String dbName = "contact";//声明bookHSSFWorkbook book = new HSSFWorkbook();//获取Connection,获取db的元数据Connection con = DataSourceUtils.getConn();//声明statemenStatement st = con.createStatement();//st.execute("use "+dbName);DatabaseMetaData dmd = con.getMetaData();//获取数据库有多少表ResultSet rs = dmd.getTables(null,null,null,new String[]{"TABLE"});//获取所有表名　－　就是一个sheetList<String> tables = new ArrayList<String>();while(rs.next()){String tableName = rs.getString("TABLE_NAME");System.out.println("**********" + tableName);tables.add(tableName);}for(String tableName:tables){HSSFSheet sheet = book.createSheet(tableName);//声明sqlString sql = "SELECT * FROM "+tableName;//查询数据rs = st.executeQuery(sql);//根据查询的结果，分析结果集的元数据ResultSetMetaData rsmd = rs.getMetaData();//获取这个查询有多少行int cols = rsmd.getColumnCount();//获取所有列名//创建第一行HSSFRow row = sheet.createRow(0);for(int i=0;i<cols;i++){String colName = rsmd.getColumnName(i+1);//创建一个新的列HSSFCell cell = row.createCell(i);//写入列名cell.setCellValue(colName);}//遍历数据int index = 1;while(rs.next()){row = sheet.createRow(index++);//声明列for(int i=0;i<cols;i++){String val = rs.getString(i+1);//声明列HSSFCell cel = row.createCell(i);//放数据cel.setCellValue(val);}}}con.close();book.write(new FileOutputStream("d:/a/"+dbName+".xls"));}

0 0