Automated Excel Work Log Generation

This program automatically generaets Excel spreadsheets for daily work logs. It cnosists of three modules that handle user input, date processing, and Excel file creation.

Project Structure

1. Main Entry Point (main.py)

Accepts user input for log entries, validates the data, and triggers the Excel generation process.

import datetime_processor

log_date = input('Enter log date (e.g., 20240808): ')
work_description = input('Enter work description: ')
duration = input('Enter work duration: ')
overtime_status = input('Is overtime required (yes/no): ')

confirmation = input('Confirm input (y/n): ')
while confirmation.lower() == 'n':
    log_date = input('Enter log date: ')
    work_description = input('Enter work description: ')
    duration = input('Enter work duration: ')
    overtime_status = input('Is overtime required (yes/no): ')
    confirmation = input('Confirm input (y/n): ')

datetime_processor.process_entry(log_date, work_description, duration, overtime_status)

2. Date Processing Module (datetime_processor.py)

Converts the input date string to determine the day of the week, then formats and passes the data to the Excel handler.

from datetime import datetime

def process_entry(date_str, task, time_spent, overtime):
    def get_day_name():
        parsed_date = datetime.strptime(date_str, "%Y%m%d")
        return parsed_date.weekday()
    
    day_index = get_day_name()
    day_names = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
    day_name = day_names[day_index]
    
    month_key = f'{date_str[4:6]} Month'
    entry_data = {
        month_key: [date_str, time_spent, task, day_name, overtime]
    }
    
    spreadsheet = SpreadsheetHandler()
    spreadsheet.save_data(entry_data, 'Onsite Work Log', 'Work Log Records', 
                          ('Date', 'Duration', 'Task Description', 'Day', 'Overtime'))

3. Excel Handler (spreadsheet.py)

Handles all Excel file creation and data writing operations using xlwt and xlrd libraries.

import os
import xlwt
import xlrd
from xlutils.copy import copy

class SpreadsheetHandler:
    @staticmethod
    def save_data(data_dict, folder_name, file_name, headers):
        base_path = os.getcwd() + f'/{folder_name}/'
        if not os.path.exists(base_path):
            os.mkdir(base_path)
        
        file_path = base_path + f'{file_name}.xls'
        
        if not os.path.exists(file_path):
            workbook = xlwt.Workbook(encoding='utf-8')
            for sheet_name in data_dict.keys():
                worksheet = workbook.add_sheet(sheet_name, cell_overwrite_ok=True)
                for idx, header in enumerate(headers):
                    worksheet.col(idx).width = 7680
                    worksheet.write(0, idx, header)
            workbook.save(file_path)
        
        if os.path.exists(file_path):
            workbook = xlrd.open_workbook(file_path)
            sheets = workbook.sheet_names()
            for i, sheet_title in enumerate(sheets):
                for data_key in data_dict.keys():
                    if sheet_title == data_key:
                        rows_count = workbook.sheet_by_name(sheet_title).nrows
                        updated_book = copy(workbook)
                        target_sheet = updated_book.get_sheet(i)
                        for col_idx, value in enumerate(data_dict[data_key]):
                            target_sheet.write(rows_count, col_idx, value)
                        updated_book.save(file_path)

How It Works

1. The program prompts for log date in YYYYMMDD format, work description, duration, and overtime status
2. Input validation loop allows users to correct errors before proceeding
3. The date processor converts the date string into a corresponding weekday
4. All data gets organized into a dictionary keyed by month
5. The spreadsheet handler creates an Excel file with proper headers, or appends new rows to existing files

Requirements

• xlwt
• xlrd
• xlutils