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[0]).replace("=", "")
dummy2 = str(dummy2).replace("'", "")
if found == 0:
dummy3 = str(dummy[1])
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...