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](%s)](%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)