項(xiàng)目需求：
本地導(dǎo)入excel，頁面渲染excel，一鍵計(jì)算：根據(jù)計(jì)算邏輯求出得分回寫到對(duì)應(yīng)單元格，最后導(dǎo)出excel；
前端技術(shù)：Vue2，luckysheet，luckyExcel，exceljs，mathjs，antdv
Luckysheet?，一款純前端類似excel的在線表格，功能強(qiáng)大、配置簡(jiǎn)單、完全開源。
上傳下載demo：??luckysheet-demo: luckysheet-demo
用到的插件和api：
1，本地導(dǎo)入excel并渲染
npm下載luckysheet后通過 import 方式引入報(bào)錯(cuò)，官網(wǎng)給出了兩種引入方式：CDN , 本地引入；CDN沒什么說的直接引入即可，此次介紹一下本地引入；
快速上手 | Luckysheet文檔
//文件上傳按鈕
<div>
<a-upload
:file-list="fileList"
name="file"
:multiple="false" //禁止多選
:showUploadList="{showRemoveIcon: false}" //隱藏刪除文件icon
:before-upload="handleUpload"
>
<a-button type="primary" class="upload">
點(diǎn)擊上傳
</a-button>
</a-upload>
<div class="uploadTip">
只能上傳xlsx文件！
</div>
</div>
//渲染excel容器
<div id="luckysheet" class="luckySheet" v-show="showLuckyExcel"></div>
//-----methods----------------------------------------------
import LuckyExcel from 'luckyexcel'; //引入LuckyExcel
//luckysheet 引入報(bào)錯(cuò)，官網(wǎng)給出了兩種引入方式：CDN , 本地引入
//（1）上傳文件
handleUpload(file){
if(file.name.substring(file.name.length-5) === '.xlsx'){
this.spinning = true
this.spinningTip = '文件上傳中...'
this.file = file;
this.fileList = [file]; //只允許上傳一個(gè)文件
LuckyExcel.transformExcelToLucky(file, (exportJson, luckysheetfile) => {
luckysheet.destroy();
this.initExcel(exportJson);
},error =>{
this.$message.error(error)
});
return false;
}else{
this.$message.error('文件格式錯(cuò)誤，請(qǐng)上傳.xlsx文件！')
}
},
//(2)渲染excel
initExcel(exportJson) {
//工作表保護(hù)
exportJson.sheets.forEach(t=>{
t.config.authority = {
sheet: 1,
hintText: "您試圖更改的單元格或圖表位于受保護(hù)的工作表中!",
allowRangeList: [
{ sqref: '$A$2:$D$6' },//設(shè)置A2~D6為可編輯區(qū)域，其它區(qū)域不可編輯
],
}
});
//初始化excel
luckysheet.create({
container: 'luckysheet', //dom id
showtoolbar: false, //隱藏工具欄
sheetFormulaBar: false, //隱藏公式欄
enableAddRow: false, //隱藏新增row
showtoolbarConfig: {
print: false // 隱藏插件內(nèi)部打印按鈕
},
sheetRightClickConfig: { // 工作表右鍵：禁用 刪除，復(fù)制...
delete: false,
copy: false,
rename: false,
color: false,
hide: false,
move: false,
},
cellRightClickConfig: { //單元格右鍵配置
paste: false, // 粘貼
insertRow: false, // 插入行
insertColumn: false, // 插入列
deleteRow: false, // 刪除選中行
deleteColumn: false, // 刪除選中列
deleteCell: false, // 刪除單元格
hideRow: false, // 隱藏選中行和顯示選中行
hideColumn: false, // 隱藏選中列和顯示選中列
clear: false, // 清除內(nèi)容
matrix: false, // 矩陣操作選區(qū)
sort: false, // 排序選區(qū)
filter: false, // 篩選選區(qū)
chart: false, // 圖表生成
image: false, // 插入圖片
link: false, // 插入鏈接
data: false, // 數(shù)據(jù)驗(yàn)證
cellFormat: false // 設(shè)置單元格格式
},
showinfobar: false, // 顯示頭部返回標(biāo)題欄
data: exportJson.sheets, //excel數(shù)據(jù)
});
},2，luckysheet 本地引入步驟：
（1）gitee上下載項(xiàng)目:?Luckysheet: ??Luckysheet ，一款純前端類似excel的在線表格，功能強(qiáng)大、配置簡(jiǎn)單、完全開源。
? (2) 下載后執(zhí)行 npm run build 打包，生成的dist文件中找到以下文件（直接把除了index.html，demoData的copy過去），加入vue public文件夾下
（3）index.html引入后，直接在vue組件中就可以用luckysheet對(duì)象上的屬性和方法了；
<!DOCTYPE html>
<html lang="">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width,initial-scale=1.0">
<title>測(cè)試</title>
<link rel="icon" href="<%= BASE_URL %>favicon.ico">
<link rel='stylesheet' href='./plugins/css/pluginsCss.css'/>
<link rel='stylesheet' href='./plugins/plugins.css'/>
<link rel='stylesheet' href='./css/luckysheet.css'/>
<link rel='stylesheet' href='./assets/iconfont/iconfont.css'/>
<script src="./plugins/js/plugin.js"></script>
<script src="./luckysheet.umd.js"></script>
</head>
<body>
<div id="app"></div>
</body>
</html>
3，luckysheet 常用API
luckysheet.getAllSheets() //1,獲取所有sheet
//2,排除sheet中的空白行，注意第一個(gè)單元格為null的情況，根據(jù)實(shí)際情況調(diào)整
let rowCount = sheet.data.filter(t => t[0] !== null).length;
//3,獲取G列數(shù)據(jù)，獲取cellValue用 v ,修改cellValue V 和 m 都要修改（官網(wǎng)有介紹）
let sheetCol_G = sheet.data.slice(6, rowCount).map(row => row[6].v);
//4,獲取L列數(shù)據(jù)
let sheetSpans_L = this.getSpans(sheet,6,11,rowCount - 6);
//獲取合并單元格
getSpans(sheet,r,c,rs){
let spans = [], merge = sheet.config.merge;
for ( let i in merge) {
let _r = i.split('_')[0], _c = i.split('_')[1];
if (_c == c && _r >= r && _r < r+rs) {
spans.push(merge[i]);
}
}
return spans;
},
//5,合并單元格按照row順序排序
sheetSpans_L.sort((a,b)=>{
return a.r - b.r;
});
//6,獲取L列非合并單元格：
//（1） 先獲取所有合并單元格的rowIndex
getSpansIndex(spans){
let spansIndexs = [];
for(let i = 0; i < spans.length; i++){
let {r,rs} = spans[i];
for(let j = 0; j < rs; j++){
let index = r + j;
spansIndexs.push(index)
}
}
return spansIndexs
},
//(2)再遍歷看L列這行的rowIndex是否包含在里面，不在合并行里就是非合并單元格
getSingleRow(r,rowCount,col,spans){
let spansIndexs = this.getSpansIndex(spans);
let singleRows = [];
for(let i = r; i < r+rowCount; i++){
singleRows.push({
r:i,
c: col,
rs:1,
cs:1
})
}
}
//7,修改單元格的值
sheet.data[4][14].m = '新數(shù)據(jù)'; //4:rowIndex, 14:colIndex
sheet.data[4][14].v = '新數(shù)據(jù)';
//8,修改單元格背景色，字體顏色（注意：導(dǎo)出時(shí)exceljs只支持argb格式，與luckysheet不兼容，需要轉(zhuǎn)化）
sheet.data[row][col].bg = "yellow"
sheet.data[row][col].fc = "red"
//9,最后執(zhí)行refresh頁面數(shù)據(jù)才會(huì)更新
luckysheet.refresh();
4，導(dǎo)出excel
（1）直接復(fù)制該js文件文章來源：http://www.zghlxwxcb.cn/news/detail-741745.html
import?Excel?from?'exceljs';
import?FileSaver?from?'file-saver';
let?workbook = null;
const?exportSheetExcel?=?function(table,?value, index)?{
//創(chuàng)建工作簿，可以為工作簿添加屬性
if(index === 0){
workbook?=?new?Excel.Workbook();
}
if (table.data.length === 0) { return true; }
const worksheet = workbook.addWorksheet(table.name);
const merge = (table.config && table.config.merge) || {};
const borderInfo = (table.config && table.config.borderInfo) || {};
// 設(shè)置單元格合并,設(shè)置單元格邊框,設(shè)置單元格樣式,設(shè)置值
setStyleAndValue(table.data, worksheet);
setMerge(merge, worksheet);
setBorder(borderInfo, worksheet);
// 寫入 buffer
if(index == 4){
const buffer = workbook.xlsx.writeBuffer().then(data => {
const blob = new Blob([data], {
type: 'application/vnd.ms-excel;charset=utf-8'
});
console.log('導(dǎo)出成功！');
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}
????//?按開始行，開始列，結(jié)束行，結(jié)束列合并（相當(dāng)于?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)?{
????//?現(xiàn)在只兼容到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)?{
????if?(!Array.isArray(cellArr))?{?return;?}
????cellArr.forEach(function(row,?rowid)?{
// const dbrow = worksheet.getRow(rowid+1);
// //設(shè)置單元格行高,默認(rèn)乘以1.2倍
// dbrow.height=luckysheet.getRowHeight([rowid])[rowid]*1.2;
????????row.every(function(cell,?columnid)?{
if(rowid==0){
const dobCol = worksheet.getColumn(columnid+1);
//設(shè)置單元格列寬除以8
dobCol.width=luckysheet.getColumnWidth([columnid])[columnid]/8;
}
????????????if?(!cell)?{?return?true;?}
//設(shè)置背景色
let bg = cell.bg || "#FFFFFF"; //默認(rèn)white
bg = bg === 'yellow' ? "FFFF00" : bg.replace('#',?'');
let?fill?=?{
????????type:?'pattern',
????????pattern:?'solid',
????????fgColor:?{?argb:?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轉(zhuǎn)為xlsx之后，內(nèi)部存在不同的格式，都會(huì)進(jìn)到富文本里，即值不存在與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中可以實(shí)現(xiàn)填充色
????????????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;
????????????return?true;
????????});
????});
};
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;
????????}
????};
????//?出現(xiàn)Bug，導(dǎo)入的時(shí)候ff為luckyToExcel的val
//設(shè)置字體顏色
fc = fc === 'red' ? 'FFFF0000' : fc.replace('#',?'');
????let?font?=?{
????????name:?typeof?ff?===?'number'???luckyToExcel[ff]?:?ff,
????????family:?1,
????????size:?fs,
????????color:?{?argb:?fc?},
????????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(旋轉(zhuǎn))
????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')?{
????//?對(duì)應(yīng)luckysheet的config中borderinfo的的參數(shù)
????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;
}
export?{
exportSheetExcel
};
（2）使用（用了批量導(dǎo)出優(yōu)化，顯示下載進(jìn)度），“requestAnimationFrame” 優(yōu)化由于js計(jì)算時(shí)造成UI線程阻塞（即頁面loading 失效）文章來源地址http://www.zghlxwxcb.cn/news/detail-741745.html
handleExport(){
this.spinning = true;
const total = 6; //sheet總數(shù)+1
const batchSize = 1; // 每批計(jì)算數(shù)量
let current = 0; // 當(dāng)前計(jì)算進(jìn)度
let tableArr = luckysheet.getAllSheets();
const fileName = '測(cè)試表_輸出';
const doBatchExport = () => {
for (let i = 0; i < batchSize; i++) {
if(current > 0 ){
let index = current - 1;
let sheet = tableArr[index];
exportSheetExcel(sheet, fileName,index);
}
current++;
// 更新計(jì)算進(jìn)度
this.updateExportProgress(current, total);
// 計(jì)算完成后
if (current >= total) {
return;
}
}
// 繼續(xù)下一批計(jì)算任務(wù)
requestAnimationFrame(doBatchExport);
};
// 開始第一批計(jì)算任務(wù)
requestAnimationFrame(doBatchExport);
},
updateExportProgress(current, total, ){
if(current < total){
this.spinningTip = `下載進(jìn)度（ ${(current/(total - 1))*100} % ）...`
}else{
this.spinning = false;
}
},到了這里，關(guān)于免費(fèi)開源luckysheet+luckyExcel，本地導(dǎo)入文件，渲染excel，公式計(jì)算，導(dǎo)出excel的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
