gctools/excel/convert.py

import zipfile
import openpyxl
import xlrd
import csv
import plac
from pathlib import Path


def convert_folder(folder=None):
    if folder is None:
        folder = str(Path(__file__).parent)
    if Path(folder).is_file():
        xls_file = Path(folder)
        if xls_file.name[-4:] == ".xls":
            convert_old(xls_file)
        elif xls_file.name[-5:] == ".xlsx":
            convert(xls_file)
        return
    for xls_file in Path(folder).absolute().glob("*.xls"):
        convert_old(xls_file)
    for xls_file in Path(folder).absolute().glob("*.xlsx"):
        convert(xls_file)


def localize_floats(el):
    return str(el).replace(".", ",") if isinstance(el, float) else el


def convert_old(xls_file: Path):
    print("\n" + str(xls_file))
    try:
        workbook = xlrd.open_workbook(xls_file, ignore_workbook_corruption=True)
    except xlrd.XLRDError as e:
        print(e)
        return

    # first sheet as master without suffix
    csv_file = f"{xls_file.parent}/{xls_file.stem}.csv"
    print("-> " + csv_file)
    ws = workbook.sheet_by_index(0)
    with open(csv_file, "w", encoding="latin-1", errors="replace", newline="") as fwh:
        wr = csv.writer(fwh, delimiter=";")
        for rownum in range(ws.nrows):
            wr.writerow([localize_floats(el) for el in ws.row_values(rownum)])

    for sheet in workbook.sheet_names():
        csv_file = f"{xls_file.parent}/{xls_file.stem}_{sheet}.csv"
        print("-> " + csv_file)
        ws = workbook.sheet_by_name(sheet)
        with open(csv_file, "w", encoding="latin-1", errors="replace", newline="") as fwh:
            wr = csv.writer(fwh, delimiter=";")
            for rownum in range(ws.nrows):
                wr.writerow([localize_floats(el) for el in ws.row_values(rownum)])


def convert(xlsx_file: Path):
    print("\n" + str(xlsx_file))
    try:
        workbook = openpyxl.load_workbook(xlsx_file, data_only=True)
    except zipfile.BadZipFile as e:
        print(e)
        return

    # first sheet as master without suffix
    csv_file = f"{xlsx_file.parent}/{xlsx_file.stem}.csv"
    print("-> " + csv_file)
    sheet = workbook.worksheets[0]
    with open(csv_file, "w", encoding="latin-1", errors="replace", newline="") as fwh:
        wr = csv.writer(fwh, delimiter=";")
        for row in sheet.iter_rows():
            wr.writerow([localize_floats(cell.value) for cell in row])

    for sheet in workbook:
        csv_file = f"{xlsx_file.parent}/{xlsx_file.stem}_{sheet.title}.csv"
        print("-> " + csv_file)
        with open(csv_file, "w", encoding="latin-1", errors="replace", newline="") as fwh:
            wr = csv.writer(fwh, delimiter=";")
            for row in sheet.iter_rows():
                wr.writerow([localize_floats(cell.value) for cell in row])


if __name__ == "__main__":
    plac.call(convert_folder)