openpyxl 速成课 N3

最新推荐文章于 2024-10-12 12:26:23 发布

weixin_46530456

最新推荐文章于 2024-10-12 12:26:23 发布

阅读量768

点赞数 13

分类专栏： openpyxl 模块文章标签： python

本文链接：https://blog.csdn.net/weixin_46530456/article/details/136547843

版权

openpyxl 模块专栏收录该内容

3 篇文章 0 订阅

订阅专栏

使用Python 处理 excel 速成课（openpyxl 实现）Note 3

本课时主要是一些 openpyxl 对 excel 的数字格式，Excel 函数公式

数字格式

(1) excel 里对python里数字和文本的显示

# 导入包，并获取一个工作表用以展示
import openpyxl

wb = openpyxl.Workbook()
ws = wb.active

# 导入演示数据
ws.append(['文本', '数字'])
ws['A2'] = '520' # python 里的字符串
ws['B2'] = 520   # python 里的数字

wb.save('demo3.xlsx')

N3Fig1

（2）python里的文本在excel里会按原样显示，但是数字在excel里有很多种类，如图：
N3Fig2

在openpyxl 里有一些内置的字符格式，当然我们也可以自定义一些字符格式如下面的例子：
（detail 在openpyxl 官方文档：https://openpyxl.readthedocs.io/en/latest/styles.html#using-number-formats ）

import datetime

ws['C1'] = 1314
ws['C1'].number_format = '#,###.00 元' 

ws['C2'] = datetime.datetime.today()
ws['C2'].number_format = 'yyyy-mm-dd'

ws['C3'] = 13
ws['C3'].number_format = '####'

ws['C4'] = 13
ws['C4'].number_format = '0000'

wb.save('demo3.xlsx')

结果：
N3Fig3

规律：

‘y’, ‘m’, ‘d’ 分别表示年，月，日，'yy’表示用两个数字代表的年份，'yyyy’则是四个数字
‘0’和’#‘均是数字占位符，两者的区别是’0’会自动用数字’0’补齐，而’#‘不会（比如实际数字是520，自定义格式为’0000’的单元格显示是0520，而自定义格式为’####'的单元格显示依然是520）
在自定义字符串中，添加字符串或文字都会直接显示在相应的位置中（比如实际数字是 88.8, 自定义格式是 '#,###.00元’的单元格显示是 88.80元)

(3) []表示颜色，以及 ;的使用
描述：自定义格式字符串可以通过分号 ; 为单元格可能出现的四种类型的数据定义不同的格式。这四种类型依次为 正值；负值；零值；文本

# RED, GREEN, BLUE, YEllOW 在之前import openpyxl里有.(这里好像不用额外导入）（not sure）

ws['D1'].number_format = '[RED]+#,###.00;[GREEN]-#,###.00' # 这里只有一个分号，对应前两种 即当单元格值为 正值（显示红）;负值（显示绿）
ws['D1'] = 2020
ws['D2'].number_format = '[RED]+#,###.00;[GREEN]-#,###.00'
ws['D2'] = -2020
ws['D3'].number_format = '[RED];[GREEN];[BLUE];[YELLOW]' # 颜色依次红，绿，蓝，黄
ws['D3'] = 2021
ws['D4'].number_format = '[RED];[GREEN];[BLUE];[YELLOW]'
ws['D4'] = -2021
ws['D5'].number_format = '[RED];[GREEN];[BLUE];[YELLOW]'
ws['D5'] = 0
ws['D6'].number_format = '[RED];[GREEN];[BLUE];[YELLOW]'
ws['D6'] = '扬州'

wb.save('demo3.xlsx')

结果：
N3Fig4

注意：可以看到D4的值是-2021，但是显示的时候我们只格式了颜色，即显示绿的2021

(4) [] 添加附加条件: [] 里如果第一个字符是 =的话即为条件的形式

ws['E1'].number_format = '[=1]男;[=0]女'
ws['E1'] = 0
ws['E2'].number_format = '[=1]男;[=0]女'
ws['E2'] = 1
ws['E3'].number_format = '[=1]男;[=0]女' # 显示 ### （乱码，啥也不是）
ws['E3'] = 2

# 与颜色合用
ws['E4'].number_format = '[<60][RED]不及格;[>=60][GREEN]及格'
ws['E4'] = 58
ws['E5'].number_format = '[<60][RED]不及格;[>=60][GREEN]及格'
ws['E5'] = 66

wb.save('demo3.xlsx')

结果：
N3Fig5

看到值为1，但显示 ‘男’
N3Fig6

Excel 的函数公式

函数公式始终以等于号(=)开头(切勿忘记=号)，后面可以跟数字、数字运算符(like + or -)和内置Excel函数，后者可以真正扩大公式的功能。

Excel 中 SUM 函数

SUM函数用于对单元格中的值求和。语法：=SUM(number1, [number2],…)
例子：

from openpyxl import load_workbook
wb = load_workbook('.//demo4.xlsx')
ws = wb['Sheet']

原始文档如图：
N3P2F1

我们想求学生的理综成绩

for row in ws.iter_rows(min_col=2, min_row=2, max_col=5,max_row=6):
    # 这里注意 iter_rows 选中要迭代的区域为B2：E6。即从第二行到第六行，从第二列（B列）到第五列（E列）
    # ws[row[3].coordinate] row 的编号从0开始，编号3即为选定范围B2：E6的第四列，即E那一列。
    # row[0] 为第1列B列；row[2] 为第三列D列
    ws[row[3].coordinate] = '=SUM(%s:%s)'%(row[0].coordinate,row[2].coordinate) # 切勿忘记=号, 以=开头
    
wb.save('demo4.xlsx')

结果：
N3P2F2

Excel 中 IF 函数

IF此函数用于在条件为真时返回一个值，条件为假时返回另一个值。语法：IF(logical_test,value_if_true,[value_if_false])。例如 =IF(C2=“YES”,1,2) 表示 IF(C2=“YES”,则返回1,否则返回2)。
例子：
这里我们想在F列加一列，如果理综总分>250，则显示’优’否则显示’良’

# 导入格式模块，调整一下格式以便观看
from openpyxl.styles import Alignment
center_alignment = Alignment(horizontal = 'center')

for row in ws.iter_rows(min_row=2,min_col=5,max_row=6,max_col=6):
    # 注意下面 '=IF(%s>250,"优","良")'。外面用单引号里面用双引号。因为Excel只能识别双引号，即保证 =IF(%s>250,"优","良") 是双引号，让 Excel 可以解析
    ws[row[1].coordinate]='=IF(%s>250,"优","良")' % (row[0].coordinate) # 切勿忘记=号, 以=开头
    ws[row[1].coordinate].alignment = center_alignment
    
wb.save('demo4.xlsx')

结果：
N3P2F3

如果单引号，Excel会报错：
N3P2F4

Excel 中 LOOKUP 函数

描述：Excel里一个查找引用的函数，用来查询一行或一列并查询一行或一列中相同位置的值。
LOOKUP函数用于查找匹配数据，语法：=LOOKUP(lookup_value,lookup_vector,[result_vector])
其中lookup_value为需要查找的值,lookup_vector查找的范围,result_vector结果的范围
例子：
现在我有如下表格，我需要在B9单元格填入生物成绩为85分(B8单元格)的，学生的姓名
N3P2F5

wb = load_workbook('.//demo5.xlsx')
ws = wb['Sheet']
ws['B9'] = '=LOOKUP(B8, D2:D5, A2:A5)' # A2:A5 为当D2:D5匹配时，返回值对应的位置
wb.save('demo5.xlsx')

结果：
N3P2F6

Excel 中 VLOOKUP 函数

VLOOKUP 函数是 LOOKUP 的大幅改进版本。用法为 VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])。
其中lookup_value为需要查找的值,table_array 为查找列的范围,col_index_num 结果所在列的序号，最后有个参数指明是否需要开启精确查找
其与 LOOKUP函数区别主要有两点:
（a) LOOKUP 函数每次只能查询一行或者一列，而 VLOOKUP 函数可以查询整个区域或者整个表格
例子：有如下表格，我需要查询姓名C的理综成绩填入B9单元格中
N3P2F7

wb = load_workbook('.//demo5.xlsx')
ws = wb['S2']
ws['B9'] = '=VLOOKUP(B8, A:F, 5, FALSE)' # A2:A5 为当D2:D5匹配时，返回值对应的位置
wb.save('demo5.xlsx')

N3P2F8

（b) LOOKUP 函数只能实现模糊匹配，而 VLOOKUP 最后一个参数可以指定是否需要实行精确匹配
这里有个知乎的帖子讲 VLOOPUP 函数很详细:https://zhuanlan.zhihu.com/p/390176592

检查一个函数是否有效

from openpyxl.utils import FORMULAE
'SUM' in FORMULAE

True

'SAM' in FORMULAE

False

附言：
本文为自学B站上鱼C的python课程（【办公篇】《极客Python之效率革命》P5-P6）随手做的笔记。一些概念和例子我个人为更好的理解做了些查询和补充
因本人水平有限，如有任何问题，欢迎大家批评指正！
原视频链接：https://www.bilibili.com/video/BV1wp411o71g?p=2&vd_source=6999af1d1ba811ac01f8eed2325bee07