diff options
Diffstat (limited to 'scripting')
| -rw-r--r-- | scripting/examples/python/NamedRanges.py | 174 |
1 files changed, 127 insertions, 47 deletions
diff --git a/scripting/examples/python/NamedRanges.py b/scripting/examples/python/NamedRanges.py index 0e47cb406745..f307d9644ffd 100644 --- a/scripting/examples/python/NamedRanges.py +++ b/scripting/examples/python/NamedRanges.py @@ -1,4 +1,3 @@ -# -*- tab-width: 4; indent-tabs-mode: nil; py-indent-offset: 4 -*- # # This file is part of the LibreOffice project. # @@ -6,59 +5,140 @@ # License, v. 2.0. If a copy of the MPL was not distributed with this # file, You can obtain one at http://mozilla.org/MPL/2.0/. # - -import traceback +# This file incorporates work covered by the following license notice: +# +# Licensed to the Apache Software Foundation (ASF) under one or more +# contributor license agreements. See the NOTICE file distributed +# with this work for additional information regarding copyright +# ownership. The ASF licenses this file to you under the Apache +# License, Version 2.0 (the "License"); you may not use this file +# except in compliance with the License. You may obtain a copy of +# the License at http://www.apache.org/licenses/LICENSE-2.0 . +# import uno +from com.sun.star.container import NoSuchElementException + +def DefineNamedRange(doc, SheetName, rangeName, rangeReference): + """Defines a new named range. If the named range exists in the document, then + update the rangeReference. + Example: DefineNamedRange(doc, "Sheet1", "test_range", '$A$1:$F$14'). -def GetNamedRanges(): - """Returns a list of the named ranges in the document. + API Reference: + https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sheet_1_1XNamedRanges.html """ + aName = rangeName + # make sure the sheet name starts with "$" + sheetName = "$" + SheetName.replace("$", "") + aContent = sheetName + "." + rangeReference + try: - desktop = XSCRIPTCONTEXT.getDesktop() - model = desktop.getCurrentComponent() - rangeNames = model.NamedRanges.ElementNames - result = [] - for i in rangeNames: - range = model.NamedRanges.getByName(i).Content - result.append((i, range)) - return result - except Exception as e: - print("Caught Exception: " + str(e)) - tb = e.__traceback__ - traceback.print_tb(tb) - return None - - -def DefineNamedRange(sheet, x0, y0, width, height, name): - """Defines a new (or replaces an existing) named range on a sheet, - using zero-based absolute coordinates - """ - desktop = XSCRIPTCONTEXT.getDesktop() - model = desktop.getCurrentComponent() - # FIXME: Is there some Python-callable API to turn a row and column into an A1 string? - # This obviously works only for the first 26 columns. - abc = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" - content = "$" + sheet + "." + "$" + \ - abc[x0: x0+1] + "$" + str(y0+1) + ":" + "$" + abc[x0+width - - 1: x0+width] + "$" + str(y0+height) - position = uno.createUnoStruct('com.sun.star.table.CellAddress') - position.Sheet = 0 - position.Column = 0 - position.Row = 0 - model.NamedRanges.addNewByName(name, content, position, 0) - return None + # If the named range exists, then update it + doc.NamedRanges.getByName(rangeName) + update = True + except NoSuchElementException: + update = False + if update: + doc.NamedRanges.getByName(rangeName).setContent(aContent) + else: + aPosition = uno.createUnoStruct('com.sun.star.table.CellAddress') + sheet = doc.Sheets.getByName(SheetName) + # the index of the sheet in the doc, 0-based + aPosition.Sheet = sheet.getRangeAddress().Sheet + + addressObj = sheet.getCellRangeByName(rangeReference) + # (com.sun.star.table.CellRangeAddress){ Sheet = (short)0x0, StartColumn = (long)0x0, StartRow = (long)0x0, EndColumn = (long)0x5, EndRow = (long)0xd } + address = addressObj.getRangeAddress() + + aPosition.Column = address.StartColumn + aPosition.Row = address.StartRow + + doc.NamedRanges.addNewByName(aName, aContent, aPosition, 0) -def DeleteNamedRange(name): - try: - desktop = XSCRIPTCONTEXT.getDesktop() - model = desktop.getCurrentComponent() - model.NamedRanges.removeByName(name) - except Exception as e: - print("Caught Exception: " + str(e)) - tb = e.__traceback__ - traceback.print_tb(tb) return None +def NamedRanges(): + """The main function to be shown on the user interface.""" + ctx = uno.getComponentContext() + smgr = ctx.ServiceManager + desktop = smgr.createInstanceWithContext("com.sun.star.frame.Desktop", ctx) + + # Create a blank spreadsheet document, instead of damanging the existing document. + doc = desktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, ()) + + # Create a new sheet to store our output information + doc.Sheets.insertNewByName("Information", 1) + infoSheet = doc.Sheets.getByName("Information") + + # Set text in the information sheet + infoSheet.getCellRangeByName("A1").String = "Operation" + infoSheet.getCellRangeByName("B1").String = "Name of Cell Range" + infoSheet.getCellRangeByName("C1").String = "Content of Named Cell Range" + + # Format the information header row + infoHeaderRange = infoSheet.getCellRangeByName("A1:C1") + # 2 = CENTER, see enum CellHoriJustify in https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1table.html + infoHeaderRange.HoriJustify = 2 + infoHeaderRange.CellBackColor = 0xdee6ef + + # Defines the named range test_range1 + dataSheetName = "data" + doc.Sheets[0].Name = dataSheetName + DefineNamedRange(doc, dataSheetName, "test_range1", "$A$1:$F$14") + + # Displays the named range information + test_range1 = doc.NamedRanges.getByName("test_range1") + infoSheet.getCellRangeByName("A2").String = "Defined test_range1" + infoSheet.getCellRangeByName("B2").String = test_range1.Name + infoSheet.getCellRangeByName("C2").String = test_range1.Content + + # Revise the named ranges. + DefineNamedRange(doc, dataSheetName, "test_range1", "$A$1:$A$10") + infoSheet.getCellRangeByName("A3").String = "Revised test_range1" + infoSheet.getCellRangeByName("B3").String = test_range1.Name + infoSheet.getCellRangeByName("C3").String = test_range1.Content + + # Defines the named range test_range2 + DefineNamedRange(doc, dataSheetName, "test_range2", "$B$1:$B$10") + test_range2 = doc.NamedRanges.getByName("test_range2") + infoSheet.getCellRangeByName("A4").String = "Defined test_range2" + infoSheet.getCellRangeByName("B4").String = test_range2.Name + infoSheet.getCellRangeByName("C4").String = test_range2.Content + + # Set data to test_range1 and test_range2 + + dataSheet = doc.Sheets.getByName(dataSheetName) + # You should use a tuple for setDataArray. For range e.g. A1:E1 it should + # be in the form tuple((1,2,3,4,5)), and for range e.g. A1:A5 it should be + # in the form tuple((1,), (2,), (3,), (4,), (5,)). + data1 = tuple(((1,),(2,),(3,),(4,),(5,),(6,),(7,),(8,),(9,),(10,))) + dataSheet.getCellRangeByName(test_range1.Content).setDataArray(data1) + infoSheet.getCellRangeByName("A5").String = "Set value to test_range1" + + data2 = tuple(((2,),(4,),(6,),(8,),(10,),(12,),(14,),(16,),(18,),(20,))) + dataSheet.getCellRangeByName(test_range2.Content).setDataArray(data2) + infoSheet.getCellRangeByName("A6").String = "Set value to test_range2" + + # Calculate sum of test_range1 + infoSheet.getCellRangeByName("A8").String = "Sum of test_range1:" + infoSheet.getCellRangeByName("B8").Formula = "=SUM(test_range1)" + + # Calcualte sum of test_range2 + infoSheet.getCellRangeByName("A9").String = "Sum of test_range2:" + infoSheet.getCellRangeByName("B9").Formula = "=SUM(test_range2)" + + # Calcualte the difference between the two ranges + infoSheet.getCellRangeByName("A10").String = "sum(test_range2) - sum(test_range1):" + infoSheet.getCellRangeByName("B10").Formula = "=B9-B8" + + # Format the sum header columns + infoSheet.getCellRangeByName("A8:A10").CellBackColor = 0xdee6ef + + # Set column width + infoSheet.Columns.getByName("A").Width = 5590 + infoSheet.Columns.getByName("B").Width = 4610 + infoSheet.Columns.getByName("C").Width = 4610 + +g_exportedScripts = (NamedRanges,) # vim: set shiftwidth=4 softtabstop=4 expandtab: |