odk/examples/python/Spreadsheet/SCalc.py

   1 # -*- tab-width: 4; indent-tabs-mode: nil; py-indent-offset: 4 -*-
   2 #
   3 # This file is part of the LibreOffice project.
   4 #
   5 # This Source Code Form is subject to the terms of the Mozilla Public
   6 # License, v. 2.0. If a copy of the MPL was not distributed with this
   7 # file, You can obtain one at http://mozilla.org/MPL/2.0/.
   8 #
   9
  10 import sys
  11 import time
  12 import traceback
  13
  14 import officehelper
  15 from com.sun.star.awt import Rectangle
  16 from com.sun.star.lang import IndexOutOfBoundsException
  17
  18 """
  19 Step 1: get the remote component context from the office
  20 Step 2: open an empty calc document
  21 Step 3: create cell styles
  22 Step 4: get the sheet an insert some data
  23 Step 5: apply the created cell styles
  24 Step 6: insert a 3D Chart
  25 """
  26
  27
  28 def main():
  29     # oooooooooooooooooooooooooooStep 1oooooooooooooooooooooooooooooooooooooooooo
  30     # call UNO bootstrap method and get the remote component context form
  31     # the a running office (office will be started if necessary)
  32     try:
  33         remote_context = officehelper.bootstrap()
  34         print("Connected to a running office ...")
  35         srv_mgr = remote_context.getServiceManager()
  36         desktop = srv_mgr.createInstanceWithContext(
  37             "com.sun.star.frame.Desktop", remote_context
  38         )
  39     except Exception as e:
  40         print(f"Couldn't get Sheet: {e}")
  41         traceback.print_exc()
  42         sys.exit(1)
  43
  44     # oooooooooooooooooooooooooooStep 2oooooooooooooooooooooooooooooooooooooooooo
  45     # open an empty document. In this case it's a calc document.
  46     # For this purpose an instance of com.sun.star.frame.Desktop
  47     # is created. The desktop provides the XComponentLoader interface,
  48     # which is used to open the document via loadComponentFromURL
  49     print("Opening an empty Calc document")
  50     doc_url = "private:factory/scalc"
  51     try:
  52         doc = desktop.loadComponentFromURL(doc_url, "_blank", 0, tuple())
  53     except Exception as e:
  54         print(f"Couldn't get Sheet: {e}")
  55         traceback.print_exc()
  56         return
  57
  58     # oooooooooooooooooooooooooooStep 3oooooooooooooooooooooooooooooooooooooooooo
  59     # create cell styles.
  60     # For this purpose get the StyleFamiliesSupplier and the family CellStyle.
  61     # Create an instance of com.sun.star.style.CellStyle and add it to the family.
  62     # Now change some properties
  63
  64     try:
  65         cell_styles = doc.StyleFamilies["CellStyles"]
  66         cell_style = doc.createInstance("com.sun.star.style.CellStyle")
  67         cell_styles["My Style"] = cell_style
  68         cell_style.IsCellBackgroundTransparent = False
  69         cell_style.CellBackColor = 6710932
  70         cell_style.CharColor = 16777215
  71         cell_style = doc.createInstance("com.sun.star.style.CellStyle")
  72         cell_styles["My Style2"] = cell_style
  73         cell_style.IsCellBackgroundTransparent = False
  74         cell_style.CellBackColor = 13421823
  75     except Exception as e:
  76         print(f"Couldn't get Sheet: {e}")
  77         traceback.print_exc()
  78
  79     # oooooooooooooooooooooooooooStep 4oooooooooooooooooooooooooooooooooooooooooo
  80     # get the sheet an insert some data.
  81     # Get the sheets from the document and then the first from this container.
  82     # Now some data can be inserted. For this purpose get a Cell via
  83     # getCellByPosition and insert into this cell via setValue() (for floats)
  84     # or setFormula() for formulas and Strings.
  85     # As a Python example, those calls are made in equivalent Pythonic ways.
  86
  87     print("Getting spreadsheet")
  88     try:
  89         sheet = doc.Sheets[0]
  90     except Exception as e:
  91         print(f"Couldn't get Sheet: {e}")
  92         traceback.print_exc()
  93         sys.exit(1)
  94
  95     print("Creating the Header")
  96
  97     insert_into_cell(1, 0, "JAN", sheet, "")
  98     insert_into_cell(2, 0, "FEB", sheet, "")
  99     insert_into_cell(3, 0, "MAR", sheet, "")
 100     insert_into_cell(4, 0, "APR", sheet, "")
 101     insert_into_cell(5, 0, "MAI", sheet, "")
 102     insert_into_cell(6, 0, "JUN", sheet, "")
 103     insert_into_cell(7, 0, "JUL", sheet, "")
 104     insert_into_cell(8, 0, "AUG", sheet, "")
 105     insert_into_cell(9, 0, "SEP", sheet, "")
 106     insert_into_cell(10, 0, "OCT", sheet, "")
 107     insert_into_cell(11, 0, "NOV", sheet, "")
 108     insert_into_cell(12, 0, "DEC", sheet, "")
 109     insert_into_cell(13, 0, "SUM", sheet, "")
 110
 111     print("Fill the lines")
 112
 113     insert_into_cell(0, 1, "Smith", sheet, "")
 114     insert_into_cell(1, 1, "42", sheet, "V")
 115     insert_into_cell(2, 1, "58.9", sheet, "V")
 116     insert_into_cell(3, 1, "-66.5", sheet, "V")
 117     insert_into_cell(4, 1, "43.4", sheet, "V")
 118     insert_into_cell(5, 1, "44.5", sheet, "V")
 119     insert_into_cell(6, 1, "45.3", sheet, "V")
 120     insert_into_cell(7, 1, "-67.3", sheet, "V")
 121     insert_into_cell(8, 1, "30.5", sheet, "V")
 122     insert_into_cell(9, 1, "23.2", sheet, "V")
 123     insert_into_cell(10, 1, "-97.3", sheet, "V")
 124     insert_into_cell(11, 1, "22.4", sheet, "V")
 125     insert_into_cell(12, 1, "23.5", sheet, "V")
 126     insert_into_cell(13, 1, "=SUM(B2:M2)", sheet, "")
 127
 128     insert_into_cell(0, 2, "Jones", sheet, "")
 129     insert_into_cell(1, 2, "21", sheet, "V")
 130     insert_into_cell(2, 2, "40.9", sheet, "V")
 131     insert_into_cell(3, 2, "-57.5", sheet, "V")
 132     insert_into_cell(4, 2, "-23.4", sheet, "V")
 133     insert_into_cell(5, 2, "34.5", sheet, "V")
 134     insert_into_cell(6, 2, "59.3", sheet, "V")
 135     insert_into_cell(7, 2, "27.3", sheet, "V")
 136     insert_into_cell(8, 2, "-38.5", sheet, "V")
 137     insert_into_cell(9, 2, "43.2", sheet, "V")
 138     insert_into_cell(10, 2, "57.3", sheet, "V")
 139     insert_into_cell(11, 2, "25.4", sheet, "V")
 140     insert_into_cell(12, 2, "28.5", sheet, "V")
 141     insert_into_cell(13, 2, "=SUM(B3:M3)", sheet, "")
 142
 143     insert_into_cell(0, 3, "Brown", sheet, "")
 144     insert_into_cell(1, 3, "31.45", sheet, "V")
 145     insert_into_cell(2, 3, "-20.9", sheet, "V")
 146     insert_into_cell(3, 3, "-117.5", sheet, "V")
 147     insert_into_cell(4, 3, "23.4", sheet, "V")
 148     insert_into_cell(5, 3, "-114.5", sheet, "V")
 149     insert_into_cell(6, 3, "115.3", sheet, "V")
 150     insert_into_cell(7, 3, "-171.3", sheet, "V")
 151     insert_into_cell(8, 3, "89.5", sheet, "V")
 152     insert_into_cell(9, 3, "41.2", sheet, "V")
 153     insert_into_cell(10, 3, "71.3", sheet, "V")
 154     insert_into_cell(11, 3, "25.4", sheet, "V")
 155     insert_into_cell(12, 3, "38.5", sheet, "V")
 156     insert_into_cell(13, 3, "=SUM(A4:L4)", sheet, "")
 157
 158     # oooooooooooooooooooooooooooStep 5oooooooooooooooooooooooooooooooooooooooooo
 159     # apply the created cell style.
 160     # For this purpose get the PropertySet of the Cell and change the
 161     # property CellStyle to the appropriate value.
 162
 163     change_backcolor(1, 0, 13, 0, "My Style", sheet)
 164     change_backcolor(0, 1, 0, 3, "My Style", sheet)
 165     change_backcolor(1, 1, 13, 3, "My Style2", sheet)
 166
 167     # oooooooooooooooooooooooooooStep 6oooooooooooooooooooooooooooooooooooooooooo
 168     # insert a 3D chart.
 169     # get the CellRange which holds the data for the chart and its RangeAddress
 170     # get the TableChartSupplier from the sheet and then the TableCharts from it.
 171     # add a new chart based on the data to the TableCharts.
 172     # get the ChartDocument, which provide the Diagram. Change the properties
 173     # Dim3D (3 dimension) and String (the title) of the diagram.
 174
 175     rect = Rectangle()
 176     rect.X, rect.Y, rect.Width, rect.Height = 500, 3000, 25000, 11000
 177
 178     print("Insert Chart")
 179     data_range = (sheet["A1:N4"].RangeAddress,)
 180     sheet.Charts.addNewByName("Example", rect, data_range, True, True)
 181
 182     # get the diagram and change some of the properties
 183     try:
 184         chart = sheet.Charts["Example"]
 185         # chart object implements XEmbeddedObjectSupplier interface
 186         diagram = chart.EmbeddedObject.Diagram
 187         print("Change Diagram to 3D")
 188         diagram.Dim3D = True
 189
 190         print("Change the title")
 191         time.sleep(.2)
 192         chart.EmbeddedObject.Title.String = "The new title"
 193     except Exception as e:
 194         print(f"Changing Properties failed: {e}", file=sys.stderr)
 195         traceback.print_exc()
 196
 197     print("done")
 198
 199
 200 def insert_into_cell(column: int, row: int, value: str, sheet, flag: str):
 201     try:
 202         cell = sheet[row, column]
 203     except IndexOutOfBoundsException:
 204         print("Could not get Cell", file=sys.stderr)
 205         traceback.print_exc()
 206     else:
 207         if flag == "V":
 208             cell.Value = float(value)
 209         else:
 210             cell.Formula = value
 211
 212
 213 def change_backcolor(left: int, top: int, right: int, bottom: int, template: str, sheet):
 214     try:
 215         cell_range = sheet[top:bottom + 1, left:right + 1]
 216         cell_range.CellStyle = template
 217     except IndexOutOfBoundsException:
 218         print("Could not get CellRange", file=sys.stderr)
 219         traceback.print_exc()
 220     except Exception as e:
 221         print(f"Can't change colors chgbColor: {e}", file=sys.stderr)
 222         traceback.print_exc()
 223
 224
 225 if __name__ == "__main__":
 226     main()
 227
 228
 229 # vim: set shiftwidth=4 softtabstop=4 expandtab: