Python如何操作office实现自动化?---win32com.client的运用

应用背景

工作中,由于需要出定期的report,需要用到office,主要是要用到excel表格,然后给各个team或者boss发email report。这里边就包含了不少重复性的工作,工作中常常有一个固定的模板来出report,而每次只需将相关的数据手动导入,如果将这些重复性的动作,实现自动化,无疑可以省去不少功夫。于是我就想到了用python来实现自动化生成表格。今天介绍的只是一部分,主要是excel表格自动修改生成。

似乎是一个很简单的活,调用常用的与excel相关的python模块xlrd, xlwd 或者 openpyxl即可,没错,这些对excel表格进行简单操作都很666,但是我的表格里边出现了透视表,这让我非常的痛苦,一下子发现上边的三个模块不好用,而且用着用着,我还发现,openpyxl似乎是没有直接删除行的功能函数,当你复制之前一个表格为基表,更改一部分内容保存后,透视表就消失了,内心是很崩溃的,在谷歌、百度上看了不少帖子,也没有很好的办法,最后还是stackoverflow上看到一个帖子里,用到了win32com.client,刚开始看不懂,里边好多函数都不知道哪里来的,也没有文档。

最后发现,win32com.client可以直接调用VBA的库,这可就强大了,VBA是包含record macro功能的,手动操作excel直接record,你就可以找到对应的函数,然后就可以调用,一下子很多的功能都实现了。

实例

先来看看案例表格:
这里写图片描述
这里写图片描述

这里只介绍一部分功能实现,也是主要的部分,其余的部分sheet页操作是类似的,这里会介绍一部分模块使用的方法。

第一个表格,主要需要从内部共享的文件夹中,去下载需要的Cases,都是文档类型的文件,需要将它们写入到表一中的AllCases列中,这里比较简单,我们只需要用open和readlines(),用遍历的方式将其写入到excel表中即可。然后在表二中,刷新透视表。以下我截取部分的代码,路径自己构建输入即可,我将分块来介绍。

class AutomationReport(object):

    def __init__(self, excel, win32c, folder_src, folder_dst, src, src_G, src_failed, src_flip, filepath):
        self.excel = excel
        self.win32c = win32c
        self.folder_src = folder_src
        self.folder_dst = folder_dst
        self.src = src
        self.src_G = src_G
        self.src_failed = src_failed
        self.src_flip = src_flip
        self.filepath = filepath

     def write_AllCases(self):
          # open one file and write it into excel AllCases sheet
          with open(self.src, 'r') as f:
              wb = self.excel.Workbooks.Open(self.filepath)
              sheet_AllCases = wb.Worksheets('AllCases')
              i = 2
              for case in f.readlines():
                  cel = 'K%d' % i
                  sheet_AllCases.Range(cel).Value = case.strip()
                  i = i + 1

              # open second file and write it into excel AllCases sheet
              with open(self.src_G, 'r') as fg:
                  for case in fg.readlines():
                      cel = 'K%d' % i
                      sheet_AllCases.Range(cel).Value = case.strip()
                      i = i + 1

              # copy format or delete redundant content
              cel = 'K%d' % i
              if sheet_AllCases.Range(cel).Value is None:
                  rg = "A%s:J%s" % (i-1, i-1)
                  selection = sheet_AllCases.Range("A29000:J29000").Select
                  selection.AutoFill(Destination=sheet_AllCases.Range(rg), Type=xlFillDefault)

              else:
                  # delete redundant content
                  while sheet_AllCases.Range(cel).Value is not None:
                      sheet_AllCases.Rows(i).Delete()

              wb.Save()


if __name__ == "__main__":

    excel = win32com.client.Dispatch('Excel.Application')
    win32c = win32com.client.constants

    # input your path
    folder_src = ...
    ...
    # Excel table file path
    filepath = ...

    # Object instance and invoke functions
    t = AutomationReport(excel, win32c, folder_src, folder_dst, src, src_G, src_failed, src_flip, filepath)
    t.copyFile()
    t.write_AllCases()
    t.write_ReportRaw()

遍历文件内容

遍历文件,写入cases,代码如下,用win32com.client模块(self.excel)打开我们需要修改的表格文件(self.filepath),用wb.Worksheets(‘AllCases’)打开sheet页’AllCases’,注意Worksheets的W是大写的,也记得加上s,用f.readlines()来读取每一行,将每一行用for in 遍历写入一个表格,这里边的Range(‘A1’)表示单元格A1,加上.Value就是它的值。

with open(self.src, 'r') as f:
     wb = self.excel.Workbooks.Open(self.filepath)
     sheet_AllCases = wb.Worksheets('AllCases')
     i = 2
     for case in f.readlines():
         cel = 'K%d' % i
         sheet_AllCases.Range(cel).Value = case.strip()
         i = i + 1

强大的win32com.client

通过以下这种方式就可以调用VBA的函数模块了,如果你要用word,更改成 ’ Word.Applicaiton ’ 即可。第一个是启用excel,第二个用于调用一些VBA中固有的变量,比如调用某个属性,直接在其前边加上即可。

excel = win32com.client.Dispatch('Excel.Application')
win32c = win32com.client.constants

运用这个,我们就可以调用VBA的一些函数模块,比如删除行的功能,在openpyxl中,我没有找到直接删除行的功能,而win32com,可以采用如下方式进行删除行:

sheet_AllCases.Rows(i).Delete()

这里边需要注意的是,VBA中Delete或者很多函数是不加括号调用的,我们在python中,需要添加括号才可以用。

以下是填充上一行的格式,就是我们常用的下拉复制:

rg = "A%s:J%s" % (i-1, i-1)
selection = sheet_AllCases.Range("A29000:J29000").Select
selection.AutoFill(Destination=sheet_AllCases.Range(rg), Type=xlFillDefault)

透视表的功能实现:

 def write_ReportRaw(self):
     # open excel and active sheet
     wb = self.excel.Workbooks.Open(self.filepath)
     sheet_ReportRaw = wb.Worksheets('ReportRaw')

     # refresh Pivot table for All cases and Failed Cases
     sheet_ReportRaw.PivotTables("PivotTable3").PivotCache().Refresh()
     sheet_ReportRaw.PivotTables("PivotTable4").PivotCache().Refresh()

     wb.Save()

这些函数,你无需上网去找,可以直接打开excel表格,在view下有一个Macros,采用Record Macros,然后手动去刷新透视表,停止record,view macro就可以看到刷新透视表的代码了,可以直接copy过来,将相应的参数更改,同时要记得添加括号,上边中,PivotCache()如果没加括号,无法使用。

其他的功能,可以根据自己的需要进行调用。

注意点

使用win32com.client可以让你更加顺利的处理各种excel或者word的复杂情况,相比于openpyxl, xlrd, xlwd功能会更加齐全。但使用过程中,也要注意一些点:

  1. 前边说到的,要对函数加括号
  2. python虽然并不是特别在意大小写,但是使用win32com.client一定要注意大小写,很多函数如果不区分大小写,是无法调用的,比如打开excel表格的Open函数,’O’必须大写,还有wb.Save(),‘S’也必须大写,而我们使用openpyxl使用小写即可。
  3. openpyxl在保存时用save(),很多原有的格式图表是无法保留下来的,比如对excel进行修改,里边原有的透视表,用openpyxl的save()是无法保存的,但是使用win32com.client的wb.Save()却是可以保存这些图表的,这也是更加方便的地方。
  4. 关于VBA函数模块调用,直接采用录制宏的方式获取,无需去找文档,这也是更加方便灵活的地方。
发布了73 篇原创文章 · 获赞 7 · 访问量 1万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览