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)