java读取数据库数据并导出到EXCEL中

时间:2023-01-13 09:32:06
 

private String daoChuExcel(HttpServletRequest request, String actionType,String jieci,HttpServletResponse response){
// 实例化AppBo对象(数据库操作类AppBo)
AppBo bo = new AppBo();
 
ArrayList list=new ArrayList();
String sql="select * from ZH_GZ_TONGJI where TYPE='国家' ";
    
try
   {
     list = bo.query(sql);
     
    // if (list.size() > 0 ){ 
     //获取web项目路径
     String root = request.getSession().getServletContext().getRealPath("/");
     //创建文件输入流(必须先做好模板tongjijieguo.xls)
     POIFSFileSystem fSystem = new POIFSFileSystem(new FileInputStream(root + "report_template\\tongjijieguo.xls"));
     
     HSSFWorkbook _wb = new HSSFWorkbook(fSystem);
     HSSFSheet st = _wb.getSheetAt(0);
      //给excel,sheet命名
     _wb.setSheetName(0, "国家");


     int index = 3;
     int num = 1;
     for (int i = 0; i < list.size(); i++) {
       HashMap map = (HashMap)list.get(i);


       HSSFRow row = st.createRow((short)index++);
       for (int c = 0; c < 6; c++) {
         HSSFCell localHSSFCell = row.createCell(c);
       }
       row.getCell(0).setCellValue(num++);
       row.getCell(1).setCellValue((String)map.get("TYPE"));
       row.getCell(2).setCellValue((String)map.get("TYPE_NAME"));
       row.getCell(3).setCellValue((String)map.get("SHULIANG"));
       row.getCell(4).setCellValue((String)map.get("ZHANBI"));
       row.getCell(5).setCellValue((String)map.get("RENSHU"));
       
     }

     response.setContentType("application/OCTET-STREAM");

    //给EXCEL命名

     String filename = "统计结果导出.xls";
     //设置文件解析方式
     response.setHeader("Content-Disposition", "Attachment;Filename=" + new String(filename.getBytes("gb2312"), "ISO-8859-1"));
     //创建文件输出流
     OutputStream out = response.getOutputStream();
     //写入EXECL数据
     _wb.write(out);
     //刷新流
     out.flush();
     //关闭流
     out.close();
    // }
     
   }catch (Exception e){
     e.printStackTrace();
     
   }
}