-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathautomating_daily_report.py
More file actions
161 lines (120 loc) · 4.9 KB
/
automating_daily_report.py
File metadata and controls
161 lines (120 loc) · 4.9 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
150
151
152
153
154
155
156
157
158
159
160
161
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Alignment, Font
from datetime import date
# This creates the pattern i want the cell to be filled in
fill_pattern = PatternFill(
fill_type='solid',
start_color='00FFFF',
end_color='00FFFF'
)
# This creates alignment
alignment_center = Alignment(
horizontal='center',
vertical='center'
)
# This creates the bolding
font_bold = Font(
bold=True
)
wb = Workbook()
ws = wb.active
# I open .txt and read it
with open('C:/users/IA/Desktop/Littleton/CALLS.txt', encoding= 'utf8') as f:
lines = f.readlines()
# Create a counter using enumerate so i can read each line and also split each line with a whitepase to get lists
ws.cell(row=1, column=1).value = 'DATE'
ws.cell(row=1, column=2).value = 'START CALL TIME'
ws.cell(row=1, column=3).value = 'END CALL TIME'
ws.cell(row=1, column=4).value = 'TELEPHONE'
ws.cell(row=1, column=5).value = 'CLAIM NO'
ws.cell(row=1, column=6).value = 'EX/NEW'
ws.cell(row=1, column=7).value = 'NOTES'
for count, line in enumerate(lines, start=2):
new_list = line.split(' ')
# This creates the header of the xlsx.
if len(new_list) == 3:
if 'EXT' in new_list[1]:
ws.cell(row=count, column=2).value = new_list[0]
ws.cell(row=count, column=2).alignment = alignment_center
ws.cell(row=count, column=3).value = new_list[-1]
ws.cell(row=count, column=3).alignment = alignment_center
ws.cell(row=count, column=4).value = new_list[1]
ws.cell(row=count, column=4).alignment = alignment_center
ws.cell(row=count, column=5).value = '-'
ws.cell(row=count, column=5).alignment = alignment_center
ws.cell(row=count, column=6).value = '-'
ws.cell(row=count, column=6).alignment = alignment_center
ws.cell(row=count, column=7).value = 'INTERPRETING'
ws.cell(row=count, column=7).alignment = alignment_center
else:
ws.cell(row=count, column=2).value = new_list[0]
ws.cell(row=count, column=2).alignment = alignment_center
ws.cell(row=count, column=3).value = new_list[-1]
ws.cell(row=count, column=3).alignment = alignment_center
ws.cell(row=count, column=4).value = new_list[1]
ws.cell(row=count, column=4).alignment = alignment_center
ws.cell(row=count, column=5).value = '-'
ws.cell(row=count, column=5).alignment = alignment_center
ws.cell(row=count, column=6).value = '-'
ws.cell(row=count, column=6).alignment = alignment_center
ws.cell(row=count, column=7).value = 'INFO'
ws.cell(row=count, column=7).alignment = alignment_center
elif len(new_list) == 4:
ws.cell(row=count, column=2).value = new_list[0]
ws.cell(row=count, column=2).alignment = alignment_center
ws.cell(row=count, column=3).value = new_list[-1]
ws.cell(row=count, column=3).alignment = alignment_center
ws.cell(row=count, column=4).value = new_list[1]
ws.cell(row=count, column=4).alignment = alignment_center
ws.cell(row=count, column=5).value = new_list[2]
ws.cell(row=count, column=5).alignment = alignment_center
ws.cell(row=count, column=6).value = 'NEW'
ws.cell(row=count, column=6).alignment = alignment_center
ws.cell(row=count, column=7).value = 'CLAIM'
ws.cell(row=count, column=7).alignment = alignment_center
else:
ws.cell(row=count, column=2).value = new_list[0]
ws.cell(row=count, column=2).alignment = alignment_center
ws.cell(row=count, column=3).value = new_list[-1]
ws.cell(row=count, column=3).alignment = alignment_center
ws.cell(row=count, column=4).value = new_list[1]
ws.cell(row=count, column=4).alignment = alignment_center
ws.cell(row=count, column=5).value = new_list[2]
ws.cell(row=count, column=5).alignment = alignment_center
ws.cell(row=count, column=6).value = 'EX'
ws.cell(row=count, column=6).alignment = alignment_center
ws.cell(row=count, column=7).value = 'UPDATE'
ws.cell(row=count, column=7).alignment = alignment_center
# Date
today = date.today()
ws['A2'] = today.strftime("%m/%d/%Y")
# Filling
ws['A1'].fill = fill_pattern
ws['B1'].fill = fill_pattern
ws['C1'].fill = fill_pattern
ws['D1'].fill = fill_pattern
ws['E1'].fill = fill_pattern
ws['F1'].fill = fill_pattern
ws['G1'].fill = fill_pattern
# Alignment
ws['A1'].alignment = alignment_center
ws['B1'].alignment = alignment_center
ws['C1'].alignment = alignment_center
ws['D1'].alignment = alignment_center
ws['E1'].alignment = alignment_center
ws['F1'].alignment = alignment_center
ws['G1'].alignment = alignment_center
# Bolding
ws['A1'].font = font_bold
ws['B1'].font = font_bold
ws['C1'].font = font_bold
ws['D1'].font = font_bold
ws['E1'].font = font_bold
ws['F1'].font = font_bold
ws['G1'].font = font_bold
# Merging Cells
ws.merge_cells(start_row=2, start_column=1, end_row=count, end_column=1)
ws['A2'].alignment = alignment_center
# Saving Workbook
wb.save(f'C:/Users/IA/Desktop/automated_report_{today.strftime("%m-%d-%Y")}.xlsx')
print(f'Report has been created successfully!')