这个博客很早以前就写了,但写的比较乱,今天来重新整理下,因为excel数据导出还是挺常用的,SSH2
页面端(大家可以根据自己来定义页面,这个不是重点)
<p style="text-align:left; padding-top:15px"> <b>导出声明:</b><font color = "red">勾选选定导出则按照勾选的学生进行导出,否则将按当前视图进行导出</font> </p> <p style="text-align:left; padding-top:15px"> <input type="checkbox" name = "require" id = "require" value="1" onclick = "require()"> 选定导出<font color = "red">[勾选后将会根据你选定的学生进行导出]</font></p> <p style="text-align:center; padding-top:15px"> <input type="button" value="全选" onclick="allSelect('stu');"/> <input type="button" value="反选" onclick="invertSelect('stu');"/> </p> <p></p> <p style="text-align:center; padding-top:15px"> 选择需要导出的选项:</br> <input type="checkbox" name="stu" id="a" value="学生学号">学生学号 <input type="checkbox" name="stu" id="a" value="学生姓名">学生姓名 <input type="checkbox" name="stu" id="a" value="学生性别">学生性别 <input type="checkbox" name="stu" id="a" value="学生专业">学生专业 <input type="checkbox" name="stu" id="a" value="专业学制">专业学制</br> <input type="checkbox" name="stu" id="a" value="入学年份">入学年份 <input type="checkbox" name="stu" id="a" value="毕业年份">毕业年份 <input type="checkbox" name="stu" id="a" value="电子邮箱">电子邮箱 <input type="checkbox" name="stu" id="a" value="通信地址">通信地址 <input type="checkbox" name="stu" id="a" value="家庭地址">家庭地址</br> <input type="checkbox" name="stu" id="a" value="工作省市">工作省市 <input type="checkbox" name="stu" id="a" value="工作单位">工作单位 <input type="checkbox" name="stu" id="a" value="工作岗位">工作岗位 <input type="checkbox" name="stu" id="a" value="职务职称">职务职称 <input type="checkbox" name="stu" id="a" value="办公电话">办公电话</br> <input type="checkbox" name="stu" id="a" value="QQ号码">QQ号码 <input type="checkbox" name="stu" id="a" value="手机号码">手机号码</br> </p> <p style="text-align:center; padding-top:15px"> <input type="button" value="全选" onclick="allSelect('stu');"/> <input type="button" value="反选" onclick="invertSelect('stu');"/> <input type="button" value="导出" onclick="exportExcel('exportExcelAction.action');"/> </p>
页面时经过js跟后台进行交互的,这里的导出不支持Ajax的
/** * 导出excel * */ function exportExcel(tagAction){ //获取当前视图 var showType = $("#showType").val(); var major = $("#major").val(); var address = $("#address").val(); var searchType = $("#searchType").val(); var searchContext = $("#searchinput").val(); //查询的内容 if($("#require").attr("checked")==true){ //选定导出 var ids1=new Array(); if($("input[name='id']:checked").size()==0){ alert("选择需要导出的学生!"); return false; } $("input[name='id']:checked").each(function(i,obj){ ids1[i]=$(obj).val(); }); var idStuStr = ids1.join("-"); } //获取要导出的项 var ids=new Array(); if($("input[name='stu']:checked").size()==0){ alert("选择需要导出的项!"); return false; } $("input[name='stu']:checked").each(function(i,obj){ ids[i]=$(obj).val(); }); var idStr = ids.join("-"); if(showType == undefined){ showType = 0; } if(major == undefined){ major = 0; } if(address == undefined){ address = 0; } if(idStuStr == undefined){ idStuStr = 0; } //alert(idStr+major + showType+ idStuStr); //return false; //进行乱码处理,进行两次encodeURI包装 window.location.href=""+tagAction+"?idStr="+encodeURI(encodeURI(idStr))+"&showType="+encodeURI(encodeURI(showType))+"&major="+encodeURI(encodeURI(major))+"&address="+encodeURI(encodeURI(address))+"&idStuStr="+encodeURI(encodeURI(idStuStr))+"&searchType="+encodeURI(encodeURI(searchType))+"&searchContext="+encodeURI(encodeURI(searchContext))+""; }
struts.xml
<!-- excel导出 -->
<action name="exportExcelAction" class="xidian.sl.action.StuInforAction">
<result name="success" type="stream"><!-- type="stream"表示要以二进制的文件去进行下载 -->
<param name="contentType">application/vnd.ms-excel</param><!-- 指明文件的类型 -->
<!--attachment属性是要选择打开时单独打开,而不是在浏览器中打开,默认是inline. 指明下载时文件的名字 -->
<param name="contentDisposition">attachment;filename="stu_infor.xls"</param>
<param name="inputName">exportInfor</param><!-- 指明文件下载的方法的名字 -->
</result>
</action>
有些情况下我们需要动态获取文件名,因为有些附件下载等,文件的类型是不能预料的
<!-- 附件下载 --> <action name = "attachmentDownload" class = "xidian.sl.crm.action.common.AttachmentAction" method = "getAttachmentDownload"> <result name="success" type="stream"> <!--<param name="contentType">${contentType}</param> --> <param name="contentDisposition">attachment;filename="${path}"</param> <param name="inputName">download</param> </result> </action>
然后在类中进行相应变量的赋值,提供get,set方法:如:
path = new String(attachment.getAttachmentName().getBytes(), "ISO-8859-1"); //转码,解决中文乱码问题
action层:
/** * 导出excel操作,get方法名必须与struts.xml中指定的文件下载方法名对应 * */ public InputStream getExportInfor() throws Exception{ idStr = java.net.URLDecoder.decode(idStr, "UTF-8"); //进行编码的转换,导出的项 major = java.net.URLDecoder.decode(major, "UTF-8"); showType = java.net.URLDecoder.decode(showType, "UTF-8"); address = java.net.URLDecoder.decode(address, "UTF-8"); idStuStr = java.net.URLDecoder.decode(idStuStr, "UTF-8"); searchContext = java.net.URLDecoder.decode(searchContext, "UTF-8"); initialize(); System.out.println(idStr + major+ showType+ address+ idStuStr+searchContext+searchType ); return this.stuInforService.getInputStream(city, major, showType, address, idStr, idStuStr, searchType, searchContext); }
serviceInterface:
/** * 进行excel的导出 * */ public InputStream getInputStream(String city, String major, String showType, String address, String idStr, String idStuStr, int searchType, String searchContext);
serviceImpl:
/* * 对excel导出做处理 * */ public InputStream getInputStream(String city, String major, String showType, String address, String idStr, String idStuStr, int searchType, String searchContext) { //System.out.println("专业:"+major+" 视图类型"+showType+" "+idStr); String[] ids = idStr.split("-"); HSSFWorkbook wb = new HSSFWorkbook(); //使用poi,导入poi的包,首先要生成一个HSSFWorkbook对象 HSSFSheet sheet = wb.createSheet("sheet1"); //创建excel的sheet HSSFRow row=sheet.createRow(0); //创建第一行 HSSFCell cell=row.createCell((short)0); //创建第一个单元格 cell.setEncoding(HSSFCell.ENCODING_UTF_16); //每个单元格都要设置编码格式 cell.setCellValue("序号"); for(int i=0;i<ids.length; i++){ cell=row.createCell((short)(i+1)); //依次创建单元格 cell.setEncoding(HSSFCell.ENCODING_UTF_16); //每个单元格都要设置编码格式 cell.setCellValue(ids[i]); } /* * 到此表头已经设计好了,下面就进行数据的插入 * */ List<StuInfor> stuInfors = new ArrayList<StuInfor>(); if(idStuStr == null){ stuInfors = stuInforDAO.getStuInforListByHQL(getHQL(city, showType, major, address, searchType, searchContext)); }else{ stuInfors = stuInforDAO.getStuInforListByHQL(getHql(idStuStr)); } for(int i=0;i<stuInfors.size();i++){ StuInfor stuInfor = stuInfors.get(i); //获得一条记录 row=sheet.createRow(i+1); //产生下面的其他行 cell=row.createCell((short)0); //第一个单元格 cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue(i+1); for(int a=0;a<ids.length; a++){ //后面的单元格依次显示要显示的内容 cell=row.createCell((short)(a+1)); cell.setEncoding(HSSFCell.ENCODING_UTF_16); if(ids[a].equals("学生学号")){ cell.setCellValue(stuInfor.getStuNum()); }else if(ids[a].equals("学生姓名")){ cell.setCellValue(stuInfor.getStuName()); }else if(ids[a].equals("学生性别")){ cell.setCellValue(stuInfor.getStuSex()); }else if(ids[a].equals("学生专业")){ cell.setCellValue(stuInfor.getStuZy()); }else if(ids[a].equals("专业学制")){ cell.setCellValue(stuInfor.getStuXz()); }else if(ids[a].equals("入学年份")){ cell.setCellValue(stuInfor.getStuStartTime()); }else if(ids[a].equals("毕业年份")){ cell.setCellValue(stuInfor.getStuEndTime()); }else if(ids[a].equals("电子邮箱")){ cell.setCellValue(stuInfor.getStuEmail()); }else if(ids[a].equals("通信地址")){ cell.setCellValue(stuInfor.getStuCommAddress()); }else if(ids[a].equals("家庭地址")){ cell.setCellValue(stuInfor.getStuAddress()); }else if(ids[a].equals("工作省市")){ cell.setCellValue(stuInfor.getStuWorkAddress()); }else if(ids[a].equals("工作单位")){ cell.setCellValue(stuInfor.getStuWorkPlace()); }else if(ids[a].equals("工作岗位")){ cell.setCellValue(stuInfor.getStuWorkPost()); }else if(ids[a].equals("职务职称")){ cell.setCellValue(stuInfor.getStuWorkZc()); }else if(ids[a].equals("办公电话")){ cell.setCellValue(stuInfor.getStuPhone()); }else if(ids[a].equals("QQ号码")){ cell.setCellValue(stuInfor.getStuQq()); }else if(ids[a].equals("手机号码")){ cell.setCellValue(stuInfor.getStuTelephone()); } } } /* * 以上就生成了所需的excel文件 *下面写另外一个方法直接不产生临时文件,就在内存中 */ ByteArrayOutputStream os=new ByteArrayOutputStream(); try { wb.write(os); } catch (IOException e) { e.printStackTrace(); } byte[] content=os.toByteArray(); InputStream is=new ByteArrayInputStream(content); return is; }
持久层的操作这里就省略不写了;