import os
import pandas as pd

def merge_excel_files(input_folder, output_file):
    """
    合并指定文件夹下所有Excel文件到同一个工作表
    :param input_folder: 存放Excel文件的文件夹路径
    :param output_file: 合并后输出的Excel文件路径
    """
    # 获取文件夹下所有xlsx文件
    excel_files = [f for f in os.listdir(input_folder) if f.endswith('.xlsx')]
    if not excel_files:
        print("文件夹中没有Excel文件")
        return
    
    # 存储所有数据
    all_data = []
    for file in excel_files:
        file_path = os.path.join(input_folder, file)
        df = pd.read_excel(file_path)
        # 添加来源文件名列
        df['来源文件'] = file
        all_data.append(df)
    
    # 合并所有数据
    merged_df = pd.concat(all_data, ignore_index=True)
    merged_df.to_excel(output_file, index=False)
    print(f"合并完成，共处理{len(excel_files)}个文件，输出文件：{output_file}")

# 使用示例
merge_excel_files('./excel_files', './merged_result.xlsx')

import pandas as pd

def extract_excel_columns(input_file, output_file, columns):
    """
    提取Excel文件中指定列的数据
    :param input_file: 输入Excel文件路径
    :param output_file: 输出Excel文件路径
    :param columns: 需要提取的列名列表，例如['姓名', '电话', '地址']
    """
    df = pd.read_excel(input_file)
    # 检查列是否存在
    missing_columns = [col for col in columns if col not in df.columns]
    if missing_columns:
        print(f"错误：文件中不存在以下列：{missing_columns}")
        return
    
    extracted_df = df[columns]
    extracted_df.to_excel(output_file, index=False)
    print(f"提取完成，共提取{len(extracted_df)}条数据")

# 使用示例
extract_excel_columns('data.xlsx', 'extracted_data.xlsx', ['姓名', '手机号', '邮箱'])

import pandas as pd

def deduplicate_excel(input_file, output_file, subset_columns=None):
    """
    Excel数据去重
    :param input_file: 输入Excel文件路径
    :param output_file: 输出去重后的Excel文件路径
    :param subset_columns: 按指定列去重，None表示按所有列去重
    """
    df = pd.read_excel(input_file)
    original_count = len(df)
    # 去重
    df_deduplicated = df.drop_duplicates(subset=subset_columns, keep='first')
    deduplicated_count = len(df_deduplicated)
    
    df_deduplicated.to_excel(output_file, index=False)
    print(f"去重完成，原数据{original_count}条，去重后{deduplicated_count}条，删除了{original_count - deduplicated_count}条重复数据")

# 使用示例
deduplicate_excel('user_data.xlsx', 'user_data_deduplicated.xlsx', subset_columns=['手机号'])

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill

def generate_excel_report(data, output_file, title="销售报表"):
    """
    生成带格式的Excel报表
    :param data: 报表数据，列表格式，第一个元素是表头
    :param output_file: 输出文件路径
    :param title: 报表标题
    """
    wb = Workbook()
    ws = wb.active
    ws.title = "报表"
    
    # 写入标题
    ws.merge_cells('A1:D1')
    title_cell = ws['A1']
    title_cell.value = title
    title_cell.font = Font(size=16, bold=True)
    title_cell.alignment = Alignment(horizontal='center', vertical='center')
    ws.row_dimensions[1].height = 30
    
    # 表头样式
    header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
    header_font = Font(color='FFFFFF', bold=True)
    
    # 写入数据
    for row_idx, row_data in enumerate(data, start=2):
        for col_idx, value in enumerate(row_data, start=1):
            cell = ws.cell(row=row_idx, column=col_idx, value=value)
            # 表头样式
            if row_idx == 2:
                cell.fill = header_fill
                cell.font = header_font
                cell.alignment = Alignment(horizontal='center')
    
    # 自动调整列宽
    for col in ws.columns:
        max_length = 0
        column = col[0].column_letter
        for cell in col:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        adjusted_width = (max_length + 2) * 1.2
        ws.column_dimensions[column].width = adjusted_width
    
    wb.save(output_file)
    print(f"报表生成成功：{output_file}")

# 使用示例
report_data = [
    ['日期', '产品名称', '销量', '销售额'],
    ['2026-01-01', '产品A', 100, 5000],
    ['2026-01-02', '产品B', 80, 4000],
    ['2026-01-03', '产品C', 120, 7200]
]
generate_excel_report(report_data, 'sales_report.xlsx', '2026年第一季度销售报表')

from docx import Document
from docx.shared import Pt
import pandas as pd

def generate_contracts(template_path, data_file, output_folder):
    """
    批量生成Word合同文档
    :param template_path: Word模板文件路径，模板中占位符使用{{变量名}}格式
    :param data_file: 包含合同数据的Excel文件路径
    :param output_folder: 生成合同的输出文件夹
    """
    import os
    os.makedirs(output_folder, exist_ok=True)
    
    # 读取数据
    df = pd.read_excel(data_file)
    
    for _, row in df.iterrows():
        doc = Document(template_path)
        # 替换模板中的占位符
        for paragraph in doc.paragraphs:
            for key, value in row.items():
                placeholder = f'{{{{{key}}}}}'
                if placeholder in paragraph.text:
                    paragraph.text = paragraph.text.replace(placeholder, str(value))
                    # 设置字体
                    for run in paragraph.runs:
                        run.font.size = Pt(12)
                        run.font.name = '宋体'
        
        # 保存文档，以客户姓名命名
        output_path = os.path.join(output_folder, f'合同_{row["客户姓名"]}.docx')
        doc.save(output_path)
        print(f"已生成合同：{output_path}")

# 使用示例
generate_contracts('合同模板.docx', '客户数据.xlsx', '生成的合同')

from docx2pdf import convert
import os

def batch_docx_to_pdf(input_folder, output_folder):
    """
    批量将Word文档转换为PDF格式
    :param input_folder: 存放Word文档的文件夹
    :param output_folder: 输出PDF的文件夹
    """
    os.makedirs(output_folder, exist_ok=True)
    docx_files = [f for f in os.listdir(input_folder) if f.endswith('.docx')]
    
    for file in docx_files:
        input_path = os.path.join(input_folder, file)
        output_path = os.path.join(output_folder, file.replace('.docx', '.pdf'))
        convert(input_path, output_path)
        print(f"已转换：{file} -> {os.path.basename(output_path)}")

# 使用示例
batch_docx_to_pdf('./word_files', './pdf_files')

from PyPDF2 import PdfMerger
import os

def merge_pdfs(input_folder, output_file):
    """
    合并文件夹下所有PDF文件
    :param input_folder: 存放PDF文件的文件夹
    :param output_file: 合并后的输出PDF文件路径
    """
    merger = PdfMerger()
    pdf_files = sorted([f for f in os.listdir(input_folder) if f.endswith('.pdf')])
    
    for pdf in pdf_files:
        pdf_path = os.path.join(input_folder, pdf)
        merger.append(pdf_path)
        print(f"已添加：{pdf}")
    
    merger.write(output_file)
    merger.close()
    print(f"合并完成，输出文件：{output_file}")

# 使用示例
merge_pdfs('./pdf_files', 'merged.pdf')

from PyPDF2 import PdfReader, PdfWriter
import os

def encrypt_pdfs(input_folder, output_folder, password):
    """
    批量给PDF文件添加密码保护
    :param input_folder: 输入PDF文件夹
    :param output_folder: 输出加密PDF文件夹
    :param password: 加密密码
    """
    os.makedirs(output_folder, exist_ok=True)
    pdf_files = [f for f in os.listdir(input_folder) if f.endswith('.pdf')]
    
    for file in pdf_files:
        input_path = os.path.join(input_folder, file)
        output_path = os.path.join(output_folder, f'加密_{file}')
        
        reader = PdfReader(input_path)
        writer = PdfWriter()
        
        for page in reader.pages:
            writer.add_page(page)
        
        writer.encrypt(password)
        with open(output_path, 'wb') as f:
            writer.write(f)
        print(f"已加密：{file}")

# 使用示例
encrypt_pdfs('./pdf_files', './encrypted_pdfs', '123456')

from PyPDF2 import PdfReader

def extract_pdf_text(pdf_path, output_txt_path):
    """
    提取PDF文件中的所有文本内容
    :param pdf_path: 输入PDF文件路径
    :param output_txt_path: 输出文本文件路径
    """
    reader = PdfReader(pdf_path)
    all_text = ""
    
    for page_num, page in enumerate(reader.pages, 1):
        text = page.extract_text()
        all_text += f"===== 第{page_num}页 =====\n{text}\n\n"
    
    with open(output_txt_path, 'w', encoding='utf-8') as f:
        f.write(all_text)
    print(f"文本提取完成，输出文件：{output_txt_path}")

# 使用示例
extract_pdf_text('document.pdf', 'pdf_text.txt')

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
import pandas as pd
import os
from dotenv import load_dotenv

# 加载环境变量，避免硬编码密码
load_dotenv()

def send_batch_emails(data_file, attachment_folder=None):
    """
    批量发送带附件的邮件
    :param data_file: 包含收件人信息的Excel文件，需要包含收件人邮箱、姓名等列
    :param attachment_folder: 附件文件夹，附件名需要和收件人姓名对应
    """
    # 邮箱配置
    smtp_server = 'smtp.qq.com'
    smtp_port = 587
    sender_email = os.getenv('SENDER_EMAIL')
    sender_password = os.getenv('SENDER_PASSWORD')  # QQ邮箱使用授权码
    
    # 读取收件人数据
    df = pd.read_excel(data_file)
    
    # 连接SMTP服务器
    server = smtplib.SMTP(smtp_server, smtp_port)
    server.starttls()
    server.login(sender_email, sender_password)
    
    for _, row in df.iterrows():
        # 创建邮件
        msg = MIMEMultipart()
        msg['From'] = sender_email
        msg['To'] = row['收件人邮箱']
        msg['Subject'] = f"{row['姓名']}您好，这是您的月度报表"
        
        # 邮件正文
        body = f"""
        尊敬的{row['姓名']}：
        您好！附件是您2026年1月的月度工作报表，请查收。
        如有任何问题，请随时联系。
        
        此致
        敬礼
        人力资源部
        """
        msg.attach(MIMEText(body, 'plain', 'utf-8'))
        
        # 添加附件
        if attachment_folder:
            attachment_path = os.path.join(attachment_folder, f"{row['姓名']}_报表.pdf")
            if os.path.exists(attachment_path):
                with open(attachment_path, 'rb') as f:
                    part = MIMEApplication(f.read())
                    part.add_header('Content-Disposition', 'attachment', filename=f"{row['姓名']}_报表.pdf")
                    msg.attach(part)
        
        # 发送邮件
        server.sendmail(sender_email, row['收件人邮箱'], msg.as_string())
        print(f"已发送邮件给：{row['姓名']} <{row['收件人邮箱']}>")
    
    server.quit()
    print("所有邮件发送完成")

# 使用示例
send_batch_emails('收件人列表.xlsx', './报表附件')

import os

def batch_rename_files(folder_path, prefix, start_num=1):
    """
    批量重命名文件夹下的所有文件
    :param folder_path: 目标文件夹路径
    :param prefix: 文件名前缀
    :param start_num: 起始编号
    """
    files = os.listdir(folder_path)
    # 过滤掉文件夹，只处理文件
    files = [f for f in files if os.path.isfile(os.path.join(folder_path, f))]
    
    for idx, filename in enumerate(files, start=start_num):
        # 获取文件扩展名
        file_ext = os.path.splitext(filename)[1]
        new_filename = f"{prefix}_{idx:03d}{file_ext}"
        old_path = os.path.join(folder_path, filename)
        new_path = os.path.join(folder_path, new_filename)
        os.rename(old_path, new_path)
        print(f"已重命名：{filename} -> {new_filename}")

# 使用示例
batch_rename_files('./images', '产品图片', start_num=1)

import os
import shutil

def organize_files_by_type(folder_path):
    """
    按文件类型整理文件夹中的文件，自动创建对应类型的文件夹
    :param folder_path: 需要整理的文件夹路径
    """
    # 定义文件类型和对应的文件夹名
    file_types = {
        '图片': ['.jpg', '.jpeg', '.png', '.gif', '.bmp', '.webp'],
        '文档': ['.doc', '.docx', '.xls', '.xlsx', '.ppt', '.pptx', '.pdf', '.txt'],
        '视频': ['.mp4', '.avi', '.mov', '.mkv', '.flv'],
        '音频': ['.mp3', '.wav', '.flac', '.aac'],
        '压缩包': ['.zip', '.rar', '.7z', '.tar', '.gz'],
        '程序': ['.exe', '.msi', '.dmg', '.pkg']
    }
    
    # 创建分类文件夹
    for folder in file_types.keys():
        folder_full_path = os.path.join(folder_path, folder)
        os.makedirs(folder_full_path, exist_ok=True)
    # 创建其他文件夹
    other_folder = os.path.join(folder_path, '其他')
    os.makedirs(other_folder, exist_ok=True)
    
    # 遍历文件
    for filename in os.listdir(folder_path):
        file_path = os.path.join(folder_path, filename)
        if os.path.isfile(file_path):
            file_ext = os.path.splitext(filename)[1].lower()
            moved = False
            # 判断文件类型
            for folder, extensions in file_types.items():
                if file_ext in extensions:
                    target_path = os.path.join(folder_path, folder, filename)
                    shutil.move(file_path, target_path)
                    print(f"已移动：{filename} -> {folder}")
                    moved = True
                    break
            # 未知类型移动到其他文件夹
            if not moved:
                target_path = os.path.join(other_folder, filename)
                shutil.move(file_path, target_path)
                print(f"已移动：{filename} -> 其他")

# 使用示例
organize_files_by_type('./downloads')

import pandas as pd
import matplotlib.pyplot as plt

def analyze_sales_data(input_file, output_report):
    """
    分析销售数据并生成统计报表
    :param input_file: 销售数据Excel文件路径
    :param output_report: 输出分析报告路径
    """
    # 读取数据
    df = pd.read_excel(input_file)
    
    # 计算总销售额
    total_sales = df['销售额'].sum()
    # 按产品统计销售额
    product_sales = df.groupby('产品名称')['销售额'].sum().sort_values(ascending=False)
    # 按月统计销售额
    df['月份'] = pd.to_datetime(df['销售日期']).dt.month
    monthly_sales = df.groupby('月份')['销售额'].sum()
    
    # 生成分析报告
    report = f"""
# 销售数据分析报告
## 一、总体情况
- 总销售额：{total_sales:.2f}元
- 总订单数：{len(df)}单
- 销售产品种类：{len(df['产品名称'].unique())}种

## 二、产品销售排行
{product_sales.to_markdown()}

## 三、月度销售趋势
{monthly_sales.to_markdown()}
    """
    
    # 保存报告
    with open(output_report, 'w', encoding='utf-8') as f:
        f.write(report)
    
    # 生成销售趋势图
    plt.figure(figsize=(10, 6))
    monthly_sales.plot(kind='line', marker='o')
    plt.title('月度销售趋势')
    plt.xlabel('月份')
    plt.ylabel('销售额')
    plt.grid(True)
    plt.savefig('月度销售趋势.png')
    
    print(f"分析报告已生成：{output_report}")

# 使用示例
analyze_sales_data('销售数据.xlsx', '销售分析报告.md')

import pandas as pd
from datetime import datetime

def calculate_attendance(attendance_file, output_file):
    """
    统计员工考勤数据，计算迟到、早退、旷工次数
    :param attendance_file: 考勤打卡记录Excel文件
    :param output_file: 输出统计结果文件
    """
    df = pd.read_excel(attendance_file)
    work_start_time = datetime.strptime('09:00:00', '%H:%M:%S').time()
    work_end_time = datetime.strptime('18:00:00', '%H:%M:%S').time()
    
    # 转换时间格式
    df['上班打卡时间'] = pd.to_datetime(df['上班打卡时间']).dt.time
    df['下班打卡时间'] = pd.to_datetime(df['下班打卡时间']).dt.time
    
    # 统计考勤状态
    def get_attendance_status(row):
        status = []
        if pd.isna(row['上班打卡时间']) or row['上班打卡时间'] > work_start_time:
            status.append('迟到')
        if pd.isna(row['下班打卡时间']) or row['下班打卡时间'] < work_end_time:
            status.append('早退')
        if pd.isna(row['上班打卡时间']) and pd.isna(row['下班打卡时间']):
            status = ['旷工']
        return '、'.join(status) if status else '正常'
    
    df['考勤状态'] = df.apply(get_attendance_status, axis=1)
    
    # 按员工统计
    attendance_stats = df.groupby(['员工姓名', '考勤状态']).size().unstack(fill_value=0)
    attendance_stats.to_excel(output_file)
    print(f"考勤统计完成，输出文件：{output_file}")

# 使用示例
calculate_attendance('考勤记录.xlsx', '考勤统计结果.xlsx')

import schedule
import time
import smtplib
from email.mime.text import MIMEText
from dotenv import load_dotenv
import os

load_dotenv()

def send_daily_reminder():
    """发送每日工作提醒邮件"""
    sender_email = os.getenv('SENDER_EMAIL')
    sender_password = os.getenv('SENDER_PASSWORD')
    receiver_email = 'team@company.com'
    
    msg = MIMEText("""
    各位同事：
    大家好！请记得在18:00前提交今日工作日报，谢谢配合！
    
    行政部
    """, 'plain', 'utf-8')
    msg['From'] = sender_email
    msg['To'] = receiver_email
    msg['Subject'] = '每日工作提醒：请提交今日日报'
    
    with smtplib.SMTP_SSL('smtp.qq.com', 465) as server:
        server.login(sender_email, sender_password)
        server.sendmail(sender_email, receiver_email, msg.as_string())
    
    print(f"{time.strftime('%Y-%m-%d %H:%M:%S')} 提醒邮件已发送")

# 设置定时任务：每天17:30发送提醒
schedule.every().day.at("17:30").do(send_daily_reminder)

print("定时任务已启动，每天17:30发送提醒邮件...")
while True:
    schedule.run_pending()
    time.sleep(60)

import schedule
import time
import shutil
import os
from datetime import datetime

def backup_files():
    """自动备份重要文件到指定目录"""
    source_folder = '/path/to/important/files'
    backup_folder = '/path/to/backup'
    # 按日期创建备份文件夹
    date_str = datetime.now().strftime('%Y%m%d')
    backup_path = os.path.join(backup_folder, f'backup_{date_str}')
    
    # 复制文件夹
    shutil.copytree(source_folder, backup_path)
    print(f"{time.strftime('%Y-%m-%d %H:%M:%S')} 文件备份完成，备份路径：{backup_path}")

# 设置定时任务：每周日晚上22点执行备份
schedule.every().sunday.at("22:00").do(backup_files)

print("备份任务已启动，每周日22:00自动备份文件...")
while True:
    schedule.run_pending()
    time.sleep(60)

import qrcode
import pandas as pd
import os

def batch_generate_qr_codes(data_file, output_folder):
    """
    批量生成二维码图片
    :param data_file: 包含二维码内容的Excel文件，需要包含'内容'和'文件名'列
    :param output_folder: 输出二维码图片的文件夹
    """
    os.makedirs(output_folder, exist_ok=True)
    df = pd.read_excel(data_file)
    
    for _, row in df.iterrows():
        qr = qrcode.QRCode(
            version=1,
            error_correction=qrcode.constants.ERROR_CORRECT_L,
            box_size=10,
            border=4,
        )
        qr.add_data(row['内容'])
        qr.make(fit=True)
        
        img = qr.make_image(fill_color='black', back_color='white')
        output_path = os.path.join(output_folder, f"{row['文件名']}.png")
        img.save(output_path)
        print(f"已生成二维码：{output_path}")

# 使用示例
batch_generate_qr_codes('二维码内容.xlsx', './qrcodes')

from PIL import Image
import os

def compress_images(input_folder, output_folder, quality=85):
    """
    批量压缩图片文件
    :param input_folder: 输入图片文件夹
    :param output_folder: 输出压缩后图片的文件夹
    :param quality: 压缩质量，0-100，数值越高质量越好
    """
    os.makedirs(output_folder, exist_ok=True)
    image_extensions = ['.jpg', '.jpeg', '.png']
    
    for filename in os.listdir(input_folder):
        file_ext = os.path.splitext(filename)[1].lower()
        if file_ext in image_extensions:
            input_path = os.path.join(input_folder, filename)
            output_path = os.path.join(output_folder, filename)
            
            with Image.open(input_path) as img:
                # 保存时压缩
                img.save(output_path, optimize=True, quality=quality)
            
            original_size = os.path.getsize(input_path) / 1024
            compressed_size = os.path.getsize(output_path) / 1024
            print(f"已压缩：{filename}，原大小：{original_size:.1f}KB，压缩后：{compressed_size:.1f}KB，压缩比：{(1 - compressed_size/original_size)*100:.1f}%")

# 使用示例
compress_images('./original_images', './compressed_images', quality=80)

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

def generate_data_visualization(data_file, output_folder):
    """
    自动生成常见的数据可视化图表
    :param data_file: 数据文件路径
    :param output_folder: 输出图表的文件夹
    """
    os.makedirs(output_folder, exist_ok=True)
    df = pd.read_excel(data_file)
    
    # 柱状图：各产品销量对比
    plt.figure(figsize=(12, 6))
    sns.barplot(data=df, x='产品名称', y='销量', palette='viridis')
    plt.title('各产品销量对比')
    plt.xticks(rotation=45)
    plt.tight_layout()
    plt.savefig(os.path.join(output_folder, '产品销量对比.png'))
    
    # 饼图：销售额占比
    plt.figure(figsize=(8, 8))
    product_sales = df.groupby('产品名称')['销售额'].sum()
    plt.pie(product_sales, labels=product_sales.index, autopct='%1.1f%%', startangle=90)
    plt.title('各产品销售额占比')
    plt.tight_layout()
    plt.savefig(os.path.join(output_folder, '销售额占比.png'))
    
    # 折线图：销售趋势
    plt.figure(figsize=(12, 6))
    df['销售日期'] = pd.to_datetime(df['销售日期'])
    daily_sales = df.groupby('销售日期')['销售额'].sum()
    plt.plot(daily_sales.index, daily_sales.values, marker='o')
    plt.title('每日销售趋势')
    plt.xlabel('日期')
    plt.ylabel('销售额')
    plt.xticks(rotation=45)
    plt.grid(True)
    plt.tight_layout()
    plt.savefig(os.path.join(output_folder, '销售趋势.png'))
    
    print(f"图表生成完成，已保存到：{output_folder}")

# 使用示例
generate_data_visualization('销售数据.xlsx', './charts')

import requests
from bs4 import BeautifulSoup
import pandas as pd

def scrape_website_data(url, output_file):
    """
    批量提取网页表格数据
    :param url: 目标网页URL
    :param output_file: 输出Excel文件路径
    """
    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36'
    }
    
    response = requests.get(url, headers=headers)
    soup = BeautifulSoup(response.text, 'html.parser')
    
    # 提取网页中的表格
    table = soup.find('table')
    if not table:
        print("网页中没有找到表格")
        return
    
    # 解析表格数据
    rows = []
    for tr in table.find_all('tr'):
        row = [td.get_text(strip=True) for td in tr.find_all(['th', 'td'])]
        if row:
            rows.append(row)
    
    # 保存为Excel
    df = pd.DataFrame(rows[1:], columns=rows[0])
    df.to_excel(output_file, index=False)
    print(f"数据提取完成，共提取{len(df)}条数据，输出文件：{output_file}")

# 使用示例
scrape_website_data('https://example.com/table-page', '网页数据.xlsx')