import random
import string
from openpyxl import load_workbook
# 定义IB Modality和Series的对应关系
LOCATION = {
'APM': {
'APM': ['Others']
},
'AW': {
'AW': ['ADVANCE AWS','Others']
},
'CT': {
'CT': ['16Pro','32Pro','ACT & ACTs','ACT & ACTs-19IW','ACT & ACTs-20IW','ACT & ACTs-21BW','ASPIRE Plus','Apex Essential','BS 3.5M','BS 6.3M','Brivo315','Brivo325','Brivo385','CARDIOGRAPHE','CT/i','EVO','HD750','Hispeed Series','LS 6.3M','LS 7.5M','LS 8.0M','LightSpeed Plus ','LightSpeed QX/I','LightSpeed RT16','LightSpeed Ultra','Lightspeed Plus','Lightspeed Ultra','Max','NEMOTO INJECTOR','Optima 660','Optima 680E ','Optima520','Optima540','Optima620','Optima670','Optima680','Others','PETCT','Prospeed Series','REVOLUTION','REVOLUTION APEX','REVOLUTION ES','RT 580','RT590','Rev Eagle','Revolution ACE','Revolution ACE ES','Revolution Maxima ','Revolution Maxima Select','Revolution Power','SYTEC','Synergy','VCT','frontier']
},
'DXR': {
'DXR': ['23','ACHILLES','ALPHA','AMX Explorer','Advantx LCP','CRYSTAL NOVA','D8K','DEF 320','DEF 656','DEF TEMPO','DEF120','DELTA 32','DIAMOND','DPX','DPX BRAVO','DPX DUO','DPX NT','DR-F','DR-FeiTian','DR-Mobile AMX','DR-Mobile TMX','DR-Mobile VMX','IDI','IDR','IDXA','LITE WORKFLOW','MM-Goldseal','MPH','MPX','OTHERS','Others','PACE SELECT','PERFORMA','PIXI','PRESTIGE','PRISTINA Athena','PRISTINA Hygeia','PRODIGY','PROTEUS XR','PS800','PT800','R500','RXI','SENO 2000D','SENO 500T/600T','SENO 700T','SENO 800T','SENO ADV','SENO CRYSTAL','SENO DMR','SENO DS','SENO ESS','SENO PRISTINA','SENO PRISTINA 2D','SENO PRISTINA 3D','SENOIRIS H/W + S/W','SLIHOUETTE VR','Seno Pristina option','TH600','TX3','UPRIGHT','XR200','XR220','XR240','XR316','XR515','XR575','XR6000','XR646HD','XR650','XR656','XRD']
},
'EX': {
'EX': ['EVO']
},
'FI': {
'FI': ['D Future','D MAX','D MAX+','D Power','D610','D710','DIQ','DISCOVERY 600 FULL','DISCOVERY 610 64SL STANDARD','DLS','DMI','DST','DSTE','DVCT','Delite','EXPLORE VISTA SR/DR PET SYSTEM','Entegra','Explore','FASTLAB','FLEX TRIMODALITY','MINItrace','Others','PETtrace','RP COMECER HOT CELLS RELATED','TL FXC-ME 115V','TL FXF-E 115V','TL FXF-N 115V','TL MXFDG 115V','TRACERCENTER-HOTCELLS','TRACERLAB FX','TRIUMPH TRIMODALITY W/LABPET4','XELERIS-PET']
},
'IIS': {
'IIS': ['IIS','Others']
},
'Intervention': {
'Intervention': ['Advantx LCP','IGS 323','IGS 330','IGS 3Eng','IGS 520','IGS 530','IGS 540','IGS 620','IGS 630','IGS 730','IGS 740','INNOVA2000','Innova 2100','Innova 3100','Innova 3131','Innova 4100','LCA','LCP','OPTIMA3100','Others']
},
'LCS': {
'LCS': ['0','1.5T HDxt','9 Series','9100C','ADU','AMPLIFIER','ANE','Aelite NXT','Aespire Series','Aestiva Series','Airborne 750i','Aisys Series','Apexpro CH','Avance Series','B1x5 Series','B650','BILISOFT','BiliSoft','Biliblanket ','Bx0 Series','Bx50','Bx50 Series','CAM','CARDIODAY','CARESCAPE Canvas D19','CARESCAPE ONE','CASE','CIC','CL/ML GE CLIENT','COMBOLAB','COMPUTER CL/ML','COMPUTER CL/ML Z440','COMPUTER CL/ML Z600 NO OS','COROMETRICS 120 SERIES','COROMETRICS 170 SERIES','COROMETRICS 250 SERIES','CSCS','CV WEB','CardioCap','CardioServ','CardioSys','Cardiosoft','Care Plus ','Carescape Bx0 Series','Carescape Bx50 Series','Carescape R860','Carestation 30','Carestation 6x0 Series','Carestation 7x0 Series','Centiva/5','Combolab','Dash Monitors','E Modules','E modules','E-MASIMO-00, MASIMO SAT MODULE','EBIKE','Eagle Series','Engstrom','F0 Dock','GIRAFFE RESUS','GIRAFFE SHUTTLE','Gateway','Giraffe Blue Spot PT','Giraffe Incubator','Giraffe Incubator Carestation','Giraffe Omnibed','Giraffe Omnibed Carestation','Giraffe Spot Phototherapy','Giraffe Warmer','Glight','Graffe Shuttle','IEB','IVENT 101','IWS','Incubator','Invasive Controller','LED EXAM','LULLABY RESUS','Lullaby PT','Lullaby Warmer','M Modules','MAC 1200','MAC 1600','MAC 2000','MAC 3500','MAC 400','MAC 5','MAC 500','MAC 5000','MAC 5500','MAC 600','MAC 7','MAC 800','MAC C3','MAC IT','MAC LINK','MAC LITE','MAC VU360','MAC3500','MARS PC','MARS Unix','MIC','MRI Patient Monitor','MS','MUSE','Micropace','Mobile care','Navigator','PANDA IRES WARMER','PANDA IRES WARMER FREESTANDING','PANDA IRES WARMER','PDM','PDM BASE STATION','PRN 50M','PRN50','Pro 1000','Pro series','Procare series','QS','Responder','Resuscitation Unit','S/5 Monitors','SEER 1000','SEER Recorders','SERVER CL/ML INW','STIMULATOR','Solar Modules','Solar Monitors','Spectrolite','T1900','T2000','T2100','T2100-ST','T4 Transmiter','TEC 850 ISO','TELEMETRY SERVER','TONOPORT V','TUFFSAT','Tango','Teleguard system','Transfmiter','Transport Pro','UnityID','V100','VIVO x0','Vaporizer','dashport','iVent 201',' CSCS V3']
},
'MR': {
'MR': ['0.2T','0.35T','0.3T','0.5T','1.0T','1.5T','1.5T 450','1.5T 450/450W','1.5T 450W','1.5T ARTIST','1.5T Artist','1.5T CREATOR','1.5T EXPLORER','1.5T HDE','1.5T HDxt','1.5T MR355','1.5T MR360','1.5T Non Excite','1.5T SIGNA PRIME','1.5T Signa MR355','1.5T Signa MR360','1.5T Voyager','3.0T','3.0T 750','3.0T 750W','3.0T ARCHITECT','3.0T HDX','3.0T Hero','3.0T PIONEER','3.0T Premier','MR Spinlab','MR380','Others','PET MR',' SIGNA 7.0T']
},
'NM': {
'NM': ['530C','B615','D630','D670','D860','D870','DISCOVERY NM/CT 670 3/8X16 ASIR','DISCOVERY NM/CT 670 DR 3/8 16SL','Entegra','Hawkeye','INFINIA','MPR/MPS','MYOSPECT CARDIAC PREMUIM','NM 830 3/8','NM XELERIS 3.1 VIEW','NM XELERIS UPGRADE','NM/CT 850 3/8 ROW','O640','Optima540','SMV DST-XLI','VENTRI','VG/VH/MG','XEL4 SWUG PKG JPN','XELERIS VIEW( DESKTOP)ALT','XELERIS-NM','XPERT','Xeleris V Workstation Full']
},
'Others': {
'Others': ['Others']
},
'Surgery': {
'Surgery': ['IT3500','LCE','NAV','NC100','OEC 3D','OEC Elite','OEC Elite CFD','OEC ONE','OEC ONE ASD','OEC ONE CFD','OEC-Goldseal','OEC2800','OEC6600','OEC6800','OEC715','OEC7500','OEC7600','OEC7700','OEC785','OEC7900','OEC850','OEC865','OEC8800','OEC9600','OEC9800','OEC9900','OECMINIVIEW','STENO','steno']
},
'US': {
'US': ['DPX','ECHOPAC','EchoPAC','INVENIA','Image Vault SW','LOGIQ 100','LOGIQ 180','LOGIQ 200','LOGIQ 3','LOGIQ 400','LOGIQ 5','LOGIQ 50','LOGIQ 500','LOGIQ 7','LOGIQ 700','LOGIQ 9','LOGIQ 9 BT03 100/120 NTSC','LOGIQ A1','LOGIQ A5 BT09','LOGIQ BOOK','LOGIQ C','LOGIQ C9','LOGIQ E','LOGIQ E20','LOGIQ E8','LOGIQ E9','LOGIQ F','LOGIQ F3','LOGIQ FORTIS','LOGIQ P10','LOGIQ P3','LOGIQ P5/P6','LOGIQ P7/P9','LOGIQ P8 R4','LOGIQ S6','LOGIQ S7/S8','LOGIQ TOTUS','LOGIQ V1','LOGIQ V1 V2','LOGIQ V3/V5','LOGIQBOOK','Others','System 5','VENUE','VENUE 40','VENUE 50','VENUE FIT','VENUE GO','VERSANA','VIVID Q','VIVID 3/4','VIVID 7','VIVID E','VIVID E7/E9','VIVID E80/E90/E95','VIVID I/Q','VIVID IQ','VIVID S5/S6','VIVID S60/S70','VIVID T8','VIVID T9','VOLUSON 530','VOLUSON 730','VOLUSON E10','VOLUSON E6','VOLUSON E8','VOLUSON EXPERT 18','VOLUSON EXPERT 20','VOLUSON EXPERT 22','VOLUSON EXPERT 24','VOLUSON I/E','VOLUSON P6','VOLUSON P8','VOLUSON S10','VOLUSON S6/S8','VOLUSON S8 TOUCH BT18','VOLUSON SIGNATURE 18','VOLUSON SWIFT','VSCAN','VSCAN AIR','Viewpoint','Vscan Extend','others']
}
}
# 生成随机数字
def test_generate_random_number():
return random.randint(1, 100)
# 定义每个 sheet 对应的字段名和规则
sheet_field_rules = {
'Segmentation Management': {
'start_field': 'Type',
'Type': ['MSA', 'Non-MSA'] * 5,
'Segmentation Name': [f'TestSegmentation{test_generate_random_number()}' for _ in range(10)],
'VAT Rate(%)': [test_generate_random_number() for _ in range(10)],
'Old Segmentation Name': [f'TestSegmentationName{test_generate_random_number()}' for _ in range(10)],
'Status': ['Active', 'Inactive'] * 5,
'Description': '测试数据,可删除'
},
'Offering Management': {
'start_field': 'Segmentation Name',
'Segmentation Name': ['ZZB'] * 5,#'ZZB', 'AI','APM','ZXB','ZUB','ZUB','ZUB'
'Offering Name': [f'ZZB_Offering{test_generate_random_number()}' for _ in range(10)],
'IB Modality':list(LOCATION.keys()),
'订单是否允许跨Modality':['是', '否']* 5,
'标准PM次数': [test_generate_random_number() for _ in range(10)],
'远程PM次数': [test_generate_random_number() for _ in range(10)],
'Old Offering Name': ['CN AI-MSA 智县域年费-订阅','CN AI-MSA 智超声年费-订阅','APM数字医工云版-年费','APM数字医工本地版-年费','APM供应商管理-年费','APM效期管理-年费','APM不良事件管理-年费','APM调剂中心-年费','APM科室调剂-年费','APM购置管理-年费','APM备件管理-年费','APM特种设备管理-年费','APM评审·质控-年费','APM等级评审-年费','APM设备质控-年费','APM无人值守-年费','APM RFID智慧盘点-年费','APM大放物联绩效-年费','APM超声/窥镜物联绩效-年费','APM小设备物联绩效-年费','APM物联绩效基础包-年费','US-智享保','CT-TUI-年','CT-TUI-年+智慧球管提前订货','CT-TUI-低病人量','CT-TUI-曝光秒','CN AI-MSA 智影像年费-买断','CN AI-MSA 智县域年费-买断','CN AI-MSA 智超声年费-买断','CN AI-MSA 智影像年费-订阅','CT-智享保-A','CT-智享保-B','CT-Premium All-in(高病人量日均>=50)-A','CT-Premium All-in(高病人量日均>=50)-B','CT-Premium All-in(中病人量日均30-50)-A','CT-Premium All-in(中病人量日均30-50)-B','CT-智优保-A','CT-智优保-B','CT-智尊保','CT-智康保','低病人量','低病人量','APM评审·质控-年费','APM供应商管理-年费','CT-智优保-A','APM调剂中心-年费'] * 5,
'有效期(开始时间)':'30',
'有效期(结束时间)':'30',
'Status': ['Active', 'Inactive'] * 5,
'订单是否允许跨End User':['是', '否']* 5,
'是否取Offering价格':['是', '否']* 5,
'Group Name': [f'Group{test_generate_random_number()}' for _ in range(10)],
'类型': ['Standard', 'Additional'] * 5,
'标识': ['主机', 'Training','AW'] * 5,
'是否使用PSI PM': ['N', 'Y'] * 5,
'Status': ['Active', 'Inactive'] * 5,
'Series_a':[],
'Series_b':[],
'List Price': [test_generate_random_number() for _ in range(10)],
'FMV': [test_generate_random_number() for _ in range(10)],
'Catalog No.':['Corrective Unlimited','Tube Watch Proactive','D3165T','D3143T'],
'CoverageType': ['Optional', 'Base'] * 5,
'Series_a':[],
'Series_b':[],
'List Price': [test_generate_random_number() for _ in range(10)],
'FMV': [test_generate_random_number() for _ in range(10)],
}
}
# # 根据IB Modality填充Series_a和Series_b
# for ib_modality in sheet_field_rules['IB Modality and Series_a']['IB Modality']:
# series_as = LOCATION[ib_modality].keys()
# for series_a in series_as:
# sheet_field_rules['IB Modality and Series_a']['Series_a'].append(series_a)
# series_bs = LOCATION[ib_modality][series_a]
# for series_b in series_bs:
# sheet_field_rules['IB Modality and Series_a']['Series_b'].append(series_b)
# 检查键是否存在,避免 KeyError
if 'IB Modality and Series_a' in sheet_field_rules:
# 初始化 Series_a 和 Series_b 列表
if 'Series_a' not in sheet_field_rules['IB Modality and Series_a']:
sheet_field_rules['IB Modality and Series_a']['Series_a'] = []
if 'Series_b' not in sheet_field_rules['IB Modality and Series_a']:
sheet_field_rules['IB Modality and Series_a']['Series_b'] = []
# 遍历 IB Modality
for ib_modality in sheet_field_rules['IB Modality and Series_a']['IB Modality']:
if ib_modality in LOCATION:
series_as = LOCATION[ib_modality].keys()
for series_a in series_as:
sheet_field_rules['IB Modality and Series_a']['Series_a'].append(series_a)
if series_a in LOCATION[ib_modality]:
series_bs = LOCATION[ib_modality][series_a]
for series_b in series_bs:
sheet_field_rules['IB Modality and Series_a']['Series_b'].append(series_b)
# 加载工作簿
workbook = load_workbook('C:\\Test_GIT\\common\\case\\data\\Admin.xlsx')
# 遍历每个 sheet
for sheet_name, field_rules in sheet_field_rules.items():
if sheet_name in workbook.sheetnames:
sheet = workbook[sheet_name]
# 检查表头是否已经存在
if sheet.max_row == 0:
# 写入表头
headers = list(field_rules.keys())
headers.remove('start_field')
sheet.append(headers)
for i in range(len(field_rules['IB Modality'])):
row_data = []
start_field = field_rules['start_field']
ib_modality = field_rules[start_field][i % len(field_rules[start_field])]
# 插入IB Modality
row_data.append(ib_modality)
# 插入对应的Seriesa和Seriesb
series_as = LOCATION[ib_modality]
for series_a, series_bs in series_as.items():
row_with_series = row_data.copy()
row_with_series.append(series_a)
for series_b in series_bs:
row = row_with_series.copy()
row.append(series_b)
for field, values in field_rules.items():
if field not in [start_field, 'Series_a', 'Series_b']:
if isinstance(values, list):
row.append(values[i % len(values)])
else:
row.append(values)
sheet.append(row)
print(f"插入数据到 Excel: {row}")
# 保存修改后的工作簿到原文件路径
workbook.save('C:\\Test_GIT\\common\\case\\data\\Admin.xlsx')
解析这段代码
最新发布