纯前端实现xls表格下载
1.createXlsStr:生成xls模板字符串函数
- xls字符串,基本不需要变更,我们只需要通过变量控制结果即可
- worksheet:xls的文件名(不是下载的文件名!)
- contentStr: xls表格内部的数据(主要是tr和td元素拼接成的字符串)
function createXlsStr(worksheet,contenStr){
return `
<html
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40"
>
<head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>
<x:Name>${worksheet}</x:Name>
<x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>
</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
</head>
<body>
<table>${contenStr}</table>
</body>
</html>
`;
}
2.tableToExcel:表格数据转换xls
- window.btoa()将去空格的encodeURIComponent转换的str转为base64
- createDataStr生成内容字符串
- 利用浏览器特性,将基础类型+准换后的base64,触发跳转下载
- 参数:jsonData-表格数据源,tableOptions-表格配置项
function tableToExcel(params){
const {worksheet, jsonData, tableOptions} = params;
const uri = 'data:application/vnd.ms-excel;base64,';
const base64 = s => window.btoa(unescape(encodeURIComponent(s)));
const contenStr = '<tr><td>测试</td></tr>'
const template =createXlsStr(worksheet,contenStr)
window.location.href = uri + base64(template);
}
3.createDataStr:封装配置化表单
- jsonData:[{},{},{}],数据源,数组对象。
- tableOptions:表格配置项,[{label:striing,key:string,render:(record:any)=>void}]
- label:表头名称
- key:数据绑定的字段名
- render:如需要自定义或者自己计算属性,将会渲染return的结果
function createDataStr(jsonData, tableOptions){
let str = tableOptions.reduce((prev, next,index) => {
prev+=`<td>${next.label}</td>${index === tableOptions.length-1 ? '</tr>':''}`
return prev
},'<tr>');
for(let i = 0 ; i < jsonData.length ; i++ ){
const item =jsonData[i]
str+='<tr>';
for (let s = 0; s < tableOptions.length; s++) {
str+=`<td>${ tableOptions[s].render ? tableOptions[s].render(item) : item[tableOptions[s].key] + '\t'}</td>`;
}
str+='</tr>';
}
return str;
}
4.完整代码:不积跬步无以至千里
function createDataStr(jsonData, tableOptions){
let str = tableOptions.reduce((prev, next,index) => {
prev+=`<td>${next.label}</td>${index === tableOptions.length-1 ? '</tr>':''}`
return prev
},'<tr>');
for(let i = 0 ; i < jsonData.length ; i++ ){
const item =jsonData[i]
str+='<tr>';
for (let s = 0; s < tableOptions.length; s++) {
str+=`<td>${ tableOptions[s].render ? tableOptions[s].render(item) : item[tableOptions[s].key] + '\t'}</td>`;
}
str+='</tr>';
}
return str;
}
function createXlsStr(worksheet,contenStr){
return `
<html
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40"
>
<head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>
<x:Name>${worksheet}</x:Name>
<x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>
</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
</head>
<body>
<table>${contenStr}</table>
</body>
</html>
`;
}
function tableToExcel(params){
const {worksheet, jsonData, tableOptions} = params;
const uri = 'data:application/vnd.ms-excel;base64,';
const base64 = s => window.btoa(unescape(encodeURIComponent(s)));
const contenStrDefault = '<tr><td>测试</td></tr>'
const contenStr = createDataStr(jsonData, tableOptions)
const template =createXlsStr(worksheet,contenStr)
window.location.href = uri + base64(template);
}
tableToExcel({
worksheet:'测试表格',
jsonData:[{name:'小明',age:18, time:'今天'},{name:'小红',age:18, time:'今天'}],
tableOptions:[
{label:'姓名',key:'name'},
{label:'年龄',key:'age'},
{label:'时间',key:'time',render:(record)=>Date().toLowerCase()},
],
})