python表格ab怎么换列

转载

mob64ca1402a190 2024-10-17 09:21:49

文章标签 python表格ab怎么换列 excel python 自动化 python副业 文章分类 Python 后端开发

前言

课间在看手机的时候，看到蚂蚁老师在群里发了一个单子，关于Excel处理的200元单子，快速看了一下客户的需求，决定接单来做。

需求分析

做项目之前，我喜欢先分析具体的需求，接单后我就在群里向客户要了参考的文档，并跟他详细对接了一下需求，最终确定下来就是要批量刷新多个 Excel 文件中的数据透视表。

拿到这个需求之后，我依次打开看了客户提供的 Excel 文件，查看了具体的数据内容，在这个过程中，我也找到了解决问题的思路，就是更新每个透视表对应的数据源。

python表格ab怎么换列_python副业

比较熟悉 Excel 的朋友可能会说，直接设置列范围，不要行范围就能覆盖全部数据，不用去更新。这个方法也是可行的，但是在透视表中会出现“（空白）”这个聚合项，可能会导致表格或者图表不好看，还要手动去取消这个选项。

python表格ab怎么换列_自动化_02

选中的空白单元格太多，会不会还有其他的问题，比如性能等，还有待研究。

所以最终我还是确定了处理方案，就是获取数据源的非空单元格内容，将数据范围更新到透视表的 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转成AABdef 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()