openpyxl 简单使用

from openpyxl import Workbook
# 实例化
wb = Workbook()  # create an excel file in RAM
# 获取当前active的sheet
sheet = wb.active
print(sheet.title)

打开已有文件

from openpyxl import load_workbook
wb2 = load_workbook("文件名.xlsx")
print(wb2.sheetnames)  # 查看sheet 的名字
sheet1 =wb2.get_sheet_by_name("sheet1")  # 根据sheet名字 操作sheet

print(sheet1["B4"].value)  # 获得sheet1[B4]的值

获取指定列的数据

 for cell in sheet1["B5:B10"]:
     print(cell[0].value)  # cell 是元组

获得全部数据

for row in sheet1:
    for cell in row:
        print(cell.value, end=",")
    print()

遍历指定行&列

按行遍历

for row in sheet:
    for cell in row:
        print(cell.value, end=",")
    print()
    

按列便利

for column in sheet1.columns:
    for cell in column:
        print(cell.value, end=",")
    print()

从第2行开始至第5行，每行打印5列

for row in sheet1.iter_rows(min_row=2, max_row=5,max_col=5):
    for cell in row:
        print(cell.value, end=",")
    print()

从第2行开始至第5行

for row in sheet1.iter_rows(min_row=2, max_row=5):
    for cell in row:
        print(cell.value, end=",")
    print()

从第2列开始至第5列

for row in sheet1.iter_cols(min_col=2, max_col=5):
    for cell in row:
        print(cell.value, end=",")
    print()

写数据

# 1 数据直接分配到单元格
sheet["C5"] = "hello "
sheet["C6"] = "nihao"
#  2 附加行，从第一列开始附加（从下方空白处，最左开始）可以多行
sheet.append([1,2,3])

删除

方式一

wb.remove(sheet1)

方式二

del wb(sheet1)

保存

wb.save()

格式设置

字体

from  openpyxl.styles import Font ,colors, Alignment

# 字号 斜体 字体颜色
bold_itatic_24_font = Font(name="等线", size=24, italic=True, color=colors.RED,bold=True)
sheet["B3"].font = bold_itatic_24_font

对齐方式

# 垂直居中，水平居中
sheet1["B1"].alignment = Alignment(horizontal='center', vertical='center')

设置行高

# 第2行 行高40
sheet1.row_dimensions[2].height =40
# 第C列  宽30
sheet1.column_dimensions['C'].wideh = 30