最近在折腾xlwings，工作中有一个需求就是把一个Excel文件中的多个sheets拆分为独立的Excel文件。之前用的是网上找到的vba代码。

但是既然想用xlwings代替vba，于是就寻找使用xlwings拆分sheets为单独文件的方法。

百度搜索了一圈，目前找到两种方法（详见附录），但都只是把值拆入到新Excel文件了，我想拆分时保留每个sheet的格式（字体、背景色、合并单元格之类）。

既然要保留各sheets的格式，最理想的办法莫过于复制每一个sheet为新的sheet，于是翻墙用Google搜索，找到了这个：

  1. import xlwings as xw
  2. wb = xw.Book('filename.xlsx')
  3. sheet = wb.sheets['Sheet1']
  4. #copy to a new workbook
  5. sheet.api.Copy()

上面代码解决了单个sheet的复制问题，但是没解决：

  • 多个sheets如何复制
  • 复制出来的每个sheets如何保存为单独的Excel文件

解决办法

既然有单个sheet的复制方法，多个sheets复制就很简单，加一个循环就行了。

保存复制每一个sheet后新生成的workbook，我没找到优雅的办法，就用了个比较笨的办法：直接即对对象【xw.books.active】进行保存和关闭操作。

用xw.books获取所有打开的workbooks列表，由于在jupyter notebook代码中，我首先打开的是包含多个sheets的原始文件（叫《xlwings.xlsx》），而复制sheet后产生的新workbook位于它后面，所以用xw.books获取的列表最后一项（xw.books[-1]）就是新的workbook，然后对其使用save()方法和close()方法即可。

查询excel的vba文档（看来还是绕不开vba）可知，copy()方法不加参数，就会生成一个新的workbook对象，并且自动激活为当前的workbook对象。利用这个特性可以获取新生成的workbook对象并对其进行操作。

新的文件名就用原始文档中各sheet的name【sht.name】。

就将拆分的文件放到原始文件的同一个文件夹下。

最后的代码如下：

  1. def xw_splitter(filepath):
  2.     import xlwings as xw
  3.     wb=xw.Book(filepath)
  4.     for sht in wb.sheets:
  5.         ws=wb.sheets[sht.name]
  6.         new=ws.api.Copy()
  7.         #最开始没有找到获取新工作簿的办法，所以用了个比较笨的办法
  8.         ##new=xw.books[-1]
  9.         #找到了获取新工作簿的“正确”方法
  10.         new=xw.books.active
  11.         new.screen_updating=False
  12.         new.display_alerts=False
  13.         path="\\".join(filepath.split("\\")[:-1])
  14.         new.save(r"{}\{}.xlsx".format(path,sht.name))
  15.         new.close()

附录：不保留格式拆分sheets到单独Excel文件的两种方法：

一是利用xlwings写入pandas到excel文件：

  1. def split_worksheets():
  3.     wb = Workbook.caller()
  5.     sheet = Range('temp', 'AA1').value
  6.     column = Range('temp', 'AA2').value
  8.     data = pd.DataFrame(pd.read_excel(sheet, 0, index_col=None, na_values=[0]))
  9.     data.sort(column, axis = 0, inplace = True)
  11.     split = data.groupby(column)
  12.     for i in split.groups:
  13.         Sheet.add()
  14.         Range('A1', index=False).value = split.get_group(i)

二是利用xlwings直接写入值到range：

  1. #!/usr/bin/python 
  3. # Author:Liu Jin Dong 
  4. # Date:2019/5/13
  6. import os
  7. import sys
  8. import time
  9. import xlwings as xw
  12. class SplitExcel(object):
  13.     def __init__(self):
  14.         self.path = os.path.abspath(os.path.dirname(__file__))
  15.         self.time = time.strftime("%Y%m%d", time.localtime(time.time()))
  16.     def get_site_list(self):
  17.         split_excel=input("\033[32;1mExcel Name>>>:\033[0m").strip()
  18.         excel_name=os.path.join(self.path,split_excel)
  19.         if len(excel_name)==0 or os.path.isfile(excel_name) is  None:
  20.             print("\033[31;1mExcel File not found...\033[0m")
  21.             sys.exit(0)
  22.         site_list=[]
  23.         app=xw.App(visible=False,add_book=False)
  24.         app.display_alerts=False
  25.         app.screen_updating=False
  26.         wb=app.books.open(excel_name)
  27.         rng=wb.sheets["sheet1"].range("a2").expand("down")
  28.         rng_count=rng.count + 2
  29.         for obj in rng:
  30.             obj_value=obj.value
  31.             if obj_value not in site_list:
  32.                 site_list.append(obj_value)
  33.         wb.save()
  34.         wb.close()
  35.         app.quit()
  36.         return site_list,rng_count,excel_name
  37.     def run(self):
  38.         app = xw.App(visible=False, add_book=False)
  39.         app.display_alerts = False
  40.         app.screen_updating = False
  41.         site_list,rng_count,excel_name=self.get_site_list()
  42.         print(site_list)
  43.         wb=app.books.open(excel_name)
  44.         for site in site_list:
  45.             file_x = os.path.join(self.path, "%s_%s.xlsx" % (str(site), str(self.time)))
  46.             wb_x = app.books.add()
  47.             n = 2
  48.             m = 2
  49.             wb_x.sheets["sheet1"].range("a1").value = "接入来源"
  50.             wb_x.sheets["sheet1"].range("b1").value = "点位名称"
  51.             wb_x.sheets["sheet1"].range("c1").value = "设备国标ID"
  52.             wb_x.sheets["sheet1"].range("d1").value = "最后人像上传日期"
  53.             while n < rng_count:
  54.                 col_a = wb.sheets["sheet1"].range("a%d" % n).value
  55.                 if str(site) in str(col_a):
  56.                     col_d = wb.sheets["sheet1"].range("d%d" % n).value
  57.                     col_e = wb.sheets["sheet1"].range("e%d" % n).value
  58.                     col_r = wb.sheets["sheet1"].range("r%d" % n).value
  59.                     wb_x.sheets["sheet1"].range("a%d" % m).value = str(col_a)
  60.                     wb_x.sheets["sheet1"].range("b%d" % m).value = str(col_d)
  61.                     wb_x.sheets["sheet1"].range("c%d" % m).value = "'" + str(col_e)
  62.                     wb_x.sheets["sheet1"].range("d%d" % m).value = str(col_r)
  63.                     print(wb_x.sheets["sheet1"].range("a%d" % m).value)
  64.                     print(wb_x.sheets["sheet1"].range("b%d" % m).value)
  65.                     print(wb_x.sheets["sheet1"].range("c%d" % m).value)
  66.                     print(wb_x.sheets["sheet1"].range("d%d" % m).value)
  67.                     print(n)
  68.                     print(m)
  69.                     m += 1
  70.                 n += 1
  71.             wb_x.save(file_x)
  72.             wb_x.close()
  73.         else:
  74.             print("\033[32;1mJob is Done...\033[0m")
  75.         wb.save()
  76.         wb.close()
  77.         app.quit()
  81. if __name__ == "__main__":
  82.     split_excel = SplitExcel()
  83.     split_excel.run()