gctools/excel/split.py

import win32com.client
from pathlib import Path


excel_format = {".csv": 6, ".xls": 56, ".xlsx": 51}


def split(file, format=None):
    excel_com = win32com.client.Dispatch("Excel.Application")

    excel_com.DisplayAlerts = False
    # excel_com.Visible = True

    filename = Path(file)
    path = str(filename.parent.resolve())
    print(filename.resolve())

    ext = filename.suffix if format is None else format

    wb = excel_com.Workbooks.Open(filename.resolve())

    for sh in wb.Sheets:
        tab_filename = f"{filename.stem}_{sh.Name}{ext}"
        print(" --> " + tab_filename)
        wb2 = excel_com.Workbooks.Add()
        wb.Worksheets(sh.Name).Activate()
        ws = excel_com.ActiveSheet
        ws.Copy(Before=wb2.sheets(1))
        wb2.sheets(2).Delete()
        wb2.UpdateLinks = 2
        wb2.SaveAs(Filename=path + "\\" + tab_filename, FileFormat=excel_format[ext])
        wb2.Close(True)

    wb.Close(True)


if __name__ == "__main__":
    split("Excel\\Mappe1.xlsx", "csv")