from openpyxl import Workbook from openpyxl.utils import getcolumnletter from…
- User
from openpyxl import Workbook from openpyxl.utils import get_column_letter from openpyxl.styles import Font, Alignment, PatternFill from openpyxl.worksheet.datavalidation import DataValidation from openpyxl.formatting.rule import FormulaRule from openpyxl.workbook.defined_name import DefinedName
MAX_ROWS = 1000 # number of employee rows prepared
Colors (ARGB)
GREEN = "FFC6EFCE" RED = "FFFFC7CE" YELLOW = "FFFFEB9C" BLUE = "FFDDEBF7" LIGHT_GRAY = "FFF2F2F2" ORANGE = "FFFFC000"
wb = Workbook() ws = wb.active ws.title = "Attendance" setup = wb.create_sheet("Setup")
Setup sheet for official holidays
setup["A1"] = "Official Holidays (enter dates)" setup["A1"].font = Font(bold=True) setup.column_dimensions["A"].width = 28 for r in range(2, 101): setup[f"A{r}"].number_format = "yyyy-mm-dd"
Header section
ws["A1"] = "Year" ws["B1"] = "=YEAR(TODAY())" ws["C1"] = "Month" ws["D1"] = "=MONTH(TODAY())"
ws["A2"] = "Emp ID" ws["B2"] = "Name"
Calendar row C2:AG2
ws["C2"] = "=DATE(1,1,1)" for col_idx in range(4, 34): # D to AG prev_col = get_column_letter(col_idx - 1) col = get_column_letter(col_idx) ws[f"{col}2"] = f'=IF({prev_col}2="","",IF({prev_col}2+1>=EDATE(2,1),"",{prev_col}2+1))' for col_idx in range(3, 34): ws[f"{get_column_letter(col_idx)}2"].number_format = "ddd-dd"
Column widths
ws.column_dimensions["A"].width = 12 ws.column_dimensions["B"].width = 24 for col_idx in range(3, 34): ws.column_dimensions[get_column_letter(col_idx)].width = 5 for col_letter in ["AI", "AJ", "AK", "AL", "AM"]: ws.column_dimensions[col_letter].width = 12
Style headers
for c in range(1, 5): ws.cell(row=1, column=c).font = Font(bold=True) for c in range(1, 40): # up to AM ws.cell(row=2, column=c).font = Font(bold=True) for col_idx in range(3, 34): ws[f"{get_column_letter(col_idx)}2"].alignment = Alignment(horizontal="center") for col_letter in ["AI", "AJ", "AK", "AL", "AM"]: ws[f"{col_letter}2"].alignment = Alignment(horizontal="center")
Data validation (C3:AG1000)
dv = DataValidation(type="list", formula1='"P,L,HD,H"', allow_blank=True) dv.errorTitle = "Invalid code" dv.error = "Use only P, L, HD, or H." dv.promptTitle = "Attendance code" dv.prompt = "Choose or type: P (Present), L (Leave), HD (Half-day), H (Holiday)" ws.add_data_validation(dv) dv.add(f"C3:AG{MAX_ROWS}")
Conditional formatting: weekend header highlight (C2:AG2)
fill_light_gray = PatternFill(fill_type="solid", fgColor=LIGHT_GRAY) ws.conditional_formatting.add("C2:AG2", FormulaRule(formula=["WEEKDAY(C2,2)>5"], fill=fill_light_gray))
Conditional formatting: daily codes (C3:AG…)
fill_green = PatternFill(fill_type="solid", fgColor=GREEN) fill_red = PatternFill(fill_type="solid", fgColor=RED) fill_yellow = PatternFill(fill_type="solid", fgColor=YELLOW) fill_blue = PatternFill(fill_type="solid", fgColor=BLUE)
Use relative reference so each cell colors by its own value
ws.conditional_formatting.add(f"C3:AG{MAX_ROWS}", FormulaRule(formula=['C3="P"'], fill=fill_green)) ws.conditional_formatting.add(f"C3:AG{MAX_ROWS}", FormulaRule(formula=['C3="L"'], fill=fill_red)) ws.conditional_formatting.add(f"C3:AG{MAX_ROWS}", FormulaRule(formula=['C3="HD"'], fill=fill_yellow)) ws.conditional_formatting.add(f"C3:AG{MAX_ROWS}", FormulaRule(formula=['C3="H"'], fill=fill_blue))
Summary headers
ws["AI2"] = "Total P" ws["AJ2"] = "L" ws["AK2"] = "HD" ws["AL2"] = "H" ws["AM2"] = "%Attend"
WorkDays helper (AH2) and named range
ws["AH2"] = '=NETWORKDAYS.INTL(DATE(1,1,1),EOMONTH(DATE(1,1,1),0),"0000011",Setup!2:100)' ws.column_dimensions["AH"].hidden = True dn = DefinedName(name="WorkDays", attr_text="Attendance!2") wb.defined_names.append(dn)
Summary formulas per row
for r in range(3, MAX_ROWS + 1): ws[f"AI{r}"] = f'=COUNTIF(AG{r},"P")' ws[f"AJ{r}"] = f'=COUNTIF(AG{r},"L")' ws[f"AK{r}"] = f'=COUNTIF(AG{r},"HD")' ws[f"AL{r}"] = f'=COUNTIF(AG{r},"H")' ws[f"AM{r}"] = f'=IF(WorkDays=0,"",ROUND((AI{r}+AK{r}*0.5)/WorkDays,3))' ws[f"AM{r}"].number_format = "0%"
Low attendance highlight (AM<75%)
ws.conditional_formatting.add( f"AM3:AM{MAX_ROWS}", FormulaRule(formula=["AM3<0.75"], font=Font(bold=True), fill=PatternFill(fill_type="solid", fgColor=ORANGE)) )
Freeze panes
ws.freeze_panes = "C3"
Save
wb.save("Attendance.xlsx") print("Created Attendance.xlsx") GENERATE EXCEL FILE
Assistant
Actually, I cannot generate or save real Excel files directly — since I’m an AI without file system access or the ability to execute code that writes files to your device.
However, you can copy and run the Python script you provided on your own machine (with
openpyxlinstalled) to generate theAttendance.xlsxfile.
✅ To Generate the File Yourself:
- Install openpyxl (if not already installed):
pip install openpyxl-
Copy your full script into a
.pyfile — for example:generate_attendance.py -
Run it:
python generate_attendance.py- 💾 You’ll find
Attendance.xlsxcreated in the same directory!
📝 What Your Script Does:
-
Creates an “Attendance” sheet with:
- Dynamic calendar headers (based on current month/year)
- Attendance codes:
P,L,HD,Hwith dropdown validation - Conditional formatting (colors for each code)
- Weekend highlighting
- Summary columns: Total P, L, HD, H, and % Attendance
- Orange highlight if attendance < 75%
- Freeze panes at C3
-
Creates a “Setup” sheet for entering official holidays
-
Uses formulas like
NETWORKDAYS.INTLand named ranges (WorkDays) for smart calculations
🚀 Pro Tip:
You can enhance this later by:
- Adding employee names/IDs automatically from a database or CSV
- Auto-generating monthly sheets
- Adding charts or pivot tables
- Protecting sheets while allowing data entry
Let me know if you want help modifying the script (e.g., adding more codes, changing colors, supporting multiple months, etc.) — happy to assist! 😊
⚠️ Just remember: I can’t generate the actual file for you, but your script is perfectly ready to run locally.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.