技术文档

Appearance

Excel 导出方案 ​

基础版本 ​

  • 按钮
vue
<el-button
  :loading="loadBtn1"
  plain
  @click="handleexportExcel"
>导出EXCEL</el-button>
  • 工具方法 utils/tools.js
js
export function exportExcel(params, filename) {
  var content = params;
  var blob = new Blob([content]);
  if ("download" in document.createElement("a")) {
    // 非ie
    var elink = document.createElement("a");
    elink.download = filename + ".xlsx";
    elink.style.display = "none";

    elink.href = URL.createObjectURL(blob);
    document.body.appendChild(elink);
    elink.click();
    document.body.removeChild(elink);
  } else {
    navigator.msSaveBlob(blob, filename + ".xlsx");
  }
}
  • 调用方法
js
import { exportExcel } from '@/utils/tools'
handleexportExcel(){
 this.loadBtn1 = true
      xxxxApi(this.formData).then(res => {
        let filename = '帖子回收站列表'
        exportExcel(res, filename)
        this.loadBtn1 = false
      }).catch(() => {
        this.loadBtn1 = false
      })
}

进阶版本 ​

注意

现有导出前端库中，XLSX.JS 功能强大但是操作颇为不便，于是封装了此插件，无额外依赖，支持 npm 引入，支持 Layui 插件形式加载，导出仅需一句话。

导出 excel 功能基于 XLSX.js，下载功能基于 FileSaver，读取文件基于 H5 的 FileReader。

功能演示地址： http://excel.wj2015.com

文档地址： http://excel.wj2015.com/_book/

官方地址

文档地址

安装 ​

bash

npm i lay-excel

基础版本 ​

  • 封装一个组件
vue
<template>
  <div>
    <el-button @click="ExportExcel">导出Excel</el-button>
  </div>
</template>

<script setup>
import LAY_EXCEL from "lay-excel";
const ExportExcel = () => {
  LAY_EXCEL.exportExcel([[1, 2, 3]], "表格导出.xlsx", "xlsx");
};
</script>

<style lang="scss" scoped></style>

复杂版本 ​

组件里面引入 ​

bash

import LAY_EXCEL from 'lay-excel'

获取到数据后插入表头 ​

注意

  1. 这里的 tabledata 就是表格中的数据

  2. 插入的头部要依据自定义表头的规则来定

  3. 例如比如我这里表格的第一列就日期和配送信息,表格的第二列就姓名，地址如图所示

获取表格数据 ​

bash
    const tableData = ref([
        {
            date: '2016-05-03',
            name: '王小虎',
            province: '上海',
            city: '普陀区',
            address: '上海市普陀区金沙江路 1518 弄',
            zip: 200333
        },
        {
            date: '2016-05-02',
            name: '王小虎',
            province: '上海',
            city: '普陀区',
            address: '上海市普陀区金沙江路 1518 弄',
            zip: 200333
        },
        {
            date: '2016-05-04',
            name: '王小虎',
            province: '上海',
            city: '普陀区',
            address: '上海市普陀区金沙江路 1518 弄',
            zip: 200333
        },
        {
            date: '2016-05-01',
            name: '王小虎',
            province: '上海',
            city: '普陀区',
            address: '上海市普陀区金沙江路 1518 弄',
            zip: 200333
        },
        {
            date: '2016-05-08',
            name: '王小虎',
            province: '上海',
            city: '普陀区',
            address: '上海市普陀区金沙江路 1518 弄',
            zip: 200333
        },
        {
            date: '2016-05-06',
            name: '王小虎',
            province: '上海',
            city: '普陀区',
            address: '上海市普陀区金沙江路 1518 弄',
            zip: 200333
        },
        {
            date: '2016-05-07',
            name: '王小虎',
            province: '上海',
            city: '普陀区',
            address: '上海市普陀区金沙江路 1518 弄',
            zip: 200333
        }
    ]);

插入表头 ​

js
// 设置table数据增加头部
const gettabledata = () => {
  let result = JSON.parse(JSON.stringify(tableData.value));
  result.unshift({
    date: "日期",
    name: "姓名",
    province: "省份",
    city: "市区",
    address: "详细地址",
    zip: "邮编",
  });
  result.unshift({
    date: "日期",
    name: "姓名",
    province: "地址",
    city: "",
    address: "",
    zip: "",
  });
  result.unshift({
    date: "日期",
    name: "配送信息",
    province: "",
    city: "",
    address: "",
    zip: "",
  });
  return result;
};

合并单元格 ​

js
// 合并单元格
const mergeCell = () => {
  let mergeConf = LAY_EXCEL.makeMergeConfig([
    ["A1", "A3"],
    ["B1", "F1"],
    ["B2", "B3"],
    ["C2", "F2"],
  ]); //从开头到结束
  let colConf = LAY_EXCEL.makeColConfig({
    A: 300,
    B: 200,
    C: 100,
    D: 200,
    E: 600,
    F: 300,
  });
  return {
    mergeConf,
    colConf,
  };
  // var rowConf = LAY_EXCEL.makeRowConfig({ 0: 300, 4: 1000 }, 200);  //第0行300高,第4行1000高 其余200高
};

导出表格 ​

js
// 导出excel
const ExportExcel = () => {
  // 设置头部
  let result = gettabledata();
  // 设置样式
  setStyle(result);
  // 合并单元格
  const { mergeConf, colConf } = mergeCell();
  // 导出
  LAY_EXCEL.exportExcel(
    {
      first1: result,
    },
    "表格导出.xlsx",
    "xlsx",
    {
      extend: {
        // extend 中可以指定某个 sheet 的属性，如果不指定 sheet 则所有 sheet 套用同一套属性
        first1: {
          // 以下配置仅 sheet1 有效
          "!merges": mergeConf,
          "!cols": colConf,
          // '!rows': rowConf
        },
      },
    }
  );
};

完整代码 ​

  • 封装一个组件
vue
<template>
  <div>
    <el-button @click="ExportExcel">导出Excel</el-button>
  </div>
</template>

<script setup>
import LAY_EXCEL from "lay-excel";
const tableData = ref([
  {
    date: "2016-05-03",
    name: "王小虎",
    province: "上海",
    city: "普陀区",
    address: "上海市普陀区金沙江路 1518 弄",
    zip: 200333,
  },
  {
    date: "2016-05-02",
    name: "王小虎",
    province: "上海",
    city: "普陀区",
    address: "上海市普陀区金沙江路 1518 弄",
    zip: 200333,
  },
  {
    date: "2016-05-04",
    name: "王小虎",
    province: "上海",
    city: "普陀区",
    address: "上海市普陀区金沙江路 1518 弄",
    zip: 200333,
  },
  {
    date: "2016-05-01",
    name: "王小虎",
    province: "上海",
    city: "普陀区",
    address: "上海市普陀区金沙江路 1518 弄",
    zip: 200333,
  },
  {
    date: "2016-05-08",
    name: "王小虎",
    province: "上海",
    city: "普陀区",
    address: "上海市普陀区金沙江路 1518 弄",
    zip: 200333,
  },
  {
    date: "2016-05-06",
    name: "王小虎",
    province: "上海",
    city: "普陀区",
    address: "上海市普陀区金沙江路 1518 弄",
    zip: 200333,
  },
  {
    date: "2016-05-07",
    name: "王小虎",
    province: "上海",
    city: "普陀区",
    address: "上海市普陀区金沙江路 1518 弄",
    zip: 200333,
  },
]);

// 设置table数据增加头部
const gettabledata = () => {
  let result = JSON.parse(JSON.stringify(tableData.value));
  result.unshift({
    date: "日期",
    name: "姓名",
    province: "省份",
    city: "市区",
    address: "详细地址",
    zip: "邮编",
  });
  result.unshift({
    date: "日期",
    name: "姓名",
    province: "地址",
    city: "",
    address: "",
    zip: "",
  });
  result.unshift({
    date: "日期",
    name: "配送信息",
    province: "",
    city: "",
    address: "",
    zip: "",
  });
  return result;
};
// 设置样式
const setStyle = (result) => {
  //设置样式的函数
  LAY_EXCEL.setExportCellStyle(result, "A1:F10", {
    s: {
      fill: { bgColor: { rgb: "cc0000" }, fgColor: { rgb: "157EE2" } },
      font: {
        color: {
          rgb: "FCDD7C",
        },
        sz: "28",
        bold: true,
      },
      alignment: {
        horizontal: "center",
        vertical: "center",
      },
      border: {
        top: { style: "thin", color: { rgb: "FFFF0000" } },
        bottom: { style: "thin", color: { rgb: "FFFF0000" } },
        left: { style: "thin", color: { rgb: "FFFF0000" } },
        right: { style: "thin", color: { rgb: "FFFF0000" } },
      },
    },
    // 回调参数，cell:原有数据，newCell:根据批量设置规则自动生成的样式，row:所在行数据，config:传入的配置,currentRow:当前行索引,currentCol:当前列索引，fieldKey:当前字段索引
    function(cell, newCell, row, config, currentRow, currentCol, fieldKey) {
      console.log(cell, newCell, row, config, currentRow, currentCol, fieldKey);
      return currentRow % 2 == 0 ? newCell : cell; // 隔行隔列上色
    },
  });
};

// 合并单元格
const mergeCell = () => {
  let mergeConf = LAY_EXCEL.makeMergeConfig([
    ["A1", "A3"],
    ["B1", "F1"],
    ["B2", "B3"],
    ["C2", "F2"],
  ]); //从开头到结束
  let colConf = LAY_EXCEL.makeColConfig({
    A: 300,
    B: 200,
    C: 100,
    D: 200,
    E: 600,
    F: 300,
  });
  return {
    mergeConf,
    colConf,
  };
  // var rowConf = LAY_EXCEL.makeRowConfig({ 0: 300, 4: 1000 }, 200);  //第0行300高,第4行1000高 其余200高
};

// 导出excel
const ExportExcel = () => {
  // 设置头部
  let result = gettabledata();
  // 设置样式
  setStyle(result);
  // 合并单元格
  const { mergeConf, colConf } = mergeCell();
  // 导出
  LAY_EXCEL.exportExcel(
    {
      first1: result,
    },
    "表格导出.xlsx",
    "xlsx",
    {
      extend: {
        // extend 中可以指定某个 sheet 的属性，如果不指定 sheet 则所有 sheet 套用同一套属性
        first1: {
          // 以下配置仅 sheet1 有效
          "!merges": mergeConf,
          "!cols": colConf,
          // '!rows': rowConf
        },
      },
    }
  );
};
</script>

<style lang="scss" scoped></style>

网站备案号:津ICP备15001525号-2