**
前后端代码,附Scripts文件包
**
//后端代码-----------------------begin-----------------------
package com.web.excel.cxtj;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.ServletRequest;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.util.ResourceUtils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.web.rest.Action;
import com.web.rest.ActionContext;
import com.web.excel.pojo.Dltjb;
import com.web.excel.pojo.Xdyjbtjb;
import com.web.excel.pojo.Cloumns;
/**
*
* 统计报表按统计表类型展示人员统计表,查看相关人员信息,导出当前报表
*
*/
public class Tjbb {
@Action
public void getDownload() throws Exception{
HttpServletRequest request = ActionContext.getActionContext().getHttpServletRequest();
HttpServletResponse response = ActionContext.getActionContext().getHttpServletResponse();
// String columns = request.getParameter("columns");
// String data = request.getParameter("a");
String columns = "[{"header":"姓名","field":"ZGJ"},{"header":"性别","field":"FGJ"},{"header":"年龄","field":"ZBJ"},{"header":"手机号","field":"FBJ"},{"header":"籍贯","field":"ZJJ"}]";
String data = "[{"Name":"张一","Gender":"男","Age":"20","Phone":18888888820,"NativePlace":"山西"},{"Name":"张二","Gender":"女","Age":"19","Phone":18888888819,"NativePlace":"山西"},{"Name":"张三","Gender":"男","Age":"18","Phone":18888888818,"NativePlace":"山西"}]";
String type = request.getParameter("type");
String fname = null;
switch(type){
case "dltjb":
fname = "dltjb统计表";
this.exportExcel(columns, data, type, fname, response);
break;
case "xdyjbtjb":
fname = "Xdyjbtjb统计表";
this.exportComplexExcel(type, data, fname, response);
break;
}
}
/**
* 动态导出excel
* @param columns
* @param data
* @param type
* @param response
*/
public void exportExcel(String columns,String data, String type, String fname, HttpServletResponse response){
JSONArray array = JSONObject.parseArray(columns);
List<Cloumns> list = JSONObject.parseArray(columns, Cloumns.class);
try {
OutputStream out = response.getOutputStream();
response.reset();
String fname1 = java.net.URLEncoder.encode(fname, "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(fname1.getBytes("UTF-8"), "GBK") + ".xls");
response.setContentType("application/ms-excel");
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(fname);
sheet.setDefaultColumnWidth(20);
HSSFCellStyle cellStyle1 = workbook.createCellStyle();
cellStyle1.setAlignment(HorizontalAlignment.CENTER);
cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 16);
font.setBold(true);
cellStyle1.setFont(font);
HSSFRow title = sheet.createRow(0);
title.setHeightInPoints(30);
HSSFCell cell = title.createCell(0);
cell.setCellValue(fname);
cell.setCellStyle(cellStyle1);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
HSSFCellStyle cellStyle2 = workbook.createCellStyle();
cellStyle2.setAlignment(HorizontalAlignment.CENTER);
cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFRow row = sheet.createRow(1);
for (int i = 0; i < list.size(); i++) {
HSSFCell columnHead = row.createCell(i);
columnHead.setCellValue(list.get(i).getHeader());
columnHead.setCellStyle(cellStyle2);
}
HSSFCellStyle cellStyle3 = workbook.createCellStyle();
cellStyle3.setAlignment(HorizontalAlignment.CENTER);
cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行,-------重要-------
cellStyle3.setWrapText(true);
//设置自动换行,-------重要-------
List entities = null;
switch(type){
case "dltjb":
entities = JSONObject.parseArray(data, Dltjb.class);
break;
}
for(int j = 0; j < entities.size(); j++){
HSSFRow dataRow = sheet.createRow(j+2);
Field[] fields = entities.get(j).getClass().getDeclaredFields();
for (int k = 0; k < fields.length; k++) {
HSSFCell createCell = dataRow.createCell(k);
createCell.setCellStyle(cellStyle3);
fields[k].setAccessible(true);
String name = fields[k].getName();
name = name.substring(0,1).toUpperCase()+name.substring(1);
Method method = entities.get(j).getClass().getMethod("get" + name);
String value = (String) method.invoke(entities.get(j));
createCell.setCellValue(value);
}
}
workbook.write(out);
out.close();
workbook.close();
}catch (Exception e){
e.printStackTrace();
}
}
/**
* 根据模板导出Excel
* @param type
* @param data
* @param fname
* @param response
* @throws Exception
*/
public void exportComplexExcel(String type, String data, String fname, HttpServletResponse response) throws Exception {
try {
String sourcePath = "com/web/excel/web_files/excel/"; //模板路径
OutputStream out = response.getOutputStream();
response.reset();
String fname1 = java.net.URLEncoder.encode(fname, "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(fname1.getBytes("UTF-8"), "GBK") + ".xls");
response.setContentType("application/ms-excel");
HSSFWorkbook workbook = new HSSFWorkbook();
sourcePath = sourcePath + fname + ".xls";
InputStream inputStream = Tjbb.class.getClassLoader().getResourceAsStream(sourcePath);
workbook = new HSSFWorkbook(inputStream);
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFCellStyle cellStyle3 = workbook.createCellStyle();
cellStyle3.setAlignment(HorizontalAlignment.CENTER);
cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行,-------重要-------
cellStyle3.setWrapText(true);
//设置自动换行,-------重要-------
List entities = null;
switch(type){
case "xdyjbtjb":
entities = JSONObject.parseArray(data, Xdyjbtjb.class);
break;
}
for(int j = 0; j < entities.size(); j++){
HSSFRow dataRow = sheet.createRow(j+3);
Field[] fields = entities.get(j).getClass().getDeclaredFields();
for (int k = 0; k < fields.length; k++) {
HSSFCell createCell = dataRow.createCell(k);
createCell.setCellStyle(cellStyle3);
fields[k].setAccessible(true);
String name = fields[k].getName();
name = name.substring(0,1).toUpperCase()+name.substring(1);
Method method = entities.get(j).getClass().getMethod("get" + name);
String value = (String) method.invoke(entities.get(j));
createCell.setCellValue(value);
}
}
workbook.write(out);
out.close();
inputStream.close();
workbook.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
// 演示POJO类 实际使用记得getset------begin------
package com.web.excel.cxtj.pojo;
import java.io.Serializable;
public class Cloumns implements Serializable {
private String header;
private String field;
}
package com.web.excel.cxtj.pojo;
import java.io.Serializable;
public class Dltjb implements Serializable {
private String Name; //关键取值,模板里要什么数据列,这里就写什么
private String Gender; //关键取值
private String Age; //关键取值
private String Phone; //关键取值
private String NativePlace; //关键取值
}
package com.web.excel.cxtj.pojo;
import java.io.Serializable;
public class Xdyjbtjb implements Serializable {
private String Name; //关键取值
private String Gender; //关键取值
private String Age; //关键取值
private String Phone; //关键取值
private String NativePlace; //关键取值
}
// 演示POJO类 实际使用记得getset------end------
//后端代码-----------------------end-----------------------
//前端代码,js相同-----------------------begin-----------------------
// ryxx.html ------begin------
<!DOCTYPE html>
<html lang="zh-cn">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<!-- <meta name="decorator" content="mesh_top" /> -->
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<script type="text/javascript" src="./scripts/js/boot.js"></script>
<script type="text/javascript" src="./scripts/js/index.umd.js"></script>
<link rel="stylesheet" type="text/css" href="./scripts/js/miniui/themes/default/miniui.css">
<link rel="stylesheet" type="text/css" href="./scripts/css/iconfont/iconfont.css">
<link rel="stylesheet" type="text/css" href="./scripts/css/miniPublicTable/minipublictable.css">
<title>人员信息统计</title>
<style>
.mini-grid-headerCell-nowrap {white-space: nowrap;word-break: keep-all;font-weight:600;}
#protal_main_div{padding:0 20px;}
.crumb span{line-height:60px;}
.globe.with_head,.globe.with_head .body{padding:0px;}
.dbsxdatagridBody .mini-grid-border{border:none;}
.head,.head .row{height:50px;display: flex;justify-content: flex-end;align-items: center;}
.table-head-css .mini-grid-headerCell{background: #eff7ff !important;}
</style>
</head>
<body>
<div style="width: 100%;">
<div class="mini-toolbar" style="border-bottom:0;padding:0;">
<table style="width:100%;">
<tr>
<td style="width:100%;">
<a class="mini-button" onclick="exportExcel()">导出</a>
</td>
</tr>
</table>
</div>
</div>
<div id="top1" headerAlign="center" header="登录情况统计分析" style="font-color:black"> -->
<div property="columns" >
<div field="Name" id="zbrs" width="120" headerAlign="center" allowSort="true">姓名</div>
<div field="Gender" width="120" headerAlign="center" renderer="Gender" allowSort="true">性别</div>
<div field="Age" width="120" headerAlign="center" renderer="Age" allowSort="true">年龄</div>
<div field="Phone" width="120" headerAlign="center" renderer="Phone" allowSort="true">手机号</div>
<div field="NativePlace" width="120" headerAlign="center" renderer="NativePlace" allowSort="true">籍贯</div>
</div>
</div>
<iframe id="exportIFrame" style="display:none;"></iframe>
</body>
<script type="text/javascript" src="./ryxx.js"></script>
</html>
// ryxx.html ------end------
// xdyjbtjb.html --begin
<!DOCTYPE html>
<html lang="zh-cn">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<!-- <meta name="decorator" content="mesh_top" /> -->
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<script type="text/javascript" src="./scripts/js/boot.js"></script>
<script type="text/javascript" src="./scripts/js/index.umd.js"></script>
<link rel="stylesheet" type="text/css" href="./scripts/js/miniui/themes/default/miniui.css">
<link rel="stylesheet" type="text/css" href="./scripts/css/iconfont/iconfont.css">
<link rel="stylesheet" type="text/css" href="./scripts/css/miniPublicTable/minipublictable.css">
<title>xdyjbtjb统计表</title>
<style>
.mini-grid-headerCell-nowrap {white-space: nowrap;word-break: keep-all;font-weight:600;}
#protal_main_div{padding:0 20px;}
.crumb span{line-height:60px;}
.globe.with_head,.globe.with_head .body{padding:0px;}
.dbsxdatagridBody .mini-grid-border{border:none;}
.head,.head .row{height:50px;display: flex;justify-content: flex-end;align-items: center;}
.table-head-css .mini-grid-headerCell{background: #eff7ff !important;}
</style>
</head>
<body>
<div style="width: 100%;">
<div class="mini-toolbar" style="border-bottom:0;padding:0;">
<table style="width:100%;">
<tr>
<td style="width:100%;">
<a class="mini-button" onclick="exportExcel()">导出</a>
</td>
</tr>
</table>
</div>
</div>
<div id="datagrid1" class="mini-datagrid table-head-css" style="width:100%;height:100%;" allowResize="true"
multiSelect="true" pageSize="50" allowAlternating="true" showpager="flase">
<div property="columns" >
<div field="Name" width="120" headerAlign="center" renderer="Name" allowSort="false">姓名</div>
<div header="性别与年龄" headerAlign="center">
<div property="columns">
<div field="Gender" width="120" headerAlign="center" renderer="Gender" allowSort="false">性别</div>
<div field="Age" width="120" headerAlign="center" renderer="Age" allowSort="false">年龄</div>
</div>
</div>
<div field="Phone" id="Phone" width="120" headerAlign="center" renderer="Phone" allowSort="true">手机号</div>
<div field="HJ2" id="NativePlace" width="120" headerAlign="center" renderer="NativePlace" allowSort="true">籍贯</div>
</div>
</div>
</body>
<script type="text/javascript" src="./ryxx.js"></script>
</html>
// xdyjbtjb.html --end
// ryxx.js ------begin------
//获取传递的参数,例如单位部门id
function getQueryString(name) {
var reg =new RegExp('(^|&)' + name +'=([^&]*)(&|$)','i');
var r =window.location.search.substr(1).match(reg);
if (r !=null) {
return unescape(r[2]);
}
return null;
}
var organizationId = getQueryString("organizationId");
// 声明所有从后台调用的 api
var api = Api(function (ajax, reg) {
return {
getDltjbList: function (pageIndex,pageSize){
return ajax({
data:{
'organizationId': organizationId,
'pageIndex': pageIndex,
'pageSize': pageSize
},
url: '/excel/getRyxxList'
})
}
}
})
var grid;
// 全局事件管理
Msger()
.regist('init', function () {mini.parse($('#datagrid1').get(0));var grid= mini.get('#datagrid1');grid.on('beforeload', function (e) {e.cancel = true;api.getDltjbList().then(function (data) {grid.loadData(data.data)})})})
.regist('loadData',function(){var gridId = 'datagrid1';grid= mini.get('#datagrid1');api.getDltjbList().then(function (data) {grid.loadData(data.data);})})
// eg: 当页面加载完成的时候调用 init 事件
Shelf.done().then(function () {Msger().emit('init');Msger().emit('loadData');})
//数据反查事件--begin
function Name(e) { name = e.field;var temp= "<a href='javascript:temp("+name+")'>"+e.value+"</a>";return temp;}
function Gender(e) {var name = e.field;var temp= "<a href='javascript:temp("+name+")'>"+e.value+"</a>";return temp;}
function Age(e) {var name = e.field;var dl= "<a href='javascript:dl("+name+")'>"+e.value+"</a>";return dl;}
function Phone(e) {var name = e.field;var dl= "<a href='javascript:dl("+name+")'>"+e.value+"</a>";return dl;}
function NativePlace(e) {var name = e.field;var dl= "<a href='javascript:dl("+name+")'>"+e.value+"</a>";return dl;}
//如果反查数据时,需要多个不同的页面展示,通过以下方式进行跳转,反查页面不做演示,可参考miniui官网的datagrid示例 ---begin
function temp (name){var name = name.name;var row = grid.getSelected();if(row.id == null || row.id == '' || row.id == undefined){row = grid.getRow(0);}window.open("./rylb.html?B00="+row.id+"&name="+name);}
function dl(name){var name = name.name;var row = grid.getSelected();if(row.id == null || row.id == '' || row.id == undefined){row = grid.getRow(0);}window.open("./dlcs.html?B00="+row.id+"&name="+name);}
//如果反查数据时,需要多个不同的页面展示,通过以下方式进行跳转,反查页面不做演示,可参考miniui官网的datagrid示例 ---end
//数据反查事件--end
//导出
function exportExcel() {
var columns = grid.columns;
function getColumns(columns) {
var cols = [];
for (var i = 0; i < columns.length; i++) {
var column = columns[i];
var col = { header: column.header, field: column.field, type: column.type };
if (column.columns) {
col.columns = getColumns(column.columns);
}
cols.push(col);
}
return cols;
}
var columns = getColumns(columns);
var a = grid.data;
DownLoad("/excel/getDownload", { type: "ryxx", columns: columns, a: a }, function () {
alert("导出成功");
});
}
function DownLoad(url, fields) {
// var msgid = mini.loading("正在导出数据,请耐心等待......")
//创建Form
var submitfrm = document.createElement("form");
submitfrm.action = url;
submitfrm.method = "post";
submitfrm.target = "_blank";
document.body.appendChild(submitfrm);
if (fields) {
for (var p in fields) {
var input = mini.append(submitfrm, "<input type='hidden' name='" + p + "'>");
var v = fields[p];
if (typeof v != "string") v = mini.encode(v);
input.value = v;
}
}
submitfrm.submit();
setTimeout(function () {
submitfrm.parentNode.removeChild(submitfrm);
}, 1000);
}
// ryxx.js ------end------
//前端代码-----------------------end-----------------------
Scripts文件包链接
链接:https://pan.baidu.com/s/1hJ4E0XnsEst2gKfsn8770g?pwd=1234