LayUI官方文档：https://layui.dev/docs/2/#introduce
XLSX NPM地址：https://www.npmjs.com/package/xlsx XLSX 使用参考文档：https://juejin.cn/post/7003153489920524301 https://blog.csdn.net/qq_20805455/article/details/1222297621、在官方网站上下载layui压缩文件解压后选择里面的layui文件夹，
2、XLSX下载里面的xlsx.core.min.js文件就可以了比如在这个https://unpkg.com/browse/xlsx@0.18.5/dist/ 里选择xlsx.core.min.js，不能下载的话，就自己建立一个同名文件，把里面的内容全部复制粘贴到这个新建文件上
文件夹示意图
页面效果如下：
HTML代码如下：
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<!-- 引入 layui.css -->
<link href="./layui/css/layui.css" rel="stylesheet">
<!-- 引入 layui.js -->
<script src="./layui/layui.js"></script>
<script type="text/javascript" src="./xlsx.core.min.js"></script>
<!-- <script type="module" src="./index.js"></script> -->
<script type="text/javascript" src="./index.js"></script>
<!-- 覆盖layui原生样式-->
<style>
.layui-form-label {
width: 100px !important;
/* padding: 9px 15px; */
padding: 9px 0px !important;
text-align: right !important;
}
.btnDiv {
display: flex;
flex-direction: row;
flex-wrap: nowrap;
align-content: center;
justify-content: flex-end;
}
.layui-inline {
margin-left: 10px;
}
.layui-laypage-em {
xbackground-color: #16baaa;
}
.layui-form-select dl dd.layui-this {
background-color: #f8f8f8;
color: #16b777;
font-weight: 700;
}
.layui-form-selected {
xcolor: red;
}
input:focus {
background-color: lightblue;
border-color: #b71818;
}
/* .layui-badge-rim,
.layui-border,
.layui-colla-content,
.layui-colla-item,
.layui-collapse,
.layui-elem-field,
.layui-form-pane .layui-form-item[pane],
.layui-form-pane .layui-form-label,
.layui-input,
.layui-input-split,
.layui-panel,
.layui-quote-nm,
.layui-select,
.layui-tab-bar,
.layui-tab-card,
.layui-tab-title,
.layui-tab-title .layui-this:after,
.layui-textarea {
border-color: #b71818;
} */
/* .layui-input-block {
margin-left: 90px !important;
} */
</style>
</head>
<body style="padding: 20px;">
<form class="layui-form" action="">
<div class="layui-row">
<div class="layui-col-xs3">
<div class="layui-form-item">
<label class="layui-form-label">部门</label>
<div class="layui-input-block">
<select name="department" lay-filter="aihao" id="selectDepartment">
<option value=""></option>
<!-- <option value="0">写作</option>
<option value="1" selected>阅读</option>
<option value="2">游戏</option>
<option value="3">音乐</option>
<option value="4">旅行</option> -->
</select>
</div>
</div>
</div>
<div class="layui-col-xs3">
<div class="layui-form-item">
<label class="layui-form-label">用户</label>
<div class="layui-input-block">
<input type="text" name="username" id="username" lay-verify="required" placeholder="请输入"
autocomplete="off" class="layui-input">
</div>
</div>
</div>
<div class="layui-col-xs3">
<div class="layui-form-item">
<label class="layui-form-label">性别</label>
<div class="layui-input-block">
<!-- <input type="radio" name="sex" value="男" title="男" checked> -->
<input type="radio" name="sex" value="男" title="男" id="manRadio" />
<input type="radio" name="sex" value="女" title="女" id="womanRadio" />
</div>
</div>
</div>
<div class="layui-col-xs3">
<div class="layui-form-item">
<label class="layui-form-label">状态</label>
<div class="layui-input-block">
<input type="checkbox" name="state" title="启用" value="启用" id="stateEnable">
<input type="checkbox" name="state" title="失效" value="失效" id="stateFailure">
<input type="checkbox" name="state" title="禁用" value="禁用" id="stateDisable">
</div>
</div>
</div>
</div>
<div class="layui-row">
<div class="layui-col-xs4">
<div class="layui-form-item">
<label class="layui-form-label">创建时间</label>
<div class="layui-inline" id="createTime">
<div class="layui-input-inline">
<input type="text" autocomplete="off" id="createTime-start-date"
class="layui-input" placeholder="开始日期">
</div>
<div class="layui-form-mid">-</div>
<div class="layui-input-inline">
<input type="text" autocomplete="off" id="createTime-end-date"
class="layui-input" placeholder="结束日期">
</div>
</div>
</div>
</div>
</div>
<div class="layui-form-item btnDiv">
<button type="button" class="layui-btn" id="btnQuery">查询</button>
<button type="button" class="layui-btn" id="btnReset">清除</button>
<button type="button" class="layui-btn" id="btnDownload">下载</button>
</div>
</form>
<table class="layui-table" lay-filter="parse-table-demo" id="data-table">
<!-- <colgroup>
<col width="120">
<col width="120">
<col width="120">
<col width="120">
<col width="120">
<col width="120">
<col width="120">
<col>
</colgroup> -->
<thead>
<tr>
<th lay-data="{field:'name6', width:100}">序号</th>
<th lay-data="{field:'name7', width:100}">id</th>
<th lay-data="{field:'name1', minWidth:120}">部门</th>
<th lay-data="{field:'name2', width:120}">用户</th>
<th lay-data="{field:'name3', width:200}">性别</th>
<th lay-data="{field:'name4', width:150}">状态</th>
<th lay-data="{field:'name5', minWidth:150}">创建时间</th>
</tr>
</thead>
<tbody id="tableData">
</tbody>
</table>
<div id="demo-laypage-layout-1" style="margin-top: 10px;"></div>
</body>
</html>
View Code
JS代码如下：
/** 当前页 */
let currentIndex = 1;
/** 每页数据大小 */
let pageLimit = 2;
/** 总页数 */
let pageTotalCount = 0;
// Usage
layui.use(['form', 'table', 'upload', 'element', 'layer', 'jquery'], function () {
var layer = layui.layer;
var table = layui.table;
var form = layui.form;
var $ = layui.jquery;
var laydate = layui.laydate;
var laypage = layui.laypage;
//初始化 查询表单里的标签元素数据 start
let departments = initDepartmentSelect();
if (departments) {
$.each(departments, function (index, item) {
// 下拉菜单里添加元素
$("#selectDepartment").append(new Option(item.text, item.value));
});
}
// // 渲染
// laydate.render({
// elem: '#ID-laydate-demo'
// });
// // 英文版
// laydate.render({
// elem: '#ID-laydate-demo-en',
// lang: 'en'
// });
// 日期范围 - 左右面板独立选择模式
laydate.render({
elem: '#createTime',
range: ['#createTime-start-date', '#createTime-end-date']
});
// // 日期范围 - 左右面板联动选择模式
// laydate.render({
// elem: '#createTime',
// range: ['#createTime-start-date', '#createTime-end-date'],
// rangeLinked: true // 开启日期范围选择时的区间联动标注模式 --- 2.8+ 新增
// });
form.render();
//初始化 查询表单里的标签元素数据 end
queryData();
//查询事件
$("#btnQuery").click(function () {
queryData();
});
//重置查询表单事件
$("#btnReset").click(function () {
$("#selectDepartment").val('');
$("#username").val('');
$("#createTime-start-date").val('');
$("#createTime-end-date").val('');
$("#stateEnable").prop('checked', false);
$("#stateFailure").prop('checked', false);
$("#stateDisable").prop('checked', false);
$("#manRadio").prop('checked', false);
$("#womanRadio").prop('checked', false);
form.render();
//inidData();
});
//excel导出事件
$("#btnDownload").click(function () {
exportExcelByTable("#data-table");
let data = initDataList();
exportExcelFile(data);
});
function queryData() {
let department = $("#selectDepartment").val();
let username = $("#username").val();
let sex = $('input[name="sex"]:checked').val();
//将页面全部复选框选中的值拼接到一个数组中
var states = [];
// $('input[type=checkbox]:checked').each(function () {
// arr_box.push($(this).val());
// });
$('input[name="state"]:checked').each(function () {
states.push($(this).val());
});
let createTime_start = $("#createTime-start-date").val();
let createTime_end = $("#createTime-end-date").val();
//console.log("aaa")
let result = getTableDataAndTotleCount(department, username, sex, states, createTime_start, createTime_end, currentIndex, pageLimit);
//console.log("result",result)
pageTotalCount = result.count;
let pageData = result.data;
setTableHtml(pageData);
// 自定义排版
laypage.render({
elem: 'demo-laypage-layout-1',
count: pageTotalCount,
limit: pageLimit,
limits: [2, 4, 8, 16],
//layout: ['limit', 'prev', 'page', 'count', 'next']
layout: ['count', 'prev', 'page', 'next', 'limit', 'refresh', 'skip'], // 功能布局
jump: function (obj, first) {
// console.log(obj);
// console.log(obj.curr); // 得到当前页，以便向服务端请求对应页的数据。
// console.log(obj.limit); // 得到每页显示的条数
// 首次不执行
if (!first) {
currentIndex = obj.curr;
pageLimit = obj.limit;
// do something
let result = getTableDataAndTotleCount(department, username, sex, states, createTime_start, createTime_end, currentIndex, pageLimit);
pageTotalCount = result.count;
let pageData = result.data;
setTableHtml(pageData);
// let data =getTableData(obj.curr,obj.limit);
// pageData = pagination(currentIndex, pageLimit, data)
// setTableHtml(pageData);
}
}
});
}
function setTableHtml(data) {
let strHtml = "";
let i =0;
$.each(data, function (index, item) {
i++;
strHtml += (`<tr><td>${i}</td> <td>${item.id}</td><td>${item.departmentName}</td>
<td>${item.username}</td><td>${item.sex}</td><td>${item.state}</td><td>${item.createTime}</td></tr>`);
});
document.getElementById('tableData').innerHTML = strHtml;
table.init('parse-table-demo', {
// height: ''
});
}
});
/** 初始化部门下拉框 */
function initDepartmentSelect() {
let array = [];
//模拟从后端拿到的部门数据数组
let list = [{ id: 1, name: '财务部' }, { id: 2, name: '行政部' }, { id: 3, name: '研发部' }, { id: 4, name: '销售部' }];
if (list) {
list.forEach(element => {
let value = element.id
let text = element.name
array.push({ text, value });
});
}
return array;
}
/*** 初始数据 */
function initDataList() {
let data = [
{ id: 1, departmentId: 1, departmentName: '财务部', username: '张三', sex: '男', state: '启用', createTime: '2023-12-27 10:54:20' },
{ id: 2, departmentId: 2, departmentName: '行政部', username: '李四', sex: '男', state: '失效', createTime: '2023-11-27 10:54:20' },
{ id: 3, departmentId: 3, departmentName: '研发部', username: '王五', sex: '男', state: '禁用', createTime: '2023-12-27 10:54:20' },
{ id: 4, departmentId: 4, departmentName: '销售部', username: '赵六', sex: '男', state: '启用', createTime: '2023-11-27 10:54:20' },
{ id: 5, departmentId: 1, departmentName: '财务部', username: '小丽', sex: '女', state: '启用', createTime: '2023-12-25 10:54:20' },
{ id: 6, departmentId: 1, departmentName: '财务部', username: '小明', sex: '男', state: '启用', createTime: '2023-12-27 10:54:20' },
{ id: 7, departmentId: 3, departmentName: '研发部', username: '小王', sex: '男', state: '启用', createTime: '2023-12-20 10:54:20' },
{ id: 8, departmentId: 1, departmentName: '财务部', username: '小慧', sex: '女', state: '启用', createTime: '2023-12-27 10:54:20' },
{ id: 9, departmentId: 2, departmentName: '行政部', username: '慧慧', sex: '女', state: '失效', createTime: '2023-12-27 10:54:20' },
{ id: 10, departmentId: 1, departmentName: '财务部', username: '晓丽', sex: '女', state: '启用', createTime: '2023-10-27 10:54:20' },
{ id: 11, departmentId: 2, departmentName: '行政部', username: '小莉', sex: '女', state: '启用', createTime: '2023-12-27 10:54:20' },
{ id: 12, departmentId: 1, departmentName: '财务部', username: '田七', sex: '男', state: '启用', createTime: '2023-12-23 10:54:20' },
{ id: 13, departmentId: 2, departmentName: '行政部', username: '张三2', sex: '男', state: '禁用', createTime: '2023-12-22 10:54:20' },
];
return data;
}
/** 获取页面表格的数据和总数 */
function getTableDataAndTotleCount(departmentId, username, sex, states, createTime_start, createTime_end, pageIndex, pageSize) {
let data = initDataList();
if (departmentId) {
data = data.filter(m => m.departmentId == departmentId);
}
if (username) {
data = data.filter(m => m.username.includes(username));
}
if (sex) {
data = data.filter(m => m.sex == sex);
}
if (states && states.length > 0) {
data = data.filter(m => states.includes(m.state));
}
if (createTime_start && createTime_end) {
data = data.filter(m => m.createTime <= createTime_end && m.createTime >= createTime_start);
}
let count = data.length;
if (data) {
data = pagination(pageIndex, pageSize, data);
}
return { count, data };
}
/** 分页获取 */
function pagination(pageNo, pageSize, array) {
var offset = (pageNo - 1) * pageSize;
return (offset + pageSize >= array.length) ? array.slice(offset, array.length) : array.slice(offset, offset + pageSize);
}
/**
* 对Date的扩展，将 Date 转化为指定格式的String
* 月(M)、日(d)、小时(h)、分(m)、秒(s)、季度(q) 可以用 1-2 个占位符，
* 年(y)可以用 1-4 个占位符，毫秒(S)只能用 1 个占位符(是 1-3 位的数字)
* 例子：
* (new Date()).Format("yyyy-MM-dd hh:mm:ss.S") ==> 2006-07-02 08:09:04.423
* (new Date()).Format("yyyy-M-d h:m:s.S") ==> 2006-7-2 8:9:4.18
*/
Date.prototype.Format = function (fmt) {
var o = {
"M+": this.getMonth() + 1, //月份
"d+": this.getDate(), //日
"H+": this.getHours(), //小时
"m+": this.getMinutes(), //分
"s+": this.getSeconds(), //秒
"q+": Math.floor((this.getMonth() + 3) / 3), //季度
"S": this.getMilliseconds() //毫秒
};
if (/(y+)/.test(fmt)) fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
for (var k in o)
if (new RegExp("(" + k + ")").test(fmt)) fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
return fmt;
}
function getDateTime() {
const now = new Date();
const year = now.getFullYear();
const month = ('0' + (now.getMonth() + 1)).slice(-2);
const day = ('0' + now.getDate()).slice(-2);
const hours = ('0' + now.getHours()).slice(-2);
const minutes = ('0' + now.getMinutes()).slice(-2);
const seconds = ('0' + now.getSeconds()).slice(-2);
const formattedTime = year + '-' + month + '-' + day + ' ' + hours + ':' + minutes + ':' + seconds;
return formattedTime;
}
// 导出excel
/** 根据table导出Excel */
function exportExcelByTable(tableName) {
// 使用XLSX导出 https://www.npmjs.com/package/xlsx 使用参考文档：https://juejin.cn/post/7003153489920524301 https://blog.csdn.net/qq_20805455/article/details/122229762
let currentDate = new Date().Format("yyyy-MM-dd HH:mm:ss");
let excelName = `${currentDate}.xlsx`;
console.log('excelName', excelName)
// if (tableList) {
// exportExcelFile(tableList, '表1', excelName);
// } else {
// //'没有导出数据'
// }
let table1 = document.querySelector(tableName);//document.querySelector("#data-table");
//将一个table对象转换成一个sheet对象，使用js-xlsx插件将table转成workbook时，会默认把内容是数字的字符串当做数字处理，raw:true属性控制把内容当做字符串进行输出
let sheet = XLSX.utils.table_to_sheet(table1, { raw: true, skipHeader: true, });
let wscols = [ // 每列不同宽度px
{ wch: 10 },
{ wch: 15 },
{ wch: 20 },
{ wch: 25 },
{ wch: 25 },
{ wch: 25 },
{ wch: 20 },
{ wch: 25 },
{ wch: 25 },
{ wch: 25 },
{ wch: 25 },
];
// workbook.SheetNames[0]获取到到是文件里的到第一个表格
sheet["!cols"] = wscols;
openDownloadDialog(sheetToblob(sheet), excelName);
}
/** 修改表格行高列宽度 */
function test(worksheet) {
//let table = document.getElementById("exportTable");
//let worksheet = XLSX.utils.table_to_sheet(table);
let workbook = XLSX.utils.book_new();
let wscols = [ // 每列不同宽度px
{ wch: 12 },
{ wch: 26 },
{ wch: 12 },
{ wch: 12 },
{ wch: 14 },
{ wch: 12 },
{ wch: 12 },
{ wch: 15 },
];
// workbook.SheetNames[0]获取到到是文件里的到第一个表格
worksheet["!cols"] = wscols;
let wsrows = [{ hpx: 20 }]; // 每行固定高度px
for (let i = 0; i <= this.total; i++) { // total 列表条数
wsrows.push({ hpx: 20 });
}
worksheet["!rows"] = wsrows;
XLSX.utils.book_append_sheet(workbook, worksheet, "sheet");
try {
XLSX.writeFile(workbook, `${this.time_date}报表统计.xlsx`); // time_date 所选日期
} catch (e) {
console.log(e, workbook);
}
}
// 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
function sheetToblob(sheet, sheetName) {
sheetName = sheetName || 'sheet1';
var workbook = {
SheetNames: [sheetName],
Sheets: {}
};
workbook.Sheets[sheetName] = sheet; // 生成excel的配置项
var wopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
type: 'binary'
};
var wbout = XLSX.write(workbook, wopts);
var blob = new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}); // 字符串转ArrayBuffer
function s2ab(s) {
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
return blob;
}
function openDownloadDialog(url, saveName) {
if (typeof url == 'object' && url instanceof Blob) {
url = URL.createObjectURL(url); // 创建blob地址
}
var aLink = document.createElement('a');
aLink.href = url;
aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
var event;
if (window.MouseEvent) event = new MouseEvent('click');
else {
event = document.createEvent('MouseEvents');
event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
}
aLink.dispatchEvent(event);
}
/**
* 根据json数据导出 excel 文件
* @param array JSON 数组
* @param sheetName 第一张表名
* @param fileName 文件名
*/
function exportExcelFile(array, sheetName = '表1', fileName = 'example.xlsx') {
const jsonWorkSheet = XLSX.utils.json_to_sheet(array);
const workBook = {
SheetNames: [sheetName],
Sheets: {
[sheetName]: jsonWorkSheet,
}
};
return XLSX.writeFile(workBook, fileName);
}
View Code
标签：username,XLSX,layui,Excel,sex,let,data,createTime From： https://www.cnblogs.com/hudean/p/17930359.html