导出Excel

本次总结了两种导出excel的方式，均在项目中测试可行。

第一种 （比较常用）

jsp页面就不介绍了。

js页面导出的方法

/**
 * 导出Excel
 */
UserFeedBackSearch.prototype.exportExcel=function(){
	var offerTotalAcount=$("#offerTotalAcount").val();
	if(offerTotalAcount>5000){
		alert("记录数("+offerTotalAcount+")大于5000，请缩小搜索范围!");
		return;
	}
	var  url = constants.CTX + "/userFeedBackManage!exportExcel.action";
	$("#form1")[0].action = url;
	$("#form1")[0].submit();
	
}

注意：你用其他方法提交的时候，要保证提交方法的返回值支持流的方式传回。

action页面

/**
     * 导出Excel
     * <功能详细描述>
     * @return [参数说明]
     * @return String [返回类型说明]
     * @exception throws [违例类型] [违例说明]
     * @see [类、类#方法、类#成员]
     */
    public void exportExcel(){
        log.info("UserFeedBackManageAction.exportExcel");
        try{
            int count=userFeedBackService.queryUserFeedbackCnt(getAuthLatnCd(), userFeedBack); 
            if(count>5000){
                super.getRequest().setAttribute("errorFlag", "1"); //errorFlag 超过5000赋值1，用来界面提示
                if (AAUtils.isAjaxRequest(super.getRequest())) {
                    AAUtils.addZonesToRefresh(super.getRequest(), "ErrorContentZone");
                }
            }else{
                Pagination pagination = initPagination(getRequest(), 5000,count);
                List<UserFeedBack> list=userFeedBackService.queryUserFeedBack(getAuthLatnCd(), userFeedBack, pagination);
                if(null!=list&&list.size()>0){
                    String[] title=new String[]{"记录ID","反馈标题","反馈人","本地网","反馈时间","状态"};
                    List<String[]> resultList = new ArrayList<String[]>();
                    String[] obj = null;
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    for (UserFeedBack vo : list) {
                        int i=0;
                        obj = new String[6];
                        obj[i++] = vo.getFbId()+"";
                        obj[i++] = vo.getFbTitle();
                        obj[i++] = vo.getFbPeople();
                        obj[i++] = vo.getLatnName();
                        obj[i++] = vo.getFbDate()+"";
                        obj[i++] = vo.getStatusCdName();
                        resultList.add(obj);
                    }
                    File file = ExcelUtils.writeExecl("用户改善意见导出_" +sdf.format(Calendar.getInstance().getTime())+ ".xls", title, resultList);
                    ResponseDownloadUtils.download(super.getResponse(), file);
                }
            }
        }catch(Exception e){
            log.error("UserFeedBackManageAction.exportExcel"+e);
        }
    }

/**
     * 销售品产品导出
     * 
     * @param title
     * @param columnTitles
     * @param data
     * @return
     */
    @SuppressWarnings("unchecked")
    public static File writeExecl(String title, String[] columnTitles, Collection data) {
        File file = null;
        WritableWorkbook book = null;
        try {
            
            file = new File(title);
            book = Workbook.createWorkbook(file);
            
            WritableSheet sheet = book.createSheet(title, 0);
            
            int rowIndex = 0;
            
            WritableCellFormat titleFormat = getDefaultTitleFormat();
            
            // 如果表头不为空，则写入表头
            if (columnTitles != null) {
                for (int i = 0; i < columnTitles.length; i++) {
                    WritableCell label = createCell(i, rowIndex, columnTitles[i], titleFormat);
                    sheet.addCell(label);
                    int newLength = columnTitles[i] == null ? 0 : columnTitles[i].getBytes().length;
                    sheet.setColumnView(i, newLength);
                    // CellView cellView = new CellView();
                    // cellView.setAutosize(true);
                    // sheet.setColumnView(i, cellView);
                }
                rowIndex++;
            }
            // 写入数据
            Iterator it = data.iterator();
            while (it.hasNext()) {
                Object[] row = (Object[])it.next();
                int colIndex = 0;
                for (Object cell : row) {
                    WritableCell label = createCell(colIndex, rowIndex, cell);
                    
                    // 将定义好的单元格添加到工作表中
                    sheet.addCell(label);
                    int maxLength = sheet.getColumnWidth(colIndex);
//                  int newLength = ((String)cell) == null ? 0 : ((String)cell).getBytes().length;
                    double newLength = 0;
                    if (cell != null) {
                        char[] ch = ((String)cell).toCharArray();
                        for (int i = 0; i < ch.length; i++) {
                            char c = ch[i];
                            if(isChinese(c)){
                                newLength = newLength+2;
                            }else {
                                newLength+=1;
                            }
                        }
                    }
                    if (maxLength < newLength) {
                        sheet.setColumnView(colIndex, ((int)newLength+1)>56?56:(int)newLength+1);
                    }
                    colIndex++;
                }
                rowIndex++;
            }
            return file;
        }
        catch (Exception e) {
            log.error(e);
        }
        finally {
            try {
                if (book != null) {
                    book.write();
                    book.close();
                }
            }
            catch (IOException e) {
                log.error(e);
            }
            catch (WriteException e) {
                log.error(e);
            }
        }
        
        return file;
    }

public static void download(HttpServletResponse response, File file) {

		BufferedInputStream br = null;
		OutputStream out = null;
		try {
			File f = file;
			if (!f.exists()) {
				response.sendError(404, "File not found!");
				return;
			}
			br = new BufferedInputStream(new FileInputStream(f));
			byte[] buf = new byte[1024];
			int len = 0;

			response.reset();

			String fileName = new String(f.getName().getBytes("GBK"), "ISO8859-1");
			response.setContentType("application/x-msdownload");
			response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
			
			out = response.getOutputStream();
			// out.flush();
			
			while ((len = br.read(buf)) > 0)
				out.write(buf, 0, len);
			br.close();
			out.close();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (br != null)
					br.close();
				if (out != null)
					out.close();
			} catch (IOException e) {
				log.error(e);
			}

		}
	}

上两个方法封装成了共用方法，直接可以用。

第二种方法（这种方法需要结合Struts框架）该导出的好处是可以很方便调整excel文档的样式

jsp和js文件和上面的类似，就不介绍了。

struts层

<action name="exportExcelAction" class="exportExcelAction">			
		<!-- 下载导出excel -->			
		<result name="success" type="stream">	
			<!-- 文件类型及编码设置 -->	
			<param name="contentType">application/vnd.ms-excel,charset=ISO8859-1</param>
			<!-- contentDisposition:文件下载的处理方式，包括内联(inline)和附件(attachment)两种方式，而附件方式会弹出文件保存对话框，
			否则浏览器会尝试直接显示文件。-->
			<!-- fileName下载文件名称-->
			<param name="contentDisposition">attachment;filename="${fileName}"</param>	
			<!-- 下载缓冲区的大小-->
			<param name="bufferSize">4096</param>	
			<!-- 下载文件的来源流-->
			<param name="inputName">excelFile</param>			
		</result>		
</action>

action层（这里方法比较多，一步步来）

私有属性

private static final long serialVersionUID = 1L;
	private static final SimpleDateFormat yyyy_MM_dd = new SimpleDateFormat("yyyy-MM-dd");
	private static final String Explain =
	    "说明：注册来源（0代表App端，1代表Pc端);性别（0代表女，1代表男）;状态（0代表未激活，1代表正常，2代表警告，3代表冻结）;用户类型（0代表用户，1代表商家，2代表其它）";
	private User user;
	private InputStream excelStream; // 这个输入流对应上面struts.xml中配置的那个excelStream，两者必须一致
	private String fileName; // 这个名称就是用来传给上面struts.xml中的${fileName}的
	private UserQuery userQuery;

Explain是excel文件第一行的说明（根据情况取舍）

public String exportExcel() throws Exception
	{
		HttpServletRequest request = ServletActionContext.getRequest();
		// 获取每页展示数据量
		int pageSize = UsualTools.getInt(request.getParameter("rows"));
		// 获取当前页
		int pageNo = UsualTools.getInt(request.getParameter("page"));
		String ids = request.getParameter("idss");
		PageControl<User> pageControl = new PageControl<User>();
		pageControl.setCurrPage(pageNo);
		pageControl.setPerPageNum(pageSize);
		// 查询分页数据
		this.userService.findUserAll(pageControl, this.user, userQuery);
		List<User> list = pageControl.getObjectList();
		List<User> userList = new ArrayList<>();
		if (ids != null && !"".equals(ids))
		{
			userList = userService.findByIds(ids);
		}
		if (userList != null && userList.size() > 0)
		{
			return this.createBookName(userList);

		}
		else if (list == null || list.size() == 0)
		{
			request.setAttribute("message", "查无此数据!");
			return "error";
		}
		else
		{
			return this.createBookName(list);
		}
	}

/**
	 * 生成表名
	 * 
	 * @param list
	 * @return
	 * @throws Exception
	 *             lishun
	 */
	public String createBookName(List<User> list) throws Exception
	{
		HSSFWorkbook workbook = getWorkbook(list);
		try
		{
			if (workbook != null)
			{
				Calendar c = Calendar.getInstance();
				int year = c.get(Calendar.YEAR);
				int month = c.get(Calendar.MONTH) + 1;
				String month_ = new String("" + month);
				if (month < 10)
				{
					month_ = "0" + month;
				}
				int day = c.get(Calendar.DAY_OF_MONTH);
				String day_ = new String("" + day);
				if (day < 10)
				{
					day_ = "0" + day;
				}
				// 第四步：将工作簿写入最上面定义的InputStream流——名称为excelStream，这个名字对应struts.xml中配置的inputName参数
				this.workbook2InputStream(workbook, "user_list_" + year + "-" + month_ + "-" + day_
				    + "");
				return "success";
			}
		}
		catch (IOException e)
		{
			return "error";
		}
		return "error";
	}

/**
	 * 将Workbook写入到InputStream
	 * 
	 * @param workbook
	 * @param fileName
	 * @throws Exception
	 * lishun
	 */
	public void workbook2InputStream(HSSFWorkbook workbook, String fileName) throws Exception
	{
		this.fileName = fileName; // 设置fileName
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		workbook.write(baos);
		baos.flush();
		byte[] aa = baos.toByteArray();
		excelStream = new ByteArrayInputStream(aa, 0, aa.length);
		baos.close();
	}

/**
	 * 将list转化为workbook输出
	 * 
	 * @param list
	 * @return
	 * @throws Exception
	 *             lishun
	 */
	public HSSFWorkbook getWorkbook(List<User> list) throws Exception
	{
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("用户信息列表");
		sheet.setDefaultColumnWidth(14);
		sheet.setDefaultRowHeightInPoints(5);
		// sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 12));//单元格合并**
		// 这里的columnMethods中的值就是pojo里面的getter方法名,是用来取值
		String[] columnMethods =
		    new String[] { "getId", "getName", "getNickname", "getSex", "getMobile", "getAddress",
		        "getRegSource", "getStatus", "getLoginTime", "getLoginAddr", "getImage",
		        "getIdcard", "getLevel", "getScore", "getInviter", "getLongitude", "getLatitude",
		        "getMoney", "getStyle", "getIp" };
		// 这里的columnNames 中的值就是导出的excel里面的标题
		String[] columnNames =
		    new String[] { "用户ID", "姓名", "昵称", "性别", "手机", "地址", "注册来源", "状态 ", "最后登录时间", "登录地址",
		        "头像", "身份证", "等级", "积分", "邀请人", "经度", "纬度", "金额", "用户类型", "最后登录IP" };
		// 首先，我们读取list中的第一个元素，根据它来确定工作表的列名，以及输出数据所对应的方法数组
		User user = list.get(0);
		HSSFRow row0 = sheet.createRow(0); // 创建第1行，也就是输出说明 **
		HSSFCell cell0 = row0.createCell(0);
		HSSFCellStyle cellStyle = cell0.getCellStyle();
		cellStyle.setWrapText(true);
		cell0.setCellValue(new HSSFRichTextString(Explain));
		HSSFRow row = sheet.createRow(1); // 创建第1行，也就是输出表头
		HSSFCell cell;
		for (int i = 0; i < columnNames.length; i++)
		{
			cell = row.createCell(i); // 创建第i列
			cell.setCellValue(new HSSFRichTextString(columnNames[i]));
		}
		// 下面是输出各行的数据
		for (int i = 0; i < list.size(); i++)
		{
			user = (User) list.get(i);
			row = sheet.createRow(i + 2);// 创建第i+1行**
			for (int j = 0; j < columnMethods.length; j++)
			{
				cell = row.createCell(j);// 创建第j列
				Method method;
				method = user.getClass().getMethod(columnMethods[j]); // 这里用到了反射机制，通过方法名来取得对应方法返回的结果对象
				if (method.toString().indexOf("getInviter") > 0)
				{
					Object obj = method.invoke(user);
					if (obj == null)
					{
						cell.setCellValue("无邀请人");
					}
					else
					{
						User u = userService.findById(obj.toString());
						cell.setCellValue(u == null ? "无邀请人" : u.getName() == null ? "邀请人无更新名字" : u
						    .getName());
						;
					}

				}
				else
				{
					Object obj = method.invoke(user);
					cell.setCellValue(obj != null ? obj.toString() : ""); // 往excel写数据
				}
			}
		}
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnMethods.length - 1));// 单元格合并**
		return workbook;
	}