Leap years don't really cause a problem -- except for 1900.
These errors are due to the use of monthly averages where the differing number of days in each month are not accounted for.
# # excel_info.py # Find worksheets, defined names and CheckBox1 value in Excel WorkBooks. # Loop thru directory and write each WorkBook's name, Last Modification Date, # CheckBox1 value (to see if uncertainties are calculated), Total # of # WorkSheets, the names of all Worksheets, Total # of Defined Names,and a # list of all Defined Names into CSV # # In the CSV file: # Column 1 = WorkBook name # Column 2 = Last Modified Date # Column 3 = CheckBox1 value # Column 4 = Total # of WorkSheets in WorkBook # Column 5 = All Worksheet names # Column 6 = Total # of Defined Names in WorkBook # Column 7 = All WorkBook Defined Names #
import win32com.client as win32 import os import glob import csv import datetime from collections import Counter wb_def_names = Counter() ws_names = Counter() os.chdir('C:\origin') xlfiles = sorted(glob.glob("*.xls")) outputFile = open('output.csv', 'w', newline='') outputWriter = csv.writer(outputFile) xlApp = win32.gencache.EnsureDispatch('Excel.Application') xlApp.EnableEvents = False #xlApp.ScreenUpdating = False cwd = os.getcwd() this_file = 0 for xlfile in xlfiles: showunc_val = 0 unccalc_val = bool(False) showunc = 0 found = 0 checkbox1_caption = "None" this_file = this_file + 1 if this_file%100 == 0: print("Processing ",this_file) if this_file >= 1: open_ok = 1 try: xlApp.DisplayAlerts = False xlwb = xlApp.Workbooks.Open(cwd+"\\"+ xlfile) except: open_ok = -1 print("error in open xlfile") try: xlwb_name = xlApp.ActiveWorkbook.Name except: open_ok = -1 print("error in open ActiveWorkbook.Name") if open_ok: cntr = 1 try: for n in xlApp.ActiveWorkbook.Names: wb_def_names[(n.Name)] += cntr found = (str.find(n.Name, "showunc")) found2 = (str.find(n.Name, "unccalc")) if found == 0 or found2 == 0: this_n = str(n) dummy = this_n.split("!") dummy2 = str(dummy).replace("=", "") dummy2 = str(dummy2).replace("'", "") if found == 0: dummy3 = str(dummy) showunc_val = xlApp.Sheets(dummy2).Range(dummy3).Value if found2 == 0: checkbox1_caption = xlApp.Sheets(dummy2).OLEObjects("CheckBox1").Object.Caption unccalc_val = xlApp.Sheets(dummy2).OLEObjects("CheckBox1").Object.Value cntr = cntr + 1 list_wb_def_names = [v for v in wb_def_names.keys()] except: open_ok = -1 print("error in ActiveWorkbook.Names",n,n.Name,cntr) cntr2 = 1 try: for s in xlApp.ActiveWorkbook.Sheets: ws_names[(s.Name)] +=cntr2 cntr2 = cntr2 + 1 list_ws_names = [v for v in ws_names.keys()] except: open_ok = -1 try: xlApp.ActiveWorkbook.Saved = True xlApp.ActiveWorkbook.Close() except: open_ok = -1 print("error in closing workbook") try: last_modified_date = datetime.datetime.fromtimestamp(os.path.getctime(cwd+"\\"+ xlfile)) except OSError: last_modified_date = 0 last_modified_date = last_modified_date.strftime("%d-%m-%Y") try: outputWriter.writerow([xlwb_name, last_modified_date,unccalc_val,cntr2-1,list_ws_names,cntr-1,list_wb_def_names]) except: print("error writing to outputfile") print("Workbook = ", xlwb_name) wb_def_names.clear() ws_names.clear() checkbox1_caption = "None" else: print("Unable to process ",xlfile) else: dummy5 = 0 #assert isinstance(xlApp.Application, object) #xlApp.Application.Quit() outputFile.close()
Since 2003, Python has consistently ranked in the top ten most popular programming languages as measured by the TIOBE Programming Community Index. As of March 2017, it is the fifth most popular language. It was ranked as Programming Language of the Year for the year 2007 and 2010. It is the third most popular language whose grammatical syntax is not predominantly based on C...