pip install openpyxl

from openpyxl import Workbook, load_workbook

# 創(chuàng)建新的工作簿
wb = Workbook()
ws = wb.active  # 獲取默認(rèn)的工作表

# 加載現(xiàn)有的工作簿
wb = load_workbook("example.xlsx")
ws = wb.active

# 創(chuàng)建新的工作表
ws_new = wb.create_sheet("NewSheet")

# 重命名工作表
ws.title = "MainSheet"

# 刪除工作表
wb.remove(ws_new)

# 寫入數(shù)據(jù)
ws["A1"] = "Hello, OpenPyXL!"

# 讀取數(shù)據(jù)
data = ws["A1"].value
print(data)  # 輸出: Hello, OpenPyXL!

# 使用行列索引訪問
ws.cell(row=2, column=1, value="This is row 2, column 1")
print(ws.cell(row=2, column=1).value)  # 輸出: This is row 2, column 1

from openpyxl.styles import Font, Color, Alignment

# 設(shè)置字體
ws["A1"].font = Font(name="Arial", size=12, bold=True, color="FF0000")  # 紅色粗體

# 設(shè)置對齊方式
ws["A1"].alignment = Alignment(horizontal="center", vertical="center")

# 設(shè)置單元格顏色
from openpyxl.styles import PatternFill
ws["A1"].fill = PatternFill("solid", fgColor="FFFF00")  # 黃色填充

wb.save("modified_example.xlsx")

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

# 創(chuàng)建新的工作簿和工作表
wb = Workbook()
ws = wb.active
ws.title = "Sales Report"

# 添加標(biāo)題行
ws.append(["Product", "Region", "Sales"])
ws["A1"].font = Font(bold=True, color="FFFFFF")
ws["B1"].font = Font(bold=True, color="FFFFFF")
ws["C1"].font = Font(bold=True, color="FFFFFF")

# 設(shè)置標(biāo)題背景顏色
ws["A1"].fill = PatternFill("solid", fgColor="4F81BD")
ws["B1"].fill = PatternFill("solid", fgColor="4F81BD")
ws["C1"].fill = PatternFill("solid", fgColor="4F81BD")

# 填充數(shù)據(jù)
data = [
    ["Widget", "North", 1200],
    ["Widget", "South", 900],
    ["Gadget", "North", 500],
    ["Gadget", "South", 300],
]

for row in data:
    ws.append(row)

# 保存工作簿
wb.save("sales_report.xlsx")
print("Sales report created and saved as sales_report.xlsx")

from openpyxl import Workbook

# 創(chuàng)建工作簿和工作表
wb = Workbook()
ws = wb.active

# 填充數(shù)據(jù)
ws["A1"] = "Value 1"
ws["A2"] = 10
ws["B1"] = "Value 2"
ws["B2"] = 20

# 添加公式
ws["C1"] = "Total"
ws["C2"] = "=A2+B2"

# 保存文件
wb.save("formula_example.xlsx")

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

# 創(chuàng)建工作簿和工作表
wb = Workbook()
ws = wb.active

# 創(chuàng)建數(shù)據(jù)驗(yàn)證規(guī)則：下拉菜單
dv = DataValidation(type="list", formula1='"Option1,Option2,Option3"', showDropDown=True)
ws.add_data_validation(dv)

# 將驗(yàn)證應(yīng)用到單元格范圍
dv.add(ws["A1"])

# 保存文件
wb.save("data_validation_example.xlsx")

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

# 創(chuàng)建工作簿和工作表
wb = Workbook()
ws = wb.active

# 填充數(shù)據(jù)
data = [
    ["Product", "Sales"],
    ["Widget", 1200],
    ["Gadget", 900],
    ["Doohickey", 700],
]
for row in data:
    ws.append(row)

# 創(chuàng)建柱狀圖
chart = BarChart()
chart.title = "Sales Data"
chart.x_axis.title = "Product"
chart.y_axis.title = "Sales"

# 設(shè)置數(shù)據(jù)范圍
data_ref = Reference(ws, min_col=2, min_row=2, max_row=4)
cats = Reference(ws, min_col=1, min_row=2, max_row=4)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats)

# 添加圖表到工作表
ws.add_chart(chart, "E5")

# 保存文件
wb.save("chart_example.xlsx")

from openpyxl import Workbook

# 創(chuàng)建工作簿和工作表
wb = Workbook()
ws = wb.active

# 合并單元格
ws.merge_cells("A1:D1")
ws["A1"] = "Merged Cells Example"

# 拆分單元格
ws.unmerge_cells("A1:D1")

# 保存文件
wb.save("merge_cells_example.xlsx")

# 設(shè)置列寬
ws.column_dimensions["A"].width = 20

# 設(shè)置行高
ws.row_dimensions[1].height = 30

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.chart import BarChart, Reference
from openpyxl.worksheet.datavalidation import DataValidation

# 創(chuàng)建工作簿和工作表
wb = Workbook()
ws = wb.active
ws.title = "Monthly Sales Report"

# 添加標(biāo)題行并格式化
ws.append(["Product", "Region", "Sales"])
for cell in ws[1]:
    cell.font = Font(bold=True)
    cell.fill = PatternFill("solid", fgColor="ADD8E6")  # 淺藍(lán)色背景

# 添加數(shù)據(jù)和數(shù)據(jù)驗(yàn)證
products = ["Widget", "Gadget", "Doohickey"]
dv = DataValidation(type="list", formula1=f'"{",".join(products)}"', showDropDown=True)
ws.add_data_validation(dv)

data = [
    ["Widget", "North", 1200],
    ["Widget", "South", 900],
    ["Gadget", "North", 700],
    ["Doohickey", "South", 300],
]

for row in data:
    ws.append(row)
    dv.add(ws[f"A{ws.max_row}"])

# 添加公式：總和
ws["D1"] = "Total Sales"
ws["D2"] = "=SUM(C2:C5)"

# 創(chuàng)建柱狀圖
chart = BarChart()
chart.title = "Sales by Product"
chart.x_axis.title = "Product"
chart.y_axis.title = "Sales"
data_ref = Reference(ws, min_col=3, min_row=2, max_row=5)
chart.add_data(data_ref, titles_from_data=True)
ws.add_chart(chart, "F5")

# 保存文件
wb.save("automated_sales_report.xlsx")
print("Automated sales report created successfully!")