📄 exportexcel_regist.jsp

📁 电信的网厅的整站代码
💻 JSP
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<!--注册帐号统计导出EXCEL功能-->
<%@ page language="java" contentType="text/html; charset=GBK" %>
<%@ page import="com.doone.data.*"%>
<%@ page import="com.doone.util.*"%>
<%@ page import="com.doone.uurm.*"%>
<%@ page import="com.doone.fj1w.fjmgr.monitor.*"%>
<%!
	   String getHTMLString(String tmp) {
		tmp = tmp.replaceAll("\'","&#39;");
		tmp = tmp.replaceAll("\"", "&#34;");
		tmp = tmp.replaceAll("<", "&lt;");
		tmp = tmp.replaceAll(">", "&gt;");
		tmp = tmp.replaceAll("&", "&amp;");

		return tmp;
	}
	DataTable dt=new DataTable();
	String state="";
	String flag="1";
	String  _year="";
    String  _popedom="";
    String  _transact="";
    String  _month="";
    String  _start="";
    String  _end="";
    String  _quarter="";
    String  _xun="";
    String  _week;
    String  _rq="";
    String  mode="";
%>
<%
	WebAuth auth = WebAuth.getInstance(request);
	
    if ( ! auth.IsAuthed() ) {
	    out.write("<script language=\"javascript\">self.parent.location.replace(\"" + request.getContextPath() + "/view/login/login.jsp" + "\");</script>");
	    return;
    }

	   // 获取参数
       Account_Stat AccS=new Account_Stat();
	   String _state=request.getParameter("state");
	   String _state1=request.getParameter("_state");
	   String citycode=request.getParameter("citycode");
	   String areacode=request.getParameter("areacode");
	   String year=request.getParameter("year");
       String quarter=request.getParameter("quarter");
       String month=request.getParameter("month");
       String xun=request.getParameter("xun");
       String week=request.getParameter("week");
       String rq=request.getParameter("rq");
       String relationproduct=request.getParameter("relationproduct");
       if(areacode==null||areacode.equals("")||areacode.equals("null")){areacode="";}
       if(citycode==null||citycode.equals("")||citycode.equals("null")){citycode="";}
       if(year==null||year.equals("")||year.equals("null")){year="";}
       if(quarter==null||quarter.equals("")||quarter.equals("null")){quarter="";}
       if(month==null||month.equals("")||month.equals("null")){month="";}
       if(xun==null||xun.equals("")||xun.equals("null")){xun="";}
       if(rq==null||rq.equals("")||rq.equals("null")){rq="";}
       if(week==null||week.equals("")||week.equals("null")){week="";}
       if(relationproduct==null||relationproduct.equals("")||relationproduct.equals("null")){relationproduct="";}
	   String start=request.getParameter("start");
	   String end=request.getParameter("end");
	   String pageNo=request.getParameter("PageNo");
	   if(start==null || start=="")start="";
	   if(end==null || end=="")end="";
	   if(pageNo==null || pageNo=="")pageNo="1";
	   
	StringBuffer buffer = new StringBuffer();
	try {
	
		
	if(!year.equals("")){_year=year+"年";}
    if(!quarter.equals("")){_quarter="第"+quarter+"季度";}
    if(!month.equals("")){_month="第"+month+"月";}
    if(!xun.equals("")){
    if(xun.equals("1")){_xun="上旬";}
    if(xun.equals("2")){_xun="中旬";}
    if(xun.equals("3")){_xun="下旬";}
    }
    if(!week.equals("")){
    if(week.equals("1")){_week="第一周";}
    if(week.equals("2")){_week="第二周";}
    if(week.equals("3")){_week="第三周";}
    if(week.equals("4")){_week="第四周";}
    }
    if(!rq.equals("")){_rq=rq.substring(0,4)+"年"+rq.substring(5,7)+"月"+rq.substring(8,10)+"日";}
    if(!start.equals("")){_start=start+"--";}
    if(!end.equals("")){_end=end;}
    mode=_year+_quarter+_month+_xun+_week+_rq+_start+_end;
	
	
		StringBuffer content = new StringBuffer();
			
			content.append("<ss:Row>\n");
			content.append("<ss:Cell ss:StyleID=\"wc0CEFE34C\"><ss:Data ss:Type=\"String\">帐号</ss:Data>\n</ss:Cell>\n");
			if(_state.equals("E")){content.append("<ss:Cell ss:StyleID=\"wc0CEFE34C\"><ss:Data ss:Type=\"String\">注册时间</ss:Data>\n</ss:Cell>\n");}
			if(_state.equals("D")){content.append("<ss:Cell ss:StyleID=\"wc0CEFE34C\"><ss:Data ss:Type=\"String\">注销时间</ss:Data>\n</ss:Cell>\n");}
			content.append("<ss:Cell ss:StyleID=\"wc0CEFE34C\"><ss:Data ss:Type=\"String\">状态</ss:Data>\n</ss:Cell>\n");
			content.append("<ss:Cell ss:StyleID=\"wc0CEFE34C\"><ss:Data ss:Type=\"String\">用户名</ss:Data>\n</ss:Cell>\n");
			content.append("<ss:Cell ss:StyleID=\"wc0CEFE34C\"><ss:Data ss:Type=\"String\">电子邮件</ss:Data>\n</ss:Cell>\n");
			content.append("<ss:Cell ss:StyleID=\"wc0CEFE34C\"><ss:Data ss:Type=\"String\">密码问题</ss:Data>\n</ss:Cell>\n");
			content.append("<ss:Cell ss:StyleID=\"wc0CEFE34C\"><ss:Data ss:Type=\"String\">答案</ss:Data>\n</ss:Cell>\n");
			content.append("<ss:Cell ss:StyleID=\"wc0CEFE34C\"><ss:Data ss:Type=\"String\">统计时间</ss:Data>\n</ss:Cell>\n");
			content.append("</ss:Row>\n");
			
		 try{
		 
		 if(!citycode.equals("")){
		  dt=AccS.getlist(_state,_state1,citycode,pageNo,year,quarter,month,xun,week,rq,start,end,flag,relationproduct);
		 }else{
		  dt=AccS.getarealist(_state,_state1,areacode,pageNo,year,quarter,month,xun,week,rq,start,end,flag,relationproduct);
		 }
		 }catch(Exception ex){ex.printStackTrace();};



	
		int   index = 0;
		while ( index < dt.getRows().getCount() ) {
			content.append("<ss:Row>\n");
				
			// 帐号
			content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
			content.append(dt.getRow(index).getString("ACCOUNTID"));
			content.append("</ss:Data></ss:Cell>\n");
				
			// 注册时间,注销时间
			content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
			content.append(getHTMLString(dt.getRow(index).getString("CREATETIME").substring(0,19)));
			content.append("</ss:Data></ss:Cell>\n");

			// 状态
			content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
			if(dt.getRow(index).getString("STATE").equals("E")){state="在用";}else{state="禁用";}
			content.append(state);
			content.append("</ss:Data></ss:Cell>\n");
				
			// 用户名
			content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
			content.append(dt.getRow(index).getString("NAME"));
			content.append("</ss:Data></ss:Cell>\n");
			
			// 电子邮件
			content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
			content.append(dt.getRow(index).getString("EMAIL"));
			content.append("</ss:Data></ss:Cell>\n");
			
			// 密码问题
			content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
			content.append(dt.getRow(index).getString("PWDQUESTION"));
			content.append("</ss:Data></ss:Cell>\n");
			
			// 答案
			content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
			content.append(dt.getRow(index).getString("PWDANSWER"));
			content.append("</ss:Data></ss:Cell>\n");
			
			//统计时间
			content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
			content.append(mode);
			content.append("</ss:Data></ss:Cell>\n");
				
	
			content.append("</ss:Row>\n");
				
			index++;
		}

		buffer.append("<?xml version=\"1.0\"?>\n");
		buffer.append("<ss:Workbook xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\n xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\n xmlns:c=\"urn:schemas-microsoft-com:office:component:spreadsheet\">\n ");
		buffer.append("<x:ExcelWorkbook>\n");
		buffer.append("<x:ProtectStructure>False</x:ProtectStructure>\n");
		buffer.append("<x:ActiveSheet>0</x:ActiveSheet>\n");
		buffer.append("</x:ExcelWorkbook>\n");

		buffer.append("<ss:Styles>\n");
		buffer.append("<ss:Style ss:ID=\"Default\">\n ");
		buffer.append("<ss:Alignment ss:Horizontal=\"Automatic\" ss:Rotate=\"0.0\" ss:Vertical=\"Bottom\"\n ss:ReadingOrder=\"Context\"/>\n");
		buffer.append("<ss:Borders>\n</ss:Borders>\n");
		buffer.append("<ss:Font ss:FontName=\"宋体\" ss:Size=\"10\" ss:Color=\"Automatic\" ss:Bold=\"0\"\n    ss:Italic=\"0\" ss:Underline=\"None\"/>\n");
		buffer.append("<ss:Interior ss:Color=\"Automatic\" ss:Pattern=\"None\"/>\n");
		buffer.append("<ss:NumberFormat ss:Format=\"General\"/>\n");
		buffer.append("<ss:Protection ss:Protected=\"1\"/>\n");
		buffer.append("</ss:Style>\n");
		
		buffer.append("<ss:Style ss:ID=\"wc0CEFE34C\">\n");
		buffer.append("<ss:Alignment ss:Horizontal=\"Center\"/>\n");
		buffer.append("<ss:Font ss:FontName=\"宋体\" ss:Size=\"10\" ss:Bold=\"1\"/>\n");
		buffer.append("</ss:Style>\n");

		buffer.append("</ss:Styles>\n ");
		
		
		buffer.append("<c:ComponentOptions>\n");
		buffer.append("<c:Label>\n");
		buffer.append("<c:Caption>Microsoft Office Spreadsheet</c:Caption>\n");
		buffer.append("</c:Label>\n  ");
		buffer.append("<c:MaxHeight>80%</c:MaxHeight>\n  ");
		buffer.append("<c:MaxWidth>80%</c:MaxWidth>\n  ");
		buffer.append("<c:NextSheetNumber>4</c:NextSheetNumber>\n ");
		buffer.append("</c:ComponentOptions>\n ");
		buffer.append("<x:WorkbookOptions>\n  ");
		buffer.append("<c:OWCVersion>10.0.0.4109</c:OWCVersion>\n");
		buffer.append("<x:Height>7620</x:Height>\n");
		buffer.append("<x:Width>15240</x:Width>\n");
		buffer.append("</x:WorkbookOptions>\n");
		buffer.append("<ss:Worksheet ss:Name=\"工作表\">\n  ");
		buffer.append("<x:WorksheetOptions>\n   ");
		buffer.append("<x:Selected/>\n   ");
		buffer.append("<x:ViewableRange>R1:R262144</x:ViewableRange>\n   ");
		buffer.append("<x:Selection>R1C1</x:Selection>\n   ");
		buffer.append("<x:TopRowVisible>0</x:TopRowVisible>\n ");  
		buffer.append("<x:LeftColumnVisible>0</x:LeftColumnVisible>\n   ");
		buffer.append("<x:ProtectContents>False</x:ProtectContents>\n  ");
		buffer.append("</x:WorksheetOptions>\n  ");
		buffer.append("<c:WorksheetOptions>\n  </c:WorksheetOptions>\n  ");
		buffer.append("<ss:Table ss:DefaultColumnWidth=\"54.0\" ss:DefaultRowHeight=\"12.75\">");
		
		buffer.append("<ss:Column ss:Width=\"54.0\" ss:AutoFitWidth=\"0\"/>");
		buffer.append("<ss:Column ss:Width=\"120.0\" ss:AutoFitWidth=\"0\"/>");
		buffer.append("<ss:Column ss:Width=\"54.0\" ss:AutoFitWidth=\"0\"/>");
		buffer.append("<ss:Column ss:Width=\"100.0\" ss:AutoFitWidth=\"0\"/>");
		buffer.append("<ss:Column ss:Width=\"250.0\" ss:AutoFitWidth=\"0\"/>");
		
		buffer.append(content);
		
		buffer.append("</ss:Table>\n</ss:Worksheet>\n");
		buffer.append("</ss:Workbook>\n");

		ExtString.replace(buffer, "&", "&amp;");
		ExtString.replace(buffer, "<", "&lt;");
		ExtString.replace(buffer, ">", "&gt;");
		ExtString.replace(buffer, "\"", "&quot;");
		
		//response.reset();
		//response.setContentType("application/vnd.ms-excel; charset=gbk");

		//out.print(buffer);
		//out.close();
		//return ;
	}
	catch(Exception ex){
		FileLogger.getLogger().warn(ex.getMessage(),ex);
	}
%>
<html>
<head>
    <meta content="text/html; charset=GB18030" http-equiv="content-type"/>
    <title>注册帐号统计导出EXCEL</title>
</head>

<body style="margin: 0px">
	<object width="100%" height="100%" classid="CLSID:0002E551-0000-0000-C000-000000000046" codebase="/manager/owc11.exe">
		<param name="DataType" value="XMLDATA">
		<param name="XMLData" value="<%= buffer%>">
		<table width='100%' cellpadding='0' cellspacing='0' border='0' height='8'>
			<tr>
				<td bgColor='#336699' height='25' width='10%'>&nbsp;</td>
				<td bgColor='#666666'width='85%'>
					<font face='宋体' color='white' size='4'>
						<b>&nbsp; 缺少 Microsoft Office Web Components</b>
					</font>
				</td>
			</tr>
			<tr>
				<td bgColor='#cccccc' width='15'>&nbsp;</td>
				<td bgColor='#cccccc' width='500px'><br> 
					<font face='宋体' size='2'>此网页要求 Microsoft Office Web Components。
					<p align='center'> 一般装完Office后会自动安装该组件.</p></font><p><font face='宋体' size='2'>此网页同时要求 Microsoft Internet Explorer 5.01 或更高版本。</p><p align='center'><a href='http://www.microsoft.com/windows/ie/default.htm'> 单击此处安装最新的 Internet Explorer</a>.</font><br>&nbsp;</td></tr></table>
	</object>

</body>

</html>
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -