一、产品需求:前端搜索出来的表格是什么样，导出的excel表格就要长什么样

二、插件版本

"exceljs": "^4.4.0",
"file-saver": "^2.0.5",
"dayjs": "^1.11.13",

三、安装

npm install exceljs
npm install file-saver

四、组件封装

<template>
    <div>
        <!-- 批量导入 -->
        <el-button type="success" plain icon="el-icon-check" size="mini" @click="openImportDialog"
          :disabled="disabled">批量导入邮寄</el-button>
        <el-dialog title="批量导入" :visible.sync="open_import" destroy-on-close @close="cancel_import">
          <el-row :gutter="40">
            <el-col :span="24" align="center">
              <div class="custom-dragger">
                <el-upload ref="uploadRef" accept=".xlsx, .xls" :file-list="fileList" :on-change="handleFileChange" action="#"
                  :on-remove="handleRemove" :on-exceed="handleExceed" :auto-upload="false" drag destroy-on-close>
                  <i class="el-icon-upload"></i>
                  <div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
                  <template #tip>
                    <div class="el-upload__tip text-center">
                      <span class="el-upload__tip">仅允许导入xls、xlsx格式文件。</span>
                      <el-button type="primary" plain icon="Download" @click="exportToExcel">下载当前数据表格</el-button>
                    </div>
                  </template>
                </el-upload>

              </div>
            </el-col>
          </el-row>
          <template v-if="uploadDataTable.length > 0">
            <h2>
              导入订单数据确认
              <span class="small_tip">{{
                upload.isRepeat ? upload.tipRepeat : upload.tipMsg
                }}</span>
            </h2>
            <el-table :data="uploadDataTable" border height="300" style="width: 100%" :row-class-name="tableRowClassName">
              <el-table-column label="序号" align="center" key="index" prop="index" />
              <el-table-column label="商品名称" align="center" key="name" prop="name" />
              <el-table-column label="商品来源" align="center" key="from" prop="from" />
              <el-table-column label="创建时间" align="center" key="createTime" prop="createTime">
              </el-table-column>
            </el-table>
            <pagination v-show="uploadTotal > 0" :total="uploadTotal" :page.sync="uploadQueryParams.pageNum"
              :limit.sync="uploadQueryParams.pageSize" @pagination="getuploadTist" small />
          </template>
          <div slot="footer" class="dialog-footer">
            <el-button type="primary" @click="submitForm_import()">确 定</el-button>
            <el-button @click="cancel_import">取 消</el-button>
          </div>
        </el-dialog>
    </div>
</template>
...
<script>
//引入插件
import * as XLSX from "xlsx";
import ExcelJS from "exceljs";
import { saveAs } from "file-saver";
import dayjs from "dayjs";

export default {
    ...
    data() {
      tableList:[],//表格数据
      ids:[],//选中的数据
      open_import:false,
      uploadData: [],
      uploadDataTable: [],
      uploadQueryParams: {
        pageNum: 1,
        pageSize: 10,
      },
      fileList: [], //上传文件列表
      uploadTotal: 0,
      upload: {
        // 是否显示弹出层（用户导入）
        open: false,
        // 弹出层标题（用户导入）
        title: "",
        // 是否禁用上传
        isUploading: false,
        isRepeat: false, //上传的表格是否重复
        tipMsg: "（已为您自动去重，请确认数据无误后再进行提交！）",
        tipRepeat: "（您导入的数据存在重复数据，请调整后重新上传！）",
      },
      orderNos: [],
      disabled: true,
    },
    methods: {
        // 前端生成并导出表格
        exportToExcel() {
          //表格若有勾选的数据，导出勾选的数据，若没有勾选导出当前页全部数据，否则提示“暂无数据导出”
          let exportData = this.tableList;
          if (this.ids.length > 0) {
            exportData = this.tableList.filter((item) => this.ids.includes(item.id))
          } else {
            exportData = this.tableList
          }
          if (exportData.length == 0) {
            this.$message.error('暂无数据导出');
            return
          }

          //创建excel工作表
          const workbook = new ExcelJS.Workbook();
          const worksheet = workbook.addWorksheet("Sheet 1");

          // 设置表头样式
          const headerStyle = {
            font: { bold: true, color: { rgb: "000000" }, size: 13 }, // 灰色加粗
            fill: {
              type: "pattern",
              pattern: "darkTrellis",
              fgColor: { argb: "808080" },
              bgColor: { argb: "808080" },
            }, // 背景色填充
            alignment: { horizontal: "center" }, // 水平居中
          };

          // 设置单元格样式
          const cellStyle = {
            alignment: { horizontal: "center" }, // 水平居中
          };

          // 设置列宽
          const columnWidths = {
            A: 10,
            B: 20,
            C: 20,
            C: 22,
          };

          // 遍历columnWidths对象并设置列宽
          Object.keys(columnWidths).forEach((columnLetter) => {
            const column = worksheet.getColumn(columnLetter);
            if (column) {
              column.width = columnWidths[columnLetter];
              column.style = { alignment: { horizontal: "center" } };
            }
          });
          // 添加表头
          let header = [
            "序号",
            "商品名称",
            "商品来源",
            "创建时间",
          ];
          const headerRow = worksheet.addRow(header);
          headerRow.eachCell({ includeEmpty: true }, (cell, colNumber) => {
            cell.style = headerStyle; // 应用样式到每个单元格
          });
          // 冻结首行
          worksheet.views = [
            {
              state: "frozen",
              xSplit: 0,
              ySplit: 1,
            },
          ];

          // 添加示例数据
          let data = [];
          exportData.forEach((item,index) => {
            data.push([
              index,
              item.name,
              item.from,
              item.createTime
            ])
          })
          data.forEach((row) => {
            worksheet
              .addRow(row)
              .eachCell({ includeEmpty: true }, (cell, colNumber) => {
                cell.style = cellStyle; // 应用样式到每个单元格
              });
          });

          // 设置下拉
          let company_name_arr = ['罗森尼娜','仟吉','元祖'];
          for (let i = 1; i <= 1000; i++) {
            if (company_name_arr.length > 0) {
              worksheet.getCell("T" + i).dataValidation = {
                type: "list",
                allowBlank: false,
                formulae: ['"' + company_name_arr.toString() + '"'],
                operator: "notEqual",
                showErrorMessage: true,
                errorStyle: "error",
                errorTitle: "提示",
                error: "请按照下拉选择食品来源公司",
              };
            }
          }

          // 将工作簿写入到一个 Blob 对象
          workbook.xlsx.writeBuffer().then((buffer) => {
            const blob = new Blob([buffer], {
              type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            });

            // 使用 file-saver 触发下载
            saveAs(blob, `商品清单${parseTime(new Date())}.xlsx`);
          });
        },
        //打开导入弹窗
        openImportDialog() {
          this.open_import = true
        },
        // 确认导入
        submitForm_import() {
          this.$emit('submitForm_import', this.uploadData)//调用接口
          this.open_import = false
          this.handleRemove()
        },
        // 取消导入
        cancel_import() {
          this.open_import = false;
          this.handleRemove()
        },
        // 当超出限制时，执行的钩子函数
        handleExceed(files, uploadFiles) {
          let getFiles = files;
          let getUploadFiles = uploadFiles[0];
          handleRemove();
          this.fileList = getFiles;
          handleFileChange(getUploadFiles);
        },
        // 添加文件
        handleFileChange(response) {
          this.fileList = [response];
          let raw = response.raw;
          // console.log(raw);
          const reader = new FileReader();
          reader.onload = (e) => {
            const data = e.target.result;
            const workbook = XLSX.read(data, { type: "binary", cellDates: true });
            const sheetName = workbook.SheetNames[0];
            const sheet = workbook.Sheets[sheetName];
            const json = XLSX.utils.sheet_to_json(sheet);
            // 处理解析后的数据
            let arr = [], isEmpty = false;
            if (json.length > 0) {
              json.forEach((item) => {
                let obj = {};
                for (const key in item) {
                  switch (key) {
                    case "序号号":
                      obj.index = item[key];
                      break;
                    case "商品名称":
                      obj.name = item[key];
                      break;
                    case "商品来源":
                    obj.from = item[key];
                      break;
                    case "创建时间":
                      let getTime = this.dealEXcelTime(item[key]);
                      obj.fromTime = dayjs(getTime);
                      break;
                    default:
                      break;
                  }
                }
                arr.push(obj);
              });
            }
            arr.forEach((item) => {
              if (!item.index || !item.name || !item.from || !item.createTime) {
                isEmpty = true
              }
            })
            if (isEmpty) {
              this.$message.error("表格有空白项，请检查后再次提交！");
              return false
            }
            this.uploadData = arr;
            this.uploadData = this.markedArray(this.uploadData);
            this.uploadTotal = this.uploadData.length;
            this.uploadDataTable = this.uploadData.slice(
              0,
              this.uploadQueryParams.pageSize
            );
            if (this.upload.isRepeat == true) {
              this.$message.error("您导入的数据存在重复数据，请调整后重新上传！");
            }
          };
          reader.readAsBinaryString(raw);
        },
        // 移除文件
        handleRemove() {
          this.uploadData = [];
          this.uploadDataTable = [];
          this.uploadTotal = 0;
          this.fileList = [];
          this.upload.isRepeat = false;
        },
        // 处理表格时间
        dealEXcelTime(dateStr) {
          dateStr = dateStr.toString();
          if (dateStr) {
            if (dateStr?.includes("23:59:17")) {
              dateStr = dayjs(dateStr).add(43, "second");
            } else {
              dateStr = dayjs(dateStr).add(44, "second");
            }
            // 如果需要可以格式化成需要的格式
            const dayObj = dayjs(dateStr.toString());
            if (dayObj.isValid()) {
              dateStr = dayObj.format("YYYY-MM-DD HH:mm");
            }
            return dateStr;
          }
        },
        // 标记重复项，去重
        markedArray(array) {
          let newArr = [];
          let getArr = JSON.parse(JSON.stringify(array));
          getArr.forEach((item) => {
            // 唯一key值
            item.key = item.index;
          });
          newArr = getArr.reduce((accumulator, item) => {
            // 创建一个临时对象来存储已经添加到累加器中的对象的id
            const existingItem = accumulator.find(
              (accumulatedItem) => accumulatedItem.key === item.key
            );
            if (!existingItem) {
              // 如果当前项的key不在累加器中，将其添加到累加器中
              accumulator.push({ ...item, isDuplicate: "否" });
            }
            // else {
            //   accumulator.push({ ...item, isDuplicate: "是" });
            //   upload.isRepeat = true;
            // }
            return accumulator;
          }, []);
          return newArr;
        },
        // 表格预览，前端分页
        getuploadTist() {
          let baseCount = this.uploadQueryParams.pageSize;
          if (this.uploadQueryParams.pageNum == 1) {
            this.uploadDataTable = uploadData.value.slice(0, baseCount);
          } else {
            this.uploadDataTable = this.uploadData.slice(
              baseCount * (this.uploadQueryParams.value - 1),
              baseCount * this.uploadQueryParams.value
            );
          }
        },
    }
}
</script>

五、参考来源

ExcelJS文档