python给excel添加超链接_使用Python将超链接插入到Excel中的本地文件夹

__author__='Paul'importosimportopenpyxlfromopenpyxlimportload_workbookimportxlsxwritersite_info_root='C:\\Users\\paul.EPSCONSTRUCTION\\PycharmProjects\\Excel_Jobs\\Site Information\\'# This function returns the last row on eps.xlsm to be populateddefget_last_row(cell_ref='P1'):#P1 contains the count of the used rowsglobalwbglobalwswb=load_workbook("eps.xlsm",data_only=True)#Workbookws=wb["jobnoeps"]#Worksheetlast_row=ws.cell(cell_ref).value#Value of P1 from that worksheetreturnlast_row# This function will read the job number in format EPS-XXXX-YRdefread_last_row_jobno(last_row_position):last_row_data=[]forcolsinrange(1,5):last_row_data+=str(ws.cell(column=cols,row=last_row_position).value)last_row_data_all="".join(last_row_data)returnlast_row_data_all#This function will return the Customerdefread_last_row_cust(last_row_position):cols=5customer_name=str(ws.cell(column=cols,row=last_row_position).value)returncustomer_name#This function will return the Sitedefread_last_row_site(last_row_position):cols=6site_name=str(ws.cell(column=cols,row=last_row_position).value)returnsite_name#This function will return the Job Discriptiondefread_last_row_disc(last_row_position):cols=7site_disc=str(ws.cell(column=cols,row=last_row_position).value)returnsite_disclast_row=get_last_row()job_no_details=read_last_row_jobno(last_row)job_customer=read_last_row_cust(last_row)job_site=read_last_row_site(last_row)job_disc=read_last_row_disc(last_row)cust_folder=job_customerjob_dir=job_no_details+"\\"+job_site+" - "+job_disc#Insert Hyperlink to folderdeffolder_hyperlink(last_row_position,destination):cols='B'rows=str(last_row_position)position=cols+rowsfinal_position="".join(position)printfinal_position# This is just to check the value# The statement below should insert hyperlink in eps.xlsm > worksheet jobnoeps at column B and last completed row.ws.cell(final_position).hyperlink=destinationfolder_location=site_info_root+job_customer+"\\"+job_dirprintfolder_location# This is just to check the valuefolder_hyperlink(last_row,folder_location)