王秀文; 郭明鑫; 王宇韬. 超简单:用Python让Excel飞起来(零基础学python,用python实现办公自动化,让excel化繁为简,减少重复工作)
第 1 章 Python 快 速 上 手
用 VBA 对 当 前 Excel 工 作 簿 中 的 内 容 进 行 操 作 会 比 较 方 便, 但 对 多 个 工 作 簿 或 不 同 格 式 文 件 的 控 制 就 要 比 Python 复 杂, 如 批 量 修 改 某 一 文 件 夹 下 的 Excel 工 作 簿 名, 用 Python 会 方 便 很 多。 因 此, 使 用 Python 能 让 自 动 化 办 公 的 实 现 范 围 更 广、 过 程 更 轻 松。
Anaconda 是 Python 的 一 个 发 行 版 本, 安 装 好 了 Anaconda 就 相 当 于 安 装 好 了 Python, 并 且 它 里 面 还 集 成 了 很 多 大 数 据 分 析 与 科 学 计 算 的 第 三 方 模 块, 如 NumPy、 Matplotlib 等。 PyCharm 则 是 一 款 Python 代 码 编 辑 器, 它 比 Anaconda 自 带 的 两 款 编 辑 器 Spyder 和 Jupyter Notebook 更 好 用。
Jupyter Notebook 可 在 线 编 辑 和 运 行 代 码, 是 一 款 适 合 初 学 者 和 教 育 工 作 者 的 优 秀 编 辑 器。 Spyder 则 提 供 一 些 非 常 漂 亮 的 可 视 化 选 项, 可 以 让 数 据 看 起 来 更 加 简 洁。
安装 xlwings 模块:
pip install xlwings
# 使用清华镜像
pip install xlwings -i https://pypi.tuna.tsinghua.edu.cn/simple
示例:
import xlwings as xw
import os
# 启动EXCEL程序窗口,但不新建工作簿
app = xw.App(visible = True, add_book = False)
# 如果路径不存在,则创建
if not os.path.exists("example\\01\\员工信息表"):
os.makedirs("example\\01\\员工信息表")
for i in range(1, 21):
workbook = app.books.add() # 新建工作簿, add 为books对象的函数
# 以 f开头表示在字符串内支持大括号内的python 表达式
workbook.save(f'example\\01\\员工信息表\\分公司{i}.xlsx')
workbook.close() #关闭创建的工作簿
app.quit() # 退出EXCEL程序
第 2 章 Python 的 基 础 语 法 知 识
变量类型查询函数: type(VarName)
数据类型转换函数:
- str()
- int(): 直接去掉小数点后的数字,不会四舍五入
- float()
列表与字符串的相互转换:
aList = ['a','b','c']
aStr = ','.join(aList)
print('aStr:',aStr)
anotherList = aStr.split(',')
print('anotherList:',anotherList)
'''
aStr: a,b,c
anotherList: ['a', 'b', 'c']
'''
第3章 Python模块
处理文件和文件夹的模块: os
- os.getcwd(): Gets the current working directory, 获取当前运行的脚本所在的工作目录
- os.listdir(path)
- os.path.splitext(path), 返回一个元组,包括文件主名路径和扩展名
- os.rename(oldName,newName): 重命名文件或文件夹, 需要给出path
- 对于文件,不但可以重新命名文件名, 还可以修改文件所在的文件夹位置
- 在 对 文 件 夹 进 行 重 命 名 时, 只 能 重 命 名 最 后 一 级 的 文 件 夹, 而 不 能 像 重 命 名 文 件 那 样 移 动 位 置。
import os
path = 'c:/test/temp.xlsx'
print(os.path.splitext(path)) # ('c:/test/temp', '.xlsx')
可以批量处理 EXCEL的python模块 xlwings
处理Excel的python模块功能对比:

xlwings 模 块 还 能 与 Excel VBA 结 合 使 用, 实 现 更 加 强 大 的 数 据 输 入 和 分 析 功 能。
import xlwings as xw
app = xw.App(visible = True, add_book = False)
# 打开工作簿
# 指 定 的 工 作 簿 必 须 真 实 存 在, 并 且 不 能 处 于 已 打 开 的 状 态。
workbook = app.books.open(r'example\\01\\员工信息表\\分公司1.xlsx')
# 操控工作表和单元格
worksheet = workbook.sheets['sheet1'] # 选中工作表 sheet1
worksheet.range('A1').value='A1单元格的值'
# 新增sheet
numSheets = workbook.sheets.count # sheet 数量
namesOfSheets = [] # sheet 名称列表
for i in range(numSheets):
namesOfSheets.append(workbook.sheets[i].name)
print(namesOfSheets)
if '产品统计表' not in namesOfSheets:
worksheet = workbook.sheets.add('产品统计表')
else:
workbook.sheets['产品统计表'].activate() # 激活指定的 worksheet
worksheet=workbook.sheets.active # 获得激活的工作簿
worksheet.range('A1').value='产品统计表A1单元格的值'
workbook.save()
workbook.close()
app.quit()
数据计算模块 NumPy
列表和NumPy数组的对比,以及NumPy数组的创建:
import numpy as np
aList = [1,2,3,4]
npArray = np.array([1,2,3,4])
print(aList) # [1, 2, 3, 4]
print(npArray) # [1 2 3 4]
print(type(aList)) # <class 'list'>
print(type(npArray)) # <class 'numpy.ndarray'>
# 同 样 是 做 乘 法 运 算, 列 表 是 把 元 素 复制 了 一 遍
# NumPy数 组 则 是 对 每 个 元 素 都 进 行 了 乘 法 运 算。
print(aList * 2) # [1, 2, 3, 4, 1, 2, 3, 4]
print(npArray * 2) # [2 4 6 8]
# numpy 数组的创建
print(np.arange(5)) # [0 1 2 3 4]
print(np.arange(5,10)) # [5 6 7 8 9]
print(np.arange(5,10,2)) # [5 7 9]
# 用 np.random.randn( 3) 创 建 一 个 一 维 数 组
# 其 中 包 含 服 从 正 态 分 布( 均 值 为 0、 标 准 差 为 1 的 分 布) # 的 3 个 随 机 数。
print(np.random.randn(3)) # [ 1.46183528 -0.25850555 0.92181574]
# 用reshape() 创建多维数组
print(np.arange(12).reshape(3,4))
'''
[[ 0 1 2 3]
[ 4 5 6 7]
[ 8 9 10 11]]
'''
# 随机整数数组: 包含5, 不包含20 之间的随机数,生成 4*5矩阵
print(np.random.randint(5,20,(4,5)))
数 据 导 入 和 整 理 模 块—— pandas
pandas 模 块 是 基 于 NumPy 模 块 的 一 个 开 源 Python 模 块, 广 泛 应 用 于 完 成 数 据 快 速 分 析、 数 据 清 洗 和 准 备 等 工 作, 它 的 名 字 来 源 于“ panel data”( 面 板 数 据)。 pandas 模 块 提 供 了 非 常 直 观 的 数 据 结 构 及 强 大 的 数 据 管 理 和 数 据 处 理 功 能, 某 种 程 度 上 可 以 把 pandas 模 块 看 成 Python 版 的 Excel。
与 NumPy 模 块 相 比, pandas 模 块 更 擅 长 处 理 二 维 数 据, 其 主 要 有 Series 和 DataFrame 两 种 数 据 结 构。 Series 类 似 于 通 过 NumPy 模 块 创 建 的 一 维 数 组, 不 同 的 是 Series 对 象 不 仅 包 含 数 值, 还 包 含 一 组 索 引
import pandas as pd
# 到, s 是 一 个 一 维 数 据 结 构
# 并 且 每 个 元 素 都 有 一 个 可 以 用 来 定 位 的 行 索 引,
s = pd.Series(['a','b','c'])
print(s)
'''
0 a
1 b
2 c
dtype: object
'''
print(s[1]) # b
Series 很 少 单 独 使 用, 我 们 学 习 pandas 模 块 主 要 是 为 了 使 用 它 提 供 的 DataFrame 数 据 结 构。 DataFrame 是 一 种 二 维 表 格 数 据 结 构, 可 以 将 其 看 成 一 个 Excel 表 格
1.二 维 数 据 表 格 DataFrame 的 创 建 与 索 引 的 修 改
import pandas as pd
import numpy as np
# 用列表创建 DataFrame
a = pd.DataFrame([[1,2],[3,4],[5,6]])
print(a)
'''
有行索引和列索引:
0 1
0 1 2
1 3 4
2 5 6
'''
# 自定义列索引 columns 和行索引 index
b = pd.DataFrame([[1,2],[3,4],[5,6]],columns=['height','width'],index=['A','B','C'])
print(b)
'''
height width
A 1 2
B 3 4
C 5 6
'''
# 按列创建 DataFrame
c = pd.DataFrame() # 空DataFrame
h = [1,3,4]
w = [2,4,6]
c['height'] = h
c['width'] = w
print(c)
'''
height width
0 1 2
1 3 4
2 4 6
'''
# 通过字典创建
d = {'a':[1,3,5],'b':[2,4,6]}
# 字典按列生成 DataFrame,以 字 典 的 键 名 作 为 列 索 引
e = pd.DataFrame.from_dict(d, orient='columns')
print(e)
'''
a b
0 1 2
1 3 4
2 5 6
'''
# 字典按行生成 DataFrame,以 字 典 的 键 名 作 为 行 索 引
f = pd.DataFrame.from_dict(d, orient='index')
print(f)
'''
0 1 2
a 1 3 5
b 2 4 6
'''
# 通过 NumPy 二维数组创建 DataFrame
n = np.arange(20).reshape(4,5)
df = pd.DataFrame(n)
print(df)
'''
0 1 2 3 4
0 0 1 2 3 4
1 5 6 7 8 9
2 10 11 12 13 14
3 15 16 17 18 19
'''
修改索引(略)
2.文件的读取和写入
import pandas as pd
# 读取 excel
# sheet_name 用 于 指 定 工 作 表, 可 以 是 工 作 表 名 称, 也 可 以 是 数 字( 默 认 为 0, 即 第 1 个 工 作 表)。
# index_col 用 于 设 置 索 引 列, int类型:0、1、2分别对应第一列、二列、三列,list类型:多行索引MultiIndex
# header参数为0,也就是第一行作为列索引(理解为表头)
data = pd.read_excel('data.xlsx',sheet_name='hello')
print(data)
'''
h1 h2 h3
0 1 3 5
1 2 4 6
'''
# 读取 csv
'''
·delimiter 用 于 指 定 CSV 文 件 的 数 据 分 隔 符, 默 认 为 逗 号。 ·encoding 用 于 指 定 文 件 的 编 码 方 式, 一 般 设 置 为 UTF-8 或 GBK 编 码, 以 避 免 中 文 乱 码。 ·index_col 用 于 设 置 索 引 列。
·index_col 用 于 设 置 索 引 列。
'''
data = pd.read_csv('data.csv',delimiter=',',encoding='utf-8')
print(data)
'''
h1 h2 h3
0 1 3 5
1 2 4 6
'''
# 文件写入
data = pd.DataFrame([[1,2],[3,4],[5,6]],columns=['A列','B列'])
data.to_excel('data2.xlsx')
'''
默认的EXCEL有行索引
A列 B列
0 1 2
1 3 4
2 5 6
'''
# 去掉行索引: 将index 设置为 False
data.to_excel('data3.xlsx',index=False)
3.数 据 的 选 取 和 处 理
import pandas as pd
# 可以用列表创建DataFrame, 列表中的子列表为行数据
# index 为行索引
# columns 为列索引
data = pd.DataFrame([[1,2,3,4],
[5,6,7,8],
[9,10,11,12]],
index=['r1','r2','r3'],
columns=['c1','c2','c3','c4'])
print(data)
'''
c1 c2 c3 c4
r1 1 2 3 4
r2 5 6 7 8
r3 9 10 11 12
'''
# 选取单列
print(data['c1'])
'''
# 返回Series 类型的数据
r1 1
r2 5
r3 9
Name: c1, dtype: int64
'''
print(data[['c1']])
'''
# 返回DataFrame 类型的数据
Name: c1, dtype: int64
c1
r1 1
r2 5
r3 9
'''
# 选取多列, 返回的是 DataFrame类型的数据, 所以参数必须是子列表
print(data[['c2','c4']])
'''
c2 c4
r1 2 4
r2 6 8
r3 10 12
'''
# 按照行的序号选取多行数据, 建议用 iloc
print(data[1:3]) # 选取2,3 两行
'''
c1 c2 c3 c4
r2 5 6 7 8
r3 9 10 11 12
'''
print(data.iloc[1:3])
'''
c1 c2 c3 c4
r2 5 6 7 8
r3 9 10 11 12
'''
# series
print(data.iloc[0])
'''
c1 1
c2 2
c3 3
c4 4
Name: r1, dtype: int64
'''
# 根据行名称获取数据
print(data.loc[['r1','r3']])
'''
c1 c2 c3 c4
r1 1 2 3 4
r3 9 10 11 12
'''
# 使用 heard() 函数获取前n行数据,默认为5行
print(data.head(2))
'''
c1 c2 c3 c4
r1 1 2 3 4
r2 5 6 7 8
'''
# 选取多行多列
# 方法1: 先列,后行
print(data[['c1','c3']][0:2])
'''
c1 c3
r1 1 3
r2 5 7
'''
# 方法2: 先行,后列, pandas 官方推荐的方法
# iloc 用数字做索引, loc 用字符做索引
print(data.iloc[0:2][['c1','c3']])
print(data.iloc[0:2,[0,2]])
print(data.loc[['r1','r3']][['c1','c3']])
print(data.loc[['r1','r3'],['c1','c3']])
'''
c1 c3
r1 1 3
r3 9 11
'''
# 通 过 在 中 括 号 里 设 定 筛 选 条 件 可 以 过 滤 行
# 如 果 有 多 个 筛 选 条 件,
# 可 以 用“&”( 表 示“ 且”) 或“ |”( 表 示“ 或”) 连 接 起 来。
print(data)
'''
c1 c2 c3 c4
r1 1 2 3 4
r2 5 6 7 8
r3 9 10 11 12
'''
print(data[(data['c1']>=5) & (data['c2']>6)])
'''
c1 c2 c3 c4
r3 9 10 11 12
'''
# sort_values(), 按某一列进行排序
# sort_index(), 按索引排序
a = data.sort_values(by='c2', ascending=False)
print(a)
'''
c1 c2 c3 c4
r3 9 10 11 12
r2 5 6 7 8
r1 1 2 3 4
'''
a = a.sort_index()
print(a)
'''
c1 c2 c3 c4
r1 1 2 3 4
r2 5 6 7 8
r3 9 10 11 12
'''
# 运算
data['c5'] = data['c4'] - data['c1']
print(data)
'''
c1 c2 c3 c4 c5
r1 1 2 3 4 3
r2 5 6 7 8 3
r3 9 10 11 12 3
'''
# 删除: drop, inplace=True表示在原变量上删除
a = data.drop(index='r1')
print(a)
'''
c1 c2 c3 c4 c5
r2 5 6 7 8 3
r3 9 10 11 12 3
'''
print(data)
'''
c1 c2 c3 c4 c5
r1 1 2 3 4 3
r2 5 6 7 8 3
r3 9 10 11 12 3
'''
a = data.drop(columns=['c5'],inplace=True)
print(a) # None
print(data)
'''
c1 c2 c3 c4
r1 1 2 3 4
r2 5 6 7 8
r3 9 10 11 12
'''
© Licensed under CC BY-NC-SA 4.0这辈子没法做太多的事情, 所以每一件都要做到精彩绝伦!——史蒂夫.乔布斯