devbox
小丑西瓜9
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

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

作者：小丑西瓜9 | 2024-06-06 09:36:48

赞

踩

自动化办公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 = 修改/添加内容

  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. """
  2. Author:  余婷
  3. Create Time: 2024/5/28 16:58
  4. 你只管努力，时间会给你惊喜！
  5. """
  6. import openpyxl
  7. from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
  8.  
  9. wb = openpyxl.load_workbook('files/data1.xlsx')
  10. sheet1 = wb['Sheet']
  11.  
  12.  
  13. # 1. 设置字体
  14. # 1)创建字体对象
  15. f1 = Font(
  16.     name='楷体',
  17.     color='990033',
  18.     size=20,
  19.     bold=True
  20. )
  21. # 2)设置单元格的字体
  22. sheet1.cell(2, 2).font = f1
  23.  
  24. # 2. 填充单元格
  25. # 1)创建填充对象
  26. fill1 = PatternFill(
  27.     fill_type='solid',
  28.     start_color='FFFFCC'
  29. )
  30. # 2)设置单元格的填充样式
  31. sheet1.cell(2, 2).fill = fill1
  32.  
  33. # 3. 设置边框样式
  34. # 1）创建边对象
  35. s1 = Side(
  36.     border_style='medium',
  37.     color='663366'
  38. )
  39. s2 = Side(
  40.     border_style='mediumDashDot',
  41.     color='009966'
  42. )
  43. # 2)创建边框对象
  44. border1 = Border(
  45.     bottom=s1,
  46.     top=s1,
  47.     left=s2,
  48.     right=s2
  49. )
  50.  
  51. # 3)设置单元格的边框
  52. sheet1.cell(2, 2).border = border1
  53.  
  54.  
  55. wb.save('files/data1.xlsx')

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小丑西瓜9/article/detail/680453
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号