1

2

3

4

5

6

7

8

9

10

import pandas as pd 

# 批量填充指定列的单元格 

def fill_column(file_path, column_name, value): 

    df = pd.read_excel(file_path) 

    df[column_name] = value  # 将指定列的所有单元格填充为value 

    df.to_excel(file_path, index=False) 

fill_column('example.xlsx', '备注', '已处理') 

print("备注列已成功填充！")

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

from openpyxl import load_workbook 

# 设置Excel的行高与列宽 

def set_row_column_size(file_path): 

    wb = load_workbook(file_path) 

    ws = wb.active 

    # 设置第一行行高、第一列列宽 

    ws.row_dimensions[1].height = 30  # 设置行高 

    ws.column_dimensions['A'].width = 20  # 设置列宽 

    wb.save(file_path) 

set_row_column_size('example.xlsx') 

print("行高和列宽设置成功！")

1

2

3

4

5

6

7

8

# 根据条件删除Excel中的行 

def delete_rows_based_on_condition(file_path, column_name, condition): 

    df = pd.read_excel(file_path) 

    df = df[df[column_name] != condition]  # 删除满足条件的行 

    df.to_excel(file_path, index=False) 

delete_rows_based_on_condition('example.xlsx', '状态', '无效') 

print("符合条件的行已删除！")

1

2

3

4

5

6

7

8

# 在现有Excel文件中创建新的工作表 

def create_new_sheet(file_path, sheet_name): 

    wb = load_workbook(file_path) 

    wb.create_sheet(title=sheet_name)  # 创建新的工作表 

    wb.save(file_path) 

create_new_sheet('example.xlsx', '新工作表') 

print("新工作表创建成功！")

1

2

3

4

5

6

7

# 将CSV文件导入到Excel工作表 

def import_csv_to_excel(csv_file, excel_file): 

    df = pd.read_csv(csv_file) 

    df.to_excel(excel_file, index=False) 

import_csv_to_excel('data.csv', 'imported_data.xlsx') 

print("CSV文件成功导入到Excel！")

1

2

3

4

5

6

7

8

# 生成数据透视表并保存到新的Excel文件 

def generate_pivot_table(file_path, index_column, values_column, output_file): 

    df = pd.read_excel(file_path) 

    pivot_table = df.pivot_table(index=index_column, values=values_column, aggfunc='sum')  # 汇总 

    pivot_table.to_excel(output_file) 

generate_pivot_table('sales_data.xlsx', '地区', '销售额', 'pivot_output.xlsx') 

print("透视表生成成功！")

1

2

3

4

5

6

7

8

9

10

11

12

13

14

from openpyxl.styles import Font, Color 

# 设置Excel单元格字体样式 

def format_cells(file_path): 

    wb = load_workbook(file_path) 

    ws = wb.active 

    for cell in ws['A']:  # 遍历A列 

        cell.font = Font(bold=True, color="FF0000")  # 设置字体加粗和红色 

    wb.save(file_path) 

format_cells('example.xlsx') 

print("单元格格式化成功！")

1

2

3

4

5

6

7

8

# 输出描述性统计到Excel 

def descriptive_statistics(file_path, output_file): 

    df = pd.read_excel(file_path) 

    stats = df.describe()  # 计算描述性统计 

    stats.to_excel(output_file) 

descriptive_statistics('example.xlsx', 'statistics_output.xlsx') 

print("描述性统计输出成功！")

1

2

3

4

5

6

7

8

9

10

11

import os 

# 批量重命名指定目录下的Excel文件 

def rename_excel_files(directory, prefix): 

    for filename in os.listdir(directory): 

        if filename.endswith('.xlsx'): 

            new_name = f"{prefix}_{filename}" 

            os.rename(os.path.join(directory, filename), os.path.join(directory, new_name)) 

            print(f"已将 {filename} 重命名为 {new_name}") 

rename_excel_files('/path/to/excel/files', '2024')

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

import smtplib 

from email.mime.multipart import MIMEMultipart 

from email.mime.application import MIMEApplication 

from email.mime.text import MIMEText 

# 自动发送带有Excel附件的电子邮件 

def send_email(to_address, subject, body, excel_file): 

    from_address = "your_email@example.com" 

    password = "your_password" 

    msg = MIMEMultipart() 

    msg['From'] = from_address 

    msg['To'] = to_address 

    msg['Subject'] = subject 

    # 添加正文 

    msg.attach(MIMEText(body, 'plain')) 

    # 添加Excel附件 

    with open(excel_file, "rb") as attachment: 

        part = MIMEApplication(attachment.read(), Name=os.path.basename(excel_file)) 

        part['Content-Disposition'] = f'attachment; filename="{os.path.basename(excel_file)}"' 

        msg.attach(part) 

    # 发送邮件 

    with smtplib.SMTP('smtp.example.com', 587) as server: 

        server.starttls() 

        server.login(from_address, password) 

        server.send_message(msg) 

send_email('recipient@example.com', 'Monthly Report', 'Please find attached the monthly report.', 'report.xlsx') 

print("邮件发送成功！")