import os
import subprocess

import uno
import unohelper
from com.sun.star.connection import NoConnectException
from com.sun.star.lang import IllegalArgumentException


class SpreadScript(object):
    def __init__(self, file_name=None):
        """Initialise the class.

        :arg str file_name: File name.
        """
        self._desktop = None
        self._start_soffice()
        self._connect_soffice()
        if file_name:
            self.open(file_name)

    def __del__(self):
        """Close the soffice instance."""
        if self._desktop:
            self.close()

    def _start_soffice(self):
        """Start soffice in the background."""
        process_id = os.fork()
        if not process_id:
            subprocess.call(
                'soffice --accept="socket,host=localhost,port=2002;urp;" ' + 
                '--norestore --nologo --nodefault --headless', shell=True)
            exit()

    def _connect_soffice(self):
        """Connect to a running soffice instance."""
        context = uno.getComponentContext()
        resolver = context.ServiceManager.createInstanceWithContext(
            'com.sun.star.bridge.UnoUrlResolver', context)
        while True:
            try:
                context = resolver.resolve(
                    'uno:socket,host=localhost,port=2002;urp;' +
                    'StarOffice.ComponentContext')
            except NoConnectException:
                pass
            else:
                break
        self._desktop = context.ServiceManager.createInstanceWithContext(
            'com.sun.star.frame.Desktop', context)

    def _get_cell_text(self, column, row):
        return self._interface.getCellByPosition(column, row).getString()

    def _get_cell_value(self, column, row):
        return self._interface.getCellByPosition(column, row).getValue()

    def _get_cell_formula(self, column, row):
        return self._interface.getCellByPosition(column, row).getFormula()

    def _get_link(self, column, row):
        return self._get_cell_formula(column, row)[1:].replace(
            '$', '').split('.')

    def _set_cell_value(self, sheet, cell, value):
        self._sheets.getByName(sheet).getCellRangeByName(cell).setValue(value)

    def _read_table(self, column):
        """Read names and values from a table.

        :arg int column: Upper-left coordinate of the table content.

        :returns dict: Table content.
        """
        inputs = {}

        row = 3
        while True:
            name = self._get_cell_text(column, row)
            value = self._get_cell_value(column + 1, row)
            if not name:
                break
            inputs[name] = value
            row += 1

        return inputs

    def _write_table(self, column, data):
        """Write values to a table.

        :arg int column: Upper-left coordinate of the table content.
        :arg dict data: Data to be written.
        """
        row = 3
        while True:
            name = self._get_cell_text(column, row)
            if not name:
                break
            if name in data:
                sheet, cell = self._get_link(column + 1, row)
                self._set_cell_value(sheet, cell, data[name])
            row += 1

    def open(self, file_name):
        """Open a spreadsheet.

        :arg str file_name: File name.
        """
        doc_url = unohelper.systemPathToFileUrl(os.path.abspath(file_name))
        try:
            self._desktop.loadComponentFromURL(doc_url, '_blank', 0, ())
        except IllegalArgumentException as error:
            raise ValueError('no such file or format not supported')

        self._sheets = self._desktop.getCurrentComponent().Sheets
        if 'Interface' not in self._sheets:
            raise ValueError('no sheet named "Interface" found')
        self._interface = self._sheets.Interface

    def close(self):
        """Close the soffice instance."""
        self._desktop.terminate()

    def read_input(self):
        return self._read_table(1)

    def write_input(self, data):
        self._write_table(1, data)

    def read_output(self):
        return self._read_table(4)

import os
import subprocess

import uno
import unohelper
from com.sun.star.connection import NoConnectException
from com.sun.star.lang import IllegalArgumentException


class SpreadScript(object):
    def __init__(self, file_name=None):
        """Initialise the class.

        :arg str file_name: File name.
        """
        self._desktop = None
        self._start_soffice()
        self._connect_soffice()
        if file_name:
            self.open(file_name)

    def __del__(self):
        """Close the soffice instance."""
        if self._desktop:
            self.close()

    def _start_soffice(self):
        """Start soffice in the background."""
        process_id = os.fork()
        if not process_id:
            subprocess.call(
                'soffice --accept="socket,host=localhost,port=2002;urp;" ' + 
                '--norestore --nologo --nodefault --headless', shell=True)
            exit()

    def _connect_soffice(self):
        """Connect to a running soffice instance."""
        context = uno.getComponentContext()
        resolver = context.ServiceManager.createInstanceWithContext(
            'com.sun.star.bridge.UnoUrlResolver', context)
        while True:
            try:
                context = resolver.resolve(
                    'uno:socket,host=localhost,port=2002;urp;' +
                    'StarOffice.ComponentContext')
            except NoConnectException:
                pass
            else:
                break
        self._desktop = context.ServiceManager.createInstanceWithContext(
            'com.sun.star.frame.Desktop', context)

    def _get_cell_text(self, column, row):
        return self._interface.getCellByPosition(column, row).getString()

    def _get_cell_value(self, column, row):
        return self._interface.getCellByPosition(column, row).getValue()

    def _get_cell_formula(self, column, row):
        return self._interface.getCellByPosition(column, row).getFormula()

    def _get_link(self, column, row):
        return self._get_cell_formula(column, row)[1:].replace(
            '$', '').split('.')

    def _set_cell_value(self, sheet, cell, value):
        self._sheets.getByName(sheet).getCellRangeByName(cell).setValue(value)

    def _read_table(self, column):
        """Read names and values from a table.

        :arg int column: Upper-left coordinate of the table content.

        :returns dict: Table content.
        """
        inputs = {}

        row = 3
        while True:
            name = self._get_cell_text(column, row)
            value = self._get_cell_value(column + 1, row)
            if not name:
                break
            inputs[name] = value
            row += 1

        return inputs

    def _write_table(self, column, data):
        """Write values to a table.

        :arg int column: Upper-left coordinate of the table content.
        :arg dict data: Data to be written.
        """
        row = 3
        while True:
            name = self._get_cell_text(column, row)
            if not name:
                break
            if name in data:
                sheet, cell = self._get_link(column + 1, row)
                self._set_cell_value(sheet, cell, data[name])
            row += 1

    def open(self, file_name):
        """Open a spreadsheet.

        :arg str file_name: File name.
        """
        doc_url = unohelper.systemPathToFileUrl(os.path.abspath(file_name))
        try:
            self._desktop.loadComponentFromURL(doc_url, '_blank', 0, ())
        except IllegalArgumentException as error:
            raise ValueError('no such file or format not supported')

        self._sheets = self._desktop.getCurrentComponent().Sheets
        if 'Interface' not in self._sheets:
            raise ValueError('no sheet named "Interface" found')
        self._interface = self._sheets.Interface

    def close(self):
        """Close the soffice instance."""
        self._desktop.terminate()

    def read_input(self):
        return self._read_table(1)

    def write_input(self, data):
        self._write_table(1, data)

    def read_output(self):
        return self._read_table(4)

Initialise the class.          :arg str file_name: File name. Close the soffice instance. Start soffice in the background. Connect to a running soffice instance. Read names and values from a table.          :arg int column: Upper-left coordinate of the table content.          :returns dict: Table content. Write values to a table.          :arg int column: Upper-left coordinate of the table content.         :arg dict data: Data to be written. Open a spreadsheet.          :arg str file_name: File name. Close the soffice instance.