scripting/examples/python/NamedRanges.py

   1 #
   2 # This file is part of the LibreOffice project.
   3 #
   4 # This Source Code Form is subject to the terms of the Mozilla Public
   5 # License, v. 2.0. If a copy of the MPL was not distributed with this
   6 # file, You can obtain one at http://mozilla.org/MPL/2.0/.
   7 #
   8 import uno
   9 from com.sun.star.container import NoSuchElementException
  10
  11 def DefineNamedRange(doc, SheetName, rangeName, rangeReference):
  12     """Defines a new named range. If the named range exists in the document, then
  13     update the rangeReference.
  14
  15     Example: DefineNamedRange(doc, "Sheet1", "test_range", '$A$1:$F$14').
  16
  17     API Reference:
  18     https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sheet_1_1XNamedRanges.html
  19     """
  20     aName = rangeName
  21     # make sure the sheet name starts with "$"
  22     sheetName = "$" + SheetName.replace("$", "")
  23     aContent = sheetName + "." + rangeReference
  24
  25     try:
  26         # If the named range exists, then update it
  27         doc.NamedRanges.getByName(rangeName)
  28         update = True
  29     except NoSuchElementException:
  30         update = False
  31
  32     if update:
  33         doc.NamedRanges.getByName(rangeName).setContent(aContent)
  34     else:
  35         aPosition = uno.createUnoStruct('com.sun.star.table.CellAddress')
  36         sheet = doc.Sheets.getByName(SheetName)
  37         # the index of the sheet in the doc, 0-based
  38         aPosition.Sheet = sheet.getRangeAddress().Sheet
  39
  40         addressObj = sheet.getCellRangeByName(rangeReference)
  41         # (com.sun.star.table.CellRangeAddress){ Sheet = (short)0x0, StartColumn = (long)0x0, StartRow = (long)0x0, EndColumn = (long)0x5, EndRow = (long)0xd }
  42         address = addressObj.getRangeAddress()
  43
  44         aPosition.Column = address.StartColumn
  45         aPosition.Row = address.StartRow
  46
  47         doc.NamedRanges.addNewByName(aName, aContent, aPosition, 0)
  48
  49     return None
  50
  51 def NamedRanges():
  52     """The main function to be shown on the user interface."""
  53     ctx = uno.getComponentContext()
  54     smgr = ctx.ServiceManager
  55     desktop = smgr.createInstanceWithContext("com.sun.star.frame.Desktop", ctx)
  56
  57     # Create a blank spreadsheet document, instead of damaging the existing document.
  58     doc = desktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, ())
  59
  60     # Create a new sheet to store our output information
  61     doc.Sheets.insertNewByName("Information", 1)
  62     infoSheet = doc.Sheets.getByName("Information")
  63
  64     # Set text in the information sheet
  65     infoSheet.getCellRangeByName("A1").String = "Operation"
  66     infoSheet.getCellRangeByName("B1").String = "Name of Cell Range"
  67     infoSheet.getCellRangeByName("C1").String = "Content of Named Cell Range"
  68
  69     # Format the information header row
  70     infoHeaderRange = infoSheet.getCellRangeByName("A1:C1")
  71     # 2 = CENTER, see enum CellHoriJustify in https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1table.html
  72     infoHeaderRange.HoriJustify = 2
  73     infoHeaderRange.CellBackColor = 0xdee6ef
  74
  75     # Defines the named range test_range1
  76     dataSheetName = "data"
  77     doc.Sheets[0].Name = dataSheetName
  78     DefineNamedRange(doc, dataSheetName, "test_range1", "$A$1:$F$14")
  79
  80     # Displays the named range information
  81     test_range1 = doc.NamedRanges.getByName("test_range1")
  82     infoSheet.getCellRangeByName("A2").String = "Defined test_range1"
  83     infoSheet.getCellRangeByName("B2").String = test_range1.Name
  84     infoSheet.getCellRangeByName("C2").String = test_range1.Content
  85
  86     # Revise the named ranges.
  87     DefineNamedRange(doc, dataSheetName, "test_range1", "$A$1:$A$10")
  88     infoSheet.getCellRangeByName("A3").String = "Revised test_range1"
  89     infoSheet.getCellRangeByName("B3").String = test_range1.Name
  90     infoSheet.getCellRangeByName("C3").String = test_range1.Content
  91
  92     # Defines the named range test_range2
  93     DefineNamedRange(doc, dataSheetName, "test_range2", "$B$1:$B$10")
  94     test_range2 = doc.NamedRanges.getByName("test_range2")
  95     infoSheet.getCellRangeByName("A4").String = "Defined test_range2"
  96     infoSheet.getCellRangeByName("B4").String = test_range2.Name
  97     infoSheet.getCellRangeByName("C4").String = test_range2.Content
  98
  99     # Set data to test_range1 and test_range2
 100
 101     dataSheet = doc.Sheets.getByName(dataSheetName)
 102     # You should use a tuple for setDataArray. For range e.g. A1:E1 it should
 103     # be in the form tuple((1,2,3,4,5)), and for range e.g. A1:A5 it should be
 104     # in the form tuple((1,), (2,), (3,), (4,), (5,)).
 105     data1 = tuple(((1,),(2,),(3,),(4,),(5,),(6,),(7,),(8,),(9,),(10,)))
 106     dataSheet.getCellRangeByName(test_range1.Content).setDataArray(data1)
 107     infoSheet.getCellRangeByName("A5").String = "Set value to test_range1"
 108
 109     data2 = tuple(((2,),(4,),(6,),(8,),(10,),(12,),(14,),(16,),(18,),(20,)))
 110     dataSheet.getCellRangeByName(test_range2.Content).setDataArray(data2)
 111     infoSheet.getCellRangeByName("A6").String = "Set value to test_range2"
 112
 113     # Calculate sum of test_range1
 114     infoSheet.getCellRangeByName("A8").String = "Sum of test_range1:"
 115     infoSheet.getCellRangeByName("B8").Formula = "=SUM(test_range1)"
 116
 117     # Calculate sum of test_range2
 118     infoSheet.getCellRangeByName("A9").String = "Sum of test_range2:"
 119     infoSheet.getCellRangeByName("B9").Formula = "=SUM(test_range2)"
 120
 121     # Calculate the difference between the two ranges
 122     infoSheet.getCellRangeByName("A10").String = "sum(test_range2) - sum(test_range1):"
 123     infoSheet.getCellRangeByName("B10").Formula = "=B9-B8"
 124
 125     # Format the sum header columns
 126     infoSheet.getCellRangeByName("A8:A10").CellBackColor = 0xdee6ef
 127
 128     # Set column width
 129     infoSheet.Columns.getByName("A").Width = 5590
 130     infoSheet.Columns.getByName("B").Width = 4610
 131     infoSheet.Columns.getByName("C").Width = 4610
 132
 133 g_exportedScripts = (NamedRanges,)
 134 # vim: set shiftwidth=4 softtabstop=4 expandtab: