📄 exportexcel.jsp
字号:
<%@ page language="java" contentType="text/html; charset=GB2312" %>
<%@ page import="com.doone.data.*"%>
<%@ page import="com.doone.util.*"%>
<%@ page import="com.doone.uurm.*"%>
<%@ page import="com.doone.fj1w.fj1w.faq.*"%>
<%@ page import="java.util.*"%>
<%!
String getHTMLString(String tmp) {
tmp = tmp.replaceAll("\'","'");
tmp = tmp.replaceAll("\"", """);
tmp = tmp.replaceAll("<", "<");
tmp = tmp.replaceAll(">", ">");
tmp = tmp.replaceAll("&", "&");
return tmp;
}
%>
<%
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;
}
// 获取参数
String sFaqCoteID = request.getParameter("faqcoteid");
String openMode = request.getParameter("OPENMODE");
/** add by chenjq*/
//获取条件参数
String starttime=request.getParameter("STARTTIME");//开始时间
String endtime=request.getParameter("ENDTIME");//结束时间
String citycode=request.getParameter("CITY");//地市编号
String area=request.getParameter("AREA");//区域
String custname=request.getParameter("CUSTNAME");//客户名称
String title=request.getParameter("TITLE");//标题
String isstate=request.getParameter("STATE");//状态
/**end add*/
if ( sFaqCoteID == null) {
out.write("<script language=javascript>window.location.href='"+request.getContextPath() + "/view/sysmgr/OperFailure.jsp?error=参数不正确';</script>");
return;
}
StringBuffer buffer = new StringBuffer();
try {
//构造sql语句
StringBuffer sql = new StringBuffer();
Object[] ap = null;
List _linkList=new LinkedList();
DacClient db = new DacClient();
/** modify by chenjq*/
//加上 and rownum=1 为了避免以前可支持多支回复时出现bug,现在只能支持一条回复
sql.append("Select *")
.append(" From (Select c.cityname,b.areaname,f.title,f.createtime,f.nickname,f.content,f.usertype,")
.append(" c.citycode,f.lastdate,f.faqid,f.upfaqid,")
.append(" (select content from tf_faq where upfaqid=f.faqid and rownum=1)as replycontent,")
.append(" (select userid from tf_faq where upfaqid=f.faqid and rownum=1)as replyuserid,")
.append(" (select nickname from tf_faq where upfaqid=f.faqid and rownum=1)as replynickname,")
.append(" (select createtime from tf_faq where upfaqid=f.faqid and rownum=1)as replytime")
.append(" From (Select * From Tf_Faq")
.append(" Where State = 'E'");
//栏目分类
if (sFaqCoteID != null && sFaqCoteID.trim().length() != 0
&& sFaqCoteID != "null") {
sql.append(" and faqcoteid = ?");
_linkList.add(sFaqCoteID);
//System.out.println("sFaqCoteID"+"="+sFaqCoteID);
}
sql.append(" ) f,");
sql.append(" td_city c, td_area b, tf_custinfoweb u");
sql.append(" where f.citycode = c.citycode and f.userid = u.userid ");
sql.append("and decode(u.areacode,null,u.citycode||'00',u.areacode) = b.areacode and f.upfaqid=0");
//地区
if (citycode != null && !citycode.equals("")
&& !citycode.equals("0590")) {
sql.append(" and c.citycode=?");
_linkList.add(citycode);
//System.out.println("citycode"+"="+citycode);
//区域
if (area != null && area.trim().length() != 0 && area != "null") {
sql.append(" and decode(u.areacode,null,u.citycode||'00',u.areacode)=?");
_linkList.add(area);
//System.out.println("area"+"="+area);
}
}
// 客户名称
if (custname != null && custname.trim().length() != 0
&& custname != "null") {
sql.append(" and f.NICKNAME like ?");
_linkList.add("%" + custname.trim() + "%");
//System.out.println("custname"+"="+custname);
}
// 标题
if (title != null && title.trim().length() != 0) {
sql.append(" and f.TITLE like ?");
_linkList.add("%" + title.trim() + "%");
//System.out.println("title"+"="+title);
}
//未回复：1、已回复：2
if (isstate != null && isstate.trim().length() != 0
&& isstate != "null") {
//System.out.println("isstate"+"="+isstate);
if (isstate.equals("1")) {
sql.append(" and f.SUBCOUNT=0");
} else if (isstate.equals("2")) {
sql.append(" and f.SUBCOUNT!=0");
}
}
//开始时间
if (starttime != null && starttime.trim().length() != 0
&& starttime != "null") {
sql.append(" and f.createtime >= to_date(?,'YYYY-MM-DD HH24:MI:SS')");
//System.out.println("starttime"+"="+starttime);
_linkList.add(starttime);
}
// 结束时间
if (endtime != null && endtime.trim().length() != 0
&& endtime != "null") {
sql .append(" and f.createtime <= to_date(?,'YYYY-MM-DD HH24:MI:SS')");
//System.out.println("endtime"+"="+endtime);
_linkList.add(endtime);
}
sql.append(" ORDER BY citycode,createtime desc,lastdate desc)");
sql.append(" connect by prior faqid = upfaqid start with upfaqid = 0");
System.out.println(sql.toString());
DataTable dt = db.executeQuery(sql.toString(),_linkList.toArray());
//System.out.println(dt.getRows().getCount());
StringBuffer content = new StringBuffer();
if ( openMode == null) {
if ( sFaqCoteID.equals("1") ) {
if ( ! auth.CheckPurv("NGB009001") ) {
out.write("<script language=javascript src=\"../../common/script/RightForbidden.js\"></script>");
return;
}
content.append("<ss:Row>\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: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");
}
else if ( sFaqCoteID.equals("2") ) {
if ( ! auth.CheckPurv("NGB009002") ) {
out.write("<script language=javascript src=\"../../common/script/RightForbidden.js\"></script>");
return;
}
content.append("<ss:Row>\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: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");
}
else {
out.write("<script language=javascript>window.location.href='"+request.getContextPath() + "/view/sysmgr/OperFailure.jsp?error=参数不正确';</script>");
return;
}
for(int index=0;index<dt.getRows().getCount();index++){
content.append("<ss:Row>\n");
// 城市名称
content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
content.append(dt.getRow(index).getString("cityname"));
content.append("</ss:Data></ss:Cell>\n");
// 区域名称
content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
content.append(dt.getRow(index).getString("areaname"));
content.append("</ss:Data></ss:Cell>\n");
// 标题
content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
content.append(getHTMLString(dt.getRow(index).getString("title")));
content.append("</ss:Data></ss:Cell>\n");
// 发表时间
content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
content.append(dt.getRow(index).getString("lastdate"));
content.append("</ss:Data></ss:Cell>\n");
// 发表人
content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
content.append(dt.getRow(index).getString("nickname"));//昵称
content.append("</ss:Data></ss:Cell>\n");
//发表内容
content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
content.append(getHTMLString(dt.getRow(index).getString("content")));
content.append("</ss:Data></ss:Cell>\n");
//回复内容
content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
content.append(getHTMLString(dt.getRow(index).getString("replycontent")));
content.append("</ss:Data></ss:Cell>\n");
//回复员工工号
content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
content.append(dt.getRow(index).getString("replyuserid"));
content.append("</ss:Data></ss:Cell>\n");
//回复员工姓名
content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
content.append(dt.getRow(index).getString("replynickname"));
content.append("</ss:Data></ss:Cell>\n");
//回复时间
content.append("<ss:Cell><ss:Data ss:Type=\"String\">");
content.append(dt.getRow(index).getString("replytime"));
content.append("</ss:Data></ss:Cell>\n");
content.append("</ss:Row>\n");
}
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");
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -