vue 前端导出excel

一、官方 xlsx 库本身不支持写入单元格样式（包括垂直居中）,只能设置单元格内容以及合并单元格

 

  基于版本  "xlsx": "^0.17.3"

 

/**
 * 导出支持嵌套表头的 Excel 表格，表头垂直居中、水平居中
 * @param {Array} columns 表格列定义（支持 children 嵌套）
 * @param {Array} tableData 表格数据
 * @param {string} fileName 导出的文件名（不带扩展名）
 */
export function exportExcelMergedHeaders(columns, tableData, fileName = '导出结果') {
    // 1. 展平所有叶子列，用于导出数据
    const flatColumns = flattenColumns(columns);

    // 2. 构造表头两行数据
    const headerRow1 = [];
    const headerRow2 = [];

    columns.forEach(col => {
        if (col.children && col.children.length) {
            headerRow1.push(col.label);
            for (let i = 1; i < col.children.length; i++) {
                headerRow1.push(null);
            }
            col.children.forEach(child => headerRow2.push(child.label));
        } else {
            headerRow1.push(col.label);
            headerRow2.push(null);
        }
    });

    // 3. 构造数据行
    const dataRows = tableData.map(row =>
        flatColumns.map(col => row[col.prop] != null ? row[col.prop] : '')
    );

    // 4. 合并表头数据和数据行
    const sheetData = [headerRow1, headerRow2, ...dataRows];

    // 5. 生成 worksheet
    const worksheet = XLSX.utils.aoa_to_sheet(sheetData);

    // 6. 生成合并信息
    worksheet['!merges'] = buildMergedHeaders(columns);

    // 7. 设置表头单元格样式，垂直居中水平居中
    applyVerticalCenterStyle(worksheet, 2);

    // 8. 生成 workbook 并写入 worksheet
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');

    // 9. 写文件并触发下载
    const wbout = XLSX.write(workbook, { bookType: 'xlsx', type: 'binary' });
    saveAs(new Blob([s2ab(wbout)], { type: 'application/octet-stream' }), `${fileName}.xlsx`);
}

/** 
 * 展平嵌套列（递归）
 * @param {Array} columns
 * @returns {Array}
 */
function flattenColumns(columns) {
    const result = [];
    columns.forEach(col => {
        if (col.children && col.children.length) {
            result.push(...flattenColumns(col.children));
        } else {
            result.push(col);
        }
    });
    return result;
}

/**
 * 生成合并信息，只支持两层嵌套
 * @param {Array} columns
 * @returns {Array} 合并区域数组
 */
function buildMergedHeaders(columns) {
    const merges = [];
    let colIndex = 0;

    columns.forEach(col => {
        if (col.children && col.children.length) {
            // 第一行合并跨越子列数量
            merges.push({
                s: { r: 0, c: colIndex },
                e: { r: 0, c: colIndex + col.children.length - 1 }
            });
            // 第二行子列不合并
            colIndex += col.children.length;
        } else {
            // 单列跨两行合并
            merges.push({
                s: { r: 0, c: colIndex },
                e: { r: 1, c: colIndex }
            });
            colIndex++;
        }
    });

    return merges;
}

使用数据

  getColumns() {
      var columns = [
        { type: 'index', label: this.$t('commontable.001'), align: 'center', width: 50, fixed: true },
        { type: 'selection', selectable: this.checkSelectable, align: 'center', width: 50 },
        { label: this.$t('complain.001'), headerAlign: 'center', align: 'left', prop: 'complain_number', width: '180px' },
        { label: this.$t('complain.022'), headerAlign: 'center', align: 'left', prop: 'bad_content', width: 150 },

        // 嵌套列，需要用 children 处理
        this.checkPermi(['qms:complain:defect:customer']) ? {
          label: this.$t('DefectHoriz.001'),
          headerAlign: 'center',
          children: [
            { label: this.$t('reason.001'), headerAlign: 'center', align: 'left', prop: 'happen_principle', width: '150px' },
            { label: this.$t('reason.005'), headerAlign: 'center', align: 'left', prop: 'happen_root_reason', width: '150px' },
            { label: this.$t('reason.030'), headerAlign: 'center', align: 'left', prop: 'happen_measure', width: '150px' },
            { label: this.$t('reason.026'), headerAlign: 'center', align: 'left', prop: 'outflow_reason', width: '150px' },
            { label: this.$t('reason.049'), headerAlign: 'center', align: 'left', prop: 'outflow_measure', width: '150px' },
          ]
        } : null,

        // 另一组嵌套列，条件显示
        this.showEditColumn ? {
          label: this.$t('DefectHoriz.002'),
          headerAlign: 'center',
          prop: 'owner',
          key: 'owner',
          children: [
            { label: this.$t('reason.001'), headerAlign: 'center', align: 'left', prop: 'happen_principlec', width: '150px', key: 'happen_principlec' },
            { label: this.$t('reason.005'), headerAlign: 'center', align: 'left', prop: 'happen_reason', width: '150px', key: 'happen_reason' },
            { label: this.$t('reason.030'), headerAlign: 'center', align: 'left', prop: 'happen_correct', width: '150px', key: 'happen_correct' },
            { label: this.$t('reason.026'), headerAlign: 'center', align: 'left', prop: 'out_reason', width: '150px', key: 'out_reason' },
            { label: this.$t('reason.049'), headerAlign: 'center', align: 'left', prop: 'out_correct', width: '150px', key: 'out_correct' },
          ]
        } : null,

        // 最后一组嵌套列，带有模板插槽，需要用自定义 render (后面示例)
        {
          label: this.$t('DefectHoriz.003'),
          headerAlign: 'center',
          children: [
            { label: this.$t('DefectHoriz.004'), headerAlign: 'center', align: 'left', prop: 'h_deploy_object', width: '201px', slotName: 'h_deploy_object', editable: true },
            { label: this.$t('DefectHoriz.005'), headerAlign: 'center', align: 'left', prop: 'hd_suzhou', width: '201px', slotName: 'hd_suzhou', editable: true },
            { label: this.$t('DefectHoriz.006'), headerAlign: 'center', align: 'left', prop: 'hd_taiwan', width: '201px', slotName: 'hd_taiwan', editable: true },
            { label: this.$t('DefectHoriz.007'), headerAlign: 'center', align: 'left', prop: 'hd_japan', width: '201px', slotName: 'hd_japan', editable: true },
            { label: this.$t('DefectHoriz.008'), headerAlign: 'center', align: 'left', prop: 'hd_guangzhou', width: '201px', slotName: 'hd_guangzhou', editable: true },
          ]
        }
      ].filter(Boolean);// 过滤 null

      return columns;
    }

调用

  this.exportExcelMergedHeaders(this.getColumns(), list, '过往缺陷清单')