# Excel自动填充神技：告别重复录入效率提升100%

import pandas as pd
from openpyxl import load_workbook

def auto_fill_sequence(input_file, output_file):
    """自动填充序列号"""
    
    df = pd.read_excel(input_file)
    
    # 添加序列号列
    df.insert(0, '序号', range(1, len(df) + 1))
    
    # 添加日期编号（如 20240101-001）
    df['编号'] = df['序号'].apply(
        lambda x: f"{pd.Timestamp.now().strftime('%Y%m%d')}-{x:03d}"
    )
    
    df.to_excel(output_file, index=False)
    print(f"已填充 {len(df)} 条记录")

# 使用
auto_fill_sequence('待处理名单.xlsx', '已填充名单.xlsx')

def group_sequence(df, group_col, order_col):
    """按组自动编号"""
    df = df.sort_values([group_col, order_col])
    df['组内序号'] = df.groupby(group_col).cumcount() + 1
    df['总编号'] = df.groupby(group_col).ngroup() * 100 + df['组内序号']
    return df

# 使用示例：按部门编号员工
employees = pd.read_excel('员工表.xlsx')
result = group_sequence(employees, '部门', '入职日期')

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

def auto_fill_formulas(file_path):
    """自动填充公式"""
    wb = load_workbook(file_path)
    ws = wb.active
    
    # 获取数据范围
    last_row = ws.max_row
    last_col = ws.max_column
    
    # 在最后一列添加汇总公式
    sum_col = last_col + 1
    ws.cell(row=1, column=sum_col).value = '汇总'
    
    # 批量填充求和公式
    for row in range(2, last_row + 1):
        formula = f'=SUM(B{row}:{get_column_letter(last_col-1)}{row})'
        ws.cell(row=row, column=sum_col).value = formula
    
    # 添加平均公式
    avg_col = sum_col + 1
    ws.cell(row=1, column=avg_col).value = '平均值'
    
    for row in range(2, last_row + 1):
        formula = f'=AVERAGE(B{row}:{get_column_letter(last_col-1)}{row})'
        ws.cell(row=row, column=avg_col).value = formula
    
    wb.save(file_path)
    print(f"已添加 {last_row - 1} 行公式")

# 使用
auto_fill_formulas('销售数据.xlsx')

def conditional_formula(file_path):
    """根据条件填充不同公式"""
    wb = load_workbook(file_path)
    ws = wb.active
    
    for row in range(2, ws.max_row + 1):
        # 获取销售额
        sales = ws.cell(row=row, column=3).value
        
        # 根据销售额填充分类
        if sales and sales > 50000:
            ws.cell(row=row, column=5).value = '=C{row}*0.1'  # 提成10%
        elif sales and sales > 20000:
            ws.cell(row=row, column=5).value = '=C{row}*0.05'  # 提成5%
        else:
            ws.cell(row=row, column=5).value = '=C{row}*0.02'  # 提成2%
    
    wb.save(file_path)

import pandas as pd
from datetime import datetime, timedelta
import random

def auto_fill_employee():
    """模拟自动填充员工信息"""
    
    # 基础数据
    departments = ['技术部', '市场部', '财务部', '人力资源部', '运营部']
    positions = ['经理', '主管', '专员', '助理']
    
    # 生成工号
    def generate_emp_id(dept, index):
        dept_code = {'技术部': 'T', '市场部': 'M', '财务部': 'F', 
                     '人力资源部': 'H', '运营部': 'O'}
        return f"{dept_code.get(dept, 'X')}{datetime.now().year}{index:04d}"
    
    # 生成入职日期（近2年内随机）
    def random_date():
        start = datetime.now() - timedelta(days=730)
        return (start + timedelta(days=random.randint(0, 730))).strftime('%Y-%m-%d')
    
    # 创建员工数据
    employees = []
    for i in range(1, 51):
        dept = random.choice(departments)
        emp = {
            '工号': generate_emp_id(dept, i),
            '姓名': f'员工{i:03d}',
            '部门': dept,
            '职位': random.choice(positions),
            '入职日期': random_date(),
            '基本工资': random.randint(5000, 20000),
        }
        employees.append(emp)
    
    df = pd.DataFrame(employees)
    
    # 自动计算司龄
    df['入职日期'] = pd.to_datetime(df['入职日期'])
    df['司龄(年)'] = ((datetime.now() - df['入职日期']).dt.days / 365).round(1)
    
    # 自动计算年终奖基数
    df['年终奖基数'] = df['基本工资'] * 2
    
    # 保存
    df.to_excel('员工信息表_自动填充.xlsx', index=False)
    return df

# 使用
result = auto_fill_employee()
print(result.head(10))

import python-office

# 一键填充序列号
python-office.excel.fill_sequence(
    file_path=r'D:/员工表.xlsx',
    column='A',  # 填充到A列
    start=1,      # 从1开始
    prefix='EMP' # 工号前缀
)

from docx import Document
import pandas as pd

def batch_fill_certificate(template_path, data_file, output_folder):
    """批量填充证书模板"""
    
    df = pd.read_excel(data_file)
    
    for idx, row in df.iterrows():
        doc = Document(template_path)
        
        # 替换占位符
        for paragraph in doc.paragraphs:
            if '{姓名}' in paragraph.text:
                paragraph.text = paragraph.text.replace('{姓名}', str(row['姓名']))
            if '{日期}' in paragraph.text:
                paragraph.text = paragraph.text.replace('{日期}', str(row['发证日期']))
        
        # 保存
        output_path = f"{output_folder}/{row['姓名']}_证书.docx"
        doc.save(output_path)
    
    print(f"已生成 {len(df)} 份证书")

# 使用
batch_fill_certificate('证书模板.docx', '获奖名单.xlsx', 'D:/证书输出')

class ExcelAutoFill:
    """Excel自动填充工具类"""
    
    def __init__(self, file_path):
        self.file_path = file_path
        self.df = pd.read_excel(file_path)
    
    def fill_serial(self, column_name, start=1, prefix='', suffix=''):
        """序列填充"""
        self.df[column_name] = [
            f"{prefix}{i}{suffix}" for i in range(start, len(self.df) + start)
        ]
        return self
    
    def fill_date(self, column_name, start_date, end_date=None):
        """日期填充"""
        if end_date:
            dates = pd.date_range(start_date, end_date, periods=len(self.df))
        else:
            dates = pd.bdate_range(start_date, periods=len(self.df))
        self.df[column_name] = dates
        return self
    
    def fill_formula(self, column_name, formula_template):
        """公式填充"""
        self.df[column_name] = self.df.apply(
            lambda row: formula_template.format(**row), axis=1
        )
        return self
    
    def save(self, output_path=None):
        """保存结果"""
        path = output_path or self.file_path
        self.df.to_excel(path, index=False)
        print(f"已保存到: {path}")

# 使用示例
fill_tool = ExcelAutoFill('销售数据.xlsx')
fill_tool.fill_serial('序号') \
          .fill_date('录入日期', '2024-01-01') \
          .fill_formula('编号', '{部门}-{序号}') \
          .save('销售数据_填充后.xlsx')