สร้าง Excel Timesheet ด้วย Python กับ ChatGPT

Create excel timesheet with python and month is current

import openpyxl
from datetime import datetime

# Create a new Excel workbook
workbook = openpyxl.Workbook()

# Get the current month
today = datetime.today()
month = today.strftime("%B")

# Add a new sheet with the name of the current month
sheet = workbook.active
sheet.title = month

# Add the header row with the day names
days = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]
for i, day in enumerate(days):
    sheet.cell(row=1, column=i+1, value=day)

# Calculate the number of days in the current month
num_days = (today.replace(day=1) - today.replace(day=28)).days + 28

# Add the dates to the sheet
current_day = today.replace(day=1)
for i in range(num_days):
    day_of_week = current_day.weekday()
    sheet.cell(row=i+2, column=day_of_week+1, value=current_day.day)
    current_day = current_day + timedelta(days=1)

# Save the workbook to a file
workbook.save(f"{month} timesheet.xlsx")

How to run this file 

I found error is "name 'timedelta' is not defined"

from datetime import datetime, timedelta

Why num_days is 1 ? , I need all day in this month

import calendar

# Get the current month and year
today = date.today()
month = today.strftime("%B %Y")

# Get the number of days in the current month
num_days = calendar.monthrange(today.year, today.month)[1]

# Create a new workbook and select the first sheet
workbook = openpyxl.Workbook()
sheet = workbook.active

# Define the headers
headers = ["Day", "Time In", "Time Out", "Detail"]

# Add the headers to the first row
for i, header in enumerate(headers):
    sheet.cell(row=1, column=i+1, value=header)

# Add the dates and log cells to the sheet
current_day = today.replace(day=1)
for i in range(num_days):
    sheet.cell(row=i+2, column=1, value=current_day.strftime("%d %b %Y"))
    for j in range(4, 9):
        sheet.cell(row=i+2, column=j, value="")
    current_day = current_day + timedelta(days=1)

# Save the workbook to a file
workbook.save(f"{month} timesheet.xlsx")

How to know day is holiday ? 

import datetime

# Define a list of holidays as date strings
holidays = ['2023-01-01', '2023-04-14', '2023-05-01', '2023-06-12', '2023-08-15', '2023-11-02', '2023-11-15', '2023-12-25']

# Convert the holiday strings to date objects
holiday_dates = [datetime.datetime.strptime(h, '%Y-%m-%d').date() for h in holidays]

# Check if a date is a holiday
date_to_check = datetime.date(2023, 4, 14)
if date_to_check in holiday_dates:
    print(f"{date_to_check} is a holiday!")
else:
    print(f"{date_to_check} is not a holiday.")

{
        "2023-01-01" : "วันหยุด"
}

Holidays data in json file and has key is date and description

import json
import datetime

# Load the holidays data from a JSON file
with open("holidays.json", "r") as f:
    holidays = json.load(f)

# Convert the date strings in the holiday data to date objects
holiday_dates = [datetime.datetime.strptime(date, "%Y-%m-%d").date() for date in holidays.keys()]

# Check if a date is a holiday
date_to_check = datetime.date(2023, 4, 14)
if date_to_check in holiday_dates:
    holiday_description = holidays[date_to_check.strftime("%Y-%m-%d")]
    print(f"{date_to_check} is a holiday ({holiday_description})")
else:
    print(f"{date_to_check} is not a holiday.")

with open("holidays.json", "r", encoding="utf-8") as f:
    holidays = json.load(f)

holiday_dates = [datetime.strptime(date, "%Y-%m-%d").date() for date in holidays.keys()]

How to know date is Saturday or Sunday ?

import datetime

# Create a datetime object for a given date
d = datetime.date(2023, 2, 16)

# Use the weekday() method to get the day of the week (0=Monday, 6=Sunday)
weekday = d.weekday()

# Check if the day is a Saturday (5) or Sunday (6)
if weekday == 5 or weekday == 6:
    print("It's a weekend!")
else:
    print("It's a weekday.")

import openpyxl
from datetime import date,datetime, timedelta
import calendar
import json

# Create a new Excel workbook
workbook = openpyxl.Workbook()

# Get the current month
today = date.today()
month = today.strftime("%m")
year = today.strftime("%Y")

# Load the holidays data from a JSON file
with open(f"./holidays/{year}.json", "r",encoding="utf-8") as f:
    holidays = json.load(f)

# Convert the date strings in the holiday data to date objects
holiday_dates = [datetime.strptime(year + "-" + date, "%Y-%m-%d").date() for date in holidays.keys()]

# Add a new sheet with the name of the current month
sheet = workbook.active
sheet.title = "Timesheet_"+month+year

# Define the headers
headers = ["Day", "Time In", "Time Out", "OT Start", "OT Finish", "Manager Approve", "Detail", "Remark"]

# Add the headers to the first row
for i, header in enumerate(headers):
    sheet.cell(row=1, column=i+1, value=header)

# Get the number of days in the current month
num_days = calendar.monthrange(today.year, today.month)[1]

current_day = today.replace(day=1)
for i in range(num_days):
    date_to_check = datetime(year=int(year),month=int(month),day=i+1).date()
    
    weekday = date_to_check.weekday()

    sheet.cell(row=i+2, column=1, value=current_day.strftime("%d"))
    if date_to_check in holiday_dates:
        holiday_description = holidays[date_to_check.strftime("%Y-%m-%d")]
        sheet.cell(row=i+2, column=2, value=holiday_description)
    else:
        if weekday == 5:
            sheet.cell(row=i+2, column=2, value="วันเสาร์")
        elif weekday == 6:
            sheet.cell(row=i+2, column=2, value="วันอาทิตย์")
        else:
            sheet.cell(row=i+2, column=2, value="08:30")
            sheet.cell(row=i+2, column=3, value="17:30")
            
    current_day = current_day + timedelta(days=1)

# Save the workbook to a file
workbook.save(f"Timesheet_{month}{year}.xlsx")

{
  "result": {
    "api": "API_V2.FIHolidays",
    "timestamp": "2023-02-21 13:18:42",
    "data": [
      {
        "HolidayWeekDay": "Monday",
        "HolidayWeekDayThai": "วันจันทร์",
        "Date": "2023-01-02",
        "DateThai": "02/01/2566",
        "HolidayDescription": "Substitution for New Year's Eve and New Year's Day (Saturday 31st  December 2022 and Saturday 1st January 2023)",
        "HolidayDescriptionThai": "ชดเชยวันสิ้นปีและวันขึ้นปีใหม่ (วันเสาร์ที่ 31 ธันวาคม 2565 และวันอาทิตย์ที่ 1 มกราคม 2566)"
      }
    ]
  }
}

import http.client

...

conn = http.client.HTTPSConnection("apigw1.bot.or.th")
headers = {
    'X-IBM-Client-Id': "d872355e-4bae-4f1b-9c59-xxxxxxxxxxxx",
    'accept': "application/json"
}

...

# Call API thai holidays
conn.request("GET", "/bot/public/financial-institutions-holidays/?year="+year, headers=headers)
res = conn.getresponse().read().decode("utf-8")
data = json.loads(res)

holidays = data['result']['data']

# Convert the date strings in the holiday data to date objects
holiday_dates = [datetime.strptime(holiday['Date'], "%Y-%m-%d").date() for holiday in holidays]

...

for i in range(num_days):

...

        holiday = [holiday for holiday in holidays if holiday['Date'] == date_to_check.strftime("%Y-%m-%d")]
        sheet.cell(row=i+2, column=2, value=holiday[0]['HolidayDescriptionThai'])

import sys

#Input Argv
monthIput = sys.argv[1] if len(sys.argv) == 3 else ""
yearInput = sys.argv[2] if len(sys.argv) == 3 else ""

...

# Get the current month
today = date.today()
month = monthIput if monthIput != "" else today.strftime("%m")
year = yearInput if yearInput != "" else today.strftime("%Y")

import glob, os
 
...

#Delete all file .xlsx
for f in glob.glob("*.xlsx"):
    os.remove(f)

# Save the workbook to a file
workbook.save(f"Timesheet_{month}{year}.xlsx")