基于Vue2实现LuckSheet在线预览编辑Excel表格

长大没那么好

已于 2025-01-22 16:10:40 修改

阅读量1.7k

点赞数 7

文章标签： excel vue

于 2025-01-22 15:38:20 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_69041698/article/details/145303176

版权

1. luckyExcel读取.xlsx文件

2. 根据xlsx文件链接加载

3. 获取所有工作表配置，格式同工作表配置

一、介绍

LuckSheet是一款基于Web的在线表格组件，一款纯前端类似excel的在线表格，功能强大、配置简单、完全开源结合Vue2、Vue3可以实现数据的动态展示和编辑，为用户提供良好的用户体验。

二、使用步骤

1. 引入依赖

CND引入

 <!-- luckysheet  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>
    <script src="https://cdn.jsdelivr.net/npm/luckyexcel/dist/luckyexcel.umd.js"></script>

本地引入

克隆Luckysheet源码到本地

git clone https://github.com/dream-num/Luckysheet.git

安装依赖

npm install
npm install gulp -g

打包

npm run build

引用

npm run build 后 dist 文件夹下的所有文件复制到vue项目的public目录下，然后在index.html文件中通过相对路径引入：

    <!-- luckysheet 本地引用 -->
   <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>

2.指定一个表格容器

    <div id="luckysheet" class="luckysheet-wrap"></div>

3. 创建一个表格

   <script>

    import LuckyExcel from "luckyexcel";
    // 下载
    import { exportExcel } from "./export";


  methods: {

     /** 初始化 luckysheet **/
    handleInitLuckysheet(data = null) {
      window.luckysheet && window.luckysheet.destroy(); // 先销毁上一个

      //创建并 配置 luckysheet
      window.luckysheet.create({
        container: "luckysheet", // 设定DOM容器的id
        showinfobar: false, //是否显示顶部信息栏
        lang: "zh", // 设定表格语言
        data, // 表格数据

        hook: {
          // 数据改变后
          updated: async (operate) => {
            // console.info(operate, "更新");
            // console.log(luckysheet.getAllSheets(), "获取");

          },
        },

        // title: exportJson.info.name,
        // userInfo: exportJson.info.name.creator,
      });
    },
  },
</script>

4. 整体结构

{
	"name": "Cell", //工作表名称
	"color": "", //工作表颜色
	"index": "0", //工作表索引
	"status": "1", //激活状态
	"order": "0", //工作表的顺序
	"hide": 0,//是否隐藏
	"row": 36, //行数
	"column": 18, //列数
	"config": {
		"merge":{}, //合并单元格
		"rowlen":{}, //表格行高
		"columnlen":{}, //表格列宽
		"rowhidden":{}, //隐藏行
		"colhidden":{}, //隐藏列
		"borderInfo":{}, //边框
	},
	"celldata": [], //初始化使用的单元格数据
	"data": [], //更新和存储使用的单元格数据
	"scrollLeft": 0, //左右滚动条位置
	"scrollTop": 315, //上下滚动条位置
	"luckysheet_select_save": [], //选中的区域
	"luckysheet_conditionformat_save": {},//条件格式
	"calcChain": [],//公式链
	"isPivotTable":false,//是否数据透视表
	"pivotTable":{},//数据透视表设置
	"filter_select": {},//筛选范围
	"filter": null,//筛选配置
	"luckysheet_alternateformat_save": [], //交替颜色
	"luckysheet_alternateformat_save_modelCustom": [], //自定义交替颜色	
	"freezen": {}, //冻结行列
	"chart": [], //图表配置
	"visibledatarow": [], //所有行的位置
	"visibledatacolumn": [], //所有列的位置
	"ch_width": 2322, //工作表区域的宽度
	"rh_height": 949, //工作表区域的高度
	"load": "1", //已加载过此sheet的标识
}

三、常用API

1. luckyExcel读取.xlsx文件

excelUpload(file) {
	  //此处的file为上传文件后返回的文件信息
      LuckyExcel.transformExcelToLucky(file, (exportJson, luckysheetfile) => {

          // 读取文件失败时
        if (exportJson.sheets === null || exportJson.sheets.length === 0) {
          this.$message.error('无法读取excel文件的内容！')
          return
        }
 
         this.handleInitLuckysheet(exportJson.sheets); //  创建 luckysheet
      })
    },

2. 根据xlsx文件链接加载

 /** 根据文件链接加载luckysheet **/
    handleByFileUrlLuckysheet(fileUrl,fileName) {
      LuckyExcel.transformExcelToLuckyByUrl(
        fileUrl, // xlsx文件链接
        fileName, // 文件名
        (exportJson, luckysheetfile) => {
          if (exportJson.sheets == null || exportJson.sheets.length == 0) {
            this.$message.error("读取内容失败.....");
            return;
          }

          this.handleInitLuckysheet(exportJson.sheets); // 创建 luckysheet
        }
      );
    },

3. 获取所有工作表配置，格式同工作表配置

所以此API适用于，手动操作配置完一个表格后，将所有工作表信息取出来自行保存，再用于其他地方的表格创建。如果想得到包括工作簿配置在内的所有工作簿数据，推荐使用 toJson，并且可以直接用于初始化Luckysheet.(luckysheet.create(options)初始化工作簿时的参数options使用)

luckysheet.getAllSheets() // 所有工作表

luckysheet.getAllSheets()[0] // 取得第一个工作表的所有基本信息

4. 退出编辑模式

鼠标双击单元格后，会进入单元格编辑模式，编辑完成后，当鼠标再次点击别的地方输入框失焦的时候，则会退出编辑模, 动态保存luckysheet数据,记得先退出编辑模式.

  luckysheet.exitEditMode(); // 退出编辑模式

四、导出xlsx文件

// 依赖版本
"exceljs": "^4.3.0",
"file-saver": "2.0.5",

export.js

import FileSaver from "file-saver";
const Excel = require("exceljs");

var 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
    );
  });
};

var setBorder = function (luckyBorderInfo, worksheet) {
  if (!Array.isArray(luckyBorderInfo)) return;
  // console.log('luckyBorderInfo', luckyBorderInfo)
  luckyBorderInfo.forEach(function (elem) {
    // 现在只兼容到borderType 为range的情况

    if (elem.rangeType === "range") {
      const border = borderConvert(elem.borderType, elem.style, elem.color);
      const rang = elem.range[0];
      // console.log('range', rang)
      const row = rang.row;
      const 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") {
      const { col_index, row_index } = elem.value;

      const borderData = Object.assign({}, elem.value);
      delete borderData.col_index;
      delete borderData.row_index;

      const border = addborderToCell(borderData, row_index, col_index);
      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
  });
};

/**
 * 转换日期为Excel序列号
 * @param {string|Date} dateValue - 日期值
 * @returns {number} Excel序列号
 */
const convertToExcelDate = (dateValue) => {
  if (!dateValue) return null;

  let date;
  if (dateValue instanceof Date) {
    date = dateValue;
  } else if (typeof dateValue === "string") {
    // 处理日期字符串，例如 "2025/1/20"
    date = new Date(dateValue);
  } else {
    return dateValue; // 如果不是日期则返回原值
  }

  if (isNaN(date.getTime())) {
    return dateValue; // 如果转换失败则返回原值
  }

  // Excel的起始日期是1900年1月1日
  const start = new Date(1900, 0, 1);
  const diff = date - start;
  const oneDay = 1000 * 60 * 60 * 24;
  const excelDate = diff / oneDay + 1;

  return excelDate;
};

/**
 * 判断是否为纯数字
 * @param {string} value - 要判断的值
 * @returns {boolean} 是否为纯数字
 */
const isNumeric = (value) => {
  if (typeof value !== "string") return false;
  return !isNaN(value) && !isNaN(parseFloat(value));
}

/**
 * 设置样式和值
 */
const setStyleAndValue = (cellArr, worksheet) => {
  if (!Array.isArray(cellArr)) return;

  cellArr.forEach((row, rowid) => {
    row.forEach((cell, columnid) => {
      if (!cell) return;

      const fill = fillConvert(cell.bg);

      const font = fontConvert(
        cell.ff,
        cell.fc,
        cell.bl,
        cell.it,
        cell.fs,
        cell.cl,
        cell.ul
      );
      const alignment = alignmentConvert(cell.vt, cell.ht, cell.tb, cell.tr);

      let value = "";
      if (cell.f) {
        // 尝试新的公式处理方式
        const formulaStr = cell.f.trim();
        value = { 
          formula: formulaStr.startsWith('=') ? formulaStr.substring(1) : formulaStr,
          result: cell.v,
          // date1904: false  // 添加这个属性试试
        }
      } else if (!cell.v && cell.ct && cell.ct.s) {
        value = cell.ct.s.reduce((acc, curr) => acc + curr.v, "");
      } else {
        // 处理时间格式
        if (
          cell.ct &&
          cell.ct.fa &&
          (cell.ct.fa.includes("m") ||
            cell.ct.fa.includes("d") ||
            cell.ct.fa.includes("y"))
        ) {
          // 如果是日期字符串，转换为Excel序列号
          const excelDate = convertToExcelDate(cell.v);
          if (excelDate !== null) {
            value = excelDate;
            worksheet.getCell(rowid + 1, columnid + 1).numFmt = 'yyyy-mm-dd';
          } else {
            value = cell.v;
          }
        } else {
          value = cell.v;
        }
      }

      if (value !== null) {
        // 只在 value 不为 null 时设置单元格值
        const letter = createCellPos(columnid);
        const target = worksheet.getCell(letter + (rowid + 1));

        if (Object.keys(fill).length > 0) {
          target.fill = fill;
        }
        target.font = font;
        target.alignment = alignment;
        if (value !== undefined && value !== null) {
      
          // 判断是否是数字 设置为数字格式
          target.value = isNumeric(value) ? value * 1 : value;
          // target.value = value
        }
      }
    });
  });
};

var setImages = function (imagesArr, worksheet, workbook) {
  if (typeof imagesArr !== "object") return;
  for (const key in imagesArr) {
    // console.log(imagesArr[key]);
    // 通过 base64  将图像添加到工作簿
    const myBase64Image = imagesArr[key].src;
    // 开始行 开始列 结束行 结束列
    const start = { col: imagesArr[key].fromCol, row: imagesArr[key].fromRow };
    const end = { col: imagesArr[key].toCol, row: imagesArr[key].toRow };
    const imageId = workbook.addImage({
      base64: myBase64Image,
      extension: "png",
    });
    worksheet.addImage(imageId, {
      tl: start,
      br: end,
      editAs: "oneCell",
    });
  }
};

var fillConvert = function (bg) {
  if (!bg) {
    return {};
  }
  // const bgc = bg.replace('#', '')
  const 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
) {
  // 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;
    },
  };
  // 出现Bug，导入的时候ff为luckyToExcel的val

  let colorValue = fc;
  if (fc.indexOf('rgb') !== -1) {
    // 处理 rgb 格式
    const rgb = fc.match(/\d+/g);
    if (rgb && rgb.length >= 3) {
      const [r, g, b] = rgb;
      colorValue = ((r << 16) | (g << 8) | b).toString(16).padStart(6, '0');
    }
  } else {
    // 处理十六进制格式
    colorValue = fc.replace("#", "");
  }

  const font = {
    name: typeof ff === "number" ? luckyToExcel[ff] : ff,
    family: 1,
    size: fs,
    color: { argb: 'FF' + colorValue },
    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,
    },
  };

  const 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",
    },
  };
  const template = {
    style: luckyToExcel.style[style],
    color: { argb: color.replace("#", "") },
  };
  const 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, rowIndex, colIndex) {
  const 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) {
  const ordA = "A".charCodeAt(0);

  const ordZ = "Z".charCodeAt(0);
  const len = ordZ - ordA + 1;
  let s = "";
  while (n >= 0) {
    s = String.fromCharCode((n % len) + ordA) + s;

    n = Math.floor(n / len) - 1;
  }
  return s;
}

/**
 * 列宽
 * @param columnWidth
 * @param worksheet
 */
var setColumnWidth = function (columnWidth, worksheet) {
  for (let key in columnWidth) {
    worksheet.getColumn(parseInt(key) + 1).width = columnWidth[key] / 7.5;
  }
};

/**
 * 行高
 * @param rowHeight
 * @param worksheet
 * @param excelType
 */
var setRowHeight = function (rowHeight, worksheet) {
  for (let key in rowHeight) {
    worksheet.getRow(parseInt(key) + 1).height = rowHeight[key] * 0.75;
  }
};

export var exportExcel = async function (luckysheet, value, isDown = false) {
  const workbook = new Excel.Workbook();

  luckysheet.forEach(function (table) {
    // 深拷贝数据以避免修改原始数据
    const tableData = JSON.parse(JSON.stringify(table.data));
    
    // 移除预处理公式的代码，让 Excel 自己处理公式格式
    if (tableData.length === 0) return true;
    
    const worksheet = workbook.addWorksheet(table.name);
    const borderInfo = (table.config && table.config.borderInfo) || {};
    const columnWidth = (table.config && table.config.columnlen) || {}; //列宽
    const rowHeight = (table.config && table.config.rowlen) || {}; //行高

    // 3. 设置单元格合并, 边框, 样式, 值, 图片
    setColumnWidth(columnWidth, worksheet);
    //行高设置50导出后在ms-excel中打开显示25，在wps-excel中打开显示50这个bug不会修复
    setRowHeight(rowHeight, worksheet);

    setMerge((table.config && table.config.merge) || {}, worksheet);
    setBorder(borderInfo, worksheet);
    setStyleAndValue(tableData, worksheet);
    setImages(table.images, worksheet, workbook);

    return true;
  });

  let data = await workbook.xlsx.writeBuffer();

  const blob = new Blob([data], {
    type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8",
  });

  // 创建File对象
  const file = new File([blob], `${value}.xlsx`, {
    type: blob.type,
    lastModified: new Date().getTime(),
  });

  if (isDown) return file;

  console.log("导出成功！");
  FileSaver.saveAs(blob, `${value}.xlsx`);

  return workbook.xlsx.writeBuffer();
};

使用

<script>

import { exportExcel } from "./export";


    /** 下载 **/
    downloadExcel(name="名字") {
      exportExcel(luckysheet.getAllSheets(),name);
    },

</script>