poi(1)读写excel小例子

从databse写入到excel中

/*  * Created on 2006-8-28  *  * XXX To change the template for this generated file go to  * Window - Preferences - Java - Code Style - Code Templates  */ package pois; import java.io.FileOutputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; /**  * @author jacky  *  * XXX To change the template for this generated type comment go to  * Window - Preferences - Java - Code Style - Code Templates  */ public class DbtoExcel {     public static void main(String[] args) throws  Exception     {       String filename = "c://dept.xls";       String column_name="";       PreparedStatement ps=null;  //预编译       Connection conn=null;      //连接       Statement smt=null;         //sql语句       ResultSet rs = null,rs1=null;        //结果集合       ResultSetMetaData md = null;               long rowCount =0l;               //总行数        try{              Class.forName("net.sourceforge.jtds.jdbc.Driver");        }catch(Exception e){         e.printStackTrace();        }        String url = "jdbc:jtds:sqlserver://10.1.0.6:1433;DatabaseName=szPayroll";        String userName = "sa";        String password = "sql";        conn = DriverManager.getConnection (url, userName, password);        smt = conn.createStatement();               //取得总记录数        String sql="select * from department";        String sqlqeury="select count(*) as total from department";        try{             rs = smt.executeQuery(sqlqeury);             if(rs.next()){                 rowCount =rs.getLong("total");                 System.out.println(rowCount) ; //记录总数              }          }catch(Exception e){           System.out.print("aaa");                 e.printStackTrace();                 System.out.println(e.getMessage());                 conn.close();   //异常后关闭连接                 throw new Exception("获得记录总数失败");           }                 //处理sql语句         try{              rs1 = smt.executeQuery(sql);              md = rs1.getMetaData();               System.out.println(md.getColumnCount());          }catch(Exception e){            System.out.print("bbb");                e.printStackTrace();                System.out.println(e.getMessage());                conn.close();   //异常后关闭连接                throw new Exception("传入的SQL无法处理");         }                        //生成excel代码        HSSFWorkbook wb = new HSSFWorkbook();        HSSFSheet sheet = wb.createSheet("dbCreate");        HSSFRow row = null;        HSSFCell cell=null;        for(long i =0;i<=rowCount;i++){                       row = sheet.createRow((short)i);                     //创建一个行            for(int j =0;j<md.getColumnCount();j++){                           cell = row.createCell((short)j);                  //创建单元格               cell.setEncoding(HSSFCell.ENCODING_UTF_16);               if(i==0){                     cell.setCellValue(md.getColumnName(j+1));     //设定单元格的值               }else{                  cell.setCellValue(rs1.getString(j+1));        //设定单元格的值                }             }             rs1.next();         }        //写到excel中!        FileOutputStream fileOut = new FileOutputStream(filename);        wb.write(fileOut);          //向fileout文件写        fileOut.close();            //关闭文件输出流        conn.close();               //关闭正常连接     } }