实现纯前端数据导出excel

1、xlsx地址

https://www.npmjs.com/package/xlsx

2、安装

npm install xlsx

3、使用

<script>

import * as XLSX from 'xlsx';

export default {
  name: 'HomeView',
  methods: {
    downFile() {
      let tableHeader = [
        {"props":"87","label":"一级"},
        {"props":"88","label":"二级"},
        {"props":"89","label":"三级"}
      ]
      let tableData = [
        {"87": "好", "88": "很好", "89": "非常好" },
        {"87": "可", "88": "很可", "89": "非常可" },
      ]
      let data = [];
      tableData.forEach(el =>{
        let obj = {}
        tableHeader.forEach(item => {
          for (const key in el) {
            if(item.props === key) {
              obj[[item.label]] = el[key]
            }
          }
        })
        data.push(obj)
      })
      // 从JS对象数组创建工作表
      const ws = XLSX.utils.json_to_sheet(data);
      // 列属性对象的数组，设置每列的列宽，10代表10个字符，注意中文占2个字符
      ws['!cols'] = [
        { wch: 10 },
        { wch: 20 },
        { wch: 30 }
      ] 
      // 创建一个新的工作簿
      const wb = XLSX.utils.book_new();
      // 将工作表附加到工作簿 (工作簿, 工作表, sheet命名)
      XLSX.utils.book_append_sheet(wb, ws, '数据详情');
      // 尝试写入和保存 XLSX 文件(工作簿, xlsx文件名称)
      XLSX.writeFile(wb, '列表详情.xlsx');
    }
  },
}
</script>

4、导出合并数据excel

<script>
import * as XLSX from "xlsx";

export default {
  data() {
    return {
    }
  },
  methods: {
    exportList() {
      // excel表的数据
      const dataList = [{
        dictName: "阀门",
        model: [{
            modelName: "阀门A",
            modelCode: "ZHJ-001",
            packProduct: [{
                packProductName: "产品A",
                packProductCode: "YY-122-A",
            }],
          },
          {
            modelName: "阀门B",
            modelCode: "ZHJ-002",
            packProduct: [{
                packProductName: "产品B",
                packProductCode: "YY-122-B",
              },{
                packProductName: "产品C",
                packProductCode: "YY-122-C",
            }],
        }],
      },{
        dictName: "管道",
        model: [{
          modelName: "管道C",
          modelCode: "ZHJ-003",
          packProduct: [{
              packProductName: "产品D",
              packProductCode: "YY-122-D",
          }],
        },{
          modelName: "管道D",
          modelCode: "ZHJ-004",
          packProduct: [{
              packProductName: "产品E",
              packProductCode: "YY-122-E",
            },{
              packProductName: "产品F",
              packProductCode: "YY-122-F",
          }],
        }],
      }]
      // 要合并的合集
      let merge = [];
      // 要生成excel表的新数据
      let newXlsxData = [];//新建数据
      // 获取对应字段下标
      const filterVal = ['dictName', 'model', 'packProduct' ];
      // 1.重组数据
      // 2.获取合并的集合
      let model_c = filterVal.indexOf("model")//model的列下标
      let product_c = filterVal.indexOf("packProduct")// packProduct的列下标
      let current_r = 1; // 当前行从1开始（0是表头）
      let current_s_r = 1; // 每条数据开始行
      let model_s_r = 1; // model的开始行 从1开始
      let product_s_r = 1; // packProduct 的开始行 从1开始
      for (let i = 0; i < dataList.length; i++) {
        const dict = dataList[i];
        for (let j = 0; j < dict.model.length; j++) {
          const model = dict.model[j];
          for (let k = 0; k < model.packProduct.length; k++) {
            const product = model.packProduct[k];
            current_r++; // 当前行
            // 制造新数据
            let obj = {};
            obj.dictName = dict.dictName;
            obj.modelCode = model.modelCode;
            obj.modelName = model.modelName;
            obj.packProductCode = product.packProductCode;
            obj.packProductName = product.packProductName;
            newXlsxData.push(obj);
          }
          if (model_s_r != current_r - 1) {
            // 开始r != 结束行 则是合并
            let modelMerges = {
              s: { c: model_c, r: model_s_r },
              e: { c: model_c, r: current_r - 1 },
            };
            merge.push(modelMerges);
          }
          model_s_r = current_r;
          if (product_c != current_r) {
            //开始r != 结束行 则是合并
            let productMerges = {
              s: { c: product_c, r: product_s_r },
              e: { c: product_c, r: current_r - 1 },
            };
            merge.push(productMerges);
          }
          product_s_r = current_r;
        }
        //除dictName合并外，其他列(下标1到4) 均不合并
        //['组合件类别'0, '组合件型号代号'1, '组合件型号名称'2, '组合件产品代号'3,'组合件产品名称'4']
        for (let c = 0; c < 1 ; c++) {
          // 开始r != 结束行 则是合并
          if(current_s_r != (current_r-1)){ 
            let modelMerges = {
                s: {c: c,r: current_s_r},
                e: {c: c,r: current_r-1}
            };
            merge.push(modelMerges)
          }
        }
        current_s_r = current_r
      }
      const workSheet = XLSX.utils.json_to_sheet(newXlsxData);
      const workbook = XLSX.utils.book_new();
      // 合并的格式
      // s 意为 start ，即开始的单元格
      // e 以为end，即结束的单元格
      // r 是 row ，表示行号，从 0 计起
      // c 是 col ，表示列号，从 0 计起
      // merge = [
      //   // 纵向合并，范围是第1列的行2到行4
      //   { s: { r: 1, c: 0 }, e: { r: 3, c: 0 } },
      //   // 纵向合并，范围是第1列的行5到行7
      //   { s: { r: 4, c: 0 }, e: { r: 6, c: 0 } },
      // ];
      // 设置列宽
      workSheet['!cols'] = [
        { wch: 30 },
        { wch: 30 },
        { wch: 30 },
        { wch: 30 },
        { wch: 30 }
      ]
      //设置行高
      let rows = []
      for(let i = 0; i <length; i++) {
        let obj = { hpx: 20 }
        rows.push(obj)
       }
  	   workSheet['!rows'] = rows;
      // 合并数据
      workSheet["!merges"] = merge;
      // sheet1 是工作表的名称
      XLSX.utils.book_append_sheet(workbook, workSheet, "sheet1");
      // 添加固定表头
      XLSX.utils.sheet_add_aoa(workSheet, [["分类类别", "型号代号", "型号名称", "产品代号", "产品名称"]], { origin: "A1" });
      // 执行数据转换文件写入
      XLSX.writeFileSync(workbook, "文档名称.xlsx");
    },
  },
};
</script>