const tableToExcel = () => {
// 要导出的json数据
const jsonData = [
{
name:'路人甲1',
phone:'123456',
email:'123@123456.com'
},
{
name:'炮灰乙',
phone:'123456',
email:'123@123456.com'
},
{
name:'土匪丙',
phone:'123456',
email:'123@123456.com'
},
{
name:'流氓丁1',
phone:'123456',
email:'123@123456.com'
},
]
// 列标题
let str = '<tr><td>姓名</td><td>电话</td><td>邮箱</td></tr>';
// 循环遍历，每行加入tr标签，每个单元格加td标签
for(let i = 0 ; i < jsonData.length ; i++ ){
str+='<tr>';
for(const key in jsonData[i]){
// 增加\t为了不让表格显示科学计数法或者其他格式
str+=`<td>${ jsonData[i][key] + '\t'}</td>`;
}
str+='</tr>';
}
// Worksheet名
const worksheet = 'Sheet1'
// const uri = 'data:application/vnd.ms-excel;base64,'; // xls
const uri = 'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,'; // xlsx
// 下载的表格模板数据
const template = `<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>${str}</table></body></html>`;
// 下载模板
// window.location.href = uri + window.btoa(unescape(encodeURIComponent(template)));
const link = document.createElement('a')
link.href = uri + window.btoa(unescape(encodeURIComponent(template)));
link.download = '123.xlsx' // 对下载的文件命名
link.click()
};
export default {
exportExcel: (columns = [], list = [], fileName = '下载', fileType = 'xls', sheetName = 'Sheet1') => {
let str = ''
if (columns.length) {
str += '<tr>'
columns.forEach(column => {
str += '<th>' + column['cnName'] + '</th>'
});
str += '</tr>';
}
list.forEach((row) => {
str += '<tr>';
columns.forEach(column => {
str += `<td>${row[column['enName']] + '\t'}</td>`;
})
str += '</tr>';
})
let uri = '';
if (fileType === 'xlsx') {
uri = 'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,'; // xlsx
} else {
uri = 'data:application/vnd.ms-excel;base64,' // xls
}
// 下载的表格模板数据
let template = '';
template += '<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">'
template += '<head> '
template += ' <!--[if gte mso 9]>'
template += ' <xml> '
template += ' <x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet> '
template += ' <x:Name>' + sheetName + '</x:Name> '
template += ' <x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet> '
template += ' </x:ExcelWorksheets></x:ExcelWorkbook> '
template += ' </xml> '
template += ' <![endif]--> '
template += '<style>td{text-align: center;border:0.5px solid #808080;}</style> '
template += '</head> '
template += '<body>'
template += ' <table style="border:0.5px solid #808080;border-collapse: collapse;">' + str + '</table>'
template += '</body>'
template += '</html>'
// 下载模板
// window.location.href = uri + window.btoa(unescape(encodeURIComponent(template)));
const link = document.createElement('a')
link.href = uri + window.btoa(unescape(encodeURIComponent(template)));
link.download = fileName + '.' + fileType // 对下载的文件命名 例如 123.xls
link.click()
}
}
// 调用
let columns = [
{'enName': 'name', 'cnName': '姓名'},
{'enName': 'age', 'cnName': '年龄'},
]
let list = [
{name: '张三张三张三张三张三', age: 30},
{name: '李四', age: 40},
];
xysExcel.exportExcel(columns, list)
网友评论