1.新增功能说明
1.1原因
在辅导业主系统上线时发现，因幼儿园共有七十几名职员，在输入[职员其他资料]时需要花费许多时间输入与校对数据，故另外开发用Excel汇入的方式更新[职员其他资料]。
1.2.新增方式
因admin套件并无Excel汇入的方式(或者是我还不知道)，故以客制化的方式开发此功能。
1.4.新增画面
本次文章就只说明excel汇入的画面，Excel汇入功能的画面如下：
选择好excel并按提交按钮成功后画面如下：
1.3.新增套件
- xlrd
新版的xlrd在读取xlsx时会发生问题，建议安装1.2.0版的xlrd。
1.4.新增说明
本专案的名称是star，以下将用star作为说明。
2.新增方式
2.1.新增templates目录
因为是客制开发不是使用admin套件功能，我将客制程序的html样版放在[star—templates]内，在star下手动新增templates目录。
2.2.修改settings.py
告诉Django去新增的位置找views.py所使用的html样版，修改[star—star—settings.py]内的[TEMPLATES—DIRS]参数如下图。
2.3.新增urls.py与views.py
如果没有的话，在[star—star]下新增urls.py与views.py两只程式。
2.4.views.py加入代码
因主要说明汇入功能，所以只列出以下函式
# 显示汇入画面的函式
@permission_required('salary.add_wagecreate', login_url='/login/')
def wage(request):
last_month = datetime.now() + relativedelta(months=-1)
last_his = last_month.strftime("%Y-%m")
username = request.user.username
return render(request, 'wage.html', {'last_his': last_his, 'username': username})
# 处理excel的函式
def wage_import(request):
if request.method == "POST":
info = ''
last_month = datetime.now() + relativedelta(months=-1)
last_his = last_month.strftime("%Y-%m")
wagecreates = WageCreate.objects.filter(his=last_his)
if wagecreates.count() > 0:
wagecreate = wagecreates.first()
if wagecreate.status == '0':
if wagecreate.employee_create == '0':
employeeothers = EmployeeOther.objects.filter(his=last_his)
if employeeothers.count() > 0:
file = request.FILES['wagefile']
type_excel = file.name.split('.')[1]
if type_excel in ['xlsx', 'xls']:
# 因客户实际状况，所以抓出来的excel资料以职员[姓名]为主键
# excel格式也是客户在系统上线之前计算工资的格式
# 开始解析上传的excel表格
wb = xlrd.open_workbook(filename=None, file_contents=file.read())
sheet_names = wb.sheet_names()
# 延时2
delay_index = 7
delay_name_index = 3
delay_value_index = 5
delay_field_names = []
delay_table = wb.sheets()[delay_index]
rows = delay_table.nrows
try:
num = 0
delay_field_names.append(delay_table.row_values(0)[delay_name_index])
delay_field_names.append(delay_table.row_values(0)[delay_value_index])
for i in range(1, rows):
row_values = delay_table.row_values(i)
# 姓名
name = str(row_values[delay_name_index]).strip()
# 延时2奖金
delay = str(row_values[delay_value_index]).strip()
if name == '' or not isinstance(float(delay), float):
break
else:
handle_delay(request.user, last_his, name, float(delay))
num += 1
except Exception as e:
info = '读取[{}]页签失败:'.format(sheet_names[delay_index]) + str(e)
info += "[{}]页签({}, {})总共读取[{}]笔延时2奖金资料成功<br/>".format(sheet_names[delay_index],
delay_field_names[0], delay_field_names[1], num)
# 个税
tax_index = 2
tax_name_index = 3
tax_value_index = 18
tax_field_names = []
tax_table = wb.sheets()[tax_index]
rows = tax_table.nrows
try:
num = 0
tax_field_names.append(tax_table.row_values(0)[tax_name_index])
tax_field_names.append(tax_table.row_values(0)[tax_value_index])
for i in range(1, rows):
row_values = tax_table.row_values(i)
# 姓名
name = str(row_values[tax_name_index]).strip()
# 个税
tax = str(row_values[tax_value_index]).strip()
if name == '' or not isinstance(float(tax), float):
break
else:
handle_tax(request.user, last_his, name, float(tax))
num += 1
except Exception as e:
info = '读取[{}]页签失败:'.format(sheet_names[tax_index]) + str(e)
info += "[{}]页签({}, {})总共读取[{}]笔个税成绩资料成功<br/>".format(sheet_names[tax_index],
tax_field_names[0], tax_field_names[1], num)
# 考核分数
asses_index = 6
asses_name_index = 3
asses_value_index = 5
asses_field_names = []
asses_table = wb.sheets()[asses_index]
rows = asses_table.nrows
try:
num = 0
asses_field_names.append(asses_table.row_values(0)[asses_name_index])
asses_field_names.append(asses_table.row_values(0)[asses_value_index])
for i in range(1, rows):
row_values = asses_table.row_values(i)
# 姓名
name = str(row_values[asses_name_index]).strip()
# 考核成绩
grade = str(row_values[asses_value_index]).strip()
if name == '' or not isinstance(float(grade), float):
break
else:
handle_asses(request.user, last_his, name, float(grade))
num += 1
except Exception as e:
info = '读取[{}]页签失败:'.format(sheet_names[asses_index]) + str(e)
info += "[{}]页签({}, {})总共读取[{}]笔考核成绩资料成功<br/>".format(sheet_names[asses_index],
asses_field_names[0], asses_field_names[1], num)
else:
info = '上传文件不是excel格式'
else:
info = '尚无[{}]月份的[职员其他资料]，请先产生该月份的[工资底稿]'.format(last_his)
else:
info = '[{}]月份[工资产生记录]的[职员其他资料]已确认输入，不可修改'.format(last_his)
else:
info = '[{}]月份的[工资产生记录]已锁定，不可修改'.format(last_his)
else:
info = '请先产生[{}]月份的[工资产生记录]'.format(last_his)
else:
info = '不是POST请求'
return render(request, 'wage.html', {'info': safestring.mark_safe((info)), 'last_his': last_his})
# 处理延时2奖金的函式
def handle_delay(user, his, name, delay):
employees = Employee.objects.filter(title=name)
if employees.count() > 0:
employee = employees.first()
employeeothers = EmployeeOther.objects.filter(his=his, employee=employee)
if employeeothers.count() > 0:
employeeother = employeeothers.first()
employeeother.delay2_bonus = delay
employeeother.update_user = user
employeeother.save()
# 处理个税的函式
def handle_tax(user, his, name, tax):
employees = Employee.objects.filter(title=name)
if employees.count() > 0:
employee = employees.first()
employeeothers = EmployeeOther.objects.filter(his=his, employee=employee)
if employeeothers.count() > 0:
employeeother = employeeothers.first()
employeeother.tax = tax
employeeother.update_user = user
employeeother.save()
# 处理考核成绩的函式
def handle_asses(user, his, name, grade):
employees = Employee.objects.filter(title=name)
if employees.count() > 0:
employee = employees.first()
employeeothers = EmployeeOther.objects.filter(his=his, employee=employee)
if employeeothers.count() > 0:
employeeother = employeeothers.first()
employeeother.asses_grade = grade
employeeother.update_user = user
employeeother.save()
2.5.加入html样版
wage.html位置如2.1.。
{% load static %}
<!DOCTYPE html>
<html lang="zh-hans">
<head>
<meta charset="UTF-8">
<title>工资资料汇入</title>
<link type="text/css" rel="stylesheet" href="{% static 'css/bootstrap.min.css' %}">
</head>
<body>
<div id="title" class="row">
<div id="header_left" class="col-2"> </div>
<div id="header_center" class="col-6"> </div>
<div id="header_right" class="col-4">欢迎您！{{ username }} | <a href="/logout/">注销</a> | <a href="/">首页</a> | {% if request.user.is_staff %} <a href="/admin/">管理页面</a>{% endif %}</div>
</div>
<div id="content" class="container">
<form method="post" action="/wage/import/" enctype="multipart/form-data">
<div class="form-group">
<label for="wagefile">[{{ last_his }}]月份工资档案(.xlsx/.xls)：</label>{% csrf_token %}
<input type="file" class="form-control" id="wagefile" name="wagefile">
</div>
<input type="submit" class="form-control btn btn-primary" value="提交">
<label style="font-size: 16px;color:red;margin: 10px">{{ info }}</label>
</form>
</div>
</body>
</html>
2.4.urls.py加入代码
主要是加入[wage/]与[wage/import/]两个路径。
from django.contrib import admin
from django.urls import path, include
from . import views
urlpatterns = [
path('', views.index),
path('login/', views.login),
path('logout/', views.logout),
path('wage/', views.wage),
path('wage/import/', views.wage_import),
path('admin/', admin.site.urls),
path('salary/', include('salary.urls', namespace='salary')),
]
608
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
