import os
import time

import xlwings as xw


def patchCreate():
    """
    批量创建excel文件,并关闭文件
    :return:
    """
    # visible创建过程可见       add_book默认不要添加book
    app = xw.App(visible=True, add_book=False)
    for i in ["研发部", "销售部", "运营部", "市场部", "总经办"]:
        workbook = app.books.add()
        workbook.save(f"./华耀集团--{i}.xlsx")
        print(f'======================正在创建  {i} 文件中===========================')
    time.sleep(5)
    app.kill()
    time.sleep(5)


def patchOpen():
    """
    批量打开文件
    :return:
    """
    app = xw.App(visible=True, add_book=False)
    for file in os.listdir('.'):
        if file.endswith('.xlsx'):
            app.books.open(file)


def patchRename():
    """
    批量重命名
    :return:
    """
    app = xw.App(visible=True, add_book=False)
    workbook = app.books.open('test.xlsx')
    for sheet in workbook.sheets:
        sheet.name = sheet.name.replace('华耀', '信安世纪')
    workbook.save()
    app.quit()


def deleteExcel():
    """
    批量删除excel文件
    :return:
    """
    for file in os.listdir('.'):
        if file.endswith('xlsx'):
            os.remove(file)
            print(f'========================删除   {file} 成功===========================')


def patchReplaceSheet(path, oldSheet, newSheet):
    """
    批量替换excel中sheet
    :return:
    """
    app = xw.App(visible=True, add_book=False)
    workbook = app.books.open(path)
    for sheet in workbook.sheets:
        sheet.name = sheet.name.replace(oldSheet, newSheet)
    workbook.save()
    app.kill()


def patchCreateSheet():
    """
    批量创建sheet
    :return:
    """
    # visible创建过程可见       add_book默认不要添加book
    try:
        if os.path.exists('./test.xlsx'):
            os.remove('./test.xlsx')
            print('删除test.xlsx文件成功')
            time.sleep(10)
    finally:
        app = xw.App(visible=True, add_book=False)
        workbook = app.books.add()
        workbook.save('./test.xlsx')
        time.sleep(5)
        app.kill()