import pandas as pd

# 讀取 Excel 文件
file_path = 'example_data.xlsx'
df = pd.read_excel(file_path)

# 打印讀取的數(shù)據(jù)框架
print("讀取的數(shù)據(jù)框架：")
print(df)

import pandas as pd

# 創(chuàng)建示例數(shù)據(jù)框架
data = {
    'Product': ['A', 'B', 'C'],
    'Price': [25.5, 30.2, 15.8],
    'Quantity': [10, 8, 4]
}

df = pd.DataFrame(data)

# 將數(shù)據(jù)框架寫入 Excel 文件
df.to_excel('output_data.xlsx', index=False)

import pandas as pd

# 創(chuàng)建示例數(shù)據(jù)框架
data = {
    'Product': ['A', 'B', 'C', 'A', 'B'],
    'Price': [25.5, 30.2, 15.8, 22.0, 18.5],
    'Quantity': [10, 8, 4, 6, 2]
}

df = pd.DataFrame(data)

# 篩選 Price 大于 20 的數(shù)據(jù)
filtered_data = df[df['Price'] > 20]

# 打印篩選后的數(shù)據(jù)框架
print("Price 大于 20 的數(shù)據(jù)：")
print(filtered_data)

import pandas as pd

# 創(chuàng)建示例數(shù)據(jù)框架
data = {
    'Product': ['B', 'A', 'C', 'D', 'A'],
    'Price': [30.2, 25.5, 15.8, 40.0, 20.5],
    'Quantity': [8, 10, 4, 2, 6]
}

df = pd.DataFrame(data)

# 按 'Product' 列升序排序
df_sorted = df.sort_values(by='Product')

# 打印排序后的數(shù)據(jù)框架
print("按 'Product' 列升序排序：")
print(df_sorted)

import pandas as pd

# 假設(shè)我們有一個(gè)包含銷售數(shù)據(jù)的數(shù)據(jù)框架
data = {
    'Product': ['A', 'B', 'C', 'A', 'B', 'A'],
    'Sales_Amount': [100, 150, 200, 120, 180, 130],
    'Quantity': [5, 3, 4, 6, 2, 5],
    'Unit_Price': [20, 50, 50, 20, 90, 26]
}

sales_data = pd.DataFrame(data)

# 打印原始數(shù)據(jù)
print("原始數(shù)據(jù)：")
print(sales_data)

# 統(tǒng)計(jì)與匯總
mean_sales_amount = sales_data['Sales_Amount'].mean()
median_quantity = sales_data['Quantity'].median()
mode_product = sales_data['Product'].mode().values[0]
std_unit_price = sales_data['Unit_Price'].std()
min_sales_amount = sales_data['Sales_Amount'].min()
max_quantity = sales_data['Quantity'].max()

# 打印統(tǒng)計(jì)結(jié)果
print("\n統(tǒng)計(jì)與匯總結(jié)果：")
print(f"平均銷售額：{mean_sales_amount}")
print(f"銷售數(shù)量中位數(shù)：{median_quantity}")
print(f"產(chǎn)品銷售頻率最高的是：{mode_product}")
print(f"單價(jià)標(biāo)準(zhǔn)差：{std_unit_price}")
print(f"最小銷售額：{min_sales_amount}")
print(f"最大銷售數(shù)量：{max_quantity}")

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

# 創(chuàng)建一個(gè)工作簿和工作表
workbook = Workbook()
sheet = workbook.active

# 示例數(shù)據(jù)
data = [
    ["Product", "Price", "Quantity"],
    ["A", 25.5, 10],
    ["B", 30.2, 8],
    ["C", 15.8, 15],
]

# 將數(shù)據(jù)寫入工作表
for row in data:
    sheet.append(row)

# 單元格格式設(shè)置
# 設(shè)置標(biāo)題行的字體為粗體、字號(hào)14、顏色為藍(lán)色
sheet['A1'].font = Font(bold=True, size=14, color="0000FF")

# 設(shè)置數(shù)據(jù)區(qū)域的對(duì)齊方式為居中
for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column):
    for cell in row:
        cell.alignment = Alignment(horizontal='center', vertical='center')

# 設(shè)置價(jià)格列的數(shù)值格式為貨幣格式
for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row, min_col=2, max_col=2):
    for cell in row:
        cell.number_format = '"$"#,##0.00'

# 設(shè)置數(shù)量列的背景顏色為淺黃色
for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row, min_col=3, max_col=3):
    for cell in row:
        cell.fill = PatternFill(start_color="FFFF99", end_color="FFFF99", fill_type="solid")

# 保存工作簿
workbook.save("formatted_excel.xlsx")

from openpyxl import Workbook

# 創(chuàng)建一個(gè)工作簿和工作表
workbook = Workbook()
sheet = workbook.active

# 原始數(shù)據(jù)
data = [
    ["Name", "Age", "Country"],
    ["Alice", 25, "USA"],
    ["Bob", 30, "Canada"],
]

# 將數(shù)據(jù)寫入工作表
for row in data:
    sheet.append(row)

# 打印原始數(shù)據(jù)
print("原始數(shù)據(jù)：")
for row in sheet.iter_rows(values_only=True):
    print(row)

# 插入新的行（在第二行之后插入）
new_row_data = ["Charlie", 28, "UK"]
sheet.insert_rows(new_row_data, row_idx=2)

# 打印插入新行后的數(shù)據(jù)
print("\n插入新行后的數(shù)據(jù)：")
for row in sheet.iter_rows(values_only=True):
    print(row)

# 保存工作簿
workbook.save("inserted_row.xlsx")

from openpyxl import Workbook

# 創(chuàng)建一個(gè)工作簿和工作表
workbook = Workbook()
sheet = workbook.active

# 原始數(shù)據(jù)
data = [
    ["Name", "Age", "Country"],
    ["Alice", 25, "USA"],
    ["Bob", 30, "Canada"],
]

# 將數(shù)據(jù)寫入工作表
for row in data:
    sheet.append(row)

# 打印原始數(shù)據(jù)
print("原始數(shù)據(jù)：")
for row in sheet.iter_rows(values_only=True):
    print(row)

# 插入新的列（在第二列之后插入）
new_column_data = ["Female", "Male", "Female"]
sheet.insert_cols(values=new_column_data, col_idx=2)

# 打印插入新列后的數(shù)據(jù)
print("\n插入新列后的數(shù)據(jù)：")
for row in sheet.iter_rows(values_only=True):
    print(row)

# 保存工作簿
workbook.save("inserted_column.xlsx")

from openpyxl import Workbook

# 創(chuàng)建一個(gè)工作簿和工作表
workbook = Workbook()
sheet = workbook.active

# 原始數(shù)據(jù)
data = [
    ["Name", "Age", "Country"],
    ["Alice", 25, "USA"],
    ["Bob", 30, "Canada"],
]

# 將數(shù)據(jù)寫入工作表
for row in data:
    sheet.append(row)

# 合并 A1 到 C1 的單元格
sheet.merge_cells('A1:C1')

# 在合并的單元格中寫入標(biāo)題
sheet['A1'] = 'Personal Information'

# 保存工作簿
workbook.save("merged_cells.xlsx")

from openpyxl import load_workbook

# 加載已存在的工作簿
workbook = load_workbook("merged_cells.xlsx")
sheet = workbook.active

# 取消合并 A1 到 C1 的單元格
sheet.unmerge_cells('A1:C1')

# 保存工作簿
workbook.save("unmerged_cells.xlsx")

from openpyxl import Workbook

# 創(chuàng)建一個(gè)工作簿和工作表
workbook = Workbook()
sheet = workbook.active

# 原始數(shù)據(jù)
data = [
    ["Product", "Price", "Quantity", "Total"],
    ["A", 25.5, 10, None],
    ["B", 30.2, 8, None],
]

# 將數(shù)據(jù)寫入工作表
for row in data:
    sheet.append(row)

# 添加公式計(jì)算 Total 列，Total = Price * Quantity
for row in range(2, sheet.max_row + 1):
    sheet[f'D{row}'] = f'B{row} * C{row}'

# 保存工作簿
workbook.save("formulas.xlsx")

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
import matplotlib.pyplot as plt
from io import BytesIO

# 創(chuàng)建一個(gè)工作簿和工作表
workbook = Workbook()
sheet = workbook.active

# 原始數(shù)據(jù)
data = [
    ["Category", "Value"],
    ["A", 25],
    ["B", 30],
    ["C", 20],
]

# 將數(shù)據(jù)寫入工作表
for row in data:
    sheet.append(row)

# 創(chuàng)建柱狀圖
chart = BarChart()
chart.title = "Category vs Value"
chart.x_axis.title = "Category"
chart.y_axis.title = "Value"

# 數(shù)據(jù)范圍
data_range = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=sheet.max_row)

# 設(shè)置圖表數(shù)據(jù)
chart.add_data(data_range, titles_from_data=True)

# 將圖表插入到工作表中
sheet.add_chart(chart, "D2")

# 保存工作簿
workbook.save("chart_example.xlsx")

import pandas as pd

# 創(chuàng)建示例數(shù)據(jù)框架
data = {
    'Category': ['A', 'B', 'A', 'B', 'A', 'B'],
    'Value': [10, 15, 20, 25, 30, 35],
    'Quantity': [2, 3, 4, 5, 6, 7]
}

df = pd.DataFrame(data)

# 創(chuàng)建數(shù)據(jù)透視表
pivot_table = pd.pivot_table(df, values='Value', index='Category', columns='Quantity', aggfunc='sum', fill_value=0)

# 打印數(shù)據(jù)透視表
print("數(shù)據(jù)透視表：")
print(pivot_table)

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

# 創(chuàng)建一個(gè)工作簿和工作表
workbook = Workbook()
sheet = workbook.active

# 原始數(shù)據(jù)
data = [
    ["Name", "Age", "Country"],
    ["Alice", 25, "USA"],
    ["Bob", 30, "Canada"],
]

# 將數(shù)據(jù)寫入工作表
for row in data:
    sheet.append(row)

# 創(chuàng)建數(shù)據(jù)驗(yàn)證規(guī)則（Age 列只允許輸入 18 到 60 之間的整數(shù)）
dv = DataValidation(type="whole", operator="between", formula1=18, formula2=60)
dv.errorTitle = "Invalid Input"
dv.error = "Age must be between 18 and 60."
dv.add("B2:B1048576")  # 應(yīng)用規(guī)則到整個(gè) B 列

# 添加數(shù)據(jù)驗(yàn)證規(guī)則到工作表
sheet.add_data_validation(dv)

# 保存工作簿
workbook.save("data_validation_example.xlsx")

from openpyxl import Workbook

# 創(chuàng)建一個(gè)工作簿和工作表
workbook = Workbook()
sheet = workbook.active

# 原始數(shù)據(jù)
data = [
    ["Product", "Price", "Quantity", "Total"],
    ["A", 25.5, 10, None],
    ["B", 30.2, 8, None],
]

# 將數(shù)據(jù)寫入工作表
for row in data:
    sheet.append(row)

# 批量計(jì)算 Total 列的值（Total = Price * Quantity）
for row in range(2, sheet.max_row + 1):
    price = sheet[f'B{row}'].value
    quantity = sheet[f'C{row}'].value
    total = price * quantity
    sheet[f'D{row}'] = total

# 打印批量計(jì)算后的數(shù)據(jù)
print("批量計(jì)算后的數(shù)據(jù)：")
for row in sheet.iter_rows(values_only=True):
    print(row)

# 保存工作簿
workbook.save("batch_operations.xlsx")

from openpyxl import Workbook

try:
    # 創(chuàng)建一個(gè)工作簿和工作表
    workbook = Workbook()
    sheet = workbook.active

    # 原始數(shù)據(jù)
    data = [
        ["Product", "Price", "Quantity", "Total"],
        ["A", 25.5, 10, None],
        ["B", 30.2, 8, None],
    ]

    # 將數(shù)據(jù)寫入工作表
    for row in data:
        sheet.append(row)

    # 嘗試計(jì)算 Total 列的值，但存在空值導(dǎo)致的錯(cuò)誤
    for row in range(2, sheet.max_row + 1):
        try:
            price = sheet[f'B{row}'].value
            quantity = sheet[f'C{row}'].value
            total = price * quantity
            sheet[f'D{row}'] = total
        except TypeError as e:
            print(f"Error in row {row}: {e}")

    # 打印處理后的數(shù)據(jù)
    print("處理后的數(shù)據(jù)：")
    for row in sheet.iter_rows(values_only=True):
        print(row)

    # 保存工作簿
    workbook.save("error_handling_example.xlsx")

except Exception as e:
    print(f"An error occurred: {e}")