Stringconfig_value=systemConfigService.getConfigValueByKey("Export.Xls");

logger.info("获取的导出类型为execl表格"+",每页sheet导出的行数为:"+config_value+"行-------------------");

Filefile=newFile(newSimpleDateFormat("yyyy-MM-dd").format(newDate())+".xls");

WritableWorkbookwbook=null;WritableSheetwsheet=null;

wbook=Workbook.createWorkbook(file);//建立excel文件

longstartTime=System.currentTimeMillis();

try{

ExportToExcelSearchConditioncondittion=((ExportToExcelSearchCondition)ServletContext.currentSession().getAttribute("condittion"));

List<ContentToExcel>cte=newArrayList<ContentToExcel>();

if(condittion==null){

thrownewException("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)){

intvalue=NumberUtils.toInt(config_value);

if(cte!=null&&cte.size()>0){

intk=0;//分sheet的个数

inti=0;//用于循环Excel的行号

Iterator<ContentToExcel>it=cte.iterator();

Map<String,String>contentidAndContentTypeMap=newHashMap<String,String>();

Map<String,String>contentIdAndDurationMap=newHashMap<String,String>();

for(ContentToExcelcontentToExcel:cte){

contentidAndContentTypeMap.put(contentToExcel.getContentId(),contentToExcel.getContentType());

}

//计算时长

contentIdAndDurationMap=category2ContentDAO.getDurationByContentIdMap(contentidAndContentTypeMap);

/**

*1)采用iterator迭代器进行迭代，与for循环相比可优化迭代效率。

*2)大量数据影响效率，求大数据量的解决办法。

*addbyguohua.yuan2013-06-08

*/

while(it.hasNext()){

ContentToExcelcontentToExcel=it.next();

if(i%value==0){

wsheet=wbook.createSheet("节目单导出("+(int)(i/value+1)+")",(int)(i/value+1));//工作表名称

//设置Excel字体

WritableFontwfont=newWritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);

WritableCellFormattitleFormat=newWritableCellFormat(wfont);

String[]title={"标识","名称","类型","码流分档","时长","快编人员","初审人员","复审人员","终审人员","最后更新时间","内容创建时间","入库时间","状态"};

//设置Excel表头

for(intj=0;j<title.length;j++){

LabelexcelTitle=newLabel(j,0,title[j],titleFormat);

wsheet.addCell(excelTitle);

}

k=k+1;

}

wsheet.addCell(newLabel(0,i+1-value*(k-1),contentToExcel.getContentId()));

wsheet.addCell(newLabel(1,i+1-value*(k-1),contentToExcel.getContentName()));

if(contentToExcel.getContentType().equals("Serie")){

wsheet.addCell(newLabel(2,i+1-value*(k-1),"电视剧单集"));

}elseif(contentToExcel.getContentType().equals("Series")){

wsheet.addCell(newLabel(2,i+1-value*(k-1),"连续剧"));

}elseif(contentToExcel.getContentType().equals("Movie")){

wsheet.addCell(newLabel(2,i+1-value*(k-1),"电影"));

}

if(contentToExcel.getHSDFlag()==1){

wsheet.addCell(newLabel(3,i+1-value*(k-1),"标清"));

}elseif(contentToExcel.getHSDFlag()==2){

wsheet.addCell(newLabel(3,i+1-value*(k-1),"高清"));

}elseif(contentToExcel.getHSDFlag()==3){

wsheet.addCell(newLabel(3,i+1-value*(k-1),"超高清"));

}

//添加时长

wsheet.addCell(newLabel(4,i+1-value*(k-1),contentIdAndDurationMap.get(contentToExcel.getContentId())));

wsheet.addCell(newLabel(5,i+1-value*(k-1),contentToExcel.getKbPersonner()));

wsheet.addCell(newLabel(6,i+1-value*(k-1),contentToExcel.getCsPersonner()));

wsheet.addCell(newLabel(7,i+1-value*(k-1),contentToExcel.getFsPersonner()));

wsheet.addCell(newLabel(8,i+1-value*(k-1),contentToExcel.getZsPersonner()));

if(contentToExcel.getModifyTime()==null){

wsheet.addCell(newLabel(9,i+1-value*(k-1),""));

}else{

wsheet.addCell(newLabel(9,i+1-value*(k-1),newSimpleDateFormat("yyyy-MM-ddHH:mm:ss").format(contentToExcel.getModifyTime())));

}

if(contentToExcel.getCreateTime()==null){

wsheet.addCell(newLabel(10,i+1-value*(k-1),""));

}else{

wsheet.addCell(newLabel(10,i+1-value*(k-1),newSimpleDateFormat("yyyy-MM-ddHH:mm:ss").format(contentToExcel.getCreateTime())));

}

if(contentToExcel.getInstorageTime()==null){

wsheet.addCell(newLabel(11,i+1-value*(k-1),""));

}else{

wsheet.addCell(newLabel(11,i+1-value*(k-1),newSimpleDateFormat("yyyy-MM-ddHH:mm:ss").format(contentToExcel.getInstorageTime())));

}

if(StringUtils.equals(contentToExcel.getStatus(),"1500")){

wsheet.addCell(newLabel(12,i+1-value*(k-1),"成品就绪"));

}elseif(StringUtils.equals(contentToExcel.getStatus(),"1700")){

wsheet.addCell(newLabel(12,i+1-value*(k-1),"发布成功"));

}elseif(StringUtils.equals(contentToExcel.getStatus(),"1800")){

wsheet.addCell(newLabel(12,i+1-value*(k-1),"发布失败"));

}elseif(StringUtils.equals(contentToExcel.getStatus(),"1901")){

wsheet.addCell(newLabel(12,i+1-value*(k-1),"发布中"));

}

i++;

}

wbook.write();//写入文件

}else{

thrownewException("没有数据可导");

}

}else{

thrownewException("请检查系统配置管理是否配置导出类型的数据");

}

}catch(Exceptione){

thrownewException(e);

}finally{

if(wbook!=null){

wbook.close();

}

}

logger.info("导出excel耗时："+(System.currentTimeMillis()-startTime)+"ms");

ServletContext.currentSession().removeAttribute("condittion");

returnnewDownloadFile(file.getName()).readFrom(file);

}

下一篇：在jsp程序中使用com组件