name: week_report_gen description: "Automated weekly report generation from exported Excel time tracking data. Transforms cost report exports into formatted weekly project reports following company templates. Use when generating 項目週報 (project weekly reports) from time tracking data exported from project management systems." license: MIT

Weekly Report Generator Skill

Overview

This skill automates the creation of weekly project reports (項目週報) from time tracking data exported from project management systems. It reads Excel cost reports, aggregates work hours by person and project, and generates formatted weekly reports following the company's standard template.

When to Use This Skill

Use this skill when:

User wants to generate a weekly report (週報) from time tracking data
User has an exported cost report Excel file (.xls or .xlsx)
User needs to create 項目週報 in the standard company format
User mentions transforming time tracking data into weekly reports

Input Format

Input File: Cost Report Excel file (e.g., cost-report-2026-01-16-T-16-22-3620260116-7-1r1n4h.xls)

Expected columns in the input file:

日期 - Date of the work entry
使用者 - User/person name
活動 - Activity type (Development, Testing, Specification, Support, etc.)
專案 - Project name
留言 - Comments/notes (optional)
單位 - Work hours/units
費用類別 - Cost category
費用 - Cost amount

Output Format

Output File: Weekly Report Excel file (e.g., 項目週報-台灣-軟體部-智能控制組 (20260115).xlsx)

The output follows the company's standard 項目週報 template with these columns:

專案名稱 - Project name
子項目名稱 - Sub-project name
進度 - Progress (percentage 0-1)
本周主要進展 (請條列說明) - This week's main progress (bullet points)
參與人員 - Participants
工时（个人投入研发工时 - Work hours invested
交付物 - Deliverables
代碼（如有）是否上傳 - Code uploaded (Y/N)
下周計畫 - Next week's plan

Processing Workflow

Step 1: Read and Parse Input Data

import pandas as pd
import openpyxl
from datetime import datetime

# Read the cost report Excel file
def read_cost_report(file_path):
    """
    Read cost report and parse the data.
    Skip header row if present.
    """
    # Try reading with header=1 (skip first row if it's a title)
    df = pd.read_excel(file_path, header=1)
    
    # Verify expected columns exist
    required_cols = ['日期', '使用者', '活動', '專案', '單位']
    if not all(col in df.columns for col in required_cols):
        # Try header=0 if header=1 doesn't work
        df = pd.read_excel(file_path, header=0)
    
    # Clean data: remove rows with missing critical values
    df = df.dropna(subset=['使用者', '專案', '單位'])
    
    return df

Step 2: Aggregate Data by Person and Project

def aggregate_work_hours(df):
    """
    Group work hours by user and project.
    Returns a summary DataFrame.
    """
    # Extract date range for report period
    df['日期'] = pd.to_datetime(df['日期'])
    start_date = df['日期'].min()
    end_date = df['日期'].max()
    
    # Group by user and project
    summary = df.groupby(['使用者', '專案', '活動']).agg({
        '單位': 'sum',
        '留言': lambda x: '\n'.join(x.dropna().unique()) if '留言' in df.columns else ''
    }).reset_index()
    
    summary.columns = ['使用者', '專案', '活動', '工時', '備註']
    
    # Sort by user and hours (descending)
    summary = summary.sort_values(['使用者', '工時'], ascending=[True, False])
    
    return summary, start_date, end_date

Step 3: Format Data for Weekly Report

def format_for_weekly_report(summary_df):
    """
    Transform aggregated data into weekly report format.
    Group entries by project and list participants.
    """
    # Group by project
    report_data = []
    
    for project in summary_df['專案'].unique():
        project_data = summary_df[summary_df['專案'] == project]
        
        # Get all participants and their hours
        participants = []
        total_hours = 0
        activities = []
        notes = []
        
        for _, row in project_data.iterrows():
            participants.append(f"{row['使用者']}")
            total_hours += row['工時']
            if row['活動'] and row['活動'] != '-':
                activities.append(row['活動'])
            if row['備註']:
                notes.append(row['備註'])
        
        # Format main progress
        progress_text = '\n'.join(set(notes)) if notes else ''
        if activities:
            activity_text = '\n'.join(set(activities))
            progress_text = f"{activity_text}\n{progress_text}" if progress_text else activity_text
        
        report_data.append({
            '專案名稱': project,
            '子項目名稱': None,
            '進度': None,  # User should fill this in
            '本周主要進展': progress_text,
            '參與人員': ', '.join(set(participants)),
            '工時': total_hours,
            '交付物': None,
            '代碼上傳': None,
            '下周計畫': None
        })
    
    return pd.DataFrame(report_data)

Step 4: Generate Excel Output with Template

def generate_weekly_report(input_file, output_file, template_file=None):
    """
    Main function to generate weekly report from cost report.
    
    Args:
        input_file: Path to input cost report Excel file
        output_file: Path to output weekly report Excel file
        template_file: Optional path to template file (if not using default)
    """
    # Read and process input
    df = read_cost_report(input_file)
    summary, start_date, end_date = aggregate_work_hours(df)
    report_df = format_for_weekly_report(summary)
    
    # Load template or create new workbook
    if template_file and os.path.exists(template_file):
        wb = openpyxl.load_workbook(template_file)
        # Create new sheet for this week
        sheet_name = f"{end_date.strftime('%Y%m%d')}-智能控制組"
        ws = wb.create_sheet(sheet_name)
    else:
        wb = openpyxl.Workbook()
        ws = wb.active
        sheet_name = f"{end_date.strftime('%Y%m%d')}-週報"
        ws.title = sheet_name
    
    # Write header
    ws['B1'] = '弘訊科技股份有限公司'
    ws['B2'] = f"2026年度週報({start_date.strftime('%m月%d日')}-{end_date.strftime('%m月%d日')}）"
    ws['B3'] = '建議用可視化素材（如有）輔助報告：設計圖、產品架構圖、產品實物照片等等相關材料'
    
    # Write column headers (row 4)
    headers = ['專案名稱', '子項目名稱', '進度', '本周主要進展 (請條列說明)', 
               '參與人員', '工时（个人投入研发工时', '交付物', '代碼（如有）是否上傳', '下周計畫']
    for col_idx, header in enumerate(headers, start=2):  # Start from column B
        ws.cell(row=4, column=col_idx, value=header)
    
    # Write data starting from row 5
    current_row = 5
    for _, row_data in report_df.iterrows():
        ws.cell(row=current_row, column=2, value=row_data['專案名稱'])
        ws.cell(row=current_row, column=3, value=row_data['子項目名稱'])
        ws.cell(row=current_row, column=4, value=row_data['進度'])
        ws.cell(row=current_row, column=5, value=row_data['本周主要進展'])
        ws.cell(row=current_row, column=6, value=row_data['參與人員'])
        ws.cell(row=current_row, column=7, value=row_data['工時'])
        ws.cell(row=current_row, column=8, value=row_data['交付物'])
        ws.cell(row=current_row, column=9, value=row_data['代碼上傳'])
        ws.cell(row=current_row, column=10, value=row_data['下周計畫'])
        current_row += 1
        
        # Add separator row
        current_row += 1
        ws.cell(row=current_row, column=2, value='專案名稱')
        for col_idx, header in enumerate(headers[1:], start=3):
            ws.cell(row=current_row, column=col_idx, value=header)
        current_row += 1
    
    # Apply formatting
    from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
    
    # Header formatting
    header_fill = PatternFill(start_color='D3D3D3', end_color='D3D3D3', fill_type='solid')
    bold_font = Font(bold=True)
    center_align = Alignment(horizontal='center', vertical='center', wrap_text=True)
    
    # Format title rows
    ws['B1'].font = Font(bold=True, size=14)
    ws['B1'].alignment = center_align
    ws['B2'].font = Font(bold=True, size=12)
    ws['B3'].font = Font(size=10, italic=True)
    
    # Format column headers
    for col_idx in range(2, 11):
        cell = ws.cell(row=4, column=col_idx)
        cell.fill = header_fill
        cell.font = bold_font
        cell.alignment = center_align
    
    # Set column widths
    column_widths = {
        'B': 20,  # 專案名稱
        'C': 20,  # 子項目名稱
        'D': 8,   # 進度
        'E': 35,  # 本周主要進展
        'F': 15,  # 參與人員
        'G': 12,  # 工時
        'H': 15,  # 交付物
        'I': 10,  # 代碼上傳
        'J': 20   # 下周計畫
    }
    
    for col, width in column_widths.items():
        ws.column_dimensions[col].width = width
    
    # Save workbook
    wb.save(output_file)
    
    return output_file, report_df

Usage Example

When the user provides a cost report file and wants to generate a weekly report:

# Example usage
input_file = 'skills/week_report_gen/references/cost-report-2026-01-16-T-16-22-3620260116-7-1r1n4h.xls'
output_file = 'skills/week_report_gen/references/項目週報-智能控制組-20260116.xlsx'
template_file = 'skills/week_report_gen/references/項目週報-模板.xlsx'

# Generate the report
output_path, summary_data = generate_weekly_report(
    input_file=input_file,
    output_file=output_file,
    template_file=template_file
)

print(f"Weekly report generated: {output_path}")
print("\nSummary:")
print(summary_data)

Important Notes

Data Validation: The script validates that required columns exist in the input file. If column names don't match, it will attempt different header configurations.
Date Range: The report automatically extracts the date range from the input data to generate the report period header.
Work Hours Aggregation: Hours are summed by person and project to provide accurate totals for the weekly report.
Manual Fields: Some fields like "進度" (Progress), "交付物" (Deliverables), and "下周計畫" (Next Week's Plan) are left blank for manual input, as they require subjective assessment.
Formatting: The output maintains the company's standard formatting including:
- Company header
- Date range
- Column structure
- Separator rows between projects
- Appropriate column widths
- Template colors and styles: When a template file is provided, all cell colors, fonts, borders, and other styling from the template's first 4 rows (headers) are automatically preserved in the output
Multiple Team Members: When multiple people work on the same project, their names are combined in the "參與人員" column and hours are summed.

Error Handling

The script includes error handling for common issues:

Missing or incorrect column names
Empty or invalid data
Missing template file (will create basic template)
Date parsing errors

Customization

You can customize the output by:

Modifying column widths in the column_widths dictionary
Adjusting header formatting styles
Changing the grouping logic (e.g., by activity type)
Adding additional calculated fields

Next Steps After Generation

After generating the weekly report:

Review the "本周主要進展" (Main Progress) entries - these are auto-populated from notes but may need editing
Fill in the "進度" (Progress) percentages for each project
Add specific "交付物" (Deliverables) if any
Indicate "代碼上傳" (Code Uploaded) status as Y/N
Plan and fill in "下周計畫" (Next Week's Plan) for each project
Add any visual materials mentioned in row 3 header

File Naming Convention

Output files should follow this pattern:

項目週報-[部門名稱]-[組別] ([YYYYMMDD]).xlsx

Example: 項目週報-台灣-軟體部-智能控制組 (20260115).xlsx

week-report-gen

SKILL.md