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的操作方法总结,如有不当之处,欢迎批评指正!