• 自动化办公02 用openpyxl库操作excel.xlsx文件(新版本)

    目录

    一、文件读操作

    二、文件写操作

    三、修改单元格样式

    openpyxl 是一个处理Excel表格的第三方库。openpyxl 库可以处理Excel2010以后的电子表格格式，包括：xlsx/xlsm/xltx/xltm。

    openpyxl教程

    一、文件读操作

    工作簿(workbook): excel文件
    工作表(worksheet): 工作簿中的每一个活动就是一个工作表
    单元格(cell): 工作表中用来存储数据的每个格子

    注意：openpyxl只能操作新版本的excel文件(后缀为.xlsx)

    1. 打开excel文件，得到一个工作簿对象

    openpyxl.load_workbook(excel文件路径)

    2. 获取工作簿相关信息

    1）获取工作簿中所有的工作表的名称

    变量 = 工作簿.sheetnames

    2) 获取所有的工作表，返回值是一个列表，列表中的元素是工作表对象

    变量 = 工作簿.worksheets

    3) 获取指定工作表:

    变量 = 工作簿对象[表名]

    4) 获取活跃表(当前处于选中状态的工作表)

    变量 = 工作簿.active

    3. 获取工作表的相关信息

    1) 获取数据的最大行数和列数
       工作表对象.max_row   -  获取最大行数
       工作表对象.max_column  -   获取最大列数

    2) 获取指定单元格(注意这里写的是行号和列号，都是从1开始)

    变量 = 工作表.cell(row, column)

    4. 获取单元格相关信息

    获取单元格内容

    变量 = 工作表.cell(row, column).value

    1. import openpyxl
    2. # 注意：openpyxl只能操作新版本的excel文件(后缀为.xlsx)
    3.  
    4. # 工作簿(workbook): excel文件
    5. # 工作表(worksheet): 工作簿中的每一个活动就是一个工作表
    6. # 单元格(cell): 工作表中用来存储数据的每个格子
    7.  
    8. # 1. 打开excel文件，得到一个工作簿对象
    9. # openpyxl.load_workbook(excel文件路径)
    10. workbook = openpyxl.load_workbook('files/2020年销售数据.xlsx')
    11.  
    12. # 2. 获取工作簿相关信息
    13. # 1）获取工作簿中所有的工作表的名称
    14. names = workbook.sheetnames
    15. print(names)
    16.  
    17. # 2)获取所有的工作表，返回值是一个列表，列表中的元素是工作表对象
    18. all_sheet = workbook.worksheets
    19. print(all_sheet)
    20.  
    21. # 3)获取指定工作表: 工作簿对象[表名]
    22. sheet1 = workbook['data']
    23. print(sheet1)
    24.  
    25. # 4)获取活跃表(当前处于选中状态的工作表)
    26. sheet2 = workbook.active
    27. print(sheet2)
    28.  
    29. # 3. 获取工作表的相关信息
    30. # 1)获取数据的最大行数和列数
    31. # 工作表对象.max_row   -  获取最大行数
    32. # 工作表对象.max_column  -   获取最大列数
    33. print(sheet1.max_row, sheet1.max_column)
    34. print(sheet2.max_row, sheet2.max_column)
    35.  
    36. # 2)获取指定单元格
    37. # 注意这里写的是行号和列号，都是从1开始
    38. cell1 = sheet1.cell(7, 4)
    39. print(cell1)
    40.  
    41. # 4. 获取单元格相关信息
    42. # 获取单元格内容
    43. result = cell1.value
    44. print(result)

    练习：

    1. import openpyxl
    2.  
    3. # 获取工作簿
    4. wb = openpyxl.load_workbook('files/2020年销售数据.xlsx')
    5. # 获取工作表
    6. datasheet = wb['data']
    7.  
    8. #  案例：获取第五行
    9. max_col = datasheet.max_column  # 最大列数
    10. data = []
    11. for col in range(1, max_col + 1):
    12.     cell = datasheet.cell(5, col)
    13.     data.append(cell.value)
    14. print(data)
    15.  
    16. print('-----------------------分------割------线-----------------------')
    17.  
    18. # 练习1：获取data中第5列所有的数据
    19. data1 = []
    20. max_row = datasheet.max_row
    21. for row in range(1, max_row + 1):
    22.     cell = datasheet.cell(row, 5)
    23.     data1.append(cell.value)
    24. print(data1)
    25.  
    26. print('-----------------------分------割------线-----------------------')
    27.  
    28. # 练习2：获取所有的品牌(列表去重)
    29. brand_list = []
    30. max_row = datasheet.max_row
    31. for row in range(3, max_row + 1):
    32.     value = datasheet.cell(row, 5).value
    33.     if value not in brand_list:
    34.         brand_list.append(value)
    35. print(brand_list)
    36.  
    37. print('-----------------------分------割------线-----------------------')
    38.  
    39. # 练习3：计算不同品牌的总的销售额
    40. # 方法1：使用上面已经搜索出来的品牌列表
    41. sale = {}
    42. for b in brand_list:
    43.     sum1 = 0
    44.     for row in range(3, max_row+1):
    45.         if datasheet.cell(row, 5).value == b:
    46.             sum1 += datasheet.cell(row, 8).value
    47.     sale[b] = sum1
    48. print(sale)
    49.  
    50. print('-----------------------分------割------线-----------------------')
    51.  
    52. # print(type(datasheet.cell(row, 5).value))
    53. # 方法2：
    54. dic = {}
    55. for row in range(3, max_row + 1):
    56.     brand = datasheet.cell(row, 5).value
    57.     sale = datasheet.cell(row, 8).value
    58.     # a.使用setdefault函数添加键值对
    59.     # dic.setdefault(brand, 0)
    60.     # dic[brand] += sale
    61.     # b.使用if判断
    62.     # if brand in dic:
    63.     #     dic[brand] += sale
    64.     # else:
    65.     #     dic[brand] = sale
    66.     # c.使用.get()方法读取第一个数据，则不会报错
    67.     dic[brand] = dic.get(brand, 0) + sale
    68. print(dic)
    69.  
    70. print('-----------------------分------割------线-----------------------')
    71.  
    72. dic = {'八匹马': 0, '皮皮虾': 0, '壁虎': 0, '花花姑娘': 0, '啊哟喂': 0}
    73. # dic = {}
    74. max_row = datasheet.max_row
    75. for row in range(3, max_row + 1):
    76.     value = datasheet.cell(row, 5).value
    77.     for d in list(dic.keys()):
    78.         if value == d:
    79.             dic[d] += datasheet.cell(row, 8).value
    80. print(dic)
    81.  
    82. print('-----------------------分------割------线-----------------------')
    83.  
    84. # data2 = []
    85. # for row in range(1, max_row):
    86. #     temp = []
    87. #     for col in range(1, max_col):
    88. #         cell = datasheet.cell(row, col)
    89. #         temp.append(cell.value)
    90. #     data2.append(temp)
    91. # print(data2)

    二、文件写操作

    注意：所有写操作在保存后才会有效

    1. 新建工作簿(创建一个工作簿对象)

    新建的工作簿中默认有一个工作表

    变量 = openpyxl.Workbook()

    2. 添加工作表

    工作簿对象.create_sheet()  -  在工作簿的最后添加一个名字为Sheet?的工作表

    工作簿对象.create_sheet(表名)    -   在工作簿的最后添加一个名字为指定值的工作表

    工作簿对象.create_sheet(表名,下标)  -  在指定下标对应位置添加名字为指定值的工作表

    3. 删除工作表

    工作簿对象.remove(工作簿[表名])

    del 工作簿[表名] 

    4.修改单元格内容

    工作表.cell(row, column, 修改/添加内容)

    工作表.cell(row, column).value = 修改/添加内容

    5.添加一行内容

    a.使用循环，用 工作表.cell() 一格一格的写

    b.使用 工作表.append() 向列表一样直接添加一行数据

    如果要在末尾追加，则

    先将当前行设置为最大行数 工作表._current_row = 工作表.max_row

    然后再使用append追加

    1. import openpyxl
    2.  
    3. # 1. 新建工作簿(创建一个工作簿对象)
    4. wb = openpyxl.Workbook()
    5.  
    6. # 2. 添加工作表
    7. wb.create_sheet()
    8.  
    9. # 注意：添加工作表的逻辑 - 不存在才添加
    10. if 'students' not in wb.sheetnames:
    11.     wb.create_sheet('students')
    12.  
    13. wb.create_sheet('teachers', 0)
    14.  
    15. # 3. 删除工作表
    16. # 工作簿对象.remove(工作簿[表名])
    17. # del 工作簿[表名]
    18. if 'Sheet' in wb.sheetnames:
    19.     wb.remove(wb['Sheet'])
    20.  
    21. if 'Sheet1' in wb.sheetnames:
    22.     del wb['Sheet1']
    23.  
    24. # 4.修改单元格内容
    25. students_sheet = wb['students']
    26. students_sheet.cell(1, 1, '姓名')     # 方法1
    27. students_sheet.cell(1, 2).value = '年龄'    # 方法2
    28.  
    29. wb.save('files/school.xlsx')

    案例：

    1. import openpyxl
    2.  
    3. # 1.将data数据写深入到新建的excel中默认的工作表中的第二行
    4. data = ['2020-1-1', '上海', '天猫', '205654-021', '八匹马', 169, 85, 14365]
    5.  
    6. # 准备工作表
    7. wb1 = openpyxl.Workbook()
    8. sheet = wb1.active
    9.  
    10. # 写入数据
    11. for x in range(len(data)):
    12.     value = data[x]
    13.     col = x + 1
    14.     sheet.cell(2, col, value)
    15.  
    16. wb1.save('files/data1.xlsx')

    练习：

     练习1：将data中的写入到新建的表中的第3列中

    1. # 练习1：将data中的写入到新建的表中的第3列中
    2. data = ['八匹马', '皮皮虾', '壁虎', '花花姑娘', '啊哟喂']
    3. sheet1 = wb1.create_sheet('练习1')
    4.  
    5. for x in range(len(data)):
    6.     row = x + 1
    7.     value = data[x]
    8.     sheet1.cell(row, 3, value)
    9.  
    10. wb1.save('files/data1.xlsx')

     练习2：将class1中所有学生的信息以合理方式保存到新建的'学生表'中

    1. # 练习2：将class1中所有学生的信息以合理方式保存到新建的'学生表'中
    2. class1 = {
    3.     'name': 'python2402',
    4.     'address': '6教室',
    5.     'lecturer': {'name': '余婷', 'age': 18, 'gender': '女', 'qq': '726550822'},
    6.     'head_teacher': {'name': '舒嚒嚒', 'age': 18, 'gender': '女', 'tel': '110'},
    7.     'students': [
    8.         {'name': 'stu1', 'age': 17, 'gender': '男', 'score': 89, 'link_man': {'name': '张三', 'tel': '122334'}},
    9.         {'name': 'stu2', 'age': 28, 'gender': '女', 'score': 99, 'link_man': {'name': '李四', 'tel': '29833'}},
    10.         {'name': 'stu3', 'age': 22, 'gender': '女', 'score': 65, 'link_man': {'name': '王五', 'tel': '22223'}},
    11.         {'name': 'stu4', 'age': 22, 'gender': '男', 'score': 77, 'link_man': {'name': '赵六', 'tel': '6544'}},
    12.         {'name': 'stu5', 'age': 21, 'gender': '男', 'score': 46, 'link_man': {'name': '何七', 'tel': '664322'}},
    13.         {'name': 'stu6', 'age': 16, 'gender': '女', 'score': 82, 'link_man': {'name': '李八', 'tel': '12278334'}}
    14.     ]
    15. }
    16. data = ['姓名', '年龄', '性别', '分数', '联系人', '联系人电话']
    17. sheet2 = wb1.create_sheet('学生表')
    18. for x in range(len(data)):
    19.     col = x + 1
    20.     value = data[x]
    21.     sheet2.cell(1, col, value)
    22. student_list = class1['students']
    23. for x in range(len(student_list)):
    24.     stu = student_list[x]
    25.     row = x + 2
    26.     stu_value = list(stu.values())
    27.     link_man = stu_value.pop()
    28.     # print(stu_value,link_man)   # ['stu1', 17, '男', 89] {'name': '张三', 'tel': '122334'}
    29.     stu_value.append(link_man['name'])
    30.     stu_value.append(link_man['tel'])
    31.     # print(stu_value)    # ['stu1', 17, '男', 89, '张三', '122334']
    32.     for y in range(len(stu_value)):
    33.         col = y + 1
    34.         value = stu_value[y]
    35.         sheet2.cell(row, col, value)
    36. wb1.save('files/data1.xlsx')

    练习3：将所有的0分都替换成补考；添加总分列，并且计算出每个学生的总分 

    1. import openpyxl
    2.  
    3. # 练习3：将所有的0分都替换成补考；添加总分列，并且计算出每个学生的总分
    4. wb = openpyxl.load_workbook('files/data2.xlsx')
    5.  
    6. sheet1 = wb['Sheet1']
    7.  
    8. max_row = sheet1.max_row  # 19
    9. max_col = sheet1.max_column  # 5
    10. sheet1.cell(1, max_col + 1, '总分')
    11. for row in range(2, max_row + 1):
    12.     sum1 = 0
    13.     for col in range(2, max_col + 1):
    14.         value = sheet1.cell(row, col).value
    15.         sum1 += value
    16.         if value == 0:
    17.             sheet1.cell(row, col).value = '补考'
    18.     sheet1.cell(row, max_col + 1).value = sum1
    19.  
    20. wb.save('files/data2.xlsx')

    三、修改单元格样式

    1. import openpyxl
    2. from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
    3.  
    4. # 1.打开工作簿
    5. wb = openpyxl.load_workbook('files/school.xlsx')
    6. sheet = wb.active
    7.  
    8. # 2.设置单元格字体样式
    9. """
    10. Font(
    11.     name=None,      # 字体名，可以用字体名字的字符串
    12.     strike=None,    # 删除线，True/False
    13.     color=None,     # 文字颜色
    14.     size=None,      # 字号
    15.     bold=None,      # 加粗, True/False
    16.     italic=None,    # 倾斜，Tue/False
    17.     underline=None # 下划线, 'singleAccounting', 'double', 'single', 'doubleAccounting'
    18. )
    19. """
    20. # 1) 创建字体对象
    21. font1 = Font(
    22.         size=20,
    23.         italic=True,
    24.         color='ff0000',
    25.         bold=True,
    26.         strike=True
    27. )
    28. # 2) 设置指定单元格的字体
    29. # 单元格对象.font = 字体对象
    30. sheet['B2'].font = font1
    31.  
    32.  
    33. # 3. 设置单元格填充样式
    34. """
    35. PatternFill(
    36. 	fill_type=None,		# 设置填充样式: 'darkGrid', 'darkTrellis', 'darkHorizontal', 'darkGray', 'lightDown', 'lightGray', 'solid', 'lightGrid', 'gray125', 'lightHorizontal', 'lightTrellis', 'darkDown', 'mediumGray', 'gray0625', 'darkUp', 'darkVertical', 'lightVertical', 'lightUp'
    37. 	start_color=None	# 设置填充颜色
    38. )
    39. """
    40. # 1） 设置填充对象
    41. fill = PatternFill(
    42.         fill_type='solid',
    43.         start_color='ffff00'
    44. )
    45. # 2）设置单元格的填充样式
    46. # 单元格对象.fill = 填充对象
    47. sheet['B2'].fill = fill
    48.  
    49.  
    50. # 4. 设置单元格对齐样式
    51. # 1）创建对象
    52. al = Alignment(
    53.         horizontal='right',     # 水平方向:center, left, right
    54.         vertical='top'       # 垂直方向: center, top, bottom
    55. )
    56. # 2） 设置单元格的对齐方式
    57. sheet['B2'].alignment = al
    58.  
    59. # 5. 设置边框样式
    60. # 1）设置边对象（四个边的边可以是一样的也可以不同，如果不同就创建对个Side对象）
    61. side = Side(border_style='thin', color='0000ff')
    62. # 2) 设置边框对象
    63. # 这儿的left、right、top、bottom表示的是边框的四个边，这儿四个边使用的是一个边对象
    64. bd = Border(left=side, right=side, top=side, bottom=side)
    65. # 3）设置单元格的边框
    66. sheet['B2'].border = bd
    67.  
    68. # 6.设置单元格的宽度和高度
    69. # 设置指定列的宽度
    70. sheet.column_dimensions['A'].width = 20
    71. # 设置指定行的高度
    72. sheet.row_dimensions[1].height = 45
    73.  
    74. # 7. 保存
    75. wb.save('files/school.xlsx')

    简单示例：

    1. import openpyxl
    2. from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
    3.  
    4. wb = openpyxl.load_workbook('files/data1.xlsx')
    5. sheet1 = wb['Sheet']
    6.  
    7.  
    8. # 1. 设置字体
    9. # 1)创建字体对象
    10. f1 = Font(
    11.     name='楷体',
    12.     color='990033',
    13.     size=20,
    14.     bold=True
    15. )
    16. # 2)设置单元格的字体
    17. sheet1.cell(2, 2).font = f1
    18.  
    19. # 2. 填充单元格
    20. # 1)创建填充对象
    21. fill1 = PatternFill(
    22.     fill_type='solid',
    23.     start_color='FFFFCC'
    24. )
    25. # 2)设置单元格的填充样式
    26. sheet1.cell(2, 2).fill = fill1
    27.  
    28. # 3. 设置边框样式
    29. # 1）创建边对象
    30. s1 = Side(
    31.     border_style='medium',
    32.     color='663366'
    33. )
    34. s2 = Side(
    35.     border_style='mediumDashDot',
    36.     color='009966'
    37. )
    38. # 2)创建边框对象
    39. border1 = Border(
    40.     bottom=s1,
    41.     top=s1,
    42.     left=s2,
    43.     right=s2
    44. )
    45.  
    46. # 3)设置单元格的边框
    47. sheet1.cell(2, 2).border = border1
    48.  
    49.  
    50. wb.save('files/data1.xlsx')

  • 相关阅读:
    安装CUDA、anaconda、pytorch
    【Echarts】自定义提示框tooltip样式，实现点击路由跳转
    CSS零碎知识点记录
    go-zero服务自动收集线上问题线上实战
    （附源码）springboot教学评价 毕业设计 641310
    Qt 项目实战 | 音乐播放器
    【STL】用一棵红黑树封装map和set
    基于SpringBoot+Vue的动漫漫画投稿网站 element
    FL Studio 2023中文安装设置指南！四招教你玩转FL Studio21！
    基于C#实现的《勇士返乡》游戏设计
  • 原文地址：https://blog.csdn.net/m0_67182197/article/details/139373234