應(yīng)用背景

工作中，由于需要出定期的report，需要用到office，主要是要用到excel表格，然后給各個(gè)team或者boss發(fā)email report。這里邊就包含了不少重復(fù)性的工作，工作中常常有一個(gè)固定的模板來出report，而每次只需將相關(guān)的數(shù)據(jù)手動(dòng)導(dǎo)入，如果將這些重復(fù)性的動(dòng)作，實(shí)現(xiàn)自動(dòng)化，無疑可以省去不少功夫。于是我就想到了用python來實(shí)現(xiàn)自動(dòng)化生成表格。今天介紹的只是一部分，主要是excel表格自動(dòng)修改生成。

似乎是一個(gè)很簡單的活，調(diào)用常用的與excel相關(guān)的python模塊xlrd, xlwd 或者 openpyxl即可，沒錯(cuò)，這些對(duì)excel表格進(jìn)行簡單操作都很666，但是我的表格里邊出現(xiàn)了透視表，這讓我非常的痛苦，一下子發(fā)現(xiàn)上邊的三個(gè)模塊不好用，而且用著用著，我還發(fā)現(xiàn)，openpyxl似乎是沒有直接刪除行的功能函數(shù)，當(dāng)你復(fù)制之前一個(gè)表格為基表，更改一部分內(nèi)容保存后，透視表就消失了，內(nèi)心是很崩潰的，在谷歌、百度上看了不少帖子，也沒有很好的辦法，最后還是stackoverflow上看到一個(gè)帖子里，用到了win32com.client，剛開始看不懂，里邊好多函數(shù)都不知道哪里來的，也沒有文檔。

最后發(fā)現(xiàn)，win32com.client可以直接調(diào)用VBA的庫，這可就強(qiáng)大了，VBA是包含record macro功能的，手動(dòng)操作excel直接record，你就可以找到對(duì)應(yīng)的函數(shù)，然后就可以調(diào)用，一下子很多的功能都實(shí)現(xiàn)了。

實(shí)例

先來看看案例表格：

這里只介紹一部分功能實(shí)現(xiàn)，也是主要的部分，其余的部分sheet頁操作是類似的，這里會(huì)介紹一部分模塊使用的方法。

第一個(gè)表格，主要需要從內(nèi)部共享的文件夾中，去下載需要的Cases，都是文檔類型的文件，需要將它們寫入到表一中的AllCases列中，這里比較簡單，我們只需要用open和readlines()，用遍歷的方式將其寫入到excel表中即可。然后在表二中，刷新透視表。以下我截取部分的代碼，路徑自己構(gòu)建輸入即可，我將分塊來介紹。

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()

遍歷文件內(nèi)容

遍歷文件，寫入cases，代碼如下，用win32com.client模塊（self.excel）打開我們需要修改的表格文件（self.filepath），用wb.Worksheets(‘AllCases')打開sheet頁'AllCases'，注意Worksheets的W是大寫的，也記得加上s，用f.readlines()來讀取每一行，將每一行用for in 遍歷寫入一個(gè)表格，這里邊的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

強(qiáng)大的win32com.client

通過以下這種方式就可以調(diào)用VBA的函數(shù)模塊了，如果你要用word，更改成 ' Word.Applicaiton ' 即可。第一個(gè)是啟用excel，第二個(gè)用于調(diào)用一些VBA中固有的變量，比如調(diào)用某個(gè)屬性，直接在其前邊加上即可。

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

運(yùn)用這個(gè)，我們就可以調(diào)用VBA的一些函數(shù)模塊，比如刪除行的功能，在openpyxl中，我沒有找到直接刪除行的功能，而win32com，可以采用如下方式進(jìn)行刪除行：

sheet_AllCases.Rows(i).Delete()

這里邊需要注意的是，VBA中Delete或者很多函數(shù)是不加括號(hào)調(diào)用的，我們在python中，需要添加括號(hào)才可以用。

以下是填充上一行的格式，就是我們常用的下拉復(fù)制：

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)

透視表的功能實(shí)現(xiàn)：

 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()

這些函數(shù)，你無需上網(wǎng)去找，可以直接打開excel表格，在view下有一個(gè)Macros，采用Record Macros，然后手動(dòng)去刷新透視表，停止record，view macro就可以看到刷新透視表的代碼了，可以直接copy過來，將相應(yīng)的參數(shù)更改，同時(shí)要記得添加括號(hào)，上邊中，PivotCache()如果沒加括號(hào)，無法使用。

其他的功能，可以根據(jù)自己的需要進(jìn)行調(diào)用。

注意點(diǎn)

使用win32com.client可以讓你更加順利的處理各種excel或者word的復(fù)雜情況，相比于openpyxl, xlrd, xlwd功能會(huì)更加齊全。但使用過程中，也要注意一些點(diǎn)：

• 前邊說到的，要對(duì)函數(shù)加括號(hào)
• python雖然并不是特別在意大小寫，但是使用win32com.client一定要注意大小寫，很多函數(shù)如果不區(qū)分大小寫，是無法調(diào)用的，比如打開excel表格的Open函數(shù)，'O'必須大寫，還有wb.Save()，‘S'也必須大寫，而我們使用openpyxl使用小寫即可。
• openpyxl在保存時(shí)用save()，很多原有的格式圖表是無法保留下來的，比如對(duì)excel進(jìn)行修改，里邊原有的透視表，用openpyxl的save()是無法保存的，但是使用win32com.client的wb.Save()卻是可以保存這些圖表的，這也是更加方便的地方。
• 關(guān)于VBA函數(shù)模塊調(diào)用，直接采用錄制宏的方式獲取，無需去找文檔，這也是更加方便靈活的地方。
  

到此這篇關(guān)于Python如何操作office實(shí)現(xiàn)自動(dòng)化及win32com.client的運(yùn)用的文章就介紹到這了,更多相關(guān)Python操作office自動(dòng)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論