python自动化办公(第9课)批量调整Excel中字体、样式

python自动化办公(第9课)批量调整Excel中字体、样式

- 修改字体样式

  • Font(name=“字体名”,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)
from openpyxl.styles import Font
from openpyxl import load_workbook
workbook = load_workbook(filename="新建表.xlsx")
sheet = workbook.active
cell = sheet["A1"]
font = Font(name="隶书",size=12,bold=True,italic=True,color="59A869")
cell.font = font
workbook.save(filename="新建表.xlsx")
  • 注意styles后面有s
  • 注意Font首字母大写
    在这里插入图片描述

- 获取表格中字体的样式

workbook = load_workbook(filename="新建表.xlsx")
sheet = workbook.active
cell = sheet["A1"]
font = cell.font
print(font.name,font.size,font.bold,font.italic)
隶书 12.0 True True

- 设置对齐样式

  • Alignment(horizontal=水平对齐,vertical=垂直对齐,text_rotation=旋转角度,wrap_text=是否自动换行)
from openpyxl.styles import Alignment
from openpyxl import load_workbook
workbook = load_workbook(filename="新建表.xlsx")
sheet = workbook.active
cell = sheet["A1"]
alignment = Alignment(horizontal="center",vertical="center",text_rotation=45,wrap_text=True)
cell.alignment = alignment
workbook.save(filename="新建表.xlsx")

在这里插入图片描述

  • 水平对齐:“distributed”,“justify”,“center”,“left”,“fill”,“centerContinuous”,“right”,“general”
  • 垂直对齐:“bottom”,“distributed”,“justify”,“center”,“top”
    在这里插入图片描述

- 设置边框样式

  • Side(style=边线样式,color=边线颜色)
  • Border(left=左边线样式,right=右边线样式,top=上边线样式,bottom=下边线样式)
from openpyxl.styles import Side,Border
from openpyxl import load_workbook
workbook = load_workbook(filename="新建表.xlsx")
sheet = workbook.active
cell = sheet["C2"]
side_left = Side(style="thin",color="0277D9")  #蓝色
side_right = Side(style="dotted",color="EE3131")  #红色
border = Border(left=side_left,right=side_right,top=side_left,bottom=side_right)
cell.border = border
workbook.save(filename="新建表.xlsx")
  • 颜色只能是16进制的颜色名

  • 注意首字母大小写
    在这里插入图片描述

  • 边线样式:“double”,“dotted”,“hair”,“dashed”,“dashDot”,“thin”,“medium”,“thick”

- 设置填充样式

  • PatternFill(fill_type=填充样式,fgColor=填充颜色)
  • GradientFill(stop=(渐变颜色1,渐变颜色2,…))
from openpyxl.styles import PatternFill,GradientFill
from openpyxl import load_workbook
workbook = load_workbook(filename="新建表.xlsx")
sheet = workbook.active

cell_1 = sheet["A3"]
cell_2 = sheet["A4"]

pattern_fill = PatternFill(fill_type="solid",fgColor="6C91FC")
gradient_fill = GradientFill(stop=("FFFFFF","3E7AAB","1A1A1A"))

cell_1.fill = pattern_fill
cell_2.fill = gradient_fill
workbook.save(filename="新建表.xlsx")
  • 注意fgColor中C要大写
  • 设置好填充样式,再赋值到格子的fill属性中
    在这里插入图片描述

- 设置行高和列宽

  • .row_dimensions[行编号].height = 行高
  • .column_dimensions[列编号].width = 列宽
from openpyxl import load_workbook
workbook = load_workbook(filename="新建表.xlsx")
sheet = workbook.active
sheet.row_dimensions[6].height = 50
sheet.column_dimensions["A"].width = 20
workbook.save(filename="新建表.xlsx")
  • 注意A列中的字母A要加双引号
    在这里插入图片描述

- 合并单元格

  • .merge_cells(待合并的格子编号)
  • .merage_cells(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)
from openpyxl import load_workbook
workbook = load_workbook(filename="新建表.xlsx")
sheet = workbook.active
sheet.merge_cells("D1:E2")
sheet.merge_cells(start_row=4,start_column=4,end_row=5,end_column=5)
workbook.save(filename="新建表.xlsx")

- 取消单元格合并

  • .unmerge_cells(待取消合并格子编号)
  • .unmerge_cells(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)
from openpyxl import load_workbook
workbook = load_workbook(filename="新建表.xlsx")
sheet = workbook.active
sheet.unmerge_cells("D1:E2")
sheet.unmerge_cells(start_row=4,start_column=4,end_row=5,end_column=5)
workbook.save(filename="新建表.xlsx")

作业:编写python程序,要求如下:

  • 打开阿里云天池电商婴儿用户数证.xlsx
  • 找到day在2014年以后,buy_mount中大于5的数据
  • 将其他数据行删除掉,最后不要在中间留空行
  • 将buy_mount中大于等于10的数据行背景颜色标为红色,字体标为白色
  • 保存该Excel文件
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.styles import Font

# 设置查找表头所在列号函数
def search_col(col_name):
    workbook = load_workbook(filename="阿里云天池电商婴儿用户数据.xlsx")
    sheet = workbook.active
    for cell in sheet[1]:
        if cell.value == col_name:
            return(cell.column)
    # 没有对表进行修改,不需要workbook.save

# 设置删除行函数
def delete_row():
    workbook = load_workbook(filename="阿里云天池电商婴儿用户数据.xlsx")
    sheet = workbook.active
    for cell in sheet["A"]:
        if cell.value == "待删除":
            sheet.delete_rows(idx=cell.row)
    workbook.save(filename="阿里云天池电商婴儿用户数据.xlsx")
    # 对表进行了修改,需要workbook.save

# 设置筛选出2014年之后的数据行,其余待删除
def search_col_value(num):
    workbook = load_workbook(filename="阿里云天池电商婴儿用户数据.xlsx")
    sheet = workbook.active
    # range为左闭右开区间,从第2行一直遍历到1001行
    for r in range(2,1002):
        cell = sheet.cell(row=r, column=num)
        # cell.value是int型,转为str型用字符串切片的方法把value中年份取出来,再转为整形和2014比较大小
        if cell.value !=None:
            if int(str(cell.value)[0:4]) > 2014:
                continue
            else:
                # 2014年以前的行删掉
                re_cell = sheet.cell(row=r, column=1)
                re_cell.value = "待删除"
        else:
            # value为空时默认在2014年之前,删掉该行
            re_cell = sheet.cell(row=r, column=1)
            re_cell.value = "待删除"
    workbook.save(filename="阿里云天池电商婴儿用户数据.xlsx")

# 设置返回剩余行数函数
def remain_row():
    from openpyxl import load_workbook
    workbook = load_workbook(filename="阿里云天池电商婴儿用户数据.xlsx")
    sheet = workbook.active
    sheet_size = sheet.dimensions     # 返回的是一个字符串如A1:G1001
    # 字符串切片取出行数,因为A1固定输出,所以行数为编号4以后(去掉“A1:G”即为行数)
    sheet_rows = sheet_size[4:]
    return(sheet_rows)

def main():
    col_num = search_col("day")     # 接收待遍历的列号
    search_col_value(col_num)     # 筛选2014年之后的数据行
    # 调用删除数据行函数,将待删除的数据行删除
    delete_row()

    workbook = load_workbook(filename="阿里云天池电商婴儿用户数据.xlsx")
    sheet = workbook.active
    # 获取buy_mount列号
    buy_col_num = search_col("buy_mount")
    sheet_rows = int(remain_row()) + 1     # 获取剩余行数,并str转int。range左闭右开所以要+1
    for r in range(2,sheet_rows):
        cell = sheet.cell(row=r, column=buy_col_num)
        if cell.value == None:
            # 默认待删除
            re_cell = sheet.cell(row=r, column=1)
            re_cell.value = "待删除"
        else:
            if int(cell.value) < 5:
                # 待删除
                re_cell = sheet.cell(row=r, column=1)
                re_cell.value = "待删除"
            else:
                if int(cell.value) >= 10:
                    pattern_fill = PatternFill(fill_type="solid", fgColor="DD001B")
                    for need_pattern_cell in sheet[r]:
                        need_pattern_cell.fill = pattern_fill

                    # 字体颜色白
                    font = Font(color="FFFFFF")
                    for change_fontColor_cell in sheet[r]:
                        change_fontColor_cell.font = font
    workbook.save(filename="阿里云天池电商婴儿用户数据.xlsx")
    # 再调用一次删除行数据函数,buy_mount小于10的待删除数据行删除
    delete_row()

main()

问题:delete_row时,不要放在循环中执行,因为删除了那一行,下面的行数据会向上自动填补,整张表的行标就变了,起不到删除目标行的作用。

解决方法:先将该行某一个cell的value置空,在主函数中用for循环找出空cell那一行再删掉,这种方法不涉及行号,不用担心目标行变化,为了避免某一个单元格本就是空的,可以将单元格value写如特定字符,如待删除字段

  • 记得选中最后一行下面的那一行,清除内容,里面好像有什么东西,输出表的大小时行数比实际多一行

  • 什么时候打开工作簿,什么时候对工作簿进行保存操作,都是需要斟酌的。写错了位置也会跑不出想要的结果
    在这里插入图片描述

  • 截图中单元格大小为手动调节,方便查看

©️2020 CSDN 皮肤主题: 游动-白 设计师:上身试试 返回首页