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

jquery - Responsive Navbar with Sub Navbar -

good day, forgive me nav vocabulary not best. if question confusing apologize. i'm working on navigation bar needs centered on page @ 768px , above. items in nav open (or perhaps toggle) subnav lives directly below. these nav should appear centered on page. i have been able work through responsive portion of nav bar, having main items align vertically , sub nav menu of each item roll out below, , gave them indent separation. working well. however, issue not responsive side, behavior @ width greater 768px. happens toggling menu's sub navigation pushes other main items down page below sub nav. here's code: $(document).ready(function () { //stick in fixed 100% height behind navbar don't wrap $('#slide-nav.navbar-inverse').after($('<div class="inverse" id="navbar-height-col"></div>')); $('#slide-nav.navbar-default').after($('<div id="navbar-height-col"></d
Read more