jsp导出excel并支持分sheet导出的方法
本文实例讲述了jsp导出excel并支持分sheet导出的方法。分享给大家供大家参考，具体如下：
publicDownloadFileexportToExcel()throwsException{
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程序设计有所帮助。