1. 环境
vue：2.6.10
x-data-spreadsheet：1.1.8
less：4.0.0
less-loader：7.1.0
xlsx：0.15.1
axios：0.18.1
2. 优点
可以导入本地excel文件，读取文件数据及合并单元格信息，呈现在线上excel中。可以将线上的excel导出成excel文件，并保留合并单元格信息可直接上传在线的excel文件可直接读取网络上的excel文件3. 缺点
不能读取本地excel文件中的样式信息不能导出线上excel文件样式信息3. 代码
3.1 前端代码
<template><div><div class="mb-md"><input type="file" @change="getWorkbook"><button @click="exportJson">导出JSON</button><button @click="exportExcel">导出xlsx</button><button @click="uploadExcel">上传xlsx</button><button @click="downloadExcel">下载xlsx</button></div><!--web spreadsheet组件--><div id="x-spreadsheet-demo"></div></div></template><script>//引入依赖包import zhCN from 'x-data-spreadsheet/src/locale/zh-cn';import Spreadsheet from 'x-data-spreadsheet';import XLSX from 'xlsx'import axios from 'axios'//设置中文Spreadsheet.locale('zh-cn',zhCN);export default {name: "xspreadsheet-demo",data() {return {xs: null,jsondata: {type: '',label: ''},};},mounted() {this.init()},methods: {init() {this.xs = new Spreadsheet('#x-spreadsheet-demo', {showToolbar: true, showGrid: true}).loadData([]).change((cdata) => {// console.log(cdata);console.log('>>>', this.xs.getData());});this.xs.on('cell-selected', (cell, ri, ci) => {console.log('cell:', cell, ', ri:', ri, ', ci:', ci);}).on('cell-edited', (text, ri, ci) => {console.log('text:', text, ', ri: ', ri, ', ci:', ci);});setTimeout(() => {// xs.loadData([{ rows }]);// xs.cellText(14, 3, 'cell-text').reRender();// console.log('cell(8, 8):', this.xs.cell(8, 8));// console.log('cellStyle(8, 8):', this.xs.cellStyle(8, 8));}, 5000);},loadExcelFile(fileSelected) {var workbook_object = this.getWorkbook(fileSelected)this.xs.loadData(this.stox(workbook_object));},/** 导出excel */exportExcel(){var new_wb = this.xtos(this.xs.getData());/* generate download */XLSX.writeFile(new_wb, "SheetJS.xlsx");},exportJson(){console.log(this.xs.getData())},/** 下载并读取excel */downloadExcel(){axios.get("http://localhost:8088/api/v1/test/test1",{responseType: 'arraybuffer'}).then(res=>{if(res.status == 200) {var data = res.dataconsole.log('data',data)var data = new Uint8Array(data)var workbook = XLSX.read(data, {type: 'array'});this.xs.loadData(this.stox(workbook));}})},/** 上传excel */uploadExcel(){var new_wb = this.xtos(this.xs.getData());var wbout = XLSX.write(new_wb, {type: 'binary'})console.log('new_wb',new_wb)var file = new Blob([this.s2ab(wbout)]);var forms = new FormData()var configs = {headers:{'Content-Type':'multipart/form-data'}};forms.append('file',file)forms.append('token','231231')axios.post("http://localhost:8088/api/v1/test/test",forms ,configs).then(res=>{console.log(res)})},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;},/** 将x-data-spreadsheet中的数据格式转为xlsx中的workbook */xtos(sdata) {console.log(sdata)var out = XLSX.utils.book_new();sdata.forEach(function(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(function(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;},stox(wb) {var out = [];wb.SheetNames.forEach(function (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(function (r, i) {var cells = {};r.forEach(function (c, j) {cells[j] = ({text: c});});o.rows[i] = {cells: cells};})// 设置合并单元格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;},/*** 获取文件* @param fileSelected*/getWorkbook(fileSelected) {console.log('fileSelected',fileSelected)let file = fileSelected.target.files[0]let reader = new FileReader()reader.onload = e => {let data = e.target.result,fixedData = this.fixData(data),workbook = XLSX.read(btoa(fixedData), {type: 'base64'})this.xs.loadData(this.stox(workbook));// console.log("workbook",workbook)console.log("fixedData",fixedData)// console.log("this.stox(workbook)",this.stox(workbook))}reader.readAsArrayBuffer(file)// return workbook},fixData(data) {var o = "", l = 0, w = 10240for (; 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},}}</script><style scoped></style>
3.2 后端php代码
public function test(){$file=fopen('xxxxxx', 'r');$filesize=filesize('xxxxx');header("Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");header("Accept-Ranges: bytes");header("Content-Length: $filesize");Header("Content-Disposition: attachment; filename=" . basename('xxxxx'));ob_clean();flush();$data = fread($file, $filesize);echo $data;}
