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-uploadv-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-buttontype="primary":loading="data.serverLoading"@click="() => (modal.visible = true)">服务端导入</a-button><!-- <a-dropdown><template v-slot:overlay><a-menu><a-menu-item key="1"><a-uploadv-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 idlang: '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>