vue实现在线Excel表格功能

1.安装x-data-spreadsheet xlsx

2.引入

3.使用

1.安装x-data-spreadsheet xlsx

npm i x-data-spreadsheet xlsx

2.引入

import zhCN from "x-data-spreadsheet/src/locale/zh-cn";
import Spreadsheet from "x-data-spreadsheet";
import * as XLSX from "xlsx";
Spreadsheet.locale("zh-cn", zhCN);

3.使用

<template><div class="CptOutbox"><div class="ToolOutbox"><input type="file" @change="chageFile" ref="inputFile" /><button @click="exportExcel">导出xlsx</button><button @click="SaveExcel">批量保存</button></div><div class="ExcelOutbox"><!--web spreadsheet组件--><divclass="sheetContainerbox"ref="sheetContainer"id="x-spreadsheet-demo"></div></div></div>
</template><script>
//引入依赖包
import zhCN from "x-data-spreadsheet/src/locale/zh-cn";
import Spreadsheet from "x-data-spreadsheet";
import * as XLSX from "xlsx";
Spreadsheet.locale("zh-cn", zhCN);
export default {name: "XspreadsheetDemo",data() {return {xs: null,};},mounted() {this.init();},methods: {init() {this.xs = new Spreadsheet("#x-spreadsheet-demo", {mode: "edit",showToolbar: true,showGrid: true,showContextmenu: true,showBottomBar: true,view: {height: () => this.$refs.sheetContainer.offsetHeight,width: () => this.$refs.sheetContainer.offsetWidth,},style: {bgcolor: "#ffffff",align: "left",valign: "middle",textwrap: false,strike: false,underline: false,color: "#0a0a0a",font: {name: "Helvetica",size: 10,bold: false,italic: false,},},row: {len: 10000,height: 25,},col: {len: 26,width: 100,indexWidth: 60,minWidth: 60,},});let excelData = localStorage.getItem("WebExcelData");if (excelData) {this.xs.loadData(excelData); // load data}this.xs.change((data) => {localStorage.setItem("WebExcelData", data);});// data validationthis.xs.validate();import('./data.json').then((res) => {console.log(`res`, res);localStorage.setItem("WebExcelData", res.Data);this.xs.loadData(res.Data)})},exportExcel() {var new_wb = this.xtos(this.xs.getData());console.log(`new_wb`, new_wb);XLSX.writeFile(new_wb, "SheetJS.xlsx");},xtos(sdata) {console.log(sdata);var out = XLSX.utils.book_new();sdata.forEach((xws) => {var aoa = [[]];var rowobj = xws.rows;for (var ri = 0; ri < rowobj.len; ++ri) {var row = rowobj[ri];if (!row) continue;aoa[ri] = [];Object.keys(row.cells).forEach((k) => {var idx = +k;if (isNaN(idx)) return;aoa[ri][idx] = row.cells[k].text;});}var ws = XLSX.utils.aoa_to_sheet(aoa);/** 读取在线中的合并单元格，并写入导出的数据中* merges: Array(19)0: "A16:P16"1: "A17:P17"2: "O2:P2"3: "F2:G2"*/ws["!merges"] = [];xws.merges.forEach((merge) => {ws["!merges"].push(XLSX.utils.decode_range(merge));});XLSX.utils.book_append_sheet(out, ws, xws.name);});return out;},chageFile() {this.importExcel(this.$refs.inputFile.files[0]);},importExcel(file) {let reader = new FileReader();reader.onload = (e) => {let data = e.target.result;let fixedData = this.fixData(data);let workbook = XLSX.read(btoa(fixedData), { type: "base64" });console.log(workbook);console.log("this.stox(workbook)",this.stox(workbook));this.xs.loadData(this.stox(workbook));};reader.readAsArrayBuffer(file);// return workbook},fixData(data) {var o = "",l = 0,w = 10240;for (; l < data.byteLength / w; ++l)o += String.fromCharCode.apply(null,new Uint8Array(data.slice(l * w, l * w + w)));o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)));return o;},stox(wb) {console.log(`wb`, wb);var out = [];wb.SheetNames.forEach((name) => {var o = { name: name, rows: {}, merges: [] };var ws = wb.Sheets[name];var aoa = XLSX.utils.sheet_to_json(ws, { raw: false, header: 1 });aoa.forEach((r, i) => {var cells = {};r.forEach((c, j) => {cells[j] = { text: c };});o.rows[i] = { cells: cells };});///console.log(`ws`, ws);// 设置合并单元格if (ws["!merges"]) {ws["!merges"].forEach((merge) => {/** merge = {*  s: {c: 0, r: 15}*  e: {c: 15, r: 15}* }*/// 修改 cell 中 merge [合并行数,合并列数]let cell = o.rows[merge.s.r].cells[merge.s.c];//无内容单元格处理if (!cell) {cell = { text: "" };}cell.merge = [merge.e.r - merge.s.r, merge.e.c - merge.s.c];o.rows[merge.s.r].cells[merge.s.c] = cell;// 修改 mergeso.merges.push(XLSX.utils.encode_range(merge));});}out.push(o);});return out;},///SaveExcel() {},},
};
</script><style lang="scss" scoped>
.CptOutbox {width: 100%;height: 100%;.ToolOutbox {display: flex;flex-flow: row nowrap;align-items: center;width: 100%;height: 50px;}.ExcelOutbox {display: flex;flex-flow: row nowrap;align-items: center;width: 100%;height: calc(100% - 50px);.sheetContainerbox {width: 100%;height: 100%;}}
}
</style>