【开源组件】- 表格处理 - Luckysheet

Luckysheet + Vue的使用

😄生命不息，写作不止
🔥 继续踏上学习之路，学之分享笔记
👊 总有一天我也能像各位大佬一样
🏆 一个有梦有戏的人 @怒放吧德德
🌝分享学习心得，欢迎指正，大家一起学习成长！

简介

Luckysheet ，一款纯前端类似excel的在线表格，功能强大、配置简单、完全开源。

官网：https://mengshukeji.gitee.io/LuckysheetDocs/zh/guide/

需求

前端从服务器中获取excel文件，通过luckysheet插件在线编辑，完成后将此文件传送到服务器保存。

安装：

<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/plugins/css/pluginsCss.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/plugins/plugins.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/css/luckysheet.css' />
<link rel='stylesheet' href='https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/assets/iconfont/iconfont.css' />
<script src="https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/plugins/js/plugin.js"></script>
<script src="https://cdn.jsdelivr.net/npm/luckysheet@latest/dist/luckysheet.umd.js"></script>

npm install luckyexcel

获取文件对象

通过后端传来文件下载地址和文件名就可以，使用luckyexcel的方法进行初始化

//导入
import LuckyExcel from 'luckyexcel'
​
//在mounted中
mounted() {
    $(function () {
      //配置项 option - 初始化luckysheet实例
      var options = {
        container: 'luckysheet', //luckysheet为容器id
        title:'s',
        lang:'zh',
        showinfobar:false,
        data:[
          {
            "name": "sheet1", //工作表名称
            "color": "", //工作表颜色
            "index": 0, //工作表索引
            "status": 1, //激活状态
            "order": 0, //工作表的下标
            "hide": 0,//是否隐藏
            "row": 20, //行数
            "column": 50, //列数
            "defaultRowHeight": 19, //自定义行高
            "defaultColWidth": 73, //自定义列宽
            "celldata": [
              {"r":0,"c":0,"v":{"v":6656,"ct":{"fa":"General","t":"n"},}},
              {"r":0,"c":1,"v":{"fs":11,"fc":"rgb(51,68,222)","ht":1,"vt":1,"v":"sasa","ct":{"fa":"General","t":"n"},"m":"2"}},
            ], //初始化使用的单元格数据
            "config": {
              "merge": {
              }, //合并单元格
              "rowlen":{}, //表格行高
              "columnlen":{}, //表格列宽
              "rowhidden":{}, //隐藏行
              "colhidden":{}, //隐藏列
              "borderInfo":{}, //边框
              "authority":{}, //工作表保护
            },
          },
          //工作表2
          {
            "name": "Sheet2",
            "color": "",
            "index": 1,
            "status": 0,
            "order": 1,
            "celldata": [],
            "config": {}
          },
          //工作表3
          {
            "name": "Sheet3",
            "color": "",
            "index": 2,
            "status": 0,
            "order": 2,
            "celldata": [],
            "config": {},
          }
        ]
      }
      luckysheet.create(options) // 创建
      //如果是获取了数据，就不需要option，直接往下走就行
      var url = 'http://localhost:8585/file/a9270176e41c41c1ac67b0c8965e5555.xlsx'
      LuckyExcel.transformExcelToLuckyByUrl(url, "op",  (exportJson, luckysheetfile) => {
        console.log(exportJson)
        if (exportJson.sheets == null || exportJson.sheets.length == 0) {
          alert("读取excel文件内容失败，目前不支持xls文件！");
          return;
        }
        luckysheet.create({
          container: 'luckysheet', // luckysheet is the container id
          data: exportJson.sheets,
          title: exportJson.info.name,
          userInfo: exportJson.info.name.creator,
          showtoolbarConfig: {
            print: false // 隐藏插件内部打印按钮
          },
          showinfobar: false, // 显示头部返回标题栏
        });
      });
    })
  },

以上即可在线编辑excel文件了。

将sheet转成excel文件发到后端进行保存到服务器

安装插件

cnpm i vue-print-nb --save
cnpm i exceljs --save

（如果需要打印）就引入静态资源，不然使用cdn的就够了

<link rel='stylesheet' href='./luckysheet/plugins/css/pluginsCss.css' />
<link rel='stylesheet' href='./luckysheet/plugins/plugins.css' />
<link rel='stylesheet' href='./luckysheet/css/luckysheet.css' />
<link rel='stylesheet' href='./luckysheet/assets/iconfont/iconfont.css' />
<script src="./luckysheet/plugins/js/plugin.js"></script>
<script src="./luckysheet/luckysheet.umd.js"></script>

思路：
先获取luckysheet编辑好的数据，并且转成excel的文件对象，在将文件对象传到后端，后端是接收MultipartFile类型的文件，所以在前端传参的时候需要注意参数的设置。

首先，使用csdn博主写好的js文件，也就使用到获取file文件，具体代码后面备注

获取excel文件

async exportExcel() {
    // eslint-disable-next-line no-undef
    const data = luckysheet.getluckysheetfile()
    const exportData = await exportExcel(data)
    const blob = new Blob([exportData])
    const file = new File([blob], Math.random()*100 + '.xlsx', { 
    type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
return file
},

请求接口将文件保存到服务器中,参数要设置好，需要添加"Content-Type": “multipart/form-data”，方可识别数据类型

async saveSheet() {
    // console.log(JSON.stringify(luckysheet.getAllSheets()));
    // console.log(JSON.stringify(luckysheet.getluckysheetfile()));
    this.$confirm('此操作会将excel上传到服务器, 请检查是否已编辑?', '提示', {
        confirmButtonText: '确定',
        cancelButtonText: '取消',
        type: 'warning'
    }).then(async () => {
        // 拿到file对象
        const file = await this.exportExcel()
        let formData = new FormData();
        //数据
        formData.append('file', file);
        //请求头
        let config = {headers: {"Content-Type": "multipart/form-data",},};
        //请求路径
        let url = "/file/upload";
        this.$http.post(url, formData, config).then((res) => {
            //上传成功后要进行的操作
            console.log(res)
            this.$message({
                type: 'success',
                message: '上传成功!'
            });
        }).finally(() => {
            if (window.history.length <= 1) {
                this.$router.push({ path: "/home" });
                return false;
            } else {
                this.$router.go(-1);
            }
        })
    }).catch(() => {
        this.$message({
            type: 'info',
            message: '已取消上传'
        });
    });
},

后端接口

@PostMapping("/upload")
    public String upload(@RequestParam MultipartFile file) throws IOException {
        String originalFilename = file.getOriginalFilename();
        String type = FileUtil.extName(originalFilename);
        long size = file.getSize();
        //定义一个唯一文件识别码
        String uuid = IdUtil.fastSimpleUUID();
        String fileUUID =  uuid + StrUtil.DOT + type;
        File uploadFile = new File(fileUploadPath + fileUUID);
        File parentFile = uploadFile.getParentFile();
        //判断配置的文件目录是否存在，若不在则创建一个新的文件目录
        if (!parentFile.exists()) {
            parentFile.mkdir();
        }
        //获取文件的url
        String url;
        //创建文件的MD5
        String md5 = SecureUtil.md5(file.getInputStream());
        //从数据库里查询是否存在相同的记录...通过文件的md5查询文件
        Files dbFile = fileService.getFileByMd5(md5);
        if (dbFile != null) {
            url = dbFile.getUrl();
        } else {
            // 上传文件到磁盘
            file.transferTo(uploadFile);
            // 数据库若不存在重复文件，则不删除刚才上传的文件
            url = "http://localhost:8585/file/" + fileUUID;
        }
        //存储到数据库
        Files saveFile = new Files();
        saveFile.setName(originalFilename);
        saveFile.setType(type);
        saveFile.setSide(size/1024);//这是b要装成k就除以1024
        saveFile.setUrl(url);
        saveFile.setMd5(md5);
        fileService.save(saveFile);
        return url;
    }

使用浏览器下载excel文件到本地

/**
* @description: 浏览器下载excel
* @author: flyer
* @param {Object} blob excel数据，也可以是file对象
*/
async download() {
    const file = await this.exportExcel()
    if (window.navigator && window.navigator.msSaveBlob) {
        window.navigator.msSaveBlob(file, file.name)
    } else {
        const a = document.createElement('a')
        a.download = file.name
        a.href = URL.createObjectURL(file)
        a.style.display = 'none'
        document.body.appendChild(a)
        a.click()
        document.body.removeChild(a)
    }
},

打印功能

/**
* @description: 打印默认区域
* @author: flyer
* @param {Boolean} flag 是否默认打印
*/
printFn(flag) {
    // eslint-disable-next-line no-undef
    const src = flag ? luckysheet.getScreenshot() : luckysheet.getScreenshot({ range: 'A1:J50' })
    const $img = `<img src=${src} style="max-width: 90%;" />`
    this.$nextTick(() => {
        document.querySelector(`#${this.printParam.id}`).innerHTML = $img
    })
},

代码备注

Editexcel：

<template>
  <div>
    <el-form :inline="true" style="margin-left: 10px">
      <el-form-item>
        <el-button size="mini" type="success" @click="saveSheet">保存</el-button>
      </el-form-item>
      <el-form-item>
        <el-button size="mini" type="danger" @click="download">下载</el-button>
      </el-form-item>
      <el-form-item>
        <el-button size="mini" v-print="`#${printParam.id}`" type="primary" class="tool" plain @click="printFn(false)">打印</el-button>
      </el-form-item>
      <el-form-item>
        <el-button size="mini" v-print="`#${printParam.id}`" type="primary" class="tool" plain @click="printFn(true)">打印选中区域</el-button>
      </el-form-item>
      <el-form-item>
        <el-tooltip class="item" effect="dark" content="选择一个本地文件上传到服务器" placement="bottom">
          <el-upload action="http://localhost:8585/file/upload"
                     :file-list="fileList">
            <el-button size="mini" type="primary">点击上传</el-button>
            <div slot="tip" class="el-upload__tip">选择xlsx文件上传</div>
          </el-upload>
        </el-tooltip>
      </el-form-item>
    </el-form>
    <div id="luckysheet" style="margin:40px 0 0 0;padding:0px;position:absolute;width:100%;height:100%;left: 0px;top: 0px;"></div>
    <div :id="printParam.id" :style="{ textAlign : printPosition }" />
  </div>
</template>

<script>
import LuckyExcel from 'luckyexcel'
import exportExcel from '../exportExcel'
export default {
  name: "EditExcel",
  props: {
    // 打印时base64图片的位置
    printPosition: {
      default: 'center',
      type: String
    },
  },
  data() {
    return {
      fileList: [],
      // 打印基础配置
      printParam: {
        // 对应区域
        id: 'print_html'
      }
    }
  },
  mounted() {
    $(function () {
      //配置项
      // var options = {
      //   container: 'luckysheet', //luckysheet为容器id
      //   title:'new excel',
      //   lang:'zh',
      //   showinfobar:false,
      //   data:[
      //     {
      //       "name": "sheet1", //工作表名称
      //       "color": "", //工作表颜色
      //       "index": 0, //工作表索引
      //       "status": 1, //激活状态
      //       "order": 0, //工作表的下标
      //       "hide": 0,//是否隐藏
      //       "row": 20, //行数
      //       "column": 30, //列数
      //       "defaultRowHeight": 19, //自定义行高
      //       "defaultColWidth": 73, //自定义列宽
      //       "celldata": [], //初始化使用的单元格数据
      //       "config": {
      //         "merge": {
      //         }, //合并单元格
      //         "rowlen":{}, //表格行高
      //         "columnlen":{}, //表格列宽
      //         "rowhidden":{}, //隐藏行
      //         "colhidden":{}, //隐藏列
      //         "borderInfo":{}, //边框
      //         "authority":{}, //工作表保护
      //       },
      //     },
      //     //工作表2
      //     {
      //       "name": "Sheet2",
      //       "color": "",
      //       "index": 1,
      //       "status": 0,
      //       "order": 1,
      //       "celldata": [],
      //       "config": {}
      //     }
      //   ],
      //   "scrollLeft": 0, //左右滚动条位置
      //   "scrollTop": 0, //上下滚动条位置
      // }
      //意味着一定要有路径过来
      var url = 'http://localhost:8585/file/a9270176e41c41c1ac67b0c8965e5555.xlsx';
      var name = '用户表';
      LuckyExcel.transformExcelToLuckyByUrl(url, name,  (exportJson, luckysheetfile) => {
        console.log(exportJson)
        if (exportJson.sheets == null || exportJson.sheets.length == 0) {
          alert("读取excel文件内容失败，目前不支持xls文件！");
          return;
        }
        luckysheet.create({
          container: 'luckysheet', // luckysheet is the container id
          data:exportJson.sheets,
          title:exportJson.info.name,
          userInfo:exportJson.info.name.creator,
          showtoolbarConfig: {
            print: false // 隐藏插件内部打印按钮
          },
          showinfobar: false, // 显示头部返回标题栏
        });
      });
    })
  },
  methods: {
    /**
     * @description: 获取在线excel数据后编辑成excel文件导出
     * @author: flyer
     */
    async exportExcel() {
      // eslint-disable-next-line no-undef
      const data = luckysheet.getluckysheetfile()
      const exportData = await exportExcel(data)
      const blob = new Blob([exportData])
      const file = new File([blob], Math.random()*100 + '.xlsx', { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })
      return file
    },

    async saveSheet() {
      // console.log(JSON.stringify(luckysheet.getAllSheets()));
      // console.log(JSON.stringify(luckysheet.getluckysheetfile()));
      this.$confirm('此操作会将excel上传到服务器, 请检查是否已编辑?', '提示', {
        confirmButtonText: '确定',
        cancelButtonText: '取消',
        type: 'warning'
      }).then(async () => {
        // 拿到file对象
        const file = await this.exportExcel()
        let formData = new FormData();
        //数据
        formData.append('file', file);
        //请求头
        let config = {headers: {"Content-Type": "multipart/form-data",},};
        //请求路径
        let url = "/file/upload";
        this.$http.post(url, formData, config).then((res) => {
          //上传成功后要进行的操作
          console.log(res)
          this.$message({
            type: 'success',
            message: '上传成功!'
          });
        }).finally(() => {
          if (window.history.length <= 1) {
            this.$router.push({ path: "/home" });
            return false;
          } else {
            this.$router.go(-1);
          }
        })
      }).catch(() => {
        this.$message({
          type: 'info',
          message: '已取消上传'
        });
      });
    },
    /**
     * @description: 浏览器下载excel
     * @author: flyer
     * @param {Object} blob excel数据，也可以是file对象
     */
    async download() {
      const file = await this.exportExcel()
      if (window.navigator && window.navigator.msSaveBlob) {
        window.navigator.msSaveBlob(file, file.name)
      } else {
        const a = document.createElement('a')
        a.download = file.name
        a.href = URL.createObjectURL(file)
        a.style.display = 'none'
        document.body.appendChild(a)
        a.click()
        document.body.removeChild(a)
      }
    },
    /**
     * @description: 打印默认区域
     * @author: flyer
     * @param {Boolean} flag 是否默认打印
     */
    printFn(flag) {
      // eslint-disable-next-line no-undef
      const src = flag ? luckysheet.getScreenshot() : luckysheet.getScreenshot({ range: 'A1:J50' })
      const $img = `<img src=${src} style="max-width: 90%;" />`
      this.$nextTick(() => {
        document.querySelector(`#${this.printParam.id}`).innerHTML = $img
      })
    },
  }
}
</script>
<style scoped>
</style>

exportExcel.js

const Excel = require('exceljs');

const exportExcel = async luckysheet => {
  // 参数为luckysheet.getluckysheetfile()获取的对象
  // 1.创建工作簿，可以为工作簿添加属性
  const workbook = new Excel.Workbook();
  // 2.创建表格，第二个参数可以配置创建什么样的工作表
  luckysheet.every(table => {
    if (table.data.length === 0) return true;
    console.log('table', table);
    // const worksheet = workbook.addWorksheet(table.name)
    const worksheet = workbook.addWorksheet(table.name, { views: [{ state: 'frozen', xSplit: 1, ySplit: 0 }] });
    setFrozen(table.frozen, worksheet);
    setColunmWidth(table.config.columnlen, worksheet);
    setRowWidth(table.config.rowlen, worksheet);
    // 3.设置单元格合并,设置单元格边框,设置单元格样式,设置值
    setStyleAndValue(table.data, worksheet, table.hyperlink);
    setMerge(table.config.merge, worksheet);
    setBorder(table.config.borderInfo, worksheet);
    setImg(table.images, workbook, worksheet);
    return true;
  });
  // 4.写入 buffer
  const buffer = await workbook.xlsx.writeBuffer();
  return buffer;
};

/**
 * @description: 冻结行列
 * @author: flyer
 * @param {Object} frozen 冻结数据
 * @param {Object} worksheet 表格
 */
const setFrozen = (frozen, worksheet) => {
  if (!frozen) return
  if(!worksheet.views) worksheet.views = {}
  switch (frozen.type) {
    case 'row':
      worksheet.views = [{ state: 'frozen', xSplit: 1, ySplit: 0 }];
      break;
    case 'column':
      worksheet.views = [{ state: 'frozen', xSplit: 0, ySplit: 1 }];
      break;
    case 'both':
      worksheet.views = [{ state: 'frozen', xSplit: 1, ySplit: 1 }];
      break;
    case 'rangeRow':
      worksheet.views = [{ state: 'frozen', xSplit: 0, ySplit: frozen.range.row_focus + 1}];
      break;
    case 'rangeColumn':
      worksheet.views = [{ state: 'frozen', xSplit: frozen.range.column_focus + 1, ySplit:0 }];
      break;
    case 'rangeBoth':
      worksheet.views = [{ state: 'frozen', xSplit: frozen.range.column_focus + 1, ySplit: frozen.range.row_focus + 1 }];
      break;
    case 'cancel':
      worksheet.views = [{ state: 'frozen', xSplit: 0, ySplit: 0}];
      break;
    default:
      break;
  }
};

/**
 * @description: 设置插入图片
 * @author: flyer
 * @param {Object} images 图片文件
 * @param {Object} workbook 工作区
 * @param {Object} worksheet excel表格
 */
const setImg = (images, workbook, worksheet) => {
  for (const key in images) {
    const imageId = workbook.addImage({
      base64: images[key].src,
      extension: 'png'
    });
    const heights = worksheet.columns.map(item => item.width);
    let width = 0;
    let i = 0;
    let k = 0;
    while (width < images[key].default.left) {
      k = i + (images[key].default.left - width) / (heights[i] ? heights[i] * 8 || 72 : 72);
      width += heights[i] ? heights[i] * 8 || 72 : 72;
      i++;
    }
    worksheet.addImage(imageId, {
      tl: { col: k, row: images[key].default.top / 20 },
      ext: { width: images[key].default.width, height: images[key].default.height }
    });
  }
};

/**
 * @description: 设置列宽
 * @author: flyer
 * @param {Object} widths 列宽配置
 * @param {Object} worksheet 当前excel
 */
const setColunmWidth = (widths = {}, worksheet) => {
  const keys = Object.keys(widths).map(item => Number(item));
  const maxKey = Math.max(...keys);
  const mergearr = [];
  for (let i = 0; i <= maxKey; i++) {
    mergearr.push({ key: `${i}`, width: (widths[`${i}`] || 0) / 7.5 });
  }
  worksheet.columns = mergearr;
};

/**
 * @description: 设置行宽
 * @author: flyer
 * @param {Object} widths 列宽配置
 * @param {Object} worksheet 当前excel
 */
const setRowWidth = (widths = {}, worksheet) => {
  const keys = Object.keys(widths).map(item => Number(item));
  const maxKey = Math.max(...keys);
  for (let i = 0; i <= maxKey; i++) {
    worksheet.getRow(i + 1).height = (widths[`${i}`] || 0) / 1.35;
  }
};

/**
 * @description: 设置合并单元格
 * @author: flyer
 * @param {Object} luckyMerge 合并单元格配置
 * @param {Object} worksheet 当前excel
 */
const setMerge = (luckyMerge = {}, worksheet) => {
  const mergearr = Object.values(luckyMerge);
  mergearr.forEach(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);
  });
};

/**
 * @description: 设置边框
 * @author: flyer
 * @param {Object} luckyBorderInfo 边框配置
 * @param {Object} worksheet 当前excel
 */
const setBorder = (luckyBorderInfo, worksheet) => {
  if (!Array.isArray(luckyBorderInfo)) return;
  luckyBorderInfo.forEach(elem => {
    // 设置传入的边框
    if (elem.rangeType === 'cell') {
      const border = borderConvertOld(elem.value);
      worksheet.getCell(elem.value.row_index + 1, elem.value.col_index + 1).border = border;
    }
  });
  luckyBorderInfo.forEach(elem => {
    // 设置在线修改的边框
    if (elem.rangeType === 'range') {
      borderConvert(elem, worksheet);
    }
  });
};

/**
 * @description: 设置单元格值和样式
 * @author: flyer
 * @param {Object} cellArr 单元格数据
 * @param {Object} worksheet 当前excel
 * @param {Object} hyperlink 所有链接
 */
const setStyleAndValue = (cellArr, worksheet, hyperlink) => {
  if (!Array.isArray(cellArr)) return;
  cellArr.forEach((row, rowid) => {
    row.every((cell, columnid) => {
      if (!cell) return true;
      const target = worksheet.getCell(rowid + 1, columnid + 1);
      const font = fontConvert(cell.ff, cell.fc, cell.bl, cell.it, cell.fs, cell.cl, cell.un);
      const alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr);
      let value;
      if (cell.f) {
        value = { formula: cell.f[0] === '=' ? cell.f.substring(1, cell.f.length) : cell.f, result: cell.v };
      } else {
        if (cell.ct) {
          if (!cell.ct.s) {
            value = cell.v;
            target.numFmt = cell.ct.fa;
          } else {
            value = cell.ct.s.map(item => item.v).join('');
          }
        } else {
          value = cell.m || cell.v;
        }
      }
      if (cell.bg) {
        const fill = fillConvert(cell.bg);
        target.fill = fill;
      }
      target.font = font;
      target.alignment = alignment;
      if (hyperlink && hyperlink[`${rowid}_${columnid}`]) {
        // 文本是链接的情况,luckysheet未支持
        target.value = { text: value, hyperlink: hyperlink[`${rowid}_${columnid}`].linkAddress, tooltip: hyperlink[`${rowid}_${columnid}`].linkTooltip };
      } else {
        target.value = value;
      }
      return true;
    });
  });
};

/**
 * @description: 设置单元格背景色
 * @author: flyer
 * @param {String} bg 颜色
 */
const fillConvert = bg => {
  const fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: bg.replace('#', '') }
  };
  return fill;
};

/**
 * @description: 设置单元格字体
 * @author: flyer
 * @param {String} bg 颜色
 */
const fontConvert = (ff = 0, fc = '#000000', bl = 0, it = 0, fs = 10, cl = 0, un = 0) => {
  // luckysheet：ff(样式), fc(颜色), bl(粗体), it(斜体), fs(大小), cl(删除线), un(下划线)
  const luckyToExcel = {
    num2bl: num => {
      return num !== 0;
    }
  };

  const font = {
    name: ff,
    family: 1,
    size: fs,
    color: { argb: fc.replace('#', '') },
    bold: luckyToExcel.num2bl(bl),
    italic: luckyToExcel.num2bl(it),
    underline: luckyToExcel.num2bl(un) && 'singleAccounting',
    strike: luckyToExcel.num2bl(cl)
  };

  return font;
};

/**
 * @description: 设置单元格风格
 * @author: flyer
 * @param {String} vt 垂直
 * @param {String} ht 水平
 * @param {String} tb 换行
 * @param {String} tr 旋转
 */
const alignmentConvert = (vt = 'default', ht = 'default', tb = 'default', tr = 'default') => {
  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
    }
  };

  const alignment = {
    vertical: luckyToExcel.vertical[vt],
    horizontal: luckyToExcel.horizontal[ht],
    wrapText: luckyToExcel.wrapText[tb],
    textRotation: luckyToExcel.textRotation[tr]
  };
  return alignment;
};

/**
 * @description: 设置在线编辑的excel风格样式
 * @author: flyer
 * @param {String} borderType 类型
 * @param {String} style 风格
 * @param {String} color 颜色
 */
const borderConvert = (elem, worksheet) => {
  const { borderType, style = 1, color = '#000' } = elem;
  if (!borderType || !elem.range || !elem.range.length) {
    return;
  }
  const styleArr = {
    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'
  };
  const rang = elem.range[0];
  const template = { style: styleArr[style], color: { argb: color.replace('#', '') } };
  switch (borderType) {
    case 'border-top':
      for (let i = rang.column[0]; i <= rang.column[1]; i++) {
        if (!worksheet.getCell(rang.row[0] + 1, i + 1).border) {
          worksheet.getCell(rang.row[0] + 1, i + 1).border = {};
        }
        worksheet.getCell(rang.row[0] + 1, i + 1).border.top = template;
      }
      break;
    case 'border-bottom':
      for (let i = rang.column[0]; i <= rang.column[1]; i++) {
        if (!worksheet.getCell(rang.row[1] + 2, i + 1).border) {
          worksheet.getCell(rang.row[1] + 2, i + 1).border = {};
        }
        worksheet.getCell(rang.row[1] + 2, i + 1).border.top = template;
      }
      break;
    case 'border-left':
      for (let j = rang.row[0]; j <= rang.row[1]; j++) {
        if (!worksheet.getCell(j + 1, rang.column[0] + 1).border) {
          worksheet.getCell(j + 1, rang.column[0] + 1).border = {};
        }
        worksheet.getCell(j + 1, rang.column[0] + 1).border.left = template;
      }
      break;
    case 'border-right':
      for (let j = rang.row[0]; j <= rang.row[1]; j++) {
        if (!worksheet.getCell(j + 1, rang.column[1] + 2).border) {
          worksheet.getCell(j + 1, rang.column[1] + 2).border = {};
        }
        worksheet.getCell(j + 1, rang.column[1] + 2).border.left = template;
      }
      break;
    case 'border-outside':
      for (let i = rang.column[0] + 1; i <= rang.column[1]; i++) {
        if (!worksheet.getCell(rang.row[0] + 1, i + 1).border) {
          worksheet.getCell(rang.row[0] + 1, i + 1).border = {};
        }
        worksheet.getCell(rang.row[0] + 1, i + 1).border.top = template;
      }
      for (let i = rang.column[0] + 1; i <= rang.column[1]; i++) {
        if (!worksheet.getCell(rang.row[1] + 2, i + 1).border) {
          worksheet.getCell(rang.row[1] + 2, i + 1).border = {};
        }
        worksheet.getCell(rang.row[1] + 2, i + 1).border.top = template;
      }
      for (let j = rang.row[0] + 1; j <= rang.row[1]; j++) {
        if (!worksheet.getCell(j + 1, rang.column[0] + 1).border) {
          worksheet.getCell(j + 1, rang.column[0] + 1).border = {};
        }
        worksheet.getCell(j + 1, rang.column[0] + 1).border.left = template;
      }
      for (let j = rang.row[0] + 1; j <= rang.row[1]; j++) {
        if (!worksheet.getCell(j + 1, rang.column[1] + 2).border) {
          worksheet.getCell(j + 1, rang.column[1] + 2).border = {};
        }
        worksheet.getCell(j + 1, rang.column[1] + 2).border.left = template;
      }
      if (!worksheet.getCell(rang.row[0] + 1, rang.column[0] + 1).border) {
        worksheet.getCell(rang.row[0] + 1, rang.column[0] + 1).border = {};
      }
      if (!worksheet.getCell(rang.row[0] + 1, rang.column[1] + 1).border) {
        worksheet.getCell(rang.row[0] + 1, rang.column[1] + 1).border = {};
      }
      if (!worksheet.getCell(rang.row[1] + 1, rang.column[0] + 1).border) {
        worksheet.getCell(rang.row[1] + 1, rang.column[0] + 1).border = {};
      }
      if (!worksheet.getCell(rang.row[1] + 1, rang.column[1] + 1).border) {
        worksheet.getCell(rang.row[1] + 1, rang.column[1] + 1).border = {};
      }
      if (!worksheet.getCell(rang.row[0] + 1, rang.column[1] + 2).border) {
        worksheet.getCell(rang.row[0] + 1, rang.column[1] + 2).border = {};
      }
      if (!worksheet.getCell(rang.row[1] + 2, rang.column[0] + 1).border) {
        worksheet.getCell(rang.row[1] + 2, rang.column[0] + 1).border = {};
      }
      if (!worksheet.getCell(rang.row[1] + 2, rang.column[1] + 1).border) {
        worksheet.getCell(rang.row[1] + 2, rang.column[1] + 1).border = {};
      }
      if (!worksheet.getCell(rang.row[1] + 1, rang.column[1] + 2).border) {
        worksheet.getCell(rang.row[1] + 1, rang.column[1] + 2).border = {};
      }
      worksheet.getCell(rang.row[0] + 1, rang.column[0] + 1).border.top = template;
      worksheet.getCell(rang.row[0] + 1, rang.column[0] + 1).border.left = template;
      worksheet.getCell(rang.row[0] + 1, rang.column[1] + 1).border.top = template;
      worksheet.getCell(rang.row[0] + 1, rang.column[1] + 2).border.left = template;
      worksheet.getCell(rang.row[1] + 2, rang.column[0] + 1).border.top = template;
      worksheet.getCell(rang.row[1] + 1, rang.column[0] + 1).border.left = template;
      worksheet.getCell(rang.row[1] + 2, rang.column[1] + 1).border.top = template;
      worksheet.getCell(rang.row[1] + 1, rang.column[1] + 2).border.left = template;
      break;
    case 'border-inside':
      for (let i = rang.column[0]; i <= rang.column[1]; i++) {
        for (let j = rang.row[0]; j <= rang.row[1]; j++) {
          // 最后一列并且不是最后一行并且不是一列的情况只渲染下方
          if (i === rang.column[1] && rang.column[1] - rang.column[0] !== 0 && j !== rang.row[1]) {
            if (!worksheet.getCell(j + 2, i + 1).border) {
              worksheet.getCell(j + 2, i + 1).border = {};
            }
            worksheet.getCell(j + 2, i + 1).border.top = template;
            // 最后一行并且不是最后一列并且不是一行的情况只渲染右方
          } else if (j === rang.row[1] && rang.row[1] - rang.row[0] !== 0 && i !== rang.column[1]) {
            if (!worksheet.getCell(j + 1, i + 2).border) {
              worksheet.getCell(j + 1, i + 2).border = {};
            }
            worksheet.getCell(j + 1, i + 2).border.left = template;
            // 最后一行最后一列只有一行有多列的情况只渲染下方
          } else if (i === rang.column[1] && j === rang.row[1] && rang.row[1] - rang.row[0] === 0 && rang.column[1] - rang.column[0] !== 0) {
            if (!worksheet.getCell(j + 2, i + 1).border) {
              worksheet.getCell(j + 2, i + 1).border = {};
            }
            worksheet.getCell(j + 2, i + 1).border.top = template;
            // 最后一行最后一列只有一列有多行的情况只渲染右方
          } else if (i === rang.column[1] && j === rang.row[1] && rang.row[1] - rang.row[0] !== 0 && rang.column[1] - rang.column[0] === 0) {
            if (!worksheet.getCell(j + 1, i + 2).border) {
              worksheet.getCell(j + 1, i + 2).border = {};
            }
            worksheet.getCell(j + 1, i + 2).border.left = template;
            // 最后一行最后一列有多行多列的情况不渲染
          } else if (i === rang.column[1] && j === rang.row[1] && rang.row[1] - rang.row[0] !== 0 && rang.column[1] - rang.column[0] !== 0) {
            console.log('不渲染');
          } else {
            if (!worksheet.getCell(j + 2, i + 1).border) {
              worksheet.getCell(j + 2, i + 1).border = {};
            }
            if (!worksheet.getCell(j + 1, i + 1).border) {
              worksheet.getCell(j + 1, i + 1).border = {};
            }
            worksheet.getCell(j + 2, i + 1).border.top = template;
            worksheet.getCell(j + 1, i + 2).border.left = template;
          }
        }
      }
      break;
    case 'border-all':
      for (let i = rang.column[0]; i <= rang.column[1]; i++) {
        for (let j = rang.row[0]; j <= rang.row[1]; j++) {
          worksheet.getCell(j + 1, i + 1).border = { top: template, left: template };
          if (!worksheet.getCell(j + 2, i + 1).border) {
            worksheet.getCell(j + 2, i + 1).border = {};
          }
          if (!worksheet.getCell(j + 1, i + 2).border) {
            worksheet.getCell(j + 1, i + 2).border = {};
          }
          worksheet.getCell(j + 2, i + 1).border.top = template;
          worksheet.getCell(j + 1, i + 2).border.left = template;
        }
      }
      break;
    case 'border-horizontal':
      for (let i = rang.column[0]; i <= rang.column[1]; i++) {
        for (let j = rang.row[0]; j <= rang.row[1]; j++) {
          if (j !== rang.row[1] || rang.row[1] - rang.row[0] === 0) {
            if (!worksheet.getCell(j + 2, i + 1).border) {
              worksheet.getCell(j + 2, i + 1).border = {};
            }
            worksheet.getCell(j + 2, i + 1).border.top = template;
          }
        }
      }
      break;
    case 'border-vertical':
      for (let i = rang.column[0]; i <= rang.column[1]; i++) {
        for (let j = rang.row[0]; j <= rang.row[1]; j++) {
          if (i !== rang.column[1] || rang.column[1] - rang.column[0] === 0) {
            if (!worksheet.getCell(j + 1, i + 2).border) {
              worksheet.getCell(j + 1, i + 2).border = {};
            }
            worksheet.getCell(j + 1, i + 2).border.left = template;
          }
        }
      }
      break;
    case 'border-none':
      for (let i = rang.column[0]; i <= rang.column[1]; i++) {
        for (let j = rang.row[0]; j <= rang.row[1]; j++) {
          worksheet.getCell(j + 1, i + 1).border = null;
        }
      }
      break;
    default:
      break;
  }
};

/**
 * @description: 设置传入的excelborder样式
 * @author: flyer
 * @param {Object} value 样式配置
 */
const borderConvertOld = value => {
  if (!value) {
    return {};
  }
  const luckyToExcel = {
    style: {
      0: 'none',
      1: 'thin',
      2: 'hair',
      3: 'dotted',
      4: 'dashDot',
      5: 'dashDot',
      6: 'dashDotDot',
      7: 'double',
      8: 'medium',
      9: 'mediumDashed',
      10: 'mediumDashDot',
      11: 'mediumDashDotDot',
      12: 'slantDashDot',
      13: 'thick'
    }
  };
  const border = {
    top: { style: luckyToExcel.style[value.t ? value.t.style : 0], color: { argb: (value.t ? value.t.color : '').replace('#', '') } },
    left: { style: luckyToExcel.style[value.l ? value.l.style : 0], color: { argb: (value.l ? value.l.color : '').replace('#', '') } },
    bottom: { style: luckyToExcel.style[value.b ? value.b.style : 0], color: { argb: (value.b ? value.b.color : '').replace('#', '') } },
    right: { style: luckyToExcel.style[value.r ? value.r.style : 0], color: { argb: (value.r ? value.r.color : '').replace('#', '') } }
  };
  return border;
};
export default exportExcel;

参考内容

网站文档
LuckySheet官网：https://mengshukeji.gitee.io/LuckysheetDocs/zh/guide/#demo
博客1：https://blog.csdn.net/m0_67391870/article/details/123304434
博客2：https://blog.csdn.net/qq_39157025/article/details/123848761
博客3：https://blog.csdn.net/u014632228/article/details/109738221

👍创作不易，如有错误请指正，感谢观看！记得点赞哦！👍文章来源地址https://www.toymoban.com/news/detail-466027.html

到了这里，关于【开源组件】- 表格处理 - Luckysheet的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！