<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>拜访情况通报</title>
<style>
body {
font-family: Arial, sans-serif;
background: linear-gradient(135deg, #f8f8f8, #e0e0e0);
margin: 0;
padding: 20px;
display: flex;
justify-content: center;
align-items: center;
min-height: 100vh;
box-sizing: border-box;
}
.container {
background-color: #ffffff;
padding: 30px;
border-radius: 15px;
box-shadow: 0 10px 30px rgba(0, 0, 0, 0.1);
width: 90%;
max-width: 1200px;
position: relative;
overflow-x: auto;
}
h2 {
color: #d32f2f;
text-align: center;
margin-bottom: 20px;
font-size: 28px;
font-weight: bold;
}
.upload-section {
background-color: #fff3e0;
padding: 20px;
border-radius: 10px;
box-shadow: 0 5px 15px rgba(0, 0, 0, 0.1);
margin-bottom: 20px;
text-align: center;
}
.file-upload {
background-color: #d32f2f;
color: white;
padding: 10px 20px;
border-radius: 5px;
cursor: pointer;
display: inline-block;
margin: 10px 0;
font-size: 16px;
transition: background-color 0.3s ease;
}
.file-upload:hover {
background-color: #b71c1c;
}
.progress-bar {
width: 100%;
background-color: #e0e0e0;
border-radius: 5px;
overflow: hidden;
margin: 10px 0;
display: none;
}
.progress-bar-inner {
width: 0;
height: 10px;
background-color: #d32f2f;
transition: width 0.3s ease;
}
.success-message {
color: #388e3c;
font-weight: bold;
margin: 10px 0;
display: none;
}
.buttons {
display: flex;
justify-content: center;
gap: 20px;
margin: 20px 0;
flex-wrap: wrap;
}
button {
background-color: #d32f2f;
color: white;
padding: 12px 24px;
border: none;
border-radius: 5px;
cursor: pointer;
font-size: 16px;
transition: background-color 0.3s ease;
}
button:hover {
background-color: #b71c1c;
}
#downloadBtn {
background-color: #ffa000;
}
#downloadBtn:hover {
background-color: #e65100;
}
table {
width: 100%;
border-collapse: collapse;
margin-top: 20px;
box-shadow: 0 5px 15px rgba(0, 0, 0, 0.1);
}
th, td {
border: 1px solid #ddd;
padding: 12px;
text-align: center;
white-space: nowrap;
}
th {
background-color: #d32f2f;
color: white;
font-weight: bold;
}
td {
background-color: #fff3e0;
}
</style>
</head>
<body>
<div class="container">
<h2>拜访情况通报</h2>
<div class="upload-section">
<label for="excelFile极" class="file-upload">上传 Excel 文件</label>
<input type="file" id="excelFile" accept=".xlsx, .xls" style="display: none;"/>
<div class="progress-bar">
<div class="progress-bar-inner"></div>
</div>
<div class="success-message">文件上传成功！</div>
</div>
<div class="buttons">
<button onclick="processExcel()">生成通报</button>
<button id="downloadBtn" onclick="downloadExcel()" disabled>下载 Excel</button>
</div>
<table id="reportTable">
<thead>
<tr>
<th>分公司</th>
<th>清单客户拜访次数</th>
<th>非清单客户拜访次数</th>
<th>清单客户数量</th>
<th>非清单客户数量</th>
<th>二次拜访客户数量</th>
<th>添加微信</th>
<th>获取商机</th>
</tr>
</thead>
<tbody>
<!-- 数据将在这里插入 -->
</tbody>
</table>
</div>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.16.9/xlsx.full.min.js"></script>
<script>
// 分公司名称映射关系
const branchMapping = {
"碑林区分公司": "碑林",
"高新区分公司": "高新",
"莲湖区分公司": "莲湖",
"未央区分公司": "未央",
"新城区分公司": "新城",
"雁塔区分公司": "雁塔",
"浐灞区分公司": "浐灞",
"航天新区分公司": "航天",
"经开区分公司": "经开",
"曲江新区分公司": "曲江",
"灞桥区分公司": "灞桥",
"沣东新区分公司": "沣东",
"长安区分公司": "长安",
"高陵分公司": "高陵",
"鄠邑区分公司": "鄠邑",
"蓝田县分公司": "蓝田",
"临潼区分公司": "临潼",
"阎良区分公司": "阎良",
"周至县分公司": "周至"
};
// 分公司名称固定顺序
const branchOrder = [
"碑林", "高新", "莲湖", "未央", "新城", "雁塔", "浐灞", "航天", "经开", "曲江",
"灞桥", "沣东", "长安", "高陵", "鄠邑", "蓝田", "临潼", "阎良", "周至"
];
let tableData = []; // 存储表格数据，用于生成 Excel
// 文件上传逻辑
const fileInput = document.getElementById('excelFile');
const progressBar = document.querySelector('.progress-bar');
const progressBarInner = document.querySelector('.progress-bar-inner');
const successMessage = document.querySelector('.success-message');
fileInput.addEventListener('change', function() {
if (fileInput.files.length > 0) {
const reader = new FileReader();
// 显示进度条
progressBar.style.display = 'block';
progressBarInner.style.width = '0';
successMessage.style.display = 'none';
reader.onprogress = function(e) {
if (e.lengthComputable) {
const percent = (e.loaded / e.total) * 100;
progressBarInner.style.width = percent + '%';
}
};
reader.onload = function(e) {
// 上传完成
progressBarInner.style.width = '100%';
setTimeout(() => {
progressBar.style.display = 'none';
successMessage.style.display = 'block';
}, 500);
};
reader.readAsArrayBuffer(fileInput.files[0]);
}
});
function processExcel() {
if (fileInput.files.length === 0) {
alert('请先上传文件！');
return;
}
const reader = new FileReader();
reader.onload = function(e) {
const data = new Uint8Array(e.target.result);
const workbook = XLSX.read(data, {type: 'array'});
const firstSheet = workbook.Sheets[workbook.SheetNames[0]];
const json = XLSX.utils.sheet_to_json(firstSheet, {header:1});
let stats = {}; // 用于存储分公司统计数据
let visitCounts = {}; // 用于记录每个客户的拜访次数
json.slice(1).forEach(row => { // 忽略标题行
if (row[5] === '已结束') { // F列是流程状态
const branchFullName = row[8]; // I列是分公司全称
const branchShortName = branchMapping[branchFullName]; // 映射为简称
if (!branchShortName) return; // 如果找不到映射则跳过
const hotelName = row[1]; // B列是酒店名称
stats[branchShortName] = stats[branchShortName] || {
清单客户拜访次数: 0,
非清单客户拜访次数: 0,
添加微信: 0,
获取商机: 0,
清单客户数量: new Set(),
非清单客户数量: new Set(),
二次拜访客户数量: 0
};
// 统计拜访次数
if (!visitCounts[branchShortName]) visitCounts[branchShortName] = {};
if (!visitCounts[branchShortName][hotelName]) visitCounts[branchShortName][hotelName] = 0;
visitCounts[branchShortName][hotelName]++;
// 统计清单/非清单客户
if (row[11] === '是') { // L列是否清单客户
stats[branchShortName].清单客户拜访次数++;
stats[branchShortName].清单客户数量.add(hotelName); // 添加到清单客户集合
} else if (row[11] === '否') {
stats[branchShortName].非清单客户拜访次数++;
stats[branchShortName].非清单客户数量.add(hotelName); // 添加到非清单客户集合
}
if (row[30] === '是') stats[branchShortName].添加微信++; // AE列是否添加微信
if (row[32] === '是') stats[branchShortName].获取商机++; // AG列是否获取商机
}
});
// 计算二次拜访客户数量
for (const branch in visitCounts) {
let count = 0;
for (const hotel in visitCounts[branch]) {
if (visitCounts[branch][hotel] >= 2) count++;
}
stats[branch].二次拜访客户数量 = count;
}
// 生成表格
const tbody = document.querySelector('#reportTable tbody');
tbody.innerHTML = '';
tableData = []; // 重置表格数据
branchOrder.forEach(branch => {
const data = stats[branch] || {
清单客户拜访次数: 0,
非清单客户拜访次数: 0,
添加微信: 0,
获取商机: 0,
清单客户数量: new Set(),
非清单客户数量: new Set(),
二次拜访客户数量: 0
};
const row = `<tr>
<td>${branch}</td>
<td>${data.清单客户拜访次数}</td>
<td>${data.非清单客户拜访次数}</td>
<td>${data.清单客户数量.size}</td>
<td>${data.非清单客户数量.size}</td>
<td>${data.二次拜访客户数量}</td>
<td>${data.添加微信}</td>
<td>${data.获取商机}</td>
</tr>`;
tbody.innerHTML += row;
tableData.push([
branch,
data.清单客户拜访次数,
data.非清单客户拜访次数,
data.清单客户数量.size,
data.非清单客户数量.size,
data.二次拜访客户数量,
data.添加微信,
data.获取商机
]);
});
document.getElementById('downloadBtn').disabled = false; // 启用下载按钮
};
reader.readAsArrayBuffer(fileInput.files[0]);
}
function downloadExcel() {
const wsData = [
['分公司', '清单客户拜访次数', '非清单客户拜访次数', '清单客户数量', '非清单客户数量', '二次拜访客户数量', '添加微信', '获取商机'],
...tableData
]; // 添加表头
const ws = XLS极X.utils.aoa_to_sheet(wsData);
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, "拜访情况通报");
XLSX.writeFile(wb, "拜访情况通报.xlsx");
}
</script>
</body>
</html>
index.html
style.css
index.js