Libreoffice toolbox to generate calc files

1 files changed, 190 insertions, 0 deletions

diff --git a/ishtar_common/libreoffice.py b/ishtar_common/libreoffice.py
new file mode 100644
index 000000000..6337675fe
--- /dev/null
+++ b/ishtar_common/libreoffice.py
@@ -0,0 +1,190 @@
+#!/usr/bin/env python
+# -*- coding: utf-8 -*-
+
+import time
+
+import uno
+from com.sun.star.awt import Size
+from com.sun.star.beans import PropertyValue
+from com.sun.star.connection import NoConnectException
+from com.sun.star.sheet.ValidationType import LIST
+
+from com.sun.star.table import CellRangeAddress, CellAddress
+
+from ishtar_common.utils import num2col
+
+from django.conf import settings
+
+
+RETRY = 1
+
+
+class UnoClient:
+    def __init__(self):
+        self.service_manager = None
+        self.remote_context = None
+        self.desktop = None
+        self.connect()
+
+    def connect(self):
+        local_context = uno.getComponentContext()
+
+        resolver = local_context.ServiceManager.createInstanceWithContext(
+            "com.sun.star.bridge.UnoUrlResolver", local_context)
+        connection = "socket,host={},port={};urp".format(
+            settings.LIBREOFFICE_HOST, settings.LIBREOFFICE_PORT
+        )
+        try:
+            self.service_manager = resolver.resolve(
+                "uno:{};StarOffice.ServiceManager".format(connection))
+        except NoConnectException:
+            self.service_manager = None
+        # self.service_manager = self.service_manager.ServiceManager
+
+    def create_context(self):
+        if self.remote_context and self.desktop:
+            return
+        try_nb = 0
+        while not self.service_manager or try_nb > RETRY:
+            self.connect()
+            try_nb += 1
+        if not self.service_manager:
+            return
+        self.remote_context = self.service_manager.getPropertyValue(
+            "DefaultContext")
+        self.desktop = self.service_manager.createInstanceWithContext(
+            "com.sun.star.frame.Desktop", self.remote_context)
+
+    def create_document(self, app):
+        self.create_context()
+        if not self.remote_context or not self.desktop:
+            return
+        url = "private:factory/{}".format(app)
+        return self.desktop.loadComponentFromURL(url, "_blank", 0, ())
+
+    def get_document(self, filename, propval=None):
+        self.create_context()
+        url = "file://{}".format(filename)
+        if not propval:
+            propval = ()
+        return self.desktop.loadComponentFromURL(url, "_blank", 0, propval)
+
+
+class UnoCalc(UnoClient):
+    def create_calc(self):
+        return self.create_document('scalc')
+
+    def save_calc(self, calc, filename):
+        url = "file://{}".format(filename)
+        args = (PropertyValue(Name="FilterName", Value="Calc8"),)
+        calc.storeToURL(url, args)
+
+    def get_sheet(self, calc, sheet_index, name=None):
+        sheets = calc.getSheets()
+        while sheets.Count < (sheet_index + 1):
+            if name and sheets.Count == sheet_index:
+                sheet_name = name
+            else:
+                sheet_name = "Sheet{}".format(sheets.Count + 1)
+            sheets.insertNewByName(sheet_name, sheets.Count)
+        return calc.getSheets().getByIndex(sheet_index)
+
+    def create_list(self, sheet, col, start_row, title, items):
+        items = [title] + items
+        row_idx = 0
+        for row_idx, item in enumerate(items):
+            cell = sheet.getCellByPosition(col, start_row + row_idx)
+            if not row_idx:  # bold for title
+                cell.CharWeight = 150
+            cell.setString(item)
+        return start_row + row_idx
+
+    def set_cell_validation_list(
+            self, sheet, col, row_min, row_max, data_sheet, data_col,
+            data_row_range):
+        validation = sheet.getCellByPosition(col, row_min).Validation
+        validation.setPropertyValue("Type", LIST)
+
+        dat_col_str = num2col(data_col + 1)
+        form = "$'{sheet}'.${col}${row_min}:${col}${row_max}".format(
+            sheet=data_sheet.getName(), col=dat_col_str,
+            row_min=data_row_range[0] + 1, row_max=data_row_range[1] + 1)
+        validation.setFormula1(form)
+        validation.setPropertyValue("ShowErrorMessage", True)
+
+        col_str = num2col(col)
+        ooorange = "{}{}:{}{}".format(
+            col_str, row_min + 1, col_str, row_max + 1)
+        sheet.getCellRangeByName(ooorange).setPropertyValue(
+            "Validation", validation)
+
+    def load_graphic_into_doc(self, calc, image_path):
+        # WIP
+        bitmap_component = calc.createInstance(
+            "com.sun.star.drawing.BitmapTable"
+        )
+        oo_id = "ishtar-gen-image-{}".format(time.time())
+        bitmap_component.insertByName(oo_id, image_path)
+        internal_url = bitmap_component.getByName(oo_id)
+
+        img_temp = calc.createInstance(
+            "com.sun.star.drawing.GraphicObjectShape")
+        img_temp.setPropertyValue("GraphicURL", internal_url)
+        size = Size()
+        size.Width = 3000
+        size.Height = 3000
+        img_temp.setSize(size)
+        # bitmap_component.removeByName(oo_id)
+        return img_temp
+
+    def test(self):
+        self.test_2()
+
+    def test_1(self):
+        title = "Test"
+        lst = ["item 1", "item 2"]
+
+        if not lst:  # no list
+            return
+        calc = self.create_calc()
+        lst_sheet = self.get_sheet(calc, 1, "List types")
+
+        lst_col, lst_row = 0, 0
+        end_row = self.create_list(lst_sheet, lst_col, lst_row, title, lst)
+        main_sheet = self.get_sheet(calc, 0)
+        self.set_cell_validation_list(main_sheet, 0, 0, 200, lst_sheet, lst_col,
+                                      [lst_row + 1, end_row])
+        self.save_calc(calc, "/tmp/test.ods")
+
+    def test_2(self):
+        propval = PropertyValue()
+        propval.Name = 'Hidden'
+        propval.Value = True
+        filename = "/tmp/main.ods"
+        calc = self.get_document(filename, (propval,))
+        sheet = self.get_sheet(calc, 0)
+        validation = sheet.getCellByPosition(0, 0).Validation
+        for k in dir(validation):
+            if k.startswith("get"):
+                try:
+                    print(k, getattr(validation, k)())
+                except:
+                    pass
+
+    def test_image(self):
+        # WIP
+        calc = self.create_calc()
+        sheet = calc.getSheets().getByIndex(0)
+        cell = sheet.getCellByPosition(1, 1)
+        image_path = "file:///tmp/test.jpg"
+        img_temp = self.load_graphic_into_doc(calc, image_path)
+
+        draw_page = calc.getDrawPages().getByIndex(0)
+
+        img_temp.setPosition(cell.Position)
+
+        draw_page.add(img_temp)
+        url = "file:///tmp/test.ods"
+        args = (PropertyValue(Name="FilterName", Value="Calc8"),)
+        calc.storeToURL(url, args)
+