泛微Excel文件导出
说明
这个功能是基于泛微8.0平台，以js代码，调用后端jsp动态导出Excel文件。
前端代码
前端页面现有的id和name标准规格、配置页面：
js代码：
//明细1导出
var str1="<input type='button' class='e8_btn_top_first' οnclick='getDetail(1);' value='导出明细' style='font-size:18px; height:30px; width:124px;'>";
jQuery(".mx_botton1").html(str1);
function getDetail(tableNum){
var requestId=jQuery("#requestid").val();
if(!!requestId){
var fileName="执行预算申请";
var columns="yskm,fl,ndyskyje,ndysysq,jtsx,kszxrq,jszxrq,dj1,yhzk,zhdj,sl2,dw,xjy,yfbl,yfk,hzdw,bz"; // 注意点：这里字段的顺序就是你导出excel文件字段的顺序。
location.href="/path/FL_ExcelExportDetailUtil.jsp?requestId="+requestId+"&fileName="+fileName+"&tableNum="+tableNum+"&columns="+columns;
}else{
window.top.Dialog.alert("请先保存！");
}
}
js代码注意点：
后端代码
jsp代码获取到数据并把数据以Excel形式表现出来
把html页面以EXCEL形式显示时：
<%@ page language="java" contentType="application/vnd.ms-excel; charset=UTF-8" %>
关键代码：response.setHeader("Content-disposition","attachment;filename="+new String(fileName.getBytes("GBK"),"iso8859-1")+requestmark+".xls");
// 把xls文件信息，把响应头的格式存进去。
FL_ExcelExportDetailUtil.jsp文件
<%@page import="weaver.general.BaseBean"%>
<%@ page language="java" contentType="application/vnd.ms-excel; charset=UTF-8" %>
<%@ page import="weaver.systeminfo.*,java.util.*,weaver.hrm.*" %>
<%@ page import="weaver.interfaces.workflow.action.GetTextByColumn" %>
<%@ page import="weaver.general.Util,weaver.hrm.common.*" %>
<%@ page import="weaver.general.TimeUtil" %>
<%@ page import="weaver.conn.RecordSet" %>
<!-- modified by wcd 2014-07-24 [E7 to E8] -->
<jsp:useBean id="ResourceComInfo" class="weaver.hrm.resource.ResourceComInfo" scope="page"/>
<jsp:useBean id="DepartmentComInfo" class="weaver.hrm.company.DepartmentComInfo" scope="page"/>
<jsp:useBean id="format" class="weaver.hrm.common.SplitPageTagFormat" scope="page"/>
<jsp:useBean id="strUtil" class="weaver.common.StringUtil" scope="page"/>
<jsp:useBean id="dateUtil" class="weaver.common.DateUtil" scope="page"/>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<style>
<!--
td{font-size:12px}
.title{font-weight:bold;font-size:20px}
requestId="+requestId+"&fileName="+fileName+"&tableNum="+tableNum+"&columns="+columns;
-->
</style>
<%
BaseBean bas=new BaseBean();
RecordSet rs=new RecordSet();
RecordSet rs1=new RecordSet();
GetTextByColumn gtbc=new GetTextByColumn();
List<Map<String,String>> detailMap=new ArrayList<Map<String,String>>();
String requestid = Util.null2String(request.getParameter("requestId"));
String tableNum = Util.null2String(request.getParameter("tableNum"));
String columns = Util.null2String(request.getParameter("columns"));
String fileName = Util.null2String(request.getParameter("fileName"));
bas.writeLog("参数：requestid="+requestid+"，明细表="+tableNum);
String[] fielnames=columns.split("\\,");
//根据requestId获取表单名称 // 表单名称、工作流id、文件编号
String sql1="select tablename,c.workflowid,c.requestmark from workflow_bill a inner join workflow_base b on a.id=b.formid ,"
+"workflow_requestbase c where b.id=c.workflowid and c.requestid='"+requestid+"'";
rs.executeSql(sql1);
rs.next();
String mainTable=Util.null2String(rs.getString(1));//表单名称
String workflowId=Util.null2String(rs.getString(2));//流程ID
String requestmark = Util.null2String(rs.getString(3));//流程编号
//获得明细表数据
if(tableNum.length()>0){
String detailTable=mainTable+"_dt"+tableNum;//tableNum的值：1,2,3......N
String sql2="select * from "+detailTable+" a, "+mainTable+" b where a.mainid=b.id and b.requestid='"+requestid+"' order by a.id";
bas.writeLog("获取明细数据sql:"+sql2);
rs.executeSql(sql2);
int index=0;
while(rs.next()){
Map<String,String> map=new LinkedHashMap<String, String>();
map.put("序号", index+1+"");//序号
for(String fielname:fielnames){
String value=Util.null2String(rs.getString(fielname));// 用表格头的名字获取到数据
String sql3="select h.labelname from workflow_billfield t,workflow_base w,HtmlLabelInfo h where w.formid = t.billid "
+"and t.fieldlabel = h.indexid and h.languageid = 7 and t.fieldname='"+fielname+"' and w.id = '"+workflowId+"'";
rs1.executeSql(sql3);
rs1.next();
String columnName=Util.null2String(rs1.getString(1));
String val=gtbc.getData(workflowId, fielname, detailTable, value);
map.put(columnName, val);
}
detailMap.add(index,map);
index++;
}
}
String cmd="DSproductModelExcel";
bas.writeLog("打印集合"+detailMap.toString());
response.setHeader("Content-disposition","attachment;filename="+new String(fileName.getBytes("GBK"),"iso8859-1")+requestmark+".xls");
%>
<%
if(cmd.equals("DSproductModelExcel")){
%>
<input type="hidden" id="page" name="page" value="">
<table border=1 bordercolor=black style="border-collapse:collapse;" width="100%" >
<COLGROUP>
<% // 循环多少列数并定下宽度
Map<String,String> map1=detailMap.get(0);
int k=0;
for(String key:map1.keySet()){
%>
<COL width="7%">
<%
bas.writeLog("循环COL"+k+1);
k++;
}
%>
</COLGROUP>
<tbody>
<tr style="background-color:#999999">
<%
int h=0;
for(String key:map1.keySet()){
%>
<td style="HEIGHT: 38px" rowspan=1 align="center"><%=key%></td>
<%
bas.writeLog("循环表头td"+key);
h++;
}
%>
</tr>
<%
for(int i=0;i<detailMap.size();i++){
Map<String,String> map2=detailMap.get(i);
int j=0;
%>
<tr>
<%
bas.writeLog("循环tr"+i+1);
for(String key:map2.keySet()){
String value=map2.get(key);
%>
<td align="center"><%=value%></td>
<%
bas.writeLog("循环表体td"+value);
j++;
}
%>
</tr>
<%
}
%>
</tbody>
</table>
<%
}
%>
GetTextByColumn.java文件
package weaver.interfaces.workflow.action;
import weaver.conn.RecordSet;
import weaver.general.BaseBean;
import weaver.general.Util;
/**
* 根据字段和值获取Text输出
* @date 2018年11月28日
*/
public class GetTextByColumn extends BaseBean {
public String getData(String workflowId,String column,String dt,String value) {
RecordSet rs = new RecordSet();
String result="";
String sql0="";
if(value.length()>0) {
String SQL="select b.fieldname ,b.id ,h.labelname , b.fielddbtype ,b.detailtable,b.fieldhtmltype,b.type "
+"from workflow_billfield b,workflow_base w,HtmlLabelInfo h "
+"where w.formid = b.billid and b.fieldlabel = h.indexid and h.languageid = 7 and w.id = '"+workflowId+"' "
+"and b.fieldname='"+column+"' ";
if(dt.length()>0) {//明细表
sql0 ="and b.detailtable='"+dt+"'";
}else {//主表
sql0 ="and b.detailtable is null";
}
rs.executeSql(SQL+sql0);
rs.next();
String fieldname=Util.null2String(rs.getString("fieldname"));
String fieldid=Util.null2String(rs.getString("id"));
String labelname=Util.null2String(rs.getString("labelname"));
String fielddbtype=Util.null2String(rs.getString("fielddbtype"));
String fieldhtmltype=Util.null2String(rs.getString("fieldhtmltype"));
String type=Util.null2String(rs.getString("type"));
if(fieldhtmltype.equals("1")||fieldhtmltype.equals("2")) {//单行文本、多行文本
result=value;
}else if(fieldhtmltype.equals("3")) {//浏览按钮
if(type.equals("1")) {//人力资源
String sql1="select lastname from hrmresource where id='"+value+"'";
rs.execute(sql1);
rs.next();
result=Util.null2String(rs.getString(1));
}else if(type.equals("2")) {//日期
result=value;
}else if(type.equals("3")) {//会议室联系单
}else if(type.equals("4")) {//部门
String sql1="select departmentname from hrmdepartment where id='"+value+"'";
rs.execute(sql1);
rs.next();
result=Util.null2String(rs.getString(1));
}else if(type.equals("5")) {//仓库
}else if(type.equals("6")) {//成本中心
String sql1="select name from fnaCostCenter where id='"+value+"'";
rs.execute(sql1);
rs.next();
result=Util.null2String(rs.getString(1));
}else if(type.equals("7")) {//客户
}else if(type.equals("8")) {//项目
}else if(type.equals("9")) {//文档
String sql="select a.imagefilename,a.filesize,a.imagefileid,b.docfiletype from imagefile a , docimagefile b where b.imagefileid=a.imagefileid and b.docid=";
if(value.length()>0) {
String[] ids=value.split("\\,");
for(String id:ids) {
rs.executeSql(sql+id);
rs.next();
String fileName=Util.null2String(rs.getString("imagefilename"));
result +=result.length()>0?"</br>"+fileName:fileName;
}
}
}else if(type.equals("10")) {//入库方式
}else if(type.equals("11")) {//出库方式
}else if(type.equals("12")) {//币种
String sql1="select currencyname from fnacurrency where id='"+value+"'";
rs.execute(sql1);
rs.next();
result=Util.null2String(rs.getString(1));
}else if(type.equals("13")) {//资产种类
}else if(type.equals("14")) {//科目－全部
}else if(type.equals("15")) {//科目－明细
}else if(type.equals("16")) {//流程
String sql1="select requestname from workflow_requestbase where requestid='"+value+"'";
rs.execute(sql1);
rs.next();
result=Util.null2String(rs.getString(1));
}else if(type.equals("17")) {//多人力资源
String sql1="select lastname from hrmresource where id in ("+value+")";
rs.execute(sql1);
while(rs.next()) {
String val=Util.null2String(rs.getString(1));
result +=result.length()>0?"，"+val:val;
}
}else if(type.equals("18")) {//多客户
}else if(type.equals("19")) {//时间
result=value;
}else if(type.equals("20")) {//计划类型
}else if(type.equals("21")) {//计划种类
}else if(type.equals("22")) {//报销费用类型
String sql1="select name from fnabudgetfeetype where id='"+value+"'";
rs.execute(sql1);
rs.next();
result=Util.null2String(rs.getString(1));
}else if(type.equals("23")) {//资产
}else if(type.equals("24")) {//职务/岗位
String sql1="select jobtitlename from hrmjobtitles where id='"+value+"'";
rs.execute(sql1);
rs.next();
result=Util.null2String(rs.getString(1));
}else if(type.equals("25")) {//资产组
}else if(type.equals("26")) {//车辆
}else if(type.equals("27")) {//应聘人
}else if(type.equals("28")) {//会议
}else if(type.equals("29")) {//奖惩种类
}else if(type.equals("30")) {//学历
}else if(type.equals("31")) {//用工性质
}else if(type.equals("32")) {//培训安排
}else if(type.equals("33")) {//加班类型
}else if(type.equals("34")) {//请假类型
}else if(type.equals("35")) {//业务合同
}else if(type.equals("36")) {//合同性质
}else if(type.equals("37")) {//多文档
String sql="select a.imagefilename,a.filesize,a.imagefileid,b.docfiletype from imagefile a , docimagefile b where b.imagefileid=a.imagefileid and b.docid=";
if(value.length()>0) {
String[] ids=value.split("\\,");
for(String id:ids) {
rs.executeSql(sql+id);
rs.next();
String fileName=Util.null2String(rs.getString("imagefilename"));
result +=result.length()>0?"</br>"+fileName:fileName;
}
}
}else if(type.equals("38")) {//相关产品
}else if(type.equals("52")) {//公文种类
}else if(type.equals("53")) {//紧急程度
}else if(type.equals("54")) {//秘密等级
}else if(type.equals("55")) {//发文字号
}else if(type.equals("56")) {//
}else if(type.equals("57")) {//多部门
String sql1="select departmentname from hrmdepartment where id in("+value+")";
rs.execute(sql1);
while(rs.next()) {
String val=Util.null2String(rs.getString(1));
result +=result.length()>0?"，"+val:val;
}
}else if(type.equals("58")) {//城市
String sql1="select cityname from hrmcity where id='"+value+"'";
rs.execute(sql1);
rs.next();
result=Util.null2String(rs.getString(1));
}else if(type.equals("59")) {//称呼
}else if(type.equals("60")) {//客户类型
}else if(type.equals("61")) {//客户描述
}else if(type.equals("62")) {//客户规模
}else if(type.equals("63")) {//行业
}else if(type.equals("64")) {//
}else if(type.equals("65")) {//多角色
String sql1="select rolesmark from hrmroles where id in ("+value+")";
rs.execute(sql1);
while(rs.next()) {
String val=Util.null2String(rs.getString(1));
result +=result.length()>0?"，"+val:val;
}
}else if(type.equals("66")) {//
}else if(type.equals("67")) {//
}else if(type.equals("68")) {//
}else if(type.equals("69")) {//计量单位
String sql1="select unitname from lgcassetunit where id ='"+value+"'";
rs.execute(sql1);
rs.next();
result=Util.null2String(rs.getString(1));
}else if(type.equals("87")) {//会议室
}else if(type.equals("89")) {//会议类型
}else if(type.equals("118")) {//
}else if(type.equals("119")) {//专业
}else if(type.equals("124")) {//
}else if(type.equals("125")) {//
}else if(type.equals("126")) {//
}else if(type.equals("129")) {//项目模板
}else if(type.equals("134")) {//
}else if(type.equals("135")) {//多项目
}else if(type.equals("137")) {//车辆
}else if(type.equals("141")) {//人力资源条件
}else if(type.equals("142")) {//收（发）文单位
}else if(type.equals("152")) {//多流程
String sql1="select requestname from workflow_requestbase where requestid in ("+value+")";
rs.execute(sql1);
while(rs.next()) {
String val=Util.null2String(rs.getString(1));
result +=result.length()>0?"</br>"+val:val;
}
}else if(type.equals("160")) {//角色人员
String sql1="select lastname from hrmresource where id in ("+value+")";
rs.execute(sql1);
while(rs.next()) {
String val=Util.null2String(rs.getString(1));
result +=result.length()>0?"，"+val:val;
}
}else if(type.equals("161")) {//自定义单选
String unitname=fielddbtype.split("\\.")[1];
String sql1="select searchbyid from datashowset where showname='"+unitname+"'";
rs.executeSql(sql1);
rs.next();
String sql2=Util.null2String(rs.getString("searchbyid"));
rs.executeQuery(sql2, new Object[] {value});
rs.next();
result=Util.null2String(rs.getString(1));
}else if(type.equals("162")) {//自定义多选
String unitname=fielddbtype.split("\\.")[1];
String sql1="select searchbyid from datashowset where showname='"+unitname+"'";
rs.executeSql(sql1);
rs.next();
String sql2=Util.null2String(rs.getString("searchbyid"));
String[] ids=value.split("\\,");
for(String id:ids) {
rs.executeQuery(sql2, new Object[] {id});
rs.next();
String val=Util.null2String(rs.getString(1));
result +=result.length()>0?"，</br>"+val:val;
}
}else if(type.equals("164")) {//公司
String sql1="select subcompanyname from hrmsubcompany where id='"+value+"'";
rs.execute(sql1);
rs.next();
result=Util.null2String(rs.getString(1));
}else if(type.equals("165")) {//分权单人力资源
}else if(type.equals("166")) {//分权多人力资源
}else if(type.equals("167")) {//分权单部门
}else if(type.equals("168")) {//分权多部门
}else if(type.equals("169")) {//分权单分部
}else if(type.equals("170")) {//分权多分部
}else if(type.equals("171")) {//归档流程
String sql1="select requestname from workflow_requestbase where requestid='"+value+"'";
rs.execute(sql1);
rs.next();
result=Util.null2String(rs.getString(1));
}else if(type.equals("178")) {//年份
result=value;
}else if(type.equals("179")) {//资产资料
}else if(type.equals("182")) {//单网上调查
}else if(type.equals("184")) {//多会议室
}else if(type.equals("194")) {//多分部
String sql1="select subcompanyname from hrmsubcompany where id in ("+value+")";
rs.execute(sql1);
while(rs.next()) {
String val=Util.null2String(rs.getString(1));
result +=result.length()>0?"，"+val:val;
}
}else if(type.equals("226")) {//系统集成单选浏览按钮
}else if(type.equals("244")) {//项目类型
}else if(type.equals("245")) {//工作类型
}else if(type.equals("251")) {//项目名称
String sql1="select name from fnaCostCenter where id='"+value+"'";
rs.execute(sql1);
rs.next();
result=Util.null2String(rs.getString(1));
}else if(type.equals("256")) {//自定义树形单选
String mainid=value.split("_")[0];
String detailid=value.split("_")[1];
String sql1="select tablename,showfield from mode_customtreedetail where mainid='"+mainid+"'";
rs.execute(sql1);
rs.next();
String tabl=Util.null2String(rs.getString("tablename"));
String showfield=Util.null2String(rs.getString("showfield"));
String sql2="select "+showfield+" from "+tabl+" where id='"+value+"'";
rs.executeSql(sql2);
rs.next();
result=Util.null2String(rs.getString(1));
}else if(type.equals("257")) {//自定义树形多选
String[] ids=value.split("\\,");
for(String id:ids) {
String mainid=id.split("_")[0];
String detailid=id.split("_")[1];
String sql1="select tablename,showfield from mode_customtreedetail where mainid='"+mainid+"'";
rs.execute(sql1);
rs.next();
String tabl=Util.null2String(rs.getString("tablename"));
String showfield=Util.null2String(rs.getString("showfield"));
String sql2="select "+showfield+" from "+tabl+" where id='"+id+"'";
rs.executeSql(sql2);
rs.next();
String val=Util.null2String(rs.getString(1));
result +=result.length()>0?"，"+val:val;
}
}else if(type.equals("259")) {//语言
}else if(type.equals("260")) {//职称
}else if(type.equals("262")) {//办公地点
}else if(type.equals("263")) {//区县
}else if(type.equals("268")) {//星期多选
}else if(type.equals("269")) {//多提醒方式
}else if(type.equals("270")) {//服务项目
}else if(type.equals("274")) {//商机来源
}else if(type.equals("278")) {//多岗位
String sql1="select jobtitlename from hrmjobtitles where id in ("+value+")";
rs.execute(sql1);
while(rs.next()) {
String val=Util.null2String(rs.getString(1));
result=result.length()>0?"，"+val:val;
}
}else if(type.equals("279")) {//合同
}else if(type.equals("280")) {//班次
}
}else if(fieldhtmltype.equals("4")) {//Check框
result=value;
}else if(fieldhtmltype.equals("5")) {//选择框
String sql8="select a.selectname from WORKFLOW_SELECTITEM a LEFT JOIN WORKFLOW_BILLFIELD b on b.id=a.fieldid ,WORKFLOW_BASE c"
+ " where b.billid=c.formid and b.fieldhtmltype=5 and b.fieldname='"+column+"' and a.selectvalue='"+value+"' "
+ "and c.id='"+workflowId+"'";
rs.executeSql(sql8+sql0);
rs.next();
result=Util.null2String(rs.getString(1));
}else if(fieldhtmltype.equals("6")) {//附件
String sql9="select a.imagefilename,a.filesize,a.imagefileid,b.docfiletype from imagefile a , docimagefile b where b.imagefileid=a.imagefileid and b.docid=";
if(value.length()>0) {
String[] ids=value.split("\\,");
for(String id:ids) {
rs.executeSql(sql9+id);
rs.next();
String fileName=Util.null2String(rs.getString("imagefilename"));
result +=result.length()>0?"</br>"+fileName:fileName;
}
}
}
}
return result;
}
}
下面是查询的sql
select tablename,c.workflowid,c.requestmark from workflow_bill a inner join workflow_base b on a.id=b.formid ,
workflow_requestbase c where b.id=c.workflowid and c.requestid=311305
-- 表单名称、工作流id、文件编号
--获取明细数据sql:
select * from formtable_main_31_dt1 a, formtable_main_31 b where a.mainid=b.id and b.requestid='312081' order by a.id
-- 查询字段 显示名
select h.labelname from workflow_billfield t,workflow_base w,HtmlLabelInfo h where w.formid = t.billid
and t.fieldlabel = h.indexid and h.languageid = 7 and t.fieldname='yskm' and w.id = 2201
-- 获取到 字段名，字段id，字段显示名，字段类型，表单名，字段类型，字段类型
select b.fieldname ,b.id ,h.labelname , b.fielddbtype ,b.detailtable,b.fieldhtmltype,b.type
from workflow_billfield b,workflow_base w,HtmlLabelInfo h
where w.formid = b.billid and b.fieldlabel = h.indexid and h.languageid = 7 and w.id = '2201'
and b.fieldname='yskm'
--and b.detailtable is null
and b.detailtable = 'formtable_main_31_dt1'
741
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
