之前在做后台管理系统时候，有导出和导入excel的需求，其实不难，很快就用xlrd和xlwt实现了。
首先先安装xlrd和xlwt
import xlrd
import xlwt导入excel业务实现
def xls_import():
"""学生导入"""
form = validators.XlsImportStudent().validate_()
file = request.files.get('file')
filename = f'{uuid.uuid1().hex}.{form.file_extension.data}'
# 保存文件
file_path = 'inxlsx/' + filename
path = os.getcwd() + '/plugins/public/'
file_path = os.path.join(path, file_path)
file.save(file_path + filename)
data = open_excel(file=file_path + filename).sheets()[0]
nrows = data.nrows
pass_num = 0
success_num = 0
for i in range(1, nrows):
# 判断学生是否存在
student = Student.query.filter_by(name=data.row_values(i)[0], phone=data.row_values(i)[1]).first()
if student:
pass_num += 1
pass
else:
# 数据处理
# 性别
gender = data.row_values(i)[5]
if gender == "男":
gender_int = 1
else:
gender_int = 2
# 政治面貌
political = data.row_values(i)[10]
if political == "群众":
political_int = 1
elif political == "中共共青团员":
political_int = 2
elif political == "中共预备党员":
political_int = 3
elif political == "中共党员":
political_int = 4
else:
political_int = 5
# 最高学历
education = data.row_values(i)[15]
if education == "小学":
education_int = 1
elif education == "初中":
education_int = 2
elif education == "高中":
education_int = 3
elif education == "大专":
education_int = 4
elif education == "本科":
education_int = 5
elif education == "硕士研究生":
education_int = 6
elif education == "博士研究生":
education_int = 7
else:
education_int = 8
# 日期处理
birthday = data.row_values(i)[4]
# print(birthday)
birthday = datetime.date.fromordinal(int(birthday) + 693594)
# dt = datetime.date(1993, 4, 27)
# print(dt.toordinal())
graduated_time = data.row_values(i)[17]
graduated_time = datetime.date.fromordinal(int(graduated_time) + 693594)
# 学校和专业
profession_code = data.row_values(i)[3]
profession = Profession.query.filter_by(profession_code=profession_code).first()
if profession:
Student(
bh=uuid.uuid4().hex,
name=data.row_values(i)[0],
phone=data.row_values(i)[1],
id_card=data.row_values(i)[2],
school=profession.school_id,
profession=profession.id,
birthday=birthday,
gender=gender_int,
mail=data.row_values(i)[6],
clan=data.row_values(i)[7],
hometown=data.row_values(i)[8],
address=data.row_values(i)[9],
political=political_int,
health=data.row_values(i)[11],
birth_place=data.row_values(i)[12],
home_address=data.row_values(i)[13],
work_unit=data.row_values(i)[14],
education=education_int,
graduated_school=data.row_values(i)[16],
graduated_time=graduated_time,
avatar=data.row_values(i)[18],
source=2
).direct_add_()
success_num += 1
else:
return result_format(data={'fail': '第' + str(i+1) + '条数据的专业编码不存在，请检查！'})
Student.static_commit_()
return ''导出excel业务实现
def xls_out_port():
"""学生导出"""
form = validators.XlsOutportStudent(request.args).validate_()
query = Student.query.filter_by(status=1).order_by(Student.id.desc())
if form.profession.data:
query = query.filter_by(profession=form.profession.data)
paginate = query.paginate(page=form.page.data, per_page=form.limit.data, error_out=True)
funcs = [orm_func('change_school_info')]
data = paginate_info(paginate=paginate, items=[item.serialization(funcs=funcs) for item in paginate.items])
# 生成excel对象
filename = xlwt.Workbook()
sheet = filename.add_sheet("Sheet1")
# 写入第一行数据
row0 = ['姓名', '手机号', '身份证号', '报名学校', '报名专业', '出生日期',
'性别', '邮箱', '民族', '籍贯', '通信地址', '政治面貌', '身体状况',
'出生地', '家庭地址', '工作单位', '最高学历', '毕业学校', '毕业日期', '用户头像']
for i in range(0, len(row0)):
sheet.write(0, i, row0[i], set_style('sky_blue'))
for index, item in enumerate(data['items']):
row_start = 1 + index
sheet.write(row_start, 0, item['name'], set_style())
sheet.write(row_start, 1, item['phone'], set_style())
sheet.write(row_start, 2, item['id_card'], set_style())
sheet.write(row_start, 3, item['school'], set_style())
sheet.write(row_start, 4, item['profession'], set_style())
sheet.write(row_start, 5, item['birthday'].split(' ')[0], set_style())
# 性别
if item['gender'] == 1:
gender = "男"
else:
gender = "女"
sheet.write(row_start, 6, gender, set_style())
sheet.write(row_start, 7, item['mail'], set_style())
sheet.write(row_start, 8, item['clan'], set_style())
sheet.write(row_start, 9, item['hometown'], set_style())
sheet.write(row_start, 10, item['address'], set_style())
# 政治面貌
if item['political'] == 1:
political = "群众"
elif item['political'] == 2:
political = "中共共青团员"
elif item['political'] == 3:
political = "中共预备党员"
elif item['political'] == 4:
political = "中共党员"
else:
political = "其他"
sheet.write(row_start, 11, political, set_style())
sheet.write(row_start, 12, item['health'], set_style())
sheet.write(row_start, 13, item['birth_place'], set_style())
sheet.write(row_start, 14, item['home_address'], set_style())
sheet.write(row_start, 15, item['work_unit'], set_style())
# 学历
if item['education'] == 1:
education = "小学"
elif item['education'] == 2:
education = "初中"
elif item['education'] == 3:
education = "高中"
elif item['education'] == 4:
education = "大专"
elif item['education'] == 5:
education = "本科"
elif item['education'] == 6:
education = "硕士研究生"
elif item['education'] == 7:
education = "博士研究生"
else:
education = "其他"
sheet.write(row_start, 16, education, set_style())
sheet.write(row_start, 17, item['graduated_school'], set_style())
sheet.write(row_start, 18, item['graduated_time'].split(' ')[0], set_style())
sheet.write(row_start, 19, item['avatar'], set_style())
# 设置单元格宽度
sheet.col(1).width = 4500
sheet.col(2).width = 6500
sheet.col(3).width = 3000
sheet.col(4).width = 3000
sheet.col(5).width = 3700
sheet.col(7).width = 5500
sheet.col(10).width = 5000
sheet.col(11).width = 3000
sheet.col(12).width = 3000
sheet.col(13).width = 3000
sheet.col(14).width = 5000
sheet.col(15).width = 5000
sheet.col(16).width = 3000
sheet.col(17).width = 5000
sheet.col(18).width = 3700
sheet.col(19).width = 15000
file_name = '奥翔教育学生信息-' + datetime.datetime.now().strftime("%Y%m%d%H%M%S%f") + '.xlsx'
# 保存文件
file_path = 'outxlsx/' + file_name
path = os.getcwd() + '/plugins/public/'
file_path = os.path.join(path, file_path)
filename.save(file_path)
return ''版权属于：Jolly
本文链接：https://totoro.site/index.php/archives/86/
关于转载：原创文章，禁止转载
