Jsp Excel导出功能（其他语言类似）

Jsp页面表格内容Excel导出功能
(不使用IE的ActiveX，同时不需要为了添加Excel导出操作而增加多少工作量)

已经查询出来的表格导出
前台：

导出内容
<div id="divExcel" fileName="文件名 -${year}年${province}${city}${country}(${type_name}).xls">
导出内容
</div>

导出脚本
<script type="text/javascript">//<![CDATA[
function excel(divId, path){	
	divId = divId || "divExcel";
	path  = path  || "";
	
	var divExcel = document.getElementById(divId);
	
	var formToPrint = document.createElement("form");
	var hiddenHtml = document.createElement("input");
	hiddenHtml.type = "hidden";
	hiddenHtml.name = "hiddenHtml";
	hiddenHtml.value = divExcel.innerHTML;
	formToPrint.appendChild(hiddenHtml);
	
	var hiddenName = document.createElement("input");
	hiddenName.type = "hidden";
	hiddenName.name = "hiddenName";
	hiddenName.value = divExcel.fileName;
	formToPrint.appendChild(hiddenName);
	
	document.body.appendChild(formToPrint);
	formToPrint.method = "post";
	formToPrint.action = path;
	//formToPrint.target = "_blank";
	formToPrint.submit();
}
//]]>--></script>

导出按钮
<html:button property="toExcel" onclick="excel('divExcel','XXXAction.do?method=toExcel')" value="导出到Excel" />

后台：

public ActionForward toExcel(ActionMapping mapping, ActionForm form, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		super.renderExcel(request, response);
		return null;
	}

	protected void renderExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
	    String hiddenHtml = StringUtils.lowerCase(request.getParameter("hiddenHtml"));

	    hiddenHtml = StringUtils.replace(hiddenHtml, "border=0", "border=1");
	    hiddenHtml = StringUtils.replace(hiddenHtml, "border=\"0\"", "border=\"1\"");

	    String fname = EncryptUtils.encodingFileName(request.getParameter("hiddenName"));

	    response.setCharacterEncoding("UTF-8");
	    response.setContentType("application/octet-stream");
	    response.setHeader("Content-Disposition", "attachment;filename=" + fname);

	    PrintWriter out = response.getWriter();
	    out.println(hiddenHtml);

	    out.flush();
	    out.close();
	  }

	public static String encodingFileName(String fileName) {
		String returnFileName = "";
		try {
			returnFileName = URLEncoder.encode(fileName, "UTF-8");
			returnFileName = StringUtils.replace(returnFileName, "+", "%20");

			returnFileName = new String(fileName.getBytes("GB2312"), "ISO8859-1");
			returnFileName = StringUtils.replace(returnFileName, " ", "%20");

		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		return returnFileName;
	}

没有查询出来的不分页的数据导出：
前台：

<html-el:button property="toExcle" styleId="toExcle" value="导 出" onclick="" />
根据要导出的数据做成不可见的虚拟表格
<div id="divExcelHide" title="B2B销售数据统计" style="display:none;">
      <table width="100%" border="0" align="left" cellpadding="0" cellspacing="1">
        <tr>
          <td colspan="7" align="center"><span style="font-weight:700;font-size:13px">B2B销售数据统计</span></td>
        </tr>
        <tr>
          <th  nowrap="nowrap">订单序号</th>
          <th  nowrap="nowrap">网点名称</th>
          <th nowrap="nowrap">所在区域</th>
          <th  nowrap="nowrap" >下单时间</th>
          <th  nowrap="nowrap">企业名称</th>
          <th  nowrap="nowrap">产品名称</th>
          <th nowrap="nowrap">产品数量</th>
          <th nowrap="nowrap">单价</th>
          <th nowrap="nowrap">总价</th>
        </tr>
        <tbody id="exportData">
        </tbody>
        <tr id="exportDataTR" style="display:none;">
          <td align="left" nowrap="nowrap"></td>
          <td align="left" nowrap="nowrap"></td>
          <td align="left" nowrap="nowrap"></td>
          <td align="left" nowrap="nowrap"></td>
          <td align="left" nowrap="nowrap"></td>
          <td align="left" nowrap="nowrap"></td>
          <td align="left" nowrap="nowrap"></td>
          <td align="left" nowrap="nowrap"></td>
          <td align="left" nowrap="nowrap"></td>
        </tr>
      </table>
    </div> 

导出脚本：
	$("#toExcle").click(function(){
		$.ajax({
			type: "POST",
			url: "SalesStat.do?method=exportToExcel",
			data: "exportExcel=1&" + $("#bottomPageForm").serialize(),
			dataType: "json",
			error: function(request, settings) {alert("数据导出失败！");},
			success: function(returnDataArray) {
				$("#exportData").empty();
				$exportData = $("#exportData");
				$exportDataTR = $("#exportDataTR");
				for (var i = 0; i < returnDataArray.list.length; i++) {
					var cur = returnDataArray.list[i];
					$newTR_TD = $exportDataTR.clone().css("display","").appendTo($exportData).children();
					$newTR_TD.eq(0).text("=\""+cur.trade_index+"\"");
					$newTR_TD.eq(1).text(cur.shop_name);
					$newTR_TD.eq(2).text(cur.p_name);
					$newTR_TD.eq(3).text(cur.add_date);
					$newTR_TD.eq(4).text(cur.entp_name);
					$newTR_TD.eq(5).text(cur.md_name);
					$newTR_TD.eq(6).text(cur.order_num);
					$newTR_TD.eq(7).text(cur.price);
					$newTR_TD.eq(8).text(cur.money);
				}
				toExcel('divExcelHide', 'SalesStat.do?method=toExcel');
			}
		});
	});

后台：

	private final String date_pattern = "yyyy-MM-dd HH:mm:ss";

	public ActionForward exportToExcel(ActionMapping mapping, ActionForm form, HttpServletRequest request,
			HttpServletResponse response) throws Exception {

		DynaBean dynaBean = (DynaBean) form;
		String exportExcel = (String) dynaBean.get("exportExcel");
		if (StringUtils.isNotBlank(exportExcel) && "1".equals(exportExcel)) {

			OrderInfo entity = new OrderInfo();
			String province = (String) dynaBean.get("province");
			String city = (String) dynaBean.get("city");
			String country = (String) dynaBean.get("country");
			String trade_index = (String) dynaBean.get("trade_index");
			String p_index = "";

			String add_date_begin = (String) dynaBean.get("add_date_begin");
			String add_date_end = (String) dynaBean.get("add_date_end");

			String shop_name = (String) dynaBean.get("shop_name");

			if (StringUtils.isNotBlank(country)) {
				p_index = country;
			} else if (StringUtils.isNotBlank(city)) {
				p_index = city;
			} else {
				p_index = province;
			}
			entity.getMap().put("p_index", p_index);
			// 5：已结束
			String[] default_state = { "5" };
			entity.getMap().put("default_state", default_state);

			if (StringUtils.isNotBlank(trade_index)) {
				entity.getMap().put("trade_index", trade_index);
			}

			DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
			if (StringUtils.isNotBlank(add_date_begin)) {
				entity.getMap().put("add_date_begin", format.parse(add_date_begin));
			}
			if (StringUtils.isNotBlank(add_date_end)) {
				entity.getMap().put("add_date_end", format.parse(add_date_end));
			}

			if (StringUtils.isNotBlank(shop_name)) {
				entity.getMap().put("shop_name_like", shop_name);
			}

			// 所有B2B订单相关业务都需要追加的查询条件：SALES_MODEL销售模式：0:B2B
			entity.setSales_model(0);
			entity.getRow().setFirst(0);
// 最大导出500行记录的数据
			entity.getRow().setCount(500);
			List<OrderInfo> entityList = super.getFacade().getOrderInfoService()
					.getOrderInfoForEntpList(entity);
			JSONObject result = new JSONObject();
			JSONArray list = new JSONArray();
			for (int i = 0; i < entityList.size(); i++) {
				JSONObject obj = new JSONObject();
				OrderInfo cur = entityList.get(i);
				obj.put("trade_index", cur.getTrade_index());
				obj.put("shop_name", cur.getMap().get("shop_name"));
				obj.put("p_name", cur.getMap().get("p_name"));
				if (cur.getAdd_date() != null) {
					obj.put("add_date", DateFormatUtils.format(cur.getAdd_date(), date_pattern));
				}
				obj.put("entp_name", cur.getMap().get("entp_name"));
				obj.put("md_name", cur.getMap().get("md_name"));
				obj.put("order_num", cur.getOrder_num());
				obj.put("price", cur.getMap().get("price"));
				obj.put("money", cur.getMoney());
				list.put(obj);
			}
			result.put("list", list);
			logger.info(result.toString());
			super.render(response, result.toString(), "text/x-json;charset=UTF-8");
			return null;
		}
		return null;
	}

	public ActionForward toExcel(ActionMapping mapping, ActionForm form, HttpServletRequest request,
			HttpServletResponse response) {

		response.setCharacterEncoding("utf-8");

		String name = request.getParameter("hiddenName");
		String html = request.getParameter("hiddenHtml");

		try {
			response.setContentType("application/vnd.ms-excel");

			response.addHeader("Content-Disposition", "attachment; filename=\"" + URLEncoder.encode(name, "UTF-8")
					+ ".xls\"");

			// response.setContentType("text/plain");
			// response.addHeader("Content-Disposition",
			// "attachment; filename=\"" + URLEncoder.encode(name, "UTF-8")
			// + ".csv\"");

			// response.setContentType("application/vnd.ms-word");
			// response.addHeader("Content-Disposition",
			// "attachment; filename=\"" + URLEncoder.encode(name, "UTF-8")
			// + ".doc\"");

			PrintWriter out = response.getWriter();

			out
					.println("<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">");
			out.println("<html xmlns=\"http://www.w3.org/1999/xhtml\">");
			out.println("<head>");
			out.println("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />");
			out.println("<meta http-equiv=\"MSThemeCompatible\" content=\"no\" />");
			out.println("<meta name=\"MSSmartTagsPreventParsing\" content=\"true\" />");
			out.println("<title>" + name + "</title>");
			out.println("</head>");

			html = html.replace("border=0", "border=1");
			// html = html.replace("<A href=\"[^>]*?\">([^<]*?)<\/A>", "$1");

			out.println(html);

			out.println("</body>");
			out.println("</html>");
			out.println("<body>");
		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}

评论

2 楼 itabclichao 2013-08-29  

搞起，顶一个

1 楼 itabclichao 2013-08-29  

顶一个，帮我大忙了