Python xlwings 更新表格sheet

需求

有A、B、C三个表格，需要根据A的sheet（名字为“定义”）来更新B、C对应的sheet，并且要保持表格的样式不变。

实现

# #####################################################
# Function: update Excel information and remain the style
# Install:
#  a.install it manually with pip:
#    pip install xlwings
#  b.install with offline file tar.gz with pip:
#    pip install xlwings-0.24.9.tar.gz
# Reference: https://github.com/xlwings/xlwings
# #####################################################
import xlwings as xw
import os
 
def updateInfo(sheetName, path, save_path):
    # file FROM you want to copy sheet
    wb = xw.Book(path)
    # select sheet you want to copy
    sht = wb.sheets[sheetName]
    # file where you want to copy
    new_wb = xw.Book(save_path)
    print(new_wb.sheets)
    # copy needed sheet to the new_wb
    # set the filed from cell A1 to cell Z240
    sht.range('A1:Z240').api.Copy(new_wb.sheets[sheetName].range('a1').api)
    new_wb.save(save_path)
 
if __name__ == "__main__":
 
    sheetName = "定义"
    baseFile = "\A.xlsx"
    updateFile_1 = r"\B.xlsx"
    updateFile_2 = r"\C.xlsx"
 
    # print(os.getcwd())
 
    path = os.getcwd() + baseFile
    save_path1 = os.getcwd() + updateFile_1
    save_path2 = os.getcwd() + updateFile_2
 
    # update info
    updateInfo(sheetName, path, save_path1)
    updateInfo(sheetName, path, save_path2)
 
 

优化

# #####################################################
# Function: update Excel information and remain the style
# Install:
#  a.install it manually with pip:
#    pip install xlwings
#  b.install with offline file tar.gz with pip:
#    pip install xlwings-0.24.9.tar.gz
# Reference: https://github.com/xlwings/xlwings
# #####################################################
import xlwings as xw
import os
 
def updateInfo(sheetName, path, save_path):
    # set app invisible
    app = xw.App(visible=False, add_book=False)
    app.display_alerts = False
    app.screen_updating = False
    # file FROM you want to copy sheet
    # wb = xw.Book(path)
    wb = app.books.open(path)
    # select sheet you want to copy
    sht = wb.sheets[sheetName]
    # file where you want to copy
    # new_wb = xw.Book(save_path)
    new_wb = app.books.open(save_path)
    # print(new_wb.sheets)
    # copy needed sheet to the new_wb
    # set the filed from cell A1 to cell Z240
    sht.range('A1:Z240').api.Copy(new_wb.sheets[sheetName].range('a1').api)
    new_wb.save(save_path)
    wb.close()
    new_wb.close()
 
if __name__ == "__main__":
 
    sheetName = "定义"
    baseFile = "\A.xlsx"
    updateFile_1 = r"\B.xlsx"
    updateFile_2 = r"\C.xlsx"
 
    # print(os.getcwd())
 
    path = os.getcwd() + baseFile
    save_path1 = os.getcwd() + updateFile_1
    save_path2 = os.getcwd() + updateFile_2
 
    # update info
    print("start update information")
    updateInfo(sheetName, path, save_path1)
    updateInfo(sheetName, path, save_path2)
    print("update information finish")
 
 

参考资料

xlwings

xlwings操控excel表格