js利用sheetjs生成excel并导出
sheetjs
📗 SheetJS Spreadsheet Data Toolkit -- New home https://git.sheetjs.com/SheetJS/sheetjs
项目地址:https://gitcode.com/gh_mirrors/sh/sheetjs
免费下载资源
·
下载sheet.js
github官方地址 : https://github.com/SheetJS/js-xlsx
引入sheet.js
<script src="./sheetjs-master/dist/xlsx.full.min.js"></script>
依赖js
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);
}
// 将一个sheet转成最终的excel文件的blob对象,然后利用URL.createObjectURL下载
function sheet2blob(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;
}
点击下载
var aoa = [
['姓名', '性别', '年龄', '注册时间'],
['张三', '男', 18, new Date()],
['李四', '女', 22, new Date()]
];
var sheet = XLSX.utils.aoa_to_sheet(aoa);
openDownloadDialog(sheet2blob(sheet), '导出.xlsx');
可以格式化aoa和sheet
function dataToAoa(list) {
var hashHeadToChinese = {
name: '姓名',
mobile: '手机',
goods_name: '所报项目',
admin_realname: '学习顾问',
success_time: '成交时间'
}
let aoa = [],
header = []
Object.keys(hashHeadToChinese).forEach((key) => {
header.push(hashHeadToChinese[key])
if (key === 'goods_name') {
header.push(null, null, null)
} else if (key === 'admin_realname' || key === 'success_time') {
header.push(null, null)
} else if (key === 'mobile') {
header.push(null)
}
})
aoa.push(header)
list.forEach(item => {
let line = [];
Object.keys(hashHeadToChinese).forEach(key => {
line.push(item[key])
if (key === 'goods_name') {
line.push(null, null, null)
} else if (key === 'admin_realname' || key === 'success_time') {
line.push(null, null)
} else if (key === 'mobile') {
line.push(null)
}
})
aoa.push(line)
})
return aoa
}
$('.getsheet').on('click', () => {
let excelName = '表格'
if (userList && userList.length !== 0) {
let aoa = dataToAoa(userList)
var tableName = window.prompt('请输入你要导出的表格的名字,例如: 表格')
var sheet = XLSX.utils.aoa_to_sheet(aoa);
let merges = [];
for (let i = 0; i < aoa.length; i++) {
merges.push({
s: { // 合并第i行的第1个到第2个单元格为一个格
r: i,
c: 1
},
e: {
r: i,
c: 2
}
}, {
s: { // 合并第i行的第3个到第6个单元格为一个格
r: i,
c: 3
},
e: {
r: i,
c: 6
}
}, {
s: {
r: i,
c: 7
},
e: {
r: i,
c: 9
}
}, {
s: {
r: i,
c: 10
},
e: {
r: i,
c: 13
}
})
}
sheet['!merges'] = merges;
console.log(merges);
openDownloadDialog(sheet2blob(sheet), `${tableName}.xlsx`);
} else {
alert('数据异常,生成excel失败')
}
})
GitHub 加速计划 / sh / sheetjs
34.82 K
8 K
下载
📗 SheetJS Spreadsheet Data Toolkit -- New home https://git.sheetjs.com/SheetJS/sheetjs
最近提交(Master分支:1 个月前 )
3f44ddd9 - 7 个月前
5b4806bf - 1 年前
更多推荐
已为社区贡献1条内容
所有评论(0)