openpyxl笔记

2022-03-23  本文已影响0人  辰雨蒋_python
import openpyxl as vb

工作簿

工作表

# 创建工作簿
workbook1 = vb.Workbook(path)
workbook1.save(path)

# 打开工作表
workbook2 = vb.load_workbook(path)
worksheet2 = workbook2["Sheet1"]
        = workbook2.active]

# 读取单元格内容时,不要读公式,只读数据结果
workbook = vb.load_workbook(path, data_only=True)

# 显示工作簿中所有工作表和表名
for worksheet in workbook.worksheets:
  print(worksheet.title)

#新建、删除指定工作表
path = r"...test.xlsx"
workbook = vb.load_workbook(path)
worksheet = workbook["4月"]
workbook.create_sheet("5月")
workbook.remove(worksheet)
workbook.save(path)

# 复制工作表
worksheet_new = workbook.copy_worksheet(workbook["5月"])
workbook_new.title="5月_new"
workbook.save(path)

单元格读取操作

# 获取单元格值
cell= worksheet["A1"]
     = worksheet.cell(row=1,column=1)
print(cell.value)]

# 获取1,3,5,7行的数据
for i in range(1,8,2):
    print(i, worksheet.cell(row=i, column=2),value)

# 获取一个区域的单元格
area = worksheet["A1:C10"]  # 用["1:10"]   ["A:C"]会自动扩展到used cells 
for row in area:
    for cell in row:
        print(cell.value)

# 获取某个区域的单元格
# 一般只需要定位起点就够够了
for row in worksheet.iter_rows(min_row=1, min_col=1):
    for cell in row:
        print(cell.value)

# 标记空单元格
for row in worksheet.iter_rows(min_row=2, min_col=2):
    for cell in row:
        if cell.value == None:
            cell.value = "空"
workbook.save(path)

# 获取所有行/列
for row in worksheet.rows:
    for cell in row:
        print(cell.value)

# 获取单元格所在行列
row_1 = cell.row
col_2 = cell.column

# 列数字和数字之前的转换
col_text = vb.utils.get_column_letter(2)
col_num = vb.utils.column_index_from_string("D")

常用单元格操作

# 向一个单元格写入数据
worksheet.cell(1,5, value="写入数据")
worksheet["E2"] = "写入数据"

# 在最后一行写入数据
worksheet.append(list)

# 向一个内容区域写入数据
for row in worksheet["A1:B4"]
    for cell in row:
        cell.value = 12344

# 插入、删除行列
worksheet,insert_cols(idx=2, amount=5)
worksheet.insert_rows(idx=2, amount=5)
worksheet.delete-cols(idx=2, amount=5)

# 移动单元格
worksheet.move_range("A1:C3",row=10,cols=10) # 向右10,向下10

# 冻结单元格
worksheet.freeze_panes = "C3"

# 直接在单元格内写公式
worksheet["F1"] = "sum(C1:E1)"

# 添加批注
comment_1 = vb.comments.Comment("这里写了批注","jyc")
workwheet["F20"].comment = comment_1
workbook.save(path)

表格格式设置

# 字体对象
font_style = vb.styles.Font(name=u"微软雅黑", bold=True, italic=False, size=9, 
      vertAlign=None, underline=None, strike=False, color="FF000000")
worksheet["A2"].font = font_style

# 单元格对齐 Alignment
Alignment(horizontal="general", vertical="bottom", text_rotation=0, wrap_text=False, 
    shrink_to_fit=False, indent=0)
    # horinzontal: "general", "center","left", "justify"(两端对齐),"right", "centerContinuous"(跨列居中), "distributed"(分散对齐),
    # vertical: "center","top","bottom","justify","distributed"

# 边框 Side
Side(style="连线样式", color="边线颜色")
Border(left="左边线样式", right= , top= , bottom= )
    # style: double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick
side = vb.style.Side(style="thin", color="FF000000")
border = vb.styles.Border(left=side, right=side, top=side, bottom=side)
worksheet["A1"].border = border

# 填充 PatternFill
PatternFill(fill_type=None, start_color="FFFFFFFF", end_color="FF000000")
    # fill_type: None, solid, darGray, mediumGray, lightGray, gray0625, darkHorizontal, darkVertical, darkDown, darkUp, darkGrid, darkTrellis, lightHorizontal, ...
    #start_color/fgColor 背景颜色
    #end_color/bgColor 图案颜色

# 渐变填充 GradientFill
GradientFill(stop=("渐变颜色1", "渐变颜色2", ...))
    # fill_type: linear, path(中心扩散)
    # degree: 旋转角度
    # stop:一个元组,(起始颜色,...,结束颜色)

# 锁定单元格、隐藏公式
Protection(locked=True, hidden=False)
    # 只有开启”保护工作表“之后,锁定单元格和隐藏公式才能生效

# 行高和列宽
worksheet.row_dimensions[1].height=200
worksheet.column_dimensions["B"].width=100

上一篇 下一篇

猜你喜欢

热点阅读