前端方式导入导出xlsx
直接上代码： 所需的插件(xlsx) 提取码：gfg4
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>xlsx导入导出</title> </head> <body> <input type="file" onchange="importFile(event)"> <br> <button onclick="downXLSX()">下载</button> <script src="./xlsx.core.min.js"></script> <script>
// 如果有先使用导入功能将数据缓存下来，作为下载数据 var importJson = [];
// 导入 function importFile(e) { let render = new FileReader(); render.onload = function () { var workbook = XLSX.read(this.result, { type: "binary" }); filterSheet(workbook); } render.readAsBinaryString(e.target.files[0]) } function filterSheet(workbook) { var sheetNames = workbook.SheetNames; // 工作表名称集合 var worksheet = workbook.Sheets[sheetNames[0]]; let arr = []; let obj = {}; for (var key in worksheet) { // v是读取单元格的原始值 // console.log(key, key[0] === "!" ? worksheet[key] : worksheet[key].v); if (key[0] !== "!") { if (key[0] == "A") { obj["mobile"] = worksheet[key].v; } else if (key[0] == "B") { obj["province"] = worksheet[key].v; } else if (key[0] == "C") { obj["city"] = worksheet[key].v; arr.push(obj); obj = {}; } } } console.log(arr) arr.shift(); // 去除标题 importJson = arr; console.log(importJson) // 现在这个数据可以上传都后端 } // 导出 function json_to_array(key, jsonData) { return jsonData.map(v => key.map(j => { return v[j] })); } function auto_width(ws, data) { /*set worksheet max width per col*/ const colWidth = data.map(row => row.map(val => { /*if null/undefined*/ if (val == null) { return { 'wch': 10 }; } /*if chinese*/ else if (val.toString().charCodeAt(0) > 255) { return { 'wch': val.toString().length * 2 }; } else { return { 'wch': val.toString().length }; } })) /*start in the first row*/ let result = colWidth[0]; for (let i = 1; i < colWidth.length; i++) { for (let j = 0; j < colWidth[i].length; j++) { if (result[j]['wch'] < colWidth[i][j]['wch']) { result[j]['wch'] = colWidth[i][j]['wch']; } } } ws['!cols'] = result; } function export_array_to_excel({ key, data, title, filename, autoWidth }) { const wb = XLSX.utils.book_new(); const arr = json_to_array(key, data); arr.unshift(title); const ws = XLSX.utils.aoa_to_sheet(arr); if (autoWidth) { auto_width(ws, arr); } XLSX.utils.book_append_sheet(wb, ws, filename); XLSX.writeFile(wb, filename + '.xlsx'); } function downXLSX() { let tempTitle = ['手机', '省份', '城市']; let tempKey = ['mobile', 'province', 'city']; let tempData = importJson.length > 0 ? importJson : [ { mobile: 13512345678, province: '广东省', city: '广州市' }, { mobile: 13612345678, province: '广西省', city: '南宁市' }, { mobile: 13712345678, province: '福建省', city: '福州市' }, ]; const params = { title: tempTitle, key: tempKey, data: tempData, autoWidth: true, filename: "测试列表" }; export_array_to_excel(params); } </script> </body> </html>
代码里面有简单的注释
