devbox
知新_RL
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

全网最全luckysheet协同编辑导出功能，包含单元格样式、超链接、数据验证、条件格式、图片导出、批注、数据透视图等等复杂功能的导出

作者：知新_RL | 2024-04-10 02:05:41

赞

踩

luckysheet

前言：

        最近公司准备研究使用luckySheet，为了开源节流，在开掉了前端大佬的情况下，这苦差事儿落在了我这个前端菜鸟身上。无奈，只能打开luckysheet官网，仔细研读文档，并照着敲了个在线协同的demo。本以为这事儿算是结束，谁知，公司来了新需求，要做导入导出功能，又是一番查找资料后，决定先做导出，哈哈哈，别问为什么，问就是因为导出相关的文档丰富，可操作性强。

废话结束，进入正题：

        1. 在官网找到了导出代码，但只有基础的功能，标题所描述的复杂功能的导出并没有实现，于是我就拿下来进行二次开发，链接：使用exceljs导出luckysheet表格_excel 转 luckysheet-CSDN博客

        2. 用到导出库excejs和file-saver 

  1. //安装exceljs
  2. npm i exceljs --save
  3. //安装文件保存的库
  4. npm i file-saver --save

        3. 二次开发

        export.js
  1. const Excel = require('exceljs')
  2. import FileSaver from 'file-saver'
  3.  
  4. import { setConditions } from './export/setConditions'
  5. import { setDataValidation } from './export/setDataValidation'
  6. import { createCellRange } from './export/utils'
  7.  
  8. export var exportExcel = function(luckysheet, value) {
  9.   console.log(luckysheet,'sheet数组')
  10.   console.log(luckysheet[0].dataVerification,'sheet1中具体操作')
  11.   // 参数为luckysheet.getluckysheetfile()获取的对象
  12.   // 1.创建工作簿，可以为工作簿添加属性
  13.   const workbook = new Excel.Workbook()
  14.   // 2.创建表格，第二个参数可以配置创建什么样的工作表
  15.   if (Object.prototype.toString.call(luckysheet) === '[object Object]') {
  16.     luckysheet = [luckysheet]
  17.   }
  18.   //遍历sheet，将luckysheet的sheet转换成excel的sheet
  19.   luckysheet.forEach(async function(thesheet) {
  20.     // thesheet为sheet对象数据
  21.     if (thesheet.data.length === 0) return  true
  22.     const worksheet = workbook.addWorksheet(thesheet.name)
  23.     const merge = (thesheet.config && thesheet.config.merge) || {}
  24.     const borderInfo = (thesheet.config && thesheet.config.borderInfo) || {}
  25.     // 3.设置导出操作
  26.     setStyleAndValue(thesheet.data, worksheet)
  27.     setMerge(merge, worksheet)
  28.     setBorder(borderInfo, worksheet)
  29.     setImages(thesheet, worksheet, workbook);
  30.     setHyperlink(thesheet.hyperlink,worksheet)
  31.     setFrozen(thesheet.frozen,worksheet)
  32.     setConditions(thesheet.luckysheet_conditionformat_save,worksheet)
  33.     setFilter(thesheet.filter_select,worksheet)
  34.     setDataValidation(thesheet.dataVerification,worksheet)
  35.     //开启显示数据透视表
  36.     if(thesheet.isPivotTable){
  37.       worksheet.pivotTables = true
  38.       let rmax = 0
  39.       let cmax = 0
  40.       //得到行与列的最大值
  41.       thesheet.celldata.forEach(itemCell=>{
  42.         if(rmax<itemCell.r) rmax = itemCell.r
  43.         if(cmax<itemCell.c) cmax = itemCell.c
  44.       })
  45.       // 循环遍历添加边框
  46.       for(let i=0;i<=rmax;i++){
  47.         for(let j=0;j<=cmax;j++){
  48.           // 添加边框
  49.           worksheet.getCell(i+1,j+1).border = {
  50.             top: {style:'thin'},
  51.             left: {style:'thin'},
  52.             bottom: {style:'thin'},
  53.             right: {style:'thin'}
  54.           }
  55.         }
  56.       }
  57.     }
  58.     return true
  59.   })
  60.  
  61.   // return
  62.   // 4.写入 buffer
  63.   const buffer = workbook.xlsx.writeBuffer().then(data => {
  64.     const blob = new Blob([data], {
  65.       type: 'application/vnd.ms-excel;charset=utf-8'
  66.     })
  67.     //保存文件
  68.     FileSaver.saveAs(blob, `${value}.xlsx`)
  69.   })
  70.   return buffer
  71. }
  72. //设置合并数据
  73. var setMerge = function(luckyMerge = {}, worksheet) {
  74.   const mergearr = Object.values(luckyMerge)
  75.   mergearr.forEach(function(elem) {
  76.     // elem格式：{r: 0, c: 0, rs: 1, cs: 2}
  77.     // 按开始行，开始列，结束行，结束列合并（相当于 K10:M12）
  78.     worksheet.mergeCells(
  79.       elem.r + 1,
  80.       elem.c + 1,
  81.       elem.r + elem.rs,
  82.       elem.c + elem.cs
  83.     )
  84.   })
  85. }
  86. //重新设置边框
  87. var setBorder = function(luckyBorderInfo, worksheet) {
  88.   if (!Array.isArray(luckyBorderInfo)) return
  89.   luckyBorderInfo.forEach(function(elem) {
  90.     // 现在只兼容到borderType 为range的情况
  91.     if (elem.rangeType === 'range') {
  92.       let border = borderConvert(elem.borderType, elem.style, elem.color)
  93.       let rang = elem.range[0]
  94.       let row = rang.row
  95.       let column = rang.column
  96.       for (let i = row[0] + 1; i < row[1] + 2; i++) {
  97.         for (let y = column[0] + 1; y < column[1] + 2; y++) {
  98.           worksheet.getCell(i, y).border = border
  99.         }
  100.       }
  101.     }
  102.     if (elem.rangeType === 'cell') {
  103.       // col_index: 2
  104.       // row_index: 1
  105.       // b: {
  106.       //   color: '#d0d4e3'
  107.       //   style: 1
  108.       // }
  109.       const { col_index, row_index } = elem.value
  110.       const borderData = Object.assign({}, elem.value)
  111.       delete borderData.col_index
  112.       delete borderData.row_index
  113.       let border = addborderToCell(borderData, row_index, col_index)
  114.       worksheet.getCell(row_index + 1, col_index + 1).border = border
  115.     }
  116.   })
  117. }
  118. // 设置单元格样式和值
  119. var setStyleAndValue = function (cellArr, worksheet) {
  120.   if (!Array.isArray(cellArr)) return;
  121.  
  122.   cellArr.forEach(function (row, rowid) {
  123.     const dbrow = worksheet.getRow(rowid + 1);
  124.     //设置单元格行高,默认乘以0.8倍
  125.     dbrow.height = luckysheet.getRowHeight([rowid])[rowid] * 0.8;
  126.     row.every(function (cell, columnid) {
  127.       if (!cell) return true;
  128.       if (rowid == 0) {
  129.         const dobCol = worksheet.getColumn(columnid + 1);
  130.         //设置单元格列宽除以8
  131.         dobCol.width = luckysheet.getColumnWidth([columnid])[columnid] / 8;
  132.       }
  133.       let fill = fillConvert(cell.bg);
  134.       let font = fontConvert(
  135.         cell.ff||'Times New Roman',
  136.         cell.fc,
  137.         cell.bl,
  138.         cell.it,
  139.         cell.fs,
  140.         cell.cl,
  141.         cell.ul
  142.       );
  143.       let alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr);
  144.       let value;
  145.  
  146.       var v = "";
  147.       if (cell.ct && cell.ct.t == "inlineStr") {
  148.         var s = cell.ct.s;
  149.         s.forEach(function (val, num) {
  150.           v += val.v;
  151.         });
  152.       } else {
  153.         //导出后取显示值
  154.         v = cell.m;
  155.       }
  156.       if (cell.f) {
  157.         value = { formula: cell.f, result: v };
  158.       } else {
  159.         value = v;
  160.       }
  161.       let target = worksheet.getCell(rowid + 1, columnid + 1);
  162.       //添加批注
  163.       if(cell.ps){
  164.         let ps = cell.ps
  165.         target.note = ps.value
  166.       }
  167.       //单元格填充
  168.       target.fill = fill;
  169.       //单元格字体
  170.       target.font = font;
  171.       target.alignment = alignment;
  172.       target.value = value;
  173.       return true;
  174.     });
  175.   });
  176. };
  177. /**
  178.  * *数据验证
  179.  */
  180. // var setDataValidation =  function(verify,worksheet){
  181. //   if(!verify) return
  182. //   for (const key in verify) {
  183. //     const row_col = key.split('_')
  184. //     let cell =worksheet.getCell(Number(row_col[0])+1,Number(row_col[1])+1)
  185.  
  186. //   }
  187. // }
  188. /**
  189.  * *数据透视图
  190.  */
  191. /**
  192.  * *筛选导出
  193.  */
  194. var setFilter = function(filter,worksheet){
  195.   if(!filter) return
  196.   worksheet.autoFilter = createCellRange(filter.row,filter.column)
  197. }
  198. /**
  199.  * *冻结视图
  200.  */
  201. var setFrozen = function(frozen,worksheet){
  202.   //不存在冻结或取消冻结，则不执行后续代码
  203.   if(!frozen||frozen.type=='cancel') return
  204.   //执行冻结操作代码
  205.   let views = []
  206.   switch (frozen.type) {
  207.     //冻结首行
  208.     case 'row':
  209.       views = [
  210.         {state: 'frozen', xSplit: 0, ySplit: 1}
  211.       ];
  212.       break;
  213.     //冻结首列
  214.     case 'column':
  215.       views = [
  216.         {state: 'frozen', xSplit: 1, ySplit: 0}
  217.       ];
  218.       break;
  219.     //冻结首行首列
  220.     case 'both':
  221.       views = [
  222.         {state: 'frozen', xSplit: 1, ySplit: 1}
  223.       ];
  224.       break;
  225.     //冻结行至选区
  226.     case 'rangeRow':
  227.       views = [
  228.         {state: 'frozen', xSplit: 0, ySplit: frozen.range.row_focus+1}
  229.       ];
  230.       break;
  231.     //冻结列至选区
  232.     case  'rangeColumn':
  233.       views = [
  234.         {state: 'frozen', xSplit: frozen.range.column_focus+1, ySplit: 0}
  235.       ];
  236.       break;
  237.     //冻结至选区
  238.     case 'rangeBoth':
  239.       views = [
  240.         {state: 'frozen', xSplit: frozen.range.column_focus+1, ySplit: frozen.range.row_focus+1}
  241.       ];
  242.       break;
  243.   }
  244.   worksheet.views = views
  245. }
  246. /**
  247.  * *设置超链接
  248.  */
  249. var setHyperlink = function(hyperlink,worksheet){
  250.   if (!hyperlink) return;
  251.   for (const key in hyperlink) {
  252.     const row_col = key.split('_')
  253.     let cell =worksheet.getCell(Number(row_col[0])+1,Number(row_col[1])+1)
  254.     let font = cell.style.font
  255.     //设置导出后超链接的样式
  256.     // cell.font= fontConvert(font.name,'#0000ff',font.bold,font.italic,font.size,font.strike,true)
  257.     cell.font= fontConvert(font.name,'#0000ff',0,0,font.size,0,true)
  258.     if(hyperlink[key].linkType=="external"){
  259.       //外部链接
  260.       cell.value = {
  261.         text: cell.value,
  262.         hyperlink: hyperlink[key].linkAddress,
  263.         tooltip: hyperlink[key].linkTooltip
  264.       }
  265.     }else{
  266.       // 内部链接
  267.       const linkArr = hyperlink[key].linkAddress.split('!')
  268.       let hyper = '#\\'+linkArr[0]+'\\'+'!'+linkArr[1]
  269.       cell.value = {
  270.         text: cell.value,
  271.         hyperlink:hyper,
  272.         tooltip: hyperlink[key].linkTooltip
  273.       }
  274.     }
  275.     
  276.   }
  277. }
  278. /**
  279.  * *设置图片
  280.  */
  281. var setImages = function (thesheet, worksheet, workbook) {
  282.   let {
  283.     images,//图片对象或者数组
  284.     visibledatacolumn, //所有行的位置
  285.     visibledatarow, //所有列的位置
  286.   } = { ...thesheet };
  287.   if (typeof images != "object") return;
  288.   for (let key in images) {
  289.     // 通过 base64  将图像添加到工作簿
  290.     const myBase64Image = images[key].src;
  291.     //开始行 开始列 结束行 结束列
  292.     const item = images[key];
  293.     const imageId = workbook.addImage({
  294.       base64: myBase64Image,
  295.       extension: "png",
  296.     });
  297.  
  298.     const col_st = getImagePosition(item.default.left, visibledatacolumn);
  299.     const row_st = getImagePosition(item.default.top, visibledatarow);
  300.  
  301.     //模式1，图片左侧与luckysheet位置一样，像素比例保持不变，但是，右侧位置可能与原图所在单元格不一致
  302.     worksheet.addImage(imageId, {
  303.       tl: { col: col_st, row: row_st },
  304.       ext: { width: item.default.width, height: item.default.height },
  305.     });
  306.     //模式2,图片四个角位置没有变动，但是图片像素比例可能和原图不一样
  307.     // const w_ed = item.default.left+item.default.width;
  308.     // const h_ed = item.default.top+item.default.height;
  309.     // const col_ed = getImagePosition(w_ed,visibledatacolumn);
  310.     // const row_ed = getImagePosition(h_ed,visibledatarow);
  311.     // worksheet.addImage(imageId, {
  312.     //   tl: { col: col_st, row: row_st},
  313.     //   br: { col: col_ed, row: row_ed},
  314.     // });
  315.   }
  316. };
  317. //获取图片在单元格的位置
  318. var getImagePosition = function (num, arr) {
  319.   let index = 0;
  320.   let minIndex;
  321.   let maxIndex;
  322.   for (let i = 0; i < arr.length; i++) {
  323.     if (num < arr[i]) {
  324.       index = i;
  325.       break;
  326.     }
  327.   }
  328.  
  329.   if (index == 0) {
  330.     minIndex = 0;
  331.     maxIndex = 1;
  332.     return Math.abs((num - 0) / (arr[maxIndex] - arr[minIndex])) + index;
  333.   } else if (index == arr.length - 1) {
  334.     minIndex = arr.length - 2;
  335.     maxIndex = arr.length - 1;
  336.   } else {
  337.     minIndex = index - 1;
  338.     maxIndex = index;
  339.   }
  340.   let min = arr[minIndex];
  341.   let max = arr[maxIndex];
  342.   let radio = Math.abs((num - min) / (max - min)) + index;
  343.   return radio;
  344. };
  345. //单元格背景填充色处理
  346. var fillConvert = function (bg) {
  347.   if (!bg) {
  348.     return null;
  349.     // return {
  350.     // 	type: 'pattern',
  351.     // 	pattern: 'solid',
  352.     // 	fgColor:{argb:'#ffffff'.replace('#','')}
  353.     // }
  354.   }
  355.   bg = bg.indexOf("rgb") > -1 ? rgb2hex(bg) : bg;
  356.   let fill = {
  357.     type: "pattern",
  358.     pattern: "solid",
  359.     fgColor: { argb: bg.replace("#", "") },
  360.   };
  361.   return fill;
  362. };
  363. //转换颜色
  364. var rgb2hex = function (rgb) {
  365.   if (rgb.charAt(0) == "#") {
  366.     return rgb;
  367.   }
  368.  
  369.   var ds = rgb.split(/\D+/);
  370.   var decimal = Number(ds[1]) * 65536 + Number(ds[2]) * 256 + Number(ds[3]);
  371.   return "#" + zero_fill_hex(decimal, 6);
  372.  
  373.   function zero_fill_hex(num, digits) {
  374.     var s = num.toString(16);
  375.     while (s.length < digits) s = "0" + s;
  376.     return s;
  377.   }
  378. };
  379. //字体转换处理
  380. var fontConvert = function (
  381.   ff = 0,
  382.   fc = "#000000",
  383.   bl = 0,
  384.   it = 0,
  385.   fs = 10,
  386.   cl = 0,
  387.   ul = 0
  388. ) {
  389.   // luckysheet：ff(样式), fc(颜色), bl(粗体), it(斜体), fs(大小), cl(删除线), ul(下划线)
  390.   const luckyToExcel = {
  391.     0: "微软雅黑",
  392.     1: "宋体（Song）",
  393.     2: "黑体（ST Heiti）",
  394.     3: "楷体（ST Kaiti）",
  395.     4: "仿宋（ST FangSong）",
  396.     5: "新宋体（ST Song）",
  397.     6: "华文新魏",
  398.     7: "华文行楷",
  399.     8: "华文隶书",
  400.     9: "Arial",
  401.     10: "Times New Roman",
  402.     11: "Tahoma ",
  403.     12: "Verdana",
  404.     num2bl: function (num) {
  405.       return num === 0||false ? false : true;
  406.     },
  407.   };
  408.   // let color = fc ? "" : (fc + "").indexOf("rgb") > -1 ? util.rgb2hex(fc) : fc;
  409.   // let color = fc ? fc : (fc + "").indexOf("rgb") > -1 ? util.rgb2hex(fc) : fc;
  410.  
  411.   let font = {
  412.     name: ff,
  413.     family: 1,
  414.     size: fs,
  415.     color: { argb: fc.replace("#", "") },
  416.     bold: luckyToExcel.num2bl(bl),
  417.     italic: luckyToExcel.num2bl(it),
  418.     underline: luckyToExcel.num2bl(ul),
  419.     strike: luckyToExcel.num2bl(cl),
  420.   };
  421.  
  422.   return font;
  423. };
  424. //对齐转换
  425. var alignmentConvert = function(
  426.   vt = 'default',
  427.   ht = 'default',
  428.   tb = 'default',
  429.   tr = 'default'
  430. ) {
  431.   // luckysheet:vt(垂直), ht(水平), tb(换行), tr(旋转)
  432.   const luckyToExcel = {
  433.     vertical: {
  434.       0: 'middle',
  435.       1: 'top',
  436.       2: 'bottom',
  437.       default: 'top'
  438.     },
  439.     horizontal: {
  440.       0: 'center',
  441.       1: 'left',
  442.       2: 'right',
  443.       default: 'left'
  444.     },
  445.     wrapText: {
  446.       0: false,
  447.       1: false,
  448.       2: true,
  449.       default: false
  450.     },
  451.     textRotation: {
  452.       0: 0,
  453.       1: 45,
  454.       2: -45,
  455.       3: 'vertical',
  456.       4: 90,
  457.       5: -90,
  458.       default: 0
  459.     }
  460.   }
  461.  
  462.   let alignment = {
  463.     vertical: luckyToExcel.vertical[vt],
  464.     horizontal: luckyToExcel.horizontal[ht],
  465.     wrapText: luckyToExcel.wrapText[tb],
  466.     textRotation: luckyToExcel.textRotation[tr]
  467.   }
  468.   return alignment
  469. }
  470. //边框转换
  471. var borderConvert = function(borderType, style = 1, color = '#000') {
  472.   // 对应luckysheet的config中borderinfo的的参数
  473.   if (!borderType) {
  474.     return {}
  475.   }
  476.   const luckyToExcel = {
  477.     type: {
  478.       'border-all': 'all',
  479.       'border-top': 'top',
  480.       'border-right': 'right',
  481.       'border-bottom': 'bottom',
  482.       'border-left': 'left'
  483.     },
  484.     style: {
  485.       0: 'none',
  486.       1: 'thin',
  487.       2: 'hair',
  488.       3: 'dotted',
  489.       4: 'dashDot', // 'Dashed',
  490.       5: 'dashDot',
  491.       6: 'dashDotDot',
  492.       7: 'double',
  493.       8: 'medium',
  494.       9: 'mediumDashed',
  495.       10: 'mediumDashDot',
  496.       11: 'mediumDashDotDot',
  497.       12: 'slantDashDot',
  498.       13: 'thick'
  499.     }
  500.   }
  501.   let template = {
  502.     style: luckyToExcel.style[style],
  503.     color: { argb: color.replace('#', '') }
  504.   }
  505.   let border = {}
  506.   if (luckyToExcel.type[borderType] === 'all') {
  507.     border['top'] = template
  508.     border['right'] = template
  509.     border['bottom'] = template
  510.     border['left'] = template
  511.   } else {
  512.     border[luckyToExcel.type[borderType]] = template
  513.   }
  514.   return border
  515. }
  516. //向单元格添加边框
  517. function addborderToCell(borders, row_index, col_index) {
  518.   let border = {}
  519.   const luckyExcel = {
  520.     type: {
  521.       l: 'left',
  522.       r: 'right',
  523.       b: 'bottom',
  524.       t: 'top'
  525.     },
  526.     style: {
  527.       0: 'none',
  528.       1: 'thin',
  529.       2: 'hair',
  530.       3: 'dotted',
  531.       4: 'dashDot', // 'Dashed',
  532.       5: 'dashDot',
  533.       6: 'dashDotDot',
  534.       7: 'double',
  535.       8: 'medium',
  536.       9: 'mediumDashed',
  537.       10: 'mediumDashDot',
  538.       11: 'mediumDashDotDot',
  539.       12: 'slantDashDot',
  540.       13: 'thick'
  541.     }
  542.   }
  543.   for (const bor in borders) {
  544.     if (borders[bor].color.indexOf('rgb') === -1) {
  545.       border[luckyExcel.type[bor]] = {
  546.         style: luckyExcel.style[borders[bor].style],
  547.         color: { argb: borders[bor].color.replace('#', '') }
  548.       }
  549.     } else {
  550.       border[luckyExcel.type[bor]] = {
  551.         style: luckyExcel.style[borders[bor].style],
  552.         color: { argb: borders[bor].color }
  553.       }
  554.     }
  555.   }
  556.  
  557.   return border
  558. }

        4. 涉及到的其他文件 

        setConditions.js
  1. /**
  2.  * *条件格式设置
  3. */
  4. import { createCellRange } from './utils'
  5. export const setConditions = function(conditions,worksheet){
  6.     //条件格式不存在，则不执行后续代码
  7.     if(conditions==undefined) return
  8.  
  9.     //循环遍历规则列表
  10.     conditions.forEach(item => {
  11.         let ruleObj = {
  12.             ref:createCellRange(item.cellrange[0].row,item.cellrange[0].column),
  13.             rules:[]
  14.         }
  15.         //lucksheet对应的为----突出显示单元格规则和项目选区规则
  16.         if(item.type=='default'){
  17.             //excel中type为cellIs的条件下
  18.             if(item.conditionName=='equal'||'greaterThan'||'lessThan'||'betweenness'){
  19.                     ruleObj.rules = setDefaultRules({
  20.                         type:'cellIs',
  21.                         operator:item.conditionName=='betweenness'?'between':item.conditionName,
  22.                         condvalue:item.conditionValue,
  23.                         colorArr:[item.format.cellColor,item.format.textColor]
  24.                     })
  25.                     worksheet.addConditionalFormatting(ruleObj)
  26.                 }
  27.             //excel中type为containsText的条件下
  28.             if(item.conditionName=='textContains'){
  29.                 ruleObj.rules = [
  30.                         {
  31.                             type:'containsText',
  32.                             operator:'containsText', //表示如果单元格值包含在text 字段中指定的值，则应用格式
  33.                             text:item.conditionValue[0],
  34.                             style: setStyle([item.format.cellColor,item.format.textColor])
  35.                         }
  36.                     ]
  37.                 worksheet.addConditionalFormatting(ruleObj)
  38.             }
  39.             //发生日期--时间段
  40.             if(item.conditionName=='occurrenceDate'){
  41.                 ruleObj.rules = [
  42.                         {
  43.                             type:'timePeriod',
  44.                             timePeriod:'today', //表示如果单元格值包含在text 字段中指定的值，则应用格式
  45.                             style: setStyle([item.format.cellColor,item.format.textColor])
  46.                         }
  47.                     ]
  48.                 worksheet.addConditionalFormatting(ruleObj)
  49.             }
  50.             //重复值--唯一值
  51.             // if(item.conditionName=='duplicateValue'){
  52.             //     ruleObj.rules = [
  53.             //             {
  54.             //                 type:'expression',
  55.             //                 formulae:'today', //表示如果单元格值包含在text 字段中指定的值，则应用格式
  56.             //                 style: setStyle([item.format.cellColor,item.format.textColor])
  57.             //             }
  58.             //         ]
  59.             //     worksheet.addConditionalFormatting(ruleObj)
  60.             // }
  61.             //项目选区规则--top10前多少项的操作
  62.             if(item.conditionName=='top10'||'top10%'||'last10'||'last10%'){
  63.                 ruleObj.rules = [
  64.                         {
  65.                             type:'top10',
  66.                             rank:item.conditionValue[0], //指定格式中包含多少个顶部（或底部）值
  67.                             percent:item.conditionName=='top10'||'last10'?false:true,
  68.                             bottom:item.conditionName=='top10'||'top10%'?false:true,
  69.                             style: setStyle([item.format.cellColor,item.format.textColor])
  70.                         }
  71.                     ]
  72.                 worksheet.addConditionalFormatting(ruleObj)
  73.             }
  74.             //项目选区规则--高于/低于平均值的操作
  75.             if(item.conditionName=='AboveAverage'||'SubAverage'){
  76.                 ruleObj.rules = [
  77.                         {
  78.                             type:'aboveAverage',
  79.                             aboveAverage:item.conditionName=='AboveAverage'?true:false,
  80.                             style: setStyle([item.format.cellColor,item.format.textColor])
  81.                         }
  82.                     ]
  83.                 worksheet.addConditionalFormatting(ruleObj)
  84.             }
  85.             return
  86.         }
  87.             
  88.         //数据条
  89.         if(item.type == 'dataBar'){
  90.             ruleObj.rules = [
  91.                 {
  92.                     type:'dataBar',
  93.                     style:{}
  94.                 }
  95.             ]
  96.             worksheet.addConditionalFormatting(ruleObj)
  97.             return
  98.         }
  99.         //色阶
  100.         if(item.type == 'colorGradation'){
  101.             ruleObj.rules = [
  102.                 {
  103.                     type:'colorScale',
  104.                     color:item.format,
  105.                     style:{}
  106.                 }
  107.             ]
  108.             worksheet.addConditionalFormatting(ruleObj)
  109.             return
  110.         }
  111.         //图标集
  112.         if(item.type == 'icons'){
  113.             ruleObj.rules = [
  114.                     {
  115.                         type:'iconSet',
  116.                         iconSet:item.format.len
  117.                     }
  118.                 ]
  119.             worksheet.addConditionalFormatting(ruleObj)
  120.             return
  121.         }
  122.     });
  123.   }
  124. /**
  125.  * 
  126.  * @param {
  127.  *  type:lucketsheet对应的条件导出类型；
  128.  *  operator：excel对应的条件导入类型；
  129.  *  condvalue：1个公式字符串数组，返回要与每个单元格进行比较的值；
  130.  *  colorArr：颜色数组，第一项为单元格填充色，第二项为单元格文本颜色
  131.  * } obj 
  132.  * @returns 
  133.  */
  134. function setDefaultRules(obj){
  135.     let rules = [
  136.         {
  137.             type:obj.type,
  138.             operator:obj.operator,
  139.             formulae:obj.condvalue,
  140.             style:setStyle(obj.colorArr)
  141.         }
  142.     ]
  143.     return rules
  144. }
  145. /**
  146.  * 
  147.  * @param {颜色数组，第一项为单元格填充色，第二项为单元格文本颜色} colorArr 
  148.  */
  149. function setStyle(colorArr){
  150.     return {
  151.         fill: {type: 'pattern', pattern: 'solid', bgColor: {argb: colorArr[0].replace("#", "")}},
  152.         font: {color:{ argb: colorArr[1].replace("#", "")},}
  153.     }
  154. }
 setDataValidation.js
  1. /**
  2.  * *数据验证
  3.  */
  4. export const setDataValidation = function (verify, worksheet) {
  5.   // 不存在不执行
  6.   if (!verify) return;
  7.   // 存在则有以下逻辑
  8.   for (const key in verify) {
  9.     const row_col = key.split("_");
  10.     let cell = worksheet.getCell(
  11.       Number(row_col[0]) + 1,
  12.       Number(row_col[1]) + 1
  13.     );
  14.     let { type, type2, value1, value2 } = verify[key];
  15.     //下拉框--list
  16.     if (type == "dropdown") {
  17.       cell.dataValidation = {
  18.         type: "list",
  19.         allowBlank: true,
  20.         formulae: [`${value1}`],
  21.       };
  22.       continue;
  23.     }
  24.     //整数--whole
  25.     if (type == "number_integer") {
  26.       cell.dataValidation = {
  27.         type: "whole",
  28.         operator: setOperator(type2),
  29.         showErrorMessage: true,
  30.         formulae: value2 ? [Number(value1), Number(value2)] : [Number(value1)],
  31.         errorStyle: "error",
  32.         errorTitle: "警告",
  33.         error: errorMsg(type2, type, value1, value2),
  34.       };
  35.       continue;
  36.     }
  37.     //小数-数字--decimal
  38.     if (type == "number_decimal" || "number") {
  39.       cell.dataValidation = {
  40.         type: "decimal",
  41.         operator: setOperator(type2),
  42.         allowBlank: true,
  43.         showInputMessage: true,
  44.         formulae: value2 ? [Number(value1), Number(value2)] : [Number(value1)],
  45.         promptTitle: "警告",
  46.         prompt: errorMsg(type2, type, value1, value2),
  47.       };
  48.       continue;
  49.     }
  50.     //长度受控的文本--textLength
  51.     if (type == "text_length") {
  52.       cell.dataValidation = {
  53.         type: "textLength",
  54.         operator: setOperator(type2),
  55.         showErrorMessage: true,
  56.         allowBlank: true,
  57.         formulae: value2 ? [Number(value1), Number(value2)] : [Number(value1)],
  58.         promptTitle: "错误提示",
  59.         prompt: errorMsg(type2, type, value1, value2),
  60.       };
  61.       continue;
  62.     }
  63.     //文本的内容--text_content
  64.     if (type == "text_content") {
  65.       cell.dataValidation = {};
  66.       continue;
  67.     }
  68.     //日期--date
  69.     if (type == "date") {
  70.       cell.dataValidation = {
  71.         type: "date",
  72.         operator: setDateOperator(type2),
  73.         showErrorMessage: true,
  74.         allowBlank: true,
  75.         promptTitle: "错误提示",
  76.         prompt: errorMsg(type2, type, value1, value2),
  77.         formulae: value2
  78.           ? [new Date(value1), new Date(value2)]
  79.           : [new Date(value1)],
  80.       };
  81.       continue;
  82.     }
  83.     //有效性--custom;type2=="phone"/"card"
  84.     if (type == "validity") {
  85.       
  86.       // cell.dataValidation = {
  87.       //   type: 'custom',
  88.       //   allowBlank: true,
  89.       //   formulae: [type2]
  90.       // };
  91.       continue;
  92.     }
  93.     //多选框--checkbox
  94.     if (type == "checkbox") {
  95.       cell.dataValidation = {};
  96.       continue;
  97.     }
  98.   }
  99. };
  100. //类型type值为"number"/"number_integer"/"number_decimal"/"text_length"时，type2值可为
  101. function setOperator(type2) {
  102.   let transToOperator = {
  103.     bw: "between",
  104.     nb: "notBetween",
  105.     eq: "equal",
  106.     ne: "notEqual",
  107.     gt: "greaterThan",
  108.     lt: "lessThan",
  109.     gte: "greaterThanOrEqual",
  110.     lte: "lessThanOrEqual",
  111.   };
  112.   return transToOperator[type2];
  113. }
  114. //数字错误性提示语
  115. function errorMsg(type2, type, value1 = "", value2 = "") {
  116.   const tip = "你输入的不是";
  117.   const tip1 = "你输入的不是长度";
  118.   let errorTitle = {
  119.     bw: `${
  120.       type == "text_length" ? tip1 : tip
  121.     }介于${value1}和${value2}之间的${numType(type)}`,
  122.     nb: `${
  123.       type == "text_length" ? tip1 : tip
  124.     }不介于${value1}和${value2}之间的${numType(type)}`,
  125.     eq: `${type == "text_length" ? tip1 : tip}等于${value1}的${numType(type)}`,
  126.     ne: `${type == "text_length" ? tip1 : tip}不等于${value1}的${numType(
  127.       type
  128.     )}`,
  129.     gt: `${type == "text_length" ? tip1 : tip}大于${value1}的${numType(type)}`,
  130.     lt: `${type == "text_length" ? tip1 : tip}小于${value1}的${numType(type)}`,
  131.     gte: `${type == "text_length" ? tip1 : tip}大于等于${value1}的${numType(
  132.       type
  133.     )}`,
  134.     lte: `${type == "text_length" ? tip1 : tip}小于等于${value1}的${numType(
  135.       type
  136.     )}`,
  137.     //日期
  138.     bf: `${type == "text_length" ? tip1 : tip}早于${value1}的${numType(type)}`,
  139.     nbf: `${type == "text_length" ? tip1 : tip}不早于${value1}的${numType(type)}`,
  140.     af: `${type == "text_length" ? tip1 : tip}晚于${value1}的${numType(
  141.       type
  142.     )}`,
  143.     naf: `${type == "text_length" ? tip1 : tip}不晚于${value1}的${numType(
  144.       type
  145.     )}`,
  146.   };
  147.  
  148.   return errorTitle[type2];
  149. }
  150. // 数字类型（整数，小数，十进制数）
  151. function numType(type) {
  152.   let num = {
  153.     number_integer: "整数",
  154.     number_decimal: "小数",
  155.     number: "数字",
  156.     text_length: "文本",
  157.     date:'日期'
  158.   };
  159.   return num[type];
  160. }
  161. //类型type值为date时
  162. function setDateOperator(type2) {
  163.   let transToOperator = {
  164.     bw: "between",
  165.     nb: "notBetween",
  166.     eq: "equal",
  167.     ne: "notEqual",
  168.     bf: "greaterThan",
  169.     nbf: "lessThan",
  170.     af: "greaterThanOrEqual",
  171.     naf: "lessThanOrEqual",
  172.   };
  173.   return transToOperator[type2];
  174. }
 utils.js
  1. /**
  2.  * *创建单元格所在列的列的字母
  3.  * @param {列数的index值} n 
  4.  * @returns 
  5.  */
  6. export const createCellPos = function(n) {
  7.     let ordA = 'A'.charCodeAt(0)
  8.   
  9.     let ordZ = 'Z'.charCodeAt(0)
  10.     let len = ordZ - ordA + 1
  11.     let s = ''
  12.     while (n >= 0) {
  13.       s = String.fromCharCode((n % len) + ordA) + s
  14.   
  15.       n = Math.floor(n / len) - 1
  16.     }
  17.     return s
  18.   }
  19.   /**
  20.    * *创建单元格范围，期望得到如：A1:D6
  21.    * @param {单元格行数组（例如：[0,3]）} rowArr 
  22.    * @param {单元格列数组（例如：[5,7]）} colArr 
  23.    * */
  24. export const createCellRange = function(rowArr,colArr){
  25.     const startCell = createCellPos(colArr[0])+(rowArr[0]+1)
  26.     const endCell = createCellPos(colArr[1])+(rowArr[1]+1)
  27.   
  28.     return startCell+':'+endCell
  29.   }

最后 ：

附上exceljs文档的参考地址：

GitHub： GitHub - exceljs/exceljs: Excel Workbook Manager

gitee：exceljs: exceljs不更新了在exceljs@4.3.0基础上优化了内存占用，增加了新的api

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/知新_RL/article/detail/396052
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号