前端 - 如何将el-table表格中的20万条数据导出到excel表格中？

Question

如何将el-table表格中的20万条数据导出到excel表格中？
导出要求：
1、所有分页数据都需要导出
2、只能导出到一个excel表中，不能分成多个excel表导出

方案：
将每页显示的数据量设置为99999999，并设置只显示一页

以下是我的解决方法和报错提示，请问如何进行修改？

报错信息：

 Error exporting to Excel: RangeError: Invalid array length
    at Array.push (<anonymous>)
    at XmlStream.openNode (exceljs.js:7332:1)
    at XmlStream.leafNode (exceljs.js:7411:1)
    at CellXform.render (exceljs.js:13243:1)
    at exceljs.js:17810:1
    at Array.forEach (<anonymous>)
    at RowXform.render (exceljs.js:17809:1)
    at exceljs.js:12787:1
    at Array.forEach (<anonymous>)
    at ListXform.render (exceljs.js:12786:1)

index.vue

methods: {
  exportExcel() {
        this.$refs['ruleForm'].validate(valid => {
          if (valid) {
            this.isloading = true
            this.dataListShow = []
            this.showItems = []
            getList3(
              {
                CurrentPage: 1,
                ItemsPerPage: 99999999
              }).then(res => {
                if (res.data.status === 0) {
                  this.TotalItems = res.data.data.TotalItems || 0
                  if (res.data.data.Items) {
                    var exportItems = res.data.data.Items
                    const items = exportItems || []
                    if (items.length === 0) {
                      this.$message.warning('当前没有数据可以导出！')
                      return
                    }
                    const nowDate = new Date()
                    import('@/vendor/Export2Excel9').then(excel => {
                      var tHeader = []
                      var filterVal = []
                      tHeader = ['批次号','名称','编号','状态','更新时间']
                      filterVal = ['localkeyid','name','code','status','updataTime']
                      const data = this.formatJson(filterVal, items)
                      excel.export_json_to_excel({
                        header: tHeader,
                        data,
                        filename: '数据列表' + nowDate.getFullYear() + '-' + (nowDate.getMonth() + 1) + '-' + nowDate.getDate(),
                        autoWidth: true
                      })
                    })
                  }
                }
              }).catch(err => {
                console.error('导出Excel失败:', err)
              }).finally(() => {
                this.isloading = false
              })
          }
        })
      },
}

export2Excel9.js

  require('script-loader!file-saver');
  require('./Blob');
  // import ExcelJS from 'xlsx';
  const ExcelJS = require('exceljs')

  function generateArray(table) {
    var out = [];
    var rows = table.querySelectorAll('tr');
    var ranges = [];
    for (var R = 0; R < rows.length; ++R) {
      var outRow = [];
      var row = rows[R];
      var columns = row.querySelectorAll('td');
      for (var C = 0; C < columns.length; ++C) {
        var cell = columns[C];
        var colspan = cell.getAttribute('colspan');
        var rowspan = cell.getAttribute('rowspan');
        var cellValue = cell.innerText;
        if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;

        // Skip ranges
        ranges.forEach(function (range) {
          if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
            for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
          }
        });

        // Handle Row Span
        if (rowspan || colspan) {
          rowspan = rowspan || 1;
          colspan = colspan || 1;
          ranges.push({
            s: {
              r: R,
              c: outRow.length
            },
            e: {
              r: R + rowspan - 1,
              c: outRow.length + colspan - 1
            }
          });
        };

        // Handle Value
        outRow.push(cellValue !== "" ? cellValue : null);

        // Handle Colspan
        if (colspan)
          for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
      }
      out.push(outRow);
    }
    return [out, ranges];
  };

  function datenum(v, date1904) {
    if (date1904) v += 1462;
    var epoch = Date.parse(v);
    return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
  }

  function sheet_from_array_of_arrays(data, ranges) {
    const worksheet = new ExcelJS.Worksheet();
    worksheet.columns = data[0].map((header, index) => ({
      header: header,
      key: `col${index}`,
      width: 50 // 默认宽度
    }));

    data.forEach((row, rowIndex) => {
      worksheet.addRow(row);
    });

    // 处理合并单元格
    ranges.forEach(range => {
      worksheet.mergeCells(
        range.s.r + 1, // exceljs 使用 1 基础索引
        range.s.c + 1,
        range.e.r + 1,
        range.e.c + 1
      );
    });

    return worksheet;
  }

  export function export_table_to_excel(id) {
    var theTable = document.getElementById(id);
    var oo = generateArray(theTable);
    var ranges = oo[1];

    /* original data */
    var data = oo[0];
    var ws_name = "SheetJS";

    const workbook = new ExcelJS.Workbook();
    const worksheet = sheet_from_array_of_arrays(data, ranges);

    /* add worksheet to workbook */
    workbook.addWorksheet(ws_name, { data: data });

    // 处理合并单元格
    ranges.forEach(range => {
      worksheet.mergeCells(
        range.s.r + 1, // exceljs 使用 1 基础索引
        range.s.c + 1,
        range.e.r + 1,
        range.e.c + 1
      );
    });

    workbook.xlsx.writeBuffer().then(buffer => {
      const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
      saveAs(blob, "test.xlsx");
    });
  }

  export function export_json_to_excel({
    header,
    data,
    filename = 'excel-list',
    autoWidth = true,
    bookType = 'xlsx',
    batchSize = 1000, // 每次处理的数据量
    cellStyles = true
  } = {}) {
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet("SheetJS");

    // 添加表头
    worksheet.addRow(header);

    // 分批处理数据
    for (let i = 0; i < data.length; i += batchSize) {
      const batch = data.slice(i, i + batchSize);
      batch.forEach(row => {
        worksheet.addRow(row);
      });
    }

    // 设置列宽
    if (autoWidth) {
      // 计算每列的最大宽度
      const colWidths = header.map(() => 0);

      // 计算表头宽度
      header.forEach((cell, index) => {
        colWidths[index] = Math.max(colWidths[index], cell.toString().length);
      });

      // 计算数据行宽度
      data.forEach(row => {
        row.forEach((cell, index) => {
          if (cell !== null && cell !== undefined) {
            const cellLength = cell.toString().length;
            if (cellLength > colWidths[index]) {
              colWidths[index] = cellLength;
            }
          }
        });
      });

      // 设置列宽，考虑中文字符的宽度
      worksheet.columns.forEach((column, index) => {
        column.width = colWidths[index] + 10; // 加上一些额外的空间
      });
    }

    // 处理单元格样式
    if (cellStyles) {
      worksheet.eachRow((row, rowNumber) => {
        row.eachCell((cell, colNumber) => {
          if (cell.value && cell.value.toString().includes('-----X')) {
            const value = cell.value.substring(0, cell.value.indexOf('-----X'));
            cell.value = value;
            cell.font = { color: { argb: 'FFFFFFFF' } };
            cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFFF0000' } };
          }
        });
      });
    }

    workbook.xlsx.writeBuffer().then(buffer => {
      const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
      saveAs(blob, `${filename}.${bookType}`);
    });
  }

Answer 1

使用流式写入

index.vue

<template>
  <div>
    <el-form ref="ruleForm" :model="form" :rules="rules">
      <!-- 表单内容，根据实际需求调整 -->
      <el-form-item prop="example">
        <el-input v-model="form.example"></el-input>
      </el-form-item>
    </el-form>
    <button :disabled="isloading" @click="exportExcel">
      {{ isloading ? '正在导出...' : '导出到 Excel' }}
    </button>
  </div>
</template>

<script>
import { export_json_to_excel_stream } from '@/utils/export2Excel9';
import { getList3 } from '@/api/yourApi'; // 替换为您的实际 API 方法

export default {
  name: 'ExportExcel',
  data() {
    return {
      isloading: false,
      form: {
        example: '' // 示例表单字段，根据实际需求调整
      },
      rules: {
        example: [{ required: true, message: '请输入', trigger: 'blur' }]
      },
      headers: ['批次号', '名称', '编号', '状态', '更新时间'],
      filterVal: ['localkeyid', 'name', 'code', 'status', 'updataTime']
    };
  },
  methods: {
    formatJson(filterVal, jsonData) {
      return jsonData.map(item =>
        filterVal.map(key => (item[key] !== undefined ? item[key] : ''))
      );
    },
    async exportExcel() {
      try {
        const valid = await this.$refs.ruleForm.validate().catch(() => false);
        if (!valid) return;

        this.isloading = true;
        this.$message.info('开始导出数据，请稍候...');

        const res = await getList3({
          CurrentPage: 1,
          ItemsPerPage: 999999
        });

        if (res.data.status !== 0 || !res.data.data.Items) {
          this.$message.error('获取数据失败');
          return;
        }

        const items = res.data.data.Items;
        if (items.length === 0) {
          this.$message.warning('当前没有数据可以导出！');
          return;
        }

        const nowDate = new Date();
        const data = this.formatJson(this.filterVal, items);

        await export_json_to_excel_stream({
          header: this.headers,
          data,
          filename: `数据列表${nowDate.getFullYear()}-${nowDate.getMonth() + 1}-${nowDate.getDate()}`,
          autoWidth: true,
          chunkSize: 3000
        });

        this.$message.success('导出完成！');
      } catch (error) {
        this.$message.error(`导出失败：${error.message}`);
      } finally {
        this.isloading = false;
      }
    }
  }
};
</script>

<style scoped>
button {
  padding: 10px 20px;
  background-color: #409eff;
  color: white;
  border: none;
  border-radius: 4px;
  cursor: pointer;
}
button:disabled {
  background-color: #a0cfff;
  cursor: not-allowed;
}
</style>

export2Excel9.js

import ExcelJS from 'exceljs';
import { saveAs } from 'file-saver';

// 使用流式写入
export async function export_json_to_excel_stream({
  header = [],
  data = [],
  filename = 'excel-list',
  autoWidth = true,
  bookType = 'xlsx',
  chunkSize = 5000
} = {}) {
  try {
    const workbook = new ExcelJS.stream.xlsx.WorkbookWriter({
      useStyles: false,
      useSharedStrings: false
    });

    const worksheet = workbook.addWorksheet('Sheet1', { useStyles: false });

    worksheet.columns = header.map((h, index) => ({
      header: h,
      key: `col_${index}`,
      width: autoWidth ? Math.max(h.length + 5, 15) : 15
    }));

    for (let i = 0; i < data.length; i += chunkSize) {
      const chunk = data.slice(i, i + chunkSize);
      for (const row of chunk) {
        const rowData = {};
        row.forEach((cell, index) => {
          rowData[`col_${index}`] = cell;
        });
        worksheet.addRow(rowData).commit();
      }
      await new Promise(resolve => setTimeout(resolve, 10));
    }

    const buffer = await workbook.commit();
    const blob = new Blob([buffer], {
      type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    });

    saveAs(blob, `${filename}.${bookType}`);
  } catch (error) {
    throw new Error(`导出失败：${error.message}`);
  }
}

Answer 2

这么多数据受限于用户浏览器的性能，会崩溃的吧，转后端不好嘛？