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)
    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') 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') 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(xls_file: Path):
    print('\n' + str(xls_file))
    try:
        workbook = openpyxl.load_workbook(xls_file)
    except zipfile.BadZipFile as e:
        print(e)
        return

    # first sheet as master without suffix
    csv_file = f'{xls_file.parent}/{xls_file.stem}.csv'
    print('-> ' + csv_file)
    sheet = workbook.worksheets[0]
    with open(csv_file, 'w', encoding='latin-1') 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'{xls_file.parent}/{xls_file.stem}_{sheet.title}.csv'
        print('-> ' + csv_file)
        with open(csv_file, 'w', encoding='latin-1') 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)