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("備注列已成功填充！")

from openpyxl import load_workbook  

# 設(shè)置Excel的行高與列寬  
def set_row_column_size(file_path):  
    wb = load_workbook(file_path)  
    ws = wb.active  

    # 設(shè)置第一行行高、第一列列寬  
    ws.row_dimensions[1].height = 30  # 設(shè)置行高  
    ws.column_dimensions['A'].width = 20  # 設(shè)置列寬  

    wb.save(file_path)  

set_row_column_size('example.xlsx')  
print("行高和列寬設(shè)置成功！")

# 根據(jù)條件刪除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', '狀態(tài)', '無效')  
print("符合條件的行已刪除！")

# 在現(xiàn)有Excel文件中創(chuàng)建新的工作表  
def create_new_sheet(file_path, sheet_name):  
    wb = load_workbook(file_path)  
    wb.create_sheet(title=sheet_name)  # 創(chuàng)建新的工作表  
    wb.save(file_path)  

create_new_sheet('example.xlsx', '新工作表')  
print("新工作表創(chuàng)建成功！")

# 將CSV文件導(dǎo)入到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文件成功導(dǎo)入到Excel！")

# 生成數(shù)據(jù)透視表并保存到新的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', '地區(qū)', '銷售額', 'pivot_output.xlsx')  
print("透視表生成成功！")

from openpyxl.styles import Font, Color  

# 設(shè)置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")  # 設(shè)置字體加粗和紅色  

    wb.save(file_path)  

format_cells('example.xlsx')  
print("單元格格式化成功！")

# 輸出描述性統(tǒng)計(jì)到Excel  
def descriptive_statistics(file_path, output_file):  
    df = pd.read_excel(file_path)  
    stats = df.describe()  # 計(jì)算描述性統(tǒng)計(jì)  
    stats.to_excel(output_file)  

descriptive_statistics('example.xlsx', 'statistics_output.xlsx')  
print("描述性統(tǒng)計(jì)輸出成功！")

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')

import smtplib  
from email.mime.multipart import MIMEMultipart  
from email.mime.application import MIMEApplication  
from email.mime.text import MIMEText  

# 自動(dòng)發(fā)送帶有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)  

    # 發(fā)送郵件  
    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("郵件發(fā)送成功！")

import pandas as pd
import os

def merge_excel_files(folder_path, output_file):
    all_data = pd.DataFrame()
    for filename in os.listdir(folder_path):
        if filename.endswith('.xlsx'):
            file_path = os.path.join(folder_path, filename)
            df = pd.read_excel(file_path)
            all_data = pd.concat([all_data, df], ignore_index=True)
    all_data.to_excel(output_file, index=False)

merge_excel_files('your_folder_path', 'merged_file.xlsx')
print("多個(gè)Excel文件合并成功！")

import pandas as pd

def split_excel_file(file_path, column_name, output_folder):
    df = pd.read_excel(file_path)
    unique_values = df[column_name].unique()
    for value in unique_values:
        sub_df = df[df[column_name] == value]
        output_file = os.path.join(output_folder, f'{value}.xlsx')
        sub_df.to_excel(output_file, index=False)

split_excel_file('example.xlsx', '部門', 'output_folder')
print("Excel文件拆分成功！")

import pandas as pd

def replace_cell_content(file_path, column_name, old_value, new_value):
    df = pd.read_excel(file_path)
    df[column_name] = df[column_name].replace(old_value, new_value)
    df.to_excel(file_path, index=False)

replace_cell_content('example.xlsx', '產(chǎn)品名稱', '舊產(chǎn)品', '新產(chǎn)品')
print("單元格內(nèi)容替換成功！")

import pandas as pd

def sort_excel_data(file_path, column_name, ascending=True):
    df = pd.read_excel(file_path)
    df = df.sort_values(by=column_name, ascending=ascending)
    df.to_excel(file_path, index=False)

sort_excel_data('example.xlsx', '銷售額', ascending=False)
print("數(shù)據(jù)排序成功！")

import pandas as pd

def count_unique_values(file_path, column_name):
    df = pd.read_excel(file_path)
    unique_count = df[column_name].nunique()
    print(f"{column_name}列的唯一值數(shù)量為: {unique_count}")

count_unique_values('example.xlsx', '客戶編號(hào)')

import pandas as pd

def extract_columns(file_path, columns, output_file):
    df = pd.read_excel(file_path)
    new_df = df[columns]
    new_df.to_excel(output_file, index=False)

extract_columns('example.xlsx', ['姓名', '年齡'], 'extracted_columns.xlsx')
print("指定列提取成功！")

from openpyxl import load_workbook
from openpyxl.styles import Border, Side

def add_border_to_excel(file_path):
    wb = load_workbook(file_path)
    ws = wb.active
    thin_border = Border(left=Side(style='thin'), 
                         right=Side(style='thin'), 
                         top=Side(style='thin'), 
                         bottom=Side(style='thin'))
    for row in ws.iter_rows():
        for cell in row:
            cell.border = thin_border
    wb.save(file_path)

add_border_to_excel('example.xlsx')
print("表格邊框添加成功！")

import pandas as pd

def remove_empty_rows(file_path):
    df = pd.read_excel(file_path)
    df = df.dropna(how='all')
    df.to_excel(file_path, index=False)

remove_empty_rows('example.xlsx')
print("空行刪除成功！")

import pandas as pd

def filter_data_by_multiple_conditions(file_path, conditions, output_file):
    df = pd.read_excel(file_path)
    query_str = ' & '.join([f'{col} {op} {val}' for col, op, val in conditions])
    filtered_df = df.query(query_str)
    filtered_df.to_excel(output_file, index=False)

# 示例條件：年齡大于25且性別為女
conditions = [('年齡', '>', 25), ('性別', '==', "'女'")]
filter_data_by_multiple_conditions('example.xlsx', conditions, 'filtered_data.xlsx')
print("多條件篩選數(shù)據(jù)成功！")

import pandas as pd

def format_date_column(file_path, column_name, date_format):
    df = pd.read_excel(file_path)
    df[column_name] = pd.to_datetime(df[column_name]).dt.strftime(date_format)
    df.to_excel(file_path, index=False)

format_date_column('example.xlsx', '日期', '%Y-%m-%d')
print("日期列格式化成功！")