| 1 |
"""
|
| 2 |
|
| 3 |
EndoFieldsFlaggedSummaryReport
|
| 4 |
|
| 5 |
Created by:
|
| 6 |
Emanuel Borges
|
| 7 |
08.18.2021
|
| 8 |
|
| 9 |
This program will take the results from the marked-up table-field report
|
| 10 |
and export a summary of how many fields are selected as which flag per table.
|
| 11 |
|
| 12 |
"""
|
| 13 |
|
| 14 |
import os
|
| 15 |
from datetime import datetime
|
| 16 |
from win32com.client import Dispatch
|
| 17 |
|
| 18 |
|
| 19 |
|
| 20 |
if __name__ == '__main__':
|
| 21 |
|
| 22 |
inputSpreadsheetName = r"C:\Users\eborges\Documents\Cases\Endo\20210729_SIF_Backup_TableFieldReport (A&P Highlights - 8.11.2021)-EB_v2.xlsx"
|
| 23 |
outputFileName = r'C:\Users\eborges\Documents\Cases\Endo\Temp\DatabaseReports\MarkUpResults.csv'
|
| 24 |
|
| 25 |
colorCodeMatrix = {'50':['Green','David Fauvre recommended producing on 7/14'],
|
| 26 |
'33':['Blue','A&P recommends producing'],
|
| 27 |
'6':['Yellow','Requires further discussion as to relevancy'],
|
| 28 |
'44':['Orange','Unclear what field name represents, or field requires technical explanation to understand'],
|
| 29 |
'3':['Red','A&P preliminarily recommends not producing'],
|
| 30 |
'-4142':['No Color','Nothing yet selected']}
|
| 31 |
|
| 32 |
xlApp = Dispatch('Excel.Application')
|
| 33 |
xlBook = xlApp.Workbooks.Open(inputSpreadsheetName)
|
| 34 |
print("there are %s sheets in this workbook"% xlBook.Sheets.Count)
|
| 35 |
|
| 36 |
colorMatrix = {}
|
| 37 |
categorizationMatrix = {}
|
| 38 |
pNpTestMatrix = {}
|
| 39 |
|
| 40 |
|
| 41 |
for i in range(1,xlBook.Sheets.Count +1):
|
| 42 |
sht = xlBook.Worksheets(i)
|
| 43 |
count = 1
|
| 44 |
tmpMatrix = {}
|
| 45 |
while sht.Cells(2,count).value:
|
| 46 |
if sht.Cells(2,count).Interior.ColorIndex in tmpMatrix.keys():
|
| 47 |
tmpMatrix[sht.Cells(2,count).Interior.ColorIndex] +=1
|
| 48 |
else:
|
| 49 |
tmpMatrix[sht.Cells(2,count).Interior.ColorIndex] = 1
|
| 50 |
count +=1
|
| 51 |
if sht.Cells(1,8).value:
|
| 52 |
categorizationMatrix[sht.name] = sht.Cells(1,8).value
|
| 53 |
else:
|
| 54 |
categorizationMatrix[sht.name] = "None Yet"
|
| 55 |
colorMatrix[sht.name] = []
|
| 56 |
for clr in list(tmpMatrix.keys()):
|
| 57 |
colorMatrix[sht.name].append([clr,tmpMatrix[clr]])
|
| 58 |
|
| 59 |
|
| 60 |
|
| 61 |
xlBook.Close()
|
| 62 |
|
| 63 |
for s in list(colorMatrix.keys()):
|
| 64 |
prodTest = []
|
| 65 |
for c in colorMatrix[s]:
|
| 66 |
if c[0] == 3:
|
| 67 |
c = ["x3",""]
|
| 68 |
prodTest.append(c[0])
|
| 69 |
prodResult = True
|
| 70 |
if 6 in prodTest:
|
| 71 |
prodResult = False
|
| 72 |
elif 44 in prodTest:
|
| 73 |
prodResult = False
|
| 74 |
elif 'x3' in prodTest:
|
| 75 |
prodResult = False
|
| 76 |
#print(s)
|
| 77 |
elif -4142 in prodTest:
|
| 78 |
prodResult = False
|
| 79 |
if prodResult:
|
| 80 |
pNpTestMatrix[s] = "Produce"
|
| 81 |
else:
|
| 82 |
pNpTestMatrix[s] = "TBD"
|
| 83 |
|
| 84 |
outputFile = open(outputFileName,'w')
|
| 85 |
|
| 86 |
for s in list(colorMatrix.keys()):
|
| 87 |
outputFile.write("%s|%s|%s"%(s,categorizationMatrix[s],pNpTestMatrix[s]))
|
| 88 |
colorList = colorMatrix[s]
|
| 89 |
colorList.sort()
|
| 90 |
for c in colorList:
|
| 91 |
outputFile.write("|%s fields %s"%(c[1],colorCodeMatrix[str(c[0])][1]))
|
| 92 |
outputFile.write("\n")
|
| 93 |
|
| 94 |
outputFile.close()
|
| 95 |
print("Categorizations are as follows:")
|
| 96 |
catList = []
|
| 97 |
for s in list(categorizationMatrix.keys()):
|
| 98 |
if categorizationMatrix[s] in catList:
|
| 99 |
pass
|
| 100 |
else:
|
| 101 |
catList.append(categorizationMatrix[s])
|
| 102 |
catList.sort()
|
| 103 |
for cat in catList:
|
| 104 |
print(cat) |
