excel自动化之openpyxl

读excel

用openpyxl模块打开excel文档

wb=openpyxl.load_workbokk(filename='./data/test.xlsx')

获取工作簿的sheet表的名称

wb.get_sheet_names() #返回列表

​

获取指定的sheet对象

sheet=wb.get_sheet_by_name('基本信息') #返回sheet对象

获取活动表

wb.get_active_sheet()  #返回sheet对象

获取sheet之后，接着就是定位cell

cell对象具有如下属性：

cell = sheet[‘A2’]
cell.value
cell.row
cell.column
cell.coordinate

定位cell(返回的是cell对象)

1.sheet['A2']  #直接类似于dict
2.sheet.cell(row=1,column=2)

取cell时也可以切片操作

for cell_row in sheet['A2':'E6']:
    for cell in cell_row:
        print(cell.coordinate,cell.value)

• 要访问特定行或列的单元格的值，也可以利用 Worksheet 对象的 rows 和 columns属性。

sheet.columns[0] 
输出:
/*(<Cell 基本信息.A1>,
 <Cell 基本信息.A2>,
 <Cell 基本信息.A3>,
 <Cell 基本信息.A4>,
 <Cell 基本信息.A5>,
 <Cell 基本信息.A6>,
 <Cell 基本信息.A7>)*/

写excel

创建新的工作簿对象

wb=openpyxl.Workbook()

给工作表设置名称

sheet = wb.get_active_sheet()
#此时默认有一个sheet表
wb.get_sheet_names()
#['Sheet']

sheet.title = '跟进记录表'

wb.save('./data/第一个工作簿.xlsx') #保存

创建sheet表

• 创建和删除工作表
  • 利用 create_sheet() and remove_sheet()方法，可以在工作簿中添加或删除工作表
    • create_sheet()方法返回一个新的 Worksheet 对象，名为 SheetX，它默认是工作 簿的最后一个工作表。可以利用 index 和 title 关键字参数，指定新工作表的 索引或名称。
    • remove_sheet()方法接受一个 Worksheet 对象作为其参数，而不是工作表名称的字符串。如果你只知道要删除的工作表的名称，就调用 get_sheet_by_name()，将它的返回值传入 remove_sheet()。
    • 在工作簿中添加或删除工作表之后，记得调用 save()方法来保存变更。

创建

wb = openpyxl.Workbook()

wb.create_sheet(title='销售记录')

wb.create_sheet(index=1,title='养殖技术')
#<Worksheet "养殖技术">
wb.get_sheet_names()
#['Sheet', '养殖技术', '销售记录']

删除sheet表

r_sheet = wb.get_sheet_by_name('养殖技术')
wb.remove_sheet(r_sheet)

修改某个cell值

sheet = wb.get_active_sheet()
sheet['A1'] = 'hello'
sheet['B2'] = 'world'
wb.save('./data/第二个工作簿.xlsx')	

示例：

import openpyxl
wb = openpyxl.load_workbook('./data/produceSales.xlsx')
sheet = wb.get_sheet_by_name('Sheet')
PRICE_UPDATES = {'Garlic': 3.07,
                 'Celery': 1.19,
                 'Lemon': 1.27}
for rowNum in range(2, sheet.get_highest_row()+1): 
    produceName = sheet.cell(row=rowNum, column=1).value
    if produceName in PRICE_UPDATES:
        sheet.cell(row=rowNum, column=2).value = PRICE_UPDATES[produceName]
wb.save('./data/updatedProduceSales.xlsx')

修改单元格的字体风格

​ 首先我们需要导入字体包，用于生成对应的字体对象

from openpyxl.styles import Font,Style

​ cell.style

sheet['A3'].style #返回当前单元格的默认风格

​ 可以调用 Font()来创建一个 Font 对象，并将这个 Font 对象保存在一个变量中。 然后将它传递给 Style()，得到 的 Style 对象保存在一个变量中，并将该变量赋给 Cell 对象的 style 属性。

f = Font(sz=50,b=True,i=True)
s = Style(font=f)
sheet['A3'].style = s
wb.save('./data/Styles.xlsx')

单元格添加公式

• 利用 openpyxl 模块，用编程的方式在单元格中添加公式，就像 添加普通的值一样。例如:
  • sheet[‘B9’] = ‘=SUM(B1:B8)’

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.get_active_sheet()
sheet['A1'] = 200
sheet['A2'] = 300
sheet['A3'] = '=SUM(A1:A2)'
wb.save('./data/sum.xlsx')

调整单元格行和列

• 在 Excel 中，调整行和列的大小非常容易，只要点击并拖动行的边缘，或列的 头部。但如果你需要根据单元格的内容来设置行或列的大小，或者希望设置大量电子表格文件中的行列大小，编写 Python 程序来做就要快得多。

• 设置行高和列宽

  • Worksheet 对象有 row_dimensions 和 column_dimensions 属性，控制行高和列宽。

sheet.row_dimensions

#输出 {1: <openpyxl.worksheet.dimensions.RowDimension at 0x108af1f98>,
 2: <openpyxl.worksheet.dimensions.RowDimension at 0x108af1dd8>}

sheet.column_dimensions

#输出 DimensionHolder([('A',
                  <openpyxl.worksheet.dimensions.ColumnDimension at 0x108af1c88>),
                 ('B',
                  <openpyxl.worksheet.dimensions.ColumnDimension at 0x108af1978>)])

设置行高

#设置行高
sheet.row_dimensions[2].height = 50

设置列宽

#设置列宽
sheet.column_dimensions['A'].width = 80

• 注意：
  • 行的高度可以设置为 0 到 409 之间的整数或浮点值。这个值表示高度的点数。
    • 一点等于 1/72 英寸。默认的行高是 12.75。
  • 列宽可以设置为 0 到 255 之间的整数或浮点数。
    • 默认的列宽是 8.43 个字符。列宽为零或行高为零，将使单元格隐藏。

合并和拆分单元格

• 利用 merge_cells()工作表方法，可以将一个矩形区域中的单元格合并为一个单元格。
• 要拆分单元格，就调用 unmerge_cells()工作表方法。

sheet.merge_cells('A1:D7')

sheet.unmerge_cells('A1:D7')

练习题

1.openpyxl.load_workbook()函数返回什么?

​ excel的文件对象

2.get_sheet_names()工作簿方法返回什么?

​ sheet列表

3.如何取得名为’Sheet1’的工作表的 Worksheet 对象?

​ 文件对象.get_sheet_by_name(‘Sheet1’)

4.如何取得工作簿的活动工作表的 Worksheet 对象?

​ 文件对象.get_active_sheet()

5.如何取得单元格 C5 中的值?

​ sheet对象[‘C5’]

​ sheet对象.cell(row=5,column=3).value

6.如何将单元格 C5 中的值设置为"Hello"?

​ sheet对象.cell(row=5,column=3).value=‘Hello’

​ sheet对象[‘C5’]=‘Hello’

7.工作表方法 get_highest_column()和 get_highest_row()返回什么?

​ 此方法已被废弃

​ 您可以通过调用工作表的max_row或max_column属性获得最高的行或列。

8.如何取得从 A1 到 F1 的所有 Cell 对象的元组?

	for row in range(1,2):
		for col in range(1,7):
    		print(Sheet1.cell(row=row,column=col).value)

9.如何将工作簿保存到文件名 example.xlsx?

​ wb.save(路径)

10.如何在一个单元格中设置公式?

​ 和数字文本一样设置

11.如果需要取得单元格中公式的结果，而不是公式本身，必须先做什么?

​ load_workbook(… read_only=true)

12.如何将第 5 行的高度设置为 100?

​ sheet对象.row_dimensions[5].height=100

13.如何设置列 C 的宽度?

​ Sheet对象.column_dimensions[‘C’].width=100

14.什么是冻结窗格?

• 对于太大而不能一屏显示的电子表格，“冻结”顶部的几行或最左边的几列，是很有帮助的。在 OpenPyXL 中，每个 Worksheet 对象都有一个 freeze_panes 属性，可以设置为一个 Cell 对象或一个单元格坐标的字符串。
  • 请注意，单元格上边的所有行和左边的所有列都会冻结，但单元格所在的行和列不会冻结。

实践项目

• 创建一个9*9乘法表

  for i in range(1,10):
      for j in range(1,i+1):
          print(i,j)
          sheet.cell(row=i,column=j).value=f'{j}*{i}={i*j}'
  

• 编写一个程序，翻转电子表格中行和列的单元格。例如，第 5 行第 3 列的值将 出现在第 3 行第 5 列(反之亦然)。这应该针对电子表格中所有单元格进行