vue 下使用 exceljs + x-spreadsheet 带样式导入Excel

云烟，不再年轻

已于 2025-01-05 11:42:57 修改

阅读量5.9k

点赞数 4

分类专栏： Vue 文章标签： excel javascript 开发语言

于 2022-11-20 18:11:10 首次发布

本文链接：https://blog.youkuaiyun.com/lee576/article/details/127951876

版权

Vue 专栏收录该内容

21 篇文章

订阅专栏

导入的样式包括字体，字号，列宽，合并单元格，【部分能识别】的背景色，文字颜色

【说明】导入的Excel 字体颜色和背景色只能识别【标准色】,别的如"主题颜色"，exceljs 解析出来不是颜色值

原Excel样式如下

导入到 x-data-spreadsheet 如下图

完整代码如下

<template>
  <div class="container">
    <div class="toolbar">
      <input
        type="file"
        @change="loadExcelFile"
        accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel"
      />
      <button @click="exportJson">导出JSON</button>
      <button @click="exportExcel">导出xlsx</button>
    </div>
    <!--web spreadsheet组件-->
    <div ref="sheetContainer" class="grid" id="x-spreadsheet-demo"></div>
  </div>
</template>

<script>
// 在spreadsheet.js中初始化json数据 https://blog.youkuaiyun.com/CBGCampus/article/details/125366246
// 带样式导出 https://blog.youkuaiyun.com/weixin_42302145/article/details/121476579
// 引入依赖包
import Spreadsheet from "x-data-spreadsheet";
import zhCN from 'x-data-spreadsheet/src/locale/zh-cn'
import _ from "lodash";
import * as XLSX from "xlsx";
import * as Excel from 'exceljs/dist/exceljs'
import * as tinycolor from "tinycolor2";
export default {
  name: "xspreadsheet-demo",
  data() {
    return {
      xs: null,
      jsondata: {
        type: "",
        label: "",
      },
    };
  },
  mounted() {
    this.init();
  },
  methods: {
    init() {
      //设置中文
      Spreadsheet.locale("zh-cn", zhCN);
      this.xs = new Spreadsheet("#x-spreadsheet-demo", {
        mode: "edit",
        showToolbar: true,
        showGrid: true,
        showContextmenu: true,
        showBottomBar: true,
        view: {
          height: () => this.$refs.sheetContainer.offsetHeight,
          width: () => this.$refs.sheetContainer.offsetWidth,
        },
        formats: [],
        fonts: [],
        formula: [],
        row: {
          len: 100,
          height: 25,
        },
        col: {
          len: 26,
          width: 100,
          indexWidth: 60,
          minWidth: 60,
        },
        style: {
          bgcolor: "#ffffff",
          align: "left",
          valign: "middle",
          textwrap: false,
          textDecoration: "normal",
          strikethrough: false,
          color: "#0a0a0a",
          font: {
            name: "Helvetica",
            size: 10,
            bold: false,
            italic: false,
          }
        },
      })
        .loadData([])
        .change((cdata) => {
          // console.log(cdata);
          console.log(">>>", this.xs.getData());
        });

      this.xs
        .on("cell-selected", (cell, ri, ci) => {
          console.log("cell:", cell, ", ri:", ri, ", ci:", ci);
        })
        .on("cell-edited", (text, ri, ci) => {
          console.log("text:", text, ", ri: ", ri, ", ci:", ci);
        });

      setTimeout(() => {
        // xs.loadData([{ rows }]);
        // xs.cellText(14, 3, 'cell-text').reRender();
        // console.log('cell(8, 8):', this.xs.cell(8, 8));
        // console.log('cellStyle(8, 8):', this.xs.cellStyle(8, 8));
      }, 5000);
    },
    transRgba(rgba) {
          let result = '';
          let reg = /\(.*\)/;  //字符串匹配括号内的子串
          let arr = []
          result = reg.exec(rgba)[0];  // 截取'(255,255,255,0.6)'
          result = result.substr(1, result.length - 2); //截取十进制的'255,255,255,0.6'
          arr = result.split(','); //字符串切割 ['255','255','255','0.6']
          for (let i = 0; i < arr.length; i++) {
              arr[i] = parseFloat(arr[i]); //字符串类型转为浮点数类型
              if (i == arr.length - 1) {  //对于最后一个透明度数据，需要先将0-1的数值*255
                  arr[i] = arr[i] * 255;
              }
  
              arr[i] = trans10to16(arr[i]);
          }
          return arr.join('');
    },
    // 向x-spreadsheet 带样式导入Excel
      loadExcelFile(e) {
      const wb = new Excel.Workbook();
      const reader = new FileReader()
      reader.readAsArrayBuffer(e.target.files[0])
      reader.onload = () => {
        const buffer = reader.result;
        // 微软的 Excel ColorIndex 一个索引数字对应一个颜色
        const indexedColors = [
          '000000',
          'FFFFFF',
          'FF0000',
          '00FF00',
          '0000FF',
          'FFFF00',
          'FF00FF',
          '00FFFF',
          '000000',
          'FFFFFF',
          'FF0000',
          '00FF00',
          '0000FF',
          'FFFF00',
          'FF00FF',
          '00FFFF',
          '800000',
          '008000',
          '000080',
          '808000',
          '800080',
          '008080',
          'C0C0C0',
          '808080',
          '9999FF',
          '993366',
          'FFFFCC',
          'CCFFFF',
          '660066',
          'FF8080',
          '0066CC',
          'CCCCFF',
          '000080',
          'FF00FF',
          'FFFF00',
          '00FFFF',
          '800080',
          '800000',
          '008080',
          '0000FF',
          '00CCFF',
          'CCFFFF',
          'CCFFCC',
          'FFFF99',
          '99CCFF',
          'FF99CC',
          'CC99FF',
          'FFCC99',
          '3366FF',
          '33CCCC',
          '99CC00',
          'FFCC00',
          'FF9900',
          'FF6600',
          '666699',
          '969696',
          '003366',
          '339966',
          '003300',
          '333300',
          '993300',
          '993366',
          '333399',
          '333333',
        ];
        wb.xlsx.load(buffer).then(workbook => {
          let workbookData = []
          console.log(workbook)
          workbook.eachSheet((sheet, sheetIndex) => {
            // 构造x-data-spreadsheet 的 sheet 数据源结构
            let sheetData = { name: sheet.name,styles : [], rows: {}, merges:[] }
            // 收集合并单元格信息
            let mergeAddressData = []
            for(let mergeRange in sheet._merges) {
              sheetData.merges.push(sheet._merges[mergeRange].shortRange)
              let mergeAddress = {}
              // 合并单元格起始地址
              mergeAddress.startAddress = sheet._merges[mergeRange].tl
              // 合并单元格终止地址
              mergeAddress.endAddress = sheet._merges[mergeRange].br
              // Y轴方向跨度
              mergeAddress.YRange = sheet._merges[mergeRange].model.bottom - sheet._merges[mergeRange].model.top
              // X轴方向跨度
              mergeAddress.XRange = sheet._merges[mergeRange].model.right - sheet._merges[mergeRange].model.left
              mergeAddressData.push(mergeAddress)
            }
            sheetData.cols = {}
            for(let i = 0;i < sheet.columns.length; i++)
            {
              sheetData.cols[i.toString()] = {}
              if(sheet.columns[i].width) {
                // 不知道为什么从 exceljs 读取的宽度显示到 x-data-spreadsheet 特别小, 这里乘以8
                sheetData.cols[i.toString()].width = sheet.columns[i].width * 8
              } else {
                // 默认列宽
                sheetData.cols[i.toString()].width = 100
              }
            }

            // 遍历行
            sheet.eachRow((row, rowIndex) => {
              sheetData.rows[(rowIndex - 1).toString()] = { cells: {} }         
              //includeEmpty = false 不包含空白单元格
              row.eachCell({ includeEmpty: true }, function(cell, colNumber) {
                let cellText = ''
                if(cell.value && cell.value.result) {
                  // Excel 单元格有公式
                  cellText = cell.value.result
                } else if(cell.value && cell.value.richText) {
                  // Excel 单元格是多行文本
                  for(let text in cell.value.richText) {
                    // 多行文本做累加
                    cellText += cell.value.richText[text].text
                  }
                }
                else {
                  // Excel 单元格无公式
                  cellText = cell.value
                }
                
                //解析单元格,包含样式
                //*********************单元格存在背景色******************************
                // 单元格存在背景色
                let backGroundColor = null
                if(cell.style.fill && cell.style.fill.fgColor && cell.style.fill.fgColor.argb) {
                  // 8位字符颜色先转rgb再转16进制颜色
                  backGroundColor = ((val) => {
                    val = val.trim().toLowerCase();  //去掉前后空格
                    let color = {};
                    try {
                        let argb = /^#?([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})$/i.exec(val);
                        color.r = parseInt(argb[2], 16);
                        color.g = parseInt(argb[3], 16);
                        color.b = parseInt(argb[4], 16);
                        color.a = parseInt(argb[1], 16) / 255;
                        return tinycolor(`rgba(${color.r}, ${color.g}, ${color.b}, ${color.a})`).toHexString()
                    } catch (e) {
                      console.log(e)
                    }
                  })(cell.style.fill.fgColor.argb)
                } 

                if(backGroundColor) {
                  cell.style.bgcolor = backGroundColor
                }
                //*************************************************************************** */
                
                //*********************字体存在背景色******************************
                // 字体颜色
                let fontColor = null
                if(cell.style.font && cell.style.font.color && cell.style.font.color.argb) {
                  // 8位字符颜色先转rgb再转16进制颜色
                  fontColor = ((val) => {
                    val = val.trim().toLowerCase();  //去掉前后空格
                    let color = {};
                    try {
                        let argb = /^#?([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})$/i.exec(val)
                        color.r = parseInt(argb[2], 16);
                        color.g = parseInt(argb[3], 16);
                        color.b = parseInt(argb[4], 16);
                        color.a = parseInt(argb[1], 16) / 255;
                        return tinycolor(`rgba(${color.r}, ${color.g}, ${color.b}, ${color.a})`).toHexString()
                    } catch (e) {
                      console.log(e)
                    }
                  })(cell.style.font.color.argb)
                }
                if(fontColor) {
                  //console.log(fontColor)
                  cell.style.color = fontColor
                }
                //************************************************************************ */

                // exceljs 对齐的格式转成 x-date-spreedsheet 能识别的对齐格式 
                if(cell.style.alignment && cell.style.alignment.horizontal) {
                  cell.style.align = cell.style.alignment.horizontal
                  cell.style.valign = cell.style.alignment.vertical
                }

                //处理合并单元格
                let mergeAddress = _.find(mergeAddressData, function(o) { return o.startAddress == cell._address })
                if(mergeAddress) 
                {
                  // 遍历的单元格属于合并单元格
                  if(cell.master.address != mergeAddress.startAddress){
                    // 不是合并单元格中的第一个单元格不需要计入数据源
                    return
                  }
                  // 说明是合并单元格区域的起始单元格
                  sheetData.rows[(rowIndex - 1).toString()].cells[(colNumber - 1).toString()] = { text: cellText, style: 0, merge: [mergeAddress.YRange, mergeAddress.XRange] }
                  sheetData.styles.push(cell.style)
                  //对应的style存放序号
                  sheetData.rows[(rowIndex - 1).toString()].cells[(colNumber - 1).toString()].style = sheetData.styles.length - 1
                }
                else {
                  // 非合并单元格
                  sheetData.rows[(rowIndex - 1).toString()].cells[(colNumber - 1).toString()] = { text: cellText, style: 0 }
                  //解析单元格,包含样式
                  sheetData.styles.push(cell.style)
                  //对应的style存放序号
                  sheetData.rows[(rowIndex - 1).toString()].cells[(colNumber - 1).toString()].style = sheetData.styles.length - 1
                }
              });
            })
            workbookData.push(sheetData)
          })
          this.xs.loadData(workbookData);
        })
      }
    },
    // 导出excel
    exportExcel() {
      let new_wb = XLSX.utils.book_new();
      this.xs.getData().forEach(function (xws) {
        let aoa = [[]];
        let rowobj = xws.rows;
        for (let ri = 0; ri < rowobj.len; ++ri) {
          let row = rowobj[ri];
          if (!row) continue;
          aoa[ri] = [];
          Object.keys(row.cells).forEach(function (k) {
            let idx = +k;
            if (isNaN(idx)) return;
            aoa[ri][idx] = row.cells[k].text;
          });
        }
        let ws = XLSX.utils.aoa_to_sheet(aoa);
        XLSX.utils.book_append_sheet(new_wb, ws, xws.name);
      });
      XLSX.writeFile(new_wb, "调配单变量配置.xlsx");
    },
    // 导出为 JSON
    exportJson() {
      let sheetsData = this.xs.getData();
      let rows = Object.entries(sheetsData[0].rows);
      let objectProperties = [
        "Index",
        "OrderIndex",
        "OrderNo",
        "ProductName",
        "OrderStatus",
      ];
      let jsonData = [];
      // 遍历数据，跳过第一行表头
      for (let i = 1; i < rows.length; i++) {
        if (rows[i] && rows[i][1] && rows[i][1].cells) {
          let row = Object.entries(rows[i][1].cells);
          // 构造行对象
          let JsonRow = {
            Index: null,
            OrderIndex: null,
            OrderNo: null,
            ProductName: null,
            OrderStatus: null,
          };
          for (let k = 0; k < row.length; k++) {
            let cells = row[k];
            JsonRow[objectProperties[k]] = cells[1].text;
          }
          jsonData.push(JsonRow);
        }
      }
      console.log(jsonData);
    },
    stox(wb) {
      var out = [];
      wb.SheetNames.forEach(function (name) {
        var o = { name: name, rows: {} };
        var ws = wb.Sheets[name];
        var aoa = XLSX.utils.sheet_to_json(ws, { raw: false, header: 1 });
        aoa.forEach(function (r, i) {
          var cells = {};
          r.forEach(function (c, j) {
            cells[j] = { text: c };
          });
          o.rows[i] = { cells: cells };
        });
        out.push(o);
      });
      return out;
    },
    fixData(data) {
      var o = "",
        l = 0,
        w = 10240;
      for (; l < data.byteLength / w; ++l)
        o += String.fromCharCode.apply(
          null,
          new Uint8Array(data.slice(l * w, l * w + w))
        );
      o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)));
      return o;
    },
  },
};
</script>
<style lang="less" scoped>
.container {
  width: 100%;
  height: 100%;
  display: flex;
  flex-direction: column;
  .toolbar {
    width: 100%;
    height: 50px;
  }
  .grid {
    width: 100%;
    height: calc(100% - 80px);
  }
  /deep/ .x-spreadsheet-toolbar {
    padding: 0px;
    width: calc(100% - 2px) !important;
  }
}
</style>

不少人说这报错，那报错的，我整个代码都好几年前的了，我也不想再去看，所幸源码还在，我把package.json 贴出来，你们自己研究吧

{
	"name": "vue_study",
	"version": "20230905.134743",
	"description": "A Vue.js project",
	"author": "",
	"private": true,
	"scripts": {
		"dev": "webpack-dev-server --inline --progress --config build/webpack.dev.conf.js",
		"start": "npm run dev",
		"lint": "eslint --ext .js,.vue src",
		"build": "node modifyVersion.js && node build/build.js",
		"demo": "node modifyVersion.js && node build/build.demo.js",
		"ali": "node modifyVersion.js && node build/build.ali.js",
		"local": "node modifyVersion.js && node build/build.local.js"
	},
	"dependencies": {
		"-": "0.0.1",
		"@fullcalendar/core": "^6.0.2",
		"@fullcalendar/daygrid": "^6.0.2",
		"@fullcalendar/interaction": "^6.0.2",
		"@fullcalendar/timegrid": "^6.0.2",
		"@fullcalendar/vue": "^6.0.2",
		"@jiaminghi/data-view": "^2.10.0",
		"@riophae/vue-treeselect": "^0.4.0",
		"axios": "^0.21.1",
		"cancel_axios_request": "^1.0.5",
		"default-passive-events": "^2.0.0",
		"echarts": "^5.3.3",
		"echarts-gl": "^2.0.9",
		"element-ui": "^2.15.13",
		"exceljs": "^4.3.0",
		"font-awesome": "^4.7.0",
		"fullcalendar": "^6.0.2",
		"highcharts": "^11.0.1",
		"interactjs": "^1.10.11",
		"jquery": "^3.6.0",
		"js-guid": "^1.0.0",
		"js-md5": "^0.7.3",
		"jsplumb": "^2.15.6",
		"leader-line": "^1.0.7",
		"leader-line-vue": "^2.1.1",
		"less": "^4.1.2",
		"linq-es2015": "^2.5.1",
		"lodash": "^4.17.21",
		"mockjs": "^1.1.0",
		"moment": "^2.29.1",
		"moment-recur": "^1.0.7",
		"nzh": "^1.0.8",
		"print-js": "^1.6.0",
		"print.js": "^1.0.18",
		"qrious": "^4.0.2",
		"qs": "^6.10.1",
		"save": "^2.5.0",
		"side-channel": "^1.0.4",
		"signalr": "^2.4.2",
		"snapsvg": "^0.5.1",
		"snapsvg-cjs": "^0.0.6",
		"sortablejs": "^1.15.0",
		"tinycolor2": "^1.4.2",
		"vue": "^2.6.0",
		"vue-axios": "^3.2.4",
		"vue-froala-wysiwyg": "^4.0.8",
		"vue-giant-tree": "^0.1.5",
		"vue-grid-layout": "^2.3.7",
		"vue-interactjs": "^0.1.10",
		"vue-resize": "^1.0.1",
		"vue-resize-directive": "^1.2.0",
		"vue-resize-observer": "^1.0.37",
		"vue-router": "^3.0.7",
		"vue-text-format": "^1.2.6",
		"vuex": "^3.6.2",
		"webpack-cli": "^4.9.2",
		"x-data-spreadsheet": "^1.1.9",
		"xlsx": "^0.18.5"
	},
	"devDependencies": {
		"autoprefixer": "^7.1.2",
		"babel-core": "^6.22.1",
		"babel-eslint": "^8.2.1",
		"babel-helper-vue-jsx-merge-props": "^2.0.3",
		"babel-loader": "^7.1.1",
		"babel-plugin-syntax-jsx": "^6.18.0",
		"babel-plugin-transform-runtime": "^6.22.0",
		"babel-plugin-transform-vue-jsx": "^3.5.0",
		"babel-preset-env": "^1.3.2",
		"babel-preset-stage-2": "^6.22.0",
		"chalk": "^2.0.1",
		"copy-webpack-plugin": "^4.0.1",
		"css-loader": "^0.28.0",
		"eslint": "^4.15.0",
		"eslint-config-standard": "^10.2.1",
		"eslint-friendly-formatter": "^3.0.0",
		"eslint-loader": "^1.7.1",
		"eslint-plugin-import": "^2.7.0",
		"eslint-plugin-node": "^5.2.0",
		"eslint-plugin-promise": "^3.4.0",
		"eslint-plugin-standard": "^3.0.1",
		"eslint-plugin-vue": "^4.0.0",
		"extract-text-webpack-plugin": "^3.0.0",
		"file-loader": "^1.1.4",
		"friendly-errors-webpack-plugin": "^1.6.1",
		"html-webpack-plugin": "^2.30.1",
		"less-loader": "^5.0.0",
		"node-notifier": "^5.1.2",
		"optimize-css-assets-webpack-plugin": "^3.2.0",
		"ora": "^1.2.0",
		"portfinder": "^1.0.13",
		"postcss-import": "^11.0.0",
		"postcss-loader": "^2.0.8",
		"postcss-url": "^7.2.1",
		"rimraf": "^2.6.0",
		"semver": "^5.3.0",
		"shelljs": "^0.7.6",
		"uglifyjs-webpack-plugin": "^1.1.1",
		"url-loader": "^0.5.8",
		"vue-loader": "^13.3.0",
		"vue-style-loader": "^3.0.1",
		"vue-template-compiler": "^2.6.0",
		"webpack": "^3.6.0",
		"webpack-bundle-analyzer": "^2.9.0",
		"webpack-dev-server": "^2.11.5",
		"webpack-merge": "^4.1.0"
	},
	"engines": {
		"node": ">= 6.0.0",
		"npm": ">= 3.0.0"
	},
	"browserslist": [
		"> 1%",
		"last 2 versions",
		"not ie <= 8"
	],
	"lastBuildTime": "2023-09-05 13:47:43"
}