luckyexcel 编辑预览excel文件

luckyexcel 编辑预览excel文件

支持后端传文件流预览编辑，也支持选择本地文件编辑预览

看效果

上代码

<template>
  <div style="margin: 30px">
    <div class="button-box2">
      <div>
        <div style="color: red">当前弹框不要修改列名称，如需修改，请去列管理页面修改列。</div>
      </div>

      <div>
        <a-space>
          <!-- <input id="uploadBtn" type="file" @change="loadExcel3" /> -->
          <a-button type="primary" @click="downExcel">导出模板</a-button>
          <a-upload
            v-model:file-list="data.fileList"
            accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            :customRequest="customUpload"
            name="file"
            :multiple="false"
            :showUploadList="false"
          >
            <a-button :loading="data.clientLoading" type="primary">客户端导入</a-button>
          </a-upload>
          <a-button
            type="primary"
            :loading="data.serverLoading"
            @click="() => (modal.visible = true)"
            >服务端导入</a-button
          >
          <!-- <a-dropdown>
            <template v-slot:overlay>
              <a-menu>
                <a-menu-item key="1">
                  <a-upload
                    v-model:file-list="data.fileList"
                    accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                    :customRequest="customUpload"
                    name="file"
                    :multiple="false"
                    :showUploadList="false"
                  >
                    <div :loading="data.clientLoading">客户端导入</div>
                  </a-upload>
                </a-menu-item>
                
                <a-menu-item key="2">
                  <div :loading="data.serverLoading" @click="() => (modal.visible = true)">
                    服务端导入
                  </div>
                </a-menu-item>
              </a-menu>
            </template>
            <a-button type="primary">
              导入预览(如果这样写的话就要把loading放到这里，统一一个变量就好了)
              <DownOutlined />
            </a-button>
          </a-dropdown> -->
        </a-space>
      </div>
    </div>

    <div id="luckysheet"></div>
    <div class="button-box">
      <a-space>
        <a-button @click="cancel"> 取消 </a-button>
        <a-button type="primary" :loading="loading" @click="downloadExcel">确定 </a-button>
      </a-space>
    </div>
    <div class="tips">
      <div style="color: red" v-if="data.isShowDataType">
        测序方法请填写：Nanopore pod5,Nanopore fast5,Nanopore fastq,齐碳
        fastq,PacBio_CCS,PacBio_CLR,华大,illumina,ABI,其他
      </div>
      <div style="color: red" v-if="data.isShowDataType">
        类型请填写：细菌,病毒,真菌,16S,宏基因组,动植物,转录组,人源,其他
      </div>
      <div style="color: red" v-if="data.isShowDataType">
        日期请按照YYYY-MM-DD格式填写，如：2024-01-01
      </div>
    </div>
    <a-modal
      :visible="modal.visible"
      title="选择文件"
      width="1020px"
      @ok="fileOk"
      @cancel="() => (modal.visible = false)"
      zIndex="1000"
      destroyOnClose
    >
      <FileSelect />
    </a-modal>
  </div>
</template>

<script lang="ts" setup>
import { reactive, onMounted, watch } from 'vue';
import * as Api from '/@/serve/api/samples/samples';
import { exportExcel } from '/@/utils/utils/exceljs';
import FileSelect from '/@/components/FileSelector/index.vue';
import { DownOutlined } from '@ant-design/icons-vue';
import LuckyExcel from 'luckyexcel';
import xlsx from 'node-xlsx';

import { message } from 'ant-design-vue';
const emit = defineEmits(['cancel', 'batchAddData']);
const props = defineProps({
  headers: {
    type: Array,
    default: () => [],
  },
  loading: {
    type: Boolean,
    default: false,
  },
});
const modal = reactive({
  visible: false,
});
const cancel = () => {
  luckysheet.exitEditMode(); // 退出编辑模式
  emit('cancel', '');
};
//服务器导入确定
const fileOk = () => {
  let selectPath = localStorage.getItem('nowSelector');
  if (selectPath) {
    if (selectPath.indexOf(' ') != -1) {
      message.warning('所选路径不能包含空格');
      return;
    }
    if (selectPath.slice(-5).includes('.xlsx')) {
      loadExcel2(selectPath);
      modal.visible = false;
    } else {
      message.info('请选择xlsx文件');
    }
  }
};
const loadExcel2 = (filePath: string) => {
  let params = { filePath };
  Api.xlsxFile(params)
    .then((res: any) => {
      const blob = new Blob([res], {
        // 设置返回的文件类型
        type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      });
      let file = new window.File([blob], 'fileName.xlsx', {
        type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      });
      data.serverLoading = true;
      loadExcel([file]);
    })
    .catch(() => {
      message.error('error');
    });
};
const loadExcel3 = (evt: any) => {
  loadExcel(evt.target.files);
};
//自定义上传
const customUpload = (info: any) => {
  data.clientLoading = true;
  loadExcel([info.file]);
};

const isFunction = (val: Function) => {
  return Object.prototype.toString.call(val).slice(8, -1) === 'Function';
};

const loadExcel = (files: any) => {
  //通过接口获取文件流，将文件流转为file文件，
  if (files == null || files.length == 0) {
    message.info('没有文件等待导入');
    return;
  }
  let name = files[0].name;
  let suffixArr = name.split('.'),
    suffix = suffixArr[suffixArr.length - 1];
  if (suffix != 'xlsx') {
    message.error('目前只支持导入xlsx文件');
    return;
  }
  LuckyExcel.transformExcelToLucky(files[0], function (exportJson, luckysheetfile) {
    if (exportJson.sheets == null || exportJson.sheets.length == 0) {
      message.error('读取excel文件内容失败，目前不支持xls文件!');
      return;
    }
    //销毁原有的表格
    isFunction(luckysheet?.destroy) && luckysheet.destroy();
    luckysheet.create({
      container: 'luckysheet', //luckysheet is the container id
      lang: 'zh', // 设定表格语言
      showinfobar: false,
      data: exportJson.sheets,
      title: exportJson.info.name,
      userInfo: exportJson.info.name.creator,
    });
    console.log('start loading');
    data.clientLoading = false;
    data.serverLoading = false;
  });
};
const downExcel = () => {
  let result = xlsx.build([{ name: 'sheet1', data: [props.headers] }]);
  const ab = Buffer.from(result, 'binary');
  const blob = new Blob([ab]);
  const blobUrl = URL.createObjectURL(blob);
  const a = document.createElement('a');
  a.href = blobUrl;
  a.download = '模版.xlsx';
  a.click();
  window.URL.revokeObjectURL(blobUrl);
  // exportExcel(luckysheet.getAllSheets(), '模板');
};
const downloadExcel = () => {
  // console.log(luckysheet.getAllSheets());
  // exportExcel(luckysheet.getAllSheets(), '下载');
  luckysheet.exitEditMode(); // 退出编辑模式
  let data = luckysheet.getAllSheets()[0].celldata;
  console.log(data);

  let result: any[] = [];
  data.forEach((item: any) => {
    if (result.length > item.r) {
      let tempArray = result[item.r];
      // console.log('item.c', item.c);

      tempArray[item.c] = item.v.m;
    } else {
      let tempArray = [];
      for (let i = 0; i < props.headers.length; i++) {
        tempArray.push(undefined);
      }
      tempArray[item.c] = item.v.m;
      result.push(tempArray);
    }
  });
  // let result2 = result.map((item: any) => {
  //   return item.slice(0, props.headers.length);
  // });
  // console.log(result2);
  emit('batchAddData', result);
};

const data = reactive({
  options: {},
  fileList: [],
  clientLoading: false,
  serverLoading: false,
  isShowDataType: false,
});
// !!! create luckysheet after mounted
onMounted(() => {
  drawSheet();
});

watch(
  () => props.headers,
  (n) => {
    drawSheet();
  },
);

const drawSheet = () => {
  console.log('111');

  data.isShowDataType = props.headers.includes('测序方法');
  let celldata = props.headers.map((item: any, index: number) => {
    let con = {
      r: 0,
      c: index,
      v: {
        bl: 1,
        ct: { fa: 'General', t: 'g' },
        ht: 0,
        v: item,
        m: item,
      },
    };
    return con;
  });
  data.options = {
    container: 'luckysheet',
    lang: 'zh', // 设定表格语言
    showinfobar: false,
    data: [
      {
        name: 'Sheet1', //工作表名称
        color: '', //工作表颜色
        index: 0, //工作表索引
        status: 1, //激活状态
        order: 0, //工作表的下标
        hide: 0, //是否隐藏
        row: 36, //行数
        column: 36, //列数
        defaultRowHeight: 39, //自定义行高
        defaultColWidth: 146, //自定义列宽
        celldata, //初始化使用的单元格数据
        config: {
          merge: {}, //合并单元格
          rowlen: {}, //表格行高
          columnlen: {}, //表格列宽
          rowhidden: {}, //隐藏行
          colhidden: {}, //隐藏列
          borderInfo: {}, //边框
          authority: {}, //工作表保护
        },
        scrollLeft: 0, //左右滚动条位置
        scrollTop: 0, //上下滚动条位置
        luckysheet_select_save: [], //选中的区域
        calcChain: [], //公式链
        isPivotTable: false, //是否数据透视表
        pivotTable: {}, //数据透视表设置
        filter_select: {}, //筛选范围
        filter: null, //筛选配置
        luckysheet_alternateformat_save: [], //交替颜色
        luckysheet_alternateformat_save_modelCustom: [], //自定义交替颜色
        luckysheet_conditionformat_save: {}, //条件格式
        frozen: {}, //冻结行列配置
        chart: [], //图表配置
        zoomRatio: 1, // 缩放比例
        image: [], //图片
        showGridLines: 1, //是否显示网格线
        dataVerification: {}, //数据验证配置
      },
      // {
      //   name: 'Sheet2',
      //   color: '',
      //   index: 1,
      //   status: 0,
      //   order: 1,
      // celldata: [
      //     {
      //       r: 0,
      //       c: 0,
      //       v: {
      //         v: '暂时淀粉',
      //         m: '暂时淀粉',
      //       },
      //     },
      //     {
      //       r: 0,
      //       c: 1,
      //       v: {
      //         v: 1,
      //         ct: {
      //           fa: 'General',
      //           t: 'n',
      //         },
      //         m: '1',
      //       },
      //     },
      //     {
      //       r: 1,
      //       c: 0,
      //       v: {
      //         v: 10,
      //         ct: {
      //           fa: 'General',
      //           t: 'n',
      //         },
      //         m: '10',
      //       },
      //     },
      //     {
      //       r: 1,
      //       c: 1,
      //       v: {
      //         v: 11,
      //         ct: {
      //           fa: 'General',
      //           t: 'n',
      //         },
      //         m: '11',
      //       },
      //     },
      //   ],
      //   config: {},
      // },
    ],
  };

  isFunction(luckysheet?.destroy) && luckysheet.destroy();
  luckysheet.create(data.options);
};
</script>

<style scoped>
#luckysheet {
  /* position: relative; */
  width: 79vw;
  height: 70vh;
}
.button-box {
  /* margin: 20px; */
  margin-top: 20px;
  display: flex;
  justify-content: flex-end;
}
.button-box2 {
  margin: 20px;
  display: flex;
  /* justify-content: flex-end; */
  justify-content: space-between;
}
.tips {
  margin-top: -30px;
}
:deep(.luckysheet-wa-editor) {
  overflow: hidden;
}
:deep(.luckysheet-stat-area) {
  background-color: transparent;
}
</style>
<style>
#chat-assistant-container {
  display: none;
}
</style>