tdf#143123 Port some Spreadsheet Java examples to Python

Some examples inside odk/examples/java/Spreadsheet are ported to
Python:

ChartTypeChange.java    -> ChartTypeChange.py
EuroAdaption.java       -> EuroAdaption.py
SCalc.java              -> SCalc.py

Code format is checked with 'pycodestyle':

    pycodestyle --ignore=E501,E722 odk/examples/python/Spreadsheet/*.py

Signed-off-by: Chenxiong Qi <qcxhome@gmail.com>
Change-Id: If0631b5970faab6499cfea3eef559e003fad24d5
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/143810
Tested-by: Jenkins
Reviewed-by: Hossein <hossein@libreoffice.org>

4 files changed, 530 insertions, 7 deletions

diff --git a/odk/examples/examples.html b/odk/examples/examples.html
index 86589c3624a0..f50dffbc79e8 100644
--- a/odk/examples/examples.html
+++ b/odk/examples/examples.html
@@ -509,13 +509,46 @@
         </tr>
         <tr>
           <td>
-          <table class="table3">
-		  <tr>
-		  <td  colspan="3">
-		  <p>No Additional information</p>
-		  </td>
-		  </tr>
-		  </table>
+            <table class="table4">
+              <tbody>
+                <tr class="thead">
+                  <td class="cell20">Spreadsheet Document Examples</td>
+                  <td class="cell80">Description</td>
+                </tr>
+                <tr>
+                  <td class="cell20"><a href="python/Spreadsheet/" title="link to the source directory of the Python Spreadsheet examples">ChartTypeChange</a></td>
+                  <td class="cell80">This class loads a LibreOffice %PRODUCT_RELEASE% Calc document and changes the type of the embedded chart.</td>
+                </tr>
+                <tr>
+                  <td class="cell20"><a href="python/Spreadsheet/" title="link to the source directory of the Python Spreadsheet examples">EuroAdaption</a></td>
+                  <td class="cell80">The application connects to the office server and gets
+                    the <a href="%DOXYGEN_PREFIX1%/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1lang_1_1XMultiComponentFactory.html" title="link into the IDL reference to the interface com.sun.star.lang.XMultiComponentFactory"> multi component factory</a>, opens an empty Calc document, enters an
+                    example text, sets the number format to DM, changes the number format to
+                    EUR (Euro), and uses the DM/EUR factor on each cell with content.
+                  </td>
+                </tr>
+                <tr>
+                  <td class="cell20"><a href="python/Spreadsheet/" title="link to the source directory of the Python Spreadsheet examples">SCalc</a></td>
+                  <td class="cell80">The program connects to the office server and gets the
+                    <a href="%DOXYGEN_PREFIX1%/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1lang_1_1XMultiComponentFactory.html" title="link into the IDL reference to the interface com.sun.star.lang.XMultiComponentFactory"> multi component factory</a>. Then an empty calc document will be opened,
+                    cell styles will be created, some data will be inserted into the sheets,
+                    and the created cell styles will be applied. Finally, a 3D chart will be
+                    inserted.
+                  </td>
+                </tr>
+              </tbody>
+            </table>
+          </td>
+        </tr>
+        <tr>
+          <td>
+            <table class="table3">
+              <tr>
+                <td colspan="3">
+                 <p>No Additional information</p>
+                </td>
+              </tr>
+            </table>
           </td>
         </tr>
       </table>
diff --git a/odk/examples/python/Spreadsheet/ChartTypeChange.py b/odk/examples/python/Spreadsheet/ChartTypeChange.py
new file mode 100644
index 000000000000..6ee2c31d6766
--- /dev/null
+++ b/odk/examples/python/Spreadsheet/ChartTypeChange.py
@@ -0,0 +1,101 @@
+# -*- tab-width: 4; indent-tabs-mode: nil; py-indent-offset: 4 -*-
+#
+# This file is part of the LibreOffice project.
+#
+# This Source Code Form is subject to the terms of the Mozilla Public
+# 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 sys
+import time
+import traceback
+from typing import Union
+
+import officehelper
+from com.sun.star.awt import Rectangle
+
+
+def main():
+    try:
+        remote_context = officehelper.bootstrap()
+        srv_mgr = remote_context.getServiceManager()
+        desktop = srv_mgr.createInstanceWithContext(
+            "com.sun.star.frame.Desktop", remote_context
+        )
+        doc = desktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, tuple())
+        sheet = doc.Sheets[0]
+
+        cell_values = (
+            ("", "Jan", "Feb", "Mar", "Apr", "Mai"),
+            ("Profit", 12.3, 43.2, 5.1, 76, 56.8),
+            ("Rival in business", 12.2, 12.6, 17.7, 20.4, 100),
+        )
+
+        # Write the data into spreadsheet.
+        for row, row_data in enumerate(cell_values):
+            for column, cell_value in enumerate(row_data):
+                insert_into_cell(column, row, cell_value, sheet)
+
+        # Create a rectangle, which holds the size of the chart.
+        rect = Rectangle()
+        rect.X, rect.Y, rect.Width, rect.Height = 500, 3000, 25000, 11000
+
+        # Create the Unicode of the character for the column name.
+        char_rect = chr(65 + len(cell_values[0]) - 1)
+        # Get the cell range of the written values.
+        chart_cell_range = sheet[f"A1:{char_rect}{len(cell_values)}"]
+        # Create a table chart with all written values
+        chart_data_source = [chart_cell_range.RangeAddress]
+
+        sheet.Charts.addNewByName("Example", rect, chart_data_source, True, True)
+
+        # Get the newly created chart
+        table_chart = sheet.Charts["Example"]
+
+        # Change chart types one by one
+        chart_types = (
+            "com.sun.star.chart.LineDiagram",
+            "com.sun.star.chart.BarDiagram",
+            "com.sun.star.chart.PieDiagram",
+            "com.sun.star.chart.NetDiagram",
+            "com.sun.star.chart.XYDiagram",
+            "com.sun.star.chart.StockDiagram",
+            "com.sun.star.chart.AreaDiagram",
+        )
+        total = len(chart_types)
+
+        for i, type_name in enumerate(chart_types, start=1):
+            time.sleep(3)
+            print("Change chart type to:", f"[{i}/{total}]", type_name)
+            try:
+                chart_doc = table_chart.EmbeddedObject
+                chart_doc.Title.String = f"Chart Type: {type_name}"
+                diagram = chart_doc.createInstance(type_name)
+                diagram.Dim3D = False
+                chart_doc.Diagram = diagram
+            except Exception as e:
+                print(f"Fail to change chart type to {type_name}: {e}", file=sys.stderr)
+                traceback.print_exc()
+    except Exception as e:
+        print(f"Fail to change chart type: {e}", file=sys.stderr)
+        traceback.print_exc()
+
+
+def insert_into_cell(column: int, row: int, value: Union[str, float], sheet):
+    try:
+        cell = sheet[row, column]
+    except com.sun.star.lang.IndexOutOfBoundsException:
+        print("Could not get Cell", file=sys.stderr)
+        traceback.print_exc()
+    else:
+        if isinstance(value, str):
+            cell.String = value
+        else:
+            cell.Value = value
+
+
+if __name__ == "__main__":
+    main()
+
+# vim: set shiftwidth=4 softtabstop=4 expandtab:
diff --git a/odk/examples/python/Spreadsheet/EuroAdaption.py b/odk/examples/python/Spreadsheet/EuroAdaption.py
new file mode 100644
index 000000000000..dc3cbbfb47a3
--- /dev/null
+++ b/odk/examples/python/Spreadsheet/EuroAdaption.py
@@ -0,0 +1,164 @@
+# -*- tab-width: 4; indent-tabs-mode: nil; py-indent-offset: 4 -*-
+#
+# This file is part of the LibreOffice project.
+#
+# This Source Code Form is subject to the terms of the Mozilla Public
+# 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 sys
+import traceback
+
+import officehelper
+from com.sun.star.lang import Locale
+from com.sun.star.util import NumberFormat
+from com.sun.star.sheet import CellFlags
+
+
+def get_desktop():
+    desktop = None
+    try:
+        remote_context = officehelper.bootstrap()
+        srv_mgr = remote_context.getServiceManager()
+        if srv_mgr is None:
+            print("Can't create a desktop. No connection, no remote office servicemanager available!")
+        else:
+            desktop = srv_mgr.createInstanceWithContext("com.sun.star.frame.Desktop", remote_context)
+    except:
+        traceback.print_exc()
+        sys.exit(1)
+    return desktop
+
+
+def get_number_format_key(number_formats, format: str, language) -> int:
+    """Get number format key, create one if not exist yet.
+
+    :param number_formats: object managing number formats. It implements XNumberFormats interface.
+    :param str format: the number format.
+    :param language: language locale.
+    :type language: com.sun.star.lang.Locale
+    :return: key of the number format specific to the given language and format.
+    """
+    key = 0
+    try:
+        # Try to query the existing number format
+        if (key := number_formats.queryKey(format, language, True)) == -1:
+            # If not exist, create a new one
+            if (key := number_formats.addNew(format, language)) == -1:
+                key == 0
+    except:
+        traceback.print_exc()
+    return key
+
+
+def create_example_data(sheet, number_formats):
+    """Create example data with specific number format.
+
+    :param sheet: spreadsheet object. It implements XSpreadsheet interface.
+    :param number_formats: object managing number formats. It implements XNumberFormats interface.
+    """
+    try:
+        language = Locale()
+        language.Country = "de"  # Germany -> DM
+        language.Language = "de"  # German
+
+        # Numberformat string from DM
+        simple = "0 [$DM]"
+        number_format_key = get_number_format_key(number_formats, simple, language)
+
+        for counter in range(1, 10):
+            cell = sheet[1 + counter, 2]
+            cell.Value = float(counter * 2)
+            cell.NumberFormat = number_format_key
+            cell_range = sheet[counter + 1:counter + 2, 2:3]
+            cell_range.NumberFormat = number_format_key
+    except:
+        traceback.print_exc()
+
+
+def convert(sheet, number_formats, old_symbol: str, new_symbol: str, factor: float):
+    """Convert the currency
+
+    :param sheet: spreadsheet object. It implements XSpreadsheet interface.
+    :param number_formats: object managing number formats. It implements XNumberFormats interface.
+    :param str old_symbol: the old number format symbol.
+    :param str new_symbol: convert to this the new one.
+    :param float factor: conversion factor.
+    """
+    try:
+        language = Locale()
+        language.Country = "de"  # Germany -> DM
+        language.Language = "de"  # German
+
+        simple = f"0 [${new_symbol}]"
+        simple_key = get_number_format_key(number_formats, simple, language)
+
+        for cell_range in sheet.CellFormatRanges:
+            format = number_formats.getByKey(cell_range.NumberFormat)
+            format_type = format.Type
+            currency_symbol = format.CurrencySymbol
+            if format_type & NumberFormat.CURRENCY and currency_symbol == old_symbol:
+                thousands_sep = format.ThousandsSeparator
+                negative_red = format.NegativeRed
+                decimals = format.Decimals
+                leading_zeros = format.LeadingZeros
+                locale = format.Locale
+                # create a new numberformat string
+                new_number_format = number_formats.generateFormat(
+                    simple_key, locale, thousands_sep, negative_red, decimals, leading_zeros
+                )
+                # get the NumberKey from the numberformat
+                new_number_format_key = get_number_format_key(number_formats, new_number_format, locale)
+                # set the new numberformat to the cellrange DM->EUR
+                cell_range.NumberFormat = new_number_format_key
+
+                # iterate over all cells from the cellrange with a
+                # content and use the DM/EUR factor
+                sheet_cell_ranges = cell_range.queryContentCells(CellFlags.VALUE)
+                if sheet_cell_ranges.getCount() > 0:
+                    for cell in sheet_cell_ranges.getCells():
+                        cell.Value = cell.Value / factor
+    except:
+        traceback.print_exc()
+
+
+def main():
+    desktop = get_desktop()
+    if desktop is None:
+        return
+
+    try:
+        doc = desktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, tuple())
+        print("Create a new Spreadsheet")
+    except:
+        traceback.print_exc()
+        return
+
+    # the Action Interface provides methods to hide actions,
+    # like inserting data, on a sheet, that increase the performance
+    doc.addActionLock()
+
+    try:
+        sheet = doc.Sheets[0]
+    except:
+        traceback.print_exc()
+        return
+
+    # insert some example data in a sheet
+    create_example_data(sheet, doc.NumberFormats)
+    print("Insert example data and use the number format with the currency 'DM'")
+
+    # Change the currency from the cells from DM to Euro
+    convert(sheet, doc.NumberFormats, "DM", "EUR", 1.95583)
+    print("Change the number format to EUR and divide the values with the factor 1.95583")
+
+    # remove all locks, the user see all changes
+    doc.removeActionLock()
+    print("done")
+
+
+if __name__ == "__main__":
+    main()
+
+# vim: set shiftwidth=4 softtabstop=4 expandtab:
diff --git a/odk/examples/python/Spreadsheet/SCalc.py b/odk/examples/python/Spreadsheet/SCalc.py
new file mode 100644
index 000000000000..c016b96ef12d
--- /dev/null
+++ b/odk/examples/python/Spreadsheet/SCalc.py
@@ -0,0 +1,225 @@
+# -*- tab-width: 4; indent-tabs-mode: nil; py-indent-offset: 4 -*-
+#
+# This file is part of the LibreOffice project.
+#
+# This Source Code Form is subject to the terms of the Mozilla Public
+# 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 sys
+import time
+import traceback
+
+import officehelper
+from com.sun.star.awt import Rectangle
+
+"""
+Step 1: get the remote component context from the office
+Step 2: open an empty calc document
+Step 3: create cell styles
+Step 4: get the sheet an insert some data
+Step 5: apply the created cell styles
+Step 6: insert a 3D Chart
+"""
+
+
+def main():
+    # oooooooooooooooooooooooooooStep 1oooooooooooooooooooooooooooooooooooooooooo
+    # call UNO bootstrap method and get the remote component context form
+    # the a running office (office will be started if necessary)
+    try:
+        remote_context = officehelper.bootstrap()
+        print("Connected to a running office ...")
+        srv_mgr = remote_context.getServiceManager()
+        desktop = srv_mgr.createInstanceWithContext(
+            "com.sun.star.frame.Desktop", remote_context
+        )
+    except:
+        traceback.print_exc()
+        sys.exit(1)
+
+    # oooooooooooooooooooooooooooStep 2oooooooooooooooooooooooooooooooooooooooooo
+    # open an empty document. In this case it's a calc document.
+    # For this purpose an instance of com.sun.star.frame.Desktop
+    # is created. The desktop provides the XComponentLoader interface,
+    # which is used to open the document via loadComponentFromURL
+    print("Opening an empty Calc document")
+    doc_url = "private:factory/scalc"
+    try:
+        doc = desktop.loadComponentFromURL(doc_url, "_blank", 0, tuple())
+    except:
+        traceback.print_exc()
+        return
+
+    # oooooooooooooooooooooooooooStep 3oooooooooooooooooooooooooooooooooooooooooo
+    # create cell styles.
+    # For this purpose get the StyleFamiliesSupplier and the family CellStyle.
+    # Create an instance of com.sun.star.style.CellStyle and add it to the family.
+    # Now change some properties
+
+    try:
+        cell_styles = doc.StyleFamilies["CellStyles"]
+        cell_style = doc.createInstance("com.sun.star.style.CellStyle")
+        cell_styles["My Style"] = cell_style
+        cell_style.IsCellBackgroundTransparent = False
+        cell_style.CellBackColor = 6710932
+        cell_style.CharColor = 16777215
+        cell_style = doc.createInstance("com.sun.star.style.CellStyle")
+        cell_styles["My Style2"] = cell_style
+        cell_style.IsCellBackgroundTransparent = False
+        cell_style.CellBackColor = 13421823
+    except:
+        traceback.print_exc()
+
+    # oooooooooooooooooooooooooooStep 4oooooooooooooooooooooooooooooooooooooooooo
+    # get the sheet an insert some data.
+    # Get the sheets from the document and then the first from this container.
+    # Now some data can be inserted. For this purpose get a Cell via
+    # getCellByPosition and insert into this cell via setValue() (for floats)
+    # or setFormula() for formulas and Strings.
+    # As a Python example, those calls are made in equivalent Pythonic ways.
+
+    print("Getting spreadsheet")
+    try:
+        sheet = doc.Sheets[0]
+    except Exception as e:
+        print(f"Couldn't get Sheet: {e}")
+        traceback.print_exc()
+        sys.exit(1)
+
+    print("Creating the Header")
+
+    insert_into_cell(1, 0, "JAN", sheet, "")
+    insert_into_cell(2, 0, "FEB", sheet, "")
+    insert_into_cell(3, 0, "MAR", sheet, "")
+    insert_into_cell(4, 0, "APR", sheet, "")
+    insert_into_cell(5, 0, "MAI", sheet, "")
+    insert_into_cell(6, 0, "JUN", sheet, "")
+    insert_into_cell(7, 0, "JUL", sheet, "")
+    insert_into_cell(8, 0, "AUG", sheet, "")
+    insert_into_cell(9, 0, "SEP", sheet, "")
+    insert_into_cell(10, 0, "OCT", sheet, "")
+    insert_into_cell(11, 0, "NOV", sheet, "")
+    insert_into_cell(12, 0, "DEC", sheet, "")
+    insert_into_cell(13, 0, "SUM", sheet, "")
+
+    print("Fill the lines")
+
+    insert_into_cell(0, 1, "Smith", sheet, "")
+    insert_into_cell(1, 1, "42", sheet, "V")
+    insert_into_cell(2, 1, "58.9", sheet, "V")
+    insert_into_cell(3, 1, "-66.5", sheet, "V")
+    insert_into_cell(4, 1, "43.4", sheet, "V")
+    insert_into_cell(5, 1, "44.5", sheet, "V")
+    insert_into_cell(6, 1, "45.3", sheet, "V")
+    insert_into_cell(7, 1, "-67.3", sheet, "V")
+    insert_into_cell(8, 1, "30.5", sheet, "V")
+    insert_into_cell(9, 1, "23.2", sheet, "V")
+    insert_into_cell(10, 1, "-97.3", sheet, "V")
+    insert_into_cell(11, 1, "22.4", sheet, "V")
+    insert_into_cell(12, 1, "23.5", sheet, "V")
+    insert_into_cell(13, 1, "=SUM(B2:M2)", sheet, "")
+
+    insert_into_cell(0, 2, "Jones", sheet, "")
+    insert_into_cell(1, 2, "21", sheet, "V")
+    insert_into_cell(2, 2, "40.9", sheet, "V")
+    insert_into_cell(3, 2, "-57.5", sheet, "V")
+    insert_into_cell(4, 2, "-23.4", sheet, "V")
+    insert_into_cell(5, 2, "34.5", sheet, "V")
+    insert_into_cell(6, 2, "59.3", sheet, "V")
+    insert_into_cell(7, 2, "27.3", sheet, "V")
+    insert_into_cell(8, 2, "-38.5", sheet, "V")
+    insert_into_cell(9, 2, "43.2", sheet, "V")
+    insert_into_cell(10, 2, "57.3", sheet, "V")
+    insert_into_cell(11, 2, "25.4", sheet, "V")
+    insert_into_cell(12, 2, "28.5", sheet, "V")
+    insert_into_cell(13, 2, "=SUM(B3:M3)", sheet, "")
+
+    insert_into_cell(0, 3, "Brown", sheet, "")
+    insert_into_cell(1, 3, "31.45", sheet, "V")
+    insert_into_cell(2, 3, "-20.9", sheet, "V")
+    insert_into_cell(3, 3, "-117.5", sheet, "V")
+    insert_into_cell(4, 3, "23.4", sheet, "V")
+    insert_into_cell(5, 3, "-114.5", sheet, "V")
+    insert_into_cell(6, 3, "115.3", sheet, "V")
+    insert_into_cell(7, 3, "-171.3", sheet, "V")
+    insert_into_cell(8, 3, "89.5", sheet, "V")
+    insert_into_cell(9, 3, "41.2", sheet, "V")
+    insert_into_cell(10, 3, "71.3", sheet, "V")
+    insert_into_cell(11, 3, "25.4", sheet, "V")
+    insert_into_cell(12, 3, "38.5", sheet, "V")
+    insert_into_cell(13, 3, "=SUM(A4:L4)", sheet, "")
+
+    # oooooooooooooooooooooooooooStep 5oooooooooooooooooooooooooooooooooooooooooo
+    # apply the created cell style.
+    # For this purpose get the PropertySet of the Cell and change the
+    # property CellStyle to the appropriate value.
+
+    change_backcolor(1, 0, 13, 0, "My Style", sheet)
+    change_backcolor(0, 1, 0, 3, "My Style", sheet)
+    change_backcolor(1, 1, 13, 3, "My Style2", sheet)
+
+    # oooooooooooooooooooooooooooStep 6oooooooooooooooooooooooooooooooooooooooooo
+    # insert a 3D chart.
+    # get the CellRange which holds the data for the chart and its RangeAddress
+    # get the TableChartSupplier from the sheet and then the TableCharts from it.
+    # add a new chart based on the data to the TableCharts.
+    # get the ChartDocument, which provide the Diagram. Change the properties
+    # Dim3D (3 dimension) and String (the title) of the diagram.
+
+    rect = Rectangle()
+    rect.X, rect.Y, rect.Width, rect.Height = 500, 3000, 25000, 11000
+
+    print("Insert Chart")
+    data_range = (sheet["A1:N4"].RangeAddress,)
+    sheet.Charts.addNewByName("Example", rect, data_range, True, True)
+
+    # get the diagram and change some of the properties
+    try:
+        chart = sheet.Charts["Example"]
+        # chart object implements XEmbeddedObjectSupplier interface
+        diagram = chart.EmbeddedObject.Diagram
+        print("Change Diagram to 3D")
+        diagram.Dim3D = True
+
+        print("Change the title")
+        time.sleep(.2)
+        chart.EmbeddedObject.Title.String = "The new title"
+    except Exception as e:
+        print(f"Changing Properties failed: {e}", file=sys.stderr)
+        traceback.print_exc()
+
+    print("done")
+
+
+def insert_into_cell(column: int, row: int, value: str, sheet, flag: str):
+    try:
+        cell = sheet[row, column]
+    except com.sun.star.lang.IndexOutOfBoundsException:
+        print("Could not get Cell", file=sys.stderr)
+        traceback.print_exc()
+    else:
+        if flag == "V":
+            cell.Value = float(value)
+        else:
+            cell.Formula = value
+
+
+def change_backcolor(left: int, top: int, right: int, bottom: int, template: str, sheet):
+    try:
+        cell_range = sheet[top:bottom + 1, left:right + 1]
+        cell_range.CellStyle = template
+    except com.sun.star.lang.IndexOutOfBoundsException:
+        print("Could not get CellRange", file=sys.stderr)
+        traceback.print_exc()
+    except Exception as e:
+        print(f"Can't change colors chgbColor: {e}", file=sys.stderr)
+        traceback.print_exc()
+
+
+if __name__ == "__main__":
+    main()
+
+
+# vim: set shiftwidth=4 softtabstop=4 expandtab: