layui之静态表格的分页及搜索功能以及前端使用XLSX导出Excel功能

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/122229762

1、在官方网站上下载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:'id', width:100,hide:true}">id</th>
                <th lay-data="{field:'index', width:100}">序号</th>
                <th lay-data="{field:'departmentName', minWidth:120}">部门</th>
                <th lay-data="{field:'username', width:120}">用户</th>
                <th lay-data="{field:'sex', width:200}">性别</th>
                <th lay-data="{field:'state', width:150}">状态</th>
                <th lay-data="{field:'createTime', minWidth:150}">创建时间</th>
            </tr>
        </thead>
        <tbody id="tableData">

        </tbody>

    </table>
    <div id="demo-laypage-layout-1" style="margin-top: 10px;"></div>
</body>

</html>

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>${item.id}</td><td>${i}</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: ''

});

// 行单击事件( 双击事件为: rowDouble )

table.on('row(parse-table-demo)', function (obj) {

var data = obj.data; // 获取当前行数据

console.log('row(data-table)', data);

// 显示 - 仅用于演示

layer.msg('当前行数据：<br>' + JSON.stringify(data), {

offset: '65px'

});

// 标注当前点击行的选中状态

obj.setRowChecked({

type: 'radio' // radio 单选模式；checkbox 复选模式

});

}

});

/** 初始化部门下拉框 */

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: 20 },

{ 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: 14 },

{ 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);

}

posted on 2023-12-27 13:24 青春似雨后霓虹阅读(140) 评论(0) 编辑收藏举报