Exploration of using Python for a LibreOffice ODS pipeline

.gitignore

output.ods
.~lock*

README.md

gist-python-ods-pipeline

Exploration of using Python for a LibreOffice ODS pipeline

I've been curious if it's possible to leverage the power of a spreadsheet tool (e.g. LibreOffice Calc) and Python for easy scripting for generation/processing

This gist is an exploration of that and it works! =D

Getting started

To run the script, execute the following steps:

# Clone our repository
git clone https://gist.github.com/ed5f05d530f4ba1b1e95a468e81abe0d.git gist-python-ods-pipeline
cd gist-python-ods-pipeline

# Install our dependencies
pipenv install

# Run our script
pipenv run main.py

Results from Python will be output in output.ods with persisted visualization formulas from input.ods

input.ods

main.py

# Load in our dependencies
import math
import pandas
import random
from odf.opendocument import load

# Example will generate monte carlo simulation for dice rolls
# Then output to sheet in existing `.ods` file

# Define our constants
ITERATIONS = 10000

# Define our main function
def main():
    # Run our simulation
    results = {}
    for id in range(ITERATIONS):
        result = random.choice(range(1, 6+1)) + random.choice(range(1, 6+1))
        results[result] = results.get(result, 0) + 1

    # Sanity check our results overlap
    expected_result_range = range(2, 12+1)
    assert set(results).issubset(set(expected_result_range)), f"Expected {sorted(results.keys())} to be between 2-12"

    # Generate our dataframe to output
    df = pandas.DataFrame({'result': expected_result_range, 'count': 0}, index=expected_result_range)
    df.update({'count': results})

    # Copy from our source file and overwrite our data worksheet
    with pandas.ExcelWriter('output.ods') as writer:
        # Load in our source book
        #   https://github.com/eea/odfpy/wiki/LoadSave
        #   https://github.com/pandas-dev/pandas/blob/v1.1.5/pandas/io/excel/_odswriter.py#L28
        writer.book = load('input.ods')

        # Find our target worksheet by its name (so we can delete it)
        #   attributes = {('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'name'): 'Data', ('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'style-name'): 'ta1'}
        target_worksheet = next(
            node
            for node in writer.book.spreadsheet.childNodes
            if node.attributes[('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'name')] == 'Data'
        )
        assert target_worksheet, 'Unable to find "Data" worksheet'

        # Delete our target worksheet (otherwise run into Data_2 sheet creation)
        writer.book.spreadsheet.childNodes.remove(target_worksheet)

        # DEV: What `to_excel` calls
        #   https://github.com/pandas-dev/pandas/blob/v1.1.5/pandas/io/formats/style.py#L195-L236
        #   https://github.com/pandas-dev/pandas/blob/v1.1.5/pandas/io/formats/excel.py#L692-L742
        df.to_excel(writer, index=False, sheet_name='Data')
    print('Output saved to `output.ods`')


# Run our main function
if __name__ == "__main__":
    main()

Pipfile

[[source]]
name = "pypi"
url = "https://pypi.org/simple"
verify_ssl = true

[dev-packages]

[packages]
pandas = ">=1,<2"
odfpy = ">=1,<2"

[requires]
python_version = "3.6"

Pipfile.lock

{
    "_meta": {
        "hash": {
            "sha256": "160fa0e3eba00d2a10b936fa29b9027c9c71954d00bac01d6f8ce48566bd9aab"
        },
        "pipfile-spec": 6,
        "requires": {
            "python_version": "3.6"
        },
        "sources": [
            {
                "name": "pypi",
                "url": "https://pypi.org/simple",
                "verify_ssl": true
            }
        ]
    },
    "default": {
        "defusedxml": {
            "hashes": [
                "sha256:6687150770438374ab581bb7a1b327a847dd9c5749e396102de3fad4e8a3ef93",
                "sha256:f684034d135af4c6cbb949b8a4d2ed61634515257a67299e5f940fbaa34377f5"
            ],
            "markers": "python_version >= '2.7' and python_version not in '3.0, 3.1, 3.2, 3.3, 3.4'",
            "version": "==0.6.0"
        },
        "numpy": {
            "hashes": [
                "sha256:08308c38e44cc926bdfce99498b21eec1f848d24c302519e64203a8da99a97db",
                "sha256:09c12096d843b90eafd01ea1b3307e78ddd47a55855ad402b157b6c4862197ce",
                "sha256:13d166f77d6dc02c0a73c1101dd87fdf01339febec1030bd810dcd53fff3b0f1",
                "sha256:141ec3a3300ab89c7f2b0775289954d193cc8edb621ea05f99db9cb181530512",
                "sha256:16c1b388cc31a9baa06d91a19366fb99ddbe1c7b205293ed072211ee5bac1ed2",
                "sha256:18bed2bcb39e3f758296584337966e68d2d5ba6aab7e038688ad53c8f889f757",
                "sha256:1aeef46a13e51931c0b1cf8ae1168b4a55ecd282e6688fdb0a948cc5a1d5afb9",
                "sha256:27d3f3b9e3406579a8af3a9f262f5339005dd25e0ecf3cf1559ff8a49ed5cbf2",
                "sha256:2a2740aa9733d2e5b2dfb33639d98a64c3b0f24765fed86b0fd2aec07f6a0a08",
                "sha256:4377e10b874e653fe96985c05feed2225c912e328c8a26541f7fc600fb9c637b",
                "sha256:448ebb1b3bf64c0267d6b09a7cba26b5ae61b6d2dbabff7c91b660c7eccf2bdb",
                "sha256:50e86c076611212ca62e5a59f518edafe0c0730f7d9195fec718da1a5c2bb1fc",
                "sha256:5734bdc0342aba9dfc6f04920988140fb41234db42381cf7ccba64169f9fe7ac",
                "sha256:64324f64f90a9e4ef732be0928be853eee378fd6a01be21a0a8469c4f2682c83",
                "sha256:6ae6c680f3ebf1cf7ad1d7748868b39d9f900836df774c453c11c5440bc15b36",
                "sha256:6d7593a705d662be5bfe24111af14763016765f43cb6923ed86223f965f52387",
                "sha256:8cac8790a6b1ddf88640a9267ee67b1aee7a57dfa2d2dd33999d080bc8ee3a0f",
                "sha256:8ece138c3a16db8c1ad38f52eb32be6086cc72f403150a79336eb2045723a1ad",
                "sha256:9eeb7d1d04b117ac0d38719915ae169aa6b61fca227b0b7d198d43728f0c879c",
                "sha256:a09f98011236a419ee3f49cedc9ef27d7a1651df07810ae430a6b06576e0b414",
                "sha256:a5d897c14513590a85774180be713f692df6fa8ecf6483e561a6d47309566f37",
                "sha256:ad6f2ff5b1989a4899bf89800a671d71b1612e5ff40866d1f4d8bcf48d4e5764",
                "sha256:c42c4b73121caf0ed6cd795512c9c09c52a7287b04d105d112068c1736d7c753",
                "sha256:cb1017eec5257e9ac6209ac172058c430e834d5d2bc21961dceeb79d111e5909",
                "sha256:d6c7bb82883680e168b55b49c70af29b84b84abb161cbac2800e8fcb6f2109b6",
                "sha256:e452dc66e08a4ce642a961f134814258a082832c78c90351b75c41ad16f79f63",
                "sha256:e5b6ed0f0b42317050c88022349d994fe72bfe35f5908617512cd8c8ef9da2a9",
                "sha256:e9b30d4bd69498fc0c3fe9db5f62fffbb06b8eb9321f92cc970f2969be5e3949",
                "sha256:ec149b90019852266fec2341ce1db513b843e496d5a8e8cdb5ced1923a92faab",
                "sha256:edb01671b3caae1ca00881686003d16c2209e07b7ef8b7639f1867852b948f7c",
                "sha256:f0d3929fe88ee1c155129ecd82f981b8856c5d97bcb0d5f23e9b4242e79d1de3",
                "sha256:f29454410db6ef8126c83bd3c968d143304633d45dc57b51252afbd79d700893",
                "sha256:fe45becb4c2f72a0907c1d0246ea6449fe7a9e2293bb0e11c4e9a32bb0930a15",
                "sha256:fedbd128668ead37f33917820b704784aff695e0019309ad446a6d0b065b57e4"
            ],
            "markers": "python_version >= '3.6'",
            "version": "==1.19.4"
        },
        "odfpy": {
            "hashes": [
                "sha256:db766a6e59c5103212f3cc92ec8dd50a0f3a02790233ed0b52148b70d3c438ec",
                "sha256:fc3b8d1bc098eba4a0fda865a76d9d1e577c4ceec771426bcb169a82c5e9dfe0"
            ],
            "index": "pypi",
            "version": "==1.4.1"
        },
        "pandas": {
            "hashes": [
                "sha256:0a643bae4283a37732ddfcecab3f62dd082996021b980f580903f4e8e01b3c5b",
                "sha256:0de3ddb414d30798cbf56e642d82cac30a80223ad6fe484d66c0ce01a84d6f2f",
                "sha256:19a2148a1d02791352e9fa637899a78e371a3516ac6da5c4edc718f60cbae648",
                "sha256:21b5a2b033380adbdd36b3116faaf9a4663e375325831dac1b519a44f9e439bb",
                "sha256:24c7f8d4aee71bfa6401faeba367dd654f696a77151a8a28bc2013f7ced4af98",
                "sha256:26fa92d3ac743a149a31b21d6f4337b0594b6302ea5575b37af9ca9611e8981a",
                "sha256:2860a97cbb25444ffc0088b457da0a79dc79f9c601238a3e0644312fcc14bf11",
                "sha256:2b1c6cd28a0dfda75c7b5957363333f01d370936e4c6276b7b8e696dd500582a",
                "sha256:2c2f7c670ea4e60318e4b7e474d56447cf0c7d83b3c2a5405a0dbb2600b9c48e",
                "sha256:3be7a7a0ca71a2640e81d9276f526bca63505850add10206d0da2e8a0a325dae",
                "sha256:4c62e94d5d49db116bef1bd5c2486723a292d79409fc9abd51adf9e05329101d",
                "sha256:5008374ebb990dad9ed48b0f5d0038124c73748f5384cc8c46904dace27082d9",
                "sha256:5447ea7af4005b0daf695a316a423b96374c9c73ffbd4533209c5ddc369e644b",
                "sha256:573fba5b05bf2c69271a32e52399c8de599e4a15ab7cec47d3b9c904125ab788",
                "sha256:5a780260afc88268a9d3ac3511d8f494fdcf637eece62fb9eb656a63d53eb7ca",
                "sha256:70865f96bb38fec46f7ebd66d4b5cfd0aa6b842073f298d621385ae3898d28b5",
                "sha256:731568be71fba1e13cae212c362f3d2ca8932e83cb1b85e3f1b4dd77d019254a",
                "sha256:b61080750d19a0122469ab59b087380721d6b72a4e7d962e4d7e63e0c4504814",
                "sha256:bf23a3b54d128b50f4f9d4675b3c1857a688cc6731a32f931837d72effb2698d",
                "sha256:c16d59c15d946111d2716856dd5479221c9e4f2f5c7bc2d617f39d870031e086",
                "sha256:c61c043aafb69329d0f961b19faa30b1dab709dd34c9388143fc55680059e55a",
                "sha256:c94ff2780a1fd89f190390130d6d36173ca59fcfb3fe0ff596f9a56518191ccb",
                "sha256:edda9bacc3843dfbeebaf7a701763e68e741b08fccb889c003b0a52f0ee95782",
                "sha256:f10fc41ee3c75a474d3bdf68d396f10782d013d7f67db99c0efbfd0acb99701b"
            ],
            "index": "pypi",
            "version": "==1.1.5"
        },
        "python-dateutil": {
            "hashes": [
                "sha256:73ebfe9dbf22e832286dafa60473e4cd239f8592f699aa5adaf10050e6e1823c",
                "sha256:75bb3f31ea686f1197762692a9ee6a7550b59fc6ca3a1f4b5d7e32fb98e2da2a"
            ],
            "markers": "python_version >= '2.7' and python_version not in '3.0, 3.1, 3.2, 3.3'",
            "version": "==2.8.1"
        },
        "pytz": {
            "hashes": [
                "sha256:16962c5fb8db4a8f63a26646d8886e9d769b6c511543557bc84e9569fb9a9cb4",
                "sha256:180befebb1927b16f6b57101720075a984c019ac16b1b7575673bea42c6c3da5"
            ],
            "version": "==2020.5"
        },
        "six": {
            "hashes": [
                "sha256:30639c035cdb23534cd4aa2dd52c3bf48f06e5f4a941509c8bafd8ce11080259",
                "sha256:8b74bedcbbbaca38ff6d7491d76f2b06b3592611af620f8426e82dddb04a5ced"
            ],
            "markers": "python_version >= '2.7' and python_version not in '3.0, 3.1, 3.2, 3.3'",
            "version": "==1.15.0"
        }
    },
    "develop": {}
}