服务端源码
public JSONArray getListToDeptPart() throws Exception {
JSONArray jsonArr = new JSONArray();
List<Map<String,Object>> theLastList = new ArrayList<Map<String,Object>>();
String queryUnitNameSql = "SELECT UNIT_NAME FROM ( SELECT UNIT_TYPE FROM T_SECRECY_DEPARTMENT UNION SELECT UNIT_TYPE FROM T_SECRECY_PART ) CC JOIN T_UNIT_NAME TU ON CC.UNIT_TYPE = TU.UNIT_NAME_ID";
List<Map<String, Object>> unitNameList = dataBaseService.query(queryUnitNameSql);
if(unitNameList.size()>0 && !unitNameList.isEmpty()){
for (Map<String, Object> unitMap : unitNameList) {
Map<String,Object> theLastMap = new HashMap<String,Object>();
Map<String,Object> theInnerDeptMap = new HashMap<String,Object>();
Map<String,Object> theInnerPartMap = new HashMap<String,Object>();
Object unitNameObj = unitMap.get("unitName");
String unitName = unitNameObj != null ? unitNameObj.toString() : "";
theLastMap.put("unitName", unitName);
//查要害部门
String majorDeptSql = "SELECT TU.UNIT_NAME AS UNIT_NAME,DEPARTMENT_NAME,OFFICIAL_NAME AS DEPT_OFFICIAL_NAME,SECRECY_PEOPLES AS DEPT_SECRECY_PEOPLES FROM T_SECRECY_DEPARTMENT SD JOIN T_UNIT_NAME TU ON SD.UNIT_TYPE = TU.UNIT_NAME_ID WHERE UNIT_NAME='"+unitName+"' ORDER BY UNIT_NAME";
List<Map<String, Object>> majorDeptList = dataBaseService.query(majorDeptSql);
theInnerDeptMap.put("unitName", unitName);
if(majorDeptList.size()>0){
theInnerDeptMap.put("majorDeptInfo", majorDeptList);
}else{
Map<String,Object> deptIsNullMap = new HashMap<String,Object>();
deptIsNullMap.put("unitName", "");
deptIsNullMap.put("departmentName", "");
deptIsNullMap.put("deptOfficialName", "");
deptIsNullMap.put("deptSecrecyPeoples", 0);
majorDeptList.add(deptIsNullMap);
theInnerDeptMap.put("majorDeptInfo", majorDeptList);
}
//查要害部位 //{unit:[],dept:{unitname:[],unitname:[]},part:{unitname:[],unitname:[]}}
String majorPartSql = "SELECT TU.UNIT_NAME AS UNIT_NAME,PART_NAME,OFFICIAL_NAME AS PART_OFFICIAL_NAME,SECRECY_PEOPLES AS PART_SECRECY_PEOPLES FROM T_SECRECY_PART SP JOIN T_UNIT_NAME TU ON SP.UNIT_TYPE = TU.UNIT_NAME_ID WHERE UNIT_NAME='"+unitName+"' ORDER BY UNIT_NAME";
List<Map<String, Object>> majorPartList = dataBaseService.query(majorPartSql);
theInnerPartMap.put("unitName", unitName);
if(majorPartList.size()>0){
theInnerPartMap.put("majorPartInfo", majorPartList);
}else{
Map<String,Object> partIsNullMap = new HashMap<String,Object>();
partIsNullMap.put("unitName", "");
partIsNullMap.put("partName", "");
partIsNullMap.put("partOfficialName", "");
partIsNullMap.put("partSecrecyPeoples", 0);
majorPartList.add(partIsNullMap);
theInnerPartMap.put("majorPartInfo", majorPartList);
}
theLastMap.put("dept", theInnerDeptMap);
theLastMap.put("part", theInnerPartMap);
theLastList.add(theLastMap);
}
jsonArr = JSONArray.fromObject(theLastList);
}
return jsonArr;
}
页面jsp`
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://" + request.getServerName() + ":" +request.getServerPort() + path + "/"; %>
<link rel="stylesheet" href="../resources/css/bootstrap.min.css" type="text/css" />
<script type="text/javascript" src="../resources/js/jquery-1.11.3.min.js"></script>
<script type="text/javascript" src="../resources/jquery.base64.js"></script>
<script type="text/javascript" src="../resources/respond.min.js"></script>
<script type="text/javascript" src="../resources/js/bootstrap.min.js"></script>
<script type="text/javascript" src="../resources/html5shiv.js"></script>
<script type="text/javascript">
function isIE(){
if(!!window.ActiveXObject || "ActiveXObject" in window)
return true;
else
return false;
}
//通过后台返回的数据组装table
$.post('<%=basePath%>secretmajordeptandpart/list', function(data) {
loadData(data);
});
//base64加密
function base64 (content) {
return $.base64.btoa(unescape(encodeURIComponent(content)));
}
/*
* 导出按钮
*@tableId: table的Id
*@fileName: 要生成excel文件的名字(不包括后缀,可随意填写)
*/
function tableToExcel(tableID,name,fileName){
var table = document.getElementById(tableID);
if(isIE()){
try{
var ExApp = new ActiveXObject("Excel.Application");
}catch(e){
alert("你的电脑没有安装Microsoft Excel软件");
return false;
}
var ExWBk = ExApp.Workbooks.Add();
var ExWSh = ExWBk.ActiveSheet;
var sel =document.body.createTextRange();
sel.moveToElementText(table);
sel.select();
sel.execCommand("Copy");
/* var lenr = table.rows.length;
for(i=0;i<lenr;i++){
var lenc = table.rows(i).cells.length;
for(j=0;j<lenc;j++){
ExWSh.Cells(i+1,j+1).value = table.rows(i).cells(j).innerText;
}
} */
ExWSh.Paste();
ExApp.Visible = true;
try{}catch(e){
alert("导出失败,请检查是否安装Excel");
}finally{}
}else{
var fileName="市直机关单位保密要害部门、部位基本情况统计表"+new Date().format('yyyyMMdd');
var excelContent = table.innerHTML;
var excelFile = "<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>";
excelFile += "<head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head>";
excelFile += '<body><table border="0" cellpadding="0" cellspacing="0" style="font-size:16px;width:100%;border-color: black;border-collapse:collapse;table-layout:fixed;">';
excelFile += '<col span="9" style="mso-width-source:userset;mso-width-alt:2720;"/>';
excelFile += ' <tr>';
excelFile += ' <td colspan="9" rowspan="2" style="font-family:宋体;border-right:none;border-bottom:none;text-align:center;font-size:25px;font-weight:700;">汕头市市直机关单位保密要害部门、部位基本情况</td></tr>';
excelFile += ' <tr>';
excelFile += ' <tr style="mso-height-source:userset;mso-height-alt:540;">';
excelFile += '<td colspan="4" style="font-family:宋体;font-size:15px;border-right:none;border-bottom:none;text-align:left;">单位(盖章):汕头市保密局</td>';
excelFile += '<td colspan="5" style="font-family:宋体;font-size:15px;border-right:none;border-bottom:none;text-align:right;">填表日期:'+new Date().format('yyyy年MM月dd日')+'</td></tr>';
excelFile += excelContent;
excelFile += '<tr><td colspan="9" style="font-family : 宋体;font-size:16px;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">说明:涉密人员总数是指保密要害部门和部位的涉密工作人员总数量;计算公式是:涉密'
+'人员总数=要害部门涉密人员+要害部位涉密人员——所含人数(所含人数指要害部位涉密'+
'人员已含于要害部门涉密人员中)。</td></tr></table></body>';
excelFile += "</html>";
var link = "data:application/vnd.ms-excel;base64," + base64(excelFile);
var a = document.createElement("a");
a.download = fileName+".xls";
a.href = link;
a.click();
}
}
//刷新按钮
function onLoadData(){
$.post('<%=basePath%>secretmajordeptandpart/list', function(data) {
loadData(data);
});
}
//日期格式化
Date.prototype.format= function(formatStr){
var str = formatStr;
var week = ['日','一','二','三','四','五','六'];
str = str.replace(/yyyy|YYYY/,this.getFullYear());
str = str.replace(/MM/,(this.getMonth()+1)>9?(this.getMonth()+1).toString():'0'+(this.getMonth()+1));
str = str.replace(/dd|DD/,this.getDate()>9?this.getDate().toString():'0' + this.getDate());
return str;
}
//加载table数据的方法
function loadData(data){
var strHtml = "";
var jsonObj = JSON.parse(data);
var allDeptTotal=0;
var allPartTotal = 0;
var deptLen = 0;
var partLen = 0;
for (var i = 0; i < jsonObj.length; i++) {
var temp = jsonObj[i];
var majorDept = temp.dept.majorDeptInfo;
var deptSize = majorDept.length;
var majorPart = temp.part.majorPartInfo;
var partSize = majorPart.length;
//算总分
var deptTotal = 0;
var partTotal = 0;
for(var k = 0;k<deptSize;k++){
if(majorDept[k].departmentName){
deptLen = deptLen +1;
}
var deptNum=majorDept[k].deptSecrecyPeoples;
deptTotal += deptNum;
allDeptTotal += deptNum;
}
for(var l = 0;l<partSize;l++){
if(majorPart[l].partName){
partLen = partLen +1;
}
var partNum=majorPart[l].partSecrecyPeoples;
partTotal += partNum;
allPartTotal += partNum;
}
var total = deptTotal + partTotal;
var len = deptSize > partSize ? deptSize : partSize;
if(deptSize ==1 && partSize == 1){
strHtml += "<tr>";
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + (i + 1) + '</td>';
strHtml += ' <td width="216" style="font-family : 宋体;text-align:center;width:162;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + temp.unitName + '</td>';
for (var j = 0; j<len; j++) {
if (majorDept[j]) {
strHtml += ' <td width="144" style="font-family : 宋体;text-align:center;width:108;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + majorDept[j].departmentName + '</td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + majorDept[j].deptOfficialName + '</td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + majorDept[j].deptSecrecyPeoples + '</td>';
}else{
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;"></td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;"></td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;"></td>';
}
if (majorPart[j]) {
strHtml += ' <td width="152" style="font-family : 宋体;text-align:center;width:114;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + majorPart[j].partName + '</td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + majorPart[j].partOfficialName + '</td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + majorPart[j].partSecrecyPeoples + '</td>';
}else{
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;"></td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;"></td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;"></td>';
}
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">'+total+'</td>';
strHtml += "</tr>"
}
}else{
for (var j = 0; j<len; j++) {
if (j > 0) {
strHtml += "<tr>";
}else{
strHtml += "<tr>";
strHtml += ' <td rowspan="'+len+'" style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;" >' + (i + 1) + '</td>';
strHtml += ' <td rowspan="'+len+'" width="216" style="font-family : 宋体;text-align:center;width:162;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + temp.unitName + '</td>';
}
if (majorDept[j]) {
strHtml += ' <td width="144" style="font-family : 宋体;text-align:center;width:108;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + majorDept[j].departmentName + '</td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + majorDept[j].deptOfficialName + '</td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + majorDept[j].deptSecrecyPeoples + '</td>';
}else{
deptNum=0;
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;"></td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;"></td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;"></td>';
}
if (majorPart[j]) {
strHtml += ' <td width="152" style="font-family : 宋体;text-align:center;width:114;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + majorPart[j].partName + '</td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + majorPart[j].partOfficialName + '</td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + majorPart[j].partSecrecyPeoples + '</td>';
if(j == 0){
strHtml += ' <td rowspan="'+len+'" style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">'+total+'</td>';
}
}else{
partNum =0
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;"></td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;"></td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;"></td>';
if(j == 0){
strHtml += ' <td rowspan="'+len+'" style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">'+total+'</td>';
}
}
strHtml += "</tr>"
}
}
}
strHtml += "<tr>";
strHtml += ' <td colspan="2"style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">合 计</td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + deptLen + '个</td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;"></td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + allDeptTotal + '人</td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + partLen + '个</td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;"></td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + allPartTotal + '人</td>';
strHtml += ' <td style="font-family : 宋体;text-align:center;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">' + (allDeptTotal+allPartTotal) + '人</td>';
strHtml += "</tr>"
$("#tbData").html(strHtml);
}
</script>
</head> <style type="text/css"> body { background-color: white; }
h3 { text-align: center; font-weight:600;!important; }
table.tftable { width: 100%; border-width: 1px; border: 1px; border-color: black; border-collapse: collapse; }
table.tftable thead th { text-align: center; font-size: 15px; border-width: 1px; padding: 8px; border-style: solid; border-color: black; font-family : 宋体; }
table.tftable thead th tr { text-align: center; }
table.tftable tbody tr td { text-align: center; font-size: 15px; border-width: 1px; padding: 8px; border-style: solid; border-color: black; font-family : 宋体; } .btn-div { top: 18px; margin-left: 887px; margin-top:-38px; margin-bottom:10px; } .panel.panel-default.pd_5{ padding-left:15px; padding-right:15px; } </style> <body> <div class="container col-sm-12"> <div class="row"> <span class="col-sm-3"></span> <span class="col-md-6"> <h3>汕头市市直机关单位保密要害部门、部位基本情况</h3> </span> <span class="col-md-3" style="top: 18px;"> <button class="btn btn-primary btn-sm pull-right" type="button" onclick="onLoadData()"><span class="glyphicon glyphicon-refresh"></span> 刷新</button> <button class="btn btn-primary btn-sm pull-right" type="button" onclick="tableToExcel('tfhover')" style="margin-right:10px;"><span class="glyphicon glyphicon-export"></span> 导出Excel</button>
</span>
</div>
</div> <div class="panel panel-default pd_5"> <table id="tfhover" class="tftable" border="1">
<thead>
<tr>
<th rowspan="2" style="font-family : 宋体;border:.5pt solid windowtext;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">序号</th>
<th rowspan="2" style="font-family : 宋体;border:.5pt solid windowtext;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">单位名称</th>
<th colspan="3" style="font-family : 宋体;border:.5pt solid windowtext;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">要害部门</th>
<th colspan="3" style="font-family : 宋体;border:.5pt solid windowtext;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">要害部位</th>
<th rowspan="2" style="font-family : 宋体;border:.5pt solid windowtext;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">涉密人员总数</th>
</tr>
<tr>
<th style="font-family : 宋体;border:.5pt solid windowtext;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">名称</th>
<th style="font-family : 宋体;border:.5pt solid windowtext;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">责任人</th>
<th style="font-family : 宋体;border:.5pt solid windowtext;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">涉密人员数量</th>
<th style="font-family : 宋体;border:.5pt solid windowtext;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">名称</th>
<th style="font-family : 宋体;border:.5pt solid windowtext;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">责任人</th>
<th style="font-family : 宋体;border:.5pt solid windowtext;border-right:.5pt solid windowtext;border-bottom:.5pt solid windowtext;">涉密人员数量</th>
</tr>
</thead>
<tbody id="tbData">
</tbody>
</table>
<p style="font-family : 宋体;">
说明:涉密人员总数是指保密要害部门和部位的涉密工作人员总数量;计算公式是:涉密
人员总数=要害部门涉密人员+要害部位涉密人员——所含人数(所含人数指要害部位涉密
人员已含于要害部门涉密人员中)。
</p>
</div> </body> </html>