1. 生成对应Excel模板
2. 获取用户上传的Excel文件
3. 获取Excel数据
主要为自己工作内容记录,十分笼统,欢迎私信交流~~
import datetime
import time
import json
import pymysql
from django.conf import settings
from django.forms import model_to_dict
from django.http import HttpResponse, JsonResponse
from rest_framework.views import APIView
from openpyxl import Workbook, load_workbook
from authority.xincryption import XDECRYPT, XENCRYPT
from authority.models import Role, Menu
from echart_data.models import Version
from .models import Account, Role, Function, Menu, Log
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
from django.db.models import Q
import os
class GenerateExcelTemplate(APIView):
def post(self, request):
user_id = request.META.get("HTTP_USER_ID")
user_name = request.META.get("HTTP_USER_NAME")
roles = request.META.get("HTTP_ROLES")
# aToZ = [chr(i) for i in range(65, 91)]
# 0. 生成Excel模板
wb = Workbook()
ws = wb.active
ws.title = "系统信息，请勿修改"
ws['A1'] = '程序识别信息，请勿修改： '
ws['B1'] = 'USER_ID: '
ws['C1'] = XENCRYPT(user_id)
ws['D1'] = 'USER_NAME: '
ws['E1'] = user_name
ws['F1'] = 'ROLES: '
ws['G1'] = XENCRYPT(roles)
ws['H1'] = 'TIMESTAMP: '
ws['I1'] = XENCRYPT(str(datetime.datetime.now()))
tableTitle = ['术前诊断', '既往手术史', '手术一般信息', '股骨情况',
'软组织平衡情况', '胫骨平台截骨情况', '髌骨情况', '安装假体情况', '止血带使用情况']
for title in tableTitle:
ws_new = wb.create_sheet(title=title)
ws_new['A1'] = '程序识别信息，请勿修改： '
ws_new['A1'].font = Font(italic=True, color='AAAAAA')
ws_new['B1'] = '数据库: '
ws_new['B1'].font = Font(italic=True, color='AAAAAA')
ws_new['C1'] = XENCRYPT('idm_data_tools_data')
ws_new['C1'].font = Font(italic=True, color='AAAAAA')
ws_new['D1'] = '表: '
ws_new['D1'].font = Font(italic=True, color='AAAAAA')
ws_new['E1'].font = Font(italic=True, color='AAAAAA')
ws_new['F1'] = XENCRYPT(str(200010))
ws_new['F1'].font = Font(italic=True, color='AAAAAA')
ws_new['A2'] = '列名（英文）'
ws_new['A2'].font = Font(bold=True)
ws_new['A3'] = '列名（中文）'
ws_new['A3'].font = Font(bold=True)
ws_new['A4'] = '数据类型（最大长度）'
ws_new['A4'].font = Font(bold=True)
ws_new.column_dimensions['A'].width = 20
# 术前诊断
if title == '术前诊断':
ws_new['E1'] = XENCRYPT('echart_data_pre_diagnosis')
# 列名(英文)
ws_new['B2'].font = Font(bold=True, color="BBBBBB")
ws_new['B2'] = 'patient_name'
ws_new['C2'].font = Font(bold=True, color="BBBBBB")
ws_new['C2'] = 'patient_id'
ws_new['D2'].font = Font(bold=True, color="BBBBBB")
ws_new['D2'] = 'patient_age'
ws_new['E2'].font = Font(bold=True, color="BBBBBB")
ws_new['E2'] = 'patient_sex'
ws_new['F2'].font = Font(bold=True, color="BBBBBB")
ws_new['F2'] = 'knee_replacement'
ws_new['G2'].font = Font(bold=True, color="BBBBBB")
ws_new['G2'] = 'knee_deformity'
# 列名(中文)
ws_new['B3'].font = Font(color="BBBBBB")
ws_new['B3'] = '患者姓名'
ws_new['C3'].font = Font(color="BBBBBB")
ws_new['C3'] = '患者ID'
ws_new['D3'].font = Font(color="BBBBBB")
ws_new['D3'] = '患者年龄'
ws_new['E3'].font = Font(color="BBBBBB")
ws_new['E3'] = '患者性别'
ws_new['F3'].font = Font(color="BBBBBB")
ws_new['F3'] = '膝置换'
ws_new['G3'].font = Font(color="BBBBBB")
ws_new['G3'] = '膝畸形'
# 数据类型
ws_new['B4'].font = Font(color="BBBBBB")
ws_new['B4'] = '字符型(50)'
ws_new['C4'].font = Font(color="BBBBBB")
ws_new['C4'] = '字符型(50)'
ws_new['D4'].font = Font(color="BBBBBB")
ws_new['D4'] = '字符型(50)'
ws_new['E4'].font = Font(color="BBBBBB")
ws_new['E4'] = '字符型(50)'
ws_new['F4'].font = Font(color="BBBBBB")
ws_new['F4'] = '字符型(50)'
ws_new['G4'].font = Font(color="BBBBBB")
ws_new['G4'] = '字符型(50)'
# 既往手术史
if title == '既往手术史':
ws_new['E1'] = XENCRYPT('echart_data_pre_surgical_history')
# 列名(英文)
ws_new['B2'].font = Font(bold=True, color="BBBBBB")
ws_new['B2'] = 'patient_name'
ws_new['C2'].font = Font(bold=True, color="BBBBBB")
ws_new['C2'] = 'patient_id'
ws_new['D2'].font = Font(bold=True, color="BBBBBB")
ws_new['D2'] = 'patient_age'
ws_new['E2'].font = Font(bold=True, color="BBBBBB")
ws_new['E2'] = 'patient_sex'
ws_new['F2'].font = Font(bold=True, color="BBBBBB")
ws_new['F2'] = 'surgical_history'
ws_new['G2'].font = Font(bold=True, color="BBBBBB")
ws_new['G2'] = 'operation_time'
ws_new['H2'].font = Font(bold=True, color="BBBBBB")
ws_new['H2'] = 'puncture'
ws_new['I2'].font = Font(bold=True, color="BBBBBB")
ws_new['I2'] = 'small_needle_knife'
ws_new['J2'].font = Font(bold=True, color="BBBBBB")
ws_new['J2'] = 'arthroscopy_knee'
ws_new['K2'].font = Font(bold=True, color="BBBBBB")
ws_new['K2'] = 'incision'
ws_new['L2'].font = Font(bold=True, color="BBBBBB")
ws_new['L2'] = 'osteotomy'
ws_new['M2'].font = Font(bold=True, color="BBBBBB")
ws_new['M2'] = 'replacement'
ws_new['N2'].font = Font(bold=True, color="BBBBBB")
ws_new['N2'] = 'revision'
ws_new['O2'].font = Font(bold=True, color="BBBBBB")
ws_new['O2'] = 'other'
ws_new['P2'].font = Font(bold=True, color="BBBBBB")
ws_new['P2'] = 'surgery_history'
ws_new['Q2'].font = Font(bold=True, color="BBBBBB")
ws_new['Q2'] = 'con_time'
ws_new['R2'].font = Font(bold=True, color="BBBBBB")
ws_new['R2'] = 'con_puncture'
ws_new['S2'].font = Font(bold=True, color="BBBBBB")
ws_new['S2'] = 'con_small_needle'
ws_new['T2'].font = Font(bold=True, color="BBBBBB")
ws_new['T2'] = 'con_arthroscopy_knee'
ws_new['U2'].font = Font(bold=True, color="BBBBBB")
ws_new['U2'] = 'con_incision'
ws_new['V2'].font = Font(bold=True, color="BBBBBB")
ws_new['V2'] = 'con_osteotomy'
ws_new['W2'].font = Font(bold=True, color="BBBBBB")
ws_new['W2'] = 'con_replacement'
ws_new['X2'].font = Font(bold=True, color="BBBBBB")
ws_new['X2'] = 'con_revision'
ws_new['Y2'].font = Font(bold=True, color="BBBBBB")
ws_new['Y2'] = 'con_other'
ws_new['Z2'].font = Font(bold=True, color="BBBBBB")
ws_new['Z2'] = 'limb_history'
ws_new['AA2'].font = Font(bold=True, color="BBBBBB")
ws_new['AA2'] = 'limb_time'
ws_new['AB2'].font = Font(bold=True, color="BBBBBB")
ws_new['AB2'] = 'fixation'
ws_new['AC2'].font = Font(bold=True, color="BBBBBB")
ws_new['AC2'] = 'limb_osteotomy'
ws_new['AD2'].font = Font(bold=True, color="BBBBBB")
ws_new['AD2'] = 'limb_microsurgery'
ws_new['AE2'].font = Font(bold=True, color="BBBBBB")
ws_new['AE2'] = 'limb_replacement'
ws_new['AF2'].font = Font(bold=True, color="BBBBBB")
ws_new['AF2'] = 'limb_revision'
ws_new['AG2'].font = Font(bold=True, color="BBBBBB")
ws_new['AG2'] = 'limb_fixation'
ws_new['AH2'].font = Font(bold=True, color="BBBBBB")
ws_new['AH2'] = 'limb_arthroscopic'
ws_new['AI2'].font = Font(bold=True, color="BBBBBB")
ws_new['AI2'] = 'limb_orthopedics'
ws_new['AJ2'].font = Font(bold=True, color="BBBBBB")
ws_new['AJ2'] = 'limb_other'
ws_new['AK2'].font = Font(bold=True, color="BBBBBB")
ws_new['AK2'] = 'lumbar_history'
ws_new['AL2'].font = Font(bold=True, color="BBBBBB")
ws_new['AL2'] = 'lumbar_time'
ws_new['AM2'].font = Font(bold=True, color="BBBBBB")
ws_new['AM2'] = 'other_body_history'
ws_new['AN2'].font = Font(bold=True, color="BBBBBB")
ws_new['AN2'] = 'other_body_name'
ws_new['AO2'].font = Font(bold=True, color="BBBBBB")
ws_new['AO2'] = 'other_body_remarks'
# 列名(中文)
ws_new['B3'].font = Font(color="BBBBBB")
ws_new['B3'] = '患者姓名'
ws_new['C3'].font = Font(color="BBBBBB")
ws_new['C3'] = '患者ID'
ws_new['D3'].font = Font(color="BBBBBB")
ws_new['D3'] = '患者年龄'
ws_new['E3'].font = Font(color="BBBBBB")
ws_new['E3'] = '患者性别'
ws_new['F3'].font = Font(color="BBBBBB")
ws_new['F3'] = '患侧膝关节手术史'
ws_new['G3'].font = Font(color="BBBBBB")
ws_new['G3'] = '患侧膝关节手术时间'
ws_new['H3'].font = Font(color="BBBBBB")
ws_new['H3'] = '患侧膝关节手术名称-膝关节穿刺术'
ws_new['I3'].font = Font(color="BBBBBB")
ws_new['I3'] = '患侧膝关节手术名称-膝关节小针刀术'
ws_new['J3'].font = Font(color="BBBBBB")
ws_new['J3'] = '患侧膝关节手术名称-膝关节镜术（滑膜切除、半月板切除、交叉韧带重建、关节炎清理、游离体取出）'
ws_new['K3'].font = Font(color="BBBBBB")
ws_new['K3'] = '患侧膝关节手术名称-膝关节切开手术（滑膜切除、半月板切除、游离体取出）'
ws_new['L3'].font = Font(color="BBBBBB")
ws_new['L3'] = '患侧膝关节手术名称-膝关节截骨术（HTO、PFO、骨折内固定术）'
ws_new['M3'].font = Font(color="BBBBBB")
ws_new['M3'] = '患侧膝关节手术名称-膝关节置换术 '
ws_new['N3'].font = Font(color="BBBBBB")
ws_new['N3'] = '患侧膝关节手术名称-膝关节翻修术'
ws_new['O3'].font = Font(color="BBBBBB")
ws_new['O3'] = '患侧膝关节手术名称-其他'
ws_new['P3'].font = Font(color="BBBBBB")
ws_new['P3'] = '对侧膝关节手术史'
ws_new['Q3'].font = Font(color="BBBBBB")
ws_new['Q3'] = '对侧膝关节手术时间'
ws_new['R3'].font = Font(color="BBBBBB")
ws_new['R3'] = '对侧膝关节手术名称-膝关节穿刺术'
ws_new['S3'].font = Font(color="BBBBBB")
ws_new['S3'] = '对侧膝关节手术名称-膝关节小针刀术'
ws_new['T3'].font = Font(color="BBBBBB")
ws_new['T3'] = '对侧膝关节手术名称-膝关节镜术（滑膜切除、半月板切除、交叉韧带重建、关节炎清理、游离体取出）'
ws_new['U3'].font = Font(color="BBBBBB")
ws_new['U3'] = '对侧膝关节手术名称-膝关节切开手术（滑膜切除、半月板切除、游离体取出）'
ws_new['V3'].font = Font(color="BBBBBB")
ws_new['V3'] = '对侧膝关节手术名称-膝关节截骨术（HTO、PFO、骨折内固定术）'
ws_new['W3'].font = Font(color="BBBBBB")
ws_new['W3'] = '对侧膝关节手术名称-膝关节置换术'
ws_new['X3'].font = Font(color="BBBBBB")
ws_new['X3'] = '对侧膝关节手术名称-膝关节翻修术'
ws_new['Y3'].font = Font(color="BBBBBB")
ws_new['Y3'] = '对侧膝关节手术名称-其他'
ws_new['Z3'].font = Font(color="BBBBBB")
ws_new['Z3'] = '患侧肢体手术史'
ws_new['AA3'].font = Font(color="BBBBBB")
ws_new['AA3'] = '患侧肢体手术时间'
ws_new['AB3'].font = Font(color="BBBBBB")
ws_new['AB3'] = '患侧肢体手术名称-髋关节骨折内固定手术'
ws_new['AC3'].font = Font(color="BBBBBB")
ws_new['AC3'] = '患侧肢体手术名称-髋臼周围截骨术'
ws_new['AD3'].font = Font(color="BBBBBB")
ws_new['AD3'] = '患侧肢体手术名称-髋关节镜手术'
ws_new['AE3'].font = Font(color="BBBBBB")
ws_new['AE3'] = '患侧肢体手术名称-髋关节置换术'
ws_new['AF3'].font = Font(color="BBBBBB")
ws_new['AF3'] = '患侧肢体手术名称-髋关节翻修术'
ws_new['AG3'].font = Font(color="BBBBBB")
ws_new['AG3'] = '患侧肢体手术名称-踝关节骨折内固定术 '
ws_new['AH3'].font = Font(color="BBBBBB")
ws_new['AH3'] = '患侧肢体手术名称-踝关节镜手术'
ws_new['AI3'].font = Font(color="BBBBBB")
ws_new['AI3'] = '患侧肢体手术名称-踝关节矫形术'
ws_new['AJ3'].font = Font(color="BBBBBB")
ws_new['AJ3'] = '患侧肢体手术名称-其他'
ws_new['AK3'].font = Font(color="BBBBBB")
ws_new['AK3'] = '腰椎手术史'
ws_new['AL3'].font = Font(color="BBBBBB")
ws_new['AL3'] = '腰椎手术时间'
ws_new['AM3'].font = Font(color="BBBBBB")
ws_new['AM3'] = '身体其他部位手术史'
ws_new['AN3'].font = Font(color="BBBBBB")
ws_new['AN3'] = '身体其他部位手术名称'
ws_new['AO3'].font = Font(color="BBBBBB")
ws_new['AO3'] = '身体其他部位手术-备注信息'
# 数据类型
ws_new['B4'].font = Font(color="BBBBBB")
ws_new['B4'] = '字符型(50)'
ws_new['C4'].font = Font(color="BBBBBB")
ws_new['C4'] = '字符型(50)'
ws_new['D4'].font = Font(color="BBBBBB")
ws_new['D4'] = '字符型(50)'
ws_new['E4'].font = Font(color="BBBBBB")
ws_new['E4'] = '字符型(50)'
ws_new['F4'].font = Font(color="BBBBBB")
ws_new['F4'] = '字符型(50)'
ws_new['G4'].font = Font(color="BBBBBB")
ws_new['G4'] = '字符型(50)'
ws_new['H4'].font = Font(color="BBBBBB")
ws_new['H4'] = '字符型(50)'
ws_new['I4'].font = Font(color="BBBBBB")
ws_new['I4'] = '字符型(50)'
ws_new['J4'].font = Font(color="BBBBBB")
ws_new['J4'] = '字符型(50)'
ws_new['K4'].font = Font(color="BBBBBB")
ws_new['K4'] = '字符型(50)'
ws_new['L4'].font = Font(color="BBBBBB")
ws_new['L4'] = '字符型(50)'
ws_new['M4'].font = Font(color="BBBBBB")
ws_new['M4'] = '字符型(50)'
ws_new['N4'].font = Font(color="BBBBBB")
ws_new['N4'] = '字符型(50)'
ws_new['O4'].font = Font(color="BBBBBB")
ws_new['O4'] = '字符型(50)'
ws_new['P4'].font = Font(color="BBBBBB")
ws_new['P4'] = '字符型(50)'
ws_new['Q4'].font = Font(color="BBBBBB")
ws_new['Q4'] = '字符型(50)'
ws_new['R4'].font = Font(color="BBBBBB")
ws_new['R4'] = '字符型(50)'
ws_new['S4'].font = Font(color="BBBBBB")
ws_new['S4'] = '字符型(50)'
ws_new['T4'].font = Font(color="BBBBBB")
ws_new['T4'] = '字符型(50)'
ws_new['U4'].font = Font(color="BBBBBB")
ws_new['U4'] = '字符型(50)'
ws_new['V4'].font = Font(color="BBBBBB")
ws_new['V4'] = '字符型(50)'
ws_new['W4'].font = Font(color="BBBBBB")
ws_new['W4'] = '字符型(50)'
ws_new['X4'].font = Font(color="BBBBBB")
ws_new['X4'] = '字符型(50)'
ws_new['Y4'].font = Font(color="BBBBBB")
ws_new['Y4'] = '字符型(50)'
ws_new['Z4'].font = Font(color="BBBBBB")
ws_new['Z4'] = '字符型(50)'
ws_new['AA4'].font = Font(color="BBBBBB")
ws_new['AA4'] = '字符型(50)'
ws_new['AB4'].font = Font(color="BBBBBB")
ws_new['AB4'] = '字符型(50)'
ws_new['AC4'].font = Font(color="BBBBBB")
ws_new['AC4'] = '字符型(50)'
ws_new['AD4'].font = Font(color="BBBBBB")
ws_new['AD4'] = '字符型(50)'
ws_new['AE4'].font = Font(color="BBBBBB")
ws_new['AE4'] = '字符型(50)'
ws_new['AF4'].font = Font(color="BBBBBB")
ws_new['AF4'] = '字符型(50)'
ws_new['AG4'].font = Font(color="BBBBBB")
ws_new['AG4'] = '字符型(50)'
ws_new['AH4'].font = Font(color="BBBBBB")
ws_new['AH4'] = '字符型(50)'
ws_new['AI4'].font = Font(color="BBBBBB")
ws_new['AI4'] = '字符型(50)'
ws_new['AJ4'].font = Font(color="BBBBBB")
ws_new['AJ4'] = '字符型(50)'
ws_new['AK4'].font = Font(color="BBBBBB")
ws_new['AK4'] = '字符型(50)'
ws_new['AL4'].font = Font(color="BBBBBB")
ws_new['AL4'] = '字符型(50)'
ws_new['AM4'].font = Font(color="BBBBBB")
ws_new['AM4'] = '字符型(50)'
ws_new['AN4'].font = Font(color="BBBBBB")
ws_new['AN4'] = '字符型(50)'
ws_new['AO4'].font = Font(color="BBBBBB")
ws_new['AO4'] = '字符型(200)'
# 手术一般信息
if title == '手术一般信息':
ws_new['E1'] = XENCRYPT('echart_data_surgery_info')
# 列名(英文)
ws_new['B2'].font = Font(bold=True, color="BBBBBB")
ws_new['B2'] = 'patient_name'
ws_new['C2'].font = Font(bold=True, color="BBBBBB")
ws_new['C2'] = 'patient_id'
ws_new['D2'].font = Font(bold=True, color="BBBBBB")
ws_new['D2'] = 'patient_age'
ws_new['E2'].font = Font(bold=True, color="BBBBBB")
ws_new['E2'] = 'patient_sex'
ws_new['F2'].font = Font(bold=True, color="BBBBBB")
ws_new['F2'] = 'surgical_site'
ws_new['G2'].font = Font(bold=True, color="BBBBBB")
ws_new['G2'] = 'surgical_time'
ws_new['H2'].font = Font(bold=True, color="BBBBBB")
ws_new['H2'] = 'surgical_hospital'
ws_new['I2'].font = Font(bold=True, color="BBBBBB")
ws_new['I2'] = 'chief_surgeon'
ws_new['J2'].font = Font(bold=True, color="BBBBBB")
ws_new['J2'] = 'relaxation_knee'
ws_new['K2'].font = Font(bold=True, color="BBBBBB")
ws_new['K2'] = 'over_extension'
ws_new['L2'].font = Font(bold=True, color="BBBBBB")
ws_new['L2'] = 'contracture_degree'
ws_new['M2'].font = Font(bold=True, color="BBBBBB")
ws_new['M2'] = 'access_way'
ws_new['N2'].font = Font(bold=True, color="BBBBBB")
ws_new['N2'] = 'loose_sleeve'
ws_new['O2'].font = Font(bold=True, color="BBBBBB")
ws_new['O2'] = 'joint_manufacturer'
ws_new['P2'].font = Font(bold=True, color="BBBBBB")
ws_new['P2'] = 'before_systolic_pressure'
ws_new['Q2'].font = Font(bold=True, color="BBBBBB")
ws_new['Q2'] = 'before_diastolic_pressure'
ws_new['R2'].font = Font(bold=True, color="BBBBBB")
ws_new['R2'] = 'after_systolic_pressure'
ws_new['S2'].font = Font(bold=True, color="BBBBBB")
ws_new['S2'] = 'after_diastolic_pressure'
ws_new['T2'].font = Font(bold=True, color="BBBBBB")
ws_new['T2'] = 'heart_rate'
ws_new['U2'].font = Font(bold=True, color="BBBBBB")
ws_new['U2'] = 'blood_oxygen'
ws_new['V2'].font = Font(bold=True, color="BBBBBB")
ws_new['V2'] = 'date_operation'
ws_new['W2'].font = Font(bold=True, color="BBBBBB")
ws_new['W2'] = 'pre_systolic_pressure'
ws_new['X2'].font = Font(bold=True, color="BBBBBB")
ws_new['X2'] = 'pre_diastolic_pressure'
ws_new['Y2'].font = Font(bold=True, color="BBBBBB")
ws_new['Y2'] = 'operation_duration'
ws_new['Z2'].font = Font(bold=True, color="BBBBBB")
ws_new['Z2'] = 'bleeding_volume'
ws_new['AA2'].font = Font(bold=True, color="BBBBBB")
ws_new['AA2'] = 'blood_transfusion'
ws_new['AB2'].font = Font(bold=True, color="BBBBBB")
ws_new['AB2'] = 'breathing'
# 列名(中文)
ws_new['B3'].font = Font(color="BBBBBB")
ws_new['B3'] = '患者姓名'
ws_new['C3'].font = Font(color="BBBBBB")
ws_new['C3'] = '患者ID'
ws_new['D3'].font = Font(color="BBBBBB")
ws_new['D3'] = '患者年龄'
ws_new['E3'].font = Font(color="BBBBBB")
ws_new['E3'] = '患者性别'
ws_new['F3'].font = Font(color="BBBBBB")
ws_new['F3'] = '手术部位'
ws_new['G3'].font = Font(color="BBBBBB")
ws_new['G3'] = '手术时间'
ws_new['H3'].font = Font(color="BBBBBB")
ws_new['H3'] = '手术医院'
ws_new['I3'].font = Font(color="BBBBBB")
ws_new['I3'] = '主刀医生'
ws_new['J3'].font = Font(color="BBBBBB")
ws_new['J3'] = '麻醉后膝关节松弛度'
ws_new['K3'].font = Font(color="BBBBBB")
ws_new['K3'] = '过伸度数'
ws_new['L3'].font = Font(color="BBBBBB")
ws_new['L3'] = '屈曲挛缩度数'
ws_new['M3'].font = Font(color="BBBBBB")
ws_new['M3'] = '入路方式'
ws_new['N3'].font = Font(color="BBBBBB")
ws_new['N3'] = '袖套松解'
ws_new['O3'].font = Font(color="BBBBBB")
ws_new['O3'] = '关节厂商'
ws_new['P3'].font = Font(color="BBBBBB")
ws_new['P3'] = '麻醉前-收缩压（mmHg）'
ws_new['Q3'].font = Font(color="BBBBBB")
ws_new['Q3'] = '麻醉前-舒张压（mmHg）'
ws_new['R3'].font = Font(color="BBBBBB")
ws_new['R3'] = '麻醉后-收缩压（mmHg）'
ws_new['S3'].font = Font(color="BBBBBB")
ws_new['S3'] = '麻醉后-舒张压（mmHg）'
ws_new['T3'].font = Font(color="BBBBBB")
ws_new['T3'] = '心率（次/分）'
ws_new['U3'].font = Font(color="BBBBBB")
ws_new['U3'] = '血氧（%）'
ws_new['V3'].font = Font(color="BBBBBB")
ws_new['V3'] = '手术日期'
ws_new['W3'].font = Font(color="BBBBBB")
ws_new['W3'] = '术前血压-收缩压（mmHg）'
ws_new['X3'].font = Font(color="BBBBBB")
ws_new['X3'] = '术前血压-舒张压（mmHg）'
ws_new['Y3'].font = Font(color="BBBBBB")
ws_new['Y3'] = '手术时长（min）'
ws_new['Z3'].font = Font(color="BBBBBB")
ws_new['Z3'] = '出血量（ml）'
ws_new['AA3'].font = Font(color="BBBBBB")
ws_new['AA3'] = '输血情况'
ws_new['AB3'].font = Font(color="BBBBBB")
ws_new['AB3'] = '呼吸（次/分）'
# 数据类型
ws_new['B4'].font = Font(color="BBBBBB")
ws_new['B4'] = '字符型(50)'
ws_new['C4'].font = Font(color="BBBBBB")
ws_new['C4'] = '字符型(50)'
ws_new['D4'].font = Font(color="BBBBBB")
ws_new['D4'] = '字符型(50)'
ws_new['E4'].font = Font(color="BBBBBB")
ws_new['E4'] = '字符型(50)'
ws_new['F4'].font = Font(color="BBBBBB")
ws_new['F4'] = '字符型(50)'
ws_new['G4'].font = Font(color="BBBBBB")
ws_new['G4'] = '字符型(50)'
ws_new['H4'].font = Font(color="BBBBBB")
ws_new['H4'] = '字符型(50)'
ws_new['I4'].font = Font(color="BBBBBB")
ws_new['I4'] = '字符型(50)'
ws_new['J4'].font = Font(color="BBBBBB")
ws_new['J4'] = '字符型(50)'
ws_new['K4'].font = Font(color="BBBBBB")
ws_new['K4'] = '字符型(50)'
ws_new['L4'].font = Font(color="BBBBBB")
ws_new['L4'] = '字符型(50)'
ws_new['M4'].font = Font(color="BBBBBB")
ws_new['M4'] = '字符型(50)'
ws_new['N4'].font = Font(color="BBBBBB")
ws_new['N4'] = '字符型(50)'
ws_new['O4'].font = Font(color="BBBBBB")
ws_new['O4'] = '字符型(50)'
ws_new['P4'].font = Font(color="BBBBBB")
ws_new['P4'] = '字符型(50)'
ws_new['Q4'].font = Font(color="BBBBBB")
ws_new['Q4'] = '字符型(50)'
ws_new['R4'].font = Font(color="BBBBBB")
ws_new['R4'] = '字符型(50)'
ws_new['S4'].font = Font(color="BBBBBB")
ws_new['S4'] = '字符型(50)'
ws_new['T4'].font = Font(color="BBBBBB")
ws_new['T4'] = '字符型(50)'
ws_new['U4'].font = Font(color="BBBBBB")
ws_new['U4'] = '字符型(50)'
ws_new['V4'].font = Font(color="BBBBBB")
ws_new['V4'] = '字符型(50)'
ws_new['W4'].font = Font(color="BBBBBB")
ws_new['W4'] = '字符型(50)'
ws_new['X4'].font = Font(color="BBBBBB")
ws_new['X4'] = '字符型(50)'
ws_new['Y4'].font = Font(color="BBBBBB")
ws_new['Y4'] = '字符型(50)'
ws_new['Z4'].font = Font(color="BBBBBB")
ws_new['Z4'] = '字符型(50)'
ws_new['AA4'].font = Font(color="BBBBBB")
ws_new['AA4'] = '字符型(50)'
ws_new['AB4'].font = Font(color="BBBBBB")
ws_new['AB4'] = '字符型(50)'
# 股骨情况
if title == '股骨情况':
ws_new['E1'] = XENCRYPT('echart_data_femur_condition')
# 列名(英文)
ws_new['B2'].font = Font(bold=True, color="BBBBBB")
ws_new['B2'] = 'patient_name'
ws_new['C2'].font = Font(bold=True, color="BBBBBB")
ws_new['C2'] = 'patient_id'
ws_new['D2'].font = Font(bold=True, color="BBBBBB")
ws_new['D2'] = 'patient_age'
ws_new['E2'].font = Font(bold=True, color="BBBBBB")
ws_new['E2'] = 'patient_sex'
ws_new['F2'].font = Font(bold=True, color="BBBBBB")
ws_new['F2'] = 'medullary_position'
ws_new['G2'].font = Font(bold=True, color="BBBBBB")
ws_new['G2'] = 'eversion_degree'
ws_new['H2'].font = Font(bold=True, color="BBBBBB")
ws_new['H2'] = 'osteotomy_thickness'
ws_new['I2'].font = Font(bold=True, color="BBBBBB")
ws_new['I2'] = 'add_osteotomy'
ws_new['J2'].font = Font(bold=True, color="BBBBBB")
ws_new['J2'] = 'reduced_osteotomy'
ws_new['K2'].font = Font(bold=True, color="BBBBBB")
ws_new['K2'] = 'distal_osteotomy'
ws_new['L2'].font = Font(bold=True, color="BBBBBB")
ws_new['L2'] = 'model_reference'
ws_new['M2'].font = Font(bold=True, color="BBBBBB")
ws_new['M2'] = 'prosthesis_model'
ws_new['N2'].font = Font(bold=True, color="BBBBBB")
ws_new['N2'] = 'actual_femoral'
ws_new['O2'].font = Font(bold=True, color="BBBBBB")
ws_new['O2'] = 'special_femoral'
ws_new['P2'].font = Font(bold=True, color="BBBBBB")
ws_new['P2'] = 'extension_rod'
ws_new['Q2'].font = Font(bold=True, color="BBBBBB")
ws_new['Q2'] = 'forward_backward'
ws_new['R2'].font = Font(bold=True, color="BBBBBB")
ws_new['R2'] = 'forward_distance'
ws_new['S2'].font = Font(bold=True, color="BBBBBB")
ws_new['S2'] = 'backward_distance'
ws_new['T2'].font = Font(bold=True, color="BBBBBB")
ws_new['T2'] = 'remarks'
ws_new['U2'].font = Font(bold=True, color="BBBBBB")
ws_new['U2'] = 'notching'
ws_new['V2'].font = Font(bold=True, color="BBBBBB")
ws_new['V2'] = 'have_notching'
ws_new['W2'].font = Font(bold=True, color="BBBBBB")
ws_new['W2'] = 'external_degree'
ws_new['X2'].font = Font(bold=True, color="BBBBBB")
ws_new['X2'] = 'whiteside'
ws_new['Y2'].font = Font(bold=True, color="BBBBBB")
ws_new['Y2'] = 'posterior_condyle_line'
ws_new['Z2'].font = Font(bold=True, color="BBBBBB")
ws_new['Z2'] = 'condylar_axis'
ws_new['AA2'].font = Font(bold=True, color="BBBBBB")
ws_new['AA2'] = 'special_remarks'
# 列名(中文)
ws_new['B3'].font = Font(color="BBBBBB")
ws_new['B3'] = '患者姓名'
ws_new['C3'].font = Font(color="BBBBBB")
ws_new['C3'] = '患者ID'
ws_new['D3'].font = Font(color="BBBBBB")
ws_new['D3'] = '患者年龄'
ws_new['E3'].font = Font(color="BBBBBB")
ws_new['E3'] = '患者性别'
ws_new['F3'].font = Font(color="BBBBBB")
ws_new['F3'] = 'PCL'
ws_new['G3'].font = Font(color="BBBBBB")
ws_new['G3'] = '外翻度数'
ws_new['H3'].font = Font(color="BBBBBB")
ws_new['H3'] = '远端截骨厚度'
ws_new['I3'].font = Font(color="BBBBBB")
ws_new['I3'] = '增加截骨（mm）'
ws_new['J3'].font = Font(color="BBBBBB")
ws_new['J3'] = '减少截骨（mm）'
ws_new['K3'].font = Font(color="BBBBBB")
ws_new['K3'] = '远端截骨情况'
ws_new['L3'].font = Font(color="BBBBBB")
ws_new['L3'] = '髁型号参考'
ws_new['M3'].font = Font(color="BBBBBB")
ws_new['M3'] = '首次测量髁假体型号'
ws_new['N3'].font = Font(color="BBBBBB")
ws_new['N3'] = '实际使用股骨髁型号'
ws_new['O3'].font = Font(color="BBBBBB")
ws_new['O3'] = '特殊股骨髁'
ws_new['P3'].font = Font(color="BBBBBB")
ws_new['P3'] = '髁延长杆'
ws_new['Q3'].font = Font(color="BBBBBB")
ws_new['Q3'] = '股骨髁型号前移后移'
ws_new['R3'].font = Font(color="BBBBBB")
ws_new['R3'] = '前移距离（mm）'
ws_new['S3'].font = Font(color="BBBBBB")
ws_new['S3'] = '后移距离（mm）'
ws_new['T3'].font = Font(color="BBBBBB")
ws_new['T3'] = '前移、后移-备注信息'
ws_new['U3'].font = Font(color="BBBBBB")
ws_new['U3'] = '有无notching'
ws_new['V3'].font = Font(color="BBBBBB")
ws_new['V3'] = '有notching'
ws_new['W3'].font = Font(color="BBBBBB")
ws_new['W3'] = '外旋度数（°）'
ws_new['X3'].font = Font(color="BBBBBB")
ws_new['X3'] = '外旋参考线-whiteside线'
ws_new['Y3'].font = Font(color="BBBBBB")
ws_new['Y3'] = '外旋参考线-后髁连线'
ws_new['Z3'].font = Font(color="BBBBBB")
ws_new['Z3'] = '外旋参考线-通髁轴'
ws_new['AA3'].font = Font(color="BBBBBB")
ws_new['AA3'] = '股骨特殊情况备注'
# 数据类型
ws_new['B4'].font = Font(color="BBBBBB")
ws_new['B4'] = '字符型(50)'
ws_new['C4'].font = Font(color="BBBBBB")
ws_new['C4'] = '字符型(50)'
ws_new['D4'].font = Font(color="BBBBBB")
ws_new['D4'] = '字符型(50)'
ws_new['E4'].font = Font(color="BBBBBB")
ws_new['E4'] = '字符型(50)'
ws_new['F4'].font = Font(color="BBBBBB")
ws_new['F4'] = '字符型(50)'
ws_new['G4'].font = Font(color="BBBBBB")
ws_new['G4'] = '字符型(50)'
ws_new['H4'].font = Font(color="BBBBBB")
ws_new['H4'] = '字符型(50)'
ws_new['I4'].font = Font(color="BBBBBB")
ws_new['I4'] = '字符型(50)'
ws_new['J4'].font = Font(color="BBBBBB")
ws_new['J4'] = '字符型(50)'
ws_new['K4'].font = Font(color="BBBBBB")
ws_new['K4'] = '字符型(50)'
ws_new['L4'].font = Font(color="BBBBBB")
ws_new['L4'] = '字符型(50)'
ws_new['M4'].font = Font(color="BBBBBB")
ws_new['M4'] = '字符型(50)'
ws_new['N4'].font = Font(color="BBBBBB")
ws_new['N4'] = '字符型(50)'
ws_new['O4'].font = Font(color="BBBBBB")
ws_new['O4'] = '字符型(50)'
ws_new['P4'].font = Font(color="BBBBBB")
ws_new['P4'] = '字符型(50)'
ws_new['Q4'].font = Font(color="BBBBBB")
ws_new['Q4'] = '字符型(50)'
ws_new['R4'].font = Font(color="BBBBBB")
ws_new['R4'] = '字符型(50)'
ws_new['S4'].font = Font(color="BBBBBB")
ws_new['S4'] = '字符型(50)'
ws_new['T4'].font = Font(color="BBBBBB")
ws_new['T4'] = '字符型(150)'
ws_new['U4'].font = Font(color="BBBBBB")
ws_new['U4'] = '字符型(50)'
ws_new['V4'].font = Font(color="BBBBBB")
ws_new['V4'] = '字符型(50)'
ws_new['W4'].font = Font(color="BBBBBB")
ws_new['W4'] = '字符型(50)'
ws_new['X4'].font = Font(color="BBBBBB")
ws_new['X4'] = '字符型(50)'
ws_new['Y4'].font = Font(color="BBBBBB")
ws_new['Y4'] = '字符型(50)'
ws_new['Z4'].font = Font(color="BBBBBB")
ws_new['Z4'] = '字符型(50)'
ws_new['AA4'].font = Font(color="BBBBBB")
ws_new['AA4'] = '字符型(150)'
# 软组织平衡情况
if title == '软组织平衡情况':
ws_new['E1'] = XENCRYPT('echart_data_soft_tissue_balance')
# 列名(英文)
ws_new['B2'].font = Font(bold=True, color="BBBBBB")
ws_new['B2'] = 'patient_name'
ws_new['C2'].font = Font(bold=True, color="BBBBBB")
ws_new['C2'] = 'patient_id'
ws_new['D2'].font = Font(bold=True, color="BBBBBB")
ws_new['D2'] = 'patient_age'
ws_new['E2'].font = Font(bold=True, color="BBBBBB")
ws_new['E2'] = 'patient_sex'
ws_new['F2'].font = Font(bold=True, color="BBBBBB")
ws_new['F2'] = 'pcl'
ws_new['G2'].font = Font(bold=True, color="BBBBBB")
ws_new['G2'] = 'cr_pcl'
ws_new['H2'].font = Font(bold=True, color="BBBBBB")
ws_new['H2'] = 'pcl_osteotomy'
ws_new['I2'].font = Font(bold=True, color="BBBBBB")
ws_new['I2'] = 'cr_quality_absent'
ws_new['J2'].font = Font(bold=True, color="BBBBBB")
ws_new['J2'] = 'cr_good'
ws_new['K2'].font = Font(bold=True, color="BBBBBB")
ws_new['K2'] = 'femoral_condyle'
ws_new['L2'].font = Font(bold=True, color="BBBBBB")
ws_new['L2'] = 'platform_osteotomy'
ws_new['M2'].font = Font(bold=True, color="BBBBBB")
ws_new['M2'] = 'point_tear'
ws_new['N2'].font = Font(bold=True, color="BBBBBB")
ws_new['N2'] = 'osteoporotic_avulsion'
ws_new['O2'].font = Font(bold=True, color="BBBBBB")
ws_new['O2'] = 'bone_preservation'
ws_new['P2'].font = Font(bold=True, color="BBBBBB")
ws_new['P2'] = 'other'
ws_new['Q2'].font = Font(bold=True, color="BBBBBB")
ws_new['Q2'] = 'dislocation_subluxation'
ws_new['R2'].font = Font(bold=True, color="BBBBBB")
ws_new['R2'] = 'mcl'
ws_new['S2'].font = Font(bold=True, color="BBBBBB")
ws_new['S2'] = 'loose_sleeves'
ws_new['T2'].font = Font(bold=True, color="BBBBBB")
ws_new['T2'] = 'excrescence'
ws_new['U2'].font = Font(bold=True, color="BBBBBB")
ws_new['U2'] = 'meniscectomy'
ws_new['V2'].font = Font(bold=True, color="BBBBBB")
ws_new['V2'] = 'medial_osteotomy'
ws_new['W2'].font = Font(bold=True, color="BBBBBB")
ws_new['W2'] = 'mcl_other'
ws_new['X2'].font = Font(bold=True, color="BBBBBB")
ws_new['X2'] = 'repair'
ws_new['Y2'].font = Font(bold=True, color="BBBBBB")
ws_new['Y2'] = 'popliteal_tendon'
ws_new['Z2'].font = Font(bold=True, color="BBBBBB")
ws_new['Z2'] = 'popliteal_tendon_repair'
ws_new['AA2'].font = Font(bold=True, color="BBBBBB")
ws_new['AA2'] = 'itb_iliotibial_tract'
ws_new['aB2'].font = Font(bold=True, color="BBBBBB")
ws_new['aB2'] = 'before_stretch'
ws_new['aC2'].font = Font(bold=True, color="BBBBBB")
ws_new['aC2'] = 'after_stretch'
ws_new['aD2'].font = Font(bold=True, color="BBBBBB")
ws_new['aD2'] = 'condylar_removal'
ws_new['aE2'].font = Font(bold=True, color="BBBBBB")
ws_new['aE2'] = 'surface_preservation'
ws_new['aF2'].font = Font(bold=True, color="BBBBBB")
ws_new['aF2'] = 'crypt_cleaning'
ws_new['aG2'].font = Font(bold=True, color="BBBBBB")
ws_new['aG2'] = 'release_1'
ws_new['aH2'].font = Font(bold=True, color="BBBBBB")
ws_new['aH2'] = 'subperiosteal_lysis'
ws_new['aI2'].font = Font(bold=True, color="BBBBBB")
ws_new['aI2'] = 'excited_part'
ws_new['aJ2'].font = Font(bold=True, color="BBBBBB")
ws_new['aJ2'] = 'joint_stretch'
ws_new['aK2'].font = Font(bold=True, color="BBBBBB")
ws_new['aK2'] = 'release_excited_part'
ws_new['aL2'].font = Font(bold=True, color="BBBBBB")
ws_new['aL2'] = 'posterior_release'
ws_new['aM2'].font = Font(bold=True, color="BBBBBB")
ws_new['aM2'] = 'conventional_release'
ws_new['aN2'].font = Font(bold=True, color="BBBBBB")
ws_new['aN2'] = 'better_knee_joint'
ws_new['aO2'].font = Font(bold=True, color="BBBBBB")
ws_new['aO2'] = 'varus_deformity'
ws_new['aP2'].font = Font(bold=True, color="BBBBBB")
ws_new['aP2'] = 'cocktail_injection'
ws_new['aQ2'].font = Font(bold=True, color="BBBBBB")
ws_new['aQ2'] = 'injection_site'
ws_new['aR2'].font = Font(bold=True, color="BBBBBB")
ws_new['aR2'] = 'complication'
ws_new['aS2'].font = Font(bold=True, color="BBBBBB")
ws_new['aS2'] = 'handling'
ws_new['aT2'].font = Font(bold=True, color="BBBBBB")
ws_new['aT2'] = 'remarks_special'
ws_new['aU2'].font = Font(bold=True, color="BBBBBB")
ws_new['aU2'] = 'internal_posterior'
ws_new['aV2'].font = Font(bold=True, color="BBBBBB")
ws_new['aV2'] = 'internal_con_release'
ws_new['aW2'].font = Font(bold=True, color="BBBBBB")
ws_new['aW2'] = 'internal_better'
ws_new['aX2'].font = Font(bold=True, color="BBBBBB")
ws_new['aX2'] = 'internal_varus'
ws_new['AY2'].font = Font(bold=True, color="BBBBBB")
ws_new['AY2'] = 'cocktail_formula'
# 列名(中文)
ws_new['B3'].font = Font(bold=True, color="BBBBBB")
ws_new['B3'] = '患者姓名'
ws_new['C3'].font = Font(bold=True, color="BBBBBB")
ws_new['C3'] = '患者ID'
ws_new['D3'].font = Font(bold=True, color="BBBBBB")
ws_new['D3'] = '患者年龄'
ws_new['E3'].font = Font(bold=True, color="BBBBBB")
ws_new['E3'] = '患者性别'
ws_new['F3'].font = Font(bold=True, color="BBBBBB")
ws_new['F3'] = 'PCL'
ws_new['G3'].font = Font(bold=True, color="BBBBBB")
ws_new['G3'] = 'CR关节-PCL情况'
ws_new['H3'].font = Font(bold=True, color="BBBBBB")
ws_new['H3'] = 'PCL截骨损伤'
ws_new['I3'].font = Font(bold=True, color="BBBBBB")
ws_new['I3'] = 'CR关节-质量不佳或缺如'
ws_new['J3'].font = Font(bold=True, color="BBBBBB")
ws_new['J3'] = 'CR关节-良好'
ws_new['K3'].font = Font(bold=True, color="BBBBBB")
ws_new['K3'] = '截骨损伤类型-股骨后髁截骨损伤'
ws_new['L3'].font = Font(bold=True, color="BBBBBB")
ws_new['L3'] = '截骨损伤类型-平台截骨损伤'
ws_new['M3'].font = Font(bold=True, color="BBBBBB")
ws_new['M3'] = '截骨损伤类型-屈伸平衡不良，止点撕脱'
ws_new['N3'].font = Font(bold=True, color="BBBBBB")
ws_new['N3'] = '截骨损伤类型-骨质疏松止点撕脱'
ws_new['O3'].font = Font(bold=True, color="BBBBBB")
ws_new['O3'] = '截骨损伤类型-保留骨岛操作损伤'
ws_new['P3'].font = Font(bold=True, color="BBBBBB")
ws_new['P3'] = '截骨损伤类型-其他'
ws_new['Q3'].font = Font(bold=True, color="BBBBBB")
ws_new['Q3'] = 'CR关节PCL情况-屈曲垫片脱位/半脱位'
ws_new['R3'].font = Font(bold=True, color="BBBBBB")
ws_new['R3'] = 'MCL'
ws_new['S3'].font = Font(bold=True, color="BBBBBB")
ws_new['S3'] = 'MCL损伤类型-袖套松解'
ws_new['T3'].font = Font(bold=True, color="BBBBBB")
ws_new['T3'] = 'MCL损伤类型-股骨髁内侧骨赘切除'
ws_new['U3'].font = Font(bold=True, color="BBBBBB")
ws_new['U3'] = 'MCL损伤类型-半月板切除'
ws_new['V3'].font = Font(bold=True, color="BBBBBB")
ws_new['V3'] = 'MCL损伤类型-胫骨内侧截骨'
ws_new['W3'].font = Font(bold=True, color="BBBBBB")
ws_new['W3'] = 'MCL损伤类型-其他'
ws_new['X3'].font = Font(bold=True, color="BBBBBB")
ws_new['X3'] = 'MCL损伤修复情况'
ws_new['Y3'].font = Font(bold=True, color="BBBBBB")
ws_new['Y3'] = '腘肌腱'
ws_new['Z3'].font = Font(bold=True, color="BBBBBB")
ws_new['Z3'] = '腘肌腱修补情况'
ws_new['AA3'].font = Font(bold=True, color="BBBBBB")
ws_new['AA3'] = '髂胫束ITB'
ws_new['AB3'].font = Font(bold=True, color="BBBBBB")
ws_new['AB3'] = '截骨前拉花松解'
ws_new['AC3'].font = Font(bold=True, color="BBBBBB")
ws_new['AC3'] = '截骨后拉花松解'
ws_new['AD3'].font = Font(bold=True, color="BBBBBB")
ws_new['AD3'] = '后关节囊-内外后髁骨去除'
ws_new['AE3'].font = Font(bold=True, color="BBBBBB")
ws_new['AE3'] = '后关节囊-后髁骨面保留（cm）'
ws_new['AF3'].font = Font(bold=True, color="BBBBBB")
ws_new['AF3'] = '后关节囊-内外后隐窝清理'
ws_new['AG3'].font = Font(bold=True, color="BBBBBB")
ws_new['AG3'] = '后关节囊-松解'
ws_new['AH3'].font = Font(bold=True, color="BBBBBB")
ws_new['AH3'] = '屈曲位松解-骨膜下松解'
ws_new['AI3'].font = Font(bold=True, color="BBBBBB")
ws_new['AI3'] = '屈曲位松解-切除部分关节囊'
ws_new['AJ3'].font = Font(bold=True, color="BBBBBB")
ws_new['AJ3'] = '伸直位松解-关节囊拉花松解'
ws_new['AK3'].font = Font(bold=True, color="BBBBBB")
ws_new['AK3'] = '伸直位松解-切除部分关节囊'
ws_new['AL3'].font = Font(bold=True, color="BBBBBB")
ws_new['AL3'] = '后关节囊-胫骨平台内后角松解'
ws_new['AM3'].font = Font(bold=True, color="BBBBBB")
ws_new['AM3'] = '胫骨平台内后角松解-常规松解'
ws_new['AN3'].font = Font(bold=True, color="BBBBBB")
ws_new['AN3'] = '胫骨平台内后角松解-更好的伸直膝关节 '
ws_new['AO3'].font = Font(bold=True, color="BBBBBB")
ws_new['AO3'] = '胫骨平台内后角松解-矫正伸直位内翻畸形'
ws_new['AP3'].font = Font(bold=True, color="BBBBBB")
ws_new['AP3'] = '鸡尾酒注射'
ws_new['AQ3'].font = Font(bold=True, color="BBBBBB")
ws_new['AQ3'] = '鸡尾酒注射部位'
ws_new['AR3'].font = Font(bold=True, color="BBBBBB")
ws_new['AR3'] = '鸡尾酒注射并发症'
ws_new['AS3'].font = Font(bold=True, color="BBBBBB")
ws_new['AS3'] = '鸡尾酒注射并发症处理情况'
ws_new['AT3'].font = Font(bold=True, color="BBBBBB")
ws_new['AT3'] = '鸡尾酒注射并发症特殊情况备注'
ws_new['AU3'].font = Font(bold=True, color="BBBBBB")
ws_new['AU3'] = '后关节囊-内后角松解'
ws_new['AV3'].font = Font(bold=True, color="BBBBBB")
ws_new['AV3'] = '内后角松解-常规松解'
ws_new['AW3'].font = Font(bold=True, color="BBBBBB")
ws_new['AW3'] = '内后角松解-更好的伸直膝关节'
ws_new['AX3'].font = Font(bold=True, color="BBBBBB")
ws_new['AX3'] = '内后角松解-矫正伸直位内翻畸形'
ws_new['AY3'].font = Font(bold=True, color="BBBBBB")
ws_new['AY3'] = '鸡尾酒配方'
# 数据类型
ws_new['B4'].font = Font(color="BBBBBB")
ws_new['B4'] = '字符型(20)'
ws_new['C4'].font = Font(color="BBBBBB")
ws_new['C4'] = '字符型(20)'
ws_new['D4'].font = Font(color="BBBBBB")
ws_new['D4'] = '字符型(10)'
ws_new['E4'].font = Font(color="BBBBBB")
ws_new['E4'] = '字符型(6)'
ws_new['F4'].font = Font(color="BBBBBB")
ws_new['F4'] = '字符型(20)'
ws_new['G4'].font = Font(color="BBBBBB")
ws_new['G4'] = '字符型(20)'
ws_new['H4'].font = Font(color="BBBBBB")
ws_new['H4'] = '字符型(10)'
ws_new['I4'].font = Font(color="BBBBBB")
ws_new['I4'] = '字符型(20)'
ws_new['J4'].font = Font(color="BBBBBB")
ws_new['J4'] = '字符型(20)'
ws_new['K4'].font = Font(color="BBBBBB")
ws_new['K4'] = '字符型(20)'
ws_new['L4'].font = Font(color="BBBBBB")
ws_new['L4'] = '字符型(10)'
ws_new['M4'].font = Font(color="BBBBBB")
ws_new['M4'] = '字符型(10)'
ws_new['N4'].font = Font(color="BBBBBB")
ws_new['N4'] = '字符型(10)'
ws_new['O4'].font = Font(color="BBBBBB")
ws_new['O4'] = '字符型(10)'
ws_new['P4'].font = Font(color="BBBBBB")
ws_new['P4'] = '字符型(10)'
ws_new['Q4'].font = Font(color="BBBBBB")
ws_new['Q4'] = '字符型(10)'
ws_new['R4'].font = Font(color="BBBBBB")
ws_new['R4'] = '字符型(20)'
ws_new['S4'].font = Font(color="BBBBBB")
ws_new['S4'] = '字符型(20)'
ws_new['T4'].font = Font(color="BBBBBB")
ws_new['T4'] = '字符型(20)'
ws_new['U4'].font = Font(color="BBBBBB")
ws_new['U4'] = '字符型(20)'
ws_new['V4'].font = Font(color="BBBBBB")
ws_new['V4'] = '字符型(20)'
ws_new['W4'].font = Font(color="BBBBBB")
ws_new['W4'] = '字符型(20)'
ws_new['X4'].font = Font(color="BBBBBB")
ws_new['X4'] = '字符型(20)'
ws_new['Y4'].font = Font(color="BBBBBB")
ws_new['Y4'] = '字符型(20)'
ws_new['Z4'].font = Font(color="BBBBBB")
ws_new['Z4'] = '字符型(20)'
ws_new['AA4'].font = Font(color="BBBBBB")
ws_new['AA4'] = '字符型(20)'
ws_new['AB4'].font = Font(color="BBBBBB")
ws_new['AB4'] = '字符型(20)'
ws_new['AC4'].font = Font(color="BBBBBB")
ws_new['AC4'] = '字符型(20)'
ws_new['AD4'].font = Font(color="BBBBBB")
ws_new['AD4'] = '字符型(20)'
ws_new['AE4'].font = Font(color="BBBBBB")
ws_new['AE4'] = '字符型(20)'
ws_new['AF4'].font = Font(color="BBBBBB")
ws_new['AF4'] = '字符型(10)'
ws_new['AG4'].font = Font(color="BBBBBB")
ws_new['AG4'] = '字符型(20)'
ws_new['AH4'].font = Font(color="BBBBBB")
ws_new['AH4'] = '字符型(20)'
ws_new['AI4'].font = Font(color="BBBBBB")
ws_new['AI4'] = '字符型(4)'
ws_new['AJ4'].font = Font(color="BBBBBB")
ws_new['AJ4'] = '字符型(4)'
ws_new['AK4'].font = Font(color="BBBBBB")
ws_new['AK4'] = '字符型(4)'
ws_new['AL4'].font = Font(color="BBBBBB")
ws_new['AL4'] = '字符型(10)'
ws_new['AM4'].font = Font(color="BBBBBB")
ws_new['AM4'] = '字符型(4)'
ws_new['AN4'].font = Font(color="BBBBBB")
ws_new['AN4'] = '字符型(4)'
ws_new['AO4'].font = Font(color="BBBBBB")
ws_new['AO4'] = '字符型(4)'
ws_new['AP4'].font = Font(color="BBBBBB")
ws_new['AP4'] = '字符型(100)'
ws_new['AQ4'].font = Font(color="BBBBBB")
ws_new['AQ4'] = '字符型(20)'
ws_new['AR4'].font = Font(color="BBBBBB")
ws_new['AR4'] = '字符型(10)'
ws_new['AS4'].font = Font(color="BBBBBB")
ws_new['AS4'] = '字符型(10)'
ws_new['AT4'].font = Font(color="BBBBBB")
ws_new['AT4'] = '字符型(100)'
ws_new['AU4'].font = Font(color="BBBBBB")
ws_new['AU4'] = '字符型(10)'
ws_new['AV4'].font = Font(color="BBBBBB")
ws_new['AV4'] = '字符型(4)'
ws_new['AW4'].font = Font(color="BBBBBB")
ws_new['AW4'] = '字符型(4)'
ws_new['AX4'].font = Font(color="BBBBBB")
ws_new['AX4'] = '字符型(4)'
ws_new['AY4'].font = Font(color="BBBBBB")
ws_new['AY4'] = '字符型(100)'
# 胫骨平台截骨情况
if title == '胫骨平台截骨情况':
ws_new['E1'] = XENCRYPT('echart_data_tibial_plateau')
# 列名(英文)
ws_new['B2'].font = Font(bold=True, color="BBBBBB")
ws_new['B2'] = 'patient_name'
ws_new['C2'].font = Font(bold=True, color="BBBBBB")
ws_new['C2'] = 'patient_id'
ws_new['D2'].font = Font(bold=True, color="BBBBBB")
ws_new['D2'] = 'patient_age'
ws_new['E2'].font = Font(bold=True, color="BBBBBB")
ws_new['E2'] = 'patient_sex'
ws_new['F2'].font = Font(bold=True, color="BBBBBB")
ws_new['F2'] = 'platform_model'
ws_new['G2'].font = Font(bold=True, color="BBBBBB")
ws_new['G2'] = 'platform_extension'
ws_new['H2'].font = Font(bold=True, color="BBBBBB")
ws_new['H2'] = 'thickness'
ws_new['I2'].font = Font(bold=True, color="BBBBBB")
ws_new['I2'] = 'type'
ws_new['J2'].font = Font(bold=True, color="BBBBBB")
ws_new['J2'] = 'joint_straightness'
ws_new['K2'].font = Font(bold=True, color="BBBBBB")
ws_new['K2'] = 'buckling_stability'
ws_new['L2'].font = Font(bold=True, color="BBBBBB")
ws_new['L2'] = 'relaxation_degree'
ws_new['M2'].font = Font(bold=True, color="BBBBBB")
ws_new['M2'] = 'buckling_degree'
ws_new['N2'].font = Font(bold=True, color="BBBBBB")
ws_new['N2'] = 'inside_outside'
ws_new['O2'].font = Font(bold=True, color="BBBBBB")
ws_new['O2'] = 'prosthesis_rotation'
ws_new['P2'].font = Font(bold=True, color="BBBBBB")
ws_new['P2'] = 'backward_degrees'
ws_new['Q2'].font = Font(bold=True, color="BBBBBB")
ws_new['Q2'] = 'osteotomy_thickness'
ws_new['R2'].font = Font(bold=True, color="BBBBBB")
ws_new['R2'] = 'increase_osteotomy'
ws_new['S2'].font = Font(bold=True, color="BBBBBB")
ws_new['S2'] = 'reduce_osteotomy'
ws_new['T2'].font = Font(bold=True, color="BBBBBB")
ws_new['T2'] = 'tubercle_tibia'
ws_new['U2'].font = Font(bold=True, color="BBBBBB")
ws_new['U2'] = 'special_tibia'
ws_new['V2'].font = Font(bold=True, color="BBBBBB")
ws_new['V2'] = 'bone_defect'
ws_new['W2'].font = Font(bold=True, color="BBBBBB")
ws_new['W2'] = 'special_remarks'
ws_new['X2'].font = Font(bold=True, color="BBBBBB")
ws_new['X2'] = 'inside_out'
# 列名(中文)
ws_new['B3'].font = Font(color="BBBBBB")
ws_new['B3'] = '患者姓名'
ws_new['C3'].font = Font(color="BBBBBB")
ws_new['C3'] = '患者ID'
ws_new['D3'].font = Font(color="BBBBBB")
ws_new['D3'] = '患者年龄'
ws_new['E3'].font = Font(color="BBBBBB")
ws_new['E3'] = '患者性别'
ws_new['F3'].font = Font(color="BBBBBB")
ws_new['F3'] = '平台型号'
ws_new['G3'].font = Font(color="BBBBBB")
ws_new['G3'] = '平台延长杆'
ws_new['H3'].font = Font(color="BBBBBB")
ws_new['H3'] = '垫片厚度（mm）'
ws_new['I3'].font = Font(color="BBBBBB")
ws_new['I3'] = '垫片类型'
ws_new['J3'].font = Font(color="BBBBBB")
ws_new['J3'] = '关节伸直情况'
ws_new['K3'].font = Font(color="BBBBBB")
ws_new['K3'] = '屈曲稳定性'
ws_new['L3'].font = Font(color="BBBBBB")
ws_new['L3'] = '明显松弛度数'
ws_new['M3'].font = Font(color="BBBBBB")
ws_new['M3'] = '明显屈曲度数'
ws_new['N3'].font = Font(color="BBBBBB")
ws_new['N3'] = '力线-内外翻'
ws_new['O3'].font = Font(color="BBBBBB")
ws_new['O3'] = '内/外翻度数（°）'
ws_new['P3'].font = Font(color="BBBBBB")
ws_new['P3'] = '力线-假体旋转'
ws_new['Q3'].font = Font(color="BBBBBB")
ws_new['Q3'] = '力线-后倾度数'
ws_new['R3'].font = Font(color="BBBBBB")
ws_new['R3'] = '截骨厚度'
ws_new['S3'].font = Font(color="BBBBBB")
ws_new['S3'] = '增加截骨'
ws_new['T3'].font = Font(color="BBBBBB")
ws_new['T3'] = '减少截骨'
ws_new['U3'].font = Font(color="BBBBBB")
ws_new['U3'] = '胫骨结节'
ws_new['V3'].font = Font(color="BBBBBB")
ws_new['V3'] = '胫骨结节特殊情况'
ws_new['W3'].font = Font(color="BBBBBB")
ws_new['W3'] = '骨缺损情况'
ws_new['X3'].font = Font(color="BBBBBB")
ws_new['X3'] = '胫骨特殊情况备注'
# 数据类型
ws_new['B4'].font = Font(color="BBBBBB")
ws_new['B4'] = '字符型(50)'
ws_new['C4'].font = Font(color="BBBBBB")
ws_new['C4'] = '字符型(50)'
ws_new['D4'].font = Font(color="BBBBBB")
ws_new['D4'] = '字符型(50)'
ws_new['E4'].font = Font(color="BBBBBB")
ws_new['E4'] = '字符型(50)'
ws_new['F4'].font = Font(color="BBBBBB")
ws_new['F4'] = '字符型(50)'
ws_new['G4'].font = Font(color="BBBBBB")
ws_new['G4'] = '字符型(50)'
ws_new['H4'].font = Font(color="BBBBBB")
ws_new['H4'] = '字符型(50)'
ws_new['I4'].font = Font(color="BBBBBB")
ws_new['I4'] = '字符型(50)'
ws_new['J4'].font = Font(color="BBBBBB")
ws_new['J4'] = '字符型(50)'
ws_new['K4'].font = Font(color="BBBBBB")
ws_new['K4'] = '字符型(50)'
ws_new['L4'].font = Font(color="BBBBBB")
ws_new['L4'] = '字符型(50)'
ws_new['M4'].font = Font(color="BBBBBB")
ws_new['M4'] = '字符型(50)'
ws_new['N4'].font = Font(color="BBBBBB")
ws_new['N4'] = '字符型(50)'
ws_new['O4'].font = Font(color="BBBBBB")
ws_new['O4'] = '字符型(50)'
ws_new['P4'].font = Font(color="BBBBBB")
ws_new['P4'] = '字符型(50)'
ws_new['Q4'].font = Font(color="BBBBBB")
ws_new['Q4'] = '字符型(50)'
ws_new['R4'].font = Font(color="BBBBBB")
ws_new['R4'] = '字符型(100)'
ws_new['S4'].font = Font(color="BBBBBB")
ws_new['S4'] = '字符型(50)'
ws_new['T4'].font = Font(color="BBBBBB")
ws_new['T4'] = '字符型(50)'
ws_new['U4'].font = Font(color="BBBBBB")
ws_new['U4'] = '字符型(50)'
ws_new['V4'].font = Font(color="BBBBBB")
ws_new['V4'] = '字符型(50)'
ws_new['W4'].font = Font(color="BBBBBB")
ws_new['W4'] = '字符型(50)'
ws_new['X4'].font = Font(color="BBBBBB")
ws_new['X4'] = '字符型(150)'
# 髌骨情况
if title == '髌骨情况':
ws_new['E1'] = XENCRYPT('echart_data_patella_condition')
# 列名(英文)
ws_new['B2'].font = Font(bold=True, color="BBBBBB")
ws_new['B2'] = 'patient_name'
ws_new['C2'].font = Font(bold=True, color="BBBBBB")
ws_new['C2'] = 'patient_id'
ws_new['D2'].font = Font(bold=True, color="BBBBBB")
ws_new['D2'] = 'patient_age'
ws_new['E2'].font = Font(bold=True, color="BBBBBB")
ws_new['E2'] = 'patient_sex'
ws_new['F2'].font = Font(bold=True, color="BBBBBB")
ws_new['F2'] = 'patella_replace'
ws_new['G2'].font = Font(bold=True, color="BBBBBB")
ws_new['G2'] = 'no_patella_replace'
ws_new['H2'].font = Font(bold=True, color="BBBBBB")
ws_new['H2'] = 'original_thickness'
ws_new['I2'].font = Font(bold=True, color="BBBBBB")
ws_new['I2'] = 'retained_thickness'
ws_new['J2'].font = Font(bold=True, color="BBBBBB")
ws_new['J2'] = 'patella_size'
ws_new['K2'].font = Font(bold=True, color="BBBBBB")
ws_new['K2'] = 'patella_placement'
ws_new['L2'].font = Font(bold=True, color="BBBBBB")
ws_new['L2'] = 'release_lateral'
ws_new['M2'].font = Font(bold=True, color="BBBBBB")
ws_new['M2'] = 'snapping_bouncing'
ws_new['N2'].font = Font(bold=True, color="BBBBBB")
ws_new['N2'] = 'remarks_special_patella'
# 列名(中文)
ws_new['B3'].font = Font(color="BBBBBB")
ws_new['B3'] = '患者姓名'
ws_new['C3'].font = Font(color="BBBBBB")
ws_new['C3'] = '患者ID'
ws_new['D3'].font = Font(color="BBBBBB")
ws_new['D3'] = '患者年龄'
ws_new['E3'].font = Font(color="BBBBBB")
ws_new['E3'] = '患者性别'
ws_new['F3'].font = Font(color="BBBBBB")
ws_new['F3'] = '置换髌骨情况'
ws_new['G3'].font = Font(color="BBBBBB")
ws_new['G3'] = '不置换髌骨'
ws_new['H3'].font = Font(color="BBBBBB")
ws_new['H3'] = '髌骨原始厚度（最厚部分mm）'
ws_new['I3'].font = Font(color="BBBBBB")
ws_new['I3'] = '骨床保留厚度（mm）'
ws_new['J3'].font = Font(color="BBBBBB")
ws_new['J3'] = '髌骨大小'
ws_new['K3'].font = Font(color="BBBBBB")
ws_new['K3'] = '髌骨位置安放'
ws_new['L3'].font = Font(color="BBBBBB")
ws_new['L3'] = '外侧支持带松解'
ws_new['M3'].font = Font(color="BBBBBB")
ws_new['M3'] = '髌骨弹响、弹跳'
ws_new['N3'].font = Font(bold=True, color="BBBBBB")
ws_new['N3'] = '髌骨特殊情况备注'
# 数据类型
ws_new['B4'].font = Font(color="BBBBBB")
ws_new['B4'] = '字符型(50)'
ws_new['C4'].font = Font(color="BBBBBB")
ws_new['C4'] = '字符型(50)'
ws_new['D4'].font = Font(color="BBBBBB")
ws_new['D4'] = '字符型(50)'
ws_new['E4'].font = Font(color="BBBBBB")
ws_new['E4'] = '字符型(50)'
ws_new['F4'].font = Font(color="BBBBBB")
ws_new['F4'] = '字符型(50)'
ws_new['G4'].font = Font(color="BBBBBB")
ws_new['G4'] = '字符型(50)'
ws_new['H4'].font = Font(color="BBBBBB")
ws_new['H4'] = '字符型(50)'
ws_new['I4'].font = Font(color="BBBBBB")
ws_new['I4'] = '字符型(50)'
ws_new['J4'].font = Font(color="BBBBBB")
ws_new['J4'] = '字符型(50)'
ws_new['K4'].font = Font(color="BBBBBB")
ws_new['K4'] = '字符型(50)'
ws_new['L4'].font = Font(color="BBBBBB")
ws_new['L4'] = '字符型(50)'
ws_new['M4'].font = Font(color="BBBBBB")
ws_new['M4'] = '字符型(50)'
ws_new['N4'].font = Font(bold=True, color="BBBBBB")
ws_new['N4'] = '字符型(100)'
# 安装假体情况
if title == '安装假体情况':
ws_new['E1'] = XENCRYPT('echart_data_prosthesis_installation')
# 列名(英文)
ws_new['B2'].font = Font(bold=True, color="BBBBBB")
ws_new['B2'] = 'patient_name'
ws_new['C2'].font = Font(bold=True, color="BBBBBB")
ws_new['C2'] = 'patient_id'
ws_new['D2'].font = Font(bold=True, color="BBBBBB")
ws_new['D2'] = 'patient_age'
ws_new['E2'].font = Font(bold=True, color="BBBBBB")
ws_new['E2'] = 'patient_sex'
ws_new['F2'].font = Font(bold=True, color="BBBBBB")
ws_new['F2'] = 'bone_manufacturer'
ws_new['G2'].font = Font(bold=True, color="BBBBBB")
ws_new['G2'] = 'quantity_cement'
ws_new['H2'].font = Font(bold=True, color="BBBBBB")
ws_new['H2'] = 'dry_clean'
ws_new['I2'].font = Font(bold=True, color="BBBBBB")
ws_new['I2'] = 'not_dry'
ws_new['J2'].font = Font(bold=True, color="BBBBBB")
ws_new['J2'] = 'not_clean'
ws_new['K2'].font = Font(bold=True, color="BBBBBB")
ws_new['K2'] = 'blood_residue'
ws_new['L2'].font = Font(bold=True, color="BBBBBB")
ws_new['L2'] = 'sclerotic_bone'
ws_new['M2'].font = Font(bold=True, color="BBBBBB")
ws_new['M2'] = 'false_decency'
ws_new['N2'].font = Font(bold=True, color="BBBBBB")
ws_new['N2'] = 'removal_bone'
ws_new['O2'].font = Font(bold=True, color="BBBBBB")
ws_new['O2'] = 'removal_bone_des'
ws_new['P2'].font = Font(bold=True, color="BBBBBB")
ws_new['P2'] = 'removal_bone_remarks'
ws_new['Q2'].font = Font(bold=True, color="BBBBBB")
ws_new['Q2'] = 'surface_flushing'
# 列名(中文)
ws_new['B3'].font = Font(color="BBBBBB")
ws_new['B3'] = '患者姓名'
ws_new['C3'].font = Font(color="BBBBBB")
ws_new['C3'] = '患者ID'
ws_new['D3'].font = Font(color="BBBBBB")
ws_new['D3'] = '患者年龄'
ws_new['E3'].font = Font(color="BBBBBB")
ws_new['E3'] = '患者性别'
ws_new['F3'].font = Font(color="BBBBBB")
ws_new['F3'] = '骨水泥厂商'
ws_new['G3'].font = Font(color="BBBBBB")
ws_new['G3'] = '骨水泥数量'
ws_new['H3'].font = Font(color="BBBBBB")
ws_new['H3'] = '骨面冲洗情况-干燥清洁'
ws_new['I3'].font = Font(color="BBBBBB")
ws_new['I3'] = '骨面冲洗情况-不干燥'
ws_new['J3'].font = Font(color="BBBBBB")
ws_new['J3'] = '骨面冲洗情况-不清洁'
ws_new['K3'].font = Font(color="BBBBBB")
ws_new['K3'] = '骨面冲洗情况-血迹残留'
ws_new['L3'].font = Font(color="BBBBBB")
ws_new['L3'] = '硬化骨面处理'
ws_new['M3'].font = Font(color="BBBBBB")
ws_new['M3'] = '假体面'
ws_new['N3'].font = Font(color="BBBBBB")
ws_new['N3'] = '骨水泥清除情况'
ws_new['O3'].font = Font(color="BBBBBB")
ws_new['O3'] = '骨水泥清除情况描述'
ws_new['P3'].font = Font(color="BBBBBB")
ws_new['P3'] = '骨水泥特殊情况备注'
ws_new['Q3'].font = Font(color="BBBBBB")
ws_new['Q3'] = '骨面冲洗情况'
# 数据类型
ws_new['B4'].font = Font(color="BBBBBB")
ws_new['B4'] = '字符型(50)'
ws_new['C4'].font = Font(color="BBBBBB")
ws_new['C4'] = '字符型(50)'
ws_new['D4'].font = Font(color="BBBBBB")
ws_new['D4'] = '字符型(50)'
ws_new['E4'].font = Font(color="BBBBBB")
ws_new['E4'] = '字符型(50)'
ws_new['F4'].font = Font(color="BBBBBB")
ws_new['F4'] = '字符型(50)'
ws_new['G4'].font = Font(color="BBBBBB")
ws_new['G4'] = '字符型(50)'
ws_new['H4'].font = Font(color="BBBBBB")
ws_new['H4'] = '字符型(50)'
ws_new['I4'].font = Font(color="BBBBBB")
ws_new['I4'] = '字符型(50)'
ws_new['J4'].font = Font(color="BBBBBB")
ws_new['J4'] = '字符型(50)'
ws_new['K4'].font = Font(color="BBBBBB")
ws_new['K4'] = '字符型(50)'
ws_new['L4'].font = Font(color="BBBBBB")
ws_new['L4'] = '字符型(50)'
ws_new['M4'].font = Font(color="BBBBBB")
ws_new['M4'] = '字符型(50)'
ws_new['N4'].font = Font(color="BBBBBB")
ws_new['N4'] = '字符型(50)'
ws_new['O4'].font = Font(color="BBBBBB")
ws_new['O4'] = '字符型(50)'
ws_new['P4'].font = Font(color="BBBBBB")
ws_new['P4'] = '字符型(150)'
ws_new['Q4'].font = Font(color="BBBBBB")
ws_new['Q4'] = '字符型(50)'
# 止血带使用情况
if title == '止血带使用情况':
ws_new['E1'] = XENCRYPT('echart_data_usage_tourniquet')
# 列名(英文)
ws_new['B2'].font = Font(bold=True, color="BBBBBB")
ws_new['B2'] = 'patient_name'
ws_new['C2'].font = Font(bold=True, color="BBBBBB")
ws_new['C2'] = 'patient_id'
ws_new['D2'].font = Font(bold=True, color="BBBBBB")
ws_new['D2'] = 'patient_age'
ws_new['E2'].font = Font(bold=True, color="BBBBBB")
ws_new['E2'] = 'patient_sex'
ws_new['F2'].font = Font(bold=True, color="BBBBBB")
ws_new['F2'] = 'bleeding_volume'
ws_new['G2'].font = Font(bold=True, color="BBBBBB")
ws_new['G2'] = 'tourniquet_time'
ws_new['H2'].font = Font(bold=True, color="BBBBBB")
ws_new['H2'] = 'pressure_tourniquet'
ws_new['I2'].font = Font(bold=True, color="BBBBBB")
ws_new['I2'] = 'duration_tourniquet'
ws_new['J2'].font = Font(bold=True, color="BBBBBB")
ws_new['J2'] = 'operation_duration'
ws_new['K2'].font = Font(bold=True, color="BBBBBB")
ws_new['K2'] = 'blood_transfusion'
ws_new['L2'].font = Font(bold=True, color="BBBBBB")
ws_new['L2'] = 'hemostatic_effect'
ws_new['M2'].font = Font(bold=True, color="BBBBBB")
ws_new['M2'] = 'tourniquet_reaction'
ws_new['N2'].font = Font(bold=True, color="BBBBBB")
ws_new['N2'] = 'remarks'
# 列名(中文)
ws_new['B3'].font = Font(color="BBBBBB")
ws_new['B3'] = '患者姓名'
ws_new['C3'].font = Font(color="BBBBBB")
ws_new['C3'] = '患者ID'
ws_new['D3'].font = Font(color="BBBBBB")
ws_new['D3'] = '患者年龄'
ws_new['E3'].font = Font(color="BBBBBB")
ws_new['E3'] = '患者性别'
ws_new['F3'].font = Font(color="BBBBBB")
ws_new['F3'] = '出血量（ml）'
ws_new['G3'].font = Font(color="BBBBBB")
ws_new['G3'] = '止血带应用时间'
ws_new['H3'].font = Font(color="BBBBBB")
ws_new['H3'] = '止血带压力（mmHg）'
ws_new['I3'].font = Font(color="BBBBBB")
ws_new['I3'] = '止血带使用时长（min）'
ws_new['J3'].font = Font(color="BBBBBB")
ws_new['J3'] = '手术时长（min）'
ws_new['K3'].font = Font(color="BBBBBB")
ws_new['K3'] = '输血情况'
ws_new['L3'].font = Font(color="BBBBBB")
ws_new['L3'] = '止血效果'
ws_new['M3'].font = Font(color="BBBBBB")
ws_new['M3'] = '止血带反应'
ws_new['N3'].font = Font(color="BBBBBB")
ws_new['N3'] = '手术-备注信息'
# 数据类型
ws_new['B4'].font = Font(color="BBBBBB")
ws_new['B4'] = '字符型(50)'
ws_new['C4'].font = Font(color="BBBBBB")
ws_new['C4'] = '字符型(50)'
ws_new['D4'].font = Font(color="BBBBBB")
ws_new['D4'] = '字符型(50)'
ws_new['E4'].font = Font(color="BBBBBB")
ws_new['E4'] = '字符型(50)'
ws_new['F4'].font = Font(color="BBBBBB")
ws_new['F4'] = '字符型(50)'
ws_new['G4'].font = Font(color="BBBBBB")
ws_new['G4'] = '字符型(50)'
ws_new['H4'].font = Font(color="BBBBBB")
ws_new['H4'] = '字符型(50)'
ws_new['I4'].font = Font(color="BBBBBB")
ws_new['I4'] = '字符型(50)'
ws_new['J4'].font = Font(color="BBBBBB")
ws_new['J4'] = '字符型(50)'
ws_new['K4'].font = Font(color="BBBBBB")
ws_new['K4'] = '字符型(50)'
ws_new['L4'].font = Font(color="BBBBBB")
ws_new['L4'] = '字符型(50)'
ws_new['M4'].font = Font(color="BBBBBB")
ws_new['M4'] = '字符型(50)'
ws_new['N4'].font = Font(color="BBBBBB")
ws_new['N4'] = '字符型(300)'
wb.save("static/export_excel/ExcelImportTemplate.xlsx")
response = HttpResponse(content_type='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename="ExcelImportTemplate.xlsx"'
wb.save(response)
if response:
return response
return JsonResponse({'status': 'HttpResponse', 'msg': 'Excel下载失败'})
class UploadExcelFile(APIView):
def post(self, request):
try:
user_id = request.META.get("HTTP_USER_ID")
user_name = request.META.get("HTTP_USER_NAME")
roles = request.META.get("HTTP_ROLES")
time_now = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
# 1. 获取上传的文件
file = request.FILES.get('file')
# 2. 保存文件以便以后查阅
file_name = file.name
file_path = "static/export_excel/" + user_id+"_"+roles+"_"+time_now+"_"+file_name
with open(file_path, 'wb') as f:
for chunk in file.chunks():
f.write(chunk)
# 3. 检查文件权限是否匹配
wb = load_workbook(file_path)
sheet_ranges = wb['系统信息，请勿修改']
excel_user_id = XDECRYPT(sheet_ranges['C1'].value)
# excel_user_name = XDECRYPT(sheet_ranges['E1'].value)
excel_roles = XDECRYPT(sheet_ranges['G1'].value)
# print(XDECRYPT(sheet_ranges['G1'].value))
if user_id != excel_user_id:
result = {
"code": 50001,
"message": "用户信息不匹配，请检查上传文件是否正确，确保该上传文件是您的账号生成下载的",
}
return JsonResponse(result, safe=False)
if roles != excel_roles:
result = {
"code": 50001,
"message": "角色权限不匹配，请检查上传文件是否正确，确保该上传文件是您的账号生成下载的，并且下载后管理员未修改您的系统角色权限",
}
return JsonResponse(result, safe=False)
version = GenerateImportBatch().generate(request)
result = {
"code": 20000,
"message": "上传成功",
"file_path": file_path,
"version": version
}
except Exception as e:
result = {
"code": 40001,
"data": str(e),
"message": "获取数据失败"
}
return JsonResponse(result, safe=False)
class ImportExcelData(APIView):
def post(self, request):
try:
user_id = request.META.get("HTTP_USER_ID")
user_name = request.META.get("HTTP_USER_NAME")
roles = request.META.get("HTTP_ROLES")
file_path = request.data.get('file_path')
version_id = request.data.get('version_id')
version_now = request.data.get('version_now')
mode = request.data.get('mode')
version_object = Version.objects.get(id=version_id)
version_object.progress = 2
version_object.save()
# 0. 检查文件权限是否匹配
wb = load_workbook(file_path)
sheet_ranges = wb['系统信息，请勿修改']
excel_user_id = XDECRYPT(sheet_ranges['C1'].value)
# excel_user_name = XDECRYPT(sheet_ranges['E1'].value)
excel_roles = XDECRYPT(sheet_ranges['G1'].value)
if user_id != excel_user_id:
result = {
"code": 50001,
"message": "用户信息不匹配，请检查上传文件是否正确，确保该上传文件是您的账号生成下载的",
}
return JsonResponse(result, safe=False)
if roles != excel_roles:
result = {
"code": 50001,
"message": "角色权限不匹配，请检查上传文件是否正确，确保该上传文件是您的账号生成下载的，并且下载后管理员未修改您的系统角色权限",
}
return JsonResponse(result, safe=False)
mysql_connection = pymysql.connect(
host=settings.DATABASE_ADDRESS, port=settings.DATABASE_PORT, user=settings.DATABASE_USERNAME, password=settings.DATABASE_PASSWORD, cursorclass=pymysql.cursors.DictCursor)
mysql_cursor = mysql_connection.cursor()
# 1. 获取所有sheets
print(mode)
# 导入前去重
if mode == 'before':
for sheet_index, sheet in enumerate(wb.worksheets):
if sheet_index > 0: # 不读第一页系统信息
database_name = XDECRYPT(sheet['C1'].value)
table_name = XDECRYPT(sheet['E1'].value)
menu_code = XDECRYPT(sheet['F1'].value)
flag = False
column_array = []
for row_index, row in enumerate(sheet.iter_rows()):
if row_index == 1:
for cell_index, cell in enumerate(row):
if cell_index > 0:
if cell.value is not None:
# print(cell.value)
column_array.append(cell.value)
column_array.append('version')
column_array.append('import_time')
column_array.append('import_account_id')
column_array.append('import_account_name')
# column_array.append('roles')
column_array.append('status')
if row_index > 3:
value_array = []
for cell_index, cell in enumerate(row):
if cell_index > 0 and cell_index <= (len(column_array) - 5):
if cell.value is not None:
value_array.append(str(cell.value))
else:
value_array.append('')
if len(value_array) > 0:
# print(database_name)
# print(table_name)
print(column_array)
print(value_array)
where_sql = ''
for index1 in range(len(column_array)):
for index2 in range(len(value_array)):
if index1 == 0 and index1 == index2 and value_array[index2] != '':
where_sql += column_array[index1] + \
'=' + "'" + \
value_array[index2]+"'"
elif index1 == index2 and value_array[index2] != '':
where_sql += ' and ' + column_array[index1] + \
'=' + "'" + \
value_array[index2]+"'"
print(where_sql)
select_sql = "select id from " + database_name + \
'.' + table_name + ' where ' + where_sql
print(select_sql)
mysql_cursor.execute(select_sql)
where_id = mysql_cursor.fetchall()
print(where_id)
if where_id == []:
value_array.append(version_now)
value_array.append(
datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
value_array.append(user_id)
value_array.append(user_name)
# value_array.append(roles.replace('[','').replace(']','').replace(' ',''))
value_array.append('1')
insert_sql = "INSERT INTO " + database_name + "." + table_name + \
" (" + ','.join(column_array) + \
") VALUES ('" + \
"','".join(value_array) + "')"
# insert_sql = 'INSERT INTO ' + database_name + '.' + table_name + ' (' + ','.join(column_array) + ') VALUES ("' + '","'.join(value_array) + '")'
print(insert_sql)
mysql_cursor.execute(insert_sql)
flag = True
if flag:
new_version = Version()
new_version.version = version_now
new_version.menu_code = menu_code
new_version.tables = database_name + '.' + table_name
new_version.columns = ','.join(column_array)
new_version.progress = 100
new_version.is_finished = 1
new_version.is_synced = 0
new_version.data_status = 1
new_version.create_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
new_version.create_account_id = user_id
new_version.create_account_name = user_name
new_version.save()
# 导入后去重
if mode == 'after':
for sheet_index, sheet in enumerate(wb.worksheets):
if sheet_index > 0: # 不读第一页系统信息
database_name = XDECRYPT(sheet['C1'].value)
table_name = XDECRYPT(sheet['E1'].value)
menu_code = XDECRYPT(sheet['F1'].value)
flag = False
column_array = []
delete_column_array = []
for row_index, row in enumerate(sheet.iter_rows()):
if row_index == 1:
for cell_index, cell in enumerate(row):
if cell_index > 0:
if cell.value is not None:
# print(cell.value)
column_array.append(cell.value)
delete_column_array.append(
cell.value)
column_array.append('version')
column_array.append('import_time')
column_array.append('import_account_id')
column_array.append('import_account_name')
# column_array.append('roles')
column_array.append('status')
if row_index > 3:
value_array = []
for cell_index, cell in enumerate(row):
if cell_index > 0 and cell_index <= (len(column_array) - 5):
if cell.value is not None:
value_array.append(str(cell.value))
else:
value_array.append('')
if len(value_array) > 0:
# print(database_name)
# print(table_name)
value_array.append(version_now)
value_array.append(
datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
value_array.append(user_id)
value_array.append(user_name)
# value_array.append(roles.replace('[','').replace(']','').replace(' ',''))
value_array.append('1')
insert_sql = "INSERT INTO " + database_name + "." + table_name + \
" (" + ','.join(column_array) + \
") VALUES ('" + \
"','".join(value_array) + "')"
# insert_sql = 'INSERT INTO ' + database_name + '.' + table_name + ' (' + ','.join(column_array) + ') VALUES ("' + '","'.join(value_array) + '")'
print(insert_sql)
mysql_cursor.execute(insert_sql)
# 去重
select_sql = "select id," + \
','.join(delete_column_array)+",count(*) " + ' from ' + database_name + "." + \
table_name + " group by " + \
','.join(delete_column_array)
print(select_sql)
mysql_cursor.execute(select_sql)
data = mysql_cursor.fetchall()
for row in data:
if row['count(*)'] == 2:
print(row['count(*)'], row['id'])
# 删除该id
delete_sql = " delete from " + database_name + \
"." + table_name + \
" where id = "+str(row['id'])
print(delete_sql)
mysql_cursor.execute(delete_sql)
elif row['count(*)'] > 2:
print(row['count(*)'], row['id'])
where_sql = ''
for column in delete_column_array:
print(row[column])
if row[column] != '':
where_sql += ' and ' + \
column + '=' + "'" + \
row[column]+"'"
# 除了该id都删除
print(where_sql)
delete_sql = " delete from " + database_name + "." + \
table_name + " where id != " + \
str(row['id']) + where_sql
print(delete_sql)
mysql_cursor.execute(delete_sql)
flag = True
if flag:
new_version = Version()
new_version.version = version_now
new_version.menu_code = menu_code
new_version.tables = database_name + '.' + table_name
new_version.columns = ','.join(column_array)
new_version.progress = 100
new_version.is_finished = 1
new_version.is_synced = 0
new_version.data_status = 1
new_version.create_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
new_version.create_account_id = user_id
new_version.create_account_name = user_name
new_version.save()
version_object.progress = 99
version_object.save()
mysql_cursor.execute("commit")
mysql_cursor.close()
result = {
"code": 20000,
"message": "数据导入进程准备完毕",
"file_path": file_path
}
version_object.progress = 100
version_object.save()
except Exception as e:
result = {
"code": 40001,
"data": str(e),
"message": "数据导入失败"
}
return JsonResponse(result, safe=False)
class GenerateImportBatch():
def generate(self, request):
version_last = Version.objects.all().order_by('-id').first()
if version_last is not None:
table_version = version_last.version
else:
table_version = None
version_plus_one = VersionIncrease().increase(table_version)
version_now = Version()
version_now.version = version_plus_one
version_now.create_time = time.strftime(
"%Y-%m-%d %H:%M:%S", time.localtime())
version_now.create_account_id = request.META.get("HTTP_USERID")
version_now.create_account_name = request.META.get("HTTP_USERNAME")
version_now.is_finished = 0
version_now.is_synced = 0
version_now.progress = '1'
version_now.save()
result = {
"version_id": version_now.id,
"version_now": version_plus_one,
}
return result
class QueryImportExcelDataProgress(APIView):
def post(self, request):
try:
user_id = request.META.get("HTTP_USER_ID")
user_name = request.META.get("HTTP_USER_NAME")
roles = request.META.get("HTTP_ROLES")
file_path = request.data.get('file_path')
version_id = request.data.get('version_id')
print(file_path)
version = Version.objects.get(id=version_id)
progress = version.progress
result = {
"code": 20000,
"message": "数据导入进程准备完毕",
"file_path": file_path,
"progress": progress
}
except Exception as e:
result = {
"code": 40001,
"data": str(e),
"message": "数据导入失败"
}
return JsonResponse(result, safe=False)
class VersionIncrease():
def increase(self, versionNow):
version = ''
if versionNow is None:
version = time.strftime("%Y", time.localtime()) + '.1'
else:
versionPrefix = str(versionNow).split('.')[0]
versionPrefixInt = int(str(versionNow).split('.')[0])
yearNow = str(time.strftime("%Y", time.localtime()))
yearNowInt = int(str(time.strftime("%Y", time.localtime())))
if yearNowInt <= versionPrefixInt:
versionSuffix = str(versionNow).split('.')[1]
version = str(versionNow).split(
'.')[0] + '.' + str(int(versionSuffix)+1)
else:
version = yearNow+'.1'
return version
411
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
