java导出Excel多个工作表(添加多个sheet)

时间:2021-04-07 09:31:04

如果数据量比较大(十万以上)建议导成多个excel文件,之后压缩成zip文件进行下载


  1. //生成一个excel文件  
  2.          WritableWorkbook wwb = null;      
  3.             try {      
  4.                 //首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象      
  5.                 wwb = Workbook.createWorkbook(new File(fileName));  
  6.             } catch (IOException e) {      
  7.                    log.error(e);  
  8.             }      
  9.         long totle = service.getTotle(searchParameters);//业务逻辑方法,获取总数,便于在excel分多个工作表  
  10.         //按65536条数据分页  
  11.         float res=Float.parseFloat(String.valueOf(totle));  
  12.         float mus=65536;  
  13.         float avg=res/mus;  
  14.         Map cols = (Map) request.getSession().getAttribute("columnsMap"); //业务逻辑方法  
  15.         for (int i = 0; i < avg+1; i++) {  
  16.             searchParameters.setEvent_id(String.valueOf(i*mus)); //分页查询条件  
  17.             searchParameters.setTotalLimit(String.valueOf((i+1)*mus));//分页查询条件  
  18.             List result = service.getEvents(searchParameters); //分页查询方法  
  19.               
  20.               if(wwb!=null){      
  21.                     //创建一个可写入的工作表      
  22.                     //Workbook的createSheet方法有两个参数,第一个是工作表的名称,第二个是工作表在工作薄中的位置      
  23.                     WritableSheet ws = wwb.createSheet("列表"+(i+1), i);    
  24.                     String colss = ((String) cols.get("cols")).substring("selected"  
  25.                             .length() + 1);  
  26.                     String[] columns = colss.split(","); //业务逻辑方法(添加标题)  
  27.                     String[] colNames = ((String) cols.get("colNames")).split(",");  
  28.                     for (int j = 0; j < columns.length; j++) {  
  29.                         jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);   
  30.                         jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);   
  31.                         wcfFC.setBackground(Colour.GRAY_25);  
  32.                         Label label = new Label(j, 0,colNames[j],wcfFC);  
  33.                         ws.setColumnView(j, 20); //设置列宽  
  34.                         ws.addCell(label);  //添加标题  
  35.                     }  
  36.                     String str="";  
  37.                     //下面开始添加单元格      
  38.                     for(int m=0;m<result.size();m++){      
  39.                         for(int j=0;j<columns.length;j++){      
  40.                             Map map = (Map) result.get(m);  
  41.                             //在表格中添加(业务数据)  
  42.                             for (Object key : map.keySet()) {  
  43.                                 Object  val = map.get(key);  
  44.                                 str=String.valueOf(val);  
  45.                                 //这里需要注意的是,在Excel中,第一个参数表示列,第二个表示行      
  46.                                 Label labelC = new Label(j, m+1, str);   
  47.                                 //将生成的单元格添加到工作表中      
  48.                                 ws.addCell(labelC);      
  49.                             }  
  50.                                
  51.                         }      
  52.                     }      
  53.                 }    
  54.                   
  55.               
  56.         }  
  57.         //Excel操作完毕之后,关闭所有的操作资源   
  58.          try {      
  59.              //从内存中写入文件中      
  60.              wwb.write();      
  61.              //关闭资源,释放内存      
  62.              wwb.close();      
  63.          } catch (IOException e) {    
  64.                log.error(e);  
  65.          } catch (WriteException e) {      
  66.              log.error(e);  
  67.          }   
  68.           
  69.          //把生成的文件下载  
  70.          File file = new File(fileName);   
  71.             if(!file.exists()) throw new Exception("文件不存在!");  
  72.             FileInputStream fileInputStream = new FileInputStream(file);  
  73.             BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream);  
  74.             OutputStream outputStream = response.getOutputStream();  
  75.             BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(outputStream);  
  76.             response.setContentType("application/x-download");  
  77.             response.setHeader("Content-disposition""attachment;filename=" + URLEncoder.encode("事件列表.xls""UTF-8"));  
  78.             int bytesRead = 0;  
  79.             byte[] buffer = new byte[8192];  
  80.             while ((bytesRead = bufferedInputStream.read(buffer, 08192)) != -1) {  
  81.                 bufferedOutputStream.write(buffer, 0, bytesRead);  
  82.             }  
  83.             bufferedOutputStream.flush();  
  84.             fileInputStream.close();  
  85.             bufferedInputStream.close();  
  86.             outputStream.close();  
  87.             bufferedOutputStream.close();