第一步：克隆或者下载下面的代码
git clone https://github.com/dream-num/Luckysheet.git
第二步：安装依赖
npm install
npm install gulp -g
第三步：运行
npm run dev
效果如下图所示
第四步：打包
打包执行成功后，在文件夹目录下会出现dis文件夹，如下图所示：
npm run build
第五步：本地引入
把dist文件夹中的代码全部复制粘贴到你项目的public文件夹中，index.html文件除外。
在你项目的index.html文件中引入如下代码，如果你复制的位置是其他地方，需要用绝对路径引入这些文件。
<link rel='stylesheet' href='./public/plugins/css/pluginsCss.css' />
<link rel='stylesheet' href='./public/plugins/plugins.css' />
<link rel='stylesheet' href='./public/css/luckysheet.css' />
<link rel='stylesheet' href='./public/assets/iconfont/iconfont.css' />
<script src="./public/plugins/js/plugin.js"></script>
<script src="./public/luckysheet.umd.js"></script>
接下来就是在项目中使用这个插件
首先要引入luckyexcel 的依赖，我们导入导出本地excel会用到
npm install luckyexcel --save
如果引入依赖报错可能是依赖冲突，可以使用下面的
npm install luckyexcel --save --force
然后创建一个vue页面文件
<template>
<div>
<div style="height: 10px;position: absolute">
<el-upload
ref="upload"
class="upload-demo"
action="https://run.mocky.io/v3/9d059bf9-4660-45f2-925d-ce80ad6c4d15"
:limit="1"
:on-change="handleFileChange"
:auto-upload="false"
accept=".xlsx"
>
<template #trigger>
<el-button type="primary">select file</el-button>
</template>
</el-upload>
</div>
<div v-if="isShow" id="luckysheet" class="luckysheet-wrap"></div>
</div>
</template>
<script setup>
import { ref, onMounted } from 'vue';
import LuckyExcel from 'luckyexcel';
import {ElMessage} from "element-plus";
const isShow = ref(false)
function handleFileChange(file, newFileList) {
const selectedFile = file.raw;
if (!(selectedFile instanceof File)) {
console.error('传入了非文件对象');
return;
}
if (selectedFile.type === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {
console.log('文件是xlsx类型');
} else {
console.error('不是xlsx文件');
ElMessage.error('请选择xlsx类型文件')
return;
}
let filename = selectedFile.name.replace(/\.[^/.]+$/, ""); // 去除文件扩展名
let reader = new FileReader();
reader.onload = async (e) => {
let fileData = e.target.result;
try {
isShow.value = true
LuckyExcel.transformExcelToLucky(fileData, async (exportJson, luckysheetfile) => {
console.log(exportJson.sheets)
creatExcel(filename, JSON.stringify(exportJson.sheets))
});
} catch (e) {
console.error(e);
}
};
reader.onerror = function() {
console.error("File could not be read! Code " + reader.error.code);
};
reader.readAsArrayBuffer(selectedFile);
}
function creatExcel(title, content){
const options = {
container: 'luckysheet', // 设定DOM容器的id
title: 'excel 表格', // 设定表格名称
lang: 'zh', // 设定表格语言
hook: {
updated: (e) => {
//监听更新,并在1s后自动保存
$('#luckysheet_info_detail_save').text("已修改")
let title = $('#luckysheet_info_detail_input').val();
let content = luckysheet.getAllSheets();
//去除临时数据,减小体积
for (let i in content)
content[i].data = undefined
console.log(title)
console.log(content)
}
},
}
options.data = JSON.parse(content)
options.title = title;
window.luckysheet.create(options)
}
onMounted(() => {
});
</script>
<style scoped>
.luckysheet-wrap {
margin: 0px;
padding: 0px;
position: absolute;
width: 100%;
height: 100%;
left: 0px;
top: 0px;
}
</style>
点击按钮选择一个xlsx文件就能导入成功了，效果如下（这里只能导入xlsx文件，导入xls文件会报错，暂时不知道什么原因，如果有xls文件的话可以把表格另存为xlsx类型的文件再导入）
下面放一个完整的demo展示效果
<template>
<div style="width: 100%; height: 100vh;overflow: auto;">
<div>
<div style="display: flex;">
<div style="margin-right: 10px;">
<el-upload ref="upload" :auto-upload="false" accept=".xlsx" :show-file-list='false' :on-change="handleChangeUpload"
action="#" class="upload-demo" multiple>
<el-button type="primary">导入</el-button>
</el-upload>
</div>
<el-button type="primary" @click="goBack()">返回</el-button>
</div>
</div>
<span>电源配线图</span>
<el-table :data="dataList" width="100%" border :max-height="750">
<el-table-column label="序号" align="center" key="id" prop="id" fixed width="100px" />
<el-table-column label="模板名称" align="left" key="name" prop="name"/>
<el-table-column label="导入时间" align="center" key="addTime" prop="addTime"/>
<el-table-column label="编辑时间" align="center" key="upTime" prop="upTime"/>
<el-table-column label="操作" align="center" class-name="small-padding fixed-width" fixed="right">
<template #default="scope">
<el-button link type="primary" icon="Edit" @click="updateData(scope.row)">编辑</el-button>
<el-button link type="primary" icon="Delete" @click="deleteData(scope.row)">删除</el-button>
</template>
</el-table-column>
</el-table>
<pagination
v-show="total > 0"
:total="total"
v-model:page="pageNum"
v-model:limit="pageSize"
@pagination="getDataList"
:page-sizes="[5, 10, 20, 50]"
/>
</div>
</template>
<script setup>
import {onBeforeUnmount, ref} from "vue";
import {ElMessage} from "element-plus";
import {closeWindow, openCenteredWindow, verifyCommand} from "../../openWindow";
import {useRouter} from "vue-router";
import {deletePower, importPower, selectPowerList} from "../../../../api/draw/power";
import LuckyExcel from 'luckyexcel';
const {proxy} = getCurrentInstance();
const router = useRouter();
const dataList = ref([])
const total = ref(0)
const pageNum = ref(1)
const pageSize = ref(10)
//查询数据
function getDataList(){
selectPowerList({
pageNum: pageNum.value,
pageSize: pageSize.value
}).then(result => {
dataList.value = result.rows
total.value = result.total
})
}
//删除数据
async function deleteData(row){
if ( await verifyCommand() ){
proxy.$confirm('确定删除吗?', '提示', {
confirmButtonText: '确定',
cancelButtonText: '取消',
type: 'warning'
}).then(() => {
let data = {
id: row.id,
}
deletePower(data).then(res=>{
if (res.code === 200){
getDataList()
ElMessage.success('删除成功！');
}else {
ElMessage.error('删除失败')
}
})
}).catch(() => {
proxy.$message({
type: 'info',
message: '取消删除'
});
});
}
}
//编辑按钮
function updateData(row){
router.push({
name: 'excel',
state: {
id: row.id,
title: row.name,
content: row.content,
}
});
}
function handleChangeUpload(file) {
const selectedFile = file.raw;
if (!(selectedFile instanceof File)) {
console.error('传入了非文件对象');
return;
}
if (selectedFile.type === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {
console.log('文件是xlsx类型');
} else {
console.error('不是xlsx文件');
ElMessage.error('请选择xlsx类型文件')
return;
}
let filename = selectedFile.name.replace(/\.[^/.]+$/, ""); // 去除文件扩展名
let reader = new FileReader();
reader.onload = async (e) => {
let fileData = e.target.result;
try {
LuckyExcel.transformExcelToLucky(fileData, async (exportJson, luckysheetfile) => {
console.log(exportJson.sheets)
//更改配置中的行数为当前最大行
//exportJson.sheets[0].config.rowlen = exportJson.sheets[0].celldata[exportJson.sheets[0].celldata.length-1].r+2
let data = {
name: filename,
content: JSON.stringify(exportJson.sheets)
}
importPower(data).then(res=>{
if (res.code === 200){
getDataList()
ElMessage.success('导入成功')
}else {
ElMessage.error(res.msg)
}
})
});
} catch (e) {
console.error(e);
}
};
reader.onerror = function() {
console.error("File could not be read! Code " + reader.error.code);
};
reader.readAsArrayBuffer(selectedFile);
}
onBeforeUnmount (() => {
closeWindow();
});
// 监听页面即将刷新的事件
window.addEventListener('beforeunload', function (event) {
closeWindow();
});
const goBack = () => {
router.go(-1); // 返回上一页
};
getDataList();
</script>
<style scoped>
</style>
<template>
<div>
<div id="luckysheet" class="luckysheet-wrap"></div>
</div>
</template>
<script setup>
import { ref, onMounted } from 'vue';
import {ElMessage} from "element-plus";
import {updatePower} from "../../../api/draw/power";
onMounted(() => {
setExcelData();
setExcelStyle();
});
//对表格数据进行渲染
function setExcelData(){
let title = history.state.title;
let content = history.state.content;
const options = {
container: 'luckysheet', // 设定DOM容器的id
title: 'excel 表格', // 设定表格名称
lang: 'zh', // 设定表格语言
hook: {
updated: (e) => {
//监听更新,并在1s后自动保存
$('#luckysheet_info_detail_save').text("已修改")
let title = $('#luckysheet_info_detail_input').val();
let content = luckysheet.getAllSheets();
//去除临时数据,减小体积
for (let i in content)
content[i].data = undefined
}
},
}
options.data = JSON.parse(content)
options.title = title;
window.luckysheet.create(options)
}
//对默认表格的样式进行修改
function setExcelStyle(){
//去除左上角logo
let leftLogo = document.querySelector('.luckysheet-share-logo');
leftLogo.className = '';
//去除左上角返回按钮
let leftButton = document.getElementById('luckysheet_info_detail_title');
leftButton.remove();
// 创建一个新的保存按钮
let newDiv = document.createElement('div');
newDiv.innerHTML = '保存';
newDiv.style.cursor = 'pointer'
newDiv.addEventListener('click', function() {
saveData();
});
let firstChild = document.querySelector('#luckysheet_info_detail').firstElementChild;
document.querySelector('#luckysheet_info_detail').insertBefore(newDiv, firstChild);
}
//保存数据
function saveData(){
let title = $('#luckysheet_info_detail_input').val();
let content = luckysheet.getAllSheets();
//去除临时数据,减小体积
for (let i in content)
content[i].data = undefined
//更改配置中的行数为当前最大行
content[0].config.rowlen = content[0].celldata[content[0].celldata.length-1].r+2
let id = history.state.id
let data = {
id: id,
name: title,
content: JSON.stringify(content)
}
updatePower(data).then(res=>{
if (res.code === 200){
history.state.title = title;
history.state.content = JSON.stringify(content);
ElMessage.success('保存成功！');
}else {
ElMessage.error('保存失败')
}
})
}
</script>
<style scoped>
.luckysheet-wrap {
margin: 0px;
padding: 0px;
position: absolute;
width: 100%;
height: 100%;
left: 0px;
top: 0px;
}
</style>
接下来是导出为本地excel
首先在项目中创建export.js文件
// import { createCellPos } from './translateNumToLetter'
import Excel from 'exceljs'
import FileSaver from 'file-saver'
const exportExcel = function(luckysheet, value) {
// 参数为luckysheet.getluckysheetfile()获取的对象
// 1.创建工作簿，可以为工作簿添加属性
const workbook = new Excel.Workbook()
// 2.创建表格，第二个参数可以配置创建什么样的工作表
if (Object.prototype.toString.call(luckysheet) === '[object Object]') {
luckysheet = [luckysheet]
}
luckysheet.forEach(function(table) {
if (table.data.length === 0) return true
// ws.getCell('B2').fill = fills.
const worksheet = workbook.addWorksheet(table.name)
const merge = (table.config && table.config.merge) || {}
const borderInfo = (table.config && table.config.borderInfo) || {}
//设置单元格宽度
// 3.设置单元格合并,设置单元格边框,设置单元格样式,设置值
setStyleAndValue(table.data, worksheet)
setMerge(merge, worksheet)
setBorder(borderInfo, worksheet)
return true
})
// return
// 4.写入 buffer
const buffer = workbook.xlsx.writeBuffer().then(data => {
// console.log('data', data)
const blob = new Blob([data], {
type: 'application/vnd.ms-excel;charset=utf-8'
})
console.log("导出成功！")
FileSaver.saveAs(blob, `${value}.xlsx`)
})
return buffer
}
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
// console.log('luckyBorderInfo', luckyBorderInfo)
luckyBorderInfo.forEach(function(elem) {
// 现在只兼容到borderType 为range的情况
// console.log('ele', elem)
if (elem.rangeType === 'range') {
let border = borderConvert(elem.borderType, elem.style, elem.color)
let rang = elem.range[0]
// console.log('range', rang)
let row = rang.row
let column = rang.column
for (let i = row[0] + 1; i < row[1] + 2; i++) {
for (let y = column[0] + 1; y < column[1] + 2; y++) {
worksheet.getCell(i, y).border = border
}
}
}
if (elem.rangeType === 'cell') {
// col_index: 2
// row_index: 1
// b: {
// color: '#d0d4e3'
// style: 1
// }
const { col_index, row_index } = elem.value
const borderData = Object.assign({}, elem.value)
delete borderData.col_index
delete borderData.row_index
let border = addborderToCell(borderData, row_index, col_index)
// console.log('bordre', border, borderData)
worksheet.getCell(row_index + 1, col_index + 1).border = border
}
// console.log(rang.column_focus + 1, rang.row_focus + 1)
// worksheet.getCell(rang.row_focus + 1, rang.column_focus + 1).border = border
})
}
var setStyleAndValue = function(cellArr, worksheet) {
worksheet.columns = []
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.v && cell.ct && cell.ct.s) {
// xls转为xlsx之后，内部存在不同的格式，都会进到富文本里，即值不存在与cell.v，而是存在于cell.ct.s之后
// value = cell.ct.s[0].v
cell.ct.s.forEach(arr => {
value += arr.v
})
} else {
value = cell.v
}
// style 填入到_value中可以实现填充色
let letter = createCellPos(columnid)
let target = worksheet.getCell(letter + (rowid + 1))
// console.log('1233', letter + (rowid + 1))
for (const key in fill) {
target.fill = fill
break
}
target.font = font
target.alignment = alignment
target.value = value
setColumnsWidth(worksheet, rowid)
return true
})
})
}
var fillConvert = function(bg) {
if (!bg) {
return {}
}
// const bgc = bg.replace('#', '')
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',
num2bl: function(num) {
return num === 0 ? false : true
}
}
// 出现Bug，导入的时候ff为luckyToExcel的val
let font = {
name: typeof ff === 'number' ? luckyToExcel[ff] : 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(borderType, style = 1, color = '#000') {
// 对应luckysheet的config中borderinfo的的参数
if (!borderType) {
return {}
}
const luckyToExcel = {
type: {
'border-all': 'all',
'border-top': 'top',
'border-right': 'right',
'border-bottom': 'bottom',
'border-left': 'left'
},
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'
}
}
let template = {
style: luckyToExcel.style[style],
color: { argb: color.replace('#', '') }
}
let border = {}
if (luckyToExcel.type[borderType] === 'all') {
border['top'] = template
border['right'] = template
border['bottom'] = template
border['left'] = template
} else {
border[luckyToExcel.type[borderType]] = template
}
// console.log('border', border)
return border
}
function addborderToCell(borders, row_index, col_index) {
let border = {}
const luckyExcel = {
type: {
l: 'left',
r: 'right',
b: 'bottom',
t: 'top'
},
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'
}
}
// console.log('borders', borders)
for (const bor in borders) {
// console.log(bor)
if (borders[bor].color.indexOf('rgb') === -1) {
border[luckyExcel.type[bor]] = {
style: luckyExcel.style[borders[bor].style],
color: { argb: borders[bor].color.replace('#', '') }
}
} else {
border[luckyExcel.type[bor]] = {
style: luckyExcel.style[borders[bor].style],
color: { argb: borders[bor].color }
}
}
}
return border
}
function createCellPos(n) {
let ordA = 'A'.charCodeAt(0)
let ordZ = 'Z'.charCodeAt(0)
let len = ordZ - ordA + 1
let s = ''
while (n >= 0) {
s = String.fromCharCode((n % len) + ordA) + s
n = Math.floor(n / len) - 1
}
return s
}
function setColumnsWidth(worksheet, index){
const border = {
top: {
style: 'thin',
},
left: {
style: 'thin',
},
bottom: {
style: 'thin',
},
right: {
style: 'thin',
},
}
worksheet.columns.map((column) => {
// 表头的样式
worksheet.getCell(`${column.letter}1`).border = border
worksheet.getCell(`${column.letter}1`).font = {
bold: true,
}
worksheet.getCell(`${column.letter}1`).fill = {
type: 'pattern',
pattern: 'solid',
// fgColor: { argb: 'FF8FBC8F' }, //表头背景色
}
// 列宽自适应
let width = []
column.values.map((value) => {
if (!value) {
width.push(10)
} else if (/.*[\u4e00-\u9fa5]+.*$/.test(value)) {
width.push(parseFloat(value.toString().length * 2.15))
} else {
width.push(parseFloat(value.toString().length * 1.15))
}
})
column.width = Math.max(...width)
// 行数据的样式
worksheet.getCell(`${column.letter}${index + 2}`).border = border
})
}
export {
exportExcel
}
然后在页面中写一个导出按钮，调用方法
import { exportExcel } from './export'
function exportMyExcel(){
let title = $('#luckysheet_info_detail_input').val();
exportExcel(window.luckysheet.getAllSheets(), title)
}
最后点击导出按钮调用方法，浏览器就会弹出下载excel的窗口（如果是谷歌浏览器可能不会弹出窗口而是直接下载了，可以点击浏览器右上角的下载按钮查看）
ps：注意事项
在导出文件时，如果需要将导出的数据通过接口传输给后端，需要进行文件格式的转换，以file为例。
//获取buffer的
workbook.xlsx.writeBuffer()
.then((arrayBuffer) => {
const blob = new Blob(arrayBuffer, {
//导出为xlsx文件格式
type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
});
//转为file格式
let file = new File([blob], '导出.xlsx', {type: blob.type});
/**
这里调用接口上传代码
*/
}).catch(err=>{
dialogVisible.value = false
})
4952
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
