AI赋能教师培训管理——应用DeepSeek生成文件转换工具实现学员通讯录批量导入手机联系人

【问题情境】

开展教师培训工作前，为了方便日常联络、通知事宜，需要将培训班120名参训学员的信息批量录入手机通讯录，手动逐个添加录入效率极低，容易出错，有没有快捷的批量录入方式？

【解决办法】

目前安卓、苹果（iPhone）主流手机均支持批量导入功能。核心解决思路：将学员信息Excel表格转换为手机通讯录通用的 VCF (vCard) 格式文件，再直接导入手机即可一次性完成全部联系人录入。

【实践操作】

1.打开EXCEL表格，按照标准字段排版：第1列：姓名；第2列：手机；第3列：公司；第4列：职位。

注意表格无合并单元格、无空行、手机号为纯数字格式，保证信息完整规范。

2. 利用DeepSeek生成一个能将EXCEL表格转换为手机通讯录通用的 VCF (vCard) 格式文件的网页。提示词为：生成一个网页，能导入 EXCEL格式文件，自动识别：姓名、手机、公司、职位，生成标准 vCard 3.0 VCF 文件，手机、iPhone、安卓都能直接导入。生成网页展示如下：

4. 在网页中选择EXCEL文件，点击【生成vCard（.vcf）并下载】按钮，自动完成格式转换，下载生成好的.vcf联系人文件。

5.将得到的VCF (vCard) 格式文件通过微信文件传输助手传送到手机，在手机端接收文件，选择使用系统自带联系人应用打开文件，确认导入，即可一次性将全部学员姓名、电话、单位、职务信息批量录入手机通讯录。

【拓展应用】

该应用示例可拓展到学校班主任批量导入家长通信录、办公室人员批量导入单位职工通讯录等。

现将应用DeepSeek生成的HTML 网页代码附于文末，只需要新建.txt文本文件，然后将以下网页代码复制、粘贴，最后将后缀名修改为.html。

<!DOCTYPE html>

<head>

<title>Excel转vCard ·生成标准VCF通讯录</title>

<style>

* {

margin: 0;

padding: 0;

box-sizing: border-box;

}

body {

background: linear-gradient(145deg, #f4f9ff 0%, #e9f0fa 100%);

font-family: system-ui, -apple-system, 'Segoe UI', Roboto, 'Helvetica Neue', sans-serif;

padding: 28px 20px;

color: #1e293b;

}

.container {

max-width: 1400px;

margin: 0 auto;

}

/* 卡片设计 */

.card {

background: rgba(255,255,255,0.96);

backdrop-filter: blur(0px);

border-radius: 2rem;

box-shadow: 0 20px 35px -12px rgba(0,0,0,0.08), 0 1px 2px rgba(0,0,0,0.02);

padding: 1.8rem 2rem;

margin-bottom: 2rem;

border: 1px solid rgba(255,255,255,0.5);

transition: all 0.2s;

}

.card-header {

display: flex;

align-items: center;

justify-content: space-between;

flex-wrap: wrap;

gap: 1rem;

border-bottom: 2px solid #eef2f8;

padding-bottom: 1.2rem;

margin-bottom: 1.6rem;

}

.card-header h2 {

font-size: 1.6rem;

font-weight: 700;

background: linear-gradient(135deg, #1f6e8c, #0f4c5f);

background-clip: text;

-webkit-background-clip: text;

color: transparent;

letter-spacing: -0.3px;

}

.badge {

background: #e6f0f5;

padding: 6px 14px;

border-radius: 40px;

font-size: 0.8rem;

font-weight: 600;

color: #1f5e7e;

}

/* 上传区域 */

.upload-zone {

background: #fefefe;

border: 2px dashed #b9d0e3;

border-radius: 2rem;

padding: 2rem 1.5rem;

text-align: center;

cursor: pointer;

transition: all 0.2s ease;

margin-bottom: 1.2rem;

}

.upload-zone:hover {

background: #f9feff;

border-color: #2c7da0;

transform: scale(0.99);

}

.file-label {

display: inline-flex;

align-items: center;

gap: 10px;

background: #2c7da0;

color: white;

font-weight: 600;

padding: 12px 28px;

border-radius: 60px;

font-size: 1rem;

cursor: pointer;

transition: 0.2s;

box-shadow: 0 4px 8px rgba(0,0,0,0.05);

border: none;

}

.file-label:hover {

background: #1f5e7e;

transform: translateY(-2px);

}

.file-input {

display: none;

}

.option-row {

display: flex;

align-items: center;

gap: 28px;

flex-wrap: wrap;

background: #f8fafc;

padding: 12px 18px;

border-radius: 60px;

margin-top: 8px;

}

.checkbox-label {

display: flex;

align-items: center;

gap: 8px;

font-weight: 500;

cursor: pointer;

}

.info-note {

font-size: 0.8rem;

color: #4b6a8b;

margin-top: 12px;

}

/* 映射网格 */

.mapping-grid {

display: grid;

grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));

gap: 20px;

margin: 24px 0 18px;

}

.mapping-card-item {

background: #fbfdfe;

border-radius: 1.5rem;

padding: 0.9rem 1.2rem;

border: 1px solid #e2edf2;

box-shadow: 0 1px 2px rgba(0,0,0,0.02);

}

.mapping-card-item label {

font-weight: 700;

font-size: 0.85rem;

display: block;

margin-bottom: 10px;

color: #155f73;

}

.mapping-card-item select {

width: 100%;

padding: 10px 12px;

border-radius: 40px;

border: 1px solid #cbdde6;

background: white;

font-weight: 500;

font-size: 0.85rem;

cursor: pointer;

}

/* 表格预览 */

.table-wrapper {

overflow-x: auto;

border-radius: 1.5rem;

border: 1px solid #e4edf2;

margin: 1.5rem 0;

}

table {

width: 100%;

border-collapse: collapse;

font-size: 0.8rem;

min-width: 700px;

}

th {

background: #eef3f9;

padding: 12px 10px;

text-align: left;

font-weight: 700;

color: #1f4156;

}

td {

padding: 10px 10px;

border-bottom: 1px solid #ecf3f8;

word-break: break-word;

}

.missing-mobile {

background: #fff3e8;

}

.warning-badge {

background: #fee2e2;

color: #b91c1c;

border-radius: 40px;

padding: 2px 10px;

font-size: 0.7rem;

font-weight: 600;

display: inline-block;

}

.valid-badge {

background: #e0f2e9;

color: #0f6e3f;

border-radius: 40px;

padding: 2px 10px;

font-size: 0.7rem;

font-weight: 600;

}

.btn-group {

display: flex;

gap: 16px;

justify-content: space-between;

flex-wrap: wrap;

margin: 20px 0 5px;

}

.btn {

border: none;

padding: 12px 28px;

border-radius: 60px;

font-weight: 700;

font-size: 0.9rem;

cursor: pointer;

transition: 0.2s;

display: inline-flex;

align-items: center;

gap: 8px;

}

.btn-primary {

background: #1e6f5c;

color: white;

box-shadow: 0 4px 8px rgba(0,0,0,0.1);

}

.btn-primary:hover {

background: #0c5847;

transform: translateY(-2px);

}

.btn-secondary {

background: #eef2f8;

color: #1f4156;

}

.btn-secondary:hover {

background: #e2e8f0;

}

.stats {

background: #e8f2f7;

border-radius: 2rem;

padding: 10px 18px;

font-size: 0.85rem;

color: #1a5d74;

margin-top: 12px;

}

hr {

margin: 16px 0;

border: none;

border-top: 1px solid #e2edf2;

}

footer {

text-align: center;

font-size: 0.7rem;

color: #6c86a3;

margin-top: 30px;

}

@media (max-width: 650px) {

.card {

padding: 1.2rem;

}

.mapping-grid {

grid-template-columns: 1fr;

}

</style>

</head>

<body>

<h2>📎 Excel → vCard 转换器</h2>

<div class="badge">标准 vCard 3.0 ·兼容 iPhone/安卓</div>

</div>

<label for="excelInput" class="file-label">📂选择 Excel 文件 (.xlsx / .xls)</label>

<div class="info-note">支持拖拽上传，自动识别姓名、手机、公司、职位字段，生成通讯录vcf</div>

</div>

<input type="checkbox" id="firstRowHeader" checked> 第一行作为列标题 (推荐)

</label>

<span style="font-size:0.75rem;">💡若未勾选，将自动生成列名 (Col1..)，请手动映射字段</span>

</div>

<div class="badge">可手动调整列对应关系</div>

</div>

</div>

<div class="badge" id="recordCountBadge">0 条有效联系人</div>

</div>

<tbody id="previewBody"><tr><td colspan="6">等待上传Excel文件</td></tr></tbody>

</table>

</div>

<button class="btn btn-primary" id="generateVcfBtn">✨生成 vCard (.vcf) 并下载</button>

</div>

<div class="info-note">📌手机号码为必填字段，缺失的联系人将自动跳过。生成后可直接导入手机通讯录。</div>

</div>

<footer>vCard 3.0 格式 | 支持字段: 姓名, 手机, 公司, 职位 (群组可选) | 全平台通用</footer>

</div>

// ---------- 全局状态 ----------

let rawDataRows = [];// 存储数据行 (二维数组，不包含标题)

let columnNames = [];// 列名数组

let currentMapping = {

nameIdx: -1,

mobileIdx: -1,

companyIdx: -1,

titleIdx: -1,

groupIdx: -1// 额外可选，增强体验

};

let currentFile = null;

let firstRowAsHeader = true;

// ---------- 智能匹配关键词库 ----------

const KEYWORDS = {

name: ['姓名', '名字', '名称', '全名', '联系人', 'name', 'fullname', '称谓', '昵称'],

mobile: ['手机', '移动电话', '手机号码', '移动', '电话', 'mobile', 'cell', '手机号', 'tel', '联系电话'],

company: ['公司', '企业', '单位', '组织', 'company', 'organization', 'org', '工作单位', '部门'],

title: ['职位', '职务', '头衔', '职称', '岗位', 'title', 'job title', '角色'],

group: ['群组', '分组', '组别', '类别', '标签', 'group', 'category', '团队']

};

// 辅助：匹配最佳列

function autoMatch(fieldType, colNames) {

const kwList = KEYWORDS[fieldType];

if (!kwList || colNames.length === 0) return -1;

const lowerCols = colNames.map(c => c.trim().toLowerCase());

// 优先完全匹配

for (let i = 0; i < lowerCols.length; i++) {

const col = lowerCols[i];

for (let kw of kwList) {

if (col === kw.toLowerCase()) return i;

}

// 包含匹配

for (let i = 0; i < lowerCols.length; i++) {

const col = lowerCols[i];

for (let kw of kwList) {

if (col.includes(kw.toLowerCase())) return i;

}

return -1;

}

// 根据列名初始化映射

function initMappingFromColumns() {

currentMapping = {

nameIdx: autoMatch('name', columnNames),

mobileIdx: autoMatch('mobile', columnNames),

companyIdx: autoMatch('company', columnNames),

titleIdx: autoMatch('title', columnNames),

groupIdx: autoMatch('group', columnNames)

};

renderMappingSelectors();

renderPreviewTable();

}

// 渲染映射下拉框

function renderMappingSelectors() {

const container = document.getElementById('mappingGrid');

if (!container) return;

const fields = [

{ key: 'nameIdx', label: '👤姓名 (FN)', required: false, hint: '用于联系人全名' },

{ key: 'mobileIdx', label: '📞手机号码', required: true, hint: '必填，缺失将跳过' },

{ key: 'companyIdx', label: '🏢公司 (ORG)', required: false, hint: '选填' },

{ key: 'titleIdx', label: '💼职位 (TITLE)', required: false, hint: '选填' },

{ key: 'groupIdx', label: '🏷️群组 (CATEGORIES)', required: false, hint: '可选，多个用逗号分隔' }

];

let html = '';

fields.forEach(f => {

const currentVal = currentMapping[f.key];

html += `<div class="mapping-card-item">

<label>${f.label} ${f.required ? '<span style="color:#d1452b;">*</span>' : ''}<span style="font-weight:normal; font-size:0.7rem; margin-left:6px;">${f.hint}</span></label>

<option value="-1">-- 忽略此字段--</option>`;

for (let i = 0; i < columnNames.length; i++) {

const colName = escapeHtml(columnNames[i] || `列${i+1}`);

const selected = (currentVal === i) ? 'selected' : '';

html += `<option value="${i}" ${selected}>${colName}</option>`;

}

html += `</select></div>`;

});

container.innerHTML = html;

// 绑定事件

document.querySelectorAll('#mappingGrid select').forEach(sel => {

sel.addEventListener('change', (e) => {

const fieldKey = sel.getAttribute('data-field');

const newIdx = parseInt(sel.value, 10);

if (fieldKey && !isNaN(newIdx)) {

currentMapping[fieldKey] = newIdx;

renderPreviewTable();// 实时刷新预览

updateStatsMessage();

}

});

}

// 转义html

function escapeHtml(str) {

if (!str) return '';

return str.replace(/[&<>]/g, function(m) {

if (m === '&') return '&';

if (m === '<') return '<';

if (m === '>') return '>';

return m;

});

}

// vCard 3.0 转义特殊字符

function vcardEscape(str) {

if (str === undefined || str === null) return '';

let s = String(str);

s = s.replace(/\\/g, '\\\\');

s = s.replace(/;/g, '\\;');

s = s.replace(/,/g, '\\,');

s = s.replace(/\n/g, '\\n');

s = s.replace(/\r/g, '');

return s;

}

// 根据映射构建联系人列表（只返回有手机号的）

function buildContactsFromData() {

const contacts = [];

const nameIdx = currentMapping.nameIdx;

const mobileIdx = currentMapping.mobileIdx;

const companyIdx = currentMapping.companyIdx;

const titleIdx = currentMapping.titleIdx;

const groupIdx = currentMapping.groupIdx;

for (let i = 0; i < rawDataRows.length; i++) {

const row = rawDataRows[i];

const getVal = (idx) => (idx !== -1 && row[idx] !== undefined && row[idx] !== null) ? String(row[idx]).trim() : '';

const mobile = getVal(mobileIdx);

if (mobile === '') continue;// 无手机跳过

const nameRaw = getVal(nameIdx);

const company = getVal(companyIdx);

const title = getVal(titleIdx);

const groupRaw = getVal(groupIdx);

let fullName = (nameRaw && nameRaw !== '') ? nameRaw : '未命名';

// 处理姓名拆分

let lastName = '';

let firstName = '';

const trimmed = fullName.trim();

const lastSpace = trimmed.lastIndexOf(' ');

if (lastSpace !== -1) {

lastName = trimmed.substring(0, lastSpace);

firstName = trimmed.substring(lastSpace + 1);

} else {

lastName = trimmed;

firstName = '';

}

if (lastName === '') lastName = fullName;

contacts.push({

fullName: fullName,

lastName: lastName,

firstName: firstName,

mobile: mobile,

company: company,

title: title,

group: groupRaw

});

}

return contacts;

}

// 生成完整vCard文本

function generateVcfBlob() {

const contacts = buildContactsFromData();

if (contacts.length === 0) {

alert('❌没有有效的联系人数据 (至少需要包含手机号码且字段映射正确)');

return null;

}

let vcfString = '';

for (const c of contacts) {

let vcard = 'BEGIN:VCARD\r\n';

vcard += 'VERSION:3.0\r\n';

vcard += `FN:${vcardEscape(c.fullName)}\r\n`;

vcard += `N:${vcardEscape(c.lastName)};${vcardEscape(c.firstName)};;;\r\n`;

vcard += `TEL;TYPE=CELL:${vcardEscape(c.mobile)}\r\n`;

if (c.company && c.company !== '') {

vcard += `ORG:${vcardEscape(c.company)}\r\n`;

}

if (c.title && c.title !== '') {

vcard += `TITLE:${vcardEscape(c.title)}\r\n`;

}

if (c.group && c.group !== '') {

// 支持逗号分隔多群组

const groups = c.group.split(',').map(g => g.trim()).filter(g => g !== '');

if (groups.length) {

const cat = groups.map(g => vcardEscape(g)).join(',');

vcard += `CATEGORIES:${cat}\r\n`;

}

vcard += 'END:VCARD\r\n';

vcfString += vcard;

}

return new Blob([vcfString], { type: 'text/vcard;charset=utf-8' });

}

// 下载VCF

function downloadVcf() {

const blob = generateVcfBlob();

if (!blob) return;

const url = URL.createObjectURL(blob);

const a = document.createElement('a');

a.href = url;

a.download = 'contacts.vcf';

document.body.appendChild(a);

a.click();

document.body.removeChild(a);

URL.revokeObjectURL(url);

const contactCount = buildContactsFromData().length;

alert(`✅成功导出${contactCount} 个联系人，文件已保存为 contacts.vcf`);

}

// 更新统计信息

function updateStatsMessage() {

const statsDiv = document.getElementById('statsInfo');

const recordBadge = document.getElementById('recordCountBadge');

if (!statsDiv) return;

const totalRows = rawDataRows.length;

const validContacts = buildContactsFromData().length;

if (recordBadge) recordBadge.innerText = `${validContacts} / ${totalRows} 有效联系人`;

let missingMobileCount = 0;

const mobileIdx = currentMapping.mobileIdx;

for (let row of rawDataRows) {

if (mobileIdx !== -1 && (!row[mobileIdx] || String(row[mobileIdx]).trim() === '')) missingMobileCount++;

}

statsDiv.innerHTML = `📊总数据行: ${totalRows} 行 | ✅含手机号有效联系人: ${validContacts} 个 | ⚠️缺失手机号: ${missingMobileCount} 行 (将被跳过)`;

}

// 预览表格 (基于当前映射展示前12行)

function renderPreviewTable() {

const previewHead = document.getElementById('previewHead');

const previewBody = document.getElementById('previewBody');

if (!previewHead || !previewBody) return;

// 表头: 姓名,手机,公司,职位,群组,状态

previewHead.innerHTML = `<tr>

</tr>`;

if (rawDataRows.length === 0) {

previewBody.innerHTML = '<tr><td colspan="6">暂无数据</td></tr>';

return;

}

const nameIdx = currentMapping.nameIdx;

const mobileIdx = currentMapping.mobileIdx;

const companyIdx = currentMapping.companyIdx;

const titleIdx = currentMapping.titleIdx;

const groupIdx = currentMapping.groupIdx;

const displayLimit = Math.min(12, rawDataRows.length);

let htmlRows = '';

for (let i = 0; i < displayLimit; i++) {

const row = rawDataRows[i];

const getVal = (idx) => (idx !== -1 && row[idx] !== undefined) ? String(row[idx]).trim() : '';

const nameVal = getVal(nameIdx);

const mobileVal = getVal(mobileIdx);

const companyVal = getVal(companyIdx);

const titleVal = getVal(titleIdx);

const groupVal = getVal(groupIdx);

const hasMobile = mobileVal !== '';

const rowClass = !hasMobile ? 'missing-mobile' : '';

const statusHtml = !hasMobile ? '<span class="warning-badge">⚠️缺手机</span>' : '<span class="valid-badge">✓有效</span>';

htmlRows += `<tr class="${rowClass}">

<td>${escapeHtml(nameVal || '—')}</td>

<td>${escapeHtml(mobileVal || '—')}</td>

<td>${escapeHtml(companyVal || '—')}</td>

<td>${escapeHtml(titleVal || '—')}</td>

<td>${escapeHtml(groupVal || '—')}</td>

<td>${statusHtml}</td>

</tr>`;

}

previewBody.innerHTML = htmlRows;

updateStatsMessage();

}

// 解析Excel文件 (基于SheetJS)

function parseExcel(file) {

if (!file) return;

const firstHeaderFlag = document.getElementById('firstRowHeader').checked;

firstRowAsHeader = firstHeaderFlag;

const reader = new FileReader();

reader.onload = function(e) {

const data = new Uint8Array(e.target.result);

const workbook = XLSX.read(data, { type: 'array', cellDates: false, defval: "" });

// 取第一个工作表

const firstSheet = workbook.Sheets[workbook.SheetNames[0]];

// 转为二维数组 (包括所有行)

const matrix = XLSX.utils.sheet_to_json(firstSheet, { header: 1, defval: "" });

if (!matrix || matrix.length === 0) {

alert('Excel 文件无数据');

return;

}

// 清理全空行

let cleanedMatrix = matrix.filter(row => row.some(cell => cell !== undefined && cell !== null && String(cell).trim() !== ''));

if (cleanedMatrix.length === 0) {

alert('无有效数据行');

return;

}

if (firstRowAsHeader) {

if (cleanedMatrix.length < 2) {

alert('需要至少一行标题+一行数据');

return;

}

// 第一行为标题

let rawHeaders = cleanedMatrix[0].map(cell => (cell === undefined || cell === null) ? '' : String(cell).trim());

columnNames = rawHeaders.map((h, idx) => (h === '' ? `列${idx+1}` : h));

rawDataRows = cleanedMatrix.slice(1);

} else {

// 无标题行: 生成列名 Col1, Col2...

const maxCols = cleanedMatrix[0] ? cleanedMatrix[0].length : 0;

columnNames = Array.from({ length: maxCols }, (_, i) => `列${i+1}`);

rawDataRows = cleanedMatrix.slice(0);

}

// 二次过滤：去除全空行（所有单元格为空或空字符串）

rawDataRows = rawDataRows.filter(row => row.some(cell => cell !== undefined && cell !== null && String(cell).trim() !== ''));

if (rawDataRows.length === 0) {

alert("没有有效数据行");

return;

}

// 显示映射区和预览区

document.getElementById('mappingSection').style.display = 'block';

document.getElementById('previewSection').style.display = 'block';

initMappingFromColumns();

};

reader.onerror = () => { alert("文件读取失败"); };

reader.readAsArrayBuffer(file);

}

// 重置所有状态

function resetApp() {

rawDataRows = [];

columnNames = [];

currentMapping = { nameIdx: -1, mobileIdx: -1, companyIdx: -1, titleIdx: -1, groupIdx: -1 };

document.getElementById('mappingSection').style.display = 'none';

document.getElementById('previewSection').style.display = 'none';

document.getElementById('excelInput').value = '';

const previewBody = document.getElementById('previewBody');

if (previewBody) previewBody.innerHTML = '<tr><td colspan="6">等待上传Excel文件</td></tr>';

const recordBadge = document.getElementById('recordCountBadge');

if (recordBadge) recordBadge.innerText = '0 条有效联系人';

const statsDiv = document.getElementById('statsInfo');

if (statsDiv) statsDiv.innerHTML = '';

currentFile = null;

}

// 处理文件 (拖拽或input)

function handleFile(file) {

if (!file) return;

const ext = file.name.split('.').pop().toLowerCase();

if (ext !== 'xlsx' && ext !== 'xls' && ext !== 'csv') {

alert("请上传 Excel 文件 (.xlsx, .xls) 或 CSV 文件 (兼容)");

return;

}

currentFile = file;

parseExcel(file);

}

// 监听文件选择

const fileInput = document.getElementById('excelInput');

fileInput.addEventListener('change', (e) => {

if (e.target.files.length) handleFile(e.target.files[0]);

});

// 拖拽逻辑

const dropZone = document.getElementById('dropZone');

if (dropZone) {

dropZone.addEventListener('dragover', (e) => {

e.preventDefault();

dropZone.style.background = '#ecf9ff';

dropZone.style.borderColor = '#1f6e8c';

});

dropZone.addEventListener('dragleave', () => {

dropZone.style.background = '#fefefe';

dropZone.style.borderColor = '#b9d0e3';

});

dropZone.addEventListener('drop', (e) => {

e.preventDefault();

dropZone.style.background = '#fefefe';

const files = e.dataTransfer.files;

if (files.length) handleFile(files[0]);

});

}

// 首行标题复选框变动时重新解析当前文件

const headerCheck = document.getElementById('firstRowHeader');

headerCheck.addEventListener('change', () => {

if (currentFile) {

parseExcel(currentFile);

}

});

// 生成按钮

const genBtn = document.getElementById('generateVcfBtn');

if (genBtn) genBtn.addEventListener('click', downloadVcf);

// 重置按钮

const resetBtn = document.getElementById('resetBtn');

if (resetBtn) resetBtn.addEventListener('click', resetApp);

</script>

</body>

</html>