完成excel文件预览和在线编辑

用到的插件

luckysheet，exceljs

1.安装插件

npm install luckyexcel

npm install exceljs

2.cdn引入资源

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

3.封装组件

<template>
  <div>
    <!-- 存放表格得容器 -->
    <div id="luckysheet" style="width:100%;height:100%;background-color: antiquewhite;position: fixed;"></div>
  </div>
</template>
<script setup>
import LuckyExcel from 'luckyexcel'
import Excel from 'exceljs'
import {nextTick} from 'vue'

const change = (file) => {
  LuckyExcel.transformExcelToLucky(file,
    function (exportJson, luckysheetfile) {//exportJson 转换后的数据
      nextTick(() => {
        window.luckysheet.destroy();
        // 将拿到的数据创建表格
        luckysheet.create({
          container: 'luckysheet', // luckysheet is the container id
          data: exportJson.sheets,
          title: exportJson.info.name,
          userInfo: exportJson.info.creator,
          lang: 'zh', // 设定表格语言
          myFolderUrl: window.location.href,
          showtoolbarConfig: {
            pivotTable: false,  //'数据透视表'
            // protection: false, // '工作表保护'
            print: false, // '打印'
            image: false, // 插入图片
          },
          showinfobar: false,
          options: {
            // 其他配置
            userImage: 'http://qzz-static.forwe.store/public-assets/pgy_kj_pic_logo.png?x-oss-process=image/resize,m_fill,w_72,h_72', // 头像url
            userName: 'Lucky', // 用户名
          }
        });
      })

    },
    function (error) {
      // handle error if any thrown
    })
}

// 导出excel
const exportExcel = async function (luckysheet) { // 参数为luckysheet.getluckysheetfile()获取的对象
  // 1.创建工作簿，可以为工作簿添加属性
  const workbook = new Excel.Workbook()
  // 2.创建表格，第二个参数可以配置创建什么样的工作表
  luckysheet.every(function (table) {
    if (table.data.length === 0) return true
    const worksheet = workbook.addWorksheet(table.name)
    // 3.设置单元格合并,设置单元格边框,设置单元格样式,设置值
    setStyleAndValue(table.data, worksheet)
    setMerge(table.config.merge, worksheet)
    setBorder(table.config.borderInfo, worksheet)
    return true
  })
  // 4.写入 buffer
  const buffer = await workbook.xlsx.writeBuffer()
  return buffer
}

// 保存文件
const onClickSaveFile = async () => {
    // 转换成buf之后将buf转为blob对象
  const buf = await exportExcel(luckysheet.getAllSheets())
  const blob = new Blob([buf]);

  return blob
}

defineExpose({
  change,
  onClickSaveFile
})
// 对数据做处理===============================================================================
// 设置单元格样式
const setStyleAndValue = function (cellArr, worksheet) {
  if (!Array.isArray(cellArr)) return;
  cellArr.forEach(function (row, rowid) {
    const dbrow = worksheet.getRow(rowid + 1);
    //设置单元格行高,默认乘以0.8倍
    dbrow.height = luckysheet.getRowHeight([rowid])[rowid] * 0.8;
    row.every(function (cell, columnid) {
      if (!cell) return true;
      if (rowid == 0) {
        const dobCol = worksheet.getColumn(columnid + 1);
        //设置单元格列宽除以8
        dobCol.width = luckysheet.getColumnWidth([columnid])[columnid] / 8;
      }
      let fill = fillConvert(cell.bg);
      let font = fontConvert(
        cell.ff || 'Times New Roman',
        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;
      var v = "";
      if (cell.ct && cell.ct.t == "inlineStr") {
        var s = cell.ct.s;
        s.forEach(function (val, num) {
          v += val.v;
        });
      } else {
        //导出后取显示值
        v = cell.m;
      }
      if (cell.f) {
        value = { formula: cell.f, result: v };
      } else {
        value = v;
      }
      let target = worksheet.getCell(rowid + 1, columnid + 1);
      //添加批注
      if (cell.ps) {
        let ps = cell.ps
        target.note = ps.value
      }
      //单元格填充
      target.fill = fill;
      //单元格字体
      target.font = font;
      target.alignment = alignment;
      target.value = value;
      return true;
    });
  });
};
//单元格背景填充色处理
const fillConvert = function (bg) {
  if (!bg) {
    return null;
    // return {
    // 	type: 'pattern',
    // 	pattern: 'solid',
    // 	fgColor:{argb:'#ffffff'.replace('#','')}
    // }
  }
  bg = bg.indexOf("rgb") > -1 ? rgb2hex(bg) : bg;
  let fill = {
    type: "pattern",
    pattern: "solid",
    fgColor: { argb: bg.replace("#", "") },
  };
  return fill;
};
//字体转换处理
const 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 ? false : true;
    },
  };
  // let color = fc ? "" : (fc + "").indexOf("rgb") > -1 ? util.rgb2hex(fc) : fc;
  // let color = fc ? fc : (fc + "").indexOf("rgb") > -1 ? util.rgb2hex(fc) : fc;

  let font = {
    name: 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;
};

//对齐转换
const 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
}


//设置合并数据
const setMerge = function (luckyMerge = {}, worksheet) {
  const mergearr = Object.values(luckyMerge)
  mergearr.forEach(function (elem) {
    // elem格式：{r: 0, c: 0, rs: 1, cs: 2}
    // 按开始行，开始列，结束行，结束列合并（相当于 K10:M12）
    worksheet.mergeCells(elem.r + 1, elem.c + 1,
      elem.r + elem.rs, elem.c + elem.cs)
  })
}

//重新设置边框
const setBorder = function (luckyBorderInfo, worksheet) {
  if (!Array.isArray(luckyBorderInfo)) return
  luckyBorderInfo.forEach(function (elem) {
    // 现在只兼容到borderType 为range的情况
    if (elem.rangeType === 'range') {
      let border = borderConvert(elem.borderType, elem.style, elem.color)
      let rang = elem.range[0]
      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)
      worksheet.getCell(row_index + 1, col_index + 1).border = border
    }
  })
}
</script>