def test_bad_extension():
import excel2img
try:
excel2img.export_img("test.xlsx", "abc.xyz", "Sheet1", None)
except ValueError as e:
if 'Unsupported image format' in str(e): return # success
assert 0, "ValueError('Unsupported image format .XYZ') should have been thrown"
def generate_output(self):
'''Generates desired image file from Template's named range'''
self.report_file_name = 'Report ' + self.date_of_today_string + REPORT_FORMAT
#Generate output image - report:
excel2img.export_img(self.temp_TEMPLATE_PATH,
'data/' + self.report_file_name, None,
'Output_Area')
def run_sheet(sheet_name):
import excel2img
fnout = sheet_name + ".png"
if os.path.exists(fnout): os.unlink(fnout)
try:
excel2img.export_img("test.xlsx", fnout, sheet_name)
except OSError as e:
if "Failed to start Excel" in str(e) and os.environ.get(
"PYTEST_SKIP_EXCEL"):
# Waive Excel functionality on Travis
return
raise
assert os.path.exists(fnout), fnout + " didn't get generated"
def stackImage(self):
os.chdir(self.Lesker_Folder_Path)
for file_name in os.listdir(self.Lesker_Folder_Path):
if fnmatch.fnmatch(file_name, '* Batch %d' % self.Batch_Number):
os.chdir(
'P:\Forschungs-Projekte\OLED-measurements Bruchsal\B 2018 Lesker\%s'
% (file_name))
for file_name in glob.glob('*-sheet.xlsx'):
excel2img.export_img(file_name,
"Batch %s.png" % self.Batch_Number,
"", "fabSheet!D14:I31")
def generate_row(self, data_row, tag_cells): # Reload/reopen template file self.reload_template() # Replace all tags on cells that have them self.replace_tag_cells_in_template(tag_cells, data_row) # Save template file filename_excel = os.path.join(self.folder_excel, self.prefix + str(data_row[0].value) + ".xlsx") self.wb_template.save(filename_excel) # Save image file if self.generate_image_files: filename_image = os.path.join(self.folder_image, self.prefix + str(data_row[0].value) + ".png") excel2img.export_img(filename_excel, filename_image, None, None) if not self.generate_excel_files: os.remove(filename_excel)
def convert_to_png(self, file):
args = [
file,
'{}.png'.format(os.path.splitext(file)[0]),
'',
''
]
if self.all_cells:
args[2] = self.sheet_name
if cell_range:
args[3] = '{}!{}'.format(self.sheet_name, self.cell_range)
try:
excel2img.export_img(*args)
except Exception as e:
if self.raise_errors:
raise(e)
print('Exception raised in excel2img.export_img.')
print(e)
print('Image not saved')
def process(self,
code,
fname,
sheet="",
range="",
title=None,
div_style=None):
"""
Snapshot specified range from Excel file as picture and dump it to png
```xlsimg("fname", "optional sheet", "optional range", "optional title")
"""
if title is None:
# construct default title
atitle = []
if sheet != '': atitle.append(sheet)
if range != '': atitle.append(range)
if atitle == []:
atitle.append(os.path.splitext(os.path.basename(fname))[0])
title = '_'.join(atitle)
fn_base = self.pp.tofname("%s_%s_%s" % (fname, sheet, range))
fn_input, fname = self.pp.get_asset(fname, False)
fn_out = os.path.join(self.pp.dirs[-1], self.pp.auto, fn_base + '.png')
fn_out_relative = os.path.relpath(fn_out, self.pp.dirs[0])
if sheet == '': sheet = None
if range == '': range = None
if (not self.pp.exists_and_newer(fn_out, fn_input)):
import excel2img
excel2img.export_img(fn_input, fn_out, sheet, range)
self.pp.timestamp(fn_out)
# Return the link to the new png file
return "\n<div class=\"figure\">\n[](%s)<p class=\"caption\">%s</p></div>" % (
title, fn_out_relative, fname, title)
]
print("\nSelect the list of files that you want to convert to PDF\n")
for i in range(len(excelFiles)):
print("%d) %s" % (i, excelFiles[i]))
filesToConvert = [
int(index)
for index in input("Enter values separated by spaces:: ").split()
]
for index in filesToConvert:
outFolder = 'Output\\' if excelFiles[index] in os.listdir(
'Output') else 'Output_coloured\\'
excel2img.export_img(outFolder + excelFiles[index],
'Output_Pdf\\' + excelFiles[index][:-5] + '.png',
"Main", None)
print(excelFiles[index], " Exported")
images = []
for image in os.listdir('Output_Pdf'):
if image.lower().endswith('.png'):
image = Image.open("Output_Pdf\\" + image)
images.append(image.convert('RGB'))
print(image, " Converted")
images[0].save('Output_Pdf\\' + input("Enter File Name:: ") + '.pdf',
save_all=True,
append_images=images[1:],
resolution=100.0)
continue
elif col == 'C':
ws.column_dimensions[col].width = 13
continue
elif col == 'D':
ws.column_dimensions[col].width = 48
continue
elif col == 'E':
ws.column_dimensions[col].width = 9.86
continue
elif col == 'F':
ws.column_dimensions[col].width = 12
continue
elif col == 'G':
ws.column_dimensions[col].width = 78.43
continue
mistakeReportWorkbook.save(f'Sent/{name}MistakeReport.xlsx')
# excel is saved as png and is then sent to logger
excel2img.export_img(f'Sent/{name}MistakeReport.xlsx',
f"Sent/{name}MistakeReport.png", "Sheet",
None)
sendMistakeReport(name, user['Channel'])
paper += 1
timerEnd = time.time()
print(timerEnd - timerStart)
print(f'You saved {paper} pieces of paper today')
sys.exit()
def actual(INPATH,OUTPATH):
wb1=pd.read_excel(INPATH+'2G_KPI_Report_Hourly_IBS.xlsx',sheet_name="Report 3")
wb2=pd.read_excel(INPATH+"2G_Relation-wise_HOSR_Hourly_IBS.xlsx",sheet_name="Report 1")
wb3=pd.read_excel(INPATH+'3G_KPI_Report_Hourly_IBS.xlsx',sheet_name="3G Cellwise Hourly")
wb4=pd.read_excel(INPATH+"3G_Relation-wise_HOSR_Hourly_IBS.xlsx",sheet_name="Report 1")
wb5=pd.read_excel(INPATH+'4G_KPI_Report_Hourly_IBS.xlsx',sheet_name="4G KPI Table")
wb6=pd.read_excel(INPATH+"4G_Relation-wise_HOSR_Hourly_IBS.xlsx",sheet_name="HOSR")
wb7=pd.read_excel(INPATH+"4G_UL_RSSI_Houly_IBS.xlsx",sheet_name="UL RSSI")
wb8=pd.read_excel(INPATH+'CA_Performance_Cellwise_Hourly.xlsx',sheet_name="Peak Throughput")
wb9=pd.read_excel(INPATH+'3G_Throughput_Hourly.xlsx',sheet_name="3G KPI Table PLMN Level")
wb10=pd.read_excel(INPATH+'CA_Performance_Cellwise_Hourly.xlsx',sheet_name="CA")
wb11g=pd.read_excel(INPATH+'2G_O_D_to_I_D_Relation-wise_HOSR_Daily_IBS.xlsx',sheet_name="Report 1")
wb12=pd.read_excel(INPATH+'4G_O_D_to_I_D_Relation-wise_HOSR_Daily_IBS.xlsx',sheet_name="HOSR")
wb11=pd.read_excel(INPATH+'GD-3G_Relation-wise_HOSR_Hourly_IBS.xlsx',sheet_name="Report 1")
wb21=pd.read_excel(INPATH+'ID-3G_Relation-wise_HOSR_Hourly_IBS.xlsx',sheet_name="Report 1")
wb31=pd.read_excel(INPATH+'OD-3G_Relation-wise_HOSR_Hourly_IBS.xlsx',sheet_name="Report 1")
wb41=pd.read_excel(INPATH+'MS-3G_Relation-wise_HOSR_Hourly_IBS.xlsx',sheet_name="Report 1")
#SRS=pd.read_excel(INPATH+"SRS_DATABASE.xlsx",sheet_name="Outdoor Site Data")
wb1=wb1.replace("#DIV/0","")
wb2=wb2.replace("#DIV/0","")
wb3=wb3.replace("#DIV/0","")
wb4=wb4.replace("#DIV/0","")
wb5=wb5.replace("#DIV/0","")
wb6=wb6.replace("#DIV/0","")
wb7=wb7.replace("#DIV/0","")
wb8=wb8.replace("#DIV/0",0)
wb9=wb9.replace("#DIV/0",0)
#wb8=wb8.fillna(0)
#wb9=wb9.fillna(0)
wb10=wb10.replace("#DIV/0","")
wb11g=wb11g.replace("#DIV/0","")
wb12=wb12.replace("#DIV/0","")
#wb1.columns=wb1.iloc[0]
#wb1.columns
#wb1=wb1.reindex(wb1.index.drop(0)).reset_index(drop=True)
GSM=wb1.iloc[:,1:]
#wb2_2=wb2
wb2a=wb2.iloc[1:,1:]
#wb2a
wb2a.columns=wb2a.iloc[0]
wb2a.columns
GSM_HO=wb2a.reindex(wb2a.index.drop(1)).reset_index(drop=True)
#wb3a.columns=wb3.iloc[0]
#wb3a.columns
#wb3b=wb3a.reindex(wb3a.index.drop(0)).reset_index(drop=True)
WCDMA=wb3.iloc[:,1:]
#wb4_4=wb4
wb4a=wb4.iloc[1:,1:]
wb4a.columns=wb4a.iloc[0]
wb4a.columns
WCDMA_HO=wb4a.reindex(wb4a.index.drop(1)).reset_index(drop=True)
#wb5.columns=wb5.iloc[0]
#wb5.columns
#wb5=wb5.reindex(wb5.index.drop(0)).reset_index(drop=True)
LTE_KPI=wb5.iloc[:,1:]
#wb6.columns=wb6.iloc[0]
#wb6.columns
#wb6=wb6.reindex(wb6.index.drop(0)).reset_index(drop=True)
LTE_HO=wb6.iloc[:,1:]
#wb7.columns=wb7.iloc[0]
#wb7.columns
#wb7=wb7.reindex(wb7.index.drop(0)).reset_index(drop=True)
LTE_RSSI=wb7.iloc[:,1:]
WCDMA1=WCDMA[['Date','UCell Name','RRC Success Rate CS (%)','RRC Success Rate PS (%)','RAB Success Rate Speech (%)','RAB Success Rate HS (%)', 'Speech Drop Rate (%)','PS Drop Rate (%)','HS CC (%)','Soft Handover (%)','Soft HO Overhead (%)','IUCS SR (%)','IUPS SR (%)','Data Volume (GB)','Voice Traffic Erlang']]
WCDMA_RSSI=WCDMA[['Date','UCell Name','UL RSSI (dBm)']]
LTE_RSSI=LTE_RSSI[['Date','EUtranCell Id','UL RSSI']]
WCDMA_peak=wb9.iloc[1:,1:]
WCDMA_peak.columns=WCDMA_peak.iloc[0]
WCDMA_peak.columns
WCDMA_peak=WCDMA_peak.reindex(WCDMA_peak.index.drop(1)).reset_index(drop=True)
#LTE_peak=wb8
#LTE_peak.columns=wb8.iloc[0]
#LTE_peak.columns
LTE_peak=wb8.iloc[:,1:]
#CA=wb10.iloc[0:]
#CA.columns=CA.iloc[0]
#CA.columns
#CA=CA.reindex(CA.index.drop(0)).reset_index(drop=True)
CA=wb10.iloc[:,1:]
GSM_ngb=wb11g.iloc[1:,1:]
GSM_ngb.columns=GSM_ngb.iloc[0]
GSM_ngb=GSM_ngb.reindex(GSM_ngb.index.drop(2)).reset_index(drop=True)
LTE_ngb=wb12.iloc[:,1:]
#LTE_ngb
#LTE_ngb.columns=LTE_ngb.iloc[0]
#LTE_ngb.columns
#LTE_ngb=LTE_ngb.reindex(LTE_ngb.index.drop(0)).reset_index(drop=True)
end_time=time.time()
print("Total time taken to load the excel:",end_time-start_time)
start1_time=time.time()
siteid=pd.read_excel(INPATH+"siteid.xlsx",sheet_name="Sheet1")
for sitename in siteid["Sitename"]:
sec1_4g_1=pd.DataFrame([])
sec2_4g_1=pd.DataFrame([])
sec3_4g_1=pd.DataFrame([])
sec4_4g_1=pd.DataFrame([])
sec5_4g_1=pd.DataFrame([])
sec6_4g_1=pd.DataFrame([])
PCI_T=pd.DataFrame([])
enodeBid=pd.DataFrame([])
LTE_TAC=pd.DataFrame([])
sec1_3g_1=pd.DataFrame([])
sec2_3g_1=pd.DataFrame([])
sec3_3g_1=pd.DataFrame([])
sec4_3g_1=pd.DataFrame([])
sec5_3g_1=pd.DataFrame([])
sec6_3g_1=pd.DataFrame([])
PSC_T=pd.DataFrame([])
cell_id_2g=pd.DataFrame([])
UMTS_LAC=pd.DataFrame([])
sec1_3g_cellid=pd.DataFrame([])
sec2_3g_cellid=pd.DataFrame([])
sec3_3g_cellid=pd.DataFrame([])
sec4_3g_cellid=pd.DataFrame([])
sec5_3g_cellid=pd.DataFrame([])
sec6_3g_cellid=pd.DataFrame([])
LATITUDE=pd.DataFrame([])
#LATITUDE=pd.DataFrame([])
LONGITUDE=pd.DataFrame([])
cell_name_2g=pd.DataFrame([])
BCCH=pd.DataFrame([])
cell_id_2g=pd.DataFrame([])
GSM_LAC=pd.DataFrame([])
start = datetime.now()
date=start.strftime("%d%m%Y")
Final_site_name="CL"+"_"+sitename+"_OQA IBS SSV Submission_V1"+"_"+date+".xlsx"
######################GSM OSS KPI#################
sitename_gsm=sitename.replace('S',"")
cellname_2g=GSM[GSM["Cell Name"].str.contains(sitename_gsm)]
Hour_wise_2g=pd.DataFrame([])
######################GSM I->O#####################################
sitename_gsm=sitename.replace('S',"")
cellname_2g_1=GSM_HO[GSM_HO["Cell Name"].str.contains(sitename_gsm)]
Hour_wise_2g_HO=pd.DataFrame([])
###################WCDMA OSS KPI####################################
cellname_3g=WCDMA1[WCDMA1["UCell Name"].str.contains(sitename)]
Hour_wise_3g=pd.DataFrame([])
######################WCDMA I->O####################
cellname_3g1=WCDMA_HO[WCDMA_HO["UCell Id"].str.contains(sitename)]
Hour_wise_3g_HO=pd.DataFrame([])
############### LTE OSS KPI#############################
cellname_4g=LTE_KPI.loc[LTE_KPI["EUtranCellFDD"].str.contains(sitename)]
Hour_wise_4g=pd.DataFrame([])
#####################LTE I->O##########################
cellname_4g1=LTE_HO[LTE_HO["EUtranCellFDD"].str.contains(sitename)]
Hour_wise_4g_HO=pd.DataFrame([])
if cellname_4g1.empty==False:
#Hour_wise_4g_1=pd.DataFrame([])
#Hour_count=cellname_4g['Hour'].iloc[0]
First_date=cellname_4g1['Date'].iloc[0]
Date_counter=First_date
Date_wise_data_4g=cellname_4g1.loc[cellname_4g1['Date'] == First_date]
#print(Date_wise_data_4g)
#######################LTE RSSI#########################
cellname_4g2=LTE_RSSI[LTE_RSSI["EUtranCell Id"].str.contains(sitename)]
Hour_wise_4g_RSSI=pd.DataFrame([])
#################WCDMA RSSI###########################
cellname_3g2=WCDMA_RSSI[WCDMA_RSSI["UCell Name"].str.contains(sitename)]
Hour_wise_3g_RSSI=pd.DataFrame([])
############################LTE PEAK#################################
#LTE_peak_mod=LTE_peak.iloc[:,:]
cellname_4g_peak=LTE_peak[LTE_peak["ERBS Id"].str.contains(sitename)]
if cellname_4g_peak.empty==False:
cellname_4g_1=cellname_4g_peak
cellname_4g_123=cellname_4g_1.drop_duplicates(subset="EUtranCell Id",keep='first')
cell_details_4g=cellname_4g_123['EUtranCell Id']
cell_details_4g=cell_details_4g.tolist()
Date_wise_4G_DL_value=pd.DataFrame([])
Date_wise_4G_UL_value=pd.DataFrame([])
First_date=cellname_4g_1['Date'].iloc[0]
last_date=cellname_4g_1['Date'].iloc[-1]
Date_counter1=First_date
for i in range((last_date-First_date).days+1):
Date_wise_4G=cellname_4g_1.loc[cellname_4g_1['Date'] == Date_counter1]
for j in range(len(cell_details_4g)):
Date_wise_4G_DL_1=Date_wise_4G[Date_wise_4G["EUtranCell Id"].str.contains(cell_details_4g[j])]
Date_wise_4G_sort_Peak_DL=Date_wise_4G_DL_1.sort_values(by=["Peak DL Throughput (Mbps)"],ascending=False)
Date_wise_4G_DL=Date_wise_4G_sort_Peak_DL[['Date','EUtranCell Id','Peak DL Throughput (Mbps)']].iloc[0]
Date_wise_4G_DL_value=Date_wise_4G_DL_value.append(Date_wise_4G_DL)
Date_wise_4G_sort_Peak_UL=Date_wise_4G_DL_1.sort_values(by=["Peak UL Throughput (Mbps)"],ascending=False)
Date_wise_4G_UL=Date_wise_4G_sort_Peak_UL[['Date','EUtranCell Id','Peak UL Throughput (Mbps)']].iloc[0]
Date_wise_4G_UL_value=Date_wise_4G_UL_value.append(Date_wise_4G_UL)
Date_counter1=Date_counter1+timedelta(days=1)
LTE=pd.merge(Date_wise_4G_DL_value,Date_wise_4G_UL_value,on=['Date','EUtranCell Id'])
#print(LTE)
#################WCDMA_peak###################################
cellname_3g_12=WCDMA_peak[WCDMA_peak["Ucell Name"].str.contains(sitename,na=False)]
if cellname_3g_12.empty==False:
#cellname_3g_12
cellname_3g_12=cellname_3g_12.replace("#DIV/0",0)
cellname_3g_1=cellname_3g_12.drop_duplicates(subset="Ucell Name",keep='first')
cell_details=cellname_3g_1['Ucell Name']
cell_details=cell_details.tolist()
Date_wise_3G_HS_user_value=pd.DataFrame([])
Date_wise_3G_HS_cell_value=pd.DataFrame([])
Date_wise_3G_EUL_user_value=pd.DataFrame([])
Date_wise_3G_EUL_cell_value=pd.DataFrame([])
Date_wise_3G_UL_value=pd.DataFrame([])
First_date=cellname_3g_12['Date'].iloc[0]
last_date=cellname_3g_12['Date'].iloc[-1]
Date_counter2=First_date
for i in range((last_date-First_date).days+1):
Date_wise_3G=cellname_3g_12.loc[cellname_3g_12['Date'] == Date_counter2]
for j in range(len(cell_details)):
Date_wise_3G_1=Date_wise_3G[Date_wise_3G["Ucell Name"].str.contains(cell_details[j])]
Date_wise_3G_HS_user=Date_wise_3G_1.sort_values(by=["HS User Throughput (kbps)"],ascending=False)
Date_wise_3G_HS_user_1=Date_wise_3G_HS_user[['Date','Ucell Name','HS User Throughput (kbps)']].iloc[0]
Date_wise_3G_HS_user_value=Date_wise_3G_HS_user_value.append(Date_wise_3G_HS_user_1)
Date_wise_3G_HS_user_value_1=Date_wise_3G_HS_user_value[['Date','Ucell Name','HS User Throughput (kbps)']]
Date_wise_3G_HS_cell=Date_wise_3G_1.sort_values(by=["HS Cell Throughput (kbps)"],ascending=False)
Date_wise_3G_HS_cell_1=Date_wise_3G_HS_cell[['Date','Ucell Name','HS Cell Throughput (kbps)']].iloc[0]
Date_wise_3G_HS_cell_value=Date_wise_3G_HS_cell_value.append(Date_wise_3G_HS_cell_1)
Date_wise_3G_HS_cell_value_1=Date_wise_3G_HS_cell_value[['Date','Ucell Name','HS Cell Throughput (kbps)']]
Date_wise_3G_EUL_cell=Date_wise_3G_1.sort_values(by=["EUL Cell Throughput (kbps)"],ascending=False)
Date_wise_3G_EUL_cell_1=Date_wise_3G_EUL_cell[['Date','Ucell Name','EUL Cell Throughput (kbps)']].iloc[0]
Date_wise_3G_EUL_cell_value=Date_wise_3G_EUL_cell_value.append(Date_wise_3G_EUL_cell_1)
Date_wise_3G_EUL_cell_value_1=Date_wise_3G_EUL_cell_value[['Date','Ucell Name','EUL Cell Throughput (kbps)']]
Date_wise_3G_EUL_user=Date_wise_3G_1.sort_values(by=["EUL User Throughput (kbps)"],ascending=False)
Date_wise_3G_EUL_user_1=Date_wise_3G_EUL_user[['Date','Ucell Name','EUL User Throughput (kbps)']].iloc[0]
Date_wise_3G_EUL_user_value=Date_wise_3G_EUL_user_value.append(Date_wise_3G_EUL_user_1)
Date_wise_3G_EUL_user_value_1=Date_wise_3G_EUL_user_value[['Date','Ucell Name','EUL User Throughput (kbps)']]
Date_counter2=Date_counter2+timedelta(days=1)
a=pd.merge(Date_wise_3G_HS_user_value_1,Date_wise_3G_HS_cell_value_1,on=['Date','Ucell Name'])
b=pd.merge(Date_wise_3G_EUL_cell_value_1,Date_wise_3G_EUL_user_value_1,on=['Date','Ucell Name'])
WCDMA=pd.merge(a,b,on=['Date','Ucell Name'])
# print(WCDMA)
###########################CA######################
CA_cell_wise=pd.DataFrame([])
CA_cell=CA[CA["EUtranCell Id"].str.contains(sitename)]
############GSM 0->I###########################
GSM_cell_wise=pd.DataFrame([])
sitename_gsm=sitename.replace('S',"")
GSM_cell=GSM_ngb[GSM_ngb["Adjacent Cell Name"].str.contains(sitename_gsm)]
############SRS###########################
wb=pd.read_excel(INPATH+"SRS_DATABASE.xlsx",sheet_name="Outdoor Site Data")
# wb.columns=wb.iloc[0]
# wb.columns
#wb=wb.reindex(wb.index.drop(0)).reset_index(drop=True)
wb1=wb.iloc[:,1:]
site_name=wb1.loc[wb1['SITE ID']==sitename]
if site_name.empty==False:
#site_name_transposed=site_name_2G.T
LATITUDE=site_name['LATITUDE']
LATITUDE=LATITUDE.drop_duplicates(keep = 'first')
#LATITUDE=LATITUDE.round(3)
LONGITUDE=site_name['LONGITUDE']
LONGITUDE=LONGITUDE.drop_duplicates(keep = 'first')
#LONGITUDE=LONGITUDE.round(3)
#AZIMUTH=site_name['AZIMUTH']
#AZIMUTH=AZIMUTH.drop_duplicates(keep = 'first')
site_name_2G=site_name.loc[site_name["TECHNOLOGY"] == 'GSM']
site_name_transposed=site_name_2G.T
cell_name_2g=pd.DataFrame(site_name_transposed.loc['CELL OR SECTOR ID'])
BCCH=pd.DataFrame(site_name_transposed.loc['FREQUENCY OR FREQUENCY CHANNEL'])
cell_id_2g=pd.DataFrame(site_name_transposed.loc['CELL NUMBER OR CID'])
BCCH=BCCH.T
cell_name_2g=cell_name_2g.T
cell_id_2g=cell_id_2g.T
#LATITUDE=LATITUDE.T
#LONGITUDE=LONGITUDE.T
#AZIMUTH_T=AZIMUTH.T
GSM_LAC=site_name_2G['LAC']
GSM_LAC=GSM_LAC.drop_duplicates(keep = 'first')
site_name_3G=site_name.loc[site_name['TECHNOLOGY'] == 'WCDMA']
UMTS_LAC=site_name_3G['LAC']
UMTS_LAC=UMTS_LAC.drop_duplicates(keep = 'first')
sec1_3g=site_name_3G[site_name_3G['CELL OR SECTOR ID'].str.contains('_01')]
sec2_3g=site_name_3G[site_name_3G['CELL OR SECTOR ID'].str.contains('_02')]
sec3_3g=site_name_3G[site_name_3G['CELL OR SECTOR ID'].str.contains('_03')]
sec4_3g=site_name_3G[site_name_3G['CELL OR SECTOR ID'].str.contains('_04')]
sec5_3g=site_name_3G[site_name_3G['CELL OR SECTOR ID'].str.contains('_05')]
sec6_3g=site_name_3G[site_name_3G['CELL OR SECTOR ID'].str.contains('_06')]
sec1_3g_1=sec1_3g["CELL OR SECTOR ID"]
sec2_3g_1=sec2_3g['CELL OR SECTOR ID']
sec3_3g_1=sec3_3g['CELL OR SECTOR ID']
sec4_3g_1=sec4_3g['CELL OR SECTOR ID']
sec5_3g_1=sec5_3g['CELL OR SECTOR ID']
sec6_3g_1=sec6_3g['CELL OR SECTOR ID']
sec1_3g_cellid=sec1_3g['CELL NUMBER OR CID']
sec2_3g_cellid=sec2_3g['CELL NUMBER OR CID']
sec3_3g_cellid=sec3_3g['CELL NUMBER OR CID']
sec4_3g_cellid=sec4_3g['CELL NUMBER OR CID']
sec5_3g_cellid=sec5_3g['CELL NUMBER OR CID']
sec6_3g_cellid=sec6_3g['CELL NUMBER OR CID']
PSC=site_name_3G['PSC_PCI']
PSC=PSC.drop_duplicates(keep = 'first')
PSC=pd.DataFrame(PSC.astype(int))
PSC_T=PSC.T
site_name_4G=site_name.loc[site_name["TECHNOLOGY"] == 'LTE']
#site_name_4G=Tech_wise_data.loc[Tech_wise_data['SITE ID']==sitename]
#site_name_4G
#list=['_01','_02','_03','_04']
sec1_4g=site_name_4G[site_name_4G['CELL OR SECTOR ID'].str.contains('_01')]
sec2_4g=site_name_4G[site_name_4G['CELL OR SECTOR ID'].str.contains('_02')]
sec3_4g=site_name_4G[site_name_4G['CELL OR SECTOR ID'].str.contains('_03')]
sec4_4g=site_name_4G[site_name_4G['CELL OR SECTOR ID'].str.contains('_04')]
sec5_4g=site_name_4G[site_name_4G['CELL OR SECTOR ID'].str.contains('_05')]
sec6_4g=site_name_4G[site_name_4G['CELL OR SECTOR ID'].str.contains('_06')]
#sec3_4g
LTE_TAC=site_name_4G['LAC']
LTE_TAC=LTE_TAC.drop_duplicates(keep = 'first')
PCI=site_name_4G['PSC_PCI']
PCI=PCI.drop_duplicates(keep = 'first')
PCI=pd.DataFrame(PCI.astype(int))
PCI_T=PCI.T
#PCI_T
enodeBid=pd.DataFrame(site_name_4G['eNodeB ID'])
enodeBid=enodeBid.drop_duplicates(keep = 'first')
enodeBid=enodeBid.astype(int)
enodeBid=enodeBid.T
enodeb=enodeBid.to_string()
#type(enodeBid)
sec1_4g_1=sec1_4g["CELL OR SECTOR ID"]
sec2_4g_1=sec2_4g['CELL OR SECTOR ID']
sec3_4g_1=sec3_4g['CELL OR SECTOR ID']
sec4_4g_1=sec4_4g['CELL OR SECTOR ID']
sec5_4g_1=sec5_4g['CELL OR SECTOR ID']
sec6_4g_1=sec6_4g['CELL OR SECTOR ID']
#LTE_ngb
######################LTE O->I#################################
LTE_cell_wise=pd.DataFrame([])
enodeb=enodeBid.values.tolist()
for enb in enodeb:
for i in enb:
LTE_cell=LTE_ngb[LTE_ngb["EUtranCellRelation"].str.contains("-"+str(i))]
LTE_cell_wise=LTE_cell_wise.append(LTE_cell)
################wcdma O->I#########################################
WCDMA_1=pd.DataFrame([])
wcdma_ngb=pd.concat([wb11,wb21,wb31,wb41])
#wcdma_ngb
wcdma_ngb_1=wcdma_ngb.iloc[1:,1:]
wcdma_ngb_1=wcdma_ngb_1.replace("#DIV/0",0)
wcdma_ngb_1 = wcdma_ngb_1.reset_index(drop=True)
wcdma_ngb_1.columns=wcdma_ngb_1.iloc[0]
wcdma_ngb_1=wcdma_ngb_1.reindex(wcdma_ngb_1.index.drop(0)).reset_index(drop=True)
#wcdma_ngb_1
wcdma_ngb_2=wcdma_ngb_1.fillna(0)
WCDMA_1=pd.DataFrame([])
#First_date=wcdma_ngb_2['Date'].iloc[0]
#Date_wise_data=wcdma_ngb_2.loc[wcdma_ngb_2['Date'] == First_date]
for i in site_name_3G['CELL NUMBER OR CID']:
WCDMA_1a=wcdma_ngb_2[wcdma_ngb_2["UtranRelation"].str.endswith("_"+str(i),na=False)]
WCDMA_1=WCDMA_1.append(WCDMA_1a)
#print(WCDMA_1)
########################################VSWR###################
import shutil
shutil.copy(INPATH+"IBS.xlsx",OUTPATH+"IBS_NEW1.xlsx")
if os.path.isfile(OUTPATH+'IBS_NEW1.xlsx'):
os.rename(OUTPATH+'IBS_NEW1.xlsx',OUTPATH+Final_site_name)
invxrg=[]
invxrg1=[]
with open (INPATH+'Combined file.log', 'rt') as myfile:
myline=myfile.readlines()
for i in range(0,len(myline)):
line = myline[i]
#print(line)
if sitename+"_BB1> invxrg" in line:
for k in range(i,i+300):
stop_line=myline[k]
if sitename+"_BB1> get eutrancellfdd=* cellid" in stop_line:
break
else:
invxrg.append(stop_line)
if sitename+"_BB2> invxrg" in line:
for k in range(i,i+300):
stop_line=myline[k]
if sitename+"_BB2> get eutrancellfdd=* cellid" in stop_line:
break
else:
invxrg1.append(stop_line)
with open(OUTPATH+sitename+"BB1"+".txt", 'a') as site:
for listitem in invxrg:
site.write('%s\n' % listitem)
with open(OUTPATH+sitename+"BB2"+".txt", 'a') as site:
for listitem in invxrg1:
site.write('%s\n' % listitem)
vswr=[]
vswr1=[]
with open (OUTPATH+sitename+"BB1"+".txt", 'rt') as myfile:
myline=myfile.readlines()
for i in range(0,len(myline)):
line = myline[i]
if "Sector/AntennaGroup/Cells" in line:
for k in range(i-2,i+100):
stop_line=myline[k]
#print(stop_line)
if "Tip: use option "in stop_line:
break
else:
vswr.append(stop_line)
with open (OUTPATH+sitename+"BB2"+".txt", 'rt') as myfile:
myline=myfile.readlines()
for i in range(0,len(myline)):
line = myline[i]
if "Sector/AntennaGroup/Cells" in line:
for k in range(i-2,i+100):
stop_line=myline[k]
#print(stop_line)
if "Tip: use option "in stop_line:
break
else:
vswr1.append(stop_line)
a=os.path.getsize(OUTPATH+sitename+"BB1"+".txt")
b=os.path.getsize(OUTPATH+sitename+"BB2"+".txt")
#if os.stat(sitename+"BB1"+".txt").st_size >0 or os.stat(sitename+"BB2"+".txt")>0:
if a>0 or b>0:
import xlwt
if os.path.isfile(OUTPATH+'snap.xls'):
os.remove(OUTPATH+'snap.xls')
wb = xlwt.Workbook()
ws1 = wb.add_sheet('Sheet1')
ws2 = wb.add_sheet('Sheet2')
ws3 = wb.add_sheet('Sheet3')
first_column = 0
# write each item in the list to consecutive columns on the first row
for index, item in enumerate(vswr,1):
ws1.write(index,first_column,item)
for index, item in enumerate(vswr1,1):
ws2.write(index,first_column,item)
#for index, item in enumerate(vswr2,1):
#ws3.write(index,first_column,item)
wb.save(INPATH+'snap.xls')
excel2img.export_img(INPATH+"snap.xls",OUTPATH+"VSWR_BB1.png","","Sheet1!A2:T50")
excel2img.export_img(INPATH+"snap.xls",OUTPATH+"VSWR_BB2.png","","Sheet2!A2:T50")
book=openpyxl.load_workbook(OUTPATH+Final_site_name)
sheet = book.get_sheet_by_name("VSWR")
img1 = openpyxl.drawing.image.Image(OUTPATH+'VSWR_BB1.png')
sheet.add_image(img1,'A2')
img2 = openpyxl.drawing.image.Image(OUTPATH+'VSWR_BB2.png')
sheet.add_image(img2,'A17')
book.save(OUTPATH+Final_site_name)
book=openpyxl.load_workbook(OUTPATH+Final_site_name)
writer=pd.ExcelWriter(OUTPATH+Final_site_name,engine='openpyxl')
writer.book=book
writer.sheets=dict((ws.title,ws) for ws in book.worksheets)
#print(writer.sheets)
#4G
#sitename1.to_excel(writer,"Site Information",startcol=1,startrow=0,header=None,index=False)
CA_cell.to_excel(writer,"CA",startcol=0,startrow=1,header=None,index=False)
LTE.to_excel(writer,"LTE Peak Throughput",startcol=0,startrow=1,header=None,index=False)
WCDMA.to_excel(writer,"WCDMA Peak Throughput",startcol=0,startrow=1,header=None,index=False)
GSM_cell.to_excel(writer,"GSM O->I",startcol=0,startrow=1,header=None,index=False)
LTE_cell_wise.to_excel(writer,"LTE O->I",startcol=0,startrow=1,header=None,index=False)
WCDMA_1.to_excel(writer,"WCDMA O->I",startcol=0,startrow=0,index=False)
cellname_2g.to_excel(writer,"GSM OSS KPIs",startcol=0,startrow=1,header=None,index=False)
cellname_2g_1.to_excel(writer,"GSM I->O",startcol=0,startrow=1,header=None,index=False)
cellname_3g.to_excel(writer,"WCDMA OSS KPIs",startcol=0,startrow=1,header=None,index=False)
cellname_3g1.to_excel(writer,"WCDMA I->O",startcol=0,startrow=1,header=None,index=False)
cellname_4g.to_excel(writer,"LTE OSS KPIs",startcol=0,startrow=1,header=None,index=False)
cellname_4g1.to_excel(writer,"LTE I->O",startcol=0,startrow=1,header=None,index=False)
cellname_4g2.to_excel(writer,"LTE UL RSSI",startcol=0,startrow=1,header=None,index=False)
cellname_3g2.to_excel(writer,"WCDMA UL RSSI",startcol=0,startrow=1,header=None,index=False)
sec1_4g_1.to_excel(writer,"Site Information",startcol=1,startrow=17,header=None,index=False)
sec2_4g_1.to_excel(writer,"Site Information",startcol=2,startrow=17,header=None,index=False)
sec3_4g_1.to_excel(writer,"Site Information",startcol=3,startrow=17,header=None,index=False)
sec4_4g_1.to_excel(writer,"Site Information",startcol=4,startrow=17,header=None,index=False)
sec5_4g_1.to_excel(writer,"Site Information",startcol=5,startrow=17,header=None,index=False)
sec6_4g_1.to_excel(writer,"Site Information",startcol=6,startrow=17,header=None,index=False)
PCI_T.to_excel(writer,"Site Information",startcol=1,startrow=22,header=None,index=False)
enodeBid.to_excel(writer,"Site Information",startcol=1,startrow=21,header=None,index=False)
LTE_TAC.to_excel(writer,"Site Information",startcol=1,startrow=23,header=None,index=False)
#3G
sec1_3g_1.to_excel(writer,"Site Information",startcol=1,startrow=9,header=None,index=False)
sec2_3g_1.to_excel(writer,"Site Information",startcol=2,startrow=9,header=None,index=False)
sec3_3g_1.to_excel(writer,"Site Information",startcol=3,startrow=9,header=None,index=False)
sec4_3g_1.to_excel(writer,"Site Information",startcol=4,startrow=9,header=None,index=False)
sec5_3g_1.to_excel(writer,"Site Information",startcol=5,startrow=9,header=None,index=False)
sec6_3g_1.to_excel(writer,"Site Information",startcol=6,startrow=9,header=None,index=False)
PSC_T.to_excel(writer,"Site Information",startcol=1,startrow=13,header=None,index=False)
cell_id_2g.to_excel(writer,"Site Information",startcol=1,startrow=7,header=None,index=False)
UMTS_LAC.to_excel(writer,"Site Information",startcol=1,startrow=16,header=None,index=False)
sec1_3g_cellid.to_excel(writer,"Site Information",startcol=1,startrow=14,header=None,index=False)
sec2_3g_cellid.to_excel(writer,"Site Information",startcol=2,startrow=14,header=None,index=False)
sec3_3g_cellid.to_excel(writer,"Site Information",startcol=3,startrow=14,header=None,index=False)
sec4_3g_cellid.to_excel(writer,"Site Information",startcol=4,startrow=14,header=None,index=False)
sec5_3g_cellid.to_excel(writer,"Site Information",startcol=5,startrow=14,header=None,index=False)
sec6_3g_cellid.to_excel(writer,"Site Information",startcol=6,startrow=14,header=None,index=False)
#2G
LATITUDE.to_excel(writer,"Site Information",startcol=1,startrow=4,header=None,index=False)
LATITUDE.to_excel(writer,"Site Information",startcol=2,startrow=4,header=None,index=False)
LATITUDE.to_excel(writer,"Site Information",startcol=3,startrow=4,header=None,index=False)
LATITUDE.to_excel(writer,"Site Information",startcol=4,startrow=4,header=None,index=False)
LATITUDE.to_excel(writer,"Site Information",startcol=5,startrow=4,header=None,index=False)
LATITUDE.to_excel(writer,"Site Information",startcol=6,startrow=4,header=None,index=False)
LONGITUDE.to_excel(writer,"Site Information",startcol=1,startrow=3,header=None,index=False)
LONGITUDE.to_excel(writer,"Site Information",startcol=2,startrow=3,header=None,index=False)
LONGITUDE.to_excel(writer,"Site Information",startcol=3,startrow=3,header=None,index=False)
LONGITUDE.to_excel(writer,"Site Information",startcol=4,startrow=3,header=None,index=False)
LONGITUDE.to_excel(writer,"Site Information",startcol=5,startrow=3,header=None,index=False)
LONGITUDE.to_excel(writer,"Site Information",startcol=6,startrow=3,header=None,index=False)
cell_name_2g.to_excel(writer,"Site Information",startcol=1,startrow=5,header=None,index=False)
BCCH.to_excel(writer,"Site Information",startcol=1,startrow=6,header=None,index=False)
cell_id_2g.to_excel(writer,"Site Information",startcol=1,startrow=7,header=None,index=False)
GSM_LAC.to_excel(writer,"Site Information",startcol=1,startrow=8,header=None,index=False)
writer.save()
print("One Site completed..Next started...Enjoy!!!!!")
end1_time=time.time()
print("Total time to complete one site",end1_time-start1_time)
def get_chart_image(fn_excel: str, fn_image: str, sheet_name: str, cells_range: str):
excel2img.export_img(ROOT / fn_excel, fn_image, "", f"{sheet_name}!{cells_range}")
if (item == "woaj"):
worksheet.write(row, col, "", first_format)
worksheet.write(row, col + 1, item, first_format)
worksheet.write(row, col + 2, a0, first_format)
worksheet.write(row, col + 3, a1, first_format)
worksheet.write(row, col + 4, a2, first_format)
worksheet.write(row, col + 5, mean, first_format)
else:
worksheet.write(row, col, int(ranking), formato_verde)
worksheet.write(row, col + 1, item)
worksheet.write(row, col + 2, a0, finalformats[0])
worksheet.write(row, col + 3, a1, finalformats[1])
worksheet.write(row, col + 4, a2, finalformats[2])
worksheet.write(row, col + 5, mean, finalformats[3])
row += 1
#Change column's widths
worksheet.set_column(0, 0, 2)
worksheet.set_column(1, 1, 20)
worksheet.set_column(2, 4, 4)
worksheet.set_column(5, 5, 5)
#Done with this file!
workbook.close()
#Say cheese! Taking screenshot of XLSX range
excel2img.export_img("FMCranks.xlsx", "FMCranks.png", "",
"Sheet1!A2:F{}".format(row))
#Finished!
print(str(time.time() - start) + " seconds elapsed.\nEnjoy ;D")
def capture_matrix():
excel2img.export_img(matrix_sheet_xlsx, matrix_picture_file, "",
"Matrix!A1:G8")
def excel_screenshot(sourceFileLoc, imgFileLoc):
excel2img.export_img(sourceFileLoc, imgFileLoc, None, None)
formato_celula)
worksheet.write(row + 2, col + 1, acao, formato_acao)
worksheet.write(row + 2, col + 2, df_aux['Responsável'].values[i],
formato_celula)
worksheet.write(
row + 2, col + 3,
str(df_aux['Previsão'].values[i]) if
(df_aux['Previsão'].values[i] == np.nan) else "", formato_celula)
worksheet.write(row + 2, col + 4, df_aux['Status'].values[i],
formato_check)
worksheet.write(
row + 2, col + 5,
str(df_aux['Comentário'].values[i]) if
(df_aux['Comentário'].values[i] == np.nan) else "", formato_celula)
worksheet.set_row(row + 2, None, None, {'level': 1})
#worksheet.set_row(row + 2, None, None, {'level':2})
row += 1
i += 1
if i == len(df_aux):
i = 0
#print('inicio', row - count)
#print('final', row + len(acoes) - 1 - count)
pulo = 2
row += pulo
count += 1
workbook.close()
excel2img.export_img("Teste.xlsx", "test.png", "Sheet1", None)
return False, ''
def get_exam_schedule(self, codes):
exams = [self.exams[code] for code in codes]
exams.sort(key=lambda e: e[4])
exams.sort(key=lambda e: self.get_date_for_sort(e[1]))
return exams
def get_date_for_sort(self, date_string):
day, month, year = date_string.split("-")
return year + month + day
exam = Exam(pages)
results = exam.get_exam_schedule(requested_courses)
[print(ex) for ex in results]
wb = openpyxl.load_workbook("temp1.xlsx")
ws = wb.active
for index, row in enumerate(results):
row_num = str(index + 4)
ws['B' + row_num] = row[0]
ws['C' + row_num] = row[1]
ws['D' + row_num] = row[2] + " " + row[3]
ws['E' + row_num] = row[4]
ws['F' + row_num] = row[5]
wb.save('out.xlsx')
excel2img.export_img("out.xlsx", "out.png", "Sheet1", None)
import excel2img
import openpyxl
signal = '采购业务员'
def last_row(wb, vendor: str):
ws = wb['订单 ' + vendor]
for cell in ws['A']:
if cell.value is not None and isinstance(cell.value, str) and signal in cell.value:
return cell.row - 1
filename = "bay.xlsx"
wb = openpyxl.load_workbook(filename)
vendors = [name.split(' ')[1] for name in wb.sheetnames if name.split(' ')[0] == '订单']
for vendor in vendors:
print(vendor)
row = last_row(wb, vendor)
sheet = "'订单 " + vendor + "'!A2:M" + str(row)
excel2img.export_img(filename, vendor + ".png", "", sheet)
def convert_file_for_pptx(out_filename, template_file, ds_dict):
'''按模板转换xlsx文件
按字典转换模板文件，输出为out_filename
'''
unzip_path = os.path.join(out_filename + 't\\pptx_tmp')
if (os.path.exists(unzip_path)):
shutil.rmtree(unzip_path)
unzip_single(template_file, unzip_path)
embeddings_path = os.path.join(unzip_path, "ppt\\embeddings")
tmp_pd_dict = {}
tmp_excel_active_sheet_dict = {}
if (os.path.exists(embeddings_path)):
for x in os.listdir(embeddings_path):
if x.endswith('.xlsx'):
active_name = convert_file_for_xlsx(
os.path.join(embeddings_path, x),
os.path.join(embeddings_path, x),
ds_dict,
outImage=False)
tmp_excel_active_sheet_dict[x] = active_name
tmp_pd_dict[x] = pd.read_excel(os.path.join(
embeddings_path, x))
xlsx_emf_arr = []
root_path = os.path.join(unzip_path, "ppt")
for slide in os.listdir(f"{root_path}\slides"):
if slide.endswith(".xml") == False:
continue
doc = lxml.etree.XML(
open(f"{root_path}\\slides\\{slide}", 'rb').read())
id_embed_dict = {}
for one_oleObj in doc.xpath("//p:oleObj", namespaces=doc.nsmap):
for one_blip in one_oleObj.xpath(".//a:blip",
namespaces=doc.nsmap):
id = one_oleObj.attrib.get('{' + doc.nsmap['r'] + '}id')
embed = one_blip.attrib.get('{' + doc.nsmap['r'] + '}embed')
id_embed_dict[id] = embed
if len(id_embed_dict) > 0:
rels = lxml.etree.XML(
open(f"{root_path}\\slides\_rels\\{slide}.rels", 'rb').read())
for id, embed in id_embed_dict.items():
xlsx = rels.xpath(
f"//*[local-name() = 'Relationship'][@Id='{id}'] "
)[0].attrib['Target']
emf = rels.xpath(
f"//*[local-name() = 'Relationship'][@Id='{embed}'] "
)[0].attrib['Target']
xlsx_emf_arr.append({"xlsx": xlsx, "emf": emf, "slide": slide})
for one in xlsx_emf_arr:
png_file = os.path.realpath(root_path + "/slides/" + one['xlsx'] +
"1.png")
emf_file = os.path.realpath(root_path + "/slides/" + one['emf'])
excel2img.export_img(
root_path + "/slides/" + one['xlsx'], png_file,
tmp_excel_active_sheet_dict[one['xlsx'].split("/")[-1]])
my_cmd = f'convert "{png_file}" "{emf_file}"'
cmd_output = os.popen(my_cmd).readlines()
os.remove(png_file)
zipDir(unzip_path, out_filename)
shutil.rmtree(out_filename + "t")
env = get_jinja2_Environment()
ppt_file = Presentation(out_filename)
'''
#expr
title_lines=1
loop_var=index,row
dataset=a.sort_values(zhibiao,ascending=False)[:size]
'''
def calc_frame_txt(obj, calc_dict, calc_kind=None):
if calc_kind is None:
calc_kind = 1 if len(obj.text_frame.paragraphs) < 1 else 3
if calc_kind == 3: #text_frame 中有多个不同格式的文本，需要查runs,通常不应该是这样的
for paragraph in obj.text_frame.paragraphs:
exp_list = []
if paragraph.text.find('{{') > -1:
start, end, s_num, e_num = -1, -1, 0, 0
for idx, run in enumerate(paragraph.runs):
if run.text.find('{{') > -1:
s_num += 1
if s_num == 1:
start = idx
if run.text.find('}}') > -1:
end = idx
e_num += 1
if start >= 0 and end >= 0 and s_num == e_num:
exp_list.append((start, end))
start, end, s_num, e_num = -1, -1, 0, 0
for start, end in exp_list:
if start >= 0 and end >= 0 and start <= end:
text = ''.join([
x.text for x in paragraph.runs[start:end + 1]
])
try:
result = exec_template(env, text, calc_dict)
except Exception as e:
raise RuntimeError(text)
paragraph.runs[start].text = result
for x in paragraph.runs[start + 1:end + 1]:
x.text = ''
elif calc_kind == 2:
for paragraph in obj.text_frame.paragraphs:
if paragraph.text.find('{{') > -1:
try:
result = exec_template(env, paragraph.text, calc_dict)
except:
raise RuntimeError(paragraph.text)
for run in paragraph.runs:
run.text = '' #直接copy font 报错，我们通过将其他runs中的文字清空，计算出的新文字赋值给第一个run。这样就保留了格式
paragraph.runs[0].text = result
else:
expr = obj.text_frame.text
if expr.find('{{') > -1:
try:
result = exec_template(env, expr,
calc_dict) # env.from_string(expr)
except:
raise RuntimeError(paragraph.text)
for paragraph in obj.text_frame.paragraphs:
for run in paragraph.runs:
run.text = '' #直接copy font 报错，我们通过将其他runs中的文字清空，计算出的新文字赋值给第一个run。这样就保留了格式
obj.text_frame.paragraphs[0].runs[0].text = result
def handle_all_shapes(shapes, real_dict, tmp_pd_dict):
# real_dict 我们使用这个参数来层层传递外面定义变量
#tmp_pd_dict 是专为内嵌excel准备的，貌似递归取不到外层定义的变量
for shape in shapes: #shape.part.related_parts['rId4'].blob
if hasattr(shape, "shapes"):
handle_all_shapes(shape.shapes, real_dict, tmp_pd_dict)
continue
if shape.has_text_frame or shape.has_table:
pass
if shape.shape_type == MSO_SHAPE_TYPE.EMBEDDED_OLE_OBJECT:
pass
if shape.has_text_frame:
calc_frame_txt(shape, real_dict)
elif shape.has_chart:
key = shape.chart._workbook.xlsx_part.partname.split("/")[-1]
# 定义图表数据 ---------------------
chart_data = ChartData()
columns = list(tmp_pd_dict[key].columns.values)
chart_data.categories = tmp_pd_dict[key][columns[0]]
for one in columns[1:]:
chart_data.add_series(one, tuple(tmp_pd_dict[key][one]))
shape.chart.replace_data(chart_data)
elif shape.has_table:
current_row = 0
for row in shape.table.rows:
current_col = 0
for cell in row.cells:
if cell.text_frame.text.find('{{') < 0:
current_col = current_col + 1
continue
try:
result = exec_template(env, cell.text_frame.text,
real_dict)
except:
raise RuntimeError(cell.text_frame.text)
for paragraph in cell.text_frame.paragraphs:
for run in paragraph.runs:
run.text = '' #直接copy font 报错，我们通过将其他runs中的文字清空，计算出的新文字赋值给第一个run。这样就保留了格式
copy_row = current_row
result_lines = result.split('\n')
for one_line in result_lines: #展开模板计算结果
copy_col = current_col
#从当前位置开始，复制结果到ppt的table中
for one in one_line.split():
cur_row_cells = shape.table.rows[
copy_row].cells
if copy_col >= len(
cur_row_cells
): #如果ppt table 中的列不够用，当前行的复制就结束
break
p_cell = cur_row_cells[copy_col]
if len(p_cell.text_frame.paragraphs[0].runs
) == 0:
p_cell.text_frame.paragraphs[0].add_run()
p_cell.text_frame.paragraphs[0].runs[
0].text = one
copy_col = copy_col + 1
copy_row = copy_row + 1
if copy_row >= len(shape.table.rows): #行不够就结束复制
break
current_col = current_col + 1
current_row = current_row + 1
if current_row >= len(shape.table.rows):
break
try:
real_dict = ds_dict.copy()
for slide in ppt_file.slides:
if slide.has_notes_slide: #抽取备注栏里面的变量定义，后页会覆盖前页
notes_text = slide.notes_slide.notes_text_frame.text
for one_line in notes_text.split("\n"):
var_expr = one_line.split("=")
if len(var_expr) < 2:
continue
try:
if var_expr[1].strip().startswith("{{"):
result_lines = exec_template(
env, var_expr[1], real_dict)
else:
result_lines = exec_template(
env, "{{" + var_expr[1] + "}}", real_dict)
real_dict = real_dict.copy()
real_dict[var_expr[0].strip()] = result_lines
except Exception as e:
raise RuntimeError("\n备注说明中的公式不正确：" + one_line)
handle_all_shapes(slide.shapes, real_dict, tmp_pd_dict)
ppt_file.save(out_filename)
finally:
if ppt_file is not None:
ppt_file.save(out_filename)
del ppt_file
ppt2png(out_filename, ds_dict.get("_idx_", ''))
import excel2img
if __name__== "__main__":
excel2img.export_img("Sample Floor Map.xlsx", "map.png", "", "temp!A1:W37")
def make_xlsx_screenshot(wb_name, ws_name, st_name):
if not os.path.exists('Скриншоты'):
os.mkdir('Скриншоты')
screenshot_name = f'{ROOT_DIR}/Скриншоты/{st_name}.png'
excel2img.export_img(f"{wb_name}.xlsx", screenshot_name, ws_name, None)
def cut_image_xlsx(fn_excel, fn_image, page=None, _range=None):
excel2img.export_img(fn_excel, fn_image, page=None, _range=None)
