index.html
<!-- excel -->
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/css/pluginsCss.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/plugins.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/css/luckysheet.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet/dist/assets/iconfont/iconfont.css' />
<script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/plugins/js/plugin.js"></script>
<script src="https://cdn.jsdelivr.net/npm/luckysheet/dist/luckysheet.umd.js"></script>
还要安装下 yarn add luckyexcel exceljs
index.vue:
<template>
<div>
<el-button @click="save">保存</el-button>
<div id="luckysheet" style="font-family:EUDC;height:600px;width:100%;"></div>
</div>
</template>
<script>
import LuckyExcel from 'luckyexcel';
import { exportExcel } from './exportExcel';
export default {
name: '',
data () {
return {
}
},
mounted () {
this.importIo()
},
methods: {
save () {
exportExcel(luckysheet.getluckysheetfile()).then(async (res) => {
let formData = new FormData()
formData.append('file', res)
const { data } = await this.requestByPost('standBookOcrPython/python/saveFile', { data: formData })
console.log('上传结果:', data);
})
},
importIo (data) {
let _this = this
var excelFilePath = `http://202.100.168.38:5218/standBookOcr/chartBook/getFileStream?url=http://202.100.168.38:5218/File/ocr/standBook/e636e7efc61d4614b46a560b4035fb73/crop/1-e636e7efc61d4614b46a560b4035fb73/1-0-e636e7efc61d4614b46a560b4035fb73.xlsx`;
var xhr = new XMLHttpRequest();
xhr.open('GET', excelFilePath, true);
xhr.responseType = 'blob';
xhr.onload = function (e) {
if (this.status == 200) {
var blob = this.response;
LuckyExcel.transformExcelToLucky(blob, function (exportJson, luckysheetfile) {
if (exportJson.sheets == null || exportJson.sheets.length == 0) {
alert("Failed to read the content of the excel file, currently does not support xls files!");
return;
}
if (exportJson.sheets) {
exportJson.sheets[0].celldata.map(item => {
item.v.ff = 'EUDC'
})
}
console.log(exportJson);
window.luckysheet.destroy();
window.luckysheet.create({
container: 'luckysheet', //luckysheet is the container id
showinfobar: false,
showinfobar: false,
title: 'hahah Demo', // 设定表格名称
name: 'hahah Demo', // 设定表格名称
lang: 'zh', // 设定表格语言
allowEdit: true,//作用:是否允许前台编辑
// allowUpdate: true,
allowCopy: true, //是否允许拷贝
showtoolbar: true, //是否第二列显示工具栏
showinfobar: true, //是否显示顶部名称栏
showsheetbar: true, //是否显示底部表格名称区域
showstatisticBar: true, //是否显示底部计数栏
pointEdit: false, //是否是编辑器插入表格模式
data: exportJson.sheets,
title: exportJson.info.name,
userInfo: exportJson.info.creator,
// functionButton: '<button id="" class="btn btn-primary" style=" padding:3px 6px; font-size: 16px;width: 100px;height: 27px; margin-right: 85px;" οnclick="downExcelData()">導出</button>',
});
});
}
};
xhr.send();
},
}
}
</script>
<style lang='scss'>
#luckysheet_info_detail{
display:none !important;
}
</style>
exportExcel.js 根据实际数据需要做些修改
const Excel = require('exceljs')
export const exportExcel = async function (luckysheet) { // 参数为luckysheet.getluckysheetfile()获取的对象
// 1.创建工作簿,可以为工作簿添加属性
const workbook = new Excel.Workbook()
// 2.创建表格,第二个参数可以配置创建什么样的工作表
luckysheet.every(function (table) {
if (table.data.length === 0) return true
const worksheet = workbook.addWorksheet(table.name)
// 3.设置单元格合并,设置单元格边框,设置单元格样式,设置值
setStyleAndValue(table.data, worksheet)
setMerge(table.config.merge, worksheet)
setBorder(table.config.borderInfo, worksheet)
return true
})
// 4.写入 buffer
const buffer = await workbook.xlsx.writeBuffer()
let blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8' });
let file = new File([blob], `${luckysheet[0].name}.xlsx`); // 这里如果你做导出可以直接返回blob数据在导出
return file
}
var setMerge = function (luckyMerge = {}, worksheet) {
const mergearr = Object.values(luckyMerge)
mergearr.forEach(function (elem) { // elem格式:{r: 0, c: 0, rs: 1, cs: 2}
// 按开始行,开始列,结束行,结束列合并(相当于 K10:M12)
worksheet.mergeCells(elem.r + 1, elem.c + 1, elem.r + elem.rs, elem.c + elem.cs)
})
}
var setBorder = function (luckyBorderInfo, worksheet) {
if (!Array.isArray(luckyBorderInfo)) return
luckyBorderInfo.forEach(function (elem) {
let rang = elem.value
let border = borderConvert(rang, elem.style, elem.color)
// console.log(rang.column_focus + 1, rang.row_focus + 1)
worksheet.getCell(rang.row_index + 1, rang.col_index + 1).border = border
})
}
var setStyleAndValue = function (cellArr, worksheet) {
if (!Array.isArray(cellArr)) return
cellArr.forEach(function (row, rowid) {
row.every(function (cell, columnid) {
if (!cell) return true
let fill = fillConvert(cell.bg)
let font = fontConvert(cell.ff, cell.fc, cell.bl, cell.it, cell.fs, cell.cl, cell.ul)
let alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr)
let value
if (cell.f) {
value = { formula: cell.f, result: cell.v }
}else if (cell.ct && cell.ct.s) {
value = cell.ct.s[0].v
} else {
value = cell.v
}
let target = worksheet.getCell(rowid + 1, columnid + 1)
target.fill = fill
target.font = font
target.alignment = alignment
target.value = value
return true
})
})
}
var fillConvert = function (bg) {
if (!bg) {
return {}
}
let fill = {
type: 'pattern',
pattern: 'solid',
fgColor: {argb: bg.replace('#', '')}
}
return fill
}
var fontConvert = function (ff = 0, fc = '#000000', bl = 0, it = 0, fs = 10, cl = 0, ul = 0) { // luckysheet:ff(样式), fc(颜色), bl(粗体), it(斜体), fs(大小), cl(删除线), ul(下划线)
const luckyToExcel = {
0: '微软雅黑',
1: '宋体(Song)',
2: '黑体(ST Heiti)',
3: '楷体(ST Kaiti)',
4: '仿宋(ST FangSong)',
5: '新宋体(ST Song)',
6: '华文新魏',
7: '华文行楷',
8: '华文隶书',
9: 'Arial',
10: 'Times New Roman ',
11: 'Tahoma ',
12: 'Verdana',
'EUDC':'EUDC',
num2bl: function (num) {
return num === 0 ? false : true
}
}
let font = {
name: luckyToExcel[ff],
family: 1,
size: fs,
color: {argb: fc.replace('#', '')},
bold: luckyToExcel.num2bl(bl),
italic: luckyToExcel.num2bl(it),
underline: luckyToExcel.num2bl(ul),
strike: luckyToExcel.num2bl(cl)
}
return font
}
var alignmentConvert = function (vt = 'default', ht = 'default', tb = 'default', tr = 'default') { // luckysheet:vt(垂直), ht(水平), tb(换行), tr(旋转)
const luckyToExcel = {
vertical: {
0: 'middle',
1: 'top',
2: 'bottom',
default: 'top'
},
horizontal: {
0: 'center',
1: 'left',
2: 'right',
default: 'left'
},
wrapText: {
0: false,
1: false,
2: true,
default: false
},
textRotation: {
0: 0,
1: 45,
2: -45,
3: 'vertical',
4: 90,
5: -90,
default: 0
}
}
let alignment = {
vertical: luckyToExcel.vertical[vt],
horizontal: luckyToExcel.horizontal[ht],
wrapText: luckyToExcel.wrapText[tb],
textRotation: luckyToExcel.textRotation[tr]
}
return alignment
}
var borderConvert = function (data) { // 对应luckysheet的config中borderinfo的的参数
if (!data) {
return {}
}
let border = {}
let borderList = []
if(data.b) borderList.push({label: 'bottom',...data.b})
if(data.l) borderList.push({label: 'left',...data.l})
if(data.t) borderList.push({label: 'top',...data.t})
if(data.r) borderList.push({label: 'right',...data.r})
const luckyToExcel = {
style: {
0: 'none',
1: 'thin',
2: 'hair',
3: 'dotted',
4: 'dashDot', // 'Dashed',
5: 'dashDot',
6: 'dashDotDot',
7: 'double',
8: 'medium',
9: 'mediumDashed',
10: 'mediumDashDot',
11: 'mediumDashDotDot',
12: 'slantDashDot',
13: 'thick'
}
}
borderList.forEach(item=>{
border[item.label] = {style: luckyToExcel.style[item.style], color: {argb: item.color.replace('#', '')}}
})
return border
}