def _setup_styles(self):
"""Bootstrap styles"""
self._fonts = IndexedList()
self._fonts.add(DEFAULT_FONT)
self._alignments = IndexedList([Alignment()])
self._borders = IndexedList()
self._borders.add(DEFAULT_BORDER)
self._fills = IndexedList()
self._fills.add(DEFAULT_EMPTY_FILL)
self._fills.add(DEFAULT_GRAY_FILL)
self._number_formats = IndexedList()
self._date_formats = {}
self._protections = IndexedList([Protection()])
self._colors = COLOR_INDEX
self._cell_styles = IndexedList([StyleArray()])
self._named_styles = NamedStyleList()
self.add_named_style(NamedStyle(font=copy(DEFAULT_FONT), builtinId=0))
self._table_styles = TableStyleList()
self._differential_styles = DifferentialStyleList()
def _setup_styles(self):
"""Bootstrap styles"""
from openpyxl.styles.alignment import Alignment
from openpyxl.styles.borders import DEFAULT_BORDER
from openpyxl.styles.fills import DEFAULT_EMPTY_FILL, DEFAULT_GRAY_FILL
from openpyxl.styles.fonts import DEFAULT_FONT
from openpyxl.styles.protection import Protection
from openpyxl.styles.colors import COLOR_INDEX
from openpyxl.styles.named_styles import NamedStyleList
self._fonts = IndexedList()
self._fonts.add(DEFAULT_FONT)
self._alignments = IndexedList([Alignment()])
self._borders = IndexedList()
self._borders.add(DEFAULT_BORDER)
self._fills = IndexedList()
self._fills.add(DEFAULT_EMPTY_FILL)
self._fills.add(DEFAULT_GRAY_FILL)
self._number_formats = IndexedList()
self._protections = IndexedList([Protection()])
self._colors = COLOR_INDEX
self._cell_styles = IndexedList([StyleArray()])
self._named_styles = NamedStyleList()
self.add_named_style(NamedStyle(font=DEFAULT_FONT, builtinId=0))
def handler(event, context):
start_date = datetime.today() - timedelta(days=1)
# download activity report from Dropbox
dbx = dropbox.Dropbox(os.environ['DBACCESSTOKEN'])
dbx.files_download_to_file('/tmp/Activity.csv',
'/JHK/Activity/Daily/Activity.csv')
dbx.files_download_to_file('/tmp/FSUsers.csv', '/JHK/FSUsers/FSUsers.csv')
dbx.files_download_to_file('/tmp/Template.xlsx',
'/Timecards/Template/Template.xlsx')
# copy activity report so we have a history as it gets overwritten everyday
# copyfile('tmp/Activity.csv', 'tmp/Activity Backups/Activity{}.csv'.format(start_date.strftime("%m-%d-%Y")))
# import, sort and strip data from Activity report
data = pandas.read_csv('/tmp/Activity.csv')
data['Status Date'] = pandas.to_datetime(data['Status Date'])
data = data.sort_values(['Tech', 'Work Order #', 'Status Date'],
ascending=(True, True, True))
df = pandas.DataFrame(columns=[
'Status', 'Date Created', 'Customer ID', 'Work Order #',
'Job Description', 'Status Date', 'Status Changes', 'Location', 'Tech'
])
previous_status = ''
previous_work_order = ''
# drop all rows that are not for the current date
for index, row in data.iterrows():
# future, need to implement a fix for DST
current_row_date = row['Status Date'].to_pydatetime() - timedelta(
hours=7)
# remove entries that are not for today
if current_row_date.day != start_date.day or current_row_date.month != start_date.month \
or current_row_date.year != start_date.year:
data.drop(index, inplace=True)
continue
log_path = '/tmp/Timecards/_LOGS_'
if not os.path.exists(log_path):
os.makedirs(log_path)
log = open(
'{}/{}consecutivestartstop.txt'.format(
log_path, start_date.strftime("%m-%d-%Y")), "a")
current_status = row['Status Changes']
current_tech = row['Tech']
current_work_order = row['Work Order #']
if current_status == 'Job Complete' or current_status.startswith(
'Susp.'):
if previous_status == 'Job Complete' or previous_status.startswith(
'Susp.'):
if previous_work_order == current_work_order:
s = '{}: Removed Multiple Job Complete/Susp from: {}, for work order: {}'\
.format(start_date.strftime("%m-%d-%Y"), current_tech, current_work_order)
log.write("{}\n".format(s))
log.close()
data.drop(index, inplace=True)
previous_status = row['Status Changes']
previous_work_order = row['Work Order #']
previous_index = index
# export to csv for debuging
# data.to_csv('Results.csv')
# get unique list of techs
tech_list = data['Tech'].unique()
dt_holidays = get_holiday_dates()
template_path = '/tmp/Template.xlsx'
for tech in tech_list:
tech_data = data[data.Tech == tech]
tech_data = tech_data.sort_values(['Status Date'], ascending=True)
current_tech = tech.replace(' ', ' ')
filename = new_timecard(current_tech, start_date)
template = openpyxl.load_workbook(template_path, read_only=False)
template.save(filename=filename)
timecard = openpyxl.load_workbook(filename, read_only=False)
time_ws = timecard['Timecard']
time_ws['A1'].value = current_tech
input_row_index = 3
driving = False
in_over_time = False
daily_hours = 0
for i, (index, row) in enumerate(tech_data.iterrows()):
current_row_date = row['Status Date'].to_pydatetime() - timedelta(
hours=7)
current_row_tech = row['Tech'].replace(' ', ' ')
current_row_event = row['Status Changes']
current_row_wo = row['Work Order #']
current_row_task = row['Location']
current_row_taskname = row['Location']
task_date = current_row_date.strftime('%b-%d')
time_ws['A{}'.format(input_row_index)].value = task_date
time_ws['B{}'.format(input_row_index)].value = current_row_taskname
time_ws['C{}'.format(input_row_index)].value = current_row_wo
time_ws['K{}'.format(input_row_index)].value = "No"
dv = DataValidation(type="list",
formula1='"No,Yes"',
allow_blank=False)
time_ws.add_data_validation(dv)
dv.add(time_ws['K{}'.format(input_row_index)])
if current_row_event == 'Driving':
if not driving:
time_ws['D{}'.format(
input_row_index)].value = current_row_date
driving = True
if current_row_event == 'On Site':
time_ws['E{}'.format(input_row_index)].value = current_row_date
if i == len(tech_data) - 1:
if current_row_event == 'Job Complete' or current_row_event.startswith(
'Susp.'):
pass
else:
current_row_event = 'Job Complete'
current_row_date = current_row_date.replace(
hour=23, minute=59, second=59, microsecond=999999)
if current_row_event == 'Job Complete' or current_row_event.startswith(
'Susp.'):
if time_ws['E{}'.format(input_row_index)].value is None \
and time_ws['D{}'.format(input_row_index)].value is None:
midnight = current_row_date.replace(hour=0,
minute=0,
second=0,
microsecond=0)
time_ws['E{}'.format(input_row_index)].value = midnight
time_ws['F{}'.format(input_row_index)].value = current_row_date
try:
drive_start = None
drive_start = time_ws['D{}'.format(input_row_index)].value
except TypeError as e:
print(e)
try:
on_site_start = None
on_site_start = time_ws['E{}'.format(
input_row_index)].value
except TypeError as e:
print(e)
job_complete = time_ws['F{}'.format(input_row_index)].value
if drive_start:
on_site_start = drive_start
try:
total_hours = job_complete - on_site_start
total_hours = round(total_hours.total_seconds() / 60 / 60,
2)
temp_hours = total_hours
time_ws['G{}'.format(
input_row_index)] = '= SUM(H{}:J{})'.format(
input_row_index, input_row_index)
daily_hours += total_hours
# double time on holidays
if current_row_date.date() in dt_holidays:
time_ws['J{}'.format(
input_row_index)].value = total_hours
elif current_row_date.weekday(
) == 5 or current_row_date.weekday() == 6 or in_over_time:
time_ws['I{}'.format(
input_row_index)].value = total_hours
elif daily_hours <= 8:
time_ws['H{}'.format(
input_row_index)].value = total_hours
elif daily_hours > 8:
in_over_time = True
over_time = daily_hours - 8
time_ws['I{}'.format(
input_row_index)].value = over_time
time_ws['H{}'.format(
input_row_index)].value = total_hours - over_time
except TypeError as e:
print(e)
driving = False
input_row_index += 1
if i == len(tech_data) - 1:
# this is the last row, total it up, lock the cells and save the document
if current_row_event == 'Job Complete' or current_row_event.startswith(
'Susp.'):
pass
else:
input_row_index += 1
try:
time_ws['G{}'.format(input_row_index +
1)] = '= SUM(G3:G{})'.format(
input_row_index)
time_ws['F{}'.format(input_row_index +
1)].value = 'Total Hours'
time_ws.protection.sheet = True
columns = [
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K',
'L'
]
for col in columns:
if col == 'K' or col == 'L':
for count in range(3, input_row_index + 32):
time_ws['{}{}'.format(
col, count)].protection = Protection(
locked=False)
else:
for count in range(input_row_index + 2,
input_row_index + 32):
time_ws['{}{}'.format(
col, count)].protection = Protection(
locked=False)
except NameError as e:
print(e)
timecard.save(filename=filename)
upload_to_dropbox(dbx, '/tmp/Timecards', 'home/Timecards/Testing')
# openpyxl.styles.numbers
number_format_cell.number_format = FORMAT_PERCENTAGE_00
# Create a Workbook password and lock the structure
wb2 = Workbook()
wb2.security.workbookPassword = '******'
wb2.security.lockStructure = True
wb2.save("Cell formatting2.xlsx")
# Protect the sheet
ws1.protection.sheet = True
ws1.protection.password = '******'
ws1.protection.enable()
# Unlock cell A1
ws1.cell(row=1, column=1).protection = Protection(locked=False,
hidden=False)
wb.save(filename)
# openpyxl.styles.alignment
## alignment_cell.alignment = Alignment(horizontal="center", vertical=None, textRotation=0,\
## wrapText=None, shrinkToFit=None, indent=0,\
## relativeIndent=0, justifyLastLine=None,\
## readingOrder=0, text_rotation=None,\
## wrap_text=None, shrink_to_fit=None, mergeCell=None)
##
## wb.save(filename)
##
##
##
def write(grids, output):
"""Given a list of grids and a file-like output, save an XLSX file.
In addition to "headers" and "rows", the grid may contain these keys:
- title string: sets the sheet title in Excel
- active bool: sets the active sheet in Excel
- activeCell string: sets the selected cell in Excel
- locked bool: locks the sheet in Excel"""
bold = Font(bold=True)
# yellow = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
# orange = PatternFill(start_color="FFF1D8", end_color="FFF1D8", fill_type="solid")
red = PatternFill(start_color="FFD8D8",
end_color="FFD8D8",
fill_type="solid")
wb = Workbook()
wb.remove(wb.active)
for grid in grids:
ws = None
if "title" in grid:
ws = wb.create_sheet(grid["title"])
else:
ws = wb.create_sheet()
grid["worksheet"] = ws
i = 0
if "headers" in grid:
for header in grid["headers"]:
for j in range(0, len(header)):
value = header[j]["label"]
cell = ws.cell(column=j + 1, row=i + 1)
cell.value = value
cell.font = bold
c = get_column_letter(j + 1)
if i == 0:
w = max(len(value), MIN_COLUMN_WIDTH)
ws.column_dimensions[c].width = w
if "locked" in header[j] and header[j]["locked"]:
cell.protection = Protection(locked=True)
if "validations" in header[j]:
x = len(grid["headers"]) + 1
for validation in header[j]["validations"]:
data_val = DataValidation(**validation)
ws.add_data_validation(data_val)
data_val.add("{0}{1}:{0}{2}".format(
c, x, MAX_EXPECTED_ROWS))
i += 1
ws.freeze_panes = ws.cell(column=1, row=i + 1)
if "rows" in grid:
for row in grid["rows"]:
for j in range(0, len(row)):
c = row[j]
cell = ws.cell(column=j + 1, row=i + 1)
cell.value = c["label"]
if "status" in c and c["status"] == "ERROR":
cell.fill = red
if "comment" in c:
cell.comment = Comment(c["comment"],
"Validation service")
if "bold" in c and c["bold"]:
cell.font = bold
if "width" in c and c["width"]:
a = get_column_letter(j + 1)
ws.column_dimensions[a].width = c["width"]
i += 1
for grid in grids:
if "active" in grid and grid["active"]:
wb.active = grid["worksheet"]
if "activeCell" in grid:
grid["worksheet"].sheet_view.selection[0].activeCell = grid[
"activeCell"]
grid["worksheet"].sheet_view.selection[0].sqref = grid[
"activeCell"]
if "locked" in grid and grid["locked"]:
grid["worksheet"].protection.enable()
wb.save(output)
