-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathmakeExcel.py
More file actions
executable file
·149 lines (126 loc) · 5.43 KB
/
makeExcel.py
File metadata and controls
executable file
·149 lines (126 loc) · 5.43 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
import xlsxwriter
from ..everything import *
from string import ascii_uppercase as AtoZ
from api.API.faculty import getFacultyWithProjects
from api.API.parameters import getCurrentParameters
def getFilename(fileType):
cycle = getCurrentParameters()
fileName = cfg["downloads"]["downloadFileNameFormat"]
fileType = cfg["downloads"]["downloadFileTypes"][fileType]
fileName = fileName.replace("%%applicationCycle%%", str(cycle.year))
fileName = fileName.replace("%%downloadFileType%%", fileType)
return fileName
def getFilePath(fileType):
'''
This function returns the filepath with the filename and extension to be written into
'''
cycle = getCurrentParameters()
writeFileName = getFilename(fileType)
writeFileType = cfg["downloads"]["downloadFileTypes"][fileType]
fileExtension = cfg["downloads"]["downloadFileExtension"]
relPath = cfg["filepaths"]["projectFiles"] + str(cycle.year) + "/" + writeFileType + "/" + writeFileName + fileExtension
return os.path.join(base_path, relPath)
def checkFilePath(fileType):
'''
This function checks if the filepath for the fileType exists, if not it creates the filepath.
'''
applicationCycle = getCurrentParameters()
fileType = cfg["downloads"]["downloadFileTypes"][fileType]
path = cfg["filepaths"]["projectFiles"] + str(applicationCycle.year)
path = os.path.join(base_path, path)
if os.path.isdir(path):
pass
else:
os.mkdir(path)
path = path + "/" + fileType
if os.path.isdir(path):
pass
else:
os.mkdir(path)
def makeBudgetExcel():
params = getCurrentParameters()
faculty = getFacultyWithProjects(params.year)
applicationCycle = getCurrentParameters()
checkFilePath("allBudgets")
writeFilePath = getFilePath("allBudgets")
workbook = xlsxwriter.Workbook(writeFilePath)
workbook.set_properties({
'title': 'Budget for {}'.format(params.year),
'author': 'URCPP System',
'comments': 'Created with Python and XlsxWriter'})
# create a worksheet
worksheet = workbook.add_worksheet('All Budgets')
worksheetRow = 1
# add titles
letterCount = 0
worksheet.write('{0}{1}'.format(AtoZ[letterCount], worksheetRow),'Faculty/Proj. Title')
letterCount += 1
worksheet.write('{0}{1}'.format(AtoZ[letterCount], worksheetRow),'Project Status')
letterCount += 1
for title in cfg["budget"]:
worksheet.write('{0}{1}'.format(AtoZ[letterCount], worksheetRow), title['title'])
letterCount += 1
worksheet.write('{0}{1}'.format(AtoZ[letterCount], worksheetRow),'Total')
worksheetRow += 1
lastColumnCount = letterCount # we need the lastColumn's letter later
#loop though the budgets
for person in faculty:
letterCount = 0
facProjStr = '{0} {1} ({2})\n{3}'.format(person.username.firstname, person.username.lastname, person.programID.name, person.pID.title)
wordWrap = workbook.add_format()
wordWrap.set_text_wrap()
worksheet.write('{0}{1}'.format(AtoZ[letterCount], worksheetRow), facProjStr, wordWrap)
letterCount += 1
worksheet.write('{0}{1}'.format(AtoZ[letterCount], worksheetRow), person.pID.status)
letterCount += 1
budget = m2d(person.pID.budgetID)
for field in cfg["budget"]:
if field['amountFieldName'] == "miles":
twoSF = workbook.add_format()
twoSF.set_num_format('0.00')
milesCost = budget[field['amountFieldName']] * params.mileageRate * 1.0
worksheet.write('{0}{1}'.format(AtoZ[letterCount], worksheetRow), milesCost, twoSF)
else:
worksheet.write('{0}{1}'.format(AtoZ[letterCount], worksheetRow), budget[field['amountFieldName']])
letterCount += 1
# write the total formula for each row
worksheet.write_formula('{0}{1}'.format(AtoZ[letterCount], worksheetRow), '{{=SUM({0}{1}:{2}{1})}}'.format(AtoZ[2], worksheetRow, AtoZ[lastColumnCount-1]))
worksheetRow += 1
# write the grand total
worksheet.write('{0}{1}'.format(AtoZ[0], worksheetRow), 'Grand Total')
worksheet.write_formula('{0}{1}'.format(AtoZ[lastColumnCount], worksheetRow), '{{=SUM({0}{1}:{0}{2})}}'.format(AtoZ[lastColumnCount], 2, worksheetRow-1))
workbook.close()
def makeLaborExcel():
applicationCycle = getCurrentParameters()
faculty = getFacultyWithProjects(applicationCycle.year)
params = getCurrentParameters()
checkFilePath("allLabor")
writeFilePath = getFilePath("allLabor")
workbook = xlsxwriter.Workbook(writeFilePath)
workbook.set_properties({
'title': 'Labor for {}'.format(applicationCycle.year),
'author': 'URCPP System',
'comments': 'Created with Python and XlsxWriter'})
# create a worksheet
worksheet = workbook.add_worksheet('All Budgets')
row = 0
# add titles
worksheet.write(row, 0, 'Faculty/Proj. Title')
worksheet.write(row, 1, '#Students')
worksheet.write(row, 2, 'Duration')
worksheet.write(row, 3, 'Total')
row += 1
#loop though the budgets
for person in faculty:
facProjStr = '{0} {1} ({2})\n{3}'.format(person.username.firstname, person.username.lastname, person.programID.name, person.pID.title)
wordWrap = workbook.add_format()
wordWrap.set_text_wrap()
worksheet.write(row, 0, facProjStr, wordWrap)
worksheet.write(row, 1, person.pID.numberStudents)
worksheet.write(row, 2, person.pID.duration)
worksheet.write_formula(row, 3, '{{=B{0}*C{0}*{1}*40}}'.format(row+1, params.laborRate))
row += 1
# write the grand total
worksheet.write(row, 0, 'Grand Total')
worksheet.write_formula(row, 3, '{{=SUM({0}{1}:{0}{2})}}'.format(AtoZ[3], 2, row))
workbook.close()