Python基础——文件操作之Excel格式文件

Python内部未提供处理Excel文件的功能，想要在Python中操作Excel需要按照第三方的模块。

pip install openpyxl

此模块中集成了Python操作Excel的相关功能，接下来我们就需要去学习该模块提供的相关功能即可。

1、读Excel

1.1、读sheet

from openpyxl import load_workbook

wb = load_workbook("my_test.xlsx")

# sheet相关操作

# 1.获取excel文件中的所有sheet名称

print(wb.sheetnames) # ['数据导出', '用户列表', 'Sheet1', 'Sheet2']


# 2.选择sheet，基于sheet名称

sheet = wb["数据导出"]
cell = sheet.cell(1, 2)
print(cell.value)


# 3.选择sheet，基于索引位置

sheet = wb.worksheets[0]
cell = sheet.cell(1,2)
print(cell.value)


# 4.循环所有的sheet

for name in wb.sheetnames:
    sheet = wb[name]
    cell = sheet.cell(1, 1)
    print(cell.value)


for sheet in wb.worksheets:
    cell = sheet.cell(1, 1)
    print(cell.value)


for sheet in wb:
    cell = sheet.cell(1, 1)
    print(cell.value)

1.2、读sheet中单元格的数据

from openpyxl import load_workbook

wb = load_workbook("my_test.xlsx")
sheet = wb.worksheets[0]

# 1.获取第N行第N列的单元格(位置是从1开始）

cell = sheet.cell(1, 1)

print(cell.value)
print(cell.style)
print(cell.font)
print(cell.alignment)


# 2.获取某个单元格

c1 = sheet["A2"]
print(c1.value)

c2 = sheet['D4']
print(c2.value)


# 3.第N行所有的单元格

for cell in sheet[1]:
    print(cell.value)


# 4.所有行的数据（获取某一列数据）

for row in sheet.rows:
    print(row[0].value, row[1].value)


# 5.获取所有列的数据

for col in sheet.columns:
    print(col[1].value)

1.3、读合并的单元格

from openpyxl import load_workbook

wb = load_workbook("my_test.xlsx")
sheet = wb.worksheets[2]

# 获取第N行第N列的单元格(位置是从1开始）
c1 = sheet.cell(1, 1)
print(c1)  # <Cell 'Sheet1'.A1>
print(c1.value) # 用户信息

c2 = sheet.cell(1, 2)
print(c2)  # <MergedCell 'Sheet1'.B1>
print(c2.value) # None

from openpyxl import load_workbook

wb = load_workbook('my_test.xlsx')
sheet = wb.worksheets[2]
for row in sheet.rows:
    print(row)


>>> 输出结果
(<Cell 'Sheet1'.A1>, <MergedCell 'Sheet1'.B1>, <Cell 'Sheet1'.C1>)
(<Cell 'Sheet1'.A2>, <Cell 'Sheet1'.B2>, <Cell 'Sheet1'.C2>)
(<Cell 'Sheet1'.A3>, <Cell 'Sheet1'.B3>, <Cell 'Sheet1'.C3>)
(<MergedCell 'Sheet1'.A4>, <Cell 'Sheet1'.B4>, <Cell 'Sheet1'.C4>)
(<Cell 'Sheet1'.A5>, <Cell 'Sheet1'.B5>, <Cell 'Sheet1'.C5>)

2、写Excel

2.1、原Excel文件基础上写内容

from openpyxl import load_workbook

wb = load_workbook('files/p1.xlsx')
sheet = wb.worksheets[0]

# 找到单元格，并修改单元格的内容
cell = sheet.cell(1, 1)
cell.value = "新的开始"

# 将excel文件保存到p2.xlsx文件中
wb.save("my_test.xlsx")

2.2、新创建Excel文件写内容

from openpyxl import workbook

# 创建excel且默认会创建一个sheet（名称为Sheet）
wb = workbook.Workbook()

sheet = wb.worksheets[0] # 或 sheet = wb["Sheet"]

# 找到单元格，并修改单元格的内容
cell = sheet.cell(1, 1)
cell.value = "新的开始"

# 将excel文件保存到p2.xlsx文件中
wb.save("my_test.xlsx")

2.3、对于Excel中的sheet操作 

from openpyxl import workbook

wb = workbook.Workbook() # Sheet

# 1. 修改sheet名称

sheet = wb.worksheets[0]
sheet.title = "数据集"
wb.save("p2.xlsx")


# 2. 创建sheet并设置sheet颜色

sheet = wb.create_sheet("工作计划", 0)
sheet.sheet_properties.tabColor = "1072BA"
wb.save("p2.xlsx")


# 3. 默认打开的sheet

wb.active = 0
wb.save("p2.xlsx")


# 4. 拷贝sheet

sheet = wb.create_sheet("工作计划")
sheet.sheet_properties.tabColor = "1072BA"

new_sheet = wb.copy_worksheet(wb["Sheet"])
new_sheet.title = "新的计划"
wb.save("p2.xlsx")


# 5.删除sheet

del wb["用户列表"]
wb.save('files/p2.xlsx')

2.4、对于Excel中的sheet操作

from openpyxl import load_workbook
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill


wb = load_workbook('files/p1.xlsx')

sheet = wb.worksheets[1]

# 1. 获取某个单元格，修改值

cell = sheet.cell(1, 1)
cell.value = "开始"
wb.save("p2.xlsx")


# 2.  获取某个单元格，修改值

sheet["B3"] = "Alex"
wb.save("p2.xlsx")


# 3. 获取某些单元格，修改值

cell_list = sheet["B2":"C3"]
for row in cell_list:
    for cell in row:
        cell.value = "新的值"
wb.save("p2.xlsx")


# 4. 对齐方式

cell = sheet.cell(1, 1)

# horizontal，水平方向对齐方式："general", "left", "center", "right", "fill", "justify", "centerContinuous", "distributed"
# vertical，垂直方向对齐方式："top", "center", "bottom", "justify", "distributed"
# text_rotation，旋转角度。
# wrap_text，是否自动换行。
cell.alignment = Alignment(horizontal='center', vertical='distributed', text_rotation=45, wrap_text=True)
wb.save("p2.xlsx")


# 5. 边框
# side的style有如下：dashDot','dashDotDot', 'dashed','dotted','double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot','mediumDashed', 'slantDashDot', 'thick', 'thin'

cell = sheet.cell(9, 2)
cell.border = Border(
    top=Side(style="thin", color="FFB6C1"), 
    bottom=Side(style="dashed", color="FFB6C1"),
    left=Side(style="dashed", color="FFB6C1"),
    right=Side(style="dashed", color="9932CC"),
    diagonal=Side(style="thin", color="483D8B"),  # 对角线
    diagonalUp=True,  # 左下 ~ 右上
    diagonalDown=True  # 左上 ~ 右下
)
wb.save("p2.xlsx")


# 6.字体

cell = sheet.cell(5, 1)
cell.font = Font(name="微软雅黑", size=45, color="ff0000", underline="single")
wb.save("p2.xlsx")


# 7.背景色

cell = sheet.cell(5, 3)
cell.fill = PatternFill("solid", fgColor="99ccff")
wb.save("p2.xlsx")


# 8.渐变背景色

cell = sheet.cell(5, 5)
cell.fill = GradientFill("linear", stop=("FFFFFF", "99ccff", "000000"))
wb.save("p2.xlsx")


# 9.宽高（索引从1开始）

sheet.row_dimensions[1].height = 50
sheet.column_dimensions["E"].width = 100
wb.save("p2.xlsx")


# 10.合并单元格

sheet.merge_cells("B2:D8")
sheet.merge_cells(start_row=15, start_column=3, end_row=18, end_column=8)
wb.save("p2.xlsx")


sheet.unmerge_cells("B2:D8")
wb.save("p2.xlsx")


# 11.写入公式

sheet = wb.worksheets[3]
sheet["D1"] = "合计"
sheet["D2"] = "=B2*C2"
wb.save("p2.xlsx")


sheet = wb.worksheets[3]
sheet["D3"] = "=SUM(B3,C3)"
wb.save("p2.xlsx")


# 12.删除

# idx，要删除的索引位置
# amount，从索引位置开始要删除的个数（默认为1）
sheet.delete_rows(idx=1, amount=20)
sheet.delete_cols(idx=1, amount=3)
wb.save("p2.xlsx")


# 13.插入

sheet.insert_rows(idx=5, amount=10)
sheet.insert_cols(idx=3, amount=2)
wb.save("p2.xlsx")


# 14.循环写内容

sheet = wb["Sheet"]
cell_range = sheet['A1:C2']
for row in cell_range:
    for cell in row:
        cell.value = "xx"

for row in sheet.iter_rows(min_row=5, min_col=1, max_col=7, max_row=10):
    for cell in row:
        cell.value = "oo"
wb.save("p2.xlsx")


# 15.移动

# 将H2:J10范围的数据，向右移动15个位置、向上移动1个位置
sheet.move_range("H2:J10",rows=1, cols=15)
wb.save("p2.xlsx")


sheet = wb.worksheets[3]
sheet["D1"] = "合计"
sheet["D2"] = "=B2*C2"
sheet["D3"] = "=SUM(B3,C3)"
sheet.move_range("B1:D3",cols=10, translate=True) # 自动翻译公式
wb.save("p2.xlsx")


# 16.打印区域

sheet.print_area = "A1:D200"
wb.save("p2.xlsx")


# 17.打印时，每个页面的固定表头

sheet.print_title_cols = "A:D"
sheet.print_title_rows = "1:3"
wb.save("p2.xlsx")

以上就是在Python中Excel的操作方法总结，如有不当之处，欢迎批评指正！