Excel导出功能的实现

一、js代码

//用户导出excel
		$("#export_user").click(function(){
			 var checkedList = new Array();
		        for(var id in opts.checkBoxState){  
		            checkedList.push(id);
		        } 
		        if(checkedList.length==0){
		            jAlert("请选择要导出的用户!",'警告',"warn");
		            return;
		        };
		        var fortThreeUniformUserId = checkedList.toString();
		        var checkIds = fortThreeUniformUserId.split(",");
		        for(var i=0;i<checkIds.length;i++){
		        	if(checkIds[i]=="1000000000001"){
		        		jAlert("初始化用户不可被导出，请重新选择!",'警告',"warn");
			            return;
		        	}
		        }
		    	$.ajax({
					cache : false,
					type : 'POST',
					data : {"fortThreeUniformUserId":fortThreeUniformUserId},
					url : window.top.ctx+'/excel/three/userImportExport/export_user_data',
					dataType : 'text',
					async : false,
					success : function(data) {
						var dataObj = eval("(" + data + ")");
						if(dataObj.success == "success"){
							window.location.href=window.top.ctx+'/excel/three/userImportExport/export_user?version=normal'+'&random1='+(encrypt((new Date().getTime()+'').substring(2,10)));
						}
						else{
							jAlert("传输导出数据失败",'提示');
						}
					},
					error : function() {
						jAlert("传输导出数据失败",'提示');
					} 
				});
		});
		

 

二、Controller层代码

	/**
	 * 批量导出用户
	 * @param fortUserId
	 * @return
	 */
	@SuppressWarnings("static-access")
	@RequestMapping(value = "/export_user")
	public void exportUser(HttpServletResponse response) {
		boolean resultState = true;
		String[] fortThreeUniformUserId = null;
		try {
			log.info("批量导出用户");
			String webSessionId=getRequest().getSession().getId();
			log.info(webSessionId);
			
			String[] webSessionIds=getRequest().getSession().getAttribute(webSessionId).toString().split(",");
			ExcelUtil<Object> ex = new ExcelUtil<Object>();
            //path路径   name文件名称含后缀
			String path = ThreeUserConstant.THREE_EXPORT_USER_PATH;
			String name = ThreeUserConstant.THREE_USER_TEMP_EXCEL_NAME;
			ex.clearExcelXls(path);
			List<FortThreeUniformUser02> fortThreeUniformUsers = this.fortThreeUniformUserService01
			.getEntitysByIdsFortSecretExport(webSessionIds);

// 对查询出的用户做操作
//			List<FortThreeUniformUser02> fortThreeUniformUser = setFortUserType(fortThreeUniformUsers);
			
            //将数据写到模板中
			ex.exportExcelXls(fortThreeUniformUser, path);
			String filePath = PropertiesUtil.class.getResource(path).getPath();
            
			//下载当前模板
			FileUpDownUtils.download(getRequest(), response, filePath,
					ThreeUserConstant.USER_IMPORT_FILE_CONTENT_TYPE, name);
			

		} catch (Exception e) {
			resultState = false;
			e.printStackTrace();
		}
		if(ObjectUtil.isNotNull(fortThreeUniformUserId)){
			addSysLog(fortThreeUniformUserId, resultState);
		}
	}
	

ExcelUtil工具类   

/**
     * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
     * 
     * @param title	表格标题名
     * @param headers 表格属性列名数组
     * @param dataset  需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *            	   javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * @param fileLocation EXCEL文档导出到本地文件的路径
     * @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
	 * @throws IOException 
	 * @throws FileNotFoundException 
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public  void exportExcelXls(
            List<? extends T> dataset,String fileName) throws FileNotFoundException, IOException{
       //获取本地excel
    	FileInputStream in = new FileInputStream(new File(PropertiesUtil.class.getResource(fileName).getPath()));
    	HSSFWorkbook workbook = new HSSFWorkbook(in);
        HSSFSheet sheet=workbook.getSheet("Sheet1");//获取第一个sheet
        HSSFRow row;
        // 遍历集合数据，产生数据行
        Iterator<T> it = (Iterator<T>) dataset.iterator();
        //System.out.println("数据行:"+dataset.size());
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            Object t = (Object) it.next();
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            Field[] fields = t.getClass().getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
              //  cell.setCellStyle(style2);
                Field field = fields[i];
                String fieldName = field.getName();
                if("fortUserId".equals(fieldName)){
                	continue;
                }
                HSSFCell cell = row.createCell(i);
                String getMethodName = "get"
                        + fieldName.substring(0, 1).toUpperCase()
                        + fieldName.substring(1);
                try {
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod(getMethodName,
                            new Class[] {});
                    Object value = getMethod.invoke(t, new Object[] {});
                    // 判断值的类型后进行强制类型转换
                    String textValue = null;
                    if(value != null){
	                    if (value instanceof Date) {
	                    	String pattern="yyy-MM-dd";
	                        Date date = (Date) value;
	                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
	                        textValue = sdf.format(date);
	                    } 
	                    else {
	                        // 其它数据类型都当作字符串简单处理
	                        textValue = value.toString();
	                    }
                    }
                    // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
                    if (textValue != null) {
                        Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                        Matcher matcher = p.matcher(textValue);
                        if (matcher.matches()) {
                            // 是数字当作double处理
                            cell.setCellValue(Double.parseDouble(textValue));
                        } else {
                            HSSFRichTextString richString = new HSSFRichTextString(
                                    textValue);
                            cell.setCellValue(richString);
                        }
                    }
                    workbook.close();
                } catch (SecurityException e) {
                    e.printStackTrace();
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }
            }

        }
        try {
        	File file=new File(PropertiesUtil.class.getResource(fileName).getPath());
        	if(!file.exists()){
        		file.createNewFile();
        	}
        	OutputStream out = new FileOutputStream(file);
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

  /**
     * 清除excel
     * @param fileName
     */
    public static void clearExcelXls(String fileName){
    	try{

            FileInputStream is = new FileInputStream(new File(PropertiesUtil.class.getResource(fileName).getPath()));

            HSSFWorkbook workbook = new HSSFWorkbook(is);

            HSSFSheet sheet = workbook.getSheetAt(0);

            int lastRowNum = sheet.getLastRowNum();
            for (int i = 1;i <= lastRowNum;i++) {
           	 HSSFRow row = sheet.getRow(i);
           	 sheet.removeRow(row);
			 }
            int mergedCount = sheet.getNumMergedRegions();
            for(int i=mergedCount-1;i>=0;i--){
            	sheet.removeMergedRegion(i);
            }
            FileOutputStream os = new FileOutputStream(new File(PropertiesUtil.class.getResource(fileName).getPath()));

            workbook.write(os);

            workbook.close();
            is.close();

            os.close();

        } catch(Exception e) { 

            e.printStackTrace();

        }
    }
    

 FileUpDownUtils工具类

   /**
	     * 下载
	     * @param downLoadPath 文件路径及文件名
	     * @param realName 文件名
	     */
	    public static void download(HttpServletRequest request,  
	            HttpServletResponse response, String downLoadPath, String contentType,  
	            String realName) throws Exception {  
	    	
	    	String isIE = request.getParameter("isIE");
	       // response.setContentType("application/zip;charset=UTF-8");  
	        request.setCharacterEncoding("UTF-8");  
	        BufferedInputStream bis = null;  
	        BufferedOutputStream bos = null;
	        downLoadPath=downLoadPath.replace("../", "");
	        long fileLength = new File(downLoadPath).length();
	        //response.reset();
	        response.setContentType(contentType);
	        realName=realName.trim();
	        downLoadPath = downLoadPath.trim();
	        if ("IE".equals(isIE)) {  
	            realName = URLEncoder.encode(realName, "UTF-8");  
	        }else {  
	            realName = new String(realName.getBytes("UTF-8"), "ISO8859-1");  
	        }
	        response.setHeader("Content-Disposition", "attachment; filename="+realName);  
	        response.setHeader("Content-Length", String.valueOf(fileLength));  
            response.setHeader("pragma","public");
            response.setHeader("Cache-Control", "public");  
	        bis = new BufferedInputStream(new FileInputStream(downLoadPath));  
	        bos = new BufferedOutputStream(response.getOutputStream());  
	        byte[] buff = new byte[10240];  
	        int bytesRead;  
	        while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {  
	            bos.write(buff, 0, bytesRead);  
	        }  
	        bis.close();  
	        bos.close();  
	    }  
	    

Mapper

  
  <!-- 导出 -->
    <select id="selectByPrimaryKeysFortSecretExport" parameterType="String" resultMap="BaseResultMap02">
    select 
     <include refid="Column_List" />
    from fort_three_uniform_user
    where fort_three_uniform_user_id in 
	    <foreach collection="Ids" item="Id" open="(" separator="," close=")"> 
	    #{Id,jdbcType=VARCHAR} 
	  	</foreach>
       ORDER BY fort_user_account DESC
  </select>