DAY 184 Excel模块Openpyxl教程-基础操作

1.openpyxl简介

openpyxl是用于读取/写入Excel 2010 xlsx/xlsm文件的Python库，也就是说openpyxl这个Python库不支持xls文件的读取和操作，如果在工作中遇到xls文件我们就不能使用这个库。官方说它的诞生是因为缺少可从Python本地读取/写入Office Open XML格式的库，为了方便大家就开发了这个库，这是非常棒的。

2.文件转换

上述提到openpyxl只能操作xlsx文件，当我们遇到xls文件的时候就需要进行转化，转换方式这里提供几种方案供大家参考：

方法一：手动打开xlsx文件，然后另存为xlsx类型的文件。

方法二：使用pywin32模块进行转换，示例代码如下：

import os
import win32com.client as win32
filename = r'C:UsersXHDesktop1.xls'
Excelapp = win32.gencache.EnsureDispatch('Excel.Application')
workbook = Excelapp.Workbooks.Open(filename)
# 转xlsx时: FileFormat=51,
# 转xls时:  FileFormat=56,
workbook.SaveAs(filename.replace('xls', 'xlsx'), FileFormat=51)
workbook.Close()
Excelapp.Application.Quit()
# 删除源文件
# os.remove(filename)

# 如果想将xlsx的文件转换为xls的话，则可以使用以下的代码：
# workbook.SaveAs(filename.replace('xlsx', 'xls'), FileFormat=56)

 

方法三：使用pandas模块进行转换，代码如下：

import pandas as pd
filename = r'C:UsersXHDesktop1.xls'
filename2 = r'C:UsersXHDesktop1.xlsx'
read_res = pd.read_excel(filename)
read_res.to_excel(filename2, index=False)

方法三在很多情况下出现一定的错误，比如在很多时候因为源表格的问题会造成数据丢失类的错误。个人推荐使用第二种方法。

3.基本操作-创建工作簿

安装openpyxl这个模块非常简单，cmd窗口中输入: pip install openpyxl。无需在文件系统上创建文件即可开始使用openpyxl。接下来我们来进行一个简单操作：

1、创建一个新的名为Mytest.xlsx文件。

2、在工作簿上第一个位置新建一个名为“mytest”的sheet页。

我们可以这样来实现，代码中每一个操作上都有相应的注释：

from openpyxl import Workbook
# 创建一个工作簿对象
wb = Workbook()
# 在索引为0的位置创建一个名为mytest的sheet页
ws = wb.create_sheet('mytest',0)
# 对sheet页设置一个颜色（16位的RGB颜色）
ws.sheet_properties.tabColor = 'ff72BA'
# 将创建的工作簿保存为Mytest.xlsx
wb.save('Mytest.xlsx')
# 最后关闭文件
wb.close()

最后生成的文件样式如下：

 

那么打开已有的文件Mytest.xlsx，读取一些信息怎么操作呢?我们可以这样：

from openpyxl import load_workbook
# 加载工作簿
wb2 = load_workbook('Mytest.xlsx')
# 获取sheet页
ws2 = wb2['mytest']
ws3 = wb2.get_sheet_by_name('mytest')
# 打印sheet页的颜色属性值
print('color:',ws2.sheet_properties.tabColor)
wb2.close()

上述代码的输出如下，可以看到一些属性值或参数：

color: <openpyxl.styles.colors.Color object>
Parameters:
rgb='00ff72BA', indexed=None, auto=None, theme=None, tint=0.0, type='rgb'

另外，有些小伙伴可能看到上述代码中，ws2和ws3都是获取sheet页签的。的确这两种方法的效果是一样的，大家在平时的工作中都可以使用。

如果想获取这个工作簿的所有sheet页，可以这样：

print(wb2.sheetnames)

注意sheetnames属性值是一个列表，输出的结果为一个列表：['mytest', 'Sheet']使用for循环也是可以的：

for each_sheet in wb2.sheetnames:
    print('each_sheet:',each_sheet)

4.基本操作-访问单元格

使openpyxl访问单元格很简单，分单个单元格访问和多个单元格的访问。我们接着来学习：访问单元格的方式一般也有两种做法：假设现在我们要访问单个单元格A1，我们可以这样：

cell_1 = ws2['A1']cell_2 = ws2.cell(row=1, column=1)

如果要取得这个单元格的内容，只需要在结尾加上value属性就可以了：

value_1 = ws2['A1'].value
value_2 = ws2.cell(row=1, column=1).value

如果需要给单元格进行设置值，则可以这样实现，比如给单元格A1设置内容：

ws2['A1'].value = 'python知识学堂'
ws2.cell(row=1, column=1).value ='python知识学堂'

 

对于多个单元格的设置就要借助for循环了。注意，设置后要保存工作簿，否则没有效果。

多个单元格的获取一般需要用到列表切片的知识或者使用for循环来进行:

# 访问A1至C3范围单元格
cell_range = ws2['A1':'C3']
# 访问A列所有存在数据的单元格
colA = ws2['A']
# 访问A列到C列所有存在数据的单元格
col_range = ws2['A:C']
# 访问第1行所有存在数据的单元格
row1 = ws2[1]
# 访问第1行至第5行所有存在数据的单元格
row_range = ws2[1:5]

 

注意，上述cell_range等对象都是<class 'tuple'>类型的。如果先获取这些单元格中的值，我们可以这样：

for each_cell in cell_range:
    for each in each_cell:
     print(each.value)

for each_cell in colA:
    print(each_cell.value)

至于为什么获取cell_range和colA的for循序的次数不一样，这个问题就留给大家自己了。

 

for循环的方式访问多个单元格可以这样：

for row in ws2.iter_rows(min_row=1, max_col=2, max_row=2):
    for cell in row:
        print(cell)
#输出：
<Cell 'mytest'.A1>
<Cell 'mytest'.B1>
<Cell 'mytest'.A2>
<Cell 'mytest'.B2>

for col in ws2.iter_cols(min_row=1, max_col=2, max_row=2):
         for cell in col:
             print(cell)
#输出：
<Cell 'mytest'.A1>
<Cell 'mytest'.A2>
<Cell 'mytest'.B1>
<Cell 'mytest'.B2>

 

这两个方法是按行优先和列优先的顺序进行访问单元格的。

大家可以新建一个工作簿，然后在工作簿中写入一些数据，之后运行以下代码看看打印一些什么：

print(tuple(ws2.rows))
print(tuple(ws2.columns))

注意：ws2.rows和ws2.columns是generator对象，因此需要使用tuple进行“解析”下。

5.最后的注意

使用openpyxl进行工作的时候，当一个工作结束的时候我们需要进行Excel文件的保存操作：wb.save('Mytest.xlsx')。这个保存唯一需要注意的是：文件是默认替换的。也就是说我们在保存文件的时候，openpyxl将进行替换而不发出告警。如果大家想保存不同阶段的文件，则可以在保存文件的时候加一个时间戳。

原文链接：https://zhuanlan.zhihu.com/p/342422919