How Python helps outside of work: ods data to excel

Written by on in Coding

I hate copying and pasting, so might as well automate it

9:43:24 PM Trying to complete my research paper.. …

If you have any question, do ask in the comment section. See you!

This allows for transforming my .ods file to .xlsx

//Note –> You will have to tweak this to your needs. This is a personalised solution

# USAGE python getdata.py


from pyexcel_ods import get_data
import os
import re
import json
import openpyxl
import logging
import typing
import sys

logging.basicConfig(level=logging.DEBUG,
                    format="%(asctime)s - %(levelname)s - %(message)s")


def get_rows_xy(sheet: typing.List):
    '''
            argument:
                    sheet : a multidimensional list
            Gets all the xy data 
            returns : list 
    '''
    start = False  # when true, starts appending to the data list
    data = []
    dictionary = {"y": None, "x": None}
    for sub_list in sheet:
        if len(sub_list) == 0:
            break
        if start:
            data.append(sub_list[:2])
    dictionary["data"] = data
    return comma_to_dot(dictionary)


def comma_to_dot(dictionary: dict):
    '''
            transform a German format number to US format e.g. 100, 36 to 100.36
            arguments : dictionary -> dict 
            returns:  data -> transformed rows (all values are numbers with "," replaced with ".")
    '''
    data = []
    pattern = re.compile(r"(\d+),(\d+)")
    for sub_row in dictionary["data"]:
        temp = []
        for value in sub_row:
            if type(value) == int:
                #u = float(value)  / 1000
                #u = str(u)
                #print(str(value) + " = " + u)
                value = float(value) / 1000
                value = str(value)
            update = re.sub(pattern.pattern, r"\1.\2", str(value))
            update = float(update)
            temp.append(update)
        data.append(temp)
    dictionary["data"] = data
    return dictionary


def add_rows_to_sheet(dictionary: dict, sheet):
    '''
            Adds data to worksheet 
            arguments :
                    rows: list of data 
                    sheet: worksheet 
            returns : 
                    worksheet (updated with rows)
    '''
    rows = dictionary["data"]
    offset = 0
    if "x" in dictionary and "y" in dictionary:
        sheet.cell(row=1, column=1, value=dictionary["x"])
        sheet.cell(row=1, column=2, value=dictionary["y"])
        offset = 1
    for row in range(len(rows)):
        sub_row = rows[row]
        for col in range(len(sub_row)):
            sheet.cell(row=row + 1 + offset,
                       column=col + 1, value=sub_row[col])
    return sheet


def ods_to_excel(filepath, target) -> None:
    '''
            Generates an excel workbook with ods data 
            argument: 
                    filepath --> a valid filepath to the directory containing ods documents 
                    target -> a valid filepath to the directory for the generated excel sheets 
            returns:
                    None
    '''
    filename = os.path.basename(filepath)
    filename = filename.split(".")[0]
    data = get_data(filepath)
    json_data = json.dumps(data)
    json_data = json.loads(json_data)
    workbook_path = os.path.join(target, filename + ".xlsx")
    if not os.path.exists(workbook_path):
        wb = openpyxl.Workbook()
    else:
        wb = openpyxl.load_workbook(workbook_path)
    for sheetname in json_data:
        ws = wb.active
        ws.title = sheetname[:21]
        data_update = get_rows_xy(json_data[sheetname])
        ws = add_rows_to_sheet(data_update, ws)
        logging.info("Adding data to " + sheetname)
    wb.save(workbook_path)
    logging.info("Complete")


if __name__ == "__main__":
    if len(sys.argv) >= 3:
        path = sys.argv[1]
        filename = sys.argv[2]
        destination = os.path.abspath(".")
        if(len(sys.argv) == 4):
            destination = os.path.abspath(sys.argv[3])
        if(os.path.exists(destination)):
            filepath = os.path.join(path, filename)
            if os.path.exists(filepath):
                ods_to_excel(filepath, destination)
            else:
                logging.error("Filepath does not exist: " + filepath)
        else:
            logging.error("Destination path does not exist: " + destination)
    else:
        logging.error(
            "Usage : getdata.py   . (destination is optional)")

These might interest you: