Python操作Excel | 实例详解(openpyxl、xlrd、xlwt、xlutils模块)

1. 前言

Python对Excel的读写操作主要有xlrd、xlwt、xlutils、openpyxl、xlsxwriter几种库。其中常用的是分别是 xlrd、xlwt、xlutils、openpyxl。xlrd 操作的是xls/xlxs 的excel, 而 oppenpyxl 只支持 xlxs 格式的excel, 但是openpyxl 使用起来会更方便一些, 所以操作xlxs 文件的话, 那么可以优先选择openpyxl, 如果要兼容xls的话, 请使用xlrd/xlwt。

2. 库介绍

• xlrd：用于读取 Excel 文件；
• xlwt：用于写入 Excel 文件；
• xlutils：用于操作 Excel 文件的实用工具，比如复制、分割、筛选等；
• openpyxl:openpyxl:读写Excel 2010文档，处理更早格式的Excel文档，一个比较综合的工具，能够同时读取和修改Excel文档。其他很多的与Excel相关的项目基本只支持读或者写Excel一种功能。

3. 安装库

方式一: 打开cmd,输入下面安装命令:

pip3 install xlrd 
pip3 install xlwt 
pip3 install xlutils 
pip3 install openpyxl

# excel 里面有图片(jpg,png,bmg,...) 需要安装图片处理模块
pip install pillow

当有"Suceessfully"提示安装成功

方式二: 使用Pycharm图形界面安装
输入import xxx 后有红色波浪提示,是因为没有安装该模块,光标放上会提示"Install Package xlwt" ,点击安装即可

or
进入设置安装:


输入要安装的模块名称进行安装

安装成功如下提示:回到界面没有红色提示

4. 详解openpyxl模块

openpyxl三步走:

• 获取workbook,worksheet
• 获取单元格 进行操作
• 保存文件

4.1 读取 Excel

导入库

import openpyxl 

1、创建工作簿

from openpyxl import Workbook
wb = Workbook()

2、获取工作簿

Workbook.active属性来获取工作簿：

ws = wb.active
#`始终使用至少一个工作表创建一个工作簿。`

3、创建工作表

创建新的工作表：Workbook.create_sheet()方法

ws1 = wb.create_sheet("Mysheet") # 末尾插入 (默认)
# or
ws2 = wb.create_sheet("Mysheet", 0) # 在第一行插入
# or
ws3 = wb.create_sheet("Mysheet", -1) # 在倒数第二行插入position

图纸创建时会自动为其命名。它们按顺序编号（Sheet，Sheet1，Sheet2等）。可以随时通Worksheet.titl属性更改名称：

ws.title = "New Title"

标题的选项卡的背景颜色: Worksheet.sheet_properties.tabColor

ws.sheet_properties.tabColor = "1072BA"

查看工作簿中所有工作表的名称:Workbook.sheetname

print(wb.sheetnames)
['Sheet2', 'New Title', 'Sheet1']

4、遍历工作表

 for sheet in wb:
    print(sheet.title)

创建工作表的副本(即复制文件)：Workbook.copy_worksheet()：

source = wb.active
target = wb.copy_worksheet(source)

注意:仅复制单元格（包括值，样式，超链接和注释）和某些工作表属性（包括尺寸，格式和属性）。不复制所有其他工作簿/工作表属性-例如图像，图表。也不能在工作簿之间复制工作表。如果工作簿以只读或仅写 模式打开，则不能复制工作表。

5、工作表薄

修改工作薄名称

ws.title = New Title

获取工作薄名称

print("wb.sheetnames")

获取其中一个工作薄名称

for sheet in wb:
    print(sheet)
wb["New Title"]    #直接获取名称为New Title 的工作簿

6、读取Excel实例

实例:

from openpyxl import load_workbook

fileName = 'openpyxl_test.xlsx'

wb = load_workbook(fileName)

sheet = wb.active  # 获取当前被激活的工作薄

print(sheet.max_row)  # 最大行数
print(sheet.max_column)  # 最大列数

'''
- 获取sheet的最大行数, 并利用range()对sheet进行遍历
- 从2开始目的是为了排除A1,B1等标题行
- cell()可通过数字获取对应单元格
'''
for ri in range(2, sheet.max_row + 1):
    post = sheet.cell(row=ri, column=1)
    salary = sheet.cell(row=ri, column=2)
    age = sheet.cell(row=ri,column=3)
    print('%s--%s--%d' % (post.value, salary.value,age.value))

结果:

4.2 写入 excel

1、访问单元格

访问一个单元,进行数据读写操作:

ws["A4"] = 4     #这将返回A4处的单元格, 如果尚不存在, 则创建一个单元格, 值可以直接分配

2、行列法访问单元格

使用行和列表示法访问单元格：Worksheet.cell()。

d = ws.cell(row=4, column=2, value=10) #通过cell来进行写入, 通过行数和列数来找到单元格所在的位置, 通过value 来复制

3、添加数据

append()可以一次添加多行数据, 从第一行空白开始写入

#  添加一行
row = [1,2,3,4,5]
sheet.append(row)

print(sheet.max_row) #### 获得最大行
print(sheet.max_column) #### 获得最大列

4、访问多个单元

• 可以使用切片访问单元格范围：

cell_range = ws['A1':'C2']

• 行或列的范围可以类似地获得：

colC = ws['C']
col_range = ws['C:D']
row10 = ws[10]
row_range = ws[5:10]

• 使用Worksheet.iter_rows()：

for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
   for cell in row:
      print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.B1>
<Cell Sheet1.C1>
<Cell Sheet1.A2>
<Cell Sheet1.B2>
<Cell Sheet1.C2>

返回列：Worksheet.iter_cols()

for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
    for cell in col:
        print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.A2>
<Cell Sheet1.B1>
<Cell Sheet1.B2>
<Cell Sheet1.C1>
<Cell Sheet1.C2>

出于性能原因，该Worksheet.iter_cols()方法在只读模式下不可用。

5、遍历所有行或列

遍历文件的所有行或列:Worksheet.rows,Worksheet.columns

 ws = wb.active
 ws['C9'] = 'hello world'
 
 #Worksheet.rows实例
 tuple(ws.rows)
((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>),
(<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>),
(<Cell Sheet.A3>, <Cell Sheet.B3>, <Cell Sheet.C3>),
(<Cell Sheet.A4>, <Cell Sheet.B4>, <Cell Sheet.C4>),
(<Cell Sheet.A5>, <Cell Sheet.B5>, <Cell Sheet.C5>),
(<Cell Sheet.A6>, <Cell Sheet.B6>, <Cell Sheet.C6>),
(<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>),
(<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>),
(<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))

#Worksheet.columns实例
tuple(ws.columns)
((<Cell Sheet.A1>,
<Cell Sheet.A2>,
<Cell Sheet.A3>,
<Cell Sheet.A4>,
<Cell Sheet.A5>,
<Cell Sheet.A6>,
...
<Cell Sheet.B7>,
<Cell Sheet.B8>,
<Cell Sheet.B9>),
(<Cell Sheet.C1>,
<Cell Sheet.C2>,
<Cell Sheet.C3>,
<Cell Sheet.C4>,
<Cell Sheet.C5>,
<Cell Sheet.C6>,
<Cell Sheet.C7>,
<Cell Sheet.C8>,
<Cell Sheet.C9>))

出于性能原因，该Worksheet.columns属性在只读模式下不可用

6、合并和拆分单元格

所谓合并单元格，即以合并区域的左上角的那个单元格为基准，覆盖其他单元格使之称为一个大的单元格。
相反，拆分单元格后将这个大单元格的值返回到原来的左上角位置。

# 合并单元格， 往左上角写入数据即可
sheet.merge_cells('B1:G1') # 合并一行中的几个单元格
sheet.merge_cells('A1:C3') # 合并一个矩形区域中的单元格

合并后只可以往左上角写入数据，只保留左上角的数据，其他则丢失。如果若合并前不是在左上角写入数据，合并后单元格中没有数据。

拆分单元格:

sheet.unmerge_cells('A1:H3')

拆分后，值回到A1位置。

7、保存文件

Workbook.save()

wb.save("xxx.xlsx")

8、从文件加载

使用openpyxl.load_workbook()打开现有的工作簿：

from openpyxl import load_workbook
wb2 = load_workbook('test.xlsx')
print wb2.sheetnames
['Sheet2', 'New Title', 'Sheet1']

9、写入Excel实例

实例:

from openpyxl import Workbook
# 1.创建工作表
wb = Workbook()
# 创建工作薄
sheet = wb.create_sheet('Sheet1', 0)

# 2. 写入数据
# 待写入的数据
data = [
    ['UI设计师', '10000',23],
    ['测试工程师', '8000',27],
    ['软件开发工程师', '20000',25],
    ['产品经理', '12000',28],
    ]
# 定义标题行
sheet.cell(1, 1, 'post')
sheet.cell(1, 2, 'salary')
sheet.cell(1, 3, 'age')

# 将数据以行的形式添加到工作薄中
for row in data:
    sheet.append(row)

# 3. 保存工作表
wb.save('openpyxl_test.xlsx')

结果:

5. 详解xlrd模块

1、导入模块

# 导入模块
import xlrd

2、打开Excel文件读取数据

data = xlrd.open_workbook(filename) #文件名以及路径

注意:如果路径或者文件名有中文字符给前面加一个r拜师原生字符。

3、获取工作表

table = data.sheets()[0]          #通过索引顺序获取

table = data.sheet_by_index(sheet_indx))  #通过索引顺序获取

table = data.sheet_by_name(sheet_name)     #通过名称获取

返回book中所有工作表的名字

names = data.sheet_names()

检查某个sheet是否导入完毕

data.sheet_loaded(sheet_name or indx)  

4、行操作

• 获取该sheet中的有效行数:

nrows = table.nrows

• 返回由该行中所有的单元格对象组成的列表:

table.row(rowx)  

• 返回由该列中所有的单元格对象组成的列表:

table.row_slice(rowx)  

• 返回由该行中所有单元格的数据类型组成的列表:

table.row_types(rowx, start_colx=0, end_colx=None)    

• 返回由该行中所有单元格的数据组成的列表:

table.row_values(rowx, start_colx=0, end_colx=None) 

• 返回该列的有效单元格长度:

table.row_len(rowx) 

5、列操作

• 获取列表的有效列数:

ncols = table.ncols 

• 返回由该列中所有的单元格对象组成的列表:

table.col(colx, start_rowx=0, end_rowx=None)

• 返回由该列中所有的单元格对象组成的列表:

table.col_slice(colx, start_rowx=0, end_rowx=None) 

• 返回由该列中所有单元格的数据类型组成的列表:

table.col_types(colx, start_rowx=0, end_rowx=None)  

• 返回由该列中所有单元格的数据组成的列表:

table.col_values(colx, start_rowx=0, end_rowx=None)    

6、单元格的操作

• 返回单元格对象:

table.cell(rowx,colx)  

• 返回单元格中的数据类型:

table.cell_type(rowx,colx)

• 返回单元格中的数据:

table.cell_value(rowx,colx)

7、xlrd读取Excel文件实例

import xlrd

data = xlrd.open_workbook('demo.xls') # 打开xls文件
table = data.sheets()[0] # 打开第一张表
nrows = table.nrows # 获取表的行数
for i in range(nrows): # 循环逐行打印
    if i == 0:# 跳过第一行
        continue
    print (table.row_values(i)[:10]) # 取前10列

6. 详解xlwt模块

1、导入库

 import xlwt    # 导入xlwt

2、写入Excel

实例:

#导入 xlwt 库
import xlwt

# 1.创建 xls 文件对象
wb = xlwt.Workbook()

# 2.添加两个工作表
sh1 = wb.add_sheet('薪资')
sh2 = wb.add_sheet('人数')

# 3.按照位置来添加数据,第一个参数是行，第二个参数是列
# 写入第一个sheet
sh1.write(0, 0, '薪资')
sh1.write(0, 1, '人数')
sh1.write(0, 2, '职位')
sh1.write(1, 0, '15000')
sh1.write(1, 1, 30)
sh1.write(1, 2, '软件工程师')
sh1.write(2, 0, '10000')
sh1.write(2, 1, 4)
sh1.write(2, 2, 'UI设计师')
# 写入第二个sheet
sh2.write(0, 0, '总人数')
sh2.write(1, 0,50)

# 4.保存文件 over
wb.save('xlwt_test.xls')

运行成功后会当前文件的根目录下成功xlwt_test.xls文件

再打开文件如下:


创建文件对象 xlwt.Workbook()

file = xlwt.Workbook() #注意这里的Workbook首字母是大写

3、创建工作表

add_sheet（sheetname，cell_overwrite_ok = False ）在工作簿中创建工作表

　table = file.add_sheet('sheet name') #在工作簿中创建工作表

参数：

• sheetname –用于此工作表的名称，因为它将显示在Excel应用程序底部的选项卡中。
• cell_overwrite_ok: –如果为True，则如果多次写入，则添加的工作表中的单元格不会引发异常。
  cell_overwrite_ok=True: 可以防止对一个单元格重复操作时引发下面报错:

 returns error:
　　　 Exception: Attempt to overwrite cell:
　　　 sheetname=u'sheet 1' rowx=0 colx=0

4、写入数据

table.write(行,列,value)

table.write(0,0,'test')

5、保存文件

save（filename_or_stream)

  file.save('demo.xls')  # 保存文件

6、设置样式

     style = xlwt.XFStyle() # 初始化样式
       font = xlwt.Font() #为样式创建字体
       font.name = 'Times New Roman'
       font.bold = True
       style.font = font #为样式设置字体
       table.write(0, 0, 'some bold Times text', style) # 使用样式

xlwt: 允许单元格或者整行地设置格式。还可以添加链接以及公式。
dates.py: 展示如何设置不同的数据格式
hyperlinks.py: 展示如何创建超链接 (hint: you need to use a formula)
merged.py: 展示如何合并格子
row_styles.py: 展示如何应用Style到整行格子中.

7. 详解xlutils模块

1、xlutils介绍

xlutils模块提供了用于处理Excel文件的实用程序的集合。由于这些实用程序可能需要xlrd 和xlwt包中的一个或两个，因此在此处将它们收集在一起，与任何一个包分开。实用程序分为软件包中的几个模块，每个模块记录如下：

2、xlutils模块功能

• xlutils.copy: 用于将xlrd.Book对象复制到xlwt.Workbook对象的工具。
  详细学习地址:xlutils.copy
• xlutils.display:用于xlrd以用户友好和安全的方式显示有关对象的信息。
  详细学习地址:xlutils.display
• xlutils.filter: 用于将现有Excel文件拆分和过滤为新Excel文件的微型框架。
  详细学习地址:xlutils.filter
• xlutils.margins: 用于查找多少Excel文件包含有用数据的工具。
  详细学习地址:xlutils.margins
• xlutils.save: 用于将xlrd.Book对象序列化回Excel文件的工具。
  详细学习地址:xlutils.save
• xlutils.styles: 用于格式化信息的工具表示了Excel文件中的样式。
  详细学习地址:xlutils.styles
• xlutils.view: 易于使用的工作簿工作表中数据视图。
  详细学习地址:xlutils.view

学习参考:http://www.python-excel.org/