Excel | 前端实现复杂表格导入导出

通过前端技术实现复杂excel表格（合并单元格，这里没有做表格的数据类型）的导入导出，下面代码还有简单表格的导出。 以下代码，cv即可用。主要是vue3以及ts的简单写法。

// 表格导入导出
// 引入兼容vue2兼容vue3的插件
import { reactive, toRefs } from "@vue/composition-api";

import * as XLSX from "xlsx";

// 原来是xlsx-style，后来发现有个问题，便自己拉了一个库并且npm上去了
import XLSXStyle from 'xlsx-style-revision'

import * as zipObject from "lodash/zipObject";

import { Message } from "element-ui";


// 定义数据类型
interface WorkBookModel {
  name: string
  sheet: any
  [propname: string]: any
}
interface ExcelModel {
  id: string
  project: string
  jobNumber: string
  materialType: string
  material: string
  module: string
  unit: string
  totalDesignQuantity: string
  consumedQuantity: string
  lossFactorCoefficient: string
  totalConsumption: string
  responsibleCostPrice: string
  controlPrice: string
  actualQuantityConsumed: string
  purchasedPrice: string
  actualConsumptionAmount: string
  openTiredPurchaseQuantity: string
  bookStockQuantity: string
  inventoryCountQuantity: string
  remainingPurchaseQuantity: string
  quantityWith: string
  quantityWithout: string
  amountWithDepletion: string
  amountWithoutLoss: string
  biddingUnitPrice: string
  purchaseResponsibilityCostPrice: string
  differencePurchaseBidPrice: string
}

export const useTableImportOutput = (useGetTableData) => {
  const tableImportOutput = reactive({
    excelJsonData: [],
    loading: false,
    excelTime: [],
    excelProjectName: []
  });
  const jsonKeys: string[] = [
    "id",
    "project",
    "jobNumber",
    "materialType",
    "material",
    "module",
    "unit",
    "totalDesignQuantity",
    "consumedQuantity",
    "lossFactorCoefficient",
    "totalConsumption",
    "responsibleCostPrice",
    "controlPrice",
    "actualQuantityConsumed",
    "purchasedPrice",
    "actualConsumptionAmount",
    "openTiredPurchaseQuantity",
    "bookStockQuantity",
    "inventoryCountQuantity",
    "remainingPurchaseQuantity",
    "quantityWith",
    "quantityWithout",
    "amountWithDepletion",
    "amountWithoutLoss",
    "biddingUnitPrice",
    "purchaseResponsibilityCostPrice",
    "differencePurchaseBidPrice"
  ];

  // 导入的函数
  const handleGetExcelData = file => {
    return new Promise(resolve => {
      // 创建一个可读的文件对象
      const render = new FileReader();
      render.onload = e => {
        const eResult = e.target?.result;
        // 读文件
        const workBook = XLSX.read(eResult, {
          type: "binary"
        });
        // 定义一个结果
        const result: WorkBookModel[] = [];
        // 遍历数据中的sheet表
        workBook.SheetNames.forEach(v => {
          result.push({
            name: v,
            // 将每一个表的数据json化
            sheet: XLSX.utils.sheet_to_json(
              workBook.Sheets[v]
            )
          });
        });
        resolve(result);
      };
      render.readAsBinaryString(file.raw);
    });
  };

  // 处理json对象数据,这里只有一个sheet，如果有多个的话需要循环处理
  const parseJsonResult = jsonData => {
    // 获取第一组数据
    const headExcelData: {} = jsonData[0].sheet[0]
    // 获取第一行的日期
    const excelTime: string[] = JSON.parse(JSON.stringify(Object.keys(headExcelData)[0].match(/\d+/g)))
    // 获取项目单位
    const excelProjectName: string[] = Object.values(headExcelData)
    let header: object = {}
    if (Array.isArray(excelTime) && Array.isArray(excelProjectName)) {
      header = JSON.parse(JSON.stringify({
        year: excelTime[0],
        quarter: excelTime[1],
        projectName: excelProjectName[0].split('：')[1]
      }))
    }
    // 去除表头
    const _jsonData = jsonData[0].sheet.slice(4, jsonData[0].sheet.length);
    // 去除表头后的数组长度为0的时候，提醒错误
    if (_jsonData.length <= 0)
      return Message.error("导入数据为空，请重新导入！");
    // 否则正确处理
    const _result: ExcelModel[] = [];
    // 遍历整个sheet，获取整行数据
    _jsonData.forEach(v => {
      // 获取一行单元格数据对象
      const objectValue = Object.values(v);
      // 使用loadsh函数一对一写入
      _result.push(zipObject(jsonKeys, objectValue));
    });
    return {
      header, _result
    }
  };

  // 导入
  const handleImportData = file => {
    tableImportOutput.loading = true
    handleGetExcelData(file).then(jsonData => {
      tableImportOutput.excelJsonData = parseJsonResult(jsonData);
      useGetTableData.tableData.value = tableImportOutput.excelJsonData._result
      useGetTableData.headerData.value = tableImportOutput.excelJsonData.header
      console.log('useGetTableData', useGetTableData);
      tableImportOutput.loading = false
    });
  };

  // 导出excel函数,导出没有合并单元格的数据，数据的key就是表格的表头
  const handleExportExcel: Function = exportJson => {
    // 新建一个sheet
    const workSheet = XLSX.utils.json_to_sheet(exportJson);
    // 设置sheet单元格的宽度
    workSheet["!cols"] = [{ wpx: 220 }];

    // 新建sheetBook
    const workBook = XLSX.utils.book_new();
    // 将sheet写入book中
    XLSX.utils.book_append_sheet(
      workBook,
      workSheet,
      "附表-项目经济活动分析表"
    );
    // 将book写入excel中
    XLSX.writeFile(workBook, "物资表.xlsx");
  };

  // 导出带有样式的excel
  const handleSheet2Blob = (sheet, sheetName) => {
    sheetName = sheetName || 'sheet1';
    const workbook = {
      SheetNames: [sheetName],
      Sheets: {},
    };
    // 生成excel的配置项
    workbook.Sheets[sheetName] = sheet;
    // 下载这里一定要用 xlsx-style 的write() 方法才可以使导出excel表格带样式
    const wbout = XLSXStyle.write(workbook, { type: 'buffer' });
    // 字符串转ArrayBuffer
    const blob = new Blob([wbout], {
      type: 'application/octet-stream',
    });

    return blob;
  }

  // 下载导出的excel
  const handleDownloadDialog = (url, saveName) => {
    // 创建blob地址
    if (typeof url == 'object' && url instanceof Blob) {
      url = URL.createObjectURL(url);
    }
    const aLink = document.createElement('a');
    aLink.href = url;
    aLink.download = saveName || '';
    let event;
    if (window.MouseEvent) event = new MouseEvent('click');
    else {
      event = document.createEvent('MouseEvents');
      event.initMouseEvent(
        'click',
        true,
        false,
        window,
        0,
        0,
        0,
        0,
        0,
        false,
        false,
        false,
        false,
        0,
        null
      );
    }
    aLink.dispatchEvent(event);
  }

  // 导出合并单元格的excel函数
  const handleExportMoreExcel = exportJson => {
    // 先定义一个表头
    const headData: (string | number | null)[][] = [
      ['（开累/当期）截止  年   季（月）度分工号物资量价差表（含已消耗应结未结物资及对应设计量）', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null],
      ['单位：××项目经理部', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, '单位：元', null, null, null, null, null, null],
      ['序号', '项目名称', '工号', '材料类别', '材料名称', '规格型号', '单位', '总设计数量', '责任成本', null, null, null, null, '实际消耗成本', null, null, '开累采购数量', '账面库存数量（计算应结未结后）', '库存盘点数量', '剩余采购数量', '量差', null, null, null, '价差', null, null],
      [null, null, null, null, null, null, null, null, '应耗数量', '损耗系数', '应耗合计', '责任成本价', '控制金额', '实耗数量（含应结未结）', '采购均价', '实耗金额', null, null, null, null, '含损耗节超数量', '不含损耗节超数量', '含损耗量差金额', '不含损耗量差金额', '投标单价', '采购与责任成本价差', '采购与投标单价价差'],
      [null, null, null, null, null, null, null, 0, 1, 2, '3=1x(1+2)', 4, '5=3x4', 6, 7, '8=6x7', 9, '10=9-6', 11, '12=0-6-11', '13=3-6', '14=1-6', '15=7x13', '16=7x14', 17, '18=(7-4)x1', '19=(7-17)x1']
    ]
    // 将获取的数据放入表头的数据中
    exportJson.forEach(v => {
      const values: string[] = Object.values(v)
      headData.push(values)
    });
    // 创建一个sheet
    const workSheet = XLSX.utils.aoa_to_sheet(headData)
    // 合并sheet的单元格
    workSheet["!merges"] = [
      // 0行
      { s: { c: 0, r: 0 }, e: { c: 26, r: 0 } },
      // 1行
      { s: { c: 0, r: 1 }, e: { c: 19, r: 1 } },
      { s: { c: 20, r: 1 }, e: { c: 26, r: 1 } },
      // 2行
      { s: { c: 0, r: 2 }, e: { c: 0, r: 4 } },
      { s: { c: 1, r: 2 }, e: { c: 1, r: 4 } },
      { s: { c: 2, r: 2 }, e: { c: 2, r: 4 } },
      { s: { c: 3, r: 2 }, e: { c: 3, r: 4 } },
      { s: { c: 4, r: 2 }, e: { c: 4, r: 4 } },
      { s: { c: 5, r: 2 }, e: { c: 5, r: 4 } },
      { s: { c: 6, r: 2 }, e: { c: 6, r: 4 } },
      { s: { c: 7, r: 2 }, e: { c: 7, r: 3 } },
      { s: { c: 8, r: 2 }, e: { c: 12, r: 2 } },
      { s: { c: 13, r: 2 }, e: { c: 15, r: 2 } },
      { s: { c: 16, r: 2 }, e: { c: 16, r: 3 } },
      { s: { c: 17, r: 2 }, e: { c: 17, r: 3 } },
      { s: { c: 18, r: 2 }, e: { c: 18, r: 3 } },
      { s: { c: 19, r: 2 }, e: { c: 19, r: 3 } },
      { s: { c: 20, r: 2 }, e: { c: 23, r: 2 } },
      { s: { c: 20, r: 2 }, e: { c: 23, r: 2 } },
      { s: { c: 24, r: 2 }, e: { c: 26, r: 2 } },
    ]
    for (const key in workSheet) {
      console.log('workSheet', workSheet[key]);
      if (Object.prototype.toString.call(workSheet[key]).slice(8, -1) === 'Object') {
        workSheet[key].s = {
          font: {
            name: '仿宋',
            sz: 10,
            color: {
              auto: 1,
            },
          },
          alignment: {
            horizontal: 'center',
            vertical: 'center',
            wrapText: true,
          }
        }
      }
      if (key === 'A2' || key === 'U2') {
        workSheet[key].s = {
          font: {
            name: '仿宋',
            sz: 10,
            color: {
              auto: 1,
            },
          },
          alignment: {
            horizontal: 'left',
            vertical: 'left',
            wrapText: true,
          }
        }
      }
    }
    handleDownloadDialog(handleSheet2Blob(workSheet, '附表-项目经济活动分析表'), '物资表.xlsx')
  };

  // 导出
  const handleDownload: Function = () => {
    console.log("导出");
    // 这里是导出的示例数据
    const exportJson = [
      {
        id: 1,
        project: "项目1",
        jobNumber: "主体结构",
        materialType: "主材类",
        material: "钢筋",
        module: "HPB-001",
        unit: "吨",
        totalDesignQuantity: 1,
        consumedQuantity: 1,
        lossFactorCoefficient: 1,
        totalConsumption: 1,
        responsibleCostPrice: 1,
        controlPrice: 1,
        actualQuantityConsumed: 1,
        purchasedPrice: 1,
        actualConsumptionAmount: 1,
        openTiredPurchaseQuantity: 1,
        bookStockQuantity: 1,
        inventoryCountQuantity: 1,
        remainingPurchaseQuantity: 1,
        quantityWith: 1,
        quantityWithout: 1,
        amountWithDepletion: 1,
        amountWithoutLoss: 1,
        biddingUnitPrice: 1,
        purchaseResponsibilityCostPrice: 1,
        differencePurchaseBidPrice: 1
      },
    ];
    handleExportMoreExcel(exportJson);
  };

  return {
    ...toRefs(tableImportOutput),
    handleImportData,
    handleDownload
  };
};