POI实现导出Excel和模板导出Excel

一.导出过程

             1.用户请求导出

             2.先访问数据库，查询需要导出的结果集

             3.创建导出的Excel工作簿

             4.遍历结果集，写入工作簿

             5.将Excel已文件下载的形式回复给请求客户端

二.具体实现（截取关键代码） 

           1.

    public ResultSet userList(Connection con,PageBean pageBean)throws Exception{
        StringBuffer sb=new StringBuffer("select * from t_user");
        if(pageBean!=null){
            sb.append(" limit ?,?");            
        }
        PreparedStatement pstmt=con.prepareStatement(sb.toString());
        if(pageBean!=null){
            pstmt.setInt(1, pageBean.getStart());
            pstmt.setInt(2, pageBean.getRows());
        }
        return pstmt.executeQuery();
    }

               2.export1代表基本导出,export2代表模板导出

                  

   @RequestMapping("/export")
        public void export(HttpServletResponse response)throws Exception{
                 Connection con=null;
                con=dbUtil.getCon();
                Workbook wb=new HSSFWorkbook();
                String headers[]={"编号","姓名","电话","Email","QQ"};
                ResultSet rs=userDao.userList(con, null);
                ExcelUtil.fillExcelData(rs, wb, headers);
                ResponseUtil.export(response, wb, "导出excel.xls");
                    dbUtil.closeCon(con);    
        }
       @RequestMapping("/export2")
       public void export2(HttpServletResponse response)throws Exception{
            Connection con=null;
                con=dbUtil.getCon();
                Workbook wb=ExcelUtil.fillExcelDataWithTemplate(userDao.userList(con, null), "userExporTemplate.xls");
                ResponseUtil.export(response, wb, "利用模版导出excel.xls");
            
                    dbUtil.closeCon(con);
            
        }

                     3.

                         

public static void fillExcelData(ResultSet rs,Workbook wb,String[] headers)throws Exception{
        int rowIndex=0;
        Sheet sheet=wb.createSheet();
        Row row=sheet.createRow(rowIndex++);
        for(int i=0;i<headers.length;i++){
            row.createCell(i).setCellValue(headers[i]);
        }
        while(rs.next()){
            row=sheet.createRow(rowIndex++);
            for(int i=0;i<headers.length;i++){
                row.createCell(i).setCellValue(rs.getObject(i+1).toString());
            }
        }
    }
    
    public static Workbook fillExcelDataWithTemplate(ResultSet rs,String templateFileName)throws Exception{
        InputStream inp=ExcelUtil.class.getResourceAsStream("/com/qgx/template/"+templateFileName);
        POIFSFileSystem fs=new POIFSFileSystem(inp);
        Workbook wb=new HSSFWorkbook(fs);
        Sheet sheet=wb.getSheetAt(0);
        // 获取列数
        int cellNums=sheet.getRow(0).getLastCellNum();
        int rowIndex=1;
        while(rs.next()){
            Row row=sheet.createRow(rowIndex++);
            for(int i=0;i<cellNums;i++){
                row.createCell(i).setCellValue(rs.getObject(i+1).toString());
            }
        }
        return wb;
    }

                 4.

                  

    public static void write(HttpServletResponse response,Object o)throws Exception{
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out=response.getWriter();
        out.print(o.toString());
        out.flush();
        out.close();
    }
    
    public static void export(HttpServletResponse response,Workbook wb,String fileName)throws Exception{
        response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso8859-1"));
        response.setContentType("application/ynd.ms-excel;charset=UTF-8");
        OutputStream out=response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
    }