深入浅出理解数据分析系列之:python openpyxl库处理Excel文档
openpyxl是一个读写Excel2010(xlsx/xlsm)文档的Python库,能够同时读取和修改Excel文档。一个Excel文档称为一个工作薄,在Office2010下,典型工作薄的文件扩展名为xlsx。一个工作薄可以包含多个表格。Excel通过行和列表示一个单元格,行的坐标使用数字表示,列的坐标使用字母表示。例如表格左上角的单元格,其坐标为"A1"。
openpyxl是一个开源项目,在使用之前需要先进行安装:
pip install openpyxl
openpyxl有三个不同层次的类,分别是Workbook、Worksheet和Cell。
Workbook是对Excel工作薄的抽象,Worksheet是对表格的抽象,Cell是对单元格的抽象。
一、工作簿Workbook
在操作一个Excel之前,应该先创建一个Workbook对象。对于创建一个新的Excel文档,直接进行Workbook类调用即可。对于读取一个已经有的Excel文档,可以使用openpyxl模块的load_workbook函数。该函数接受多个参数,但只有filename参数为必传参数。
import openpyxl
wb = openpyxl.load_workbook("example.xlsx")
workbook对象有多种属性和方法:
- active:获取活跃的Worksheet
- read_only:是否以read_only模式打开Excel文档
- encoding:文档的字符集编码
- properties:文档的元素及,如标题、创建者、创建日期等
- worksheets:以列表的形式返回所有的Worksheet
import openpyxl
wb = openpyxl.load_workbook(r"D:/tmp/test.xlsx")
wb.active
#<Worksheet "Sheet1">
wb.read_only
#False
wb.encoding
#utf-8
wb.worksheets
#[<Worksheet "Sheet1">]
Workbook对象的方法大都与Worksheet相关。常用的方法如下:
- sheetnames:获取所有表格的名称
- wb[表名]:通过表格名称获取Worksheet对象
- get_active_sheet:获取活跃的表格
- remove_sheet:删除一个表格
- create_sheet:创建一个空的表格
- copy_worksheet:在Workbook内拷贝表格
wb.sheetnames
#['学生信息']
wb["学生信息"]
#<Worksheet "学生信息">
二、表单Worksheet
通过Worksheet对象获取表格属性,得到单元格中的数据,修改表格中的内容。
- title:表格的标题
- dimension:表格的大小
- max_row:表格的最大行
- min_row:表格的最小行
- max_column:表格的最大列
- min_column:表格的最小列
- rows按行获取单元格(cell对象)
- columns按列获取单元格(cell对象)
- freeze_panes冻结窗格,主要用于在表格较大时冻结顶部的行或左边的列。对于冻结的行或列,就算用户滚动电子表格,也是始终可见的
- values按行获取表格的内容(数据)
ws = wb["学生信息"]
#学生信息
ws.title
#A1:H5
ws.dimensions
#8
ws.max_column
#1
ws.min_column
#<generator object Worksheet._cells_by_col at 0x000002314A4E6930>
ws.columns
#<generator object Worksheet._cells_by_row at 0x000002314A4E6930>
ws.rows
#<generator object Worksheet.values at 0x000002314A4E6930>
ws.values
这段代码中,通过Workbook[表名]获取Worksheet对象。通过不同的属性名获取student这张表的属性。其中,columns、rows和values这几个属性都是通过生成器的方式返回数据。
Worksheet常用的一些方法:
- iter_rows:按行获取所有单元格(Cell对象)
- iter_columns:案列获取所有的单元格
- append:在表格末尾添加数据
- merged_cells:合并多个单元格
- unmerge_cells:移除合并的单元格
其中,iter_rows方法和iter_columns方法在参数取默认值时,与rows属性和columns属性的作用相同。区别在于,iter_rows方法和iter_columns方法可以通过函数参数限定访问表格的范围。
list(ws.iter_rows(min_row=2,max_row=4,min_col=1,max_col=3))
[(<Cell '学生信息'.A2>, <Cell '学生信息'.B2>, <Cell '学生信息'.C2>), (<Cell '学生信息'.A3>, <Cell '学生信息'.B3>, <Cell '学生信息'.C3>), (<Cell '学生信息'.A4>, <Cell '学生信息'.B4>, <Cell '学生信息'.C4>)]
从Worksheet的属性和方法的使用中可以看到,很多属性和方法返回的是一个Cell对象。一个Cell对象就代表一个单元格,可以直接使用Excel坐标的方式获取Cell对象,也可以使用Worksheet的cell方法获取cell对象。
ws['A2']
ws.cell(row=1,column=2)
#<Cell '学生信息'.A2>
#<Cell '学生信息'.B1>
三、行row、列column、单元格Cell
Cell对象常用属性如下;
- row:单元格所在的行
- column:单元格所在的列
- value:单元格的取值
- cordinate:单元格的坐标
用四种方法打印student表中的内容:
#"第一种方法:"
for row in ws.rows:
print(*row)
#"第二种方法:"
for row in ws.rows:
print(*[cell.value for cell in row])
#"第三种方法:"
for row in ws.iter_rows():
print(*[cell.value for cell in row])
#"第四种方法:"
for i in range(ws.min_row,ws.max_row + 1):
for j in range(ws.min_column,ws.max_column + 1):
print(ws.cell(row=i,column=j).value,end='')
print()
四、 使用openpyxl修改Excel文档
openpyxl可以读取Excel文档,而且还可以修改Excel文档,包括修改单元格的数据、合并单元格、修改单元格的字体、在Excel文档中画图等。
新建一个工作薄就是创建一个Workbook对象,创建完Workbook对象以后,默认会有一个名为”sheet1“的表格,通过表格的名称来获取这个表格。获取表格以后,通过给表格的title属性赋值的方式来修改表格的名称。
import openpyxl
import datetime
#创建一个Workbook对象
wb = openpyxl.Workbook()
#['Sheet']
#获取Worksheet对象
ws = wb['Sheet']
ws.title = "学生信息"
#创建一个worksheet对象
wb.create_sheet(index=0,title="new sheet")
#['new sheet', '学生信息']
print(wb.sheetnames)
#删除一个worksheet对象
wb.remove_sheet(wb['new sheet'])
#['学生信息']
print(wb.sheetnames)
#填充单元格的数据
ws['A1'] = 'Hello world'
ws['A2'] = datetime.datetime.now()
#调用Workbook的save方法将数据保存到磁盘之中
wb.save(r"D:\tmp\openpyxl.xlsx")
五、案例:统计平均分和总分
import openpyxl
def process_worksheet(sheet):
avg_column = sheet.max_column + 1
sum_column = sheet.max_column + 2
for row in sheet.iter_rows(min_row=2,min_col=6):
scores = [cell.value for cell in row]
sum_score = sum(scores)
avg_score = sum_score / len(scores)
# 计算平均分总和,并且保存到最后两列
sheet.cell(row=row[0].row,column=avg_column).value = avg_score
sheet.cell(row=row[0].row,column=sum_column).value = sum_score
sheet.cell(row=1,column=avg_column).value = '平均分'
sheet.cell(row=1,column=sum_column).value = '总分'
def main():
wb = openpyxl.load_workbook(r"D:/tmp/test.xlsx")
ws = wb["学生信息"]
process_worksheet(ws)
wb.save(r"D:/tmp/学生成绩统计.xlsx")
if __name__ == '__main__':
main()
六、案例:合并多个Excel文档到一个Excel文档
将多个Excel文档合并到到单个文件
合并excel的思路是:1)获取第一个文档中的表格2)依次遍历其他文件中的报名表,并通过iter_rows函数忽略报名表中的首行内容3)通过列表推导获取报名表中的数据,然后调用Worksheet的append函数将数据添加到汇总表的末尾。
import os
#glob模块的主要方法就是glob,该方法返回所有匹配的文件路径列表(list)
#该方法需要一个参数用来指定匹配的路径字符串(字符串可以为绝对路径也可以为相对路径)
#其返回的文件名只包括当前目录里的文件名,不包括子文件夹里的文件
import glob
import openpyxl
def merge_xlsx_files(xlsx_files):
wb = openpyxl.load_workbook(xlsx_files[0])
ws = wb.active
ws.title = "merged result"
for filename in xlsx_files[1:]:
workbook = openpyxl.load_workbook(filename)
sheet = workbook.active
for row in sheet.iter_rows(min_row=2):
values = [cell.value for cell in row]
ws.append(values)
return wb
def get_all_xlsx_files(path):
xlsx_files = glob.glob(os.path.join(path,'*.xlsx'))
sorted(xlsx_files,key=str.lower)
return xlsx_files
def main():
xlsx_files = get_all_xlsx_files(r"D:\tmp\test")
wb = merge_xlsx_files(xlsx_files)
wb.save(r"D:\tmp\test\merged_form.xlsx")
if __name__ == '__main__':
main()
七、glob补充
glob是python自带的文件操作相关模块,用于查找符合条件的文件,支持通配符操作。
glob方法:
glob模块的主要方法就是glob,该方法返回所有匹配的文件路径列表。
该方法需要一个参数用来指定匹配的路径字符串(字符串可以为绝对路径也可以为相对路径),其返回的文件名只包括当前目录里的文件名,不包括子文件夹里的文件。
xlsx_files = glob.glob(os.path.join(path,'*.xlsx'))