十二、处理Excel电子表格

关于《python编程快速上手-让繁琐工作自动化》的学习笔记

1.Excel表格

一个Excel表格文档称为一个工作簿，每个工作簿可以包含多个表（工作表），用户当前查看的表（或关闭Excel前最后查看的表）成为活动表。每个表都有一些列（地址从A开始的字母）和一些行（地址从1开始的数字），在特定的行和列的方格称为单元格。

• 安装openpyxl模块

>>>pip install openpyxl

• 读取Excel文档
  • 使用openpyxl模块打开Excel文档
    openpyxl.load_workbook()函数接受文件的路径，返回一个workbook数据类型的值。

  >>>import openpyxl
  >>>wb=openpyxl.load_workbook('example.xlsx')
  >>>type(wb)
  <class 'openpyxl.workbook.Workbook'>
  

  • 从工作簿中读取工作表
    调用sheetnames可以取得工作簿中的所有表名的列表，每个表由一个Worksheet对象表示，通过调用wb[‘Sheet1’]传递表名字符串获得，通过调用Workbook对象的active属性取得工作簿的活动表，取得Worksheet对象后可以通过title属性取得它的名称。

  >>>import openpyxl
  >>>wb=openpyxl.load_workbook('example.xlsx')
  >>>wb.sheetnames
  ['Sheet1', 'Sheet2', 'Sheet3']
  >>>>>> wb['Sheet1']
  <Worksheet "Sheet1">
  >>> sheet=wb['Sheet1']
  >>> sheet.title
  'Sheet1'
  >>> anothersheet=wb.active
  >>> anothersheet
  <Worksheet "Sheet1">
  

  • 从表中取得单元格
    获得了Worksheet对象后，可以按名字访问Cell对象。Cell对象有一个value属性，它包含这个单元格中保存的值；Cell对象也有row、column和coordinate属性，提供该单元格的行数、列数和坐标。使用表的cell()方法，传入行和列得到单元格的Cell对象。通过Worksheet对象的max_row和max_column属性可以确定表的大小。

  >>> import openpyxl
  >>> wb=openpyxl.load_workbook('example.xlsx')
  >>> sheet=wb['Sheet1']
  >>> sheet['A1'].value
  1
  >>>  a=sheet['A1']
  >>> a.row
  1
  >>> a.column
  1
  >>> a.coordinate
  'A1'
  >>> sheet.cell(row=1,column=2)
  <Cell 'Sheet2'.B1>
  >>> sheet.cell(row=1,column=2).value
  2
  >>>sheet.max_row
  2
  >>> sheet.max_column
  2
  

  • 列字母和数字之间的转换
    字母转换为数字，调用openpyxl.utils.column_index_from_string()函数；数字转换为字母，调用openpyxl.utils.get_column_letter()函数

  >>> import openpyxl
  >>> openpyxl.utils.get_column_letter(1)
  'A'
  >>> openpyxl.utils.get_column_letter(100)
  'CV'
  >>> openpyxl.utils.column_index_from_string('A')
  1
  >>> openpyxl.utils.column_index_from_string('ABC')
  731
  

  • 从表中取得行和列
    可以将Worksheet对象切片，取得表格中一行、一列或一个矩形区域中的所有Cell对象

  >>> import openpyxl
  >>> wb=openpyxl.load_workbook('example.xlsx')
  >>> sheet=wb['Sheeet1']
  >>> tuple(sheet['A1':'C3'])
  ((<Cell 'Sheeet1'.A1>, <Cell 'Sheeet1'.B1>, <Cell 'Sheeet1'.C1>), (<Cell 'Sheeet1'.A2>, <Cell 'Sheeet1'.B2>, <Cell 'Sheeet1'.C2>), (<Cell 'Sheeet1'.A3>, <Cell 'Sheeet1'.B3>, <Cell 'Sheeet1'.C3>))
  

2.写入Excel文档

• 创建并保存Excel文档
  调用openpyxl.Workbook()函数创建一个新的空Workbook对象。

  >>> import openpyxl
  >>> wb=openpyxl.Workbook()
  >>> sheet=wb.active
  >>> sheet.title
  'Sheet'
  >>> sheet.title='This is a test'
  >>> sheet.title
  'This is a test'
  >>> wb.save('TEST.xlsx')
  

• 创建和删除工作表
  使用create_sheet()和remove()方法可以在工作簿中添加和删除工作表

  >>> wb.create_sheet(index=0,title='test')
  <Worksheet "test">
  >>> wb.sheetnames
  ['test', 'This is a test', 'Sheet']
  >>> wb.create_sheet(index=1,title='test2')
  <Worksheet "test2">
  >>> wb.sheetnames
  ['test', 'test2', 'This is a test', 'Sheet']
  
  >>> wb.sheetnames
  ['test', 'This is a test', 'Sheet']
  >>> wb.remove(wb['test2'])
  >>> wb.sheetnames
  ['test', 'This is a test','Sheet']
  

• 将值写入单元格
  如果有单元格坐标的字符串，可以像字典的键一样将它用于Worksheet对象，指定要写入的单元格。

  >>> sheet=wb['This is a test']
  >>> sheet['A1']='Hello world!'
  >>> sheet['A1'].value
  'Hello world!'
  >>>wb.save('TEST.xlsx')
  

3.Font对象

Font对象的style属性影响文本在单元格的显示方式，要设置字体风格属性就需要向Font()函数传入关键字参数。

关键字参数	数据类型	描述
name	字符串	字体名称，诸如’Calibri’
size	整型	大小点数
bold	布尔型	True表示粗体
italic	布尔型	True表示斜体

>>> font1=openpyxl.styles.Font(name='Time New Roman',bold=True)
>>> sheet['A1'].font=font1
>>> font2=openpyxl.styles.Font(name='Time New Roman',bold=True,size=24)
>>> sheet['E5'].font=font2
>>> sheet['E5']='24 pt abc'

>>> wb.save('TEST.xlsx')

4.公式

公式以一个等号开始，可以配置单元格，让它包含通过其他单元格计算得到的值。也可以读取单元格中的公式，就像其他值一样。如果需要看到该公式的计算结果而不是计算公式，则需要将load_workbook()的data_only关键词参数设置为True

>>> import openpyxl
>>> wb=openpyxl.load_workbook('TEST.xlsx')
>>> wb.sheetnames
['This is a test', 'test']
>>> sheet=wb.active
>>> sheet['A2']=22
>>> sheet['A3']=33
>>> sheet['A4']=44
>>> sheet['A5']='=SUM(A2:A4)'

>>> sheet['A5'].value
'=SUM(A2:A4)'

5.调整行和列

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

  >>> sheet['B3']='Tall row'
  `>>> sheet['C4']='Wide column'
  >>> sheet.row_dimensions[3].height=70
  >>> sheet.column_dimensions['C'].width=20
  >>> wb.save('TEST.xlsx')
  

工作表的row_dimensions和column_dimensions是像字典一样的值，row_dimensions包含RowDimension对象，column_dimensions包含ColumnDimension对象。在row_dimensions中可以用行的编号来访问一个对象；在column_dimensions中可以用列的字母来访问一个对象。行的高度可以设置为0到409之间的整数或浮点数，这个值表示高度的点数。一点等于1/72英寸，默认行高为12.75.列宽可以设置为0到255之间的整数或浮点数。这个值表示使用默认字体大小时(11点)，单元格可以显示的字符数。默认列宽为8.43个字符。列宽为0或者行高为0将使单元格隐藏。

• 合并和差分单元格
  使用merge_cells()方法可以将一个矩形区域中的单元格合并为一个单元格。merge_cells()的参数是一个字符串，表示要合并的矩形区域左上角和右下角的单元格。要设置合并后的单元格的值，只需要设置合并单元格左上角单元格的值。

  >>> sheet.merge_cells('A6:D8')
  >>> sheet['A6']='合并单元格'
  >>> wb.save('TEST.xlsx')
  >>> sheet.unmerge_cells('A6:D8')
  >>> wb.save('TEST.xlsx')
  

使用unmerge_cells()方法拆分单元格。unmerge_cells()的参数是一个字符串，表示要拆分的合并前的矩形区域左上角和右下角的单元格。

• 冻结窗口
  在openpyxl中，每个Worksheet对象都有一个freeze_panes属性，可以设置为一个Cell对象或一个单元格坐标的字符串(注意：单元格上边的所有行和左边的所有列都会冻结，但单元格所在的行和列不会冻结)

  >>> sheet.freeze_panes='B2'
  >>> wb.save('TEST.xlsx')
  

  冻结窗口的例子

  freeze_panes的设置	冻结的行和列
  sheet.freeze_panes=‘A2’	行1
  sheet.freeze_panes=‘B1’	列1
  sheet.freeze_panes=‘B2’	行1和列A
  sheet.freeze_panes=‘C1’	列A和列B
  sheet.freeze_panes=‘C2’	行1和列A和列B
  sheet.freeze_panes='A1’或sheet.freeze_panes=None	没有冻结窗口

6.图表

• 创建图表需要的步骤：

  • 从一个矩形区域选择的单元格创建一个Reference对象
  • 通过传入Reference对象，创建一个Series对象
  • 创建一个Chart对象
  • 将Series对象添加到Chart对象
  • 可选地设置Chart对象的drawing.top、drawing.left、drawing.width和drawing.height变量
  • 将Chart对象添加到Worksheet对象

• 关于Reference对象的解释：
  Reference对象是通过调用openpyxl.charts.Reference()函数并传入3个参数创建：
  1.包含图表数据的Worksheet对象。
  2.两个整数的元组，代表矩形选择区域的左上角单元格，该区域包含图表数据：元组中第一个整数是行，第二个整数是列。
  3.两个整数的元组，代表矩形选择区域的右下角单元格，该区域包含图表数据：元组中第一个整数是行，第二个整数是列。

• 调用openpyxl.charts.BarChart()、openpyxl.charts.LineChart()、openpyxl.charts.ScatterChart()和openpyxl.charts.PieChart()创建一个条形图、折线图、散点图和饼图