js前端导出excel，结合table列拖拽换位和勾选显示

导出

实现思路：

由于项目是前后端混合，所以一些vue专用的没有下载包的插件用不了，只能手写js实现，前端js导出的方法网上一搜就有，但由于我所做的需要结合表头拖拽更换位置功能，而已有的方法，导出的时候数据的顺序就已经固定了，所以思路是：先获取拖拽后的表头顺序，然后根据此顺序将全部数据中的每一条数据进行排序

数据说明：

//list
list:[],//接口获取的所有数据列表
//tableHeader 表头列
tableHeader: [{
                            column_name: "visiting_time",
                            column_comment: "约访时间",
                            show: true,
                            fixed: true,
                            sort: true
                        }, {
                            column_name: "name",
                            column_comment: "称呼",
                            show: true,
                            fixed: true
                        }, {
                            column_name: "phone",
                            column_comment: "联系方式",
                            show: true,
                        },
                        {
                            column_name: "child_name",
                            column_comment: "孩子姓名",
                            show: true,
                        },
                        {
                            column_name: "child_gender",
                            column_comment: "孩子性别",
                            show: true,
                        },
                        {
                            column_name: "child_grade",
                            column_comment: "孩子年级",
                            show: true,

                        },

                        {
                            column_name: "area_id",
                            column_comment: "地区",
                            show: true,
                        },
                        {
                            column_name: "pro_class_id",
                            column_comment: "产品/班型",
                            show: true,
                        },
                        {
                            column_name: "num",
                            column_comment: "到访人数",
                            show: true,
                        },
                        {
                            column_name: "opendate",
                            column_comment: "开放日活动",
                            show: true,
                        },
                        {
                            column_name: "is_my",
                            column_comment: "是否考试",
                            show: true,
                        },
                        {
                            column_name: "interview_remark",
                            column_comment: "约访提醒",
                            show: true,
                        },
                        {
                            column_name: "create_id",
                            column_comment: "约访创建人",
                            show: true,
                        },
                        {
                            column_name: "createtime",
                            column_comment: "创建时间",
                            show: true,
                            sort: true
                        },
                        {
                            column_name: "real_three",
                            column_comment: "实际来源3",
                            show: true,
                        },
                        {
                            column_name: "interview_id1",
                            column_comment: "接待人",
                            show: true,
                        },
                        {
                            column_name: "is_visit",
                            column_comment: "是否到访",
                            show: true,
                        },
                        {
                            column_name: "status_id",
                            column_comment: "客户状态",
                            show: true,
                        },
                        {
                            column_name: "interview_feedback",
                            column_comment: "来访反馈",
                            show: true,
                        },
                        {
                            column_name: "telephoner_later",
                            column_comment: "后续跟进人",
                            show: true,
                        },
                        {
                            column_name: "company_id",
                            column_comment: "约访对象",
                            show: true,
                        },

                        {
                            column_name: "relation",
                            column_comment: "家长与学生关系",
                            show: true,

                        }
                    ],

核心实现代码：

//点击导出按钮后执行的方法
importData() {
                    const that = this
                    console.log('导出')
                    console.log(that.list, '全部数据')
                    const ccc = []
                    let columnNameList = [] //存储拖拽后的表头中文名
                    let columnList = []		//存储拖拽后的表头的key值
                    //获取拖拽后的表头内容（顺序固定）
                    for (let index = 0; index < that.tableHeader.length; index++) {
                    //此处判断列的显示
                        if (that.tableHeader[index].show == true) {
                            const keyyy = that.tableHeader[index].column_comment
                            // console.log(keyyy,'keyyy')
                            columnNameList.push(keyyy)
                            columnList.push(that.tableHeader[index].column_name)
                        }

                    }
                    console.log(columnNameList, '表头名字数组')
                    console.log(columnList, '表头value数组')
                    let arr = []
					// 主要靠三层循环来实现
                    //第一次循环 循环全部数据 操作每一条数据
                    for (let index = 0; index < that.list.length; index++) {
                        let eachObj = {}
                        //第二次循环 循环按顺序存储的表头key值 用来对位
                        for (let index2 = 0; index2 < columnList.length; index2++) {
						//第三次循环 循环全部数据中每一条数据中的每个字段 与上一层循环判断实现对位
                            for (const key in that.list[index]) {

                                if (columnList[index2] == key) {
                                    eachObj[key] = that.list[index][key]
                                    // console.log(eachObj, 'eachObj')
                                }
                            }
                        }
                        arr.push(eachObj)
                        console.log('--------------------------')
                        // arr.push(that.list[index])
                    }
                    console.log(arr, '处理后的数组')

					// 前端js导出excel
                    // 输出base64编码
                    const base64 = s => window.btoa(unescape(encodeURIComponent(s)));
                    const jsonData = arr
                    // 列标题
                    // let str = '<tr><td>邮箱</td><td>电话</td><td>姓名</td></tr>';
                    let main = ''
                    //根据表头名顺序数组生成顺序的excel表头
                    for (let index = 0; index < columnNameList.length; index++) {
                        main += '<td>' + columnNameList[index] + '</td>'

                    }
                    let str = '<tr>' + main + '</tr>';
                    console.log(str, 'strrrr')
                    // 循环遍历，每行加入tr标签，每个单元格加td标签
                    for (let i = 0; i < jsonData.length; i++) {
                        str += '<tr>';
                        for (const key in jsonData[i]) {
                            // 增加\t为了不让表格显示科学计数法或者其他格式
                            str += `<td>${ jsonData[i][key] + '\t'}</td>`;

                        }
                        str += '</tr>';
                    }
                    // Worksheet名
                    const worksheet = 'Sheet1'
                    const uri = 'data:application/vnd.ms-excel;base64,';

                    // 下载的表格模板数据
                    const template = `<html xmlns:o="urn:schemas-microsoft-com:office:office" 
                    xmlns:x="urn:schemas-microsoft-com:office:excel" 
                    xmlns="http://www.w3.org/TR/REC-html40">
                    <head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>
                    <x:Name>${worksheet}</x:Name>
                    <x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>
                    </x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
                    </head><body><table>${str}</table></body></html>`;
                    // 下载模板
                    window.location.href = uri + base64(template);

                    // // 要导出的json数据
                    // const jsonData = [{
                    //         name: '路人甲',
                    //         phone: '123456789',
                    //         email: '000@123456.com'
                    //     },
                    //     {
                    //         name: '炮灰乙',
                    //         phone: '123456789',
                    //         email: '000@123456.com'
                    //     },
                    //     {
                    //         name: '土匪丙',
                    //         phone: '123456789',
                    //         email: '000@123456.com'
                    //     },
                    //     {
                    //         name: '流氓丁',
                    //         phone: '123456789',
                    //         email: '000@123456.com'
                    //     },
                    // ];
                    // // 列标题，逗号隔开，每一个逗号就是隔开一个单元格
                    // let str = `姓名,电话,邮箱\n`;
                    // // 增加\t为了不让表格显示科学计数法或者其他格式
                    // for (let i = 0; i < jsonData.length; i++) {
                    //     for (const key in jsonData[i]) {
                    //         str += `${jsonData[i][key] + '\t'},`;
                    //     }
                    //     str += '\n';
                    // }
                    // // encodeURIComponent解决中文乱码
                    // const uri = 'data:text/csv;charset=utf-8,\ufeff' + encodeURIComponent(str);
                    // // 通过创建a标签实现
                    // const link = document.createElement("a");
                    // link.href = uri;
                    // // 对下载的文件命名
                    // link.download = "json数据表.csv";
                    // link.click();

                },
dragstart(index) {
                    this.dragIndex = index;
                },
dragenter(e, index) {
                    //判断 不会拖到固定列
                    console.log(index, 'index')
                    e.preventDefault();
                    let fixed = 0
                    for (let num = 0; num < this.tableHeader.length; num++) {
                        if (this.tableHeader[num].fixed == true) {
                            fixed += 1
                        }
                    }
                    // 避免源对象触发自身的dragenter事件
                    if (this.dragIndex !== index) {
                        //前几列固定 无法拖拽
                        if (index >= fixed) {
                            const source = this.tableHeader[this.dragIndex];
                            this.tableHeader.splice(this.dragIndex, 1);
                            this.tableHeader.splice(index, 0, source);
                            // 排序变化后目标对象的索引变成源对象的索引
                            this.dragIndex = index;
                        }

                    }

                },
dragover(e, index) {
                    e.preventDefault();
                },

效果2（同事优化后）

封装组件文件：columnSetDialog.vue

<template>
  <el-dialog
    v-dialogDrag
    :visible.sync="filterShow"
    title="自定义列表设置"
    :close-on-click-modal="false"
    :before-close="customCancel"
    width="35%"
  >
    <div class="dialog-main">
      <div class="main-left main">
        <div class="main-header">可添加的列</div>
        <div class="main-container">
          <el-checkbox-group
            v-model="checkedTableColumns"
            class="checkbox-columns"
          >
            <el-checkbox
              v-for="column in tableHeader"
              :key="column.column_comment"
              :label="column.column_name"
              :disabled="column.fixed"
              >{{ column.column_comment }}</el-checkbox
            >
          </el-checkbox-group>
        </div>
      </div>
      <div class="main-right main">
        <div class="main-header">
          已显示列 &nbsp;({{ checkedTableColumns.length }})
        </div>
        <div class="main-container">
          <div class="fixed-columns">
            <div class="no-drag">
              <div
                class="item"
                v-for="(item, index) in fixed_columns"
                :key="index"
              >
                <i class="el-icon-lock"></i>
                <span>{{ item.column_comment }}</span>
              </div>
            </div>
          </div>
          <draggable
            v-if="custom_fixed_columns.length"
            v-model="custom_fixed_columns"
            class="drag-columns-1"
            draggable=".item-custom-fixed"
          >
            <template v-for="(item, index) in custom_fixed_columns">
              <div
                class="drag-item custom-fixed-columns item-custom-fixed"
                :key="index"
              >
                <div class="item-left">
                  <i class="el-icon-s-operation"></i>
                  <span>{{ item.column_comment }}</span>
                </div>
                <div class="item-right">
                  <i
                    class="el-icon-lock"
                    @click.stop.prevent="
                      toggleLockColumns(item, index, 'goUnlock')
                    "
                  ></i>
                  <i
                    class="el-icon-close"
                    @click.stop.prevent="cancelChecked(item)"
                  ></i>
                </div>
              </div>
            </template>
          </draggable>
          <draggable
            v-if="custom_drag_columns.length"
            v-model="custom_drag_columns"
            class="drag-columns"
            draggable=".item-custom-drag"
          >
            <template v-for="(item, index) in custom_drag_columns">
              <div class="drag-item item-custom-drag" :key="index">
                <div class="item-left">
                  <i class="el-icon-s-operation"></i>
                  <span>{{ item.column_comment }}</span>
                </div>
                <div class="item-right">
                  <i
                    class="el-icon-unlock"
                    @click.stop.prevent="
                      toggleLockColumns(item, index, 'goLock')
                    "
                  ></i>
                  <i
                    class="el-icon-close"
                    @click.stop.prevent="cancelChecked(item)"
                  ></i>
                </div>
              </div>
            </template>
          </draggable>
        </div>
        <div></div>
      </div>
    </div>
    <span slot="footer" class="dialog-footer">
      <el-button @click="customCancel">取 消</el-button>
      <el-button type="primary" @click="customConfirm">确 定</el-button>
    </span>
  </el-dialog>
</template>
<script>
import draggable from "vuedraggable";
export default {
  name: "columnSetDialog",
  components: {
    draggable
  },
  props: {
    filterShow: {
      type: Boolean,
      default: false
    },
    tableHeader: {
      type: Array,
      default: []
    }
  },
  data() {
    return {
      bindTableColumns: [], //与表格绑定的展示列
      checkedTableColumns: [],
      custom_fixed_columns: [], //客户自定义固定的列
      custom_drag_columns: [] //客户自定义拖拽的列
    };
  },
  computed: {
    fixed_columns: {
      get() {
        return this.tableHeader.filter(
          item => item.fixed && item.fixed == true
        );
      }
    }
  },
  watch: {
    checkedTableColumns: {
      handler(val) {
        console.log("可添加的列==>", val);
        if (val && val.length) {
          let column1 = [];
          let column2 = [];
          let temp = this.tableHeader.filter(item =>
            val.some(el => !item.fixed && el === item.column_name)
          );
          temp.forEach(item => {
            if (item.custom_fixed) {
              column1.push(item);
            } else {
              column2.push(item);
            }
          });
          this.custom_fixed_columns = column1 ? column1 : [];
          this.custom_drag_columns = column2 ? column2 : [];
        } else {
          this.custom_fixed_columns = [];
          this.custom_drag_columns = [];
        }
      },
      immediate: true
    }
  },
  created() {
    // console.log("this.tableHeader==>", this.tableHeader);
    this.checkedTableColumns = this.tableHeader
      .filter(item => item.show)
      .map(el => {
        return el.column_name;
      });
    // console.log("this.checkedTableColumns==>", this.checkedTableColumns);
  },
  methods: {
    toggleLockColumns(item, index, type) {
      if (type == "goLock") {
        item.custom_fixed = true;
        this.custom_fixed_columns.push(item);
        this.custom_drag_columns.splice(index, 1);
      } else {
        item.custom_fixed = false;
        this.custom_drag_columns.push(item);
        this.custom_fixed_columns.splice(index, 1);
      }
    },
    cancelChecked(item) {
      this.checkedTableColumns.splice(
        this.checkedTableColumns.indexOf(item.column_name),
        1
      );
    },
    customCancel() {
      this.$emit("customCancel");
    },
    customConfirm() {
      let arr = [
        ...this.fixed_columns,
        ...this.custom_fixed_columns,
        ...this.custom_drag_columns
      ];
      this.$emit("customConfirm", arr);
    }
  }
};
</script>
<style lang="less" scoped>
.dialog-main {
  width: 100%;
  display: flex;
  justify-content: space-between;
  .main {
    border: 1px solid #eee;
    border-radius: 4px;
    .main-header {
      height: 35px;
      line-height: 35px;
      background-color: #eee;
      font-weight: bold;
      padding-left: 10px;
    }
    .main-container {
      height: 350px;
      overflow-y: scroll;
      // background:yellow;
      .el-checkbox {
        min-width: 120px;
      }
      .checkbox-columns {
        padding: 10px;
        box-sizing: border-box;
      }
      .fixed-columns,
      .drag-columns {
        padding: 10px 10px 0 10px;
        box-sizing: border-box;
      }
      .drag-columns-1 {
        padding: 10px 10px 0 10px;
        box-sizing: border-box;
        background: rgba(238, 238, 238, 0.45);
        border-bottom: 1px solid rgba(211, 211, 211, 0.8);
      }
      .fixed-columns,
      .custom-fixed-columns {
        border-bottom: 1px solid #eee;
      }
      .drag-item {
        display: flex;
        align-items: center;
        justify-content: space-between;
        padding: 6px 5px;
        background: #eee;
        border-radius: 4px;
        margin-bottom: 10px;

        cursor: pointer;
        span {
          color: #000;
        }
        .el-icon-s-operation,
        .el-icon-unlock,
        .el-icon-lock,
        .el-icon-close {
          transform: scale(1.2);
        }
        .el-icon-unlock,
        .el-icon-lock {
          margin-right: 10px;
          color: #000;
        }
      }
      .no-drag {
        display: flex;
        opacity: 0.5;
        cursor: default;
        .item {
          padding: 6px 5px;
          background: #eee;
          border-radius: 4px;
          margin-bottom: 10px;
          margin-right: 5px;
        }
      }
    }
  }
  .main-left {
    width: 45%;
  }
  .main-right {
    width: 50%;
  }
}
</style>

引用组件：

<ColumnSetDialog
      :filterShow="filterShow"
      :tableHeader="tableHeader"
      @customCancel="customCancel"
      @customConfirm="customConfirm"
    ></ColumnSetDialog>