pip install openpyxl

from openpyxl import load_workbook

# 1. 加載工作簿
workbook = load_workbook('銷售數(shù)據(jù).xlsx')

# 2. 獲取工作表 (通過名稱或活動表)
# sheet = workbook['Sheet1'] # 通過名稱
sheet = workbook.active # 獲取當(dāng)前活動的工作表

# 3. 讀取特定單元格的值
cell_a1 = sheet['A1'].value
print(f"A1單元格的內(nèi)容是：{cell_a1}")

# 或者使用cell方法，指定行和列 (行和列索引都從1開始)
cell_b2 = sheet.cell(row=2, column=2).value
print(f"B2單元格的內(nèi)容是：{cell_b2}")

# 4. 遍歷整個工作表的數(shù)據(jù)
print("--- 工作表全部數(shù)據(jù) ---")
for row in sheet.iter_rows(values_only=True): # values_only=True直接返回值，而不是cell對象
    print(row)

# 操作完成后記得關(guān)閉工作簿釋放資源
workbook.close()

from openpyxl import Workbook

# 1. 創(chuàng)建一個新的工作簿
workbook = Workbook()
sheet = workbook.active
sheet.title = "手機銷售數(shù)據(jù)" # 重命名工作表

# 2. 寫入表頭
headers = ['銷售員', '產(chǎn)品', '銷量', '單價', '總價']
sheet.append(headers) # append方法可以方便地添加一行數(shù)據(jù)

# 3. 寫入原始數(shù)據(jù) (銷量和單價)
raw_data = [
    ['張三', 'iPhone 15', 10, 6000],
    ['李四', '小米14', 15, 4000],
    ['王五', '華為Mate 60', 8, 7000],
]

for row_data in raw_data:
    sheet.append(row_data)

# 4. 寫入公式 (計算總價)
# 總價 = 銷量 * 單價。對于第一行數(shù)據(jù)，銷量在C2單元格，單價在D2單元格，所以公式是 "=C2*D2"
sheet['E2'] = '=C2*D2'
sheet['E3'] = '=C3*D3'
sheet['E4'] = '=C4*D4'

# 為了讓效果更明顯，我們也可以使用循環(huán)批量寫入公式
# for i in range(2, 5):
#     sheet[f'E{i}'] = f'=C{i}*D{i}'

# 5. 保存工作簿
workbook.save('手機銷售報表_生成.xlsx')
print("報表生成成功！")

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

# 創(chuàng)建工作簿和數(shù)據(jù)
workbook = Workbook()
sheet = workbook.active
sheet.title = "銷售數(shù)據(jù)報表"

# 準備數(shù)據(jù)
headers = ['產(chǎn)品名稱', '銷量', '單價', '總價']
data = [
    ['鍵盤', 100, 120.00],
    ['鼠標', 150, 80.50],
    ['顯示器', 50, 1200.00],
]
sheet.append(headers)
for row in data:
    # 先添加數(shù)據(jù)，總價列稍后用公式填充
    sheet.append(row)

# 添加總價公式
sheet['D2'] = '=B2*C2'
sheet['D3'] = '=B3*C3'
sheet['D4'] = '=B4*C4'

# ----- 開始美化 -----

# 1. 設(shè)置標題行樣式：加粗、藍色字體、黃色背景、居中
header_font = Font(name='微軟雅黑', bold=True, size=12, color='000000FF') # 藍色
header_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid') # 黃色
header_alignment = Alignment(horizontal='center', vertical='center')

for cell in sheet['1']: # 遍歷第一行的所有單元格
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = header_alignment

# 2. 為數(shù)據(jù)區(qū)域添加邊框
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)
for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column):
    for cell in row:
        cell.border = thin_border

# 3. 設(shè)置貨幣格式 (單價和總價列)
from openpyxl.styles import numbers
for row in range(2, sheet.max_row + 1):
    sheet[f'C{row}'].number_format = numbers.FORMAT_CURRENCY_CN # 人民幣格式
    sheet[f'D{row}'].number_format = numbers.FORMAT_CURRENCY_CN

# 4. 調(diào)整列寬
sheet.column_dimensions['A'].width = 20
sheet.column_dimensions['B'].width = 10
sheet.column_dimensions['C'].width = 15
sheet.column_dimensions['D'].width = 15

workbook.save('手機銷售報表_美化.xlsx')
print("美化完成！")

from openpyxl import Workbook

workbook = Workbook()
# 默認會有一個名為'Sheet'的工作表

# 1. 創(chuàng)建新工作表
sheet1 = workbook.create_sheet('產(chǎn)品銷售') # 默認插在最后
sheet2 = workbook.create_sheet('銷售員績效', 0) # 指定位置，插在第一個（索引0）

# 2. 獲取所有工作表名稱
print(workbook.sheetnames) # 輸出: ['銷售員績效', 'Sheet', '產(chǎn)品銷售']

# 3. 刪除工作表
del workbook['Sheet'] # 刪除默認的工作表

# 4. 復(fù)制工作表
if '產(chǎn)品銷售' in workbook.sheetnames:
    source_sheet = workbook['產(chǎn)品銷售']
    # 復(fù)制的工作表會自動命名，如'產(chǎn)品銷售 Copy'
    workbook.copy_worksheet(source_sheet)

print(workbook.sheetnames) # 輸出: ['銷售員績效', '產(chǎn)品銷售', '產(chǎn)品銷售 Copy']

workbook.save('工作表操作示例.xlsx')

from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference

# 加載我們之前美化過的文件
workbook = load_workbook('手機銷售報表_美化.xlsx')
sheet = workbook['銷售數(shù)據(jù)報表']

# 1. 創(chuàng)建一個柱狀圖對象
chart = BarChart()
chart.title = "產(chǎn)品銷售額分析"
chart.x_axis.title = "產(chǎn)品名稱"
chart.y_axis.title = "銷售額（元）"

# 2. 定義數(shù)據(jù)和分類的范圍
# 數(shù)據(jù)：總價列的數(shù)據(jù) (D2:D4)
data = Reference(sheet, min_col=4, min_row=2, max_row=4)
# 分類：產(chǎn)品名稱列 (A2:A4) 作為X軸的標簽
categories = Reference(sheet, min_col=1, min_row=2, max_row=4)

# 3. 將數(shù)據(jù)和分類添加到圖表
chart.add_data(data, titles_from_data=False) # titles_from_data=False表示數(shù)據(jù)區(qū)域不包含標題
chart.set_categories(categories)

# 4. 將圖表插入到工作表，例如E1單元格的位置
sheet.add_chart(chart, 'E1')

workbook.save('手機銷售報表_含圖表.xlsx')
print("圖表創(chuàng)建成功！")