NN2025081602/addons/admin/src/views/exam/component/ExcelImport.vue

<template>
    <el-dialog :visible.sync="visible" :before-close="handleClose" custom-class="excel-dialog"
        :close-on-click-modal="false" :fullscreen="isFullScreen">
        <template #title>
            <span style="cursor: move;">导入 Excel</span>
            <el-button type="text" @click="toggleFullScreen" style="float: right; margin-right: 40px;">
                {{ isFullScreen ? '退出全屏' : '全屏' }}
            </el-button>
        </template>

        <!-- 顶部试卷信息表单 -->
        <el-form :model="formData" :rules="formRules" ref="formRef" label-width="100px" class="mb-10">
            <el-row :gutter="15">
                <el-col :sm="12">
                    <el-form-item label="题目名称" prop="name">
                        <el-input v-model="formData.name" placeholder="请输入关键词" />
                    </el-form-item>
                    <el-form-item label="题目分类" prop="subject_id">
                        <el-select v-model="formData.subject_id" placeholder="请选择题目分类">
                            <el-option v-for="item in subjectOptionsComputed" :key="item.id" :label="item.name"
                                :value="item.id" />
                        </el-select>
                    </el-form-item>
                    <el-form-item label="总分" prop="score_total">
                        <el-input-number v-model="formData.score_total" :min="0" :max="1000" />
                    </el-form-item>
                </el-col>
                <el-col :sm="12">
                    <el-form-item label="题目数量" prop="topic_count">
                        <el-input-number v-model="formData.topic_count" :min="0" :max="5000" />
                    </el-form-item>
                    <el-form-item label="是否收费" prop="is_charge">
                        <el-radio-group v-model="formData.is_charge">
                            <el-radio :label="0">免费</el-radio>
                            <el-radio :label="1">收费</el-radio>
                        </el-radio-group>
                    </el-form-item>
                    <el-form-item label="状态" prop="status">
                        <el-switch v-model="formData.status" :active-value="1" :inactive-value="2" />
                    </el-form-item>
                    <el-form-item label="排序" prop="sort">
                        <el-input-number v-model="formData.sort" :min="0" />
                    </el-form-item>
                </el-col>
            </el-row>
            <el-form-item label="备注" class="mt-16">
                <el-input v-model="formData.remark" type="textarea" :rows="2" placeholder="请输入备注" />
            </el-form-item>
        </el-form>

        <!-- 操作按钮区域 -->
        <div class="import-actions">
            <el-button type="success" icon="el-icon-download" @click="downloadTemplate">
                下载导入模板
            </el-button>
            <el-upload :before-upload="beforeUpload" :show-file-list="false" :on-change="handleFileChange"
                accept=".xls,.xlsx" style="display: inline-block; margin-left: 10px;">
                <el-button type="primary" icon="el-icon-upload2">选择 Excel 文件</el-button>
            </el-upload>
        </div>

        <!-- 格式说明 -->
        <div class="format-tips">
            <el-alert title="Excel格式要求" type="info" :closable="false" show-icon>
                <template slot="default">
                    <p><strong>Excel格式要求：</strong></p>
                    CREATE TABLE `lev_ad` (
                    `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
                    `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '广告标题',
                    `position` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '广告位置：1-主页轮播图，2-会员中心占位图',
                    `cover` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '广告图片',
                    `type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '广告访问：1-站内链接，2-站外链接，3-其他小程序，4-微信视频号',
                    `description` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '广告描述',
                    `url` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '广告链接/小程序链接/视频号链接',
                    `link_type` tinyint(1) DEFAULT '1' COMMENT '跳转类型：1-默认，2-小程序',
                    `mp_appid` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '跳转小程序APPID',
                    `width` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '广告宽度',
                    `height` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '广告高度',
                    `start_time` int(10) unsigned NOT NULL COMMENT '开始时间',
                    `end_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '结束时间',
                    `view_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '广告点击次数',
                    `status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '状态：1在用 2停用',
                    `sort` smallint(5) unsigned NOT NULL DEFAULT '125' COMMENT '排序',
                    `create_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '添加时间',
                    `update_time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
                    `mark` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '有效标识(1正常 0删除)',
                    PRIMARY KEY (`id`) USING BTREE,
                    KEY `title` (`title`) USING BTREE,
                    KEY `ad_sort_id` (`position`) USING BTREE
                    ) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                    ROW_FORMAT=DYNAMIC COMMENT='广告管理表'; <p>• 第一行必须是标题行，支持以下列名：题目类型、题目、选项A~F（或答案A~F）、正确答案、解析、分数</p>
                    <p>• 列顺序可以任意排列，系统会自动识别各列</p>
                    <p>• 题目类型：单选题、多选题、判断题、填空题、问答题、写作题、应用题、计算题、证明题、解答题、论述题</p>
                    <p>• 正确答案：单选题填选项字母(A/B/C/D/E/F)，多选题用逗号分隔(A,B)，判断题填对/错，填空题填答案，问答题可留空</p>
                    <p>• 分数：每道题的分值，建议单选题2-5分，多选题5-10分，判断题1-2分，填空题3-8分，问答题10-20分</p>
                    <p>• 支持富文本格式</p>
                    <p><strong>示例标题行：</strong>题目类型、题目、解析、正确答案、答案A、答案B、答案C、答案D、分数、答案E、答案F</p>
                </template>
            </el-alert>
        </div>

        <!-- Excel 预览 -->
        <div v-if="previewData.length" class="excel-preview">
            <el-table :data="previewData" row-key="_id" border style="width: 100%" :height="tableHeight"
                :max-height="tableMaxHeight">
                <!-- 拖动列 -->
                <!-- <el-table-column label="拖动" width="60">
                    <template #default="{ $index }">
                        <i class="el-icon-rank drag-handle" @mousedown.prevent.stop="startDrag($index)"></i>
                    </template>
                </el-table-column> -->

                <el-table-column prop="topic_type" label="题目类型" width="120" />
                <el-table-column label="题目" min-width="200">
                    <template #default="{ row }">
                        <text-ellipsis :text="row.topic_name" :max-length="20" />
                    </template>
                </el-table-column>
                <el-table-column label="解析" min-width="150">
                    <template #default="{ row }">
                        <text-ellipsis :text="row.topic_analysis" :max-length="20" />
                    </template>
                </el-table-column>
                <el-table-column label="正确答案" min-width="100">
                    <template #default="{ row }">
                        <text-ellipsis :text="row.correct_answer" :max-length="20" />
                    </template>
                </el-table-column>
                <el-table-column label="答案A" min-width="100">
                    <template #default="{ row }">
                        <text-ellipsis :text="row.answer_A" :max-length="20" />
                    </template>
                </el-table-column>
                <el-table-column label="答案B" min-width="100">
                    <template #default="{ row }">
                        <text-ellipsis :text="row.answer_B" :max-length="20" />
                    </template>
                </el-table-column>
                <el-table-column label="答案C" min-width="100">
                    <template #default="{ row }">
                        <text-ellipsis :text="row.answer_C" :max-length="20" />
                    </template>
                </el-table-column>
                <el-table-column label="答案D" min-width="100">
                    <template #default="{ row }">
                        <text-ellipsis :text="row.answer_D" :max-length="20" />
                    </template>
                </el-table-column>
                <el-table-column label="答案E" min-width="100">
                    <template #default="{ row }">
                        <text-ellipsis :text="row.answer_E" :max-length="20" />
                    </template>
                </el-table-column>
                <el-table-column label="答案F" min-width="100">
                    <template #default="{ row }">
                        <text-ellipsis :text="row.answer_F" :max-length="20" />
                    </template>
                </el-table-column>
                <el-table-column prop="score" label="分数" width="80" />
            </el-table>
        </div>

        <!-- 底部按钮 -->
        <span slot="footer" class="dialog-footer">
            <el-button @click="handleReset">重置</el-button>
            <el-button @click="handleClose">取消</el-button>
            <el-button type="primary" :disabled="!previewData.length" @click="handleConfirm">确认导入</el-button>
        </span>
    </el-dialog>
</template>

<script>
import * as XLSX from "xlsx";
import TextEllipsis from "@/components/TextEllipsis.vue";

export default {
    name: "ExcelImport",
    components: { TextEllipsis },
    props: {
        visible: { type: Boolean, default: false },
        defaultSceneType: { type: Number, default: null },
        defaultType: { type: Number, default: null },
        subjectOptions: { type: Array, default: () => [] },
    },
    data() {
        return {
            file: null,
            previewData: [],
            isFullScreen: false,
            tableHeight: 400,
            formData: this.getDefaultFormData(),
            formRules: {
                name: [{ required: true, message: "请输入题目名称", trigger: "blur" }],
                subject_id: [{ required: true, message: "请选择题目分类", trigger: "change" }],
                score_total: [{ required: true, message: "请输入总分", trigger: "blur" }],
                topic_count: [{ required: true, message: "请输入题目数量", trigger: "blur" }],
                is_charge: [{ required: true, message: "请选择是否收费", trigger: "change" }]
            }
        };
    },
    computed: {
        tableMaxHeight() { return this.isFullScreen ? window.innerHeight - 250 : 400; },
        subjectOptionsComputed() { return this.subjectOptions.length ? this.subjectOptions : []; }
    },
    methods: {
        // 下载导入模板
        downloadTemplate() {
            // 创建模板数据
            const templateData = [
                ['题目类型', '题目', '选项A', '选项B', '选项C', '选项D', '选项E', '选项F', '正确答案', '解析', '分数'],
                ['单选题', '这是一道单选题示例？', '选项A内容', '选项B内容', '选项C内容', '选项D内容', '', '', 'A', '这是单选题的解析', '5'],
                ['多选题', '这是一道多选题示例？', '选项A内容', '选项B内容', '选项C内容', '选项D内容', '', '', 'A,B', '这是多选题的解析', '10'],
                ['判断题', '这是一道判断题示例？', '', '', '', '', '', '', '对', '这是判断题的解析', '2'],
                ['填空题', '这是一道填空题示例，答案是___', '', '', '', '', '', '', '答案内容', '这是填空题的解析', '8'],
                ['问答题', '这是一道问答题示例？', '', '', '', '', '', '', '', '这是问答题的解析', '15'],
                ['写作题', '这是一道写作题示例？', '', '', '', '', '', '', '', '这是写作题的解析', '20'],
                ['应用题', '这是一道应用题示例？', '', '', '', '', '', '', '', '这是应用题的解析', '25'],
                ['计算题', '这是一道计算题示例？', '', '', '', '', '', '', '', '这是计算题的解析', '30'],
                ['证明题', '这是一道证明题示例？', '', '', '', '', '', '', '', '这是证明题的解析', '35'],
                ['解答题', '这是一道解答题示例？', '', '', '', '', '', '', '', '这是解答题的解析', '40'],
                ['论述题', '这是一道论述题示例？', '', '', '', '', '', '', '', '这是论述题的解析', '45']
            ];

            // 创建工作簿
            const wb = XLSX.utils.book_new();
            const ws = XLSX.utils.aoa_to_sheet(templateData);

            // 设置列宽
            ws['!cols'] = [
                { width: 12 }, // 题目类型列
                { width: 40 }, // 题目列
                { width: 20 }, // 选项A列
                { width: 20 }, // 选项B列
                { width: 20 }, // 选项C列
                { width: 20 }, // 选项D列
                { width: 20 }, // 选项E列
                { width: 20 }, // 选项F列
                { width: 12 }, // 正确答案列
                { width: 30 }, // 解析列
                { width: 8 }   // 分数列
            ];

            // 添加工作表到工作簿
            XLSX.utils.book_append_sheet(wb, ws, '题目导入模板');

            // 下载文件
            const fileName = `题目导入模板_${new Date().toISOString().slice(0, 10)}.xlsx`;
            XLSX.writeFile(wb, fileName);

            this.$message.success('模板下载成功');
        },

        getDefaultFormData() {
            return {
                type: this.type,
                name: "",
                scene_type: this.defaultSceneType,
                subject_id: null,
                score_total: 0,
                topic_count: 0,
                is_charge: 0,
                status: 1,
                sort: 0,
                remark: ""
            };
        },
        handleClose() {
            this.clearAll();
            this.$emit("update:visible", false);
        },
        handleReset() { this.clearAll(); },
        clearAll() {
            this.file = null;
            this.previewData = [];
            this.formData = this.getDefaultFormData();
            this.isFullScreen = false;
        },
        beforeUpload(file) {
            const isExcel = file.type.includes("excel") || file.name.endsWith(".xls") || file.name.endsWith(".xlsx");
            if (!isExcel) this.$message.error("只能上传 Excel 文件");
            return isExcel;
        },
        handleFileChange(file) {
            this.file = file.raw;
            this.readExcel(this.file);
        },
        readExcel(file) {
            const reader = new FileReader();
            reader.onload = e => {
                const data = e.target.result;
                const workbook = XLSX.read(data, { type: "binary" });
                const firstSheet = workbook.Sheets[workbook.SheetNames[0]];
                const allData = XLSX.utils.sheet_to_json(firstSheet, { header: 1, defval: "" });

                // 从文件名提取题目名称（去除扩展名）
                const fileName = file.name;
                const nameWithoutExt = fileName.replace(/\.[^/.]+$/, "");
                this.formData.name = this.formData.name || nameWithoutExt;

                if (allData.length < 2) return this.$message.error("Excel 数据行数不足");

                // 读取第一行作为标题，动态匹配列索引
                const headers = allData[0].map(h => String(h).trim());

                // 优先精确匹配，然后模糊匹配，且优先匹配更长的关键词
                const getColumnIndex = (searchKeywords, excludeIndices = []) => {
                    // 先按长度降序排序，优先匹配更长的关键词（更具体的匹配）
                    const sortedKeywords = [...searchKeywords].sort((a, b) => b.length - a.length);

                    // 先尝试精确匹配
                    for (const keyword of sortedKeywords) {
                        const exactIndex = headers.findIndex((header, idx) =>
                            !excludeIndices.includes(idx) && header === keyword
                        );
                        if (exactIndex >= 0) return exactIndex;
                    }

                    // 再尝试模糊匹配（includes），但排除已匹配的列
                    for (const keyword of sortedKeywords) {
                        const fuzzyIndex = headers.findIndex((header, idx) =>
                            !excludeIndices.includes(idx) && header.includes(keyword)
                        );
                        if (fuzzyIndex >= 0) return fuzzyIndex;
                    }

                    return -1;
                };

                // 动态获取列索引，按优先级顺序匹配，避免冲突
                const colIndices = {};
                const usedIndices = [];

                // 先匹配更具体的字段（避免被包含在其他字段中）
                colIndices.topic_type = getColumnIndex(['题目类型', '题型'], usedIndices);
                if (colIndices.topic_type >= 0) usedIndices.push(colIndices.topic_type);

                colIndices.topic_name = getColumnIndex(['题目内容', '题目'], usedIndices);
                if (colIndices.topic_name >= 0) usedIndices.push(colIndices.topic_name);

                colIndices.correct_answer = getColumnIndex(['正确答案', '标准答案', '答案'], usedIndices);
                if (colIndices.correct_answer >= 0) usedIndices.push(colIndices.correct_answer);

                colIndices.topic_analysis = getColumnIndex(['题目解析', '答案解析', '解析'], usedIndices);
                if (colIndices.topic_analysis >= 0) usedIndices.push(colIndices.topic_analysis);

                colIndices.answer_A = getColumnIndex(['选项A', '答案A', '选项 A', '答案 A'], usedIndices);
                if (colIndices.answer_A >= 0) usedIndices.push(colIndices.answer_A);

                colIndices.answer_B = getColumnIndex(['选项B', '答案B', '选项 B', '答案 B'], usedIndices);
                if (colIndices.answer_B >= 0) usedIndices.push(colIndices.answer_B);

                colIndices.answer_C = getColumnIndex(['选项C', '答案C', '选项 C', '答案 C'], usedIndices);
                if (colIndices.answer_C >= 0) usedIndices.push(colIndices.answer_C);

                colIndices.answer_D = getColumnIndex(['选项D', '答案D', '选项 D', '答案 D'], usedIndices);
                if (colIndices.answer_D >= 0) usedIndices.push(colIndices.answer_D);

                colIndices.answer_E = getColumnIndex(['选项E', '答案E', '选项 E', '答案 E'], usedIndices);
                if (colIndices.answer_E >= 0) usedIndices.push(colIndices.answer_E);

                colIndices.answer_F = getColumnIndex(['选项F', '答案F', '选项 F', '答案 F'], usedIndices);
                if (colIndices.answer_F >= 0) usedIndices.push(colIndices.answer_F);

                colIndices.score = getColumnIndex(['分数', '分值', '得分'], usedIndices);
                if (colIndices.score >= 0) usedIndices.push(colIndices.score);

                // 验证必要的列是否存在
                const requiredColumns = ['topic_type', 'topic_name', 'correct_answer'];
                const missingColumns = requiredColumns.filter(key => colIndices[key] === -1);
                if (missingColumns.length > 0) {
                    return this.$message.error(`缺少必要的列：${missingColumns.join('、')}`);
                }

                const rows = allData.slice(1).filter(r => r.some(c => c !== ""));
                this.previewData = rows.map((r, index) => ({
                    _id: index,
                    topic_type: colIndices.topic_type >= 0 ? (r[colIndices.topic_type] || "") : "",
                    topic_name: colIndices.topic_name >= 0 ? (r[colIndices.topic_name] || "") : "",
                    answer_A: colIndices.answer_A >= 0 ? (r[colIndices.answer_A] || "") : "",
                    answer_B: colIndices.answer_B >= 0 ? (r[colIndices.answer_B] || "") : "",
                    answer_C: colIndices.answer_C >= 0 ? (r[colIndices.answer_C] || "") : "",
                    answer_D: colIndices.answer_D >= 0 ? (r[colIndices.answer_D] || "") : "",
                    answer_E: colIndices.answer_E >= 0 ? (r[colIndices.answer_E] || "") : "",
                    answer_F: colIndices.answer_F >= 0 ? (r[colIndices.answer_F] || "") : "",
                    correct_answer: colIndices.correct_answer >= 0 ? (r[colIndices.correct_answer] || "") : "",
                    topic_analysis: colIndices.topic_analysis >= 0 ? (r[colIndices.topic_analysis] || "") : "",
                    score: colIndices.score >= 0 ? (Number(r[colIndices.score]) || 0) : 0,
                    show_type: 1, // 导入的数据固定为文本类型
                    answer_type: 2, // 导入的数据固定为不可提交图片答案
                    topic_image_id: "", // 默认空值
                    sort: index + 1, // 按顺序排序
                    status: 1, // 默认有效状态
                    mark: 1 // 默认未删除
                }));

                this.formData.topic_count = this.previewData.length;
                this.formData.score_total = this.previewData.reduce((sum, item) => sum + item.score, 0);
            };
            reader.readAsBinaryString(file);
        },
        async handleConfirm() {
            // 验证表单
            const valid = await this.$refs.formRef.validate().catch(() => false);
            if (!valid) {
                this.$message.error("请完善必填信息");
                return;
            }

            const res = await this.$http.post(`/papers/import`, {
                formData: { ...this.formData, type: this.defaultType, scene_type: this.defaultSceneType },
                topicsData: this.previewData
            });
            if (res.data.code === 0) {
                this.$message.success("导入成功");
                this.$emit("imported", this.formData, this.previewData);
                this.handleClose();
            } else {
                this.$message.error(res.data.msg);
            }
        },
        toggleFullScreen() { this.isFullScreen = !this.isFullScreen; }
    }
};
</script>

<style scoped>
.excel-dialog {
    width: 1200px !important;
    min-width: 600px;
    resize: both;
    overflow: auto;
}

.import-actions {
    margin: 20px 0;
    text-align: center;
    display: flex;
    justify-content: center;
    align-items: center;
    gap: 10px;
}

.format-tips {
    margin: 20px 0;
}

.excel-preview {
    overflow-x: auto;
}

.el-table th {
    position: sticky;
    top: 0;
    background: #fff;
    z-index: 10;
}

.el-table td {
    white-space: nowrap;
    text-overflow: ellipsis;
    overflow: hidden;
}

.drag-handle {
    cursor: move;
}
</style>