excel - Make the below program Run faster -


i have program below. takes excel files folder, fetches data cell locations of file , puts in other larger file. thing program running, taking time complete. need make faster. ideas welcome, thankyou in advance. if point me directions or provide line of codes used alternative make faster much appreciated. thankyou. 

import openpyxl import xlrd import xlwt import datetime import os xlrd import open_workbook xlwt import workbook xlutils.copy import copy import win32com.client win32 import ctypes.wintypes os import path  processed_file_path=r'd:\pcn_automation\processed_pcns' dir_path='d:\pcn_automation' dir_output_path='d:\pcn_automation'  wb_openpyxl=openpyxl.load_workbook(os.path.join(dir_output_path,'concat_pcn.xlsx')) #wb_openpyxl=openpyxl.load_workbook(os.path.join(dir_output_path,'concat_pcn_copy.xlsx')) sheet_openpyxl=wb_openpyxl.active  last_row_index_where_inserted=0 #this indicates index too. automatically inserted in next index next value stores  def xldate_to_datetime(xldate):    tempdate = datetime.datetime(1900, 1, 1)    deltadays = datetime.timedelta(days=int(xldate)-2)    thetime = (tempdate + deltadays )    return thetime.strftime("%d/%m/%y")  def checkbox_values(path):     csidl_personal = 5     check_box=''     shgfp_type_current = 0     buf = ctypes.create_unicode_buffer(ctypes.wintypes.max_path)     ctypes.windll.shell32.shgetfolderpathw(none, csidl_personal, \                                            none, shgfp_type_current, buf)     excel = win32.dispatchex("excel.application")     wb = excel.workbooks.open(filename=path, readonly=true);     ws = wb.worksheets("pcn form")     cb in ws.checkboxes():         if cb.value == 1:             if cb.name == 'check box 12':                 check_box = check_box + 'msc' + '; '             elif cb.name == 'check box 13':                 check_box = check_box + 'isc' + '; '             elif cb.name == 'check box 14':                 check_box = check_box + 'dsc' + '; '             else:                 check_box = check_box + 'eaa' + '; '     return check_box  def getnumrecords_resourceinformation(path):     wb_pyxl=openpyxl.load_workbook(path,data_only=true);     active_sheet=wb_pyxl.active     mid_block_ending_rownum=sheet.nrows-19;     i=0;     rowofcellobjects in active_sheet['b19':('x'+str(mid_block_ending_rownum))]:         if rowofcellobjects[0].value none:             break;         i=i+1;     mid_block_ending_rownum=19+i-1;     return mid_block_ending_rownum  def putpcn_inconcatpcn(sheet,filename,file_path,last_row_index):     global sheet_openpyxl     global last_row_index_where_inserted     data2=[sheet.cell_value(col,2) col in range(4,15)]; #it holds project information details     data3=[sheet.cell_value(4,11)]; #it holds additional comments      print("-------processing file "+str(filename)+" -------")     row_num in range(18,getnumrecords_resourceinformation(file_path)):         print("-----inserting "+str(row_num - 18 + 1)+" record in pcn_concat file---------")         print("     ---inserting file name---")         sheet_openpyxl.cell(row=(row_num - 18 + 1 + last_row_index)+1,column=(0)+1).value=str(filename);         print("     ---inserting project information details---")         index2, value2 in enumerate(data2):             if index2 == 2 , value2 != '':                 value2 = xldate_to_datetime(value2);             if index2==7:                 value2=checkbox_values(file_path)             sheet_openpyxl.cell(row=(row_num-18 + 1 + last_row_index)+1,column=(index2+1)+1).value=str(value2);         print("     ---inserting additional comments---")         index3, value3 in enumerate(data3):             sheet_openpyxl.cell(row=(row_num - 18 + 1 + last_row_index)+1,column=(index2 + index3+ 2)+1).value=str(value3);         data = [sheet.cell_value(row_num, col) col in range(1, 24)];  # holds resource information         print("     ---inserting resource information---")         index,value4 in enumerate(data):             if (index==9 or index==10) , value4!='':                 value4=xldate_to_datetime(value4);             sheet_openpyxl.cell(row=(row_num-18 + 1 + last_row_index)+1,column=(index+index2+index3+3)+1).value=str(value4);      last_row_index_where_inserted=last_row_index_where_inserted+(row_num-18+1)     return  def moveprocessed_pcns(file_path,xlsfile,sheet2,num_rows):     num_rows_source=getnumrecords_resourceinformation(file_path)     num_rows_source=num_rows_source-19+1     count = 0;     in range(1, num_rows):         if sheet2.cell(i, 0).value == xlsfile:             if sheet2.cell(i,36).value=='processed':                 count = count + 1     if count==num_rows_source:         print("\n-------all records of file " + str(xlsfile) + " marked processed-------")         print("\n     -----moving file " + str(xlsfile) + " processed_pcns folder-----")         os.rename(file_path, path.join(processed_file_path, xlsfile))         print("\n     -----file " + str(xlsfile) + " moved processed_pcns folder-----")     else:         print("\n-------some records file " + str(xlsfile) + " not marked processed-----")         print("\n     -----not moving file " + str(xlsfile) + " processed_pcns folder-----")     return  def movedataofpcn(concatpcns,movepcns_toprocessed):     global sheet     global output_sheet     global workbook     global sheet_openpyxl     global wb_openpyxl     if concatpcns==1:         dir_list = os.listdir(dir_path)         in range(len(dir_list)):             xlsfile = dir_list[i]             if (xlsfile.endswith('.xlsm') or xlsfile.endswith('.xlsx')) , (xlsfile != 'concat_pcn.xlsx'):             #if (xlsfile.endswith('.xlsm') or xlsfile.endswith('.xlsx')) , (xlsfile != 'concat_pcn_copy.xlsx'):                 file_path = path.join(dir_path, xlsfile)                 workbook = xlrd.open_workbook(file_path);                 sheet = workbook.sheet_by_index(0);                 putpcn_inconcatpcn(sheet, xlsfile, file_path, last_row_index_where_inserted)          print("\n--------all files processed--------")         print("\n---saving pcn_concat file---")          wb_openpyxl.save(os.path.join(dir_output_path,'concat_pcn.xlsx'))         #wb_openpyxl.save(os.path.join(dir_output_path,'concat_pcn_copy.xlsx'))           print("\n---pcn_concat file saved---")      if movepcns_toprocessed==1:         startmoving_processed_pcns()     return  def startmoving_processed_pcns():     global sheet     global workbook     if not os.path.exists(processed_file_path):         os.makedirs(processed_file_path)     print("\n");     print("---------moving of processed pcn files started-------")     dir_list = os.listdir(dir_path)     #workbook2 = xlrd.open_workbook(os.path.join(dir_output_path, 'concat_pcn_copy.xlsx'))     workbook2 = xlrd.open_workbook(os.path.join(dir_output_path, 'concat_pcn.xlsx'))     sheet2 = workbook2.sheet_by_index(0)     num_rows = sheet2.nrows     in range(len(dir_list)):         xlsfile = dir_list[i]         #if (xlsfile.endswith('.xlsm') or xlsfile.endswith('.xlsx')) , (xlsfile!='concat_pcn_copy.xlsx'):         if (xlsfile.endswith('.xlsm') or xlsfile.endswith('.xlsx')) , (xlsfile != 'concat_pcn.xlsx'):             file_path = path.join(dir_path, xlsfile)             workbook = xlrd.open_workbook(file_path);             sheet = workbook.sheet_by_index(0);             moveprocessed_pcns(file_path, xlsfile,sheet2,num_rows)     return   movedataofpcn(1,0)


Comments

Post a Comment

Popular posts from this blog

Is there a better way to structure post methods in Class Based Views -

i'm using class based views in django , never specify url in ajax calls , omit action parameter in form because know these requests go through post method of class based view. notice view - profile page make many requests depending on user updating on page - post method become convoluted , if/else mess example: def post(self, request, *args, **kwargs): # handle user changing available date if request.post.get('availabledate') != none: self.updateavailabledate(request) return jsonresponse({'result':'success'}) if request.post.get('newprojectname') != none: creative_user = creativeuserprofile.objects.get(id = request.user.id) project = project.create_project(creative_user, request.post['newprojectname']) project.save() return jsonresponse({'projectid': project.id }) if request.post.get('projectimage') != none , request.files['file']: proj...
Read more

performance - Why is XCHG reg, reg a 3 micro-op instruction on modern Intel architectures? -

i'm doing micro-optimization on performance critical part of code , came across sequence of instructions (in at&t syntax): add %rax, %rbx mov %rdx, %rax mov %rbx, %rdx i thought had use case xchg allow me shave instruction , write: add %rbx, %rax xchg %rax, %rdx however, dimay found agner fog's instruction tables , xchg 3 micro-op instruction 2 cycle latency on sandy bridge, ivy bridge, broadwell, haswell , skylake. 3 whole micro-ops , 2 cycles of latency! 3 micro-ops throws off 4-1-1-1 cadence , 2 cycle latency makes worse original in best case since last 2 instructions in original might execute in parallel. now... cpu might breaking instruction micro-ops equivalent to: mov %rax, %tmp mov %rdx, %rax mov %tmp, %rdx where tmp anonymous internal register , suppose last 2 micro-ops run in parallel latency 2 cycles. given register renaming occurs on these micro-architectures, though, doesn't make sense me done way. why wouldn't register renamer ...
Read more

c# - Asp.net web api : redirect unauthorized requst to forbidden page -

im trying redirect unauthorized request forbidden page instead i'm getting forbidden page in response body , how can fix ? here's startup class : app.createperowincontext(storecontext.create); app.createperowincontext<applicationusermanager>(applicationusermanager.create); app.createperowincontext<applicationsigninmanager>(applicationsigninmanager.create); app.usecookieauthentication(new cookieauthenticationoptions { authenticationtype = defaultauthenticationtypes.applicationcookie, expiretimespan = timespan.fromdays(30), }); app.useexternalsignincookie(defaultauthenticationtypes.externalcookie); app.usetwofactorsignincookie(defaultauthenticationtypes.twofactorcookie, timespan.fromminutes(5)); app.usetwofactorrememberbrowsercookie(defaultauthenticationtypes.twofactorrememberbrowsercookie); the method im trying reach : [httpget] [authorize(roles = "admin")] public string getcurrentusername() { return userman...
Read more