SheetJS/js-xlsx修改表头实例操作:
你需要去下载这个包:xlsx.full.min.js
第一步:创建一个工作簿(workbook)
let workbook = XLSX.utils.book_new();
第二步:创建一个对象这个里面的数据对应是的你json数据源的title,这是我的数据源
(list: [
{ “id”: “123456”, “name”: “rfid”, “code”: “20211211” },
{ “id”: “1234567”, “name”: “rfid7”, “code”: “202112117” },
])
var titles = {id: ‘编号’, name: ‘名称’, code: ‘条码’ }
第三步:把json对象转成工作表,调用json_to_sheet方法
let sheet2 = XLSX.utils.json_to_sheet(this.list);
重点来了!
let wss = XLSX.utils.decode_range(sheet2["!ref"])
sheet2["!ref"]:这个是可以查看到你这个表格有多少行
这个图中可以看到有14条数据(包含title),注意看A1,B1,C1(没截图出来),都是头部的title
再看这个wss.e中有2个参数,c是值的抛开title标题,第二行开始有我们填充的数据,r是我们填充的数据在13行就填充完了
s: {
c: 0, // 第一行
r: 0 // 第一列
}
e: {
c: 2, // 第三列
r: 13 // 第十四行
}
其中的c对应的是一共有多少列,r是行。s.c到e.c一共3列,刚好和我们的数据列对应
开始循环替换中文头部,这个里面的sheet2[header].v中的v是第一幅图中的value值,比如:sheet2.A1.v 这个值是 id ,sheet2.A2.v 这个值是 ‘123456’
XLSX.utils.encode_col©:这个是获取表格的列入A列,B列,C列,
XLSX.utils.encode_col© + ‘1’:这个个刚好可以得到A1列的标题,一次类推
for (let c = wss.s.c; c <= wss.e.c; c++) {
debugger
var header = XLSX.utils.encode_col© + ‘1’
sheet2[header].v = titles[sheet2[header].v]
}
第三步:在工作簿中添加工作表
XLSX.utils.book_append_sheet(workbook, sheet2, ‘sheetName1’)
var myDate = new Date;
let workBookName = “扫描数据-” + myDate.getFullYear() + “-” + myDate.getMonth() + “-” + myDate.getDate() + “-” + myDate.getHours() + “-” + myDate.getMinutes() + “-” + myDate.getSeconds() + “.xlsx”
第四步:
XLSX.writeFile(workbook, workBookName); // 保存的文件名
补充说明:
excel中 列以 A开始,行以1开始,在XLSX中转化为数值后行与列都是0开始
encode_row / decode_row 转化行号
encode_col / decode_col 转化列号
encode_cell / decode_cell 转化单元格号
encode_range / decode_range 转化表格范围
如果您的工作表数据是自动生成的,并且您不知道填充了多少行和列,那么您可以使用以下方法查找工作表中的行数和列数,以进行单元格宽度/高度格式化。
var range = XLSX.utils.decode_range(ws[’!ref’]);
var noRows = range.e.r; // No.of rows
var noCols = range.e.c; // No. of cols
固定宽度:
sheet2["!cols"] = [
{ wch: 20 },
{ wch: 30 },
{ wch: 30 }
];
本文参考了SheetJS/js-xlsx修改表头
可以具体的去看看XLSX.utils.encode_col和XLSX.utils.decode_range这两个的具体用法和含义
最后附上完整的demo
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title></title>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/vant@2.12/lib/index.css" />
<script src="https://cdn.jsdelivr.net/npm/vue@2.6/dist/vue.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/vant@2.12/lib/vant.min.js"></script>
<script src="C:\Users\tangda\source\repos\Vant\Vant\Html\xlsx.full.min.js"></script>
</head>
<body>
<div id="app">
<van-tabs v-model="activeName">
<van-tab title="条码" name="code">
{{codeTitle}}
</van-tab>
<van-tab title="RFID" name="rfid" gutter="20">
<van-row type="flex" justify="space-around" style="margin:10px 0px">
<van-col justify="center">
<van-button type="primary" size="small">开始扫描</van-button>
</van-col>
<van-col justify="center">
<van-button type="primary" size="small">关闭扫描</van-button>
</van-col>
</van-row>
<van-list v-model="loading" :finished="finished" finished-text="没有更多了" @load="onLoad">
<van-row gutter="20">
<van-col span="8" class="context">id</van-col>
<van-col span="8" class="context">名称</van-col>
<van-col span="8" class="context">编号</van-col>
</van-row>
<van-row gutter="20" v-for="item in list" :key="item" :title="item.rfid">
<van-col span="8" class="context">{{ item.id }}</van-col>
<van-col span="8" class="context">{{ item.name }}</van-col>
<van-col span="8" class="context">{{ item.code }}</van-col>
</van-row>
</van-list>
</van-tab>
</van-tabs>
<div style="position: fixed; right: 0; bottom: 0px; left: 0; padding: 10px 10px;">
<van-row type="flex" justify="end" style="padding: 10px 0px;">
<van-col justify="center">
总计:{{listlengthnumber}}
</van-col>
</van-row>
<van-row type="flex" justify="end" gutter="50">
<van-col justify="center">
<van-button type="primary" size="small">发送邮件</van-button>
</van-col>
<van-col justify="center">
<van-button type="primary" size="small" v-on:click="exportData">导出excel</van-button>
</van-col>
</van-row>
</div>
</div>
<style>
.context {
justify-content: center;
align-items: center;
display: flex;
}
</style>
<script>
Vue.use(vant.Tab);
Vue.use(vant.Tabs);
Vue.use(vant.NavBar);
Vue.use(vant.List);
Vue.use(vant.Button);
Vue.use(vant.Toast);
new Vue({
el: "#app",
data: {
activeName: 'rfid',
codeTitle: "条码",
rfidTitle: "RFID",
listlengthnumber: "0",
list: [
{ "id": "123456", "name": "rfid", "code": "20211211" },
{ "id": "1234567", "name": "rfid7", "code": "202112117" },
{ "id": "12345678", "name": "rfid8", "code": "202112118" },
{ "id": "123456", "name": "rfid", "code": "20211211" },
{ "id": "1234567", "name": "rfid7", "code": "202112117" },
{ "id": "12345678", "name": "rfid8", "code": "202112118" },
{ "id": "123456", "name": "rfid", "code": "20211211" },
{ "id": "1234567", "name": "rfid7", "code": "202112117" },
{ "id": "12345678", "name": "rfid8", "code": "202112118" },
{ "id": "123456", "name": "rfid", "code": "20211211" },
{ "id": "1234567", "name": "rfid7", "code": "202112117" },
{ "id": "12345678", "name": "rfid8", "code": "202112118" }
],
loading: false,
finished: false,
},
methods: {
onLoad() {
var vm = this
//vant.Toast({
// duration: 1000,
// message: '加载数据',
// icon: 'like-o',
//});
this.list.push({ "id": "1234567899", "name": "rfid8", "code": "202112118" })
this.listlengthnumber = this.list.length;
if (this.list.length >= 50) {
this.finished = true;
}
this.loading = false;
},
exportData() {
//1. 新建一个工作簿
let workbook = XLSX.utils.book_new();
//2. 生成一个工作表,
//2.1 aoa_to_sheet 把数组转换为工作表
// let sheet1 = XLSX.utils.aoa_to_sheet(data1);
//2.2 把json对象转成工作表
/* let sheet2 = XLSX.utils.json_to_sheet(data 2);*/
//let sheet2 = XLSX.utils.json_to_sheet(this.list, fields, titles); { header: ['编号', '名称', '条码'] }
var fields = ['id', 'name', 'code']
var titles = {
id: '编号',
name: '名称',
code: '条码'
}
// let sheet2 = XLSX.utils.json_to_sheet(this.list, {header: fields});
let sheet2 = XLSX.utils.json_to_sheet(this.list);
let wss = XLSX.utils.decode_range(sheet2["!ref"])
for (let c = wss.s.c; c <= wss.e.c; c++) {
debugger
var header = XLSX.utils.encode_col(c) + '1'
sheet2[header].v = titles[sheet2[header].v]
}
debugger;
//3.在工作簿中添加工作表
// XLSX.utils.book_append_sheet(workbook, sheet1, 'sheetName1'); //工作簿名称
XLSX.utils.book_append_sheet(workbook, sheet2, 'sheetName2'); //工作簿名称
// XLSX.utils.sheet_add_json(sheet1,data2);//把已存在的sheet中数据替换成json数据
//4.输出工作表,由文件名决定的输出格式
var myDate = new Date;
let workBookName = "扫描数据-" + myDate.getFullYear() + "-" + myDate.getMonth() + "-" + myDate.getDate() + "-" + myDate.getHours() + "-" + myDate.getMinutes() + "-" + myDate.getSeconds() + ".xlsx"
//XLSX.writeFile(workbook, 'workBook1.xlsx'); // 保存的文件名
XLSX.writeFile(workbook, workBookName); // 保存的文件名
}
}
})
</script>
</body>
</html>