Openpyxl--学习记录

1.工作表的基本操作

1.1 工作表的新建打开与保存

1.1.1 创建工作簿

from openpyxl import Workbook
from pathlib import Pathfile_path = Path.home() / "Desktop" / "123.xlsx"# 1.创建工作簿
wb = Workbook()
# 2.访问默认工作簿
ws = wb.active
# 3.填充内容
ws["A4"] = 100
ws.cell(row=4, column=2, value=10)
# 4.保存工作簿
wb.save(file_path)

1.1.2 打开已经存在的工作表

一个工作表至少有一个工作簿. 你可以通过 Workbook.active 来获取这个属性

# 加载工作表
wb = load_workbook(file_path)
# 打开默认激活的工作表
ws = wb.active
# print(ws) => Worksheet "2yue">
# 打开指定的工作表
ws2 = wb["1yue"] 
# print(ws2) => <Worksheet "1yue">

1.2 工作表的创建,删除与复制

1.2.1 删除工作表

# 1.加载工作簿
wb = load_workbook(file_path)
# 2.显示所有表名,列表形式
sheets = wb.sheetnames
# print(sheets) => ['1yue', '2yue']
# 3.显示所有工作表的名字
sheets_names = [i.title for i in wb.worksheets]
# print(sheets_names) => ['1yue', '2yue']
# 4.删除工作表对象
wb.remove(wb["1yue"])
# 5.保存工作簿
wb.save(file_path)

1.2.2 创建新的工作表

# 1.加载工作簿
wb = load_workbook(file_path)
# 2.创建新的工作簿
wb.create_sheet("3yue")
# 3.保存工作簿
wb.save(file_path)

1.2.3 复制工作表

# 1.加载工作表
wb = load_workbook(file_path)
# 2.复制工作表
copy_sheet = wb.copy_worksheet(wb["2yue"])
# 3.保存工作表
wb.save(file_path)

1.2.4 练习

# 1.批量创建工作表100张
wb = Workbook(file_path)
for i in range(1, 10):month = 7 + int(i / 30)day = i % 30wb.create_sheet(f"{month}月{day}日")
wb.save(file_path)# 2.批量修改工作表名
wb = load_workbook(file_path)
for i in wb.worksheets:i.title = "beijing" + "-" + i.title
wb["beijing-7月1日"].title = "shanghai-7月1日"
wb.save(file_path)# 3.除了包含上海的表,其它全部删除
wb = load_workbook(file_path)
for i in wb.worksheets:if "shanghai" in i.title:continuewb.remove(i)
wb.save(file_path)

2.单元格基本操作

2.1 获取单元格的值

from pathlib import Path
from openpyxl import load_workbookfile_path = Path.home() / "Desktop/123.xlsx"wb = load_workbook(file_path)
ws = wb.active
print(ws["A4"].value)
print(ws.cell(row=4,column=1).value)

2.2 获取一个区域的单元格

• 先遍历行，再遍历单元格对象 
• 按列遍历，就是先遍历完一列，再遍历下一列
• list只能对整张表就行操作，但是不能对区域进行操作，但是我们可以对于list进行切片
• 可以通过iter_rows来指定行列获取区域单元格
• 可以通过rows(按行获取单元格对象），columns(按列获取单元格对象）
• 列名的数字和字母之间的转换

1）行遍历，可以使用 ws["A1:C8"]和 ws["1:8"]，获取的都是行单元格对象

for rows in ws["A1:C8"]:print(rows)

for rows in ws["1:8"]:print(rows)

2）列遍历，可以使用 ws["A:C"]，获取的是列单元格对象 

for columns in ws["A:C"]:print(columns)

 3）list操作工作表

for rows in list(ws):print(rows)

 行切片

for rows in list(ws)[1:3]:print(rows)

4）iter_rows获取指定行列的单元格数据 ，也是获取行单元格数据

for rows in ws.iter_rows(min_row=1, max_row=10, min_col=1, max_col=3):print(rows)

5）rows获取行单元格数据，columns获取列单元格数据 

for rows in ws.rows:print(rows)

for columns in ws.columns:print(columns)

6）数字和字母的转化，utils.get_column_letter(10)，utils.column_index_from_string("J")

# 列名，通过数字获取字母
letter = utils.get_column_letter(10)
# print(letter) => J
# 列名，通过字母获取数字
num = utils.column_index_from_string("J")
# print(num) => 10

7）获取区域单元格的内容 

from pathlib import Path
from openpyxl import load_workbook,utilsfile_path = Path.home() / "Desktop/123.xlsx"wb = load_workbook(file_path)
ws = wb.active
for rows in ws.iter_rows(min_row=1, max_row=8,min_col=1, max_col=3):for cell in rows:print(cell.value)

 

2.3 动态读取数据

• 获取最大行和最大列 ws.max_row, max_column
• 获取单元格的行和列 ws["A1"].row, ws["A1"].column
• 获取一行或是一列值 [i.value for i in ws[1]], [i.value for i in ws["1"]]

from pathlib import Path
from openpyxl import load_workbookfile_path = Path().home() / "Desktop" / "test.xlsx"wb = load_workbook(file_path)ws = wb.active# 最大行和最大列
max_row = ws.max_row
max_column = ws.max_column
# print(max_row, max_column) => 5 4# 获取单元格的行和列
cell_row = ws["A1"].row
cell_column = ws["A1"].column
# print(cell_row,cell_column) => 1 1# 获取一行或是一列的值
rows_value = [i.value for i in ws[1]]
columns_value = [i.value for i in ws["1"]]
# print(rows_value,columns_value) => ['1行1列', '2行1列', '3行1列', '4行1列'] ['1行1列', '2行1列', '3行1列', '4行1列']

2.4 行列的插入与删除 

# 在第二行插入,插入4行
ws.insert_rows(idx=2,amount=4)
# 在第二列插入,插入3列
ws.insert_cols(idx=2,amount=3)
# 在第二行删除,删除12行
ws.delete_rows(idx=2,amount=12)
# 在第二列删除,删除6列
ws.delete_cols(idx=2,amount=6)

2.5 移动与冻结单元格

• 移动单元格内容使用 ws.move_range(cell_range=f"{start_cell}:{end_cell}",rows=10,cols=5)
• 冻结单元格 ws.freeze_panes = "A2"

from pathlib import Path
from openpyxl import load_workbook, utilsfile_path = Path().home() / "Desktop" / "test.xlsx"
save_file_path = Path().home() / "Desktop" / "test2.xlsx"
wb = load_workbook(file_path)ws = wb.active# 获取有数据的单元格
cell_list = [cell for rows in ws.iter_rows() for cell in rows if cell.value is not None]# 获取数据区域范围
row_start = cell_list[0].row
column_start = utils.get_column_letter(cell_list[0].column)
start_cell = column_start + str(row_start)row_end = cell_list[-1].row
column_end = utils.get_column_letter(cell_list[-1].column)
end_cell = column_end + str(row_end)
# # 移动表里面所有的内容向下10行,向右5列
ws.move_range(cell_range=f"{start_cell}:{end_cell}",rows=-10,cols=-5)
wb.save(save_file_path)

2.6 合并单元格

• 合并单元格,ws.merge_cells("A1:A10")

• 取消合并的单元格, ws.unmerge_cells("A1:A10")

3. 单元格样式操作

 3.1 设置单元格行高

设置第一行数据的行高

ws.row_dimensions 是一个字典，键是行号（整数），值是 RowDimension 对象。这些对象包含关于行的尺寸和高度等信息。

workbook = openpyxl.load_workbook(sheet_name)
sheet = workbook.active
sheet.row_dimensions[1].height = 26

3.2 设置单元格数据列宽自适应

ws.column_dimensions 是一个字典，其键是列标签（如 'A', 'B', 'C' 等）,值是 ColumnDimension 对象。这些对象包含关于列的宽度和其他可能的属性（如是否隐藏）的信息

根据字母来获取获取宽度,将字符宽度+1

sheet.column_dimensions[get_column_letter(cell.column)].width = len(cell.value) + 1

ws = wb.activefor cell in list(ws.rows)[0]:print(len(cell.value))ws.column_dimensions[utils.get_column_letter(cell.column)].width = len(cell.value) + 3

 3.3 填充单元格颜色

cell.fill = PatternFill(start_color="8DB4E2", end_color="8DB4E2", fill_type="solid")

3.4 设置字体样式，大小，并加粗显示

 cell.font = Font(name="Times New Roman", size=12, bold=True)

3.5 设置字体居中显示

cell.alignment = Alignment(horizontal='center', vertical='center')

3.6 设置合并单元格

sheet.merge_cells("L3:M7")

3.7 设置多个单元格填充相同的样式

fill_green = PatternFill(start_color="008000", end_color="008000", fill_type="solid")
fill_green_cells = ["R3", "S3", "R6", "S6"]
for cell in fill_green_cells:ws[cell].fill = fill_green

4.实践 

设置首行格式，并设定内容的判定条件

 def set_judgement(self, sheet_name: str) -> None:"""set header style and set judgement"""workbook = openpyxl.load_workbook(sheet_name)ws = workbook.active# Adjust title stylews.row_dimensions[1].height = 26for cell in ws[1]:ws.column_dimensions[get_column_letter(cell.column)].width = len(cell.value) + 1cell.fill = PatternFill(start_color="8DB4E2", end_color="8DB4E2", fill_type="solid")cell.font = Font(name="Times New Roman", size=12, bold=True)cell.alignment = Alignment(horizontal='center', vertical='center')# set judgement##省略判定内容fill_green = PatternFill(start_color="008000", end_color="008000", fill_type="solid")fill_yellow = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")fill_red = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")font_blue = Font(name="Times New Roman", size=10, color="5569FA", bold=True)fill_green_cells = ["R3", "S3", "R6", "S6"]fill_red_cells = ["R4", "S4", "R8", "S8", "R10", "S10"]fill_yellow_cells = ["R7", "S7"]font_blue_cells = ["L3", "T3", "T4", "L6", "T6", "T7", "T8", "L10"]for cell in font_blue_cells:ws[cell].font = font_bluefor cell in fill_green_cells:ws[cell].fill = fill_greenfor cell in fill_red_cells:ws[cell].fill = fill_redfor cell in fill_yellow_cells:ws[cell].fill = fill_yellowworkbook.save(sheet_name)