电子表格编译导入导出
https://www.cnblogs.com/wqnmlgbsz/p/13065319.html npm install x-data-spreadsheet
- 代码
<template>
<div id="x-spreadsheet-demo"></div>
</template>
<script>
import Spreadsheet from 'x-data-spreadsheet';
import zhCN from 'x-data-spreadsheet/dist/locale/zh-cn';
export default {
data() {
return {
options:{
mode: 'edit',
showToolbar: true,
showGrid: true,
showContextmenu: true,
view: {
/*宽高，因为我是自定义的所以-----*/
/*height: () => document.documentElement.clientHeight - 300,
width: () => document.documentElement.clientWidth - 300,*/
height: () => document.documentElement.clientHeight - 64,
width: () => document.documentElement.clientWidth - 150,
},
formats: [],
fonts: [],
formula: [],
row: {
len: 100,
height: 25,
},
col: {
len: 26,
width: 100,
indexWidth: 60,
minWidth: 60,
},
/*freeze: 'C3',*/
style: {
bgcolor: '#ffffff',
align: 'left',
valign: 'middle',
textwrap: false,
textDecoration: 'normal',
strikethrough: false,
color: '#0a0a0a',
font: {
name: 'Helvetica',
size: 10,
bold: false,
italic: false,
},
},
},
/*数组*/
optionss:[{
name:'aaaa',
/*freeze: 'C3',*/
styles: [
{
/*小框背景色*/
bgcolor: '#f4f5f8',
textwrap: true,
color: '#900b09',
border: {
top: ['thin', '#0366d6'],
bottom: ['thin', '#0366d6'],
right: ['thin', '#0366d6'],
left: ['thin', '#0366d6'],
},
},{
/*小框背景色*/
bgcolor: '#000000',
textwrap: true,
color: '#000000',
border: {
top: ['thin', '#000000'],
bottom: ['thin', '#000000'],
right: ['thin', '#000000'],
left: ['thin', '#000000'],
},
},
],
/*合并单元格*/
merges: [
'C3:E4',
],
rows: {
/*第1行*/
1: {
cells: {
/*第0列 2列*/
0: { text: 'testingtesttestetst' },
2: { text: 'testing' },
},
},
2: {
cells: {
0: { text: 'render', style: 0 },
1: { text: 'Hello' },
2: { text: 'haha', merge: [1, 2] },
}
},
8: {
cells: {
8: { text: 'border test', style: 0 },
}
}
},
},{
name:'aaaa',
/*freeze: 'C3',*/
styles: [
{
/*小框背景色*/
bgcolor: '#f4f5f8',
textwrap: true,
color: '#900b09',
border: {
top: ['thin', '#0366d6'],
bottom: ['thin', '#0366d6'],
right: ['thin', '#0366d6'],
left: ['thin', '#0366d6'],
},
},{
/*小框背景色*/
bgcolor: '#000000',
textwrap: true,
color: '#000000',
border: {
top: ['thin', '#000000'],
bottom: ['thin', '#000000'],
right: ['thin', '#000000'],
left: ['thin', '#000000'],
},
},
],
/*合并单元格*/
merges: [
'C3:E4',
],
rows: {
/*第1行*/
1: {
cells: {
/*第0列 2列*/
0: { text: 'testingtesttestetst' },
2: { text: 'testing' },
},
},
2: {
cells: {
0: { text: 'render', style: 0 },
1: { text: 'Hello' },
2: { text: 'haha', merge: [1, 2] },
}
},
8: {
cells: {
8: { text: 'border test', style: 0 },
}
}
},
}]
}
},
methods: {
},
mounted:function(){
/*中文*/
Spreadsheet.locale('zh-cn', zhCN);
new Spreadsheet('#x-spreadsheet-demo', this.options).loadData(this.optionss).change((data) => {
console.log(data)
});
}
}
</script>
<style>
</style>
<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.data
console.log('data',data)
data = new Uint8Array(data)
// console.log(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
// 修改 merges
o.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 = 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
},
}
}
</script>
<style scoped>
</style>
本文来自博客园，作者：zjxgdq，转载请注明原文链接：https://www.cnblogs.com/zjxzhj/p/15040512.html
