pip install openpyxl pandas xlwings

import openpyxl

# 打開 Excel 文件
workbook = openpyxl.load_workbook('data.xlsx')

# 選擇工作表
sheet = workbook.active

# 讀取單元格的值
cell_value = sheet['A1'].value
print(f'單元格 A1 的值為: {cell_value}')

# 遍歷整個工作表
for row in sheet.iter_rows(min_row=2, values_only=True):
    print(row)

import openpyxl

# 創(chuàng)建一個新的 Excel 工作簿
workbook = openpyxl.Workbook()

# 創(chuàng)建一個新的工作表
sheet = workbook.active
sheet.title = '數(shù)據(jù)'

# 寫入數(shù)據(jù)到單元格
sheet['A1'] = '姓名'
sheet['B1'] = '年齡'

data = [('Alice', 25), ('Bob', 30), ('Carol', 28)]

for row_index, (name, age) in enumerate(data, start=2):
    sheet[f'A{row_index}'] = name
    sheet[f'B{row_index}'] = age

# 保存工作簿到文件
workbook.save('result.xlsx')

import pandas as pd

# 從 Excel 文件讀取數(shù)據(jù)
df = pd.read_excel('data.xlsx')

# 打印前幾行數(shù)據(jù)
print(df.head())

# 進行數(shù)據(jù)分析操作
mean_age = df['年齡'].mean()
max_age = df['年齡'].max()

print(f'平均年齡: {mean_age}')
print(f'最大年齡: {max_age}')

import xlwings as xw

# 連接到 Excel
app = xw.App(visible=True, add_book=False)

# 打開 Excel 文件
workbook = app.books.open('data.xlsx')

# 選擇工作表
sheet = workbook.sheets['Sheet1']

# 讀取單元格的值
cell_value = sheet.range('A1').value
print(f'單元格 A1 的值為: {cell_value}')

# 在 Excel 中運行公式
sheet.range('B1').formula = '=SUM(B2:B4)'

# 從 Excel 中獲取數(shù)據(jù)到 Python
data_range = sheet.range('A2').expand('down').value
print('從 Excel 中獲取的數(shù)據(jù):')
print(data_range)

# 寫入數(shù)據(jù)到 Excel
new_data = [['David', 35], ['Eve', 27]]
sheet.range('A6').value = new_data

# 保存 Excel 文件
workbook.save()
workbook.close()
app.quit()

import pandas as pd
import os

# 創(chuàng)建一個空的 DataFrame 以存儲所有月份的數(shù)據(jù)
all_data = pd.DataFrame()

# 遍歷文件夾中的所有 Excel 文件
folder_path = 'sales_data'
for filename in os.listdir(folder_path):
    if filename.endswith('.xlsx'):
        file_path = os.path.join(folder_path, filename)
        # 從每個文件中讀取數(shù)據(jù)并添加到總數(shù)據(jù)中
        data = pd.read_excel(file_path)
        all_data = all_data.append(data)

# 匯總數(shù)據(jù)
monthly_sales = all_data.groupby('Month')['Sales'].sum()

# 生成銷售報告
monthly_sales.to_excel('sales_report.xlsx', sheet_name='Monthly Sales')

import pandas as pd

# 從 Excel 文件中讀取原始數(shù)據(jù)
raw_data = pd.read_excel('raw_data.xlsx')

# 刪除重復(fù)行
cleaned_data = raw_data.drop_duplicates()

# 處理缺失值
cleaned_data['Age'].fillna(0, inplace=True)

# 更改數(shù)據(jù)類型
cleaned_data['Date'] = pd.to_datetime(cleaned_data['Date'])

# 保存清洗后的數(shù)據(jù)到新的 Excel 文件
cleaned_data.to_excel('cleaned_data.xlsx', index=False)

import pandas as pd
import matplotlib.pyplot as plt

# 從 Excel 文件中讀取數(shù)據(jù)
data = pd.read_excel('data.xlsx')

# 創(chuàng)建柱狀圖
plt.figure(figsize=(8, 6))
plt.bar(data['Category'], data['Sales'])
plt.xlabel('Category')
plt.ylabel('Sales')
plt.title('Sales by Category')
plt.xticks(rotation=45)
plt.show()

import pandas as pd
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

# 從 Excel 文件中讀取數(shù)據(jù)
data = pd.read_excel('email_list.xlsx')

# 連接到 SMTP 服務(wù)器
smtp_server = 'smtp.example.com'
smtp_port = 587
sender_email = 'your_email@example.com'
sender_password = 'your_password'

server = smtplib.SMTP(smtp_server, smtp_port)
server.starttls()
server.login(sender_email, sender_password)

# 遍歷數(shù)據(jù)并發(fā)送電子郵件
for index, row in data.iterrows():
    recipient_email = row['Email']
    subject = 'Important Update'
    message = f'Hello {row["Name"]},\n\nThis is an important update.'
    
    msg = MIMEMultipart()
    msg['From'] = sender_email
    msg['To'] = recipient_email
    msg['Subject'] = subject
    msg.attach(MIMEText(message, 'plain'))
    
    server.sendmail(sender_email, recipient_email, msg.as_string())

# 關(guān)閉 SMTP 連接
server.quit()