前言
课间在看手机的时候,看到蚂蚁老师在群里发了一个单子,关于Excel处理的200元单子,快速看了一下客户的需求,决定接单来做。
需求分析
做项目之前,我喜欢先分析具体的需求,接单后我就在群里向客户要了参考的文档,并跟他详细对接了一下需求,最终确定下来就是要批量刷新多个 Excel 文件中的数据透视表。
拿到这个需求之后,我依次打开看了客户提供的 Excel 文件,查看了具体的数据内容,在这个过程中,我也找到了解决问题的思路,就是更新每个透视表对应的数据源。
比较熟悉 Excel 的朋友可能会说,直接设置列范围,不要行范围就能覆盖全部数据,不用去更新。这个方法也是可行的,但是在透视表中会出现“(空白)”这个聚合项,可能会导致表格或者图表不好看,还要手动去取消这个选项。
选中的空白单元格太多,会不会还有其他的问题,比如性能等,还有待研究。
所以最终我还是确定了处理方案,就是获取数据源的非空单元格内容,将数据范围更新到透视表的 Cache 中。
项目实施
梳理好了需求和思路之后,写代码的过程就变得很简单,围绕核心问题去展开就行了。
写代码的软件用的就是 PyCharm
,Python
版本用的最新的3.11
,表格处理用了xlwings
和win32com
两个库,win32com
库只是用了其中的常量(这个也可以替换,懒得改了),主要处理还是xlwings
。
1、获取数据源范围
首先要做的就是获取到数据源的范围,打开 Excel 文件并定位到数据源所在的 Sheet,就可以拿到数据范围,上代码:
# 获取数据范围,例如数据表的数据范围是”A1:AB100“,返回”表名!A1:AB100“
def get_data_range(wb, sheet_name: str) -> str:
raw_sheet = wb.sheets[sheet_name]
raw_data = raw_sheet.used_range
nrows = raw_data.last_cell.row
ncols = raw_data.last_cell.column
max_colname = idx_to_colname(ncols - 1)
return sheet_name + '!' + raw_sheet.range('A1', max_colname + str(nrows)).address
在获取数据源范围的时候,有个小细节,就是 Excel 的数据源列名使用字母表示的,而xlwings
库拿到的是行列数,需要将列数转成字母,所以用了一个单独的方法idx_to_colname
来处理:
# 列索引值转成列名,例如0转成A,1转成B,25转成Z,26转成AA,27转成AB,702转成ZZ,703转成AAA,704转成AAB
def idx_to_colname(idx: int) -> str:
base = 65 # ASCII code for 'A'
colname = ''
while idx >= 0:
colname = chr(base + idx % 26) + colname
idx = idx // 26 - 1
return colname
2、更新透视表的数据范围
有了数据范围,就可以去找到每一个透视表来更新它的数据源了:
# 遍历每一个sheet,刷新每一张数据透视表的数据范围
def refresh_pivot_table_data_range(wb, data_range: str) -> None:
for sheet in wb.sheets:
# 跳过原始数据表
if sheet.name == '原始数据':
continue
else:
for pivot_table in sheet.api.PivotTables():
# 更新数据透视表的数据范围
pivot_table.ChangePivotCache(
wb.api.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=data_range,
Version=win32c.xlPivotTableVersion12))
# 刷新数据透视表
pivot_table.PivotCache().Refresh()
3、批量刷新多个Excel文件
单个文件的处理没问题之后,就可以遍历文件夹下所有 Excel 文件来依次处理了:
# 获取文件夹下所有xlsx文件
def get_excel_files(folder_path: str) -> list:
# 获取文件夹下所有文件
files = os.listdir(folder_path)
# 返回所有xlsx文件
return [os.path.join(folder_path, file) for file in files if file.endswith('.xlsx')]
处理单个文件的部分我也优化放到了一个函数里:
# 刷新单个excel文件下所有表的数据透视表
def refresh_file(app, file_path: str) -> None:
# 打开excel文件
wb = app.books.open(file_path)
data_range = get_data_range(wb, '原始数据')
print(data_range)
refresh_pivot_table_data_range(wb, data_range)
# 保存并关闭文件
wb.save()
wb.close()
4、程序执行入口函数
所有步骤的函数都写好之后,就可以放在入口函数中来执行了:
if __name__ == '__main__':
# 创建一个不可见的Excel应用程序
app = xw.App(visible=False, add_book=False)
# 指定文件夹名称
folder_name = '更新'
# 拼接文件夹路径
folder_path = os.path.join(os.getcwd(), folder_name)
# 获取文件夹下所有xlsx文件
files = get_excel_files(folder_path)
# 遍历每一个文件,刷新每一个文件下所有表的数据透视表
for file in files:
print('当前处理文件:' + file)
try:
refresh_file(app, file)
except Exception as e:
print(e)
finally:
print('文件处理完成:' + file)
# 关闭Excel应用程序
app.quit()
总结
这个项目的代码部分不是很难,需求也比较明确,所以代码的编写断断续续花了2个多小时就完成了。代码虽然简单,但是处理问题的思路很重要,需求搞清楚,就完成了80%。
最后,推荐蚂蚁老师的 Python 课程,学会了,你也可以向我一样优秀!:p