用Python实现表格读写

案例一：获取个人工资信息

我们需要从【10月员工绩效表】中找到江宇的信息，然后写入【江宇工资信息表】中10月工资信息对应的行。

 

实现代码：

# 从openpyxl库导入load_workbook函数
from openpyxl import load_workbook

# 打开【10月员工绩效表】的工作簿，获取活动工作表
performance_wb  = load_workbook('./material/10月员工绩效表.xlsx')
performance_ws = performance_wb.active

# 打开【江宇工资信息表】的工作簿，获取活动工作表
info_wb = load_workbook('./material/江宇工资信息表.xlsx')
info_ws = info_wb.active

# 获取【绩效】值
performance = performance_ws['D14'].value
# 获取【奖金】值
bonus = performance_ws['E14'].value
# 获取【基本工资】值
base = performance_ws['F14'].value

# 写入【绩效】值
info_ws['E11'].value = performance
# 写入【奖金】值
info_ws['F11'].value = bonus
# 写入【基本工资】值
info_ws['G11'].value = base

# 保存对【江宇工资信息表】工作簿的写入
info_wb.save('./material/江宇工资信息表.xlsx')

本案例中使用数据的过程应该是这样的：

1）取出薪资数据：打开【10月员工绩效表】工作表，根据坐标取出薪资数据。

2）原样使用数据：将取出的数据存储好，无需处理。

3）数据写入其他工作表：写入到【江宇工资信息表】中对应的位置并保存。

案例二：生成前十行绩效信息表

案例二是需要从【10月员工绩效表】中提取前十行信息，写入一个新的工作簿。这样，保存后可以用作员工绩效信息的模板表格。

 

 

实现代码：
 

# 从openpyxl库导入load_workbook和Workbook
from openpyxl import load_workbook, Workbook

# 打开【10月员工绩效表.xlsx】工作簿
performance_wb = load_workbook('./material/10月员工绩效表.xlsx')
# 获取活动工作表
performance_ws = performance_wb.active

# 新建工作簿
new_wb = Workbook()
# 获取活动工作表
new_ws = new_wb.active

# 获取performance_ws的前十行数据
for row in performance_ws.iter_rows(max_row=10, values_only=True):
    # 将数据写入新的工作表
    new_ws.append(row)

# 保存新工作簿为【员工绩效表-模板.xlsx】
new_wb.save('./material/员工绩效表-模板.xlsx')

 

 

代码的设计思路：

1）确定数据范围并取出数据：导入模块库、打开【10月员工绩效表】工作簿工作表、取出前十行数据。

2）如何使用所取数据：原样使用。

3）数据使用后的输出结果：写入到新建的【员工绩效表-模板】工作簿中，并保存。

 

下面我们来“总结和对比”下两个案例。

案例一和案例二，最大的不同点是所取的数据不一样。案例一获取三个单个单元格的数据，案例二则是取出前十行的数据范围。

同时呢，也有两个相同点，都是原样使用数据，而且输出的结果都是写入其他工作表中。

也就是说“获取数据-->如何使用数据-->输出什么结果”的基本结构中，“挥舞”着各自不同的“招式”。

“Excel文件读写”问题分解

我们可以根据“需要获取哪些数据范围”，“如何使用数据”，“如何输出结果”的步骤过程来分析“Excel文件读写类”的问题。

但是这三大步骤问题的“答案”可以有多种变体，从而衍生出不同的功能效果，解决不同的实际需求。

1. 获取哪些数据范围

常常与Excel表格打交道的话，你肯定知道，即使是对于同一个工作簿，同一个工作表，人们想要获取的数据就可能千差万别。

比如【10月薪资绩效表】，可能需要江宇的个人数据，也可能需要前十行数据作为模版，还可能需要所有员工的数据进行汇总等等。

根据要获取什么数据范围内的单元格来划分，大致可以分为三类：

1、已知坐标的个别单元格
2、单行或单列范围内的单元格
3、多行多列组成的矩形范围内的单元格

 

 

. 如何使用数据

取出数据后，就可以使用数据了。

虽然上面两个案例“使用数据”的方式比较简单，但是如果我说出其他的一些操作方式，你也一定能够理解。毕竟它们就源于日常的Excel使用。

那么除了原样的写入数据，我们还可以从行数据中抽取数据，进行计算（数学计算、字符串拼接等），比如通过“提成”与“绩效”之和，得到本月所有奖金的金额。

另外，还可以抽取数据，形成新的数据行，比如抽取出每行第1个和最后1个单元格的内容，得到一行新的数据。

当然还可以将获得的数据，存储为Python中的数据类型，以便后续使用。比如把每行数据存为字典类型等等。

3. 如何输出结果

数据处理和使用后，肯定会输出一定的数据结果。那么使用完数据，一般会得到什么样新的数据结果呢？

前面我们看到的主要是写入到其他工作表，输出新的工作簿内容。其实“写入到工作表”这种方式下也会有不少细分：

除了你已经知道的，案例一和案例二中的“写入单元格数据”或“写入行数据”。

还可以“指定”从哪一行写起，“指定”写到具体坐标位置等；还可以写到不同的工作表对象中，比如原工作表、其他已有工作表、或者新建工作簿的工作表等。

另外，如果加上“循环”，还可以同时写到多个不同的工作表中，或者多个不同的坐标位置上。

那还会不会还有其他输出结果呢？

还可以将处理后的数据直接输出，打印到终端。

总体而言，我们可以将问题分解划分为三个步骤：获取数据，使用数据，数据输出，各步骤中又蕴含着不同的具体表现。

 

再来回顾刚刚的两个项目案例：

像案例一这种，获取的数据是单个单元格；然后将所取数据原样写入到另外的表格。属于典型的单元格读写模式。

像案例二这种，获取的数据是矩形区域的，一般要按行取出；然后将所取数据原样写入到另外的表格中。属于典型的按行读写模式。

 

案例三：计算并打印奖金信息

依然是从【10月员工绩效表】提取所有员工的工资信息，然后根据每行中员工的“绩效”与“提成”的数值之和，计算出奖金总额，最后按照固定格式输出每位员工的奖金信息。

代码实现：
 

# 从openpyxl库导入load_workbook和Workbook
from openpyxl import load_workbook, Workbook

# 打开【10月员工绩效表.xlsx】工作簿
performance_wb = load_workbook('./material/10月员工绩效表.xlsx')
# 获取活动工作表
performance_ws = performance_wb.active

# 获取performance_ws中除表头外的数据
for row in performance_ws.iter_rows(min_row=2, values_only=True):
    # 读取【工号】
    staff_id = row[0]
    # 读取【员工姓名】
    staff_name = row[1]
    # 读取【绩效】
    performance = row[3]
    # 读取【提成】
    bonus = row[4]
    # 计算“奖金”
    award = performance + bonus
    # 打印结果
    print('工号：{}，姓名：{}，本月奖金为：{}'.format(staff_id, staff_name, award))

 输出结果：

 

 是有两处小的知识点细节，需要你注意：

第1点，使用iter_rows()时，要注意values_only参数，其参数值不同，后面获取单元格的代码就会不同。

上图中左侧的代码中，参数值为True，表示“只取单元格的值”，后续使用索引取到的，直接就是单元格的值。后续代码中不需单元格.value语句。

右侧的代码中，默认为False，表示不是“只取单元格的值”，那么使用索引取出的不仅仅是单元格值，而是单元格对象，因此后续的代码都需要增加.value获取单元格的值。

所以，当我们只需取出单元格的值，可以使用values_only = True，使后续代码更加简洁。

第2点就是循环体的缩进。由于提取部分单元格并计算和打印，都是每获取一条员工信息，就要处理一次。

所以这些语句需要相对上面的for循环有一层缩进。也就是说“使用数据”和“数据输出”作为循环体，要缩进以重复执行。

与前两个案例不同，案例三中对于获取的数据进行了处理使用，不再是原样使用。即，从每行数据选取需要的单元格值，并进行加法运算。这种模式是——“按行取数计算”。

案例四：创建薪资信息字典

依然是【10月薪资绩效表】，要从这个总的薪资表中，读取所需数据范围的行数据，然后取出部分信息，将其对应存储在字典中。

事情是这样的，【10月薪资绩效表】中的员工信息比较多，如果要找到某位同事（比如找到江宇的绩效、基本工资等）得按行整个查找一遍。

如果用Python的话，有没有类似Excel软件中的查找功能，能根据人名或者工号等有代表性的关键字，找到这个人所在的行，得到这行的信息呢？

当然是可以的。还记得Python中的字典类型吗？字典元素为键值对，根据唯一的键，可以找到对应的值。

所以啊，咱们可以“以工号为键，以每位员工的行数据作为值”，存储成一个“薪资信息字典”。

这样就能让“员工的工号”与“该员工的各项薪资信息”形成映射关系，找到工号就能对应关联到员工信息。

而且利用字典提取键和值的方式，也可以很方便地进行数据查询。

代码实现：
 

# 从openpyxl库导入load_workbook函数
from openpyxl import load_workbook

# 打开【10月员工绩效表.xlsx】工作簿
performance_wb = load_workbook('./material/10月员工绩效表.xlsx')
# 获取活动工作表
performance_ws = performance_wb.active

# 创建员工信息字典
staff_info = {}

# 从第二行开始读取工作表中的信息
for row in performance_ws.iter_rows(min_row=2, values_only=True):
    # 取出工号
    member_number = row[0]
    # 将信息存入员工信息字典
    staff_info[member_number] = {
         '姓名': row[1],
         '部门': row[2],
         '绩效': row[3],
         '奖金': row[4],
         '基本工资': row[5],
         '是否确认': row[6]
     }
print(staff_info)

 运行结果：

 

代码实现：

# 从openpyxl库中导入load_workbook函数
from openpyxl import load_workbook

# 打开【10月员工绩效表.xlsx】工作簿，相对路径为'./material/10月员工绩效表.xlsx'
wb = load_workbook(   './material/10月员工绩效表.xlsx' )
# 使用工作簿的active属性，获取活动工作表，存储在变量ws中
ws=wb.active

# 遍历iter_rows()获取的数据范围，最少从第2行取起，只取单元格的值
for row in ws.iter_rows(min_row=2 , values_only=True  ):
    # 打印获取的行数据
    print(row)

 运行结果：

 接下来看如何使用数据。需要选取数据，分别作为键和值，也就是从每一行提取出不同单元格的值。

那么如何将这些数据存储为字典呢？

首先要在循环外创建一个空的字典，才能再往里写入键值对。

代码实现：

# 创建员工信息字典
staff_info = {}

# 从第二行开始读取工作表中的信息
for row in performance_ws.iter_rows(min_row=2, values_only=True):
    # 取出工号
    member_number = row[0]
    # 将信息存入员工信息字典
    staff_info[member_number] = {
        '姓名': row[1],
        '部门': row[2],
        '绩效': row[3],
        '奖金': row[4],
        '基本工资': row[5],
        '是否确认': row[6]
    }
print(staff_info)

 

8-15行中，键对应的值也是一个键值对。

它是表头和数据组成的键值对，你可以结合之前终端打印的字典和下图对表格中“键值”内容的拆分，理解这种情况。

其实，此时是形成了，“字典的值”也都是一个字典的情况，也就是字典嵌套。

除了把每行员工的信息存为字典，我们还可以把每行员工的信息整个存储为元组类型，比如下图：

为什么在使用数据时要将其存储为字典，仅仅是为了打印输出吗？

其实不然，还记得本案例想解决的问题吗？我们想要根据工号，找到该员工对应的信息。

我根据得到的字典，写了一个稍复杂的程序。下面就来看一下“查询员工信息字典”的好用之处。

代码实现：

# 设置薪资信息字典（以前8条数据为例）
staff_info = {
    'S1001': {'姓名': '吕建国', '部门': '销售部', '绩效': 100, '提成': 2250, '基本工资': 7500, '是否确认': '是'},
    'S1002': {'姓名': '张想', '部门': '后勤部', '绩效': 100, '提成': 4250, '基本工资': 7500, '是否确认': '是'},
    'S1003': {'姓名': '王淑兰', '部门': '后勤部', '绩效': 300, '提成': 3500, '基本工资': 6500, '是否确认': '是'},
    'S1004': {'姓名': '赵丽娟', '部门': '开发部', '绩效': 100, '提成': 2750, '基本工资': 7500, '是否确认': '否'},
    'S1005': {'姓名': '陈利', '部门': '开发部', '绩效': 100, '提成': 1750, '基本工资': 6000, '是否确认': '是'},
    'S1006': {'姓名': '车敏', '部门': '销售部', '绩效': 300, '提成': 4250, '基本工资': 7000, '是否确认': '是'},
    'S1007': {'姓名': '孙飞', '部门': '运营部', '绩效': 200, '提成': 1500, '基本工资': 7000, '是否确认': '是'},
    'S1008': {'姓名': '陈彬', '部门': '后勤部', '绩效': 300, '提成': 1500, '基本工资': 9500, '是否确认': '是'}
}

# 输入你想查询的员工的工号
staff_id = input('请输出你所查询员工的工号(如:S1001):')

# 根据工号（键）找到员工信息（值）
dict_staff = staff_info.get(staff_id)

# 判断该员工是否存在
if dict_staff:
    # 输入你想要查询的员工信息
    search_info = input('请输出你想查询的信息(如：姓名/部门/绩效/提成/是否确认)：')

    # 判断该员工信息是否存在
    if dict_staff.get(search_info):
        print('经查询，该员工' + search_info + '为：')
        print(dict_staff[search_info])
    # 无此表头时，无法查询
    else:
        print('所输信息类型错误，只能查询姓名、部门、绩效、提成、是否确认')

# 无此工号时，无法查询
else:
    print('所输工号错误')

 输出结果：

 

可见“存储为字典”是查询数据的好工具吧，可以直接对应查到所需的“姓名”或“部门”等信息，岂不更方便。

另外，当我们有多个表格需要进行对比筛选或者分类时，将数据存为字典也是不错的选择。

因为，当把多个表格中相同的数据，比如【10月薪资绩效表】和【9月薪资绩效表】中的工号，用它作为“暗号”，查出各自的表格信息后，再进行对比或分类。

对于需要将每行数据存储为字典的场景，我们可以称为“按行取数，存为字典”或者“按行读取为字典”。

final案例：取数汇总并写入

目的】：获取【江宇工资信息表】中10月的绩效、提成、基本工资，计算出总和，写入到【江宇工资信息表】并保存。
【江宇工资信息表-相对路径】：./material/江宇工资信息表.xlsx

厘清思路

还是按照“获取数据--使用数据--数据输出”的步骤分解问题。这个问题需求与我们已知的四种问题模式有什么关联和有什么可以借鉴都是需要注意的。

1）获取哪些数据？

要获取三个单元格的值，分别是['E11']、['F11']、['G11']。

2）如何使用数据？

将以上获取的三个数据，相加得到总值。

3）数据输出的结果？

在原工作表中写入数据并保存。具体就是在['H11']写入总工资数额，另外在['H1']写入表头——'总工资'。

 

 实现代码：

# 导入模块
from openpyxl import load_workbook

# 获取数据
wb  = load_workbook('./material/江宇工资信息表.xlsx')
ws = wb['Sheet1']

performance = ws['E11'].value
bonus = ws['F11'].value
base = ws['G11'].value

# 使用数据
salary = performance + bonus + base

# 输出结果
ws['H1'].value = '总工资'
ws['H11'].value = salary
wb.save('./material/江宇工资信息表.xlsx')