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)")
