#!/usr/bin/env python3 # -*- coding: utf-8 -*- from pathvalidate import sanitize_filepath 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 # nosec: filename used is generated and sanitized import subprocess # nosec #from com.sun.star.table import CellRangeAddress, CellAddress from ishtar_common.utils import num2col from django.conf import settings RETRY = 5 def get_connection(): return "socket,host={},port={};urp".format( settings.LIBREOFFICE_HOST, settings.LIBREOFFICE_PORT ) def convert_document(document, destination_format): if not document.lower().endswith(".ods") and not document.lower().endswith(".odt"): return document document = sanitize_filepath(document) connection = get_connection() + ";StarOffice.ComponentContext" args = [settings.UNOCONV_BINARY, "--connection", connection, "-f", destination_format, document] # nosec: filename is generated and sanitized popen = subprocess.Popen(args) # nosec popen.wait(timeout=20) dest_document = document[:-4] + "." + destination_format return dest_document 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 = get_connection() 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 and 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() if not self.remote_context or not self.desktop: return 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 open_calc(self, filename): return self.get_document(filename) def save_calc(self, calc, filename): url = "file://{}".format(filename) args = (PropertyValue(Name="FilterName", Value="Calc8"),) calc.storeToURL(url, args) def get_sheet_number(self, calc): sheets = calc.getSheets() return sheets.Count def get_sheet(self, calc, sheet_index): return calc.getSheets().getByIndex(sheet_index) def get_or_create_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 sheet_get_last_row_number(self, sheet): cursor = sheet.createCursor() cursor.gotoEndOfUsedArea(False) return cursor.getRangeAddress().EndRow + 1 def sheet_get_data(self, sheet, last_column=None): if not last_column: # assume the first line is header with no empty columns last_column = 0 has_data = True while has_data: has_data = sheet.getCellByPosition(last_column, 0).getString() if not has_data: break last_column += 1 last_row = self.sheet_get_last_row_number(sheet) data = [] for row in range(last_row): data.append([]) for col in range(last_column): value = sheet.getCellByPosition(col, row).getString() data[-1].append(value) return data 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_or_create_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_or_create_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_or_create_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 (AttributeError, TypeError): 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)