from openpyxl import Workbook

# 第 1 步：創(chuàng)建新工作簿
wb = Workbook()

# 第 2 步：獲取活動工作表（默認(rèn)創(chuàng)建的第一個工作表）
ws = wb.active

# 第 3 步：設(shè)置工作表名稱
ws.title = "我的數(shù)據(jù)"

# 第 4 步：寫入數(shù)據(jù)（使用 A1 表示法）
ws['A1'] = '姓名'
ws['B1'] = '年齡'
ws['A2'] = '張三'
ws['B2'] = 25

# 第 5 步：保存文件
wb.save('示例.xlsx')
print("文件已創(chuàng)建！")

from openpyxl import load_workbook

# 打開已存在的文件
wb = load_workbook('示例.xlsx')

# 獲取工作表（通過名稱）
ws = wb['我的數(shù)據(jù)']

# 讀取單元格值
name = ws['A1'].value
age = ws['B1'].value

print(f"A1 的值：{name}")
print(f"B1 的值：{age}")

# 讀取 A2 和 B2
print(f"姓名：{ws['A2'].value}, 年齡：{ws['B2'].value}")

A1 的值：姓名
B1 的值：年齡
姓名：張三, 年齡：25

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 寫入表頭
ws.cell(1, 1, '產(chǎn)品')  # 第1行第1列
ws.cell(1, 2, '價格')  # 第1行第2列
ws.cell(1, 3, '數(shù)量')  # 第1行第3列

# 寫入數(shù)據(jù)
ws.cell(2, 1, '蘋果')
ws.cell(2, 2, 5.5)
ws.cell(2, 3, 10)

ws.cell(3, 1, '香蕉')
ws.cell(3, 2, 3.2)
ws.cell(3, 3, 20)

wb.save('商品清單.xlsx')
print("文件已創(chuàng)建！")

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 表頭
headers = ['姓名', '部門', '工資']
for col, header in enumerate(headers, start=1):
    ws.cell(1, col, header)

# 數(shù)據(jù)列表
employees = [
    ['張三', '技術(shù)部', 8000],
    ['李四', '銷售部', 6000],
    ['王五', '人事部', 5500],
    ['趙六', '技術(shù)部', 9000],
]

# 批量寫入數(shù)據(jù)
for row, employee in enumerate(employees, start=2):  # 從第2行開始
    for col, value in enumerate(employee, start=1):
        ws.cell(row, col, value)

wb.save('員工信息.xlsx')
print(f"已寫入 {len(employees)} 條員工數(shù)據(jù)")

from openpyxl import load_workbook

wb = load_workbook('員工信息.xlsx')
ws = wb.active

# 獲取表格的最大行數(shù)和列數(shù)
max_row = ws.max_row
max_column = ws.max_column

print(f"表格大?。簕max_row} 行 × {max_column} 列\(zhòng)n")

# 讀取所有數(shù)據(jù)
for row in range(1, max_row + 1):
    row_data = []
    for col in range(1, max_column + 1):
        cell_value = ws.cell(row, col).value
        row_data.append(cell_value)
    print(row_data)

表格大?。? 行 × 3 列

['姓名', '部門', '工資']
['張三', '技術(shù)部', 8000]
['李四', '銷售部', 6000]
['王五', '人事部', 5500]
['趙六', '技術(shù)部', 9000]

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

wb = Workbook()
ws = wb.active

# 寫入表頭
headers = ['姓名', '分?jǐn)?shù)', '等級']
for col, header in enumerate(headers, start=1):
    cell = ws.cell(1, col, header)
    
    # 設(shè)置字體：加粗、白色、14號
    cell.font = Font(bold=True, color='FFFFFF', size=14)
    
    # 設(shè)置背景色：藍色
    cell.fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
    
    # 設(shè)置對齊：居中
    cell.alignment = Alignment(horizontal='center', vertical='center')

# 寫入數(shù)據(jù)
data = [
    ['張三', 95, '優(yōu)秀'],
    ['李四', 78, '良好'],
    ['王五', 65, '及格'],
]

for row, row_data in enumerate(data, start=2):
    for col, value in enumerate(row_data, start=1):
        ws.cell(row, col, value)

# 調(diào)整列寬（讓內(nèi)容顯示完整）
ws.column_dimensions['A'].width = 12
ws.column_dimensions['B'].width = 10
ws.column_dimensions['C'].width = 10

wb.save('成績單.xlsx')
print("帶樣式的文件已創(chuàng)建！")

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

# 讀取原始數(shù)據(jù)
wb = load_workbook('員工信息.xlsx')
ws = wb.active

# 統(tǒng)計各部門工資
departments = {}
for row in range(2, ws.max_row + 1):  # 跳過表頭
    dept = ws.cell(row, 2).value  # 部門在第2列
    salary = ws.cell(row, 3).value  # 工資在第3列
    
    if dept not in departments:
        departments[dept] = []
    departments[dept].append(salary)

# 創(chuàng)建統(tǒng)計報表
wb_new = Workbook()
ws_new = wb_new.active
ws_new.title = "部門統(tǒng)計"

# 寫入表頭
ws_new['A1'] = '部門'
ws_new['B1'] = '人數(shù)'
ws_new['C1'] = '平均工資'

# 設(shè)置表頭樣式
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
header_font = Font(bold=True, color='FFFFFF')

for col in ['A1', 'B1', 'C1']:
    cell = ws_new[col]
    cell.fill = header_fill
    cell.font = header_font

# 寫入統(tǒng)計數(shù)據(jù)
row = 2
for dept, salaries in departments.items():
    ws_new.cell(row, 1, dept)
    ws_new.cell(row, 2, len(salaries))
    avg_salary = sum(salaries) / len(salaries)
    ws_new.cell(row, 3, round(avg_salary, 2))
    row += 1

wb_new.save('部門統(tǒng)計.xlsx')
print("統(tǒng)計報表已生成！")

import xlrd
wb = xlrd.open_workbook('file.xls')

import pandas as pd
df = pd.read_excel('file.xlsx')
df.to_excel('output.xlsx')

import xlsxwriter
wb = xlsxwriter.Workbook('file.xlsx')