表格导出excel文件，excel文件导入到表格

曾不错吖

已于 2024-10-23 10:07:15 修改

阅读量102

点赞数

分类专栏：笔记部分文章标签： excel vue.js javascript

于 2022-10-26 14:15:35 首次发布

本文链接：https://blog.csdn.net/sisuiban/article/details/127531449

版权

笔记部分专栏收录该内容

30 篇文章 0 订阅

订阅专栏

<template>
  <div class="pathOne">
    <el-container class="page-main">
      <el-header height="auto">
        <div
          v-show="toolQuery.queryParamsShow"
          style="transition: all 0.3s; overflow: hidden"
        >
          <!-- 搜索条件  -->
          <el-form
            size="mini"
            class="search-form-inline"
            :model="toolQuery.queryParams"
            label-width="80px"
            ref="toolQuery"
          >
            <el-row>
              <el-col :span="6">
                <el-form-item label="宿舍名称" style="margin-bottom: 0">
                  <el-input
                    v-model="toolQuery.queryParams.name"
                    size="mini"
                    clearable
                  ></el-input>
                </el-form-item>
              </el-col>
              <el-col :span="6">
                <el-form-item label="状态" style="margin-bottom: 0">
                  <el-select
                    v-model="toolQuery.queryParams.type"
                    size="mini"
                    filterable
                    clearable
                    placeholder="请选择"
                  >
                    <el-option
                      v-for="item in stateOptions"
                      :key="item.value"
                      :label="item.label"
                      :value="item.value"
                    >
                    </el-option>
                  </el-select>
                </el-form-item>
              </el-col>
            </el-row>
          </el-form>
        </div>

        <div
          style="
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin: 5px 0;
          "
        >
          <div>
            <h2 style="font-weight: 900; color: #000">供应商人员查看</h2>
          </div>
          <!-- 操作按钮组 -->
          <el-button-group>
            <el-button type="primary" size="mini" @click="searchData"
              >搜索</el-button
            >
            <el-button size="mini" plain @click="ReFresh">刷新</el-button>

            <!-- import * as XLSX from 'xlsx' -->
            <el-button size="mini" type="primary" @click="changeExcel()"
              >导出excel</el-button
            >
            <el-button
              size="mini"
              @click="toolQuery.queryParamsShow = !toolQuery.queryParamsShow"
              >{{
                toolQuery.queryParamsShow ? "隐藏" : "显示"
              }}条件搜索</el-button
            >
          </el-button-group>
        </div>
      </el-header>
      <el-main class="main-table_container">
        <div
          :style="{
            height: toolQuery.queryParamsShow
              ? 'calc(100% - 320px)'
              : 'calc(100% - 300px)',
          }"
        >
          <!-- 
          :style="{
            height: toolQuery.queryParamsShow
              ? 'calc(100% - 320px)'
              : 'calc(100% - 250px)',
          }"

          300px
         -->
          <el-table
            :data="tableConfig.tableData.all"
            :element-loading-text="loadingTxt"
            v-loading="loading.table"
            size="mini"
            height="calc(100vh - 300px)"
            ref="tableData"
            id="exportTab"
            border
            header-cell-class-name="my-header-cell"
            @selection-change="mainTableSelectChange"
            @row-click="New_mainTableSelectChange"
            @row-dblclick="review"
            highlight-current-row
          >
            <!-- 目前还没有说需要勾选事件 -->
            <!-- <el-table-column
            type="selection"
            align="center"
            fixed
          ></el-table-column> -->
            <!-- <el-table-column type="index" width="50" fixed label="行数"> -->
            </el-table-column>
            <template
              v-for="(column, index) in tableConfig.tableColumn.filter(
                (item) => item.show
              )"
            >
              <el-table-column
                v-if="column.field === 'location'"
                :prop="column.field"
                :label="column.label"
                :min-width="column.width"
                :sortable="column.sortable ? true : false"
                show-overflow-tooltip
              >
                <template slot-scope="scope">
                  <span style="color: #4896dd; text-decoration: none">
                    <a href="javascript:void(0)">{{ scope.row.location }}</a>
                  </span>
                </template>
              </el-table-column>

              <el-table-column
                v-else-if="column.field === 'jobtime1'"
                :prop="column.field"
                :label="column.label"
                :min-width="column.width"
                :sortable="column.sortable ? true : false"
                show-overflow-tooltip
              >
                <template slot-scope="scope">
                  <span>
                    {{ dateFormat(scope.row.jobtime1) }}
                  </span>
                </template>
              </el-table-column>
              <el-table-column
                v-else-if="column.field === 'leaveDate'"
                :prop="column.field"
                :label="column.label"
                :min-width="column.width"
                :sortable="column.sortable ? true : false"
                show-overflow-tooltip
              >
                <template slot-scope="scope">
                  <span>
                    {{ dateFormat(scope.row.leaveDate) }}
                  </span>
                </template>
              </el-table-column>
              <el-table-column
                v-else
                :key="index"
                :label="column.label"
                :fixed="column.fixed"
                :width="column.width"
                :prop="column.field"
                :sortable="column.sortable ? true : false"
                show-overflow-tooltip
              >
              </el-table-column>
            </template>
            <el-table-column
              fixed="right"
              align="center"
              label="操作"
              width="120"
            >
              <template slot-scope="scope">
                <el-button type="text" size="mini">操作一</el-button>
              </template>
            </el-table-column>
          </el-table>
        </div>
      </el-main>
      
    </el-container>
  </div>
</template>
  
  <script>
import { SupplierPeople } from "../api/index";
import Cookie from "js-cookie";
import * as XLSX from "xlsx";

import { parseCookieStringToObject, UTformatDate } from "../utils/tools";

export default {
  filters: {
    //日期格式化
    dateFormat(val) {
      let value = "";
      if (val) {
        return UTformatDate(val);
      }
      return value;
    },
  },
  data() {
    return {
      dialogVisible: false,
      ruleForm: {
        userid: null,
        name: null,
        type: null,
        page: 1,
        limit: 50,
      },
      but_click: false,

      loadingTxt: "",
      loading: {
        table: false,
      },
      tableConfig: {
        OperateIsFold: true, //表格操作列是否折叠,默认折叠的
        tableData: {
          all: [],
          select: [],
          seleSingle: {},
        },
        tableColumn: [
          {
            field: "supplierName",
            show: true,
            label: "供应商",
            fixed: true,
            width: "120",
            sortable: true,
          },
          {
            field: "name",
            show: true,
            label: "姓名",
            width: "120",
            sortable: true,
          },
          {
            field: "sfid",
            show: true,
            label: "身份证",
            width: "100",
            sortable: true,
          },
          {
            field: "customerName",
            show: true,
            label: "用工单位",
            width: "120",
            sortable: true,
          },
          {
            field: "state",
            show: true,
            label: "在职状态",
            width: "100",
            sortable: true,
          },
          {
            field: "jobtime1",
            show: true,
            label: "入职时间",
            width: "120",
            sortable: true,
          },
          {
            field: "leaveDate",
            show: true,
            label: "离职时间",
            width: "160",
            sortable: true,
          },
          {
            field: "mode",
            show: true,
            label: "结算方式",
            width: "150",
            sortable: true,
          },
        ],
      },
      pageConfig: {
        //每页行数F_IsCutOff
        limit: 50,
        // 当前页
        page: 1,
        //总记录数
        count: 0,
      },
      toolQuery: {
        queryParams: {
          name: null,
          type: "全部",
          userid: "", //cookiues里面的id
        },
        queryParamsShow: true,
      },
      //部门的数组
      departmentOptions: [],
      stateOptions: [
        {
          value: "试用在职",
          label: "试用在职",
        },
        {
          value: "全部",
          label: "全部",
        },
      ],
    };
  },
  components: {},
  computed: {},
  created() {
  },
  mounted() {
  },
  methods: {
    changeExcel() {
      if (this.tableConfig.tableData.all.length === 0) {
        this.$notify.error({
          title: "提示",
          message: "表格内没数据",
        });
        return false;
      }
      const table = document.querySelector("#exportTab").cloneNode(true);
      
      table.removeChild(table.querySelector(".el-table__fixed-right"));
      
      table.removeChild(table.querySelector(".el-table__fixed"));

      // 获取表头和数据行 DOM 元素
      const headerDom = table.querySelector(".el-table__header-wrapper");
      const bodyDom = table.querySelector(".el-table__body-wrapper");

      // 获取操作列的索引
      let actionColumnIndex = null;
      const headerChildren = headerDom.childNodes[0].childNodes;
      for (let i = 0; i < headerChildren.length; i++) {
        if (headerChildren[i].innerText === "操作") {
          actionColumnIndex = i;
          break;
        }
      }

      // 移除操作列
      if (actionColumnIndex !== null) {
        const rows = bodyDom.querySelectorAll(".el-table__row");
        for (let i = 0; i < rows.length; i++) {
          rows[i].childNodes[actionColumnIndex].remove();
        }
        headerChildren[actionColumnIndex].remove();
      }

      // 将表头和数据行的所有子节点拼接起来
      const container = document.createElement("div");
      container.appendChild(headerDom);
      container.appendChild(bodyDom);

      // 使用 XLSX 库的工具函数将表头和数据行转换为 worksheet 对象
      const ws = XLSX.utils.table_to_sheet(container, {
        raw: true,
        display: true,
      });

      // 移除操作列对应的列数据
      if (actionColumnIndex !== null) {
        const range = XLSX.utils.decode_range(ws["!ref"]);
        for (let r = range.s.r; r <= range.e.r; r++) {
          const row = XLSX.utils.decode_row(
            XLSX.utils.encode_cell({ r: r, c: actionColumnIndex })
          );
          delete ws[XLSX.utils.encode_cell({ r: row, c: actionColumnIndex })];
        }
        range.e.c--;
        ws["!ref"] = XLSX.utils.encode_range(range);
      }

      // 将 worksheet 对象添加到 workbook 对象中
      const wb = XLSX.utils.book_new();
      XLSX.utils.book_append_sheet(wb, ws, "Sheet1");

      // 将 workbook 对象导出为 Excel 文件
      const wbout = XLSX.write(wb, {
        bookType: "xlsx",
        bookSST: true,
        type: "array",
      });
      try {
        const fileData = new Blob([wbout], {
          type: "application/octet-stream",
        });
        const a = document.createElement("a");
        a.href = URL.createObjectURL(fileData);
        a.download = "文件名.xlsx";
        a.click();
      } catch (e) {
        console.log("报错信息", e, wbout);
      }
    },
  },
};
</script>

在这里插入图片描述

excel表格导入到表格

<el-upload style="position: absolute; top: 5px; right: 0px" :on-change="handleChange"
                  accept=".xlsx,.xls" :auto-upload="false" :show-file-list="false">
                  <el-button color="#ddc233" size="small" round>导入模板</el-button>
                </el-upload>
<el-table :data="fileData" highlight-current-row size="small" ref="tableData" height="100%" border
            @selection-change="handleSelectionChange">
   <el-table-column v-for="(item, index) in columns" :key="index" :prop="item.prop" :label="item.label" />
</el-table>


<script setup>
import * as XLSX from "xlsx";
// dayjs插件：处理时间格式
import dayjs from "dayjs";
import isBetween from "dayjs/plugin/isBetween";
dayjs.extend(isBetween);

let fileData = ref([]);
const columns = ref([]);
const handleChange = async (file) => {
  console.log("这个执行了", file);

  const reader = new FileReader();
  reader.onload = (e) => {
    const data = new Uint8Array(e.target.result);
    const workbook = XLSX.read(data, { type: 'array' });

    // 获取第一个工作表
    const firstSheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[firstSheetName];

    // 将数据转换为 JSON
    const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });

    // 获取列名
    if (jsonData.length > 0) {
      columns.value = jsonData[0].map((col, index) => ({
        label: col,
        prop: `col${index}`
      }));

      // 获取行数据
      fileData.value = jsonData.slice(1).map((row, rowIndex) => {
        return row.reduce((acc, curr, colIndex) => {
          const cellAddress = `${String.fromCharCode(65 + colIndex)}${
            rowIndex + 2
          }`;
          const cell = worksheet[cellAddress];
          // 处理时间格式
          if (cell && cell.t === "d") {
            acc[`col${colIndex}`] =dayjs(curr).add(1, "day").format("YYYY-MM-DD");
          } else {
            acc[`col${colIndex}`] = curr;
          }
          return acc;
        }, {});
      });
    }
  };
  reader.readAsArrayBuffer(file.raw);
};
</script>