gctools/excel/refresh.py

import zipfile
import xlwings
import win32com.client
from pathlib import Path


def refresh(filename_str: str):
    filename = Path(filename_str)
    if not filename.exists():
        print("!! Error: File does not exist !!")
        return
    if filename.name.endswith(".xls"):
        return refresh_old(filename)
    return refresh_new(filename)


def refresh_old(xls_file: Path):
    print(f"Refresh '{xls_file.resolve()}'")

    excel = win32com.client.Dispatch("Excel.Application")
    excel.DisplayAlerts = False
    workbook = excel.Workbooks.Open(xls_file.resolve())

    try:
        for source in workbook.LinkSources(1):
            print(" --> " + source)
            if Path(source).exists():
                excel.Workbooks.Open(source)
            else:
                print("     !! does not exist !!")
    except TypeError:
        print(" --> " + "no LinkSources")

    workbook.RefreshAll()
    workbook.Save()
    workbook.Close(True)
    excel.Application.Quit()


def refresh_new(xlsx_file: Path):
    print(f"Refresh '{xlsx_file.resolve()}'")
    try:
        excel = xlwings.App(visible=False)
        workbook = xlwings.Book(xlsx_file, update_links=False)
    except zipfile.BadZipFile as e:
        print(e)
        return

    try:
        for source in workbook.api.LinkSources():
            print(" --> " + source)
            if Path(source).exists():
                xlwings.Book(source, update_links=False, read_only=True)
            else:
                print("     !! does not exist !!")
    except TypeError:
        print(" --> " + "no LinkSources")

    workbook.api.RefreshAll()
    workbook.save()
    workbook.close()
    excel.quit()