EmployeeAttendanceBackend/xlsx_creator.py

import xlsxwriter
from xlsxwriter.utility import xl_range
from datetime import datetime, timedelta

class xlsx_creator():
    style_header = {
        'bold': True,
        'border': 1,
        'align': 'center',
        'valign': 'vcenter'    
    }

    def __init__(self, DATA, path='/tmp/'):
        self.FileName= F"employee-attendance-{str(datetime.now())}.xlsx"
        self.path = path+self.FileName
        self.DATA = DATA
        self.workbook = xlsxwriter.Workbook(self.path)
        self.worksheet = self.workbook.add_worksheet()

    def cook_xlsx(self):
        self.create_header()
        self.create_diff_date_and_hours()
        self.create_sub_head()
        self.create_body_data()
        self.workbook.close()
        return self.path

    def create_header(self):
        self.worksheet.set_row(0, 70)
        style_header = self.workbook.add_format(self.style_header)
        style_header.set_text_wrap() 

        colum = 0     
        
        for data in self.DATA:
            text_header = F"{data['FIO']} \n посещаемость с {data['STARTDATE']}г. по {data['ENDDATE']}г."
            self.worksheet.merge_range(self.get_column_range(0, colum, 0, colum+4), text_header, style_header)
            colum += 6

    def convert_str_to_datetime(self, date_string):
        (h,m,s) = date_string.split(':')
        return timedelta(hours=int(h), minutes=int(m), seconds=int(s))

    def get_count_work_hours(self, DateAttendance):
        count_work_hours = timedelta()
        for x in DateAttendance:
            count_work_hours += self.convert_str_to_datetime(self.return_correct_value(x["diffTime"]))

        hours, remainder = divmod(count_work_hours.total_seconds(), 3600)
        minutes, seconds = divmod(remainder, 60)
        print(count_work_hours)
        return F"{int(hours)}:{int(minutes)}:{int(seconds)}"


    def create_diff_date_and_hours(self):
        style_body = self.workbook.add_format({
            'border': 1,
            'align': 'center',  
        })
        style_header = self.workbook.add_format(self.style_header)
        col_work_day_text = "Количество рабочих дней"
        col_calendar_day = "Количество календарных дней"
        col_work_hours = "Количество рабочих часов"
        column = 0

        for data in self.DATA:
            self.worksheet.merge_range(self.get_column_range(1, column, 1, column+3),
                                        col_work_day_text, style_header)
            self.worksheet.write(1, column+4, len(data['DateAttendance']), style_body)    

            self.worksheet.merge_range(self.get_column_range(2, column, 2, column+3),
                                       col_calendar_day, style_header)
            self.worksheet.write(2, column+4, len(data['EmptyDatesAttendance']+data['DateAttendance']), style_body)    

            self.worksheet.merge_range(self.get_column_range(3, column, 3, column+3),
                                       col_work_hours, style_header)
            self.worksheet.write(3, column+4, self.get_count_work_hours(data['DateAttendance']), style_body)
            column += 6


    def create_sub_head(self):
        colum = 0
        style_header = self.workbook.add_format(self.style_header)
        style_header.set_text_wrap() 
        self.worksheet.set_row(4, 30)


        for data in self.DATA:
            self.worksheet.write(4, colum, 'Д/н', style_header)
            self.worksheet.write(4, colum+1, 'Дата', style_header)
            self.worksheet.write(4, colum+2, 'Вход',  style_header)
            self.worksheet.write(4, colum+3, 'Выход',  style_header)

            self.worksheet.set_column(4, colum+4, 15)
            self.worksheet.write(4, colum+4, 'Время пребывания', style_header)
            colum += 6


    def get_column_range(self, srow, scolumn, erow, ecolumn):
        return xl_range(srow, scolumn, erow, ecolumn)

    def sort_attendance(self, arr):
        return sorted(arr, key=lambda x : datetime.strptime(self.return_correct_value(x['date']), '%d.%m.%Y'))

    def is_bold_diff_time(self, val, s, b):
        val = self.return_correct_value(val)
        
        if len(val) > 0:
            if int(val.split(':')[0]) > 7:
                return b
        return s
        
    def is_bold_entrance(self, val, s, b):
        val = self.return_correct_value(val)
        
        if len(val) > 0:
            hours = val.split(':')[0]
            if int(hours) >= 9 and int(hours) <= 18:
                return b
        return s

    def create_body_data(self):
        colum = 0
        style_body = self.workbook.add_format({
            'border': 1,
            'align': 'center',  
        })

        style_body_with_bold =  self.workbook.add_format({
            'border': 1,
            'align': 'center',  
            'bold': True
        })

        for data in self.DATA:
            body_data = (data['EmptyDatesAttendance']+data['DateAttendance'])
            row = 5
            for attendance in self.sort_attendance(body_data):
                self.worksheet.write(row, colum, self.return_correct_value(attendance['wd']), style_body_with_bold)
                self.worksheet.write(row, colum+1, self.return_correct_value(attendance['date']), style_body)

                body_entrance_style = self.is_bold_entrance(attendance['entrance'], style_body, style_body_with_bold)
                self.worksheet.write(row, colum+2, self.return_correct_value(attendance['entrance']),  body_entrance_style)
                
                self.worksheet.write(row, colum+3, self.return_correct_value(attendance['exit']),  style_body)

                body_diff_time = self.is_bold_diff_time(attendance['diffTime'], style_body, style_body_with_bold)
                self.worksheet.write(row, colum+4, self.return_correct_value(attendance['diffTime']), body_diff_time)
                row += 1
            colum += 6

    def return_correct_value(self, val):
        if type(val).__name__ == 'list':
            return val[0]
        else:
            return val