愿意做cps的网站,建设科技处网站,深圳网站策划公司,中国网站建设平台第一步#xff1a;克隆或者下载下面的代码
git clone https://github.com/dream-num/Luckysheet.git第二步#xff1a;安装依赖
npm install
npm install gulp -g 第三步#xff1a;运行
npm run dev效果如下图所示 第四步#xff1a;打包 打包执行成功后#xff0c;…第一步克隆或者下载下面的代码
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 relstylesheet href./public/plugins/css/pluginsCss.css /
link relstylesheet href./public/plugins/plugins.css /
link relstylesheet href./public/css/luckysheet.css /
link relstylesheet 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页面文件
templatedivdiv styleheight: 10px;position: absoluteel-uploadrefuploadclassupload-demoactionhttps://run.mocky.io/v3/9d059bf9-4660-45f2-925d-ce80ad6c4d15:limit1:on-changehandleFileChange:auto-uploadfalseaccept.xlsxtemplate #triggerel-button typeprimaryselect file/el-button/template/el-upload/divdiv v-ifisShow idluckysheet classluckysheet-wrap/div/div
/templatescript 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 trueLuckyExcel.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容器的idtitle: 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 undefinedconsole.log(title)console.log(content)}},}options.data JSON.parse(content)options.title title;window.luckysheet.create(options)
}onMounted(() {});
/scriptstyle scoped
.luckysheet-wrap {margin: 0px;padding: 0px;position: absolute;width: 100%;height: 100%;left: 0px;top: 0px;
}
/style 点击按钮选择一个xlsx文件就能导入成功了效果如下这里只能导入xlsx文件导入xls文件会报错暂时不知道什么原因如果有xls文件的话可以把表格另存为xlsx类型的文件再导入 下面放一个完整的demo展示效果
templatediv stylewidth: 100%; height: 100vh;overflow: auto;divdiv styledisplay: flex;div stylemargin-right: 10px;el-upload refupload :auto-uploadfalse accept.xlsx :show-file-listfalse :on-changehandleChangeUploadaction# classupload-demo multipleel-button typeprimary导入/el-button/el-upload/divel-button typeprimary clickgoBack()返回/el-button/div/divspan电源配线图/spanel-table :datadataList width100% border :max-height750el-table-column label序号 aligncenter keyid propid fixed width100px /el-table-column label模板名称 alignleft keyname propname/el-table-column label导入时间 aligncenter keyaddTime propaddTime/el-table-column label编辑时间 aligncenter keyupTime propupTime/el-table-column label操作 aligncenter class-namesmall-padding fixed-width fixedrighttemplate #defaultscopeel-button link typeprimary iconEdit clickupdateData(scope.row)编辑/el-buttonel-button link typeprimary iconDelete clickdeleteData(scope.row)删除/el-button/template/el-table-column/el-tablepaginationv-showtotal 0:totaltotalv-model:pagepageNumv-model:limitpageSizepaginationgetDataList:page-sizes[5, 10, 20, 50]//div
/templatescript setupimport {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.rowstotal.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].r2let 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();
/scriptstyle scoped/styletemplatedivdiv idluckysheet classluckysheet-wrap/div/div
/templatescript 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容器的idtitle: 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(){//去除左上角logolet 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 pointernewDiv.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].r2let id history.state.idlet 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(保存失败)}})
}
/scriptstyle 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 exceljsimport FileSaver from file-saverconst 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.写入 bufferconst buffer workbook.xlsx.writeBuffer().then(data {// console.log(data, data)const blob new Blob([data], {type: application/vnd.ms-excel;charsetutf-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:M12worksheet.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.rowlet column rang.columnfor (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.valueconst borderData Object.assign({}, elem.value)delete borderData.col_indexdelete borderData.row_indexlet 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)) returncellArr.forEach(function(row, rowid) {row.every(function(cell, columnid) {if (!cell) return truelet 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].vcell.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 fillbreak}target.font fonttarget.alignment alignmenttarget.value valuesetColumnsWidth(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
) {// luckysheetff(样式), 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的vallet 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] templateborder[right] templateborder[bottom] templateborder[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 1let s while (n 0) {s String.fromCharCode((n % len) ordA) sn 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 borderworksheet.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 ./exportfunction 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})
