简单excel表格编辑神器 vxe-table

PBitW
469 阅读7分钟

菜鸟所在的公司很多流程都需要用到excel，因为之前没有信息化，所以现在很多功能都是要上传excel识别，然后对识别内容进行编辑！

这部分菜鸟之前写过一篇文章：vue3 导出数据为 excel 文件 + 导入并识别excel文件

上述这篇文章主要是实现的json数据导出为excel，有讲编辑功能，当时想的是直接用el-input实现，并没有实现完整。后面发现了vxe-table，但没有切实的使用，所以没有更新那篇文章。这次项目中再次需要用到excel识别后编辑的功能，所以特来补充！

vxe UI 介绍

vxe-table 是 vxe UI 里面的 table组件，很实用，其他的组件需要各位读者去开发，因为菜鸟没怎么使用。菜鸟感觉这个基本和 element plus 差不多，只是功能有的更强、有的又偏弱，基本上 element plus 的都可以替代 vxe UI 里面的组件，但菜鸟感觉 vxe UI 的：表单、vxe-grid各位读者可以好好看看，因为这两个都有通过数据结构生成内容的能力，对喜欢jsx的帮助应该很大；然后其他组件的话，感觉也比较有用，有element plus里面没有的一些功能！

vxe UI 网址：vxeui.com

为什么 vxe-table 实用？

vxe-table 实用不是因为别的，就因为其使用方式和 element plus 的 el-table 一模一样，但是不同的是，其可以对每一行数据进行编辑！

table_edit_footer_Immediately.gif

安装

这里菜鸟感觉有个槽点，按需要引入很奇怪（感觉很超出了认知，是你要用什么自己去引入的这种按需引入，而不是element plus那种自动的），主要是使用vxe-table以及vxe-table中会用到的vxe-select、vxe-input，大部分只需要引入一点就行了！

具体见：vxeui.com/#/start/use…

菜鸟的main.js部分

// 导入vxe-table --> 必须导入VxeTooltip不然有问题，其他随便
// 导入vxe ui的组件 --> 必须导入VxeUI不然没法使用VxeUI.setI18n等方法
// 导入VxeSelect --> 必须导入VxeInput，不然样式不对，因为VxeSelect中使用了VxeInput
import { VxeUI, VxeSelect, VxeTooltip, VxeInput } from "vxe-pc-ui";
import { VxeTable, VxeColumn, VxeColgroup, VxeGrid, VxeToolbar } from "vxe-table";

// 导入主题变量，也可以重写主题变量
import "vxe-table/styles/cssvar.scss";
import "vxe-pc-ui/styles/cssvar.scss";

// 导入默认的语言
import zhCN from "vxe-pc-ui/lib/language/zh-CN";
VxeUI.setI18n("zh-CN", zhCN);
VxeUI.setLanguage("zh-CN");

function lazyVxe(app) {
  app.use(VxeSelect);
  app.use(VxeInput);
  app.use(VxeTooltip);
  app.use(VxeTable);
  app.use(VxeColumn);
  app.use(VxeColgroup);
  app.use(VxeGrid);
  app.use(VxeToolbar);
}

app.use(lazyVxe);

实际应用

菜鸟的界面

image.png

代码

实现代码 -- 父组件

<script setup>
import { upload_or_download_path } from "@/enums/sampleType.js";
import { isEqual, omit } from "lodash-es";
import { sleep } from "@/utils/coroutine.js";
import SampleNucleinCol from "@/components/Lims/SampleCommon/SampleInput/cols/SampleNucleinCol.vue";
import SampleCellCol from "@/components/Lims/SampleCommon/SampleInput/cols/SampleCellCol.vue";
import SampleLibCol from "@/components/Lims/SampleCommon/SampleInput/cols/SampleLibCol.vue";
import SampleSublibraryCol from "@/components/Lims/SampleCommon/SampleInput/cols/SampleSublibraryCol.vue";
import _ from "lodash-es";

// el-dialog 里面使用 vxe-table 需要设置z-index，不然编辑的弹出会被盖住，导致没法编辑
import domZIndex from "dom-zindex";
domZIndex.setCurrent(10000);

const props = defineProps({
  sendSampleType: {
    type: [String, Number],
    default: -1
  },
  canEdit: {
    type: Boolean,
    default: true
  },
  canSelect: {
    type: Boolean,
    default: true
  },
  height: {
    type: [String, Number],
    default: "600"
  },
  otherRules: {
    type: Object,
    default: () => ({})
  }
});

const emit = defineEmits(["selectChangeEvent", "showSublib", "selectAllEvent"]);

const tableRef = ref();
const tableData = defineModel("tableData", { default: [] });

// 设置能否编辑
const editConfig = ref();
if (props.canEdit) {
  editConfig.value = {
    trigger: "click",
    mode: "cell"
  };
} else {
  editConfig.value = {
    trigger: "none",
    mode: "cell"
  };
}

const curStrategy = computed(() => {
  return (
    upload_or_download_path.find(
      (v) => v.value.toString() === `${toValue(props.sendSampleType)}`
    ) ?? {}
  );
});

// 新增一行
const addRow = async () => {
  const newRow = {
    isChecked: true
  };
  tableData.value.push(newRow);
  await nextTick();
  const $table = tableRef.value;
  if ($table) {
    if (props.canEdit) {
      await $table.setEditRow(newRow, "sampleName");
      unref(tableRef).setCheckboxRow(newRow, true);
    }
    await sleep(0);
    $table.scrollToRow(newRow);
  }
};

// 校验规则
const useRules = computed(() => {
  if (_.isFunction(curStrategy.value.rules)) {
    let rules = curStrategy.value.rules(tableData.value);
    return {
      ...rules,
      ...props.otherRules
    };
  }
  return { ...props.otherRules };
});

// 获取选中的数据
const getSelectedList = async () => {
  const selectedList = await unref(tableRef).getCheckboxRecords();
  return selectedList.map((v) =>
    omit(v, ["_X_ROW_KEY", "isChecked", "createUserId", "createTime", "updateUserId", "updateTime"])
  );
};

// 设置选中列表
const addSelectedList = (_sampleList) => {
  unref(tableRef).setCheckboxRow(_sampleList, true);
};

// 清空选中
const clearSelectEvent = () => {
  const $table = tableRef.value;
  if ($table) {
    $table.clearCheckboxRow();
  }
};

// 清空数据
const resetSelectedList = () => {
  tableData.value = [];
};

// 校验方法 -- true 表示全部校验，false 表示校验变动行
const fullValidEvent = async (bool = true) => {
  const $table = tableRef.value;
  if ($table) {
    const errMap = await $table.validate(bool);
    return errMap;
  }
};

// 删除行
const removeRow = async (row) => {
  tableData.value = tableData.value.filter((item) => !isEqual(item, row));
};

// 获取行id
const getRowIndex = (row) => {
  const $table = tableRef.value;
  if ($table) {
    return $table.getRowIndex(row);
  }
};

// 子文库
const showSublib = (row) => {
  emit("showSublib", row);
};

// 选择事件
const selectChangeEvent = (row) => {
  console.log(row);
  emit("selectChangeEvent", row.records);
};
const selectAllEvent = (checked) => {
  emit("selectAllEvent", checked);
};

// 暴露方法
defineExpose({
  getSelectedList,
  addSelectedList,
  fullValidEvent,
  addRow,
  clearSelectEvent,
  getRowIndex,
  resetSelectedList,
  removeRow
});
</script>

<template>
  <vxe-table
    border
    ref="tableRef"
    show-overflow
    :edit-config="editConfig"
    :data="tableData"
    :height="height"
    :row-config="{ isHover: true }"
    :checkbox-config="{ checkField: 'isChecked' }"
    :edit-rules="useRules"
    @checkbox-change="selectChangeEvent"
    @checkbox-all="selectAllEvent"
  >
    <template #empty>
      <el-alert type="info" :closable="false">
        当前尚未添加{{ curStrategy.label }}样本数据,请点击右上方的下载{{
          curStrategy.label
        }}模板按钮下载后并填写,填写完成后点击上传{{
          curStrategy.label
        }}模板按钮上传.或点击右上方的新增一行按钮添加{{ curStrategy.label }}样本数据手动添加
      </el-alert>
    </template>

    <vxe-column v-if="canSelect" fixed="left" type="checkbox" width="40"></vxe-column>

    <!-- 注意，因为菜鸟这里面的列可能有很多变化，所以有很多插槽 -->
    <slot name="StorageLocationCol"></slot>

    <template v-if="curStrategy.label === '组织' || curStrategy.label === '核酸'">
      <SampleNucleinCol :can-edit="canEdit">
        <!-- 需要先插入，然后再暴露 -->
        <template #userCanAddNucleninCol>
          <slot name="userCanAddNucleninCol"></slot>
        </template>
      </SampleNucleinCol>
    </template>

    <template v-if="curStrategy.label === '单细胞'">
      <SampleCellCol :can-edit="canEdit">
        <template #userCanAddCellCol>
          <slot name="userCanAddCellCol"></slot>
        </template>
      </SampleCellCol>
    </template>

    <template v-if="curStrategy.label === '文库'">
      <SampleLibCol :can-edit="canEdit">
        <template #userCanAddLibCol>
          <slot name="userCanAddLibCol"></slot>
        </template>
      </SampleLibCol>
    </template>

    <template v-if="curStrategy.label === '子文库'">
      <slot name="sublibFristCol"></slot>
      <SampleSublibraryCol :can-edit="canEdit">
        <template #userCanAddSublibcol>
          <slot name="userCanAddSublibcol"></slot>
        </template>
      </SampleSublibraryCol>
    </template>

    <slot name="userCanAddSamplelist"></slot>

    <vxe-column
      field="remark"
      title="备注"
      min-width="200"
      :edit-render="{ name: 'textarea' }"
    ></vxe-column>

    <vxe-column
      v-if="canEdit"
      field="action"
      title="操作"
      fixed="right"
      :width="curStrategy.label === '文库' ? '160' : '80'"
    >
      <template #default="{ row }">
        <template v-if="curStrategy.label === '文库'">
          <el-button type="primary" text @click="showSublib(row)"
            >子文库<span class="numbericon">{{
              row?.orderSampleSubLibraryDTOList?.length || 0
            }}</span></el-button
          >
        </template>
        <el-button type="danger" :icon="Delete" circle plain @click="removeRow(row)" />
      </template>
    </vxe-column>
  </vxe-table>
</template>

<style lang="scss" scoped>
.numbericon {
  border: 1px solid var(--el-color-primary);
  border-radius: 50%;
  display: inline-block;
  font-size: 9px;
  width: 15px;
  height: 15px;
  text-align: center;
  line-height: 15px;
  margin-left: 5px;
  margin-top: 2px;
}
</style>

这里的upload_or_download_path就是个枚举，用到了策略模式，不然每一个都要写，很麻烦：

import * as sampleSend from "@/api/sample/sampleSend";
import * as sampleInput from "@/api/sample/sampleInput";
import * as sampleListByOrderId from "@/api/sample/sampleList";
import { sublibEnumsNum } from "@/components/Lims/SampleCommon/SampleInput/common";

const rulesObject = {
  // 组织/核酸
  sampleName: [
    {
      required: true,
      message: "请输入样本名称"
    }
  ],
  latinSpeciesName: [
    {
      required: true,
      message: "请输入拉丁学名"
    }
  ],
  ……
};

export const upload_or_download_path = [
  {
    label: "组织",
    value: 1,
    height: "600px",
    downloadApi: () => sampleInput.orderSampleDownload(1),
    uploadApi: sampleInput.orderSampleUpload,
    associated: sampleSend.sampleAssociatedOrder,
    detail: sampleListByOrderId.orderSampleList,
    itemName: "orderSampleDTOList",
    serverDataToLocal: (res) => {
      let data = res.data.records;
      for (let i of data) {
        i.infective = i.infective + "";
      }
      return data;
    },
    rules: function () {
      return rulesObject;
    }
  },
  {
    label: "核酸",
    value: 2,
    height: "600px",
    downloadApi: () => sampleInput.orderSampleDownload(2),
    uploadApi: sampleInput.orderSampleUpload,
    associated: sampleSend.sampleAssociatedOrder,
    detail: sampleListByOrderId.orderSampleList,
    itemName: "orderSampleDTOList",
    serverDataToLocal: (res) => {
      let data = res.data.records;
      for (let i of data) {
        i.infective = i.infective + "";
      }
      return data;
    },
    rules: function () {
      return rulesObject;
    }
  },
  {
    label: "文库",
    value: 3,
    height: "500px",
    downloadApi: sampleInput.orderSampleLibraryDownload,
    uploadApi: sampleInput.orderSampleLibraryUpload,
    associated: sampleSend.sampleAssociatedOrderSampleLibrary,
    detail: sampleListByOrderId.orderSampleListLibrary,
    itemName: "orderSampleLibraryDTOList",
    serverDataToLocal: (res) => {
      let data = res.data.records;
      for (let i of data) {
        i.baseBalance = i.baseBalance + "";
        i.cyclized = i.cyclized + "";
        for (let j of i.orderSampleSubLibraryDTOList) {
          j.baseBalance = j.baseBalance + "";
          j.libraryEndFivePhosphorylation = j.libraryEndFivePhosphorylation + "";
        }
      }
      return data;
    },
    rules: function (data) {
      const sampleName = [];
      sampleName.push({
        required: true,
        message: "请输入文库名称"
      });
      sampleName.push({
        validator(e) {
          // console.log(e);
          let sampleNameArr = {};
          for (let i = 0; i < data.length; i++) {
            if (sampleNameArr[data[i]?.sampleName]) {
              sampleNameArr[data[i].sampleName]++;
            } else {
              sampleNameArr[data[i].sampleName] = 1;
            }
          }
          if (sampleNameArr[e.cellValue] > 1) {
            return new Error("存在重名的文库名称");
          }
        }
      });
      return Object.assign({}, rulesObject, {
        sampleName: sampleName,
        latinSpeciesName: [
          {
            required: true,
            message: "请输入来源物种"
          }
        ]
      });
    }
  },
  {
    label: "单细胞",
    value: 4,
    height: "450px",
    downloadApi: sampleInput.orderSampleCellDownload,
    uploadApi: sampleInput.orderSampleCellUpload,
    associated: sampleSend.sampleAssociatedOrderSampleCell,
    detail: sampleListByOrderId.orderSampleListCell,
    itemName: "orderSampleCellDTOList",
    serverDataToLocal: (res) => {
      let data = res.data.records;
      for (let i of data) {
        i.sampleRunOut = i.sampleRunOut + "";
        i.sampleHasBackup = i.sampleHasBackup + "";
      }
      return data;
    },
    rules: function () {
      return Object.assign({}, rulesObject, {
        latinSpeciesName: [
          {
            required: true,
            message: "请输入来源物种"
          }
        ]
      });
    }
  },
  {
    label: "子文库",
    value: sublibEnumsNum,
    height: "500px",
    detail: sampleListByOrderId.orderSampleListSubLibrary,
    serverDataToLocal: (res) => {
      let data = res.data;
      for (let i of data) {
        i.baseBalance = i.baseBalance + "";
        i.libraryEndFivePhosphorylation = i.libraryEndFivePhosphorylation + "";
      }
      return data;
    },
    rules: function () {
      return Object.assign({}, rulesObject, {
        sampleName: [
          {
            required: true,
            message: "请输入文库名称"
          }
        ]
      });
    }
  }
];

实现代码 -- 子组件

子组件都是列之类的，这里就只放一个了SampleCellCol.vue，作为例子

<script setup>
import { dictData, format, activeVal, inactiveVal } from "../common";
import { serverDateFormat } from "@/utils/date.js";
import DictTag from "@/components/DictTag/index.vue";

defineProps({
  canEdit: {
    type: Boolean,
    default: true
  }
});

const {
  send_sample_order_sample_preservation_status,
  send_sample_order_sample_preservation_medium,
  send_sample_order_single_cell_platform,
  send_sample_order_single_cell_project_type,
  send_sample_order_seq_platform,
  send_sample_order_single_cell_hand_method
} = dictData;
</script>

<template>
  <vxe-column
    field="sampleName"
    title="样本名称"
    width="160"
    :edit-render="{ name: 'input' }"
  ></vxe-column>

  <slot name="userCanAddCellCol"></slot>

  <vxe-column
    field="sampleCount"
    title="样品数量"
    width="160"
    :edit-render="{ name: 'input' }"
  ></vxe-column>
  <vxe-column
    field="latinSpeciesName"
    title="来源物种"
    width="160"
    :edit-render="{ name: 'input' }"
  ></vxe-column>
  <vxe-column
    field="organOrigin"
    title="组织来源（部位）"
    width="180"
    :edit-render="{ name: 'input' }"
  ></vxe-column>
  <vxe-column field="samplePreservationStatus" title="样本保存状态" width="200" :edit-render="{}">
    <template #edit="{ row }">
      <vxe-select
        v-model="row.samplePreservationStatus"
        :options="send_sample_order_sample_preservation_status"
      >
        <template #option="{ option }">
          <dict-tag
            :options="send_sample_order_sample_preservation_status"
            :value="option.value"
          ></dict-tag>
        </template>
      </vxe-select>
    </template>
    <template #default="{ row }">
      <span>{{
        format(row, "samplePreservationStatus", "send_sample_order_sample_preservation_status")
      }}</span>
    </template>
  </vxe-column>
  <vxe-column field="samplePreservationMedium" title="样本保存介质" width="200" :edit-render="{}">
    <template #edit="{ row }">
      <vxe-select
        v-model="row.samplePreservationMedium"
        :options="send_sample_order_sample_preservation_medium"
      >
        <template #option="{ option }">
          <dict-tag
            :options="send_sample_order_sample_preservation_medium"
            :value="option.value"
          ></dict-tag>
        </template>
      </vxe-select>
    </template>
    <template #default="{ row }">
      <span>{{
        format(row, "samplePreservationMedium", "send_sample_order_sample_preservation_medium")
      }}</span>
    </template>
  </vxe-column>
  <vxe-column field="sampleGatherTime" title="样本采集时间" width="240" :edit-render="{}">
    <template #edit="{ row }">
      <el-date-picker
        size="small"
        v-model="row.sampleGatherTime"
        type="datetime"
        :value-format="serverDateFormat"
        clearable
        placeholder="样本采集时间"
      />
    </template>
    <template #default="{ row }">
      <span>{{ row.sampleGatherTime }}</span>
    </template>
  </vxe-column>
  <vxe-column field="sampleHasBackup" title="样本是否有备份" width="160" :edit-render="{}">
    <template #edit="{ row }">
      <el-switch
        v-model="row.sampleHasBackup"
        :active-value="activeVal"
        :inactive-value="inactiveVal"
      ></el-switch>
    </template>
    <template #default="{ row }">
      <el-switch
        v-model="row.sampleHasBackup"
        :disabled="!canEdit"
        :active-value="activeVal"
        :inactive-value="inactiveVal"
      ></el-switch>
    </template>
  </vxe-column>
  <vxe-column field="sampleRunOut" title="样本是否可用完" width="160" :edit-render="{}">
    <template #edit="{ row }">
      <el-switch
        v-model="row.sampleRunOut"
        :active-value="activeVal"
        :inactive-value="inactiveVal"
      ></el-switch>
    </template>
    <template #default="{ row }">
      <el-switch
        v-model="row.sampleRunOut"
        :disabled="!canEdit"
        :active-value="activeVal"
        :inactive-value="inactiveVal"
      ></el-switch>
    </template>
  </vxe-column>
  <vxe-column field="processWay" title="处理方式" width="160" :edit-render="{}">
    <template #edit="{ row }">
      <vxe-select v-model="row.processWay" :options="send_sample_order_single_cell_hand_method">
        <template #option="{ option }">
          <dict-tag
            :options="send_sample_order_single_cell_hand_method"
            :value="option.value"
          ></dict-tag>
        </template>
      </vxe-select>
    </template>
    <template #default="{ row }">
      <span>{{ format(row, "processWay", "send_sample_order_single_cell_hand_method") }}</span>
    </template>
  </vxe-column>
  <vxe-column
    field="expectCellCount"
    title="预期细胞（核）数量"
    width="180"
    :edit-render="{ name: 'input' }"
  ></vxe-column>
  <vxe-column field="singleCellPlatform" title="单细胞平台" width="160" :edit-render="{}">
    <template #edit="{ row }">
      <vxe-select
        v-model="row.singleCellPlatform"
        :options="send_sample_order_single_cell_platform"
      >
        <template #option="{ option }">
          <dict-tag
            :options="send_sample_order_single_cell_platform"
            :value="option.value"
          ></dict-tag>
        </template>
      </vxe-select>
    </template>
    <template #default="{ row }">
      <span>{{ format(row, "singleCellPlatform", "send_sample_order_single_cell_platform") }}</span>
    </template>
  </vxe-column>
  <vxe-column field="singleCellProjectType" title="单细胞项目类型" width="200" :edit-render="{}">
    <template #edit="{ row }">
      <vxe-select
        v-model="row.singleCellProjectType"
        :options="send_sample_order_single_cell_project_type"
      >
        <template #option="{ option }">
          <dict-tag
            :options="send_sample_order_single_cell_project_type"
            :value="option.value"
          ></dict-tag>
        </template>
      </vxe-select>
    </template>
    <template #default="{ row }">
      <span>{{
        format(row, "singleCellProjectType", "send_sample_order_single_cell_project_type")
      }}</span>
    </template>
  </vxe-column>
  <vxe-column field="seqPlatform" title="测序平台" width="180" :edit-render="{}">
    <template #edit="{ row }">
      <vxe-select v-model="row.seqPlatform" :options="send_sample_order_seq_platform">
        <template #option="{ option }">
          <dict-tag :options="send_sample_order_seq_platform" :value="option.value"></dict-tag>
        </template>
      </vxe-select>
    </template>
    <template #default="{ row }">
      <span>{{ format(row, "seqPlatform", "send_sample_order_seq_platform") }}</span>
    </template>
  </vxe-column>
  <vxe-column
    field="seqReadsNum"
    title="测序reads数"
    width="160"
    :edit-render="{ name: 'input' }"
  ></vxe-column>
</template>

这里菜鸟感觉不是很难，就按照element plus的去搞就行，主要是需要找到对应的文档，具体文档在这里：

  1. 可编辑 - 插槽式：vxeui.com/#/component…
  2. 可编辑 - 配置式：vxeui.com/#/component…
  3. 校验数据：vxeui.com/#/component…
  4. 设置选择项：vxeui.com/#/component…

复杂表单

如果是复杂表单，后端识别出来，只要给过来的数据结构没啥问题，感觉都可以使用vxe-table实现编辑！

然后如果是简单excel（没有合并的列），甚至都不用后端识别，菜鸟这里有个办法，见文章：工作+学习 常用的一些东西

avatar
文章
阅读
粉丝