前端vue 导出 xlsx（后端只需要提供列表数据）

加蓓努力我先飞

已于 2023-02-10 11:31:50 修改

阅读量2.5k

点赞数 4

分类专栏：前端开发 vue 文章标签： leetcode

于 2022-04-28 15:33:50 首次发布

本文链接：https://blog.csdn.net/ingenuou_/article/details/124474446

版权

前端开发同时被 2 个专栏收录

92 篇文章 4 订阅

订阅专栏

vue

29 篇文章 4 订阅

订阅专栏

请添加图片描述

1.安装环境

npm install file-saver --save
npm install  xlsx -save

"export ‘default’ (imported as ‘XLSX’) was not found in ‘xlsx’
当出现类似这样的报错，你可以试一下这个

npm install xlsx@0.16.0 --save

2.对xlsx进行封装 - getXlsx.js

文件路径
utils/getXlsx.js

import fs from 'file-saver'
import XLSX from 'xlsx'


export function xlsx(json, fields, filename = '.xlsx') { //导出xlsx
  json.forEach(item => {
    for (let i in item) {
      if (fields.hasOwnProperty(i)) {
        item[fields[i]] = item[i];
      }
      delete item[i]; //删除原先的对象属性
    }
  })
  let sheetName = filename //excel的文件名称
  let wb = XLSX.utils.book_new() //工作簿对象包含一SheetNames数组，以及一个表对象映射表名称到表对象。XLSX.utils.book_new实用函数创建一个新的工作簿对象。
  let ws = XLSX.utils.json_to_sheet(json, {
    header: Object.values(fields)
  }) //将JS对象数组转换为工作表。
  wb.SheetNames.push(sheetName)
  wb.Sheets[sheetName] = ws
  const defaultCellStyle = {
    font: {
      name: "Verdana",
      sz: 13,
      color: "FF00FF88"
    },
    fill: {
      fgColor: {
        rgb: "FFFFAA00"
      }
    }
  }; //设置表格的样式
  let wopts = {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary',
    cellStyles: true,
    defaultCellStyle: defaultCellStyle,
    showGridLines: false
  } //写入的样式
  let wbout = XLSX.write(wb, wopts)
  let blob = new Blob([s2ab(wbout)], {
    type: 'application/octet-stream'
  })
  fs.saveAs(blob, filename + '.xlsx')
}

const s2ab = s => {
  var buf;
  if (typeof ArrayBuffer !== 'undefined') {
    buf = new ArrayBuffer(s.length)
    var view = new Uint8Array(buf)
    for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff
    return buf
  } else {
    buf = new Array(s.length);
    for (var i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xFF;
    return buf;
  }
}

3.将列表数据进行导出

<el-button style="margin-left: 8px" type="primary" @click="handleExcel">导出</el-button>
<el-table :data="tableList" border fit highlight-current-row>
  <el-table-column label="姓名" prop="name" align="center"></el-table-column>
  <el-table-column label="性别" prop="gender" align="center"></el-table-column>
  <el-table-column label="成绩" prop="score" align="center"></el-table-column>
  <el-table-column label="家庭住址" prop="address" align="center"></el-table-column>
</el-table>

//注意地址getXlsx.js所在的位置
import { xlsx } from "@/utils/getXlsx";

//列表数据
tableList: [
        {
          name: "张三",
          gender: "男",
          score: "95",
          address: "陕西省",
        },
        {
          name: "李四",
          gender: "男",
          score: "94",
          address: "山西省",
        },
        {
          name: "王五",
          gender: "男",
          score: "91",
          address: "上海市",
        },
        {
          name: "张美丽",
          gender: "女",
          score: "90",
          address: "北京市",
        },
        {
          name: "李漂亮",
          gender: "女",
          score: "88",
          address: "云南省",
        },
        {
          name: "贾有钱",
          gender: "男",
          score: "87",
          address: "四川",
        },
      ],

handleExcel() {
//以下的fields ，arry ，this.tableList都是可以更换的，但要保持一致
      let fields = {
        title1: "姓名",
        title2: "性别",
        title3: "成绩",
        title4: "家庭住址",
      };
      let arry = [];
      this.tableList.forEach((item) => {
        arry.push({
          title1: item.name,
          title2: item.gender,
          title3: item.score,
          title4: item.address,
        });
      });
      xlsx(arry, fields, "人员名单");
    },

4.例子

在这里插入图片描述

<template>
  <div>
    <el-card>
      <el-button type="primary" @click="planExportClick">计划导出</el-button>
    </el-card>
    <el-card>
      <el-table :data="tableData" height="500" style="width: 100%" center border highlight-current-row>
        <!-- <el-table-column type="selection" width="55" :show-overflow-tooltip="true" align="center" />
        <el-table-column type="index" label="序号" width="50" align="center"> </el-table-column>
        <el-table-column prop="comName1" label="姓名" :show-overflow-tooltip="true" align="center" />
        <el-table-column prop="comName2" label="年份" :show-overflow-tooltip="true" align="center" />
        <el-table-column prop="comName3" label="年龄" :show-overflow-tooltip="true" align="center" />
        <el-table-column prop="comName4" label="所在班级" :show-overflow-tooltip="true" align="center" />
        <el-table-column prop="comName5" label="性别" :show-overflow-tooltip="true" align="center" />
        <el-table-column prop="comName6" label="班主任" :show-overflow-tooltip="true" align="center" />
        <el-table-column prop="comName7" label="语文" :show-overflow-tooltip="true" align="center" />
        <el-table-column prop="comName8" label="数学" :show-overflow-tooltip="true" align="center" />
        <el-table-column prop="comName9" label="英语" :show-overflow-tooltip="true" align="center" />
        <el-table-column prop="comName10" label="物理" :show-overflow-tooltip="true" align="center" />
        <el-table-column prop="comName11" label="化学" :show-overflow-tooltip="true" align="center" />
        <el-table-column prop="comName12" label="生物" :show-overflow-tooltip="true" align="center" /> -->
        <!-- tableCol -->
        <el-table-column v-for="(val, key ) in tableCol" :key="key" :prop="key" :label="val" :show-overflow-tooltip="true" align="center">
        </el-table-column>
      </el-table>
    </el-card>
    <el-dialog title="自定义导出数据字段" :visible.sync="dialogVisible" width="30%">
      <el-row style="background: #fff;  padding:0 20px 10px">
        <el-col :span="1">
          <div style="text-align:right;padding-top:6px;">
            <el-checkbox :indeterminate="isIndeterminate" v-model="checkAll" @change="handleCheckAllChange">全选</el-checkbox>
            <el-checkbox-group v-model="checkList" @change="handleCheckedCitiesChange">
              <el-checkbox v-for="item in customCheckbox" :label="item" :key="item" style="display:block;margin:9px 20px;">{{item}}</el-checkbox>
            </el-checkbox-group>
          </div>
        </el-col>
        <el-col :span="24" style="text-align:right">
          <el-button type="primary" @click="okExportClick">确定导出</el-button>
        </el-col>
      </el-row>
    </el-dialog>
  </div>
</template>

<script>
// 导入
import Buttoncounter from '@/components/Buttoncounter';
import { xlsx } from "@/utils/getXlsx";
export default {
  name: 'basicKnowledgeThree',
  props: {},
  //   注册
  components: {
    'button-counter': Buttoncounter,
    // 'my-subtranction': Subtranction
  },
  data() {
    return {
      tableData: [
        {
          comName1: "张三",
          comName2: "2022年",
          comName3: "18",
          comName4: "高三一班",
          comName5: "女",
          comName6: "周柳",
          comName7: "110",
          comName8: "110",
          comName9: "110",
          comName10: "60",
          comName11: "60",
          comName12: "60",
        },
        {
          comName1: "李四",
          comName2: "2022年",
          comName3: "19",
          comName4: "高三一班",
          comName5: "男",
          comName6: "周柳",
          comName7: "110",
          comName8: "110",
          comName9: "110",
          comName10: "60",
          comName11: "60",
          comName12: "60",
        },
        {
          comName1: "王五",
          comName2: "2022年",
          comName3: "18",
          comName4: "高三二班",
          comName5: "男",
          comName6: "周柳",
          comName7: "110",
          comName8: "110",
          comName9: "110",
          comName10: "60",
          comName11: "60",
          comName12: "60",

        },
      ],
      //多选框选中的列表
      checkList: [
        '姓名',
        "年份",
        "年龄",
        "所在班级",
        "性别",
        "班主任",
        "语文",
        "数学",
        "英语",
        "物理",
        "化学",
        "生物",
      ],
      //所有的列表数据
      customCheckbox: [
        '姓名',
        "年份",
        "年龄",
        "所在班级",
        "性别",
        "班主任",
        "语文",
        "数学",
        "英语",
        "物理",
        "化学",
        "生物",
      ],
      checkAll: true, //是否全选 全选-true，半选/不选-false
      isIndeterminate: false, //用于实现全选，是否半选 全选/不选-false，半选-true
      tableCol: {
        'comName1': '姓名',
        'comName2': '年份',
        'comName3': '年龄',
        'comName4': '所在班级',
        'comName5': '性别',
        'comName6': '班主任',
        'comName7': '语文',
        'comName8': '数学',
        'comName9': '英语',
        'comName10': '物理',
        'comName11': '化学',
        'comName12': '生物',
      },
      dialogVisible: false,
      // 导出结束
    }
  },
  computed: {},
  mounted() {
    console.log("1111", this.$store.state.system.count);
  },
  methods: {
    // 导出开始------
    // 计划导出
    planExportClick() {
      this.dialogVisible = true
    },
    // 筛选-全选按钮
    handleCheckAllChange(val) {
      this.checkList = val ? this.customCheckbox : [];
      this.isIndeterminate = false;
    },
    // 筛选-多选按钮
    handleCheckedCitiesChange() {
      // console.log("value", value,this.checkList);
      let checkedCount = this.checkList.length;
      this.checkAll = checkedCount === this.customCheckbox.length;
      this.isIndeterminate =
        checkedCount > 0 && checkedCount < this.customCheckbox.length;
      console.log("this.checkAll", this.checkAll, this.isIndeterminate);
    },
    okExportClick() {
      console.log('this.checkList', this.checkList);
      let fields = {}
      for (const key in this.tableCol) {
        if (this.checkList.indexOf(this.tableCol[key]) > -1) {
          fields[key] = this.tableCol[key]
        }
      }
      // console.log('fields',fields,Object.keys(fields));
      let arry = this.tableData.map((item) => {
        let obj = {}
        Object.keys(fields).forEach((i) => {
          obj[i] = item[i]
        })
        return obj
      })
      xlsx(arry, fields, "信息");
      this.dialogVisible = false
    },
    // 导出结束------
  }
}
</script>

<style scoped>
</style>