原生js用Export2Excel导出excel单级表头和多级表头数据方式实现

原生js用Export2Excel导出excel单级表头和多级表头数据方式实现
- HTML文件导入需要的文件
- HTML文件中实现导出函数
- HTML总代码实现汇总（直接复制代码，注意js引入路径）

原生js用Export2Excel导出excel单级表头和多级表头数据方式实现

因为项目框架的原因，不是使用纯vue开发的，所以用不了import的方式导入 file-saver 和 xlsx 的三方插件包去实现，索性我就把这两个文件下载本地，然后在HTML使用 script 标签的方式来实现。下面是具体实现方式：

HTML文件导入需要的文件

1、新建文件夹命名为js，把对应的js文件放到里面，在js文件同级新建个表格导出.html 文件；
2、这里我用到了三个文件：FileSaver.min.js、xlsx.full.min.js、Export2Excel.js
3、注意其中的Export2Excel.js 是需要自己写的，其他两个文件去对应的GitHub地址下载即可，如果找不到可以评论区聊我，我看到可以私发你，这个Export2Excel.js 文件代码我粘贴在文章里面，直接复制使用，如下：
在这里插入图片描述

在这里插入图片描述

/* eslint-disable */
// 注意html中script js不能直接使用import方式导入文件， 需要把这两个js文件下载后，通过script方式加载进去
// import { saveAs } from 'file-saver'
// import XLSX from 'xlsx'function generateArray(table) {var out = []var rows = table.querySelectorAll('tr')var ranges = []for (var R = 0; R < rows.length; ++R) {var outRow = []var row = rows[R]var columns = row.querySelectorAll('td')for (var C = 0; C < columns.length; ++C) {var cell = columns[C]var colspan = cell.getAttribute('colspan')var rowspan = cell.getAttribute('rowspan')var cellValue = cell.innerTextif (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue//Skip rangesranges.forEach(function (range) {if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null)}})//Handle Row Spanif (rowspan || colspan) {rowspan = rowspan || 1colspan = colspan || 1ranges.push({s: {r: R,c: outRow.length},e: {r: R + rowspan - 1,c: outRow.length + colspan - 1}})};//Handle ValueoutRow.push(cellValue !== "" ? cellValue : null)//Handle Colspanif (colspan)for (var k = 0; k < colspan - 1; ++k) outRow.push(null)}out.push(outRow)}return [out, ranges]
};function datenum(v, date1904) {if (date1904) v += 1462var epoch = Date.parse(v)return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000)
}function sheet_from_array_of_arrays(data, opts) {var ws = {}var range = {s: {c: 10000000,r: 10000000},e: {c: 0,r: 0}}for (var R = 0; R != data.length; ++R) {for (var C = 0; C != data[R].length; ++C) {if (range.s.r > R) range.s.r = Rif (range.s.c > C) range.s.c = Cif (range.e.r < R) range.e.r = Rif (range.e.c < C) range.e.c = Cvar cell = {v: data[R][C]}if (cell.v == null) continuevar cell_ref = XLSX.utils.encode_cell({c: C,r: R})if (typeof cell.v === 'number') cell.t = 'n'else if (typeof cell.v === 'boolean') cell.t = 'b'else if (cell.v instanceof Date) {cell.t = 'n'cell.z = XLSX.SSF._table[14]cell.v = datenum(cell.v)} else cell.t = 's'ws[cell_ref] = cell}}if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range)return ws
}function Workbook() {if (!(this instanceof Workbook)) return new Workbook()this.SheetNames = []this.Sheets = {}
}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) & 0xFFreturn buf
}function export_table_to_excel(id) {var theTable = document.getElementById(id)var oo = generateArray(theTable)var ranges = oo[1]/* original data */var data = oo[0]var ws_name = "SheetJS"var wb = new Workbook(),ws = sheet_from_array_of_arrays(data)/* add ranges to worksheet */// ws['!cols'] = ['apple', 'banan'];ws['!merges'] = ranges/* add worksheet to workbook */wb.SheetNames.push(ws_name)wb.Sheets[ws_name] = wsvar wbout = XLSX.write(wb, {bookType: 'xlsx',bookSST: false,type: 'binary'})saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), "test.xlsx")
}function export_json_to_excel({multiHeader = [],header,data,filename,merges = [],autoWidth = true,bookType = 'xlsx'
} = {}) {/* original data */filename = filename || 'excel-list'data = [...data]data.unshift(header)for (let i = multiHeader.length - 1; i > -1; i--) {data.unshift(multiHeader[i])}var ws_name = "SheetJS"var wb = new Workbook(),ws = sheet_from_array_of_arrays(data)if (merges.length > 0) {if (!ws['!merges']) ws['!merges'] = []merges.forEach(item => {ws['!merges'].push(XLSX.utils.decode_range(item))})}if (autoWidth) {/*设置worksheet每列的最大宽度*/const colWidth = data.map(row => row.map(val => {/*先判断是否为null/undefined*/if (val == null) {return {'wch': 10}}/*再判断是否为中文*/else if (val.toString().charCodeAt(0) > 255) {return {'wch': val.toString().length * 2}} else {return {'wch': val.toString().length}}}))/*以第一行为初始值*/let result = colWidth[0]for (let i = 1; i < colWidth.length; i++) {for (let j = 0; j < colWidth[i].length; j++) {if (result[j]['wch'] < colWidth[i][j]['wch']) {result[j]['wch'] = colWidth[i][j]['wch']}}}ws['!cols'] = result}/* add worksheet to workbook */wb.SheetNames.push(ws_name)wb.Sheets[ws_name] = wsvar wbout = XLSX.write(wb, {bookType: bookType,bookSST: false,type: 'binary'})saveAs(new Blob([s2ab(wbout)], {type: "application/octet-stream"}), `${filename}.${bookType}`)
}

HTML文件中实现导出函数

  <script>console.log(XLSX)  // 打印看看  XLSXconsole.log(FileSaver)  // 打印看看  FileSaverconsole.log(export_json_to_excel)  // 打印看看export_json_to_excel 方法function downLoad() {let arr = [{sno: 1000,username: "fqniu",gender: "男",age: 27,card: "4****************7"}, {sno: 1001,username: "fqniu1",gender: "男",age: 27,card: "4****************8"},{sno: 1002,username: "fqniu2",gender: "男",age: 27,card: "4****************9"}]// 单级表格const tHeader = ["编号", "姓名", "性别", "年龄", "身份证号"]const filterVal = ["sno", "username", "gender", "age", "card"]const list = arrconst data = formatJson(filterVal, list)export_json_to_excel({header: tHeader,data,filename: "单级表格",autoWidth: true,bookType: "xlsx",})}// 多级表头function downLoad1() {let arr1 = [{date: '2024-08-17',num1: '1',count1: '10',num2: '11',count2: '100',num3: '111',count3: '1000',total: '1110',},{date: '2024-08-18',num1: '2',count1: '20',num2: '22',count2: '200',num3: '222',count3: '2000',total: '2220',},{date: '2024-08-19',num1: '3',count1: '30',num2: '33',count2: '300',num3: '333',count3: '3000',total: '3330',}]let filterVal1 = ['date', 'num1', 'count1', 'num2', 'count2', 'num3', 'count3', 'total']const data1 = formatJson(filterVal1, arr1)export_json_to_excel({multiHeader: [['日期', '上期结存', '', '本期结存', '', '下期结存', '', '总数']],header: ['', '数量', '金额', '数量', '金额', '数量', '金额', ''], //表头 必填data: data1, //具体数据 必填filename: '多级表头的表格',// 要合并的单元格merges: ['A1:A2', 'B1:C1', 'D1:E1', 'F1:G1', 'H1:H2'],autoWidth: true,bookType: 'xlsx',})}function formatJson(filterVal, jsonData) {return jsonData.map((v) =>filterVal.map((j) => {if (j === "timestamp") {return parseTime(v[j])} else {return v[j]}}))}</script>

导出的Excel文件内容截图如下：

单级表头效果

在这里插入图片描述

多级级表头效果

在这里插入图片描述

注意点：

导出多级表头首先要理解，单元格合并规则：

日期所占单元格为 A1:A3
配送信息 所占单元格为 B1:F1
姓名所占单元格为 B2:B3

然后依次类推即可

在这里插入图片描述
注：图片引用网络，仅用于学习分享

HTML总代码实现汇总（直接复制代码，注意js引入路径）

<!DOCTYPE html>
<html lang="en"><head><meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0"><title>Document</title>
</head><body><script src="./js/FileSaver.min.js"></script><script src="./js/xlsx.full.min.js"></script><script src="./js/Export2Excel.js"></script><script>console.log(XLSX)console.log(FileSaver)console.log(export_json_to_excel)function downLoad() {let arr = [{sno: 1000,username: "fqniu",gender: "男",age: 27,card: "4****************7"}, {sno: 1001,username: "fqniu1",gender: "男",age: 27,card: "4****************8"},{sno: 1002,username: "fqniu2",gender: "男",age: 27,card: "4****************9"}]// 单级表格const tHeader = ["编号", "姓名", "性别", "年龄", "身份证号"]const filterVal = ["sno", "username", "gender", "age", "card"]const list = arrconst data = formatJson(filterVal, list)export_json_to_excel({header: tHeader,data,filename: "单级表格",autoWidth: true,bookType: "xlsx",})}// 多级表头function downLoad1() {let arr1 = [{date: '2024-08-17',num1: '1',count1: '10',num2: '11',count2: '100',num3: '111',count3: '1000',total: '1110',},{date: '2024-08-18',num1: '2',count1: '20',num2: '22',count2: '200',num3: '222',count3: '2000',total: '2220',},{date: '2024-08-19',num1: '3',count1: '30',num2: '33',count2: '300',num3: '333',count3: '3000',total: '3330',}]let filterVal1 = ['date', 'num1', 'count1', 'num2', 'count2', 'num3', 'count3', 'total']const data1 = formatJson(filterVal1, arr1)export_json_to_excel({multiHeader: [['日期', '上期结存', '', '本期结存', '', '下期结存', '', '总数']],header: ['', '数量', '金额', '数量', '金额', '数量', '金额', ''], //表头 必填data: data1, //具体数据 必填filename: '多级表头的表格',// 要合并的单元格merges: ['A1:A2', 'B1:C1', 'D1:E1', 'F1:G1', 'H1:H2'],autoWidth: true,bookType: 'xlsx',})}function formatJson(filterVal, jsonData) {return jsonData.map((v) =>filterVal.map((j) => {if (j === "timestamp") {return parseTime(v[j])} else {return v[j]}}))}</script><div id="box">点击下载单级表格</div><div id="box1">点击下载多级表格</div><script>let box = document.getElementById('box')box.onclick = () => {downLoad()}let box1 = document.getElementById('box1')box1.onclick = () => {downLoad1()}</script></body>
</html>