java 写excel

/** 
     * 写Excel操作 
     * @param fileName 
     * 文件名，文件要写入到的盘符和文件名，但不需要后缀名 
     * @param fieldName 
    * 表头名 
    * @param res 
     * 数据对象,java.sql.ResultSet 
     */ 
public class ExcelFile {  

    public static void writeExcel(String fileName,String [] fieldName,ResultSet res)  
   {  
       FileOutputStream fos =null;  
       try {  
            fos = new FileOutputStream(fileName+".xls");  //excel 文件名
            HSSFWorkbook wb = new HSSFWorkbook();   
             HSSFSheet s = wb.createSheet();   
             createTag(fieldName,s);//写表格的头部  
             createValue(res,s);//获取数据集，然后获得数据，写文件  
            wb.write(fos);   
            fos.close();   
       } catch (FileNotFoundException e) {  
            e.printStackTrace();  
       } catch (IOException e) {  
           e.printStackTrace();  
       }  
        finally 
        {  
           if(fos!=null)  
           {  
                try {  
                   fos.close();  
                } catch (IOException e) {  
                    e.printStackTrace();  
                }  
           }  
        }  
    }  
      
    /** 
     * 创建表格表头 
    * @param tags 
     * @param s 
    */ 
   private  static void createTag(String [] tags,HSSFSheet s)  
   {  
           HSSFRow row = s.createRow(0);   
            HSSFCell cell = null;  
            for(int i=0;i<tags.length;i++)  
            {  
               cell = row.createCell(i);  
                cell.setCellValue(tags[i]);  
            }  
   }  
     
      
   /** 
     * 设置表格内容 
    * @param res 
     * @param s 
     */ 
    private  static void createValue(ResultSet res,HSSFSheet s)  
    {  
        try {  
           int flag = 1;  
           int count = res.getMetaData().getColumnCount();  
           HSSFRow row = null;   
           HSSFCell cell = null;  
            while(res.next())  
            {  
                row = s.createRow(flag);  
               for(int i=1;i<=count;i++)  
                {  
                   cell = row.createCell(i-1);  
                    Object obj = res.getObject(i);  
                    cell.setCellValue(obj+"");  
                }  
                flag++;  
            }  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
 
    }  
 
} 

public class TestExcel {
  public static void main(String [] args)  
      {  
         String sql = "select userid,user_name_c,user_name_e from RWSYS_USER"; //查询数据
         ArrayList list=new ArrayList();
         ConnectionDB conn = new ConnectionDB();  
          ExcelFile.writeExcel("D://excel",new String[]{"ID","姓名","昵称"},conn.query(sql, list));   //存放路径、表头、数据
     }  

}

 

数据库连接：

public class ConnectionDB {
 protected Connection conn;
 protected PreparedStatement pstmt;
 protected ResultSet rs;
 final static String DRIVER="com.microsoft.sqlserver.jdbc.SQLServerDriver";
 final static String NAME="sa";
 final static String PWD="sa123";
 public Connection getConnectionTODB() {
  try {
  Class.forName(DRIVER);
  String url="jdbc:sqlserver://localhost:1433; database=FLUX_SEC";
  conn=DriverManager.getConnection(url,NAME,PWD);
  System.out.println("连接成功");
  } catch (ClassNotFoundException e) {
  System.out.println("加载驱动失败！");
  e.printStackTrace();
  } catch (SQLException e) {
  System.out.println("连接数据库失败！");
  e.printStackTrace();
  }
  return conn;
  }
 public ResultSet query(String sql, List temp) {
  getConnectionTODB();
  try {
   pstmt = conn.prepareStatement(sql);
   int size = temp.size();
   if (temp != null && temp.size() > 0) {
    for (int i = 0; i < temp.size(); i++) {
     pstmt.setObject(i + 1, temp.get(i));
    }
   }
   rs = pstmt.executeQuery();
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return rs;
 }
}

 

要添加 poi jar包。