使用js-xlsx读取sheet和导出sheet文件
实现需求：1.将后端返回的xlsx数据展示在页面上，但不是用elementuiui的table展示，是动态生成表格
2.将xlsx数据导出成sheet（下载导本地）
使用的插件：js-xlsx
实现步骤：安装：npm i xlsx -S
使用：import XLSX from 'xlsx';
我先用后端返回的由对象组成的数组
代码如下：
生成的table显示在下面的div里
<div id="xlsxTable" :style="{height:this.tableHeight}"> <div v-html="this.table"></div> </div>
this.tableData.list 的数据如下
[{ date:'2020-01-08', salary:200, reward:30, },{ date:'2020-01-09', salary:300, reward:20, },{ date:'2020-02-08', salary:200, reward:30, },{ date:'2020-01-10', salary:300, reward:40, }]
workbook:'',
table:'',
tableHeight:0,
sheet1:'',
blob:'',
<!-- 将json数组转为table展示 --> <el-button @click="table">xlsx读取</el-button> <!-- 将table数据转成sheet下载 --> <el-button @click="sheet">xlsx导出sheet</el-button>
table(){ // 读取xlsx文件为table，在页面上显示，如果后端返回的是sheet数据，就不需要json_to_sheet这一步了 console.log(this.tableData.list,'list') var sheet1 = XLSX.utils.json_to_sheet(this.tableData.list) // 1.转成sheet(workbook) console.log(sheet1,'sheet1-980') this.sheet1 = sheet1 this.table = this.csv2table(XLSX.utils.sheet_to_csv(sheet1)) // 2.将sheet转成csv,再利用csv2table方法转为拼接成表格,也可以直接用sheet_to_html直接转成table // this.table = XLSX.utils.sheet_to_html(sheet1) this.tableHeight ='400px' // document.getElementById('table').innerHTML = this.table // console.log(this.table,'table-980') }, csv2table(csv){ var html = '<table width="100%" cellspacing="0" align="center">'; var rows = csv.split('\n'); rows.pop(); // 最后一行没用的 rows.forEach(function(row, idx) { var columns = row.split(','); columns.unshift(idx+1); // 添加行索引 html += '<tr>'; columns.forEach(function(column) { html += '<td width="100px">'+column+'</td>'; }); html += '</tr>'; }); html += '</table>'; return html; }, // 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载 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); this.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 this.blob; }, 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(){ // 将xlsx文件导出 // var sheet = this.csv2sheet(csv); var blob = this.sheet2blob(this.sheet1); this.openDownloadDialog(blob, '导出.xlsx'); },
最后效果：
下面结合实际项目中使用的情况记录下
1.实现上传本地execl后在页面上显示为table
<el-upload action="/active/uploadExcel" :show-file-list="false" :on-success="CleanSheetSuccess" :before-upload="beforeAvatarUpload"> <el-button size="small" style="display:inline">上传execl</el-button> </el-upload> // 上传成功后 CleanSheetSuccess(res){ if (res.code == "ACK") { this.sheetUrl = res.data.url this.$message.success('上传成功') } else { this.$message({ duration: 0, showClose: true, message: res.error, type: "error" }); } },
// 将csv拼接成table csv2table(csv){ var html = '<table width="100%" cellspacing="0" align="center">'; var rows = csv.split('\n'); rows.pop(); // 最后一行没用的 rows.forEach(function(row, idx) { var columns = row.split(','); columns.unshift(idx+1); // 添加行索引 html += '<tr>'; columns.forEach(function(column) { html += '<td width="100px">'+column+'</td>'; }); html += '</tr>'; }); html += '</table>'; return html; },
//上传文件之前判断类型 beforeAvatarUpload(file) {
// 这里我上传了file文件，我想直接把file文件利用XLSX转为table
var reader = new FileReader();
reader.onload = function(e) {
var data = e.target.result;
console.log(e,'dat')
var workbook = XLSX.read(data, {type: 'binary'});
// if(callback) callback(workbook);
console.log(workbook,'workbook988')
this.table = this.csv2table(XLSX.utils.sheet_to_csv(workb ook.Sheets[workbook.SheetNames[0]]))
console.log(this.table ,'table988')
};
console.log(file,'file') const isEXEL = file.type === "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; if (!isEXEL) { this.$message.error("请导入excel 文件!"); } return isEXEL; },
// 在页面中显示 ，contenteditable ：table可以编辑
<div id="xlsxTable" contenteditable :style="{height:this.tableHeight}">
<div v-html="this.table"></div>
</div>
2.上传execl文件后，实现将后端返回的.xlsx的execl链接转为table显示在页面上（每次点击查看可以预览生成的table）
// this.urlPath 是后端返回的execl链接，利用readWorkbookFromRemoteFile 方法返回workbook，
再利用XLSX的api：sheet_to_csv，最后转为table显示在页面上
this.readWorkbookFromRemoteFile(this.urlPath, (workbook) => { console.log(workbook,'workbook') this.table = this.csv2table(XLSX.utils.sheet_to_csv(workbook.Sheets[workbook.SheetNames[0]]))
// 这里是只显示一个table，如果后端给你返回的execl链接里有多个sheet表，就需要循环展示出表名和表格
var workData =[]
for(const i in workbook.SheetNames){
workData.push(workbook.Sheets[workbook.SheetNames[i]])
this.table.push({
table:this.csv2table(XLSX.utils.sheet_to_csv(workData[i])),
title:workbook.SheetNames[i]})
}
}) readWorkbookFromRemoteFile(url, callback) { var xhr = new XMLHttpRequest(); xhr.open('get', url, true); xhr.responseType = 'arraybuffer'; xhr.onload = function(e) { if(xhr.status == 200) { var data = new Uint8Array(xhr.response) var workbook = XLSX.read(data, {type: 'array'}); if(callback) callback(workbook); } }; xhr.send(); },
加油！
