用Python自动计算Excel排班表的工时

最近帮朋友写了一个自动计算Excel排班表的工时的Python脚本。原表格中的内容如下：

需求分析

• 排班表中班别为文字表述，位置较为固定（E列到K列），需转换为数字进行计算
• 按行对数字进行求和
• 写入指定位置（L列）
• 可扩展，向下可以增加行

代码实现

导入所用库和文本数据

拟用Pandas对数据进行处理。用openpyxl来进行表格的读写。

#导入运行库
import pandas as pd
from openpyxl import load_workbook

#读取数据
data = pd.read_excel('./排班.xlsx', index_col=3, skiprows=1)
data = data.iloc[:, 3:10]
print(data)

这里需要注意保证目标文件是可读写的状态，否则会Permission Error
由于排班表数据位置较为固定，直接用pandas.DataFrame.iloc[]进行数据的选取。并且代码中默认读取所有行，所以即使Excel中数据进行扩展，依然不影响使用。

文本数据转换为数字并求和

为方便维护，在同文件夹中建立了排班工时对应表.txt的文件。每一行写入排班类别和对应工时。按约定的分隔符分隔。

#读取dict
trans_dict = pd.read_table('./排班工时对应表.txt', sep=' ', index_col=0)
str2time = trans_dict['工时'].to_dict()

#数据清洗与替换
data.dropna(how='all', inplace=True) #清除NaN行
data.replace(str2time, inplace=True) #替换为工时
print(data)

man_hour = data.apply(lambda x: x.sum(), axis=1) #工时数据，按行求和
print(man_hour)

运行中的打印内容如下：
三个表格分别为：读取范围内的数据，转化后的数据，工时求和数据。

写入表格对应位置

剩下的工作就是将数据写入表格中了。

#用openpyxl读取被写入的Excel工作簿
book = load_workbook('./排班.xlsx')

#建立写入对象
write = pd.ExcelWriter(r'./排班.xlsx', engine='openpyxl')
write.book = book
write.sheets = {ws.title: ws for ws in book.worksheets}

man_hour.to_excel(write, sheet_name='sheet1', index=False, header=False, startcol=11, startrow=2) #写入指定位置

write.close() #关闭文件

由于工时数据的行数必然等于姓名的行数，所以只约束了数据写入的起始点。

运行效果

由于代码中的目录都是采用相对目录的形式，把排班表、.py文件和排班工时对应表放在同一个文件夹中。在Windows环境中双击.py文件即可。