本章介绍excel自动化办公——openpyxl库的使用。我觉得openpyxl是一款轻量级的excel操作库,适合用于一些基本的操作构建,如果涉及批量处理数据,如求一整列的最大值最小值等,无疑是pandas更好;对于一些单元格背景填充、单元格合并、批量创建sheet等的操作,无疑是openpyxl好,因为pandas无法方便地完成此类操作。python库之间的兼容性大,pandas导出到excel时,如果没有安装openpyxl,将无法完成该操作。可以说,学好openpyxl库,能为以后学pandas数据分析打好良好的基础。
必读:openpyxl 旧库新库变化似乎有点大,如果和我的不一样,特别是 iter_rows函数,那么很大可能是因为你的openpyxl库不是最新的。导入库后可运行代码 print(openpyxl.__version__) 查看其版本。也可以通过 win +R 输入cmd,确认,打开cmd黑窗后输入pip install --upgrade openpyxl 去更新库。
目录
1、新建工作簿workbook及保存
2、创建/新增工作表sheet
3、移除工作表
4、读取现有excel工作簿
5、获取所有工作表及名称
6、获取活跃工作表
7、更改工作表名称
8、复制工作表
9、获取单元格的值
10、写入/更改单元格的值
11、写入整行数据
12、获取一小块区域的内容
13、获取最小行、最大行、最小列、最大列
14、获取一整个sheet的内容
15、插入/删除行列
16、冻结单元格
17、移动单元格区域
18、合并单元格
19、写入公式
20、空值、缺失值判断
21、设置单元格的字体格式
22、设置单元格的对齐方式
23、设置行高列宽
24、关闭文件
结尾
openpyxl.Workbook() 新建工作簿。
.save(filename) 保存工作簿。filename为保存的路径。
运行后,在目录下参数一个名叫 “新建工作簿” 的excel文件,因为没有任何内容写入,所以里面是空的,打开文件,内容如下:
在这先说明一下工作簿和工作表的区别,因为我刚学也是不知道。工作簿是指这个excel文件,而工作表是指这个excel里的表。
.create_sheet 函数中,当 title不写时,创建工作表的名字为sheet1,再建即为sheet2,依此类推。当 index不写时,默认放在最后面;当 index为0,即放在最前面,1即放在第二位,依此类推。如下为index=0的情况:
当我们需要批量创建2023年1月每天的工作表时,可以使用for循环批量生成:
wb.remove(worksheet) 移除工作表,其中worksheet为工作表对象,而非工作表名称。
注意,在创建工作簿的时候就已经自动创建了一个名叫 “Sheet” 的工作表,首字母是大写的。移除工作表时,要先找到工作表对象 wb['Sheet'],再把它放进remove函数中进行移除。
此次创建的工作簿就没有上图的 “Sheet” 表了。
openpyxl.load_workbook(filename, read_only=False, keep_vba=False, data_only=False, guess_types=False, keep_links=True) 读取excel文件。
filename:文件路径。
read_only:只读模式,默认为False。建议大文件都设置为True,可以减少很多内存的耗用而加快速度;还有一种可能就是大文件把内存占用满了会直接报错,设置为True则不会。
keep_vba:默认为False,即不保留vba代码。
data_only:如果True,只有值,不包含公式(不保留公式,把公式最终的值算出来,把值写进去)
guess_types:读取单元格数据类型时,启用或禁用类型推断。
keep_links:如果设置为True,则保留外部链接。
我们可以手动创建一个文件然后用代码去读取,或者直接读取我们上面创建的文件。以下直接读取我们上面创建的 "批量创建1月每天的表.xlsx" 文件去演示 读取工作簿和工作表:
第一行代码为读取文件,其他参数一般默认就好。第二行是获取指定工作表,返回一个对象。第三行输出该工作表的名称。openpyxl总体的操作就是,先读取文件,后选择相应的sheet表,然后在表的基础上进行各种操作。
① wb.worksheets 获取所有工作表,返回一个工作表对象。可以用 .title去提取其名称。
all_sheet 输出了所有的工作表。但该输出为一个对象,想要选择表格需要先提取其名称,才能放入wb[]中,否则会报错。
② wb.sheetnames 获取所有工作表,返回一个工作表名称。
该函数直接返回了所有工作表的名称。
wb.active 获取活跃工作表。活跃工作表即在关闭工作簿的那一刻,当前处于哪个工作表,那活跃工作表就是这个。
如果重新打开改工作簿,把鼠标选择在最后一个工作表,随便点几个单元格,然后保存。那么活跃工作表就是最后一个工作表了。它表示的是文件在关闭前的最后更改的那个工作表。
sheet.title = ? 先获取到该工作表的名称,再=?,以重命名该工作表。
改后,该工作表的名称为:
除了直接改名称,也可以在原来的名称上增加字符,如:
重命名还有更多的操作,待小伙伴自己去研究。
wb.copy_worksheet(worksheet) 复制工作表。其中worksheet为工作表对象,而非工作表名称,工作表对象可用 工作簿[工作表名称] 即 wb['工作表名称'] 获取。
复制文件到最后(似乎只能复制到最后,没有index可以选择)。
获取单元格的有两种方法:① sheet['单元格'].value ② sheet.cell(row,column).value
① sheet['单元格'].value 获取该单元格的值。
② sheet.cell(row, column, value=None) 获取第row行,第column列的单元格。
row:第几行。
column:第几列。
value:默认为None。如果设置了值,则该单元格会被重新赋值为该值。
读取第4行第2列单元格的值为2300。可知,该函数的参数row和column都是从1开始计算的。需要记得,先行后列,不记得的话也可以以传参的形式输入,如:sheet.cell(row=4,column=2).value。
① 当sheet.cell(row, column, value=None)函数传入value参数时,该单元格的值会被改成value的值,从而更改单元格的值。如:
② 直接通过对 sheet.cell(row, column).value 进行赋值。
两种方法都可以更改单元格的值,本人用方法二比较多,具体要看个人习惯。如果需要获取多个单元格的值,可以用 for循环对 row和 column参数进行赋值获得。
如果需要批量写入/修改数据(一小块区域),openpyxl在这方面并不占优势,需要把每个单元格逐一遍历出来,再进行写入/修改,学了下面的遍历/获取一小块区域内容,你就会有灵感如何逐一遍历出来再写入/修改数据了。
再工作中不可能一个一个数据地写入,也为了防止出错,通常以一整行的方式写入。以一整行形式写入需要为一个列表,直接在sheet里append一个列表即可。
需要写入多行就用 for循环,一次一次地把每行写入。
获取小块区域单元格的值由两种方法:① 直接通过[]对一小块区域进行提取。② 通过函数sheet.iter_rows() 或 sheet.iter_cols() 获得。
① 通过[]获得
在讲解之前,我们先看看对于一小块区域,openpyxl是如何逐一提取单元格的值的。
可见,对于一小块区域 sg,openpyxl的[]提取法是先将行遍历出来,再将该行的单元格一个一个按顺序的提取的。如此,我们可以先遍历每行,再遍历该行的每个单元格去获得值:
② sheet.iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)
min_row:最小行。
max_row:最大行。
min_col:最小列。
max_col:最大列。
values_only:默认为False。如果设置为True,那么迭代产生的为值而非对象。
这次我们就不把一个输出为一行了,我们把(表格的)一行输出为(屏幕的)一行。
输出结果为一个对象,此时如果需要得到值,可直接用列表生成式 或者 一个个再次遍历出来,取 .title即可。而当我们设置 values_only为True时,输出结果为值。
③ sheet.iter_cols(min_col=None, max_col=None, min_row=None, max_row=None, values_only=False)
min_col:最小列。
max_col:最大列。
min_row:最小行。
max_row:最大行。
values_only:默认为False。如果设置为True,那么迭代产生的为值而非对象。
该函数的参数和上一个没有区别,意思也是一样,但是就是参数的位置不同。sheet.iter_rows各参数的顺序是最小行、最大行、最小列、最大列;而 sheet.iter_cols各参数的顺序是最小列、最大列、最小行、最大行。一句话,就是iter_rows先行后列,iter_cols先列后行。这个对于位置传参是非常重要的,而对于关键字传参没有影响,需要注意。
我们说过,sheet.iter_cols为先列后行,所以是提取2-6列 3-5行的内容。
如果你的和我的不一样,那么很有可能你的openpyxl库不是最新的。
sheet.min_row:获取最小行。
sheet.max_row:获取最大行。
sheet.min_column:获取最小列。
sheet.max_column:获取最大列。
获取到最小最大行列后可以更方便地对区域进行迭代。如第十一的获取一小块内容,在不知道最大行列的时候,需要取到最大行的情况,就需要用到了。下面举一个例子,获取第3行以后的行 第5列以后的列组成的区域:
sheet.values 获取sheet表中所有值,返回的是一个可迭代对象。如果需要得到值,可对该可迭代对象进行列表化处理 list(sheet.values)。
返回的数据依旧是以行存储的,即每一行作为一个整体元素存于列表中。这意味着可以切片去取对应的行,如取第2-4行:
sheet.insert_rows(idx, amount=1):插入几行。
sheet.insert_cols(idx, amount=1):插入几列。
sheet.delete_rows(idx, amount=1):删除几行。
sheet.delete_cols(idx, amount=1):删除几列。
① 插入行
在第3行处插入2行,插入后第3行开始(而不是第4行)为插入的数据。插入列为同样的操作,这里就不演示了。
② 删除行。
可见,第3、4行已经被删除(看最左边的序号,而不是看rank列)。删除列也是同样操作。
sheet.freeze_panes = '单元格'
冻结了E5后,当页面左右上下滑动时,A、B、C、D列以及1、2、3、4行都不会动,方便观看表头等。
至于如何解冻,小编也想到过这个问题,但是没有找到答案(找到有的说冻结填A1的,或者填None的,但是我试过不太行,因为打开文件会出现提示需要修复文件之类的),等到小编找到答案后,再修改文章,和大家分享!也希望知道如何解冻的小伙伴在下方留言,帮助到更多的人。
sheet.move_range(cell_range, rows=0, cols=0, translate=False)
cell_range:单元格区域。
rows:要向某方向移动多少行,正数向下,负数向上。
cols:要向某方向移动多少列,正数向右,负数向左。
translate:翻译公式,移动中包含公式引用的自动转换。
可全程只看左上角的单元格,如把D2移到了A7,就是向左移动了3列,向下移动了5行。移动超出范围会报错。
如果没有替换到单元格,那么很有可能是你的openpyxl库不是最新的。
sheet.merge_cells(range_string=None, start_row=None, start_column=None, end_row=None, end_column=None)
range_string:要合并的单元格的范围。
start_row:左上角单元格的行。
start_column:左上角单元格的列。
end_row:右下角单元格的行。
end_column:右下角单元格的列。
写入公式是直接在''或者""中写公式即可。至于代码中有两行都可以,如果不行,试试另一行,按道理结果是一样的(目前两行的结果都一样,没遇到过不行的)。公式单元格的读取也和普通单元格一样,具体查看第九点获取单元格的值。
写入公式也挺有趣的一个函数,如用于求和的 "=sum(A1:B1)" 等,更多功能自己去思考产生,一个功能的诞生并不是只为了解决一个问题的,而是经过灵活运用去解决很多问题。
说到这里,想到了前面第四点读取excel的一个点:data_only。如果读取excel时假如了该参数,那么原来excel里含有的公式都不是公式了,变成了一个值。如上面的L2单元格,原本意义是 "=D2&E2" ,但设置了data_only的读取模式后,它就只是 "钟睒睒先生"几个字,不再是一个公式了。
这里我们的sheet表的L2单元格的没有东西的。输出L2的值及格式如下:
这个NoneType格式是python内置的空值的格式,并非字符串。所以我们可以通过判断单元格的值是否为None来判断这个单元格是否是缺值的。
注意这个None,用于python内置缺失值的含义时是不需要加""的,如果加了,那就是判断该单元格是不是一个字符串了,如M2:
name:字体名称。
size:字体大小。
color:颜色。
bold:加粗。
italic:斜体。
underline:下划线。
用得最多的是这6个字体属性,其他如果生活中需要的话可以网上马上搜,很快的,比在我文章里找快。
给 font属性赋值 font对象即可。如下,对A1单元格的字体格式进行更改:
Alignment(horizontal=None, vertical=None, textRotation=0, wrapText=None, shrinkToFit=None, indent=0, relativeIndent=0, justifyLastLine=None, readingOrder=0, text_rotation=None, wrap_text=None, shrink_to_fit=None, mergeCell=None)
horizontal:横向(左右)对齐方式。可选参数 'left' , 'centerContinuous' , 'center' , 'distributed' , 'right' , 'fill' , 'general' , 'justify'。
vertical:竖向(上下)对齐方式。可选参数 'bottom' , 'center' , 'distributed' , 'top' , 'justify'。
同样,知道这两个就可以了,其他需要的再搜。可以这样记:h是 "横" 的首字母,横向;v像一个山沟,很深,竖向。
D1单元格已经被左对齐。
如果想要对所有行或者列进行设置行高列宽,可以使用for循环,加上前面所以学的最大最小行列的获取等知识,综合运用,做出让自己满意的表格。
关闭文件可以释放内存,加快下个文件的运行时间。如把一个sheet表按照班级拆分成多个excel文件时,每完成一个班级文件就关闭,不会拖延下一个文件的写入速度。
本篇开头说过openpyxl 的优缺点,也和pandas作了对比,各有春秋。pandas更偏向于大数据的批量处理以及数据分析、机器学习等,在pandas导出到excel之前,无法对excel 的格式进行设置,必须先导出为文件,再用openpyxl 去调格式。
不知各位小伙伴有没有试过需要把一整列数据区进行处理,如改格式,+某个数,条件+值等呢,此类操作对于openpyxl来说太复杂,如果用pandas进行处理,可能就几行代码。对于一个sheet拆分成多个sheet时,openpyxl也是麻烦透顶,先全部遍历,把它们全部录入字典,再一一取值创建sheet,太麻烦啦,一顿操作下来几十行代码,但是对于pandas来说,可能都不用10行代码就完成了。