当前位置 : 主页 > 手机开发 > harmonyos >

导出excel,并支持分sheet导出

来源:互联网 收集:自由互联 发布时间:2023-08-25
public DownloadFile exportToExcel() throws Exception{String config_value = systemConfigService.getConfigValueByKey("Export.Xls");logger.info("获取的导出类型为execl表格"+",每页sheet导出的行数为:"+config_value+"行----------



public DownloadFile exportToExcel()  throws Exception{
		String config_value =  systemConfigService.getConfigValueByKey("Export.Xls");
		logger.info("获取的导出类型为execl表格"+",每页sheet导出的行数为:"+config_value+"行-------------------");
		File file = new File(new SimpleDateFormat("yyyy-MM-dd").format(new Date())+".xls");
		WritableWorkbook wbook = null ;   WritableSheet wsheet =null;
		wbook = Workbook.createWorkbook(file); //建立excel文件 
		long startTime = System.currentTimeMillis();
		try {
			ExportToExcelSearchCondition condittion = ((ExportToExcelSearchCondition) ServletContext.currentSession().getAttribute("condittion"));
			List<ContentToExcel> cte = new ArrayList<ContentToExcel>();
			if(condittion == null){
				throw new Exception("session获取查询条件失败");
			}else{
				//根据条件查询需要导出的数据
				cte = category2ContentDAO.searchByCondition(condittion.getCategoryId(), condittion.getHSDFlag(),
						condittion.getExternalContentId(),condittion.getContentName(),condittion.getContentId(), condittion.getSystemId(), condittion.getState(),
						condittion.getCpId(),condittion.getContentType(),condittion.getBeforemodifyTime(),condittion.getAftermodifyTime());
			}
			logger.info("开始导出excel表格--");
			if(StringUtils.isNotBlank(config_value)){
					int value = NumberUtils.toInt(config_value);
		            if( cte !=null && cte.size()>0 ){
		               int k = 0;//分sheet的个数
		               int i = 0;//用于循环Excel的行号
		               Iterator<ContentToExcel> it = cte.iterator(); 
		               Map<String,String> contentidAndContentTypeMap = new HashMap<String,String>();  
		               Map<String,String> contentIdAndDurationMap= new HashMap<String,String>();
		               for (ContentToExcel contentToExcel : cte) {
		            	   contentidAndContentTypeMap.put(contentToExcel.getContentId(), contentToExcel.getContentType());
		               }
		               //计算时长
		               contentIdAndDurationMap = category2ContentDAO.getDurationByContentIdMap(contentidAndContentTypeMap);
		               /**
		                * 1)采用iterator迭代器进行迭代,与for循环相比可优化迭代效率。
		                * 2)大量数据影响效率,求大数据量的解决办法。
		                * add by guohua.yuan 2013-06-08
		                */
		               while(it.hasNext()){
		            	   ContentToExcel contentToExcel = it.next();
		            	   if( i % value == 0){
		        			    wsheet = wbook.createSheet("节目单导出("+(int)(i/value+1)+")", (int)(i/value+1)); //工作表名称 
		        			  //设置Excel字体 
		     				   WritableFont wfont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); 
		     				   WritableCellFormat titleFormat = new WritableCellFormat(wfont); 
		     				   String[] title  = { "标识","名称", "类型", "码流分档", "时长","快编人员" ,"初审人员","复审人员","终审人员","最后更新时间","内容创建时间","入库时间","状态"}; 
		     				   //设置Excel表头 
		     				   for (int j = 0; j < title.length; j++) { 
		     				    Label excelTitle = new Label(j, 0, title[j], titleFormat); 
		     				    wsheet.addCell(excelTitle); 
		     				   } 
		     				  k = k+1;
		        		   }
						    wsheet.addCell(new Label(0, i+1 -value*(k-1), contentToExcel.getContentId())); 
						    wsheet.addCell(new Label(1, i+1 - value*(k-1), contentToExcel.getContentName()));
						    if(contentToExcel.getContentType().equals("Serie")){
						    	wsheet.addCell(new Label(2, i+1 - value*(k-1), "电视剧单集")); 
						    }else if(contentToExcel.getContentType().equals("Series")){
						    	wsheet.addCell(new Label(2, i+1 - value*(k-1), "连续剧")); 
						    }else if(contentToExcel.getContentType().equals("Movie")){
						    	wsheet.addCell(new Label(2, i+1 - value*(k-1), "电影")); 
						    }
						    if(contentToExcel.getHSDFlag() == 1){
						    	wsheet.addCell(new Label(3, i+1 - value*(k-1), "标清"));  
						    }else if(contentToExcel.getHSDFlag() == 2){
						    	wsheet.addCell(new Label(3, i+1 -value*(k-1), "高清")); 
						    }else if(contentToExcel.getHSDFlag() == 3){
						    	wsheet.addCell(new Label(3, i+1 - value*(k-1), "超高清"));
						    }
						    //添加时长
						    wsheet.addCell(new Label(4, i+1 - value*(k-1),contentIdAndDurationMap.get(contentToExcel.getContentId()))); 
						    
						    wsheet.addCell(new Label(5, i+1 - value*(k-1), contentToExcel.getKbPersonner())); 
						    wsheet.addCell(new Label(6,  i+1 - value*(k-1), contentToExcel.getCsPersonner())); 
						    wsheet.addCell(new Label(7,  i+1 - value*(k-1), contentToExcel.getFsPersonner())); 
						    wsheet.addCell(new Label(8,  i+1 - value*(k-1), contentToExcel.getZsPersonner())); 
						    if(contentToExcel.getModifyTime()==null){
						    	wsheet.addCell(new Label(9,  i+1 - value*(k-1), "")); 
						    }else{
						    	 wsheet.addCell(new Label(9,  i+1 - value*(k-1), new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(contentToExcel.getModifyTime()))); 
						    }
						   if(contentToExcel.getCreateTime() == null){
							   wsheet.addCell(new Label(10,  i+1 - value*(k-1), "")); 
						   }else{
							   wsheet.addCell(new Label(10,  i+1 - value*(k-1), new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(contentToExcel.getCreateTime())));  
						   }
						   if(contentToExcel.getInstorageTime() ==null){
						    	wsheet.addCell(new Label(11,i+1 - value*(k-1),""));
						    }else{
						    	wsheet.addCell(new Label(11,i+1 - value*(k-1),new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(contentToExcel.getInstorageTime())));
						    }
						    if(StringUtils.equals(contentToExcel.getStatus(), "1500")){
						    	wsheet.addCell(new Label(12,  i+1 - value*(k-1), "成品就绪")); 
						    }else if(StringUtils.equals(contentToExcel.getStatus(), "1700")){
						    	wsheet.addCell(new Label(12,  i+1 - value*(k-1), "发布成功 ")); 
						    }else if(StringUtils.equals(contentToExcel.getStatus(), "1800")){
						    	wsheet.addCell(new Label(12,  i+1 - value*(k-1), "发布失败")); 
						    }else if(StringUtils.equals(contentToExcel.getStatus(), "1901")){
						    	wsheet.addCell(new Label(12,  i+1 - value*(k-1), "发布中")); 
						    }
						    i++;
		               }
		               wbook.write();//写入文件
		            }else{
		            	throw new Exception("没有数据可导");
		            }
			}else{
				throw new Exception("请检查系统配置管理是否配置导出类型的数据");
			}
		} catch (Exception e) {
			throw new Exception(e);
		}finally{
			if(wbook!=null){
				wbook.close();
			}
		}
		logger.info("导出excel耗时:"+(System.currentTimeMillis() - startTime)+"ms");
		ServletContext.currentSession().removeAttribute("condittion");
		return new DownloadFile(file.getName()).readFrom(file);
	}

 

上一篇:jQuery的attr与prop
下一篇:没有了
网友评论