from flask import Flask, render_template_string, send_file, request
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
import io
import datetime
app = Flask(__name__)
HTML_TEMPLATE = '''
<!DOCTYPE html>
<html>
<head>
<title>巩固率计算系统</title>
<style>
body { font-family: Arial, sans-serif; max-width: 800px; margin: 20px auto; padding: 20px; }
.container { background: #f5f5f5; padding: 20px; border-radius: 8px; box-shadow: 0 0 10px rgba(0,0,0,0.1); }
h1 { color: #2c3e50; text-align: center; }
.instructions { background: white; padding: 15px; border-radius: 5px; margin: 20px 0; }
.download-btn {
display: inline-block;
background: #3498db;
color: white;
padding: 12px 24px;
border-radius: 5px;
text-decoration: none;
transition: background 0.3s;
}
.download-btn:hover { background: #2980b9; }
.footer { text-align: center; margin-top: 30px; color: #666; }
form { margin-top: 20px; }
input[type="file"] { margin-bottom: 10px; }
input[type="submit"] {
background: #3498db;
color: white;
padding: 12px 24px;
border-radius: 5px;
border: none;
cursor: pointer;
transition: background 0.3s;
}
input[type="submit"]:hover { background: #2980b9; }
</style>
</head>
<body>
<div class="container">
<h1>学员巩固率计算系统</h1>
<div class="instructions">
<h2>使用说明</h2>
<ol>
<li>点击下方按钮下载模板文件</li>
<li>在Excel文件中填写学员信息</li>
<li>"是否在册"列请填写"是"或"否"</li>
<li>保存文件后，可上传文件查看巩固率</li>
</ol>
<h3>计算公式说明</h3>
<ul>
<li>初始人数 = 总记录数（自动计算）</li>
<li>在册人数 = 标记"是"的数量（自动统计）</li>
<li>巩固率 = 在册人数 / 初始人数（自动计算百分比）</li>
</ul>
</div>
<center>
<a href="/download" class="download-btn">下载Excel模板</a>
<p style="color: #666; margin-top: 10px;">文件版本：{{ version }}</p>
<form action="/upload" method="post" enctype="multipart/form-data">
<input type="file" name="file">
<input type="submit" value="上传文件计算巩固率">
</form>
</center>
</div>
<div class="footer">
<p>© 2023 教务管理系统 | 技术支持：信息中心</p>
</div>
</body>
</html>
'''
def create_excel_template():
wb = Workbook()
ws = wb.active
ws.title = "巩固率计算"
# 设置列标题
headers = ["序号", "姓名", "学号", "入学日期", "是否在册（是/否）"]
ws.append(headers)
# 设置数据验证
dv = ws.data_validations.DataValidation(
type="list",
formula1='"是,否"',
allow_blank=True
)
dv.add(f'E2:E1000')
ws.add_data_validation(dv)
# 设置计算公式
ws['G1'] = "统计时间"
ws['G2'] = datetime.datetime.now().strftime("%Y-%m-%d")
ws['H1'] = "初始人数"
ws['H2'] = "=COUNTA(B2:B1000)"
ws['I1'] = "在册人数"
ws['I2'] = '=COUNTIF(E2:E1000,"是")'
ws['J1'] = "巩固率"
ws['J2'] = "=I2/H2"
ws['J2'].number_format = '0.00%'
# 设置表格样式
header_fill = PatternFill(start_color="007bff", end_color="007bff", fill_type="solid")
header_font = Font(color="FFFFFF", bold=True)
align_center = Alignment(horizontal='center', vertical='center')
for col in range(1, len(headers)+1):
cell = ws.cell(1, col)
cell.fill = header_fill
cell.font = header_font
cell.alignment = align_center
# 设置列宽
column_widths = [8, 15, 15, 15, 18, 5, 12, 12, 12, 12]
for i, width in enumerate(column_widths, 1):
ws.column_dimensions[get_column_letter(i)].width = width
# 冻结首行
ws.freeze_panes = 'A2'
# 保存到内存
output = io.BytesIO()
wb.save(output)
output.seek(0)
return output
@app.route('/')
def index():
return render_template_string(HTML_TEMPLATE, version="1.2")
@app.route('/download')
def download():
excel_file = create_excel_template()
return send_file(
excel_file,
mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
as_attachment=True,
download_name=f'学员巩固率模板_{datetime.datetime.now().strftime("%Y%m%d")}.xlsx'
)
@app.route('/upload', methods=['POST'])
def upload():
file = request.files['file']
if file:
try:
wb = load_workbook(file.stream)
ws = wb.active
# 计算初始人数
initial_count = len([cell for cell in ws['B'][1:] if cell.value])
# 计算在册人数
enrolled_count = len([cell for cell in ws['E'][1:] if cell.value == '是'])
# 计算巩固率
if initial_count > 0:
consolidation_rate = enrolled_count / initial_count
else:
consolidation_rate = 0
# 更新统计信息
ws['G1'] = "统计时间"
ws['G2'] = datetime.datetime.now().strftime("%Y-%m-%d")
ws['H1'] = "初始人数"
ws['H2'] = initial_count
ws['I1'] = "在册人数"
ws['I2'] = enrolled_count
ws['J1'] = "巩固率"
ws['J2'] = consolidation_rate
ws['J2'].number_format = '0.00%'
# 保存到内存
output = io.BytesIO()
wb.save(output)
output.seek(0)
return send_file(
output,
mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
as_attachment=True,
download_name=f'学员巩固率计算结果_{datetime.datetime.now().strftime("%Y%m%d")}.xlsx'
)
except Exception as e:
return f"文件处理出错: {str(e)}"
return "未上传文件"
if __name__ == '__main__':
app.run(host='0.0.0.0', port=5000, debug=True)