基于SheetJS封装

参考: https://github.com/PengChen96/table-xlsx

<script setup>
import {parseFile} from "~/parseFile";
import {exportFile} from "~/exportFile";


async function handleParse(e) {
  let files = e.target.files;
  console.log(files)
  console.log(await parseFile({file: files[0]}))
}

async function handleExport1() {
  const dataSource = [
    {
      key: '1',
      name: '胡彦斌',
      age: 32,
      address: '西湖区湖底公园1号西湖区湖底公 西湖区湖底公园1号 园1号',
    },
    {
      key: '2',
      name: '胡彦祖',
      age: 42,
      address: '西湖区湖底公园1号',
    },
  ];

  const columns = [
    {
      title: '姓名',
      dataIndex: 'name',
      key: 'name',
    },
    {
      title: '年龄',
      dataIndex: 'age',
      key: 'age',
    },
    {
      title: '住址',
      dataIndex: 'address',
      key: 'address',
    },
  ]
  await exportFile({
    columns,
    dataSource,
    fileName: "基础导出.xlsx"
  })
}
async function handleExport2() {
  const renderContent = (value, row, index) => {
    const obj = {
      children: value,
      props: {},
    };
    if (index === 4) {
      obj.props.colSpan = 0;
    }
    return obj;
  };

  const columns = [
    {
      title: 'Name',
      dataIndex: 'name',
      render: (text, row, index) => {
        if (index < 4) {
          return text;
        }
        return {
          children: text,
          props: {
            colSpan: 5,
          },
        };
      },
    },
    {
      title: 'Age',
      dataIndex: 'age',
      render: renderContent,
    },
    {
      title: 'Home phone',
      colSpan: 2,
      dataIndex: 'tel',
      render: (value, row, index) => {
        const obj = {
          children: value,
          props: {},
        };
        if (index === 2) {
          obj.props.rowSpan = 2;
        }
        // These two are merged into above cell
        if (index === 3) {
          obj.props.rowSpan = 0;
        }
        if (index === 4) {
          obj.props.colSpan = 0;
        }
        return obj;
      },
    },
    {
      title: 'Phone',
      colSpan: 0,
      dataIndex: 'phone',
      render: renderContent,
    },
    {
      title: 'Address',
      dataIndex: 'address',
      render: renderContent,
    },
  ];

  const dataSource = [
    {
      key: '1',
      name: 'John Brown',
      age: 32,
      tel: '0571-22098909',
      phone: 18889898989,
      address: 'New York No. 1 Lake Park',
    },
    {
      key: '2',
      name: 'Jim Green',
      tel: '0571-22098333',
      phone: 18889898888,
      age: 42,
      address: 'London No. 1 Lake Park',
    },
    {
      key: '3',
      name: 'Joe Black',
      age: 32,
      tel: '0575-22098909',
      phone: 18900010002,
      address: 'Sidney No. 1 Lake Park',
    },
    {
      key: '4',
      name: 'Jim Red',
      age: 18,
      tel: '0575-22098909',
      phone: 18900010002,
      address: 'London No. 2 Lake Park',
    },
    {
      key: '5',
      name: 'Jake White',
      age: 18,
      tel: '0575-22098909',
      phone: 18900010002,
      address: 'Dublin No. 2 Lake Park',
    },
  ];
  await exportFile({
    columns,
    dataSource,
    fileName: "表格行/列合并-导出.xlsx"
  })
}
async function handleExport3() {
  const columns = [
    {
      title: 'Name',
      dataIndex: 'name',
      key: 'name',
      width: 100,
    },
    {
      title: 'Other',
      children: [
        {
          title: 'Age',
          dataIndex: 'age',
          key: 'age',
          width: 150,
        },
        {
          title: 'Address',
          children: [
            {
              title: 'Street',
              dataIndex: 'street',
              key: 'street',
              width: 150,
            },
            {
              title: 'Block',
              children: [
                {
                  title: 'Building',
                  dataIndex: 'building',
                  key: 'building',
                  width: 100,
                },
                {
                  title: 'Door No.',
                  dataIndex: 'number',
                  key: 'number',
                  width: 100,
                },
              ],
            },
          ],
        },
      ],
    },
    {
      title: 'Company',
      children: [
        {
          title: 'Company Address',
          dataIndex: 'companyAddress',
          key: 'companyAddress',
          width: 200,
        },
        {
          title: 'Company Name',
          dataIndex: 'companyName',
          key: 'companyName',
        },
      ],
    },
    {
      title: 'Gender',
      dataIndex: 'gender',
      key: 'gender',
      width: 80,
    },
  ];

  const dataSource = [];
  for (let i = 0; i < 3; i++) {
    dataSource.push({
      key: i,
      name: 'John Brown',
      age: i + 1,
      street: 'Lake Park',
      building: 'C',
      number: 2035,
      companyAddress: 'Lake Street 42',
      companyName: 'SoftLake Co',
      gender: 'M',
    });
  }
  await exportFile({
    columns,
    dataSource,
    fileName: "表头分组-导出.xlsx"
  })
}
async function handleExport4() {
  const dataSource = [
    {
      key: '1',
      name: '胡彦斌',
      age: 32,
      address: '西湖区湖底公园1号',
    },
    {
      key: '2',
      name: '胡彦祖',
      age: 42,
      address: '西湖区湖底公园1号',
    },
  ];

  const columns = [
    {
      title: '姓名',
      dataIndex: 'name',
      key: 'name',
    },
    {
      title: '年龄',
      dataIndex: 'age',
      key: 'age',
    },
    {
      title: '住址',
      dataIndex: 'address',
      key: 'address',
    },
  ];

  await exportFile({
    sheetNames: ['表1', '表2', '表3'],
    columns: [columns, columns, columns],
    dataSource: [dataSource, dataSource, dataSource],
    fileName: "多sheet页-导出.xlsx"
  })
}
async function handleExport5() {
  const dataSource = [
    {
      key: '1',
      name: '胡彦斌',
      age: 32,
      address: '西湖区湖底公园1号',
    },
    {
      key: '2',
      name: '胡彦祖',
      age: 42,
      address: '西湖区湖底公园1号',
    },
  ];

  const columns = [
    {
      title: '姓名',
      dataIndex: 'name',
      key: 'name',
    },
    {
      title: '年龄',
      dataIndex: 'age',
      key: 'age',
    },
    {
      title: '住址',
      dataIndex: 'address',
      key: 'address',
    },
  ];

  await exportFile({
    columns,
    dataSource,
    showHeader: false,
    fileName: "不显示表头-导出.xlsx"
  })
}
async function handleExport6() {
  const COL_NUM = 25;
  const DATA_NUM = 10000;
  const getColumns = (colNum = COL_NUM) => {
    const columns = [];
    for (let i = 0; i < colNum; i++) {
      columns.push({
        title: `标题${i}`,
        dataIndex: `title${i}`,
        key: `title${i}`,
        width: 120
      });
    }
    return columns;
  }
  const getDataSource = (colNum = COL_NUM, dataNum = DATA_NUM) => {
    const dataSource = [];
    for (let r = 0; r < dataNum; r++) {
      let row = {};
      for (let c = 0; c < colNum; c++) {
        row[`title${c}`] = `内容${r}:${c}`;
      }
      dataSource.push(row);
    }
    return dataSource;
  }

  const columns = getColumns();
  const dataSource = getDataSource();

  await exportFile({
    columns,
    dataSource,
    showHeader: false,
    fileName: "大数据量-导出.xlsx"
  })
}
</script>

<template>
  <div>
    <h4>解析表格</h4>
    <input type="file" @change="handleParse">
    <br>
    <h4>导出表格</h4>
    <ol>
      <li>
        <button @click="handleExport1">基础</button>
      </li>
      <li>
        <button @click="handleExport2">表格行/列合并-导出</button>
      </li>
      <li>
        <button @click="handleExport3">表头分组-导出</button>
      </li>
      <li>
        <button @click="handleExport4">多sheet页-导出</button>
      </li>
      <li>
        <button @click="handleExport5">不显示表头-导出</button>
      </li>
      <li>
        <button @click="handleExport6">大数据量-导出</button>
      </li>
    </ol>
  </div>
</template>