Python之第十二章处理Excel电子表格-Toy模板网

这篇具有很好参考价值的文章主要介绍了Python之第十二章处理Excel电子表格。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

一、Excel文档

工作薄: .xlsx文件、包含多个表（工作表）。

活动表：用户当前查看的表、关闭Excel前最后查看的表。

二、安装openpyxl模块

import openpyxl

三、读取Excel表格

先制作一张例表1.xlsx

Python之第十二章处理Excel电子表格

1、用openpyxl模块打开Excel文档

openpyxl模块提供了openpyxl.load_workbook()函数，用来打开Excel文档。

Python之第十二章处理Excel电子表格

openpyxl.load_workbook()函数打开Excel文档会返回一个workbook数据类型的值。

workbook对象代表这个Excel文件，相当于File对象代表一个打开的文本文件。

要记住， example.xlsx 需要在当前工作目录，你才能处理它。可以导入 os ，使

用函数 os.getcwd() 弄清楚当前工作目录是什么，并使用 os.chdir() 改变当前工作目录。

2、用工作薄中取得工作表

调用 get_sheet_names() 方法可以取得工作簿中所有表名的列表。

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx') 打开Excel文档，得到workbook对象
>>> wb.get_sheet_names() workbook对象调用get_sheet_names()方法，得到工作薄中的所有工作表名
['Sheet1', 'Sheet2', 'Sheet3']   所有工作表名
>>> sheet = wb.get_sheet_by_name('Sheet3') get_sheet_by_name()方法指定工作表名，得到工作表对象，即worksheet对象
>>> sheet
<Worksheet "Sheet3">  Worksheet对象
>>> type(sheet)
<class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> sheet.title      worksheet对象调用title方法，得到工作表名
'Sheet3'
>>> anotherSheet = wb.get_active_sheet() worksheet对象调用get_active_sheet()方法得到活动表
>>> anotherSheet
<Worksheet "Sheet1">

openpyxl.load_workbook('example.xlsx')：打开Excel文件，得到工作薄对象，workbook对象。

wb.get_sheet_by_name('Sheet3')：工作薄对象调用get_sheet_by_name()方法，得到工作表对象，worksheet对象。

get_sheet_names()方法可以取得工作簿中所有表名的列表 wb.sheetnames

get_sheet_by_name()方法获取工作表对象（高版本Python该方法已弃用）

sheet=wb['Sheet5'] #得到工作表对象

的 get_active_sheet() 方法，取得工作簿的活动表。

3、从表中取得单元格

工作薄对象——工作表对象——单元格对象

import openpyxl

wb=openpyxl.load_workbook('1.xlsx')    #得到工作薄对象
sheet=wb['Sheet5']                     #得到工作表对象
print(type(sheet['A1']))               #得到单元格对象
print(sheet['A1'].value)
c=sheet['B1']
print(c)
print(c.value)

Python之第十二章处理Excel电子表格

Cell对象，单元格对象，有value、row、column和coordinate属性。

工作表对象调用cell()方法，传入整数作为row和column关键字参数，也可以得到一个单元格对象。

import openpyxl

wb=openpyxl.load_workbook('1.xlsx')    #得到工作薄对象
sheet=wb['Sheet5']                     #得到工作表对象
a=sheet.cell(row=1,column=2)
print(type(a))

Python之第十二章处理Excel电子表格

利用 cell()方法和它的关键字参数，就可以编写 for 循环，打印出一系列单元格的值。

练习：打印出B列所有所有单元格的值。

工作表worksheet对象的get_highest_row()方法和get_highest_column()方法可以得到表的大小，但是在最新的openpyxl模块中，已经废除，转而用max_row、max_column。

import openpyxl

wb=openpyxl.load_workbook('1.xlsx')    #得到工作薄对象
sheet=wb['Sheet5']   #得到工作表对象
r=sheet.max_row
c=sheet.max_column
print(r,c)
for i in range(1,7):
    a=sheet.cell(row=i,column=2)
    print(a.value)

Python之第十二章处理Excel电子表格

4、列字母和数字之间的转换

要从字母转换到数字，就调用 openpyxl.cell.column_index_from_string() 函数。

要从数字转换到字母，就调用 openpyxl.cell.get_column_letter() 函数。在交互式环境

中输入以下代码：

>>> import openpyxl
>>> from openpyxl.cell import get_column_letter, column_index_from_string
>>> get_column_letter(1)
'A'
>>> get_column_letter(2) 
'B'
>>> get_column_letter(27)
'AA'
>>> get_column_letter(900)
'AHP'
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb.get_sheet_by_name('Sheet1')
>>> get_column_letter(sheet.get_highest_column())
'C'
>>> column_index_from_string('A')
1 
>>> column_index_from_string('AA')
27

5、从表中取得行和列

可以单独取得工作表中的一行、一列，或者一个矩形区域中的所有Cell对象。然后可以循环遍历这个切片中的所有单元格。

import openpyxl

wb=openpyxl.load_workbook('1.xlsx')    #得到工作薄对象
sheet=wb['Sheet5']   #得到工作表对象
print(tuple(sheet['A1':'C3']))

Python之第十二章处理Excel电子表格

使用循环，输出选定区域的值：

Python之第十二章处理Excel电子表格

我们指明需要从 A1 到 C3 的矩形区域中的 Cell 对象，得到了一个 Generator对象，它包含该区域中的 Cell 对象。为了帮助我们看清楚这个 Generator 对象，可以使用它的 tuple()方法，在一个元组中列出它的 Cell 对象。

Generator对象是个元组，是个大元组，其中有很多个小元组，每一行都是一个元组。

因此要打印出这个区域中所有单元格的值，就要使用两个for循环，外层for循环遍历这个切片中的每一行。第二个循环，针对每一行，循环遍历中该行中的每一个单元格。

还可以访问指定的行或列的值，这个时候就可以利用Worksheet对象的rots和columns属性。

import openpyxl

wb=openpyxl.load_workbook('1.xlsx')    #得到工作薄对象
sheet=wb['Sheet5']   #得到工作表对象
a=sheet['A1':'C3']
b=[col for col in sheet.columns][1]
print(b)

Python之第十二章处理Excel电子表格

利用 Worksheet 对象的 rows 属性，可以得到一个元组构成的元组。内部的每个元组都代表 1 行，包含该行中的 Cell 对象。columns 属性也会给你一个元组构成的元组，内部的每个元组都包含 1 列中的 Cell 对象。对于 example.xlsx，因为有 7 行 3 列，rows给出由 7 个元组构成的一个元组（每个内部元组包含 3 个 Cell 对象）。columns 给出由3 个元组构成的一个元组（每个内部元组包含 7 个 Cell 对象）。要访问一个特定的元组，可以利用它在大的元组中的下标。例如，要得到代表 B列的元组，可以用 sheet.columns[1]。要得到代表 A 列的元组，可以用 sheet.columns[0]。在得到了代表行或列的元组后，可以循环遍历它的对象，打印出它们的值。

6、工作薄、工作表、单元格

作为快速复习，下面是从电子表格文件中读取单元格涉及的所有函数、方法和

数据类型。

1 ．导入 openpyxl 模块。

2 ．调用 openpyxl.load_workbook() 函数。

3 ．取得 Workbook 对象。

4 ．调用 get_active_sheet() 或 get_sheet_by_name() 工作簿方法。

5 ．取得 Worksheet 对象。

6 ．使用索引或工作表的 cell() 方法，带上 row 和 column 关键字参数。

7 ．取得 Cell 对象。

8 ．读取 Cell 对象的 value 属性。

四、项目：从电子表格中读取数据

五、写入Excel表格

OpenPyXL 也提供了一些方法写入数据，这意味着你的程序可以创建和编辑电子表格文件。利用 Python ，创建一个包含几千行数据的电子表格是非常简单的。

1、创建并保存Excel文档

我发现我目前学习的这本书Python编程快速上手13章处理Excel电子表格主要使用的是openpyxl模块，但是书中对这个模块的教程已经过时了，好多函数已经废弃、或者被替换了，因此我决定重新在网上找一个教程，来学习openpyxl模块。

openpyxl

一、简介

官方文档：https://openpyxl.readthedocs.io/en/stable/

注意：openpyxl模块只支持xlsx/xlsm/xltx/xltm格式，不支持xls格式。

二、新建

1、新建工作薄

Python之第十二章处理Excel电子表格

from openpyxl import Workbook：从openpyxl模块导入Workbook类

wb = Workbook() 从Workbook类中实例化对象wb

ws = wb.active wb对象调用active方法得到活动表

2、新建工作表

可以使用create_sheet()函数新建一张工作表。

#新建工作表，名称以Sheet1，Sheet2，....自动填充
ws_1 = wb.create_sheet()    # 默认在结尾处新建一个新的工作表
ws_2 = wb.create_sheet(0)   # 在当前工作表的指定索引处新建一个工作表

# 用title指定工作表名称
ws_1.title = "新建工作表" 

# 新建工作表，并指定名称
ws_3 = wb.create_sheet(title="新建工作表-2",index=0)
ws_4 = wb.create_sheet("新建工作表-1", 0) 

# 改变工作表标签颜色，默认为无颜色
ws.sheet_properties.tabColor = "F22F27"

Python之第十二章处理Excel电子表格

三、操作

1、设置工作薄只读

wb = load_workbook(filename='数据.xlsx', read_only=True) #设置只读

Python之第十二章处理Excel电子表格

2、工作表操作

# 导入openpyxl模块的Workbook类
import openpyxl
from openpyxl import load_workbook

wb=load_workbook(filename='1.xlsx',read_only=True)
print(wb.sheetnames) # 获取工作薄中所有工作表名称
for sheet in wb:
	print(sheet.title) # 遍历工作薄中所有工作表名
#获取指定工作表名称
ws=wb['Sheet1'] #创建工作表对象
print('***********'+ws.title) #输入工作表名称
ws_copy=wb.copy_worksheet(ws) #复制工作表

#删除工作表
# 方式一
ws = wb["Sheet1"]
wb.remove(ws)
# 方式二
del wb["Sheet1"]

3、行列操作

获知单元格范围

Python之第十二章处理Excel电子表格

插入空白行、空白列

Python之第十二章处理Excel电子表格

删除行、列

Python之第十二章处理Excel电子表格

4、访问单元格

注意：当一个工作表被创建时，其中不包含单元格，只有当单元格被获取时才被创建。

这种方式我们不会创建我们从不会使用的单元格，从而减少了内存消耗。

访问单个单元格

cell_A2=ws['A2']

cell_C3=ws[row=3,column=3]

访问多个单元格

通过切片访问：

cell_area = ws['A1':'B4']
cell_exact = ws.iter_rows(min_row=1, max_row=3, min_col=1, max_col=2) #即A1:B3

通过行、列访问：

col_A = ws['A']           #A列
col_area = ws['A:B']      #A、B列
row_2 = ws[2]             #第2行
row_area = ws[2:5]        #2-5行

# 迭代所有行
all_by_row = ws.rows

# 迭代所有列
all_by_col =ws.columns

结果可以用tuple()、list()、循环进行处理

e.g. 1.xlsx

import openpyxl
from openpyxl import load_workbook

wb=load_workbook('1.xlsx')
ws=wb['Sheet5']
#获取单个单元格的值
cell_A2=ws['A2']
cell_C3=ws.cell(row=3,column=3)
print(cell_A2.value,cell_C3.value)
print('---------------------------')
#获取多个单元格的值
cell_area = ws['A1':'D4']
for row in cell_area:
    for cell in row:
        print(cell.value)

Python之第十二章处理Excel电子表格

5、设置行高和列宽

设置整行行高

# 设置第2行行高
ws.row_dimensions[2].height = 40

设置整列列宽

# 设置C列列宽
ws.column_dimensions['C'].width = 30

最后一定要记得保存，wb.save('文件名')，不然不会应用的。

e.g. 1.xlsx

import openpyxl
from openpyxl import load_workbook

wb=load_workbook('1.xlsx')
ws=wb['Sheet5']

# 设置第2行行高
ws.row_dimensions[2].height = 40

# 设置C列列宽
ws.column_dimensions['C'].width = 30

wb.save('1.xlsx')

原来的：

Python之第十二章处理Excel电子表格

修改后：

6、合并单元格

合并

注意：要想在合并单元格写入数据，只需要在合并区域左上角的单元格写入数据即可。

如果合并区域内的单元格都有数据，也只会保留左上角的数据。

ws.merge_cells('A2:D4')

ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)

import openpyxl
from openpyxl import load_workbook

wb=load_workbook('1.xlsx')
ws=wb['Sheet5']

ws.merge_cells('A5:B6')
ws['A5']='这是一个合并后的单元格'
wb.save('1.xlsx')

Python之第十二章处理Excel电子表格

取消合并

ws.unmerge_cells('A2:D4')

ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)

import openpyxl
from openpyxl import load_workbook

wb=load_workbook('1.xlsx')
ws=wb['Sheet5']

ws.merge_cells('A5:B6')
ws['A5']='这是一个合并后的单元格'
ws.unmerge_cells('A5:B6')


wb.save('1.xlsx')

Python之第十二章处理Excel电子表格

四、写入

1、写入数据

# 在单元格写入数据
ws['A1'] = 42    #A1单元格写入
ws.cell(row=1, column=2, value=42)   #B1单元格写入
ws.cell(1,3).value= 42   #C1单元格写入

# 新增一行数据
ws.append([1, 2, 3, 4])

举个例子：

import openpyxl
from openpyxl import load_workbook

wb=load_workbook('1.xlsx')
ws=wb['Sheet5']

ws['A1']='姓名'
ws.cell(row=5, column=1, value='韩梅梅')   #A5单元格写入
ws.cell(5,2).value= 42   #B5单元格写入

ws.append(['离美', 2, 3, 4]) # 新增一行数据

wb.save('1.xlsx')

Python之第十二章处理Excel电子表格

2、写入公式

#写入公式
ws['B2'] = "=SUM(A2:A4)"
ws.cell(row=2, column=2, value = "=SUM(A2:A4)")
ws.cell(2,2).value = "=SUM(A2:A4)"

3、插入图片

img = Image('image') # image：要插入的图片
ws.add_image(img, 'B1') #在B1单元格插入图片

五、设置单元格样式

单元格样式有：number_format(数据格式)、Font(字体)、Fill(填充)、Border（边框）、Alignment(对齐方式)、Protection(保护)。

1、数字格式

Python之第十二章处理Excel电子表格

import openpyxl
from openpyxl import Workbook
import datetime

wb = Workbook()
ws = wb.active

ws['A1'] = '文字'
print(ws['A1'].number_format)    #-->>> General

ws['A2'] = 5
print(ws['A2'].number_format)   #-->>> General

ws['A3'] = 0.05
ws['A3'].number_format='0.00%' # 自定义格式
print(ws['A3'].number_format)  # -->>> 0.00%

ws['B1'] = datetime.datetime.now()
print(ws['B1'].number_format)   # -->>> yyyy-mm-dd h:mm:ss

ws['B2'] = datetime.datetime.now()
ws['B2'].number_format='yyyy-mm-dd' # 自定义格式
print(ws['B2'].number_format)  #-->>>  yyyy-mm-dd 

wb.save("2.xlsx")

Python之第十二章处理Excel电子表格

2、字体

导入

# 导入Font
from openpyxl.styles import Font

参数说明

name           #字体
size           #字号，默认11。
bold           #是否加粗，默认False。加粗：True
italic         #是否斜体，默认False。斜体：True
vertAlign      #上下标，默认None。正常：baseline，上标：superscript，下标：subscript
color          #字体颜色，默认黑色（FF000000）。
strikethrough  #删除线，默认不设置。设置：True
underline      #下划线，默认不带下划线。单下划线：single，双下划线：double，会计用单下划线：singleAccounting，会计用双下划线：doubleAccounting

实例

import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Font
import datetime

wb = Workbook()
ws = wb.active

ws['A1'] = '默认'      #A1单元格写入
ws['B2'] = '设置格式'  #B2单元格写入
ws['C3'] = '设置上标'  #C3单元格写入

#设置B2单元格字体格式
ws['B2'].font = Font(name='Calibri', size=12, color="00FF9900", italic=True, underline='double', strikethrough=True)

#设置C3单元格字体格式
ws['C3'].font = Font(vertAlign='superscript',  bold=True)

#保存

wb.save("2.xlsx")

Python之第十二章处理Excel电子表格

3、填充

纯色填充

导入

#导入
from openpyxl.styles import PatternFill

参数说明

fill_type    #设置图案样式，如果不设置则不会显示颜色。
'''
fill_type可设置的值：solid（实心），lightHorizontal, 
darkTrellis, darkUp, darkGray, darkVertical, lightDown,
lightTrellis, lightUp, darkDown, darkHorizontal, mediumGray, 
lightVertical, gray0625, gray125, lightGrid, darkGrid, lightGray
'''
fgColor/start_color     #前景色 ，即填充色
bgColor/end_color      #背景色，即图案颜色

Python之第十二章处理Excel电子表格

实例

from openpyxl import Workbook
from openpyxl.styles import PatternFill

wb = Workbook()
ws = wb.active

ws['A1'] = '默认'    #A1单元格写入
ws['B2'] = '前景色'  #B2单元格写入
ws['C3'] = '背景色'  #C3单元格写入

#前景色，即填充色。也是我们一般设置的填充色
ws['B2'].fill = PatternFill(fill_type='solid', fgColor='00FF9900') 

#背景色，即图案颜色。
ws['C3'].fill = PatternFill(fill_type='solid', bgColor='00FF9900')  

wb.save("实例.xlsx")

2、渐变填充（GradientFill）

导入

#导入
from openpyxl.styles import GradientFill

参数说明

type/fill_type  # 渐变填充类型：linear，path

'''
linear ：
渐变在一组指定的 Stops 之间插入颜色，跨越一个区域的长度。默认情况下渐变是从左到右的，但可以使用 degree 属性修改此方向。可以改为提供颜色列表，它们之间的距离将相等。

path： 
渐变从区域的每个边缘应用线性渐变。属性 top、right、bottom、left 指定从各个边界填充的范围。比如top=”0.2” 将填充单元格的前 20%。
'''

实例

from openpyxl.styles import GradientFill
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 合并单元格
ws.merge_cells('B2:F4')

#对合并单元格左上角单元格设置渐变填充
top_left_cell = ws['B2']
top_left_cell.fill = GradientFill(type='linear', degree=0, stop=('FFFFFF', '99ccff', '000000'))   #渐变填充

wb.save("实例.xlsx")

4、边框

设置边框样式时还需要通过Side。文章来源地址https://www.toymoban.com/news/detail-449462.html

导入

#导入
from openpyxl.styles import Border, Side

参数说明

# Border参数说明
left = Side(style , color)  #左边框设置
right = Side(style , color)  #右边框设置
top = Side(style , color)  #上边框设置
bottom = Side(style , color) #下边框设置

diagonalDown  #是否显示左上-右下对角线，显示：True
diagonalUp   #是否显示左下-右上对角线，显示：True
diagonal = Side(style , color)    #对角线边框设置，注意首先要设置显示对角线

#Side参数说明
style/border_style   #边框样式
'''
边框样式可设置的有：
thick, mediumDashDot, dashed, mediumDashDotDot, 
dashDot, slantDashDot, dotted, double, thin, 
hair, dashDotDot, mediumDashed, medium
'''
color  #边框颜色

实例

from openpyxl import Workbook
from openpyxl.styles import Border, Side

wb = Workbook()
ws = wb.active


ws['A1'] = '默认'   #A1单元格写入
ws['B2'] = '边框'   #B2单元格写入
ws['C3'] = '对角线'      #C3单元格写入

#边框线格式设置
line_format = Side(style='medium',color='00FF9900')

#B2单元格设置上下左右边框
ws['B2'].border = Border(left=line_format, right=line_format, top=line_format, bottom=line_format)

#C3单元格设置对角线
ws['C3'].border = Border(diagonalDown=True, diagonalUp=True, diagonal=line_format)

#保存
wb.save("实例.xlsx")

参考链接：Python自动化办公：openpyxl教程(基础)

到了这里，关于Python之第十二章处理Excel电子表格的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

Python之第十二章 处理Excel电子表格

一、Excel文档

二、安装openpyxl模块

三、读取Excel表格

1、用openpyxl模块打开Excel文档

2、用工作薄中取得工作表

3、从表中取得单元格

4、列字母和数字之间的转换

5、从表中取得行和列

6、工作薄、工作表、单元格

四、项目：从电子表格中读取数据

五、写入Excel表格

1、创建并保存Excel文档

openpyxl

一、简介

二、新建

1、新建工作薄

2、新建工作表

三、操作

1、设置工作薄只读

2、工作表操作

3、行列操作

获知单元格范围

插入空白行、空白列

删除行、列

4、访问单元格

访问单个单元格

访问多个单元格

5、设置行高和列宽

设置整行行高

设置整列列宽

6、合并单元格

合并

取消合并

四、写入

1、写入数据

2、写入公式

3、插入图片

五、设置单元格样式

1、数字格式

2、字体

导入

参数说明

实例

3、填充

纯色填充

导入

参数说明

实例

2、渐变填充（GradientFill）

导入

参数说明

实例

4、边框

导入

参数说明

实例

参考链接：Python自动化办公：openpyxl教程(基础)

相关文章

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

支付宝扫一扫领取红包，优惠每天领

二维码1

二维码2

Python之第十二章处理Excel电子表格