前端Vue 结合xlxs库实现解析excel文件，并动态组装表头！

1.前言

最近有一个需求，就是用户可以任意导入一个自定义的excel文件，让用户可以自己选择，组装表头的对应关系，这样做的目的是为了解决模板的局限性，更加的灵活的导入

2.数据定义

data() {
        return {
            file: null,
            excelHeaders: [],
            tableData: [],
            page: {},
            scanPage: {},
            list: [],
            total: 0,
            scanList: [],
            scanTotal: 0,
            excelLimit: 0,
            dataLoading: false,
            visible: false,
            importDescVisible: false,
            importVisible: false,
            uploadLoading: false,
            fileName: '',
            users: [],
            selectedHeaders: [],
            headerOptions: [],
            excelData: [],
            tempExcelData: [],
            headerMappings: [],
            systemHeaders: [
                 {
                    title: '编号',
                    isRequire: true
                },
                {
                    title: '交易日期',
                    isRequire: true
                },
                {
                    title: '付款人',
                    isRequire: true
                },
                {
                    title: '付款人账号',
                    isRequire: true
                },
                // {
                //     title: '收款人',
                //     isRequire: true
                // },
                // {
                //     title: '收款人账号',
                //     isRequire: true
                // },
                {
                    title: '金额',
                    isRequire: true
                },
                {
                    title: '摘要',
                    isRequire: false
                },
                {
                    title: '备注',
                    isRequire: false
                },
            ],
            initSystemHeaders: [
                {
                    title: '编号',
                    isRequire: true
                },
                {
                    title: '交易日期',
                    isRequire: true
                },
                {
                    title: '付款人',
                    isRequire: true
                },
                {
                    title: '付款人账号',
                    isRequire: true
                },
                // {
                //     title: '收款人',
                //     isRequire: true
                // },
                // {
                //     title: '收款人账号',
                //     isRequire: true
                // },
                {
                    title: '金额',
                    isRequire: true
                },
                {
                    title: '摘要',
                    isRequire: false
                },
                {
                    title: '备注',
                    isRequire: false
                },
            ],
            fileFormat: ['xlsx', 'xlc', 'xlm', 'xls', 'xlt', 'xlw', 'csv'],
            batchStatus: '',
            headerMap: new Map,
            scanLoading: false,
            batchNumber: '',
            tableSize: 0,
            showTableSize: 0,
        }
    }

3. 页面布局

<div>
    <el-upload
            action=""
            accept=".xlsx"
            :limit="1"
            :before-upload="beforeUpload"
            :on-exceed="handleExceed"
            :on-success="handleUploadSuccess"
            :on-error="handleUploadError">
        <el-button type="primary" plain>选择文件</el-button>
    </el-upload>
    <div slot="tip" class="el-upload__tip">只能上传xlsx/xls文件，且不超过5MB</div>
    <div v-if="this.fileName !==''" style="margin-top: 10px;font-size: 15px;">
        本次导入文件名：
        <span style="margin-right: 15px;color: #dd1100">
            {{ this.fileName }}
        </span>
        导入数据条数：
        <span style="margin-right: 15px;color: #dd1100">
            {{this.tableSize -1}}
        </span>
        展示数据条数： 
        <span style="margin-right: 15px;color: #dd1100">
            {{this.tableData.length}}
        </span>
    </div>
    <div style="overflow-x: auto;">
        <table  class="text-center"  cellspacing="0" cellpadding="0" border="1"
                               style="margin-top: 10px;width: 3000px">
            <thead style="height: 35px;background-color: #f2dae2">
                <tr>
                    <th colspan="1" rowspan="1" style="width: 1500px"
                                    v-for="(header, index) in systemHeaders"
                                    :key="index"
                                    :style="header.isRequire ? { color: 'red' } : {}">
                                    {{ header.title }}
                    </th>
                </tr>
            </thead>
            <tbody v-if="tableData.length >0" class="text-center">
                <tr style="height: 35px">
                    <td v-for="(header, index) in excelHeaders" :key="index">
                        <select v-model="selectedHeaders[index]"
                                            @change="handleHeaderChange(index)"
                                            :disabled="systemHeaders[index].title === '-'"
                                            style="height: 35px;width: 100%">
                            <option v-for="option in headerOptions" :key="option" :value="option">
                                            {{ option }}
                                        </option>
                        </select>
                    </td>
                </tr>
                <tr v-for="(row, rowIndex) in tableData" :key="rowIndex"   style="min-height: 35px">
                    <td v-for="(cell, cellIndex) in row"
                                    :key="cellIndex"
                                    :style="cellIndex >= systemHeaders.length ? { 'width': '1500px' }: '' ">
                                    {{ cell }}
                   </td>
                </tr>
            </tbody>
        </table>
    </div>
    <el-button type="warning" plain @click="handleRemove" :disabled="this.fileName === ''">
                        移除文件
                    </el-button>
    <el-button type="success" plain @click="confirmImport" style="margin-top: 15px"
                               :disabled="this.excelLimit === 0" :loading="this.uploadLoading">
                        确认导入
    </el-button>
</div>

4.上传之前的事件

 beforeUpload(file) {
            this.file = file;
            this.fileName = file.name
            let fileSize = file.size
            const FIVE_M = 5 * 1024 * 1024
            //不允许上传大于5M
            if (fileSize > FIVE_M) {
                this.$message.error("最大上传5MB")
                return false
            }
            const suffix = file.name.split('.').reverse()[0];
            if (!this.fileFormat.includes(suffix)) {
                this.$message.error('只能上传.xlsx或者.xls文件')
                return false
            }
            this.handleFileUploaded(file)
            this.excelLimit = 1
            return false; // 阻止默认上传行为
        }

5.解析excel文件,并组装系统表头与excel表头的对应关系

 handleFileUploaded(file) {
            console.log("==============>>开始解析excel文件<<==============")
            let reader = new FileReader()
            let _this = this;
            reader.onload = (e) => {
                const data = new Uint8Array(e.target.result)
                const workbook = XLSX.read(data, {
                    type: 'array',
                    cellDates: true
                })
                const worksheet = workbook.Sheets[workbook.SheetNames[0]]
                const jsonData = XLSX.utils.sheet_to_json(worksheet, {header: 1})
                _this.tableSize = jsonData.length
                const regex = /^[a-zA-Z]{3} [a-zA-Z]{3} \d{2} \d{4} \d{2}:\d{2}:\d{2} GMT\+\d{4} \(中国标准时间\)$/;
                const tableData = jsonData.map(row => {
                    return row.map(cell => {
                        if (cell !== '' || cell !== null) {
                            if (regex.test(cell.toString())) {
                                let date = new Date(cell);
                                date.setDate(date.getDate() + 1);
                                const isoDateString = date.toISOString();
                                return isoDateString.slice(0, 10).replace('T', '-');
                            }
                        }
                        return cell;
                    });
                });
                // 获取Excel的表头
                _this.excelHeaders = tableData[0]
                // 根据系统表头和Excel表头生成下拉框选项
                for (let index = 0; index < _this.excelHeaders.length; index++) {
                    const excelHeader = _this.excelHeaders[index]
                    _this.headerOptions.push(excelHeader)
                }

                if (!_this.objectsAreEqual(_this.systemHeaders, _this.excelHeaders)) {
                    _this.headerOptions.unshift('缺失')
                }

                // 初始化选中的表头
                _this.initSelectHeader();
                // 获取对应列的数据（这里只展示前5条）
                _this.tableData = JSON.parse(JSON.stringify(tableData.slice(1, 6)))
                _this.tempExcelData = JSON.parse(JSON.stringify(tableData.slice(1, 6)))
                // 初始化表格数据
                _this.initExcelData()
                //组装表头初始关系
                _this.handHeaderMapping()
            }
            reader.readAsArrayBuffer(file)
        },
        initSelectHeader() {
            this.systemHeaders.forEach((systemHeader) => {
                let selectedHeader = '缺失';
                let _excelHeader = '';
                for (let index = 0; index < this.excelHeaders.length; index++) {
                    const excelHeader = this.excelHeaders[index]
                    if (excelHeader === systemHeader.title) {
                        _excelHeader = excelHeader
                        break;
                    }
                }
                if (_excelHeader !== '') {
                    this.selectedHeaders.push(_excelHeader)
                } else {
                    this.selectedHeaders.push(selectedHeader)
                }
            });
            if (this.excelHeaders.length > this.systemHeaders.length) {
                this.selectedHeaders = this.selectedHeaders.concat(this.excelHeaders.slice(this.selectedHeaders.length));
                const moreLength = this.excelHeaders.length - this.systemHeaders.length
                for (let index = 0; index < moreLength; index++) {
                    this.systemHeaders.push({title: '-', isRequire: false})
                }
            }
        },
        initExcelData() {
            for (let index = 0; index < this.selectedHeaders.length; index++) {
                this.handleHeaderChange(index)
            }
        },
        objectsAreEqual(obj1, obj2) {
            return JSON.stringify(obj1) === JSON.stringify(obj2);
        },
        handHeaderMapping() {
            const headerMap = new Map();
            let filteredSystemHeaders = this.systemHeaders.filter(header => header.title !== '-')
            filteredSystemHeaders.forEach((item, index) => {
                let key = this.selectedHeaders[index]
                headerMap.set(item.title, key)
            })
            this.headerMap = headerMap;
        }

6.下拉框改变事件

handleHeaderChange(index) {
            const selectedHeader = this.selectedHeaders[index]
            if (selectedHeader === '缺失') {
                // 如果选择了缺失，则清空对应列的数据
                this.tableData.forEach(row => {
                    row[index] = ''
                })
            } else {
                // 如果选择了Excel表头，则将对应列的数据展示到系统表头下
                this.tableData.forEach((row, _index) => {
                    const rowIndex = this.excelHeaders.findIndex(item => item === selectedHeader);
                    if (rowIndex >= 0) {
                        row[index] = this.tempExcelData[_index][rowIndex]
                    }
                })
            }
            //更新头部映射
            if (this.systemHeaders[index] !== undefined && this.systemHeaders[index] !== null) {
                let _systemHeader = this.systemHeaders[index].title
                this.headerMap.set(_systemHeader, selectedHeader)
            }
        }