我心中的王者：Python-第19章 使用Python处理Excel文件
Excel是电子表格软件，主要是做数据的统计与分析。有时候我们可能会需要从数百或更多电子表格中依条件复制一些数据到其他表格，或是从数百或更多数据表中搜寻符合特定条件的数据等，这些皆是符合使用Python处理的条件。
本章内容需要使用外部模块openpyxl，读者可参考附录B下载此模块，下载时指令是：
pip install openpyxl
程序导入方法如下：
import openpyxl
19-1 认识Excel窗口
下列是Microsoft Excel窗口。
Microsoft Excel文件的扩展名是xlsx，下列是一些基本名词。
工作簿(workbook)：Excel的文件又称工作簿。
工作表(worksheet)：一个工作簿由不同数量的工作表组成，若以上图为例，是由2020Q1、2020Q2、2020Q3等3个工作表所组成，其中2020Q1底色是白色，表示这是当前工作表(active sheet)。
栏(column)：工作表的栏名称是A、B、……
行(row)：工作表的行名称是1、2、……国人有时将row翻译为列。
单元格(cell)：工作表内的每一个格子称单元格，用(栏名, 行名)代表。
19-2 读取Excel文件
在本书ch19文件夹有sales.xlsx，本节主要以此文件为实例解说。
19-2-1 打开文件
当我们导入openpyxl模块后，可以使用openpyxl.load_workbook( )方法打开Excel文件，然后可以回传Excel文件对象，本章将用wb变量代表workbook文件对象，当然读者也可以使用其他名称。
程序实例ch19_1.py：打开sales.xlsx文件，然后列出回传Excel文件对象的文件类型。
# ch19_1.py
import openpyxl
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn) # wb是Excel档案对象
print(type(wb))
执行结果
<class 'openpyxl.workbook.workbook.Workbook'>
19-2-2 取得工作表worksheet名称
可以使用get_sheet_names( )取得所打开工作簿文件的所有工作表，工作表将以列表数据类型回传。get_active_sheet( )可以取得当前工作表的名称，注意，这里所指的当前工作表是打开文件后自动显示的工作表名称。本章将用ws变量代表worksheet文件对象，当然读者也可以使用其他名称。
程序实例ch19_2.py：列出sales.xlsx文件所有的工作表和当前工作表的名称。
# ch19_2.py
import openpyxl
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn) # wb是Excel档案对象
allSheets = wb.sheetnames # 所有工作表对象
print("所有工作表 = ", allSheets)
ws = wb.active # ws是目前工作表对象
print("目前工作表 = ", ws)
执行结果
所有工作表 = ['2020Q1', '2020Q2', '2020Q3']
目前工作表 = <Worksheet "2020Q1">
对于当前工作表对象而言，此例是ws，可以使用title属性列出实际内容。
程序实例ch19_3.py：重新设计ch19_2.py，将title属性应用在ws对象。
# ch19_3.py
import openpyxl
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn)
ws = wb.active
print("目前工作表 = ", ws.title)
执行结果
目前工作表 = 2020Q1
19-2-3 设定当前工作的工作表
使用Python操作Excel文件时，可能需随时更改当前工作表，可以使用get_sheet_by_name( )，然后将要设为当前工作表的名称当做这个方法的参数。
程序实例ch19_4.py：更改当前工作表为2020Q3。
# ch19_4.py
import openpyxl
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn)
ws = wb['2020Q3'] # 设定目前工作表
print("目前工作表 = ", ws.title)
执行结果
目前工作表 = 2020Q3
其实我们可以将上述activeSheet当作是当前工作表对象。
19-2-4 取得工作表内容
在上一小节的程序中我们有了当前工作表对象ws，我们可以用下列方式取得单元格内容。
ws[‘栏行'].value # 栏是A, B, ……、行是1, 2, ……
程序实例ch19_5.py：列出一系列不同位置的单元格内容。
# ch19_5.py
import openpyxl
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn)
ws = wb['2020Q1'] # 目前工作表2020Q1
print("单元格A1 = ", ws['A1'].value) # A1
print("单元格A2 = ", ws['A2'].value) # A2
print("单元格B2 = ", ws['B2'].value) # B2
print("单元格B3 = ", ws['B3'].value) # B3
print("单元格C5 = ", ws['C5'].value) # C5
执行结果
单元格A1 = 销售业绩表
单元格A2 = 姓名
单元格B2 = 一月
单元格B3 = 4560
单元格C5 = 6799
上述对于ws[‘栏行’]而言，除了可以使用value属性取得单元格内容外，也可以使用row、column或coordinate取得单元格相对位置信息。
程序实例ch19_6.py：列出单元格位置信息。
# ch19_6.py
import openpyxl
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn)
ws = wb['2020Q1'] # 目前工作表2020Q1
print("单元格A1 = ", ws['A1'].column, ws['A1'].row, ws['A1'].coordinate)
print("单元格A2 = ", ws['A2'].column, ws['A2'].row, ws['A2'].coordinate)
print("单元格B2 = ", ws['B2'].column, ws['B2'].row, ws['B2'].coordinate)
print("单元格B3 = ", ws['B3'].column, ws['B3'].row, ws['B3'].coordinate)
print("单元格C5 = ", ws['C5'].column, ws['C5'].row, ws['C5'].coordinate)
执行结果
单元格A1 = 1 1 A1
单元格A2 = 1 2 A2
单元格B2 = 2 2 B2
单元格B3 = 2 3 B3
单元格C5 = 3 5 C5
上述每一行输出3个数据，分别是栏(column)、行(row)和坐标(coordinate)。
19-2-5 取得工作表内容的栏数和行数
对于当前工作表对象(本节实例使用ws当变量)而言，max_column和max_row可以分别传回工作表内容的栏数和行数。
程序实例ch19_7.py：传回sales.xlsx工作簿2020Q1工作表的栏数和行数。
# ch19_7.py
import openpyxl
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn)
ws = wb['2020Q1'] # 目前工作表2020Q1
print("工作表栏数 = ", ws.max_column)
print("工作表行数 = ", ws.max_row)
执行结果
工作表栏数 = 5
工作表行数 = 12
读者可以将上述执行结果与19-1节的sales.xlsx工作表作比较，就可以知道我们得到了正确的结果了。
19-2-6 取得单元格内容
上述我们使用“ws[‘栏列’].value”取得单元格内容，我们也可以使用cell( )方法取得单元格内容，此时其语法格式如下：
ws.cell(column=N,row=M) # N是栏编号，M是行编号
程序实例ch19_8.py：列出第5行的内容。
# ch19_8.py
import openpyxl
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn)
ws = wb['2020Q1'] # 目前工作表2020Q1
for i in range(1, ws.max_column+1): # column做索引增值
print(ws.cell(column=i, row=5).value, end=' ') # row=5, 索引不变
print() # 跳行打印
print(ws['A5'].value) # 打印A5
执行结果
李连杰 8864 6799 7842 =SUM(B5:D5)
李连杰
从上图可以看到ws.cell(column=1, row=5).value的意义与ws.[‘A5’].value意义相同。上述E5单元格内容是公式，如果想要显示值，可以在第5行打开工作簿文件时，增加data_only=True参数。
程序实例ch19_9.py：重新设计ch19_8.py，以数值显示公式，下列只列出修改部分。
# ch19_9.py
import openpyxl
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn, data_only=True)
ws = wb['2020Q1'] # 目前工作表2020Q1
for i in range(1, ws.max_column+1): # column做索引增值
print(ws.cell(column=i, row=5).value, end=' ') # row=5, 索引不变
执行结果
李连杰 8864 6799 7842 23505
如果想要取得某一区块单元格空间可以使用双层循环的观念。
程序实例ch19_10.py：列出某区间的单元格数据，这个程序将列出A4:E6内容。
# ch19_10.py
import openpyxl
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn, data_only=True)
ws = wb['2020Q1'] # 目前工作表2020Q1
for j in range(4,7): # row做索引增值
for i in range(1,6): # column做索引增值
print("%5s" % ws.cell(column=i, row=j).value, end=' ')
print() # 换行输出
执行结果
魏德圣 3972 4014 3890 11876
李连杰 8864 6799 7842 23505
成祖名 5797 4312 5500 15609
19-2-7 工作表对象ws的rows和columns
当建立工作表对象ws成功后，会自动产生下列数据产生器(generators)：
ws.rows：工作表数据产生器以行方式包裹，每一行用一个Tuple包裹。
ws.columns：工作表数据产生器以栏方式包裹，每一栏用一个Tuple包裹。
程序实例ch19_11.py：列出ws.rows和ws.columns的数据类型。
# ch19_11.py
import openpyxl
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn, data_only=True)
ws = wb['2020Q1'] # 目前工作表2020Q1
print(type(ws.rows)) # 获得ws.rows数据类型
print(type(ws.columns)) # 获得ws.columns数据类型
执行结果
<class 'generator'>
<class 'generator'>
由于ws.rows和ws.columns是数据产生器，若是想取得它的内容须先将它们转成列表(list)，然后就可以用索引方式取得。
程序实例ch19_12.py：列出特定行与栏的信息。需留意由于数据转成了列表，所以索引值是从0开始。本程序会列出A栏数据和李安这行资料。
# ch19_12.py
import openpyxl
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn, data_only=True)
ws = wb['2020Q1'] # 目前工作表2020Q1
for cell in list(ws.columns)[0]: # A栏
print(cell.value)
for cell in list(ws.rows)[2]: # 索引是2
print(cell.value, end=' ')
执行结果
销售业绩表
姓名
李安
魏德圣
李连杰
成祖名
张曼玉
田中千绘
范逸臣
周华健
蔡琴
张学友
李安 4560 5152 6014 15726
对于数据产生器而言，我们可以使用逐行方式获得全部的工作表内容。
程序实例ch19_13.py：使用逐行方式获得工作表全部的内容。
# ch19_13.py
import openpyxl
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn, data_only=True)
ws = wb['2020Q1'] # 目前工作表2020Q1
for row in ws.rows:
for cell in row:
print(cell.value, end=' ')
print()
执行结果
销售业绩表 None None None None
姓名 一月 二月 三月 总计
李安 4560 5152 6014 15726
魏德圣 3972 4014 3890 11876
李连杰 8864 6799 7842 23505
成祖名 5797 4312 5500 15609
张曼玉 4234 8045 7098 19377
田中千绘 7799 5435 6680 19914
范逸臣 8152 7152 7034 22338
周华健 9040 8048 5098 22186
蔡琴 5566 4890 6690 17146
张学友 7152 6622 7452 21226
读者可能会想是否可以使用逐栏方式获得全部的工作表内容，答案是可以的。
程序实例ch19_14.py：使用逐栏方式获得全部的工作表内容。
# ch19_14.py
import openpyxl
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn, data_only=True)
ws = wb['2020Q1'] # 目前工作表2020Q1
for col in ws.columns:
for cell in col:
print(cell.value, end=' ')
print()
执行结果
销售业绩表 姓名 李安 魏德圣 李连杰 成祖名 张曼玉 田中千绘 范逸臣 周华健 蔡琴 张学友
None 一月 4560 3972 8864 5797 4234 7799 8152 9040 5566 7152
None 二月 5152 4014 6799 4312 8045 5435 7152 8048 4890 6622
None 三月 6014 3890 7842 5500 7098 6680 7034 5098 6690 7452
None 总计 15726 11876 23505 15609 19377 19914 22338 22186 17146 21226
19-2-8 用整数取代域名
在Excel中栏名称是A、B、…、Z、AA、AB、AC、……例如，1代表A、2代表B、26代表Z、27代表AA、28代表AB。如果工作表的栏数很多，很明显我们无法清楚了解到底索引是多少，例如，BC是多少。为了解决这方面的问题，下面将介绍2个转换方法：
get_column_letter(数值) # 将数值转成字母
column_index_from_string(字母) # 将字母转成数值
上述方法存在于openpyxl.utils模块内，所以程序前面要加上下列指令。
from openpyxl.utils import get_column_letter, column_index_from_string
程序实例ch19_15.py：将字段的字母转成数值与将数值转成字母。
# ch19_15.py
import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn, data_only=True)
ws = wb['2020Q1'] # 目前工作表2020Q1
print("栏数= ",get_column_letter(ws.max_column))
print("3 = ",get_column_letter(3))
print("27 = ",get_column_letter(27))
print("100 = ",get_column_letter(100))
print("800 = ",get_column_letter(800))
print("A = ", column_index_from_string('A'))
print("E = ", column_index_from_string('E'))
print("AA = ", column_index_from_string('AA'))
print("AZ = ", column_index_from_string('AZ'))
print("AAA = ", column_index_from_string('AAA'))
执行结果
栏数= E
3 = C
27 = AA
100 = CV
800 = ADT
A = 1
E = 5
AA = 27
AZ = 52
AAA = 703
19-2-9 切片
这是使用切片的观念读取某区间数据，例如，读取A3:E6数据可用下列方法：
程序实例ch19_16.py：采用切片观念读取单元格内容。
# ch19_16.py
import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn, data_only=True)
ws = wb['2020Q1'] # 目前工作表2020Q1
for row in ws['A3':'E6']:
for cell in row:
print(cell.value, end=' ')
print()
执行结果
李安 4560 5152 6014 15726
魏德圣 3972 4014 3890 11876
李连杰 8864 6799 7842 23505
成祖名 5797 4312 5500 15609
19-3 写入Excel文件
openpyxl模块也有提供方法可让我们写入Excel文件。
19-3-1 建立Excel文件
openpyxl.Workbook( )可以建立空白的工作簿，也可想成Excel文件。预设所建立的文件是可擦写，如果想要设为只写模式，可以加上write_only=True参数。
19-3-2 存储Excel文件
save( )方法可以存储Excel文件，这个方法需由Excel文件对象启动，先前我们是使用wb(workbook)当作文件对象的变量，所以使用语法如下：
wb.save(文件名) # 可以存储指定文件名的文件
程序实例ch19_17.py：建立一个空白的Excel文件，列出预设的工作表名称，然后将预设工作表名称改为“My sheet”，最后用out19_17.xlsx名称储存此文件。
# ch19_17.py
import openpyxl
wb = openpyxl.Workbook() # 建立空白的活页簿
ws = wb.active # 获得目前工作表
print("目前工作表名称 = ", ws.title) # 打印目前工作表
ws.title = 'My sheet' # 更改目前工作表名称
print("新工作表名称 = ", ws.title) # 打印新的目前工作表
wb.save('out19_17.xlsx') # 将活页簿储存
执行结果 下列是执行结果与out19_17.xlsx的结果。
目前工作表名称 = Sheet
新工作表名称 = My sheet
19-3-3 复制Excel文件
我们可以用打开文件，然后新名称存储文件方式达到复制Excel文件的效果。
程序实例ch19_18.py：将sales.xlsx复制一份至out19_18.xlsx。
# ch19_18.py
import openpyxl
fn = 'sales.xlsx'
wb = openpyxl.load_workbook(fn) # 开启sales.xlsx活页簿
wb.save('out19_18.xlsx') # 将活页簿储存至out19_18.xlsx
执行结果 可以在当前工作文件夹看到所建的out19_18.xlsx文件，文件内容与sales.xlsx相同。
19-3-4 建立工作表
create_sheet( )可以在工作簿内建立新的工作表。
程序实例ch19_19.py：建立空白工作簿，然后打印所有工作表。接着新增工作表，再度打印所有工作表，最后将这个工作簿储存至out19_19.xlsx。
# ch19_19.py
import openpyxl
wb = openpyxl.Workbook() # 建立空白的活页簿
print("所有工作表名称 =", wb.sheetnames) # 打印所有工作表
wb.create_sheet() # 建立新工作表
print("所有工作表名称 =", wb.sheetnames) # 打印所有工作表
ws = wb.active # 取得目前工作表
print("目前工作表名称 = ", ws.title) # 打印目前工作表
wb.save('out19_19.xlsx') # 将活页簿储存
执行结果 同时在文件夹可以看到拥有2个工作表的out19_19.xlsx文件。
所有工作表名称 = ['Sheet']
所有工作表名称 = ['Sheet', 'Sheet1']
目前工作表名称 = Sheet
在建立工作表时预设工作表名称是“SheetN”，N是数字编号以递增方式显示，另外新建立的工作表是放在工作表列的最右边，我们可以在create_sheet( )内增加参数title和index设定新工作表的名称和位置。工作表的位置是从0开始，所以如果index=0，表示在最左边。
程序实例ch19_20.py：扩充ch19_19.py，增加使用title和index关键词。
# ch19_20.py
import openpyxl
wb = openpyxl.Workbook() # 建立空白的活页簿
print("所有工作表名称 =", wb.sheetnames) # 打印所有工作表
wb.create_sheet() # 建立新工作表
print("所有工作表名称 =", wb.sheetnames) # 打印所有工作表
wb.create_sheet(index=0, title='First sheet') # 第一个工作表
print("所有工作表名称 =", wb.sheetnames) # 打印所有工作表
wb.create_sheet(index=2, title='Third sheet') # 第三个工作表
print("所有工作表名称 =", wb.sheetnames) # 打印所有工作表
wb.save('out19_20.xlsx') # 将活页簿储存
执行结果
所有工作表名称 = ['Sheet']
所有工作表名称 = ['Sheet', 'Sheet1']
所有工作表名称 = ['First sheet', 'Sheet', 'Sheet1']
所有工作表名称 = ['First sheet', 'Sheet', 'Third sheet', 'Sheet1']
19-3-5 删除工作表
删除工作表可以使用remove_sheet( )方法，在使用时并不是直接将工作表名称当参数，必须使用工作簿对象wb调用get_sheet_by_name( )当作参数。
程序实例ch19_21.py：重新设计ch19_20.py，主要是增加删除Sheet工作表。
# ch19_21.py
import openpyxl
wb = openpyxl.Workbook() # 建立空白的活页簿
print("所有工作表名称 =", wb.sheetnames) # 打印所有工作表
wb.create_sheet() # 建立新工作表
print("所有工作表名称 =", wb.sheetnames) # 打印所有工作表
wb.create_sheet(index=0, title='First sheet') # 第一个工作表
print("所有工作表名称 =", wb.sheetnames) # 打印所有工作表
wb.create_sheet(index=2, title='Third sheet') # 第三个工作表
print("所有工作表名称 =", wb.sheetnames) # 打印所有工作表
ws_to_remove = wb['Sheet']
wb.remove(ws_to_remove) # 删除Sheet工作表
print("所有工作表名称 =", wb.sheetnames) # 打印所有工作表
wb.save('out19_21.xlsx') # 将活页簿储存
执行结果 由下图可以看到Sheet工作表已经被删除了。
所有工作表名称 = ['Sheet']
所有工作表名称 = ['Sheet', 'Sheet1']
所有工作表名称 = ['First sheet', 'Sheet', 'Sheet1']
所有工作表名称 = ['First sheet', 'Sheet', 'Third sheet', 'Sheet1']
所有工作表名称 = ['First sheet', 'Third sheet', 'Sheet1']
19-3-6 写入单元格
我们已经学会了从特定单元格读取数据，如果想要写入资料，只要设定该单元格的值就可以了。
程序实例ch19_22.py：将资料写入单元格。
# ch19_22.py
import openpyxl
wb = openpyxl.Workbook() # 建立空白的活页簿
ws = wb.active # 获得目前工作表
ws['A1'] = 'Python'
ws['A2'] = 100
wb.save('out19_22.xlsx') # 将活页簿储存
执行结果 打开out19_22.xlsx可以看到下列结果。
输入数据的格式与在Excel窗口是相同的，字符串靠左对齐，数值数据靠右对齐。
19-3-7 将列表数据写进单元格
我们可以使用append( )方法将列表资料写入单元格，append这个名词有附加的意义，如果当前工作表没有资料，append( )可将数据从第一行(row)开始写入，如果当前工作表已经有数据，可将数据从已有数据的下一行开始写入。
程序实例ch19_23.py：在空白工作表使用append( )输入列表数据。
# ch19_23.py
import openpyxl
wb = openpyxl.Workbook() # 建立空白的活页簿
ws = wb.active # 获得目前工作表
row1 = ['数学', '物理', '化学'] # 定义列表数据
ws.append(row1) # 写入列表
row2 = [98, 82, 89] # 定义列表数据
ws.append(row2) # 写入列表
wb.save('out19_23.xlsx') # 将活页簿储存
执行结果 打开out19_23.xlsx可以看到下列结果。
上述我们成功地一次输入一个列表数据，如果列表数据的元素也是列表，我们可以使用循环方式输入内含列表元素的列表。
程序实例ch19_24.py：在已有数据的工作表，使用append( )输入内含列表元素的列表。
# ch19_24.py
import openpyxl
wb = openpyxl.Workbook() # 建立空白的活页簿
ws = wb.active # 获得目前工作表
ws['A1'] = '明志科技大学'
rows = [ # 定义列表数据
['数学', '物理', '化学'],
[98, 82, 89],
[79, 88, 90],
[80, 78, 91]]
for row in rows:
ws.append(row) # 写入列表
wb.save('out19_24.xlsx') # 将活页簿储存
执行结果 打开out19_24.xlsx可以看到下列结果。
19-4 设定单元格的字体
若是想要设定单元格的字体，建议可以导入Font( )方法，如下所示：
from openpyxl.style import Font
未来就可以直接使用Font( )，代替每次需写入openpyxl.styles.Font( )长串的方法名称。
19-4-1 Font( )
Font对象主要功能是执行字体相关的设定，可以使用Font( )方法设定此对象，有了Font对象后就可以将它应用在单元格。这个方法常见的参数如下：
程序实例ch19_25.py：设计3种字体应用，分别应用在A1、A2和A3单元格。
fontTitle1：字体名称是‘微软正黑体’、字号是24。
fontTitle2：字体名称是‘Old English Text MT’、字号是24、粗体。
fontTitle3：字号是24、粗体、斜体。
# ch19_25.py
import openpyxl
from openpyxl.styles import Font
wb = openpyxl.Workbook() # 建立空白的活页簿
ws = wb.active # 获得目前工作表
fontTitle1 = Font(name='微软正黑体', size=24)
ws['A1'].font = fontTitle1
ws['A1'] = '明志科技大学'
fontTitle2 = Font(name='Old English Text MT', size=24, bold=True)
ws['A2'].font = fontTitle2
ws['A2'] = 'Ming-Chi Institute of Technology'
fontTitle3 = Font(size=24, bold=True, italic=True)
ws['A3'].font = fontTitle3
ws['A3'] = 'Ming-Chi Institute of Technology'
wb.save('out19_25.xlsx') # 将活页簿储存
执行结果 打开out19_25.xlsx可以看到下列结果。
19-4-2 字体色彩的设定
其实所有颜色可以使用3个原色red(红色)、green(绿色)和blue(蓝色)，每个颜色数值在0—255间组成。Font( )方法内的参数color的值“FFFFFF”，分别代表Red、Green和Blue，下列是常见的256种颜色组合。
本书附录D有完整的色彩说明。
程序实例ch19_26.py：在Excel文件内建立各种字体颜色。
# ch19_26.py
import openpyxl
from openpyxl.styles import Font
wb = openpyxl.Workbook() # 建立空白的活页簿
ws = wb.active # 获得目前工作表
fontTitle1 = Font(name='Old English Text MT', size=24, color='0000FF')
ws['A1'].font = fontTitle1
ws['A1'] = 'Ming-Chi Institute of Technology'
fontTitle2 = Font(name='Old English Text MT', size=24, color='FF66FF')
ws['A2'].font = fontTitle2
ws['A2'] = 'Ming-Chi Institute of Technology'
wb.save('out19_26.xlsx') # 将活页簿储存
执行结果 打开out19_26.xlsx可以看到下列结果。
19-5 数学公式的使用
常用的数学公式如下：
程序实例ch19_27.py：计算B1:B3单元格区间的加总、平均、最高分、最低分。
# ch19_27.py
import openpyxl
wb = openpyxl.Workbook() # 建立空白的活页簿
ws = wb.active # 获得目前工作表
ws['A1'] = 'Peter' # 设定名字Peter
ws['B1'] = 98
ws['A2'] = 'Janet' # 设定名字Janet
ws['B2'] = 79
ws['A3'] = 'Nelson' # 设定名字Nelson
ws['B3'] = 81
ws['A4'] = '总分'
ws['B4'] = '=SUM(B1:B3)' # 计算总分
ws['A5'] = '平均'
ws['B5'] = '=AVERAGE(B1:B3)' # 计算平均
ws['A6'] = '最高分'
ws['B6'] = '=MAX(B1:B3)' # 计算最高分
ws['A7'] = '最低分'
ws['B7'] = '=MIN(B1:B3)' # 计算最低分
wb.save('out19_27.xlsx') # 将活页簿储存
执行结果 打开out19_27.xlsx可以看到下列结果。
19-6 设定单元格的高度和宽度
单元格预设的高度是12.75pt，72pt等于1英寸。可以使用column_dimensions属性设定行高。单元格默认的宽度是8.43个英文字符宽度，可以使用row_dimensions设定单元格的宽度。如果将高度或宽度设为0，则具有隐藏单元格效果。
程序实例ch19_28.py：设定第一行(row)高度和第B栏(column)宽度。
# ch19_28.py
import openpyxl
from openpyxl.styles import Alignment
wb = openpyxl.Workbook() # 建立空白的活页簿
ws = wb.active # 获得目前工作表
ws['A1'] = '深石数位'
ws['B2'] = 'Deep Stone'
ws.row_dimensions[1].height = 40 # 高度是40pt
ws.column_dimensions['B'].width = 20 # 宽度是20个英文字符宽
wb.save('out19_28.xlsx') # 将活页簿储存
执行结果 打开out19_28.xlsx可以看到下列结果。
19-7 单元格对齐方式
可以使用Alignment( )方法，所以须在程序前方导入下列模块。
from openpyxl.styles import Alignment
Alignment( )方法内可以有下列2个参数：
horizontal：可以设定left(靠左)、center(居中)、right(靠右)对齐。vertical：可以设定top(靠上)、center(居中)、bottom(靠下)对齐。整个单元格设定的完整公式如下：
ws[‘A1'].alignment = Alignment( ) # 详细应用可参考下列实例
程序实例ch19_29.py：为了让读者更加清楚整个城市的意义，特别增加第一行高度和第2栏宽度。‘台科大’字符串是靠左靠上对齐、‘明志科大’字符串是左右上下居中对齐、‘北科大’字符串是靠右靠下对齐。
# ch19_29.py
import openpyxl
from openpyxl.styles import Alignment
wb = openpyxl.Workbook() # 建立空白的活页簿
ws = wb.active # 获得目前工作表
ws['A1'] = '台科大'
ws['B1'] = '明志科大'
ws['C1'] = '北科大'
ws.row_dimensions[1].height = 40 # 高度是40pt
ws.column_dimensions['B'].width = 20 # 宽度是20个英文字符宽
ws['A1'].alignment = Alignment(horizontal='left', vertical='top')
ws['B1'].alignment = Alignment(horizontal='center', vertical='center')
ws['C1'].alignment = Alignment(horizontal='right', vertical='bottom')
wb.save('out19_29.xlsx') # 将活页簿储存
执行结果 打开out19_29.xlsx可以看到下列结果。
19-8 合并与取消合并单元格
19-8-1 合并单元格
可以使用merge_cells( )合并单元格，可以合并同一行(row)、同一栏(column)或一个区间的单元格，细节可以参考下列实例。
程序实例ch19_30.py：A1:E1单元格是合并的单元格，同时水平居中对齐，A2单元格未合并，读者可以比较2个字符串的执行结果。A3:A5是垂直合并的单元格，字符串“明志科大”是垂直居中对齐。C3:E4是合并一个单元格区间，字符串“机械工程系”是水平和垂直居中。
# ch19_30.py
import openpyxl
from openpyxl.styles import Alignment
wb = openpyxl.Workbook() # 建立空白的活页簿
ws = wb.active # 获得目前工作表
ws['A1'] = 'Ming-Chi Institute of Technology'
ws['A2'] = 'Ming-Chi Institute of Technology'
ws['A3'] = '明志科大'
ws['C3'] = '机械工程系'
ws.merge_cells('A1:E1') # 合并A1:E1单元格
ws.merge_cells('A3:A5') # 合并A1:E1单元格
ws.merge_cells('C3:E4') # 合并C3:E4单元格
ws['A1'].alignment = Alignment(horizontal='center') # A1单元格水平置中
ws['A3'].alignment = Alignment(vertical='center') # A3单元格垂直置中
ws['C3'].alignment = Alignment(horizontal='center', vertical='center')
wb.save('out19_30.xlsx') # 将活页簿储存
执行结果 执行结果打开out19_30.xlsx可以看到下列结果。
19-8-2 取消合并单元格
可以使用unmerge_cells( )取消合并单元格。
程序实例ch19_31.py：打开ch19_30.py所建立的Excel文件，然后取消合并同时将执行结果存入out19_31.py。
# ch19_31.py
import openpyxl
wb = openpyxl.load_workbook('out19_30.xlsx') # 开启活页簿
ws = wb.active # 获得目前工作表
ws.unmerge_cells('A1:E1') # 取消合并A1:E1单元格
ws.unmerge_cells('A3:A5') # 取消合并A1:E1单元格
ws.unmerge_cells('C3:E4') # 取消合并C3:E4单元格
wb.save('out19_31.xlsx') # 将活页簿储存
执行结果 打开out19_31.xlsx可以看到下列结果。
上述程序第5行“ws=wb.active”，笔者使用另一个获得当前工作表的用法，观念与ch19_30.py的第6行用法意义是一样的。
19-9 建立图表
Python可以建立的图表有许多，所有Excel可以建立的图表皆可使用Python建立，为了建立图表可以更方便地导入图表模块，程序需导入下列图表方法。BarChart(柱形图)、BarChart3D(3D柱形图)、PieChart(饼图)、PieChart3D(3D饼图)、BubbleChart(泡泡图)、AreaChart(分区图)、AreaChart3D(3D分区图)、LineChart(线段图)、LineChart3D(3D线段图)、RadarChart(雷达图)、StockChart(股票图)。上述英文名称就是建立图表的方法，导入方法如下：
from openpyxl.chart import BarChart, Reference # 以导入BarChart为例
另外需导入Reference方法，这个方法主要是供我们将建立图表所需的工作表数据或是卷标名称(有时也可称轴的卷标)数据导入所建的图表对象内。
本节将以4个最常用的图表，用程序实例做说明。
19-9-1 柱形图
这是最常见的图表应用，主要是显示多组数据于一段时间的变化，从此类型也可以了解各组资料间比较的情形，应用时通常数值数据是在纵轴(y轴)，而标记是在横轴(x轴)。
程序实例ch19_32.py：建立深石软件2020—2021年销售报表。
# ch19_32.py
import openpyxl
from openpyxl.chart import BarChart, Reference
wb = openpyxl.Workbook() # 开启活页簿
ws = wb.active # 获得目前工作表
rows = [
['', '2020年', '2021年'],
['亚洲', 100, 300],
['欧洲', 400, 600],
['美洲', 500, 700],
['非洲', 200, 100]]
for row in rows:
ws.append(row)
chart = BarChart() # 直方图
chart.title = '深石软件销售表' # 图表标题
chart.y_axis.title = '业绩金额' # y轴标题
chart.x_axis.title = '地区' # x轴标题
data = Reference(ws, min_col= 2, max_col=3, min_row=1, max_row=5) # 图表数据
chart.add_data(data, titles_from_data=True) # 建立图表
xtitle = Reference(ws, min_col=1, min_row = 2, max_row=5) # x轴标记名称
chart.set_categories(xtitle) # 设定x轴标记名称(亚洲欧洲美洲非洲)
ws.add_chart(chart, 'E1') # 放置图表位置
wb.save('out19_32.xlsx')
执行结果 打开out19_32.xlsx可以看到下列结果。
上述16行是建立图表类型，未来可以由此决定所要建立图表的类型。17—19行分别是建立图表标题、y轴和x轴的标题。21行是Reference( )，主要是建立图表数据对象，在此记录建立图表的数据范围，有ws、min_col、max_col、min_row、max_row参数，ws是代表图表数据源工作表，剩余的数据是列出数据是由哪些单元格区间组成，以此例可知是由B1:C5所组成。22行是将21行的图表数据对象放入16行所建的图表对象内。
23行是建立x轴标记名称的数据对象，有ws、min_col、max_col、min_row、max_row参数，ws是代表图表数据源工作表，剩余的数据是列出数据是由哪些单元格区间组成，此例没有max_col参数，表示min_col与max_col值相同，以此例可知标记名称数据是由A2:A5所组成。24行set_categories( )是将标记名称的数据对象填入整个图表对象Chart。25行是设定图表在工作表中的位置，一个程序可以建立许多图表，只要使用25行观念将图表放在不同位置即可。
19-9-2 3D柱形图
学会了建立柱形图后，若想要建立3D柱形图表就很简单，只要导入BarChart3D，可参考程序第3行，在16行将图表对象改成BarChart( )，剩余的程序代码就完全相同了。为了方便读者阅读，笔者在程序代码内也标记了不一样的地方。
程序实例ch19_33.py：以3D柱形图(BarChart3D)重新设计ch19_32.py。
# ch19_33.py
import openpyxl
from openpyxl.chart import BarChart3D, Reference
wb = openpyxl.Workbook() # 开启活页簿
ws = wb.active # 获得目前工作表
rows = [
['', '2020年', '2021年'],
['亚洲', 100, 300],
['欧洲', 400, 600],
['美洲', 500, 700],
['非洲', 200, 100]]
for row in rows:
ws.append(row)
chart = BarChart3D() # 3D直方图
chart.title = '深石软件销售表' # 图表标题
chart.y_axis.title = '业绩金额' # y轴标题
chart.x_axis.title = '地区' # x轴标题
data = Reference(ws, min_col= 2, max_col=3, min_row=1, max_row=5) # 图表数据
chart.add_data(data, titles_from_data=True) # 建立图表
xtitle = Reference(ws, min_col=1, min_row = 2, max_row=5) # x轴标记名称
chart.set_categories(xtitle) # 设定x轴标记名称(亚洲欧洲美洲非洲)
ws.add_chart(chart, 'E1') # 放置图表位置
wb.save('out19_33.xlsx')
执行结果 打开out19_33.xlsx可以看到下列结果。
19-9-3 饼图
饼图(PieChart)只适合一个数据系列，主要是供了解单笔数据相对于整体数据的关系比。设计程序需先导入PieChart，可参考第3行。
程序实例ch19_34.py：将深石员工旅游意向调查表处理成饼图。
# ch19_34.py
import openpyxl
from openpyxl.chart import PieChart, Reference
wb = openpyxl.Workbook() # 开启活页簿
ws = wb.active # 获得目前工作表
rows = [
['地区', '人次'],
['上海', 300],
['东京', 600],
['香港', 700],
['新加坡', 400]]
for row in rows:
ws.append(row)
chart = PieChart() # 直方图
chart.title = '深石员工旅游意向调查表'
data = Reference(ws, min_col= 2, min_row=1, max_row=5) # 图表数据
chart.add_data(data, titles_from_data=True) # 建立图表
labels = Reference(ws, min_col=1, min_row = 2, max_row=5) # 标签名称
chart.set_categories(labels) # 设定标签名称
ws.add_chart(chart, 'E1') # 放置图表位置
wb.save('out19_34.xlsx')
执行结果 打开out19_34.xlsx可以看到下列结果。
19-9-4 3D饼图
学会了建立饼图后，若想要建立3D饼图表就很简单，只要导入PieChart3D，可参考程序第3行，在16行将图表对象改成PieChart3D( )，剩余的程序代码就完全相同了。为了方便读者阅读，笔者在程序代码内也标记不一样的地方。
程序实例ch19_35.py：以3D饼图(PieChart3D)重新设计ch19_34.py。
# ch19_35.py
import openpyxl
from openpyxl.chart import PieChart3D, Reference
wb = openpyxl.Workbook() # 开启活页簿
ws = wb.active # 获得目前工作表
rows = [
['地区', '人次'],
['上海', 300],
['东京', 600],
['香港', 700],
['新加坡', 400]]
for row in rows:
ws.append(row)
chart = PieChart3D() # 直方图
chart.title = '深石员工旅游意向调查表'
data = Reference(ws, min_col= 2, min_row=1, max_row=5) # 图表数据
chart.add_data(data, titles_from_data=True) # 建立图表
labels = Reference(ws, min_col=1, min_row = 2, max_row=5) # 标签名称
chart.set_categories(labels) # 设定标签名称
ws.add_chart(chart, 'E1') # 放置图表位置
wb.save('out19_35.xlsx')
执行结果 打开out19_35.xlsx可以看到下列结果。
1727
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
