[JavaScript知识库]纯VUE实现自定义字段导出EXCEL

导入依赖

"element-ui": "2.13.0",
"file-saver": "^2.0.5",
"xlsx": "^0.18.5",
"xlsx-populate": "^1.21.0"

导入EXCEL导出工具类

import { MessageBox } from 'element-ui'

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

export default {

// 提示框
Msgbox(title, txt) {
  MessageBox.confirm(txt, title, {
    confirmButtonText: '确定',
    cancelButtonText: '取消'
  }).then(() => {
    console.log('取消回调')
  })
},

    // 导出的方法
xlsxDownload(tHeader, filterVal, list, DfileName) {
  console.log('tHeader:', tHeader)
  console.log('filterVal:', filterVal)
  console.log('list:', list)
  const excelName = DfileName + '_' + this.formatDate()
  require.ensure([], () => {
    const { export_json_to_excel } = require('./vendor/Export2Excel')
    const data = this.formatJson(filterVal, list)
    export_json_to_excel(tHeader, data, excelName)
  })
},

formatJson(filterVal, jsonData) {
  return jsonData.map(v => filterVal.map(j => v[j]))
},

// 获取年月日
formatDate() {
  const date = new Date()
  var myyear = date.getFullYear()
  var mymonth = date.getMonth() + 1
  var myweekday = date.getDate()

  if (mymonth < 10) {
    mymonth = '0' + mymonth
  }
  if (myweekday < 10) {
    myweekday = '0' + myweekday
  }
  return (myyear + mymonth + myweekday)
}

}

在main.js中引入

import commonutil from '@common/utils/commonutil/index.js'
Vue.prototype.$commonutil = commonutil

按钮样式

<el-button
  size="mini"
  style="border-color: #07979C; color: #07979C"
  @click="showDialog"
>导出</el-button>

?Data

data() {
    return {
      exportExcel: false,
      checkAll: false,
      checkedFields: [],
      isIndeterminate: true,
      multipleSelection: [],
      tableDataAll: [],
      exportFields: options.exportFields
}
}

staticData.js?

export const options = {
  channelOptions: [
    {
      label: 'XXXXXX',
      value: '0'
    },
    {
      label: 'XXXXX',
      value: '1'
    },
    {
      label: 'XXXXX',
      value: '2'
    },
    {
      label: 'XXXXX',
      value: '3'
    },
    {
      label: 'XXXXX',
      value: '4'
    }
  ],
  runTypeOptions: [
    {
      label: 'XXXX',
      value: '0'
    },
    {
      label: 'XXXXX',
      value: '1'
    }
  ],
  otherTypeOptions: [
    {
      label: 'XXXXX',
      value: '0'
    },
    {
      label: 'XXXX',
      value: '1'
    }
  ],
  openTypeOptions: [
    {
      label: 'XXXX',
      value: '0'
    },
    {
      label: 'XXXX',
      value: '1'
    }
  ],
  provincialOptions: [
    {
      label: '是',
      value: '0'
    },
    {
      label: '否',
      value: '1'
    }
  ],
  networdTypeOptions: [
    {
      label: 'XXXX',
      value: '0'
    },
    {
      label: 'XXXXX',
      value: '1'
    },
    {
      label: 'XXXX',
      value: '2'
    },
    {
      label: 'XXXX',
      value: '3'
    },
    {
      label: 'XXXX',
      value: '4'
    },
    {
      label: 'XXXXX',
      value: '5'
    }
  ],
    {
      title: '基础信息',
      width: 'width:25%',
      propsData: [
        { label: '终端编号', attr: 'terminalId', type: 'input', disable: true },
        { label: '接入时间', attr: 'crtTime', type: 'input', disable: true },
        { label: '所属机构', attr: 'groupName', type: 'select', disable: true },
        { label: '渠道来源', attr: 'channel', type: 'select', disable: false, style: 'width:150px' },
        { label: '启停状态', attr: 'enabled', type: 'select', disable: true },
        { label: '设备型号', attr: 'terminalModuleId', type: 'input', disable: true },
        { label: '运行类型', attr: 'runtype', type: 'select', disable: true }
      ]
    },
    {
      title: '布点属性',
      propsData: [
        { label: '五级行政区划地址', attr: 'areaName', type: 'select', disable: false, style: 'width:400px' },
        { label: '五级行政区划代码', attr: 'areaCode', type: 'input', disable: false },
        { label: '其他标识', attr: 'otherType', type: 'select', disable: false, style: 'width:150px' },
        { label: '详细布点地址', attr: 'stationingId', type: 'input', disable: true, style: 'width:1000px' },
        { label: '经纬度', attr: 'mapX', type: 'input', disable: true, style: 'width:400px' },
        { label: '管理员', attr: 'manager', type: 'input', disable: true, style: 'width:400px' },
        { label: '网络类型', attr: 'runtype', type: 'input', disable: true },
        { label: 'IP地址', attr: 'runtype', type: 'input', disable: true },
        { label: 'MAC地址', attr: 'runtype', type: 'input', disable: true }
      ]
    },
    {
      title: '其他信息',
      propsData: [
        { label: '是否接入省平台', attr: 'ifProvincial', type: 'select', disable: false },
        { label: '省统一设备编号', attr: 'provincialCode', type: 'input', disable: false }
      ]
    }
  ],
  defaultFields: [
    'terminalId',
    'crtTime',
    'groupName',
    'channel',
    'enabled',
    'runtype',
    'areaName',
    'otherType',
    'position',
    'ifProvincial',
    'provincialCode'
  ],
  exportFields: [
    { label: '编号', value: 'terminalId' },
    { label: '时间, value: 'crtTime' },
    { label: '机构', value: 'groupName' },
    { label: '来源', value: 'channel' },
    { label: '启停状态', value: 'enabled' },
    { label: '类型', value: 'runtype' },
    { label: '型号', value: 'terminalModuleId' },
    { label: '行政区划地址', value: 'areaName' },
    { label: '行政区划代码', value: 'areaCode' },
    { label: '标识', value: 'otherType' },
    { label: '地址', value: 'position' },
    { label: '经纬度', value: 'mapPoint' },
    { label: '管理员', value: 'manager' },
    { label: '网络类型', value: 'netword' },
    { label: 'IP地址', value: 'ipAddr' },
    { label: 'MAC地址', value: 'macAddr' },
    { label: '是否接入', value: 'ifProvincial' },
    { label: '设备编号', value: 'provincialCode' }
  ]
}

选择列

<el-table
        :data="tableData"
        border
        style="flex: 1; width: 100%"
        @selection-change="handleSelectionChange"
      >

?

?按钮点击事件

showDialog() {
  this.exportExcel = true    //用于弹出框的 :visible.sync 属性,为true时显示
  this.checkedFields = [...options.defaultFields] //从js配置文件中获取默认选择项的value
  this.isIndeterminate = true  //用于多选框的全选按钮 indeterminate 属性用以表示 checkbox 的不确定状态，一般用于实现全选的效果
}

弹出框

<el-dialog
      title="请勾选需要导出的字段"
      :visible.sync="exportExcel"   //为true则弹出，为false则隐藏
      custom-class="dialog-class"
    >
        //checkAll为全选按钮的绑定对象
      <el-checkbox v-model="checkAll" :indeterminate="isIndeterminate" @change="handleCheckAllChange">全选</el-checkbox>
      <el-divider />
        //checkedFields为多选框的绑定对象，当选中后会调用handleCheckedFields方法将value传入               
      //checkedFields中
      //exportFields为默认选中的label跟value
      <el-checkbox-group v-model="checkedFields" @change="handleCheckedFields">
        <el-checkbox v-for="(item,index) in exportFields" :key="index" :label="item.value" style="margin-bottom: 30px;">{{ item.label }}</el-checkbox>
      </el-checkbox-group>
      <div style="display: flex;justify-content: center;padding-top: 100px;">
        <el-button
          style="background-color: #1296DB; color: white"
          @click="downExcel"
        >导出</el-button>
      </div>
    </el-dialog>

?方法

//列选择事件
 handleSelectionChange(val) {
      this.multipleSelection = val
    },

//全选按钮点击事件
handleCheckAllChange(val) {
      if (val) {
        //如果true，则将所有的字段跟value传入checkBox多选框的v-model(checkedFields)中
        this.exportFields.forEach(item => {
          this.checkedFields.push(item.value)
        })
      } else {
        //false则全不选，清空checkedFields 
        this.checkedFields = []
      }
        //isIndeterminate为false则全选按钮变白
      this.isIndeterminate = false
    }

//多选框选中事件
handleCheckedFields(value) {
        //value为checkedFields集合
      const checkedCount = value.length
      console.log(value)
        //如果checkedCount 选中的数量等于exportFields的长度，表示全选，所以checkAll为true全选按钮需要选中
      this.checkAll = checkedCount === this.exportFields.length
        
      this.isIndeterminate = checkedCount > 0 && checkedCount < this.exportFields.length
    }

//导出
 downExcel() {
      if (this.checkedFields.length > 0) {
        //选择项大于0，表示有字段被选中
        const header = []  //列头
        const fields = []  //值  
        const title = 'XXX标题'
        this.exportFields.filter(r => {
          if (this.checkedFields.includes(r.value)) {
             //将选择项跟staticData.js中的配置项比较，相同则表示该字段需要导出
            header.push(r.label)
            fields.push(r.value)
          }
        })
         //此处为列选择项，比如只需要导出10列，那么可以在列中只选择10条
        if (this.multipleSelection.length > 0) {
          const list = this.changeData(this.multipleSelection, header, fields)
          this.$commonutil.xlsxDownload(header, fields, list, title)
        } else {
            //为0则表示全部都需要导出
          const list = this.changeData(this.tableDataAll, header, fields)
          this.$commonutil.xlsxDownload(header, fields, list, title)
        }
      } else {
        this.$message({
          message: '请勾选需要导出的字段',
          type: 'warning'
        })
      }
    },
//该方法是为了将对象中一些用数字来表示状态的字段值转换为中文
changeData(list, header, fields) {
      const source = JSON.stringify(list)
      const data = JSON.parse(source)
      data.forEach(item => {
        if (item.channel) {
          item.channel =  this.channelOptions.filter(r => r.id === item.channel).length > 0 ?
            this.channelOptions.filter(r => r.id === item.channel)[0].channelName :
            ''
          // item.channel = this.channelOptions[item.channel].channelName
        }
        //遍历所有导出的数据，比如enabled 0 表示不可用，1表示可用，那么就要在这里将0转换成为不可用。。。。。
        if (item.enabled) {
          item.enabled = this.openTypeOptions[item.enabled].label
        }
        if (item.runtype) {
          item.runtype = this.runTypeOptions[item.runtype].label
        }
        if (item.otherType) {
          item.otherType = this.otherTypeOptions[item.otherType].label
        }
        if (item.ifProvincial) {
          item.ifProvincial = options.provincialOptions[item.ifProvincial].label
        }
        if (item.netword) {
          const temp = item.netword.split(',')
          temp.forEach((e, i) => {
            if (i === 0) {
              item.netword = options.networdTypeOptions[e].label
            } else {
              item.netword = item.netword.concat(',', options.networdTypeOptions[e].label)
            }
          })
        }
        //这里将一串地址拆分成5个再保存
        if (item.areaCode && fields.includes('areaName')) {
            //得出areaName（xxxx省XXX市xxx区XXXX镇街XXX村居）一个标题拆分成'省', '市', '区', '镇街', '村居'五个标题再插入回数组
          const index = fields.indexOf('areaName')
          const label = ['省', '市', '区', '镇街', '村居']
           //将areaName的value值也拆分成5个
          const field = ['p', 'c', 'a', 'd', 'v']
          header.splice(index, 1)
          fields.splice(index, 1)
          header.splice.apply(header, [index, 0].concat(label))
          fields.splice.apply(fields, [index, 0].concat(field))

          const arr = this.getTreeNode(this.areaOptions, data => data.value === item.areaCode, 'label')
          if (arr.length > 0) {
            arr.forEach((a, i) => {
              item[field[i]] = a
            })
          }
        }
      })
      return data
    },


//获取树形
getTreeNode(tree, func, field = '', nodes = []) {
      if (!tree) return []
      for (const data of tree) {
        field === '' ? nodes.push(tree) : nodes.push(data[field])
        if (func(data)) return nodes
        if (data.children) {
          const childs = this.getTreeNode(data.children, func, field, nodes)
          if (childs.length) return childs
        }
        nodes.pop()
      }
      return []
    }