From 9753aa4a776af24c1bfb9cd41867ee2078438934 Mon Sep 17 00:00:00 2001 From: Chenxiong Qi Date: Thu, 8 Dec 2022 18:47:13 +0800 Subject: 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 Change-Id: If0631b5970faab6499cfea3eef559e003fad24d5 Reviewed-on: https://gerrit.libreoffice.org/c/core/+/143810 Tested-by: Jenkins Reviewed-by: Hossein --- odk/examples/examples.html | 47 ++++- odk/examples/python/Spreadsheet/ChartTypeChange.py | 101 +++++++++ odk/examples/python/Spreadsheet/EuroAdaption.py | 164 +++++++++++++++ odk/examples/python/Spreadsheet/SCalc.py | 225 +++++++++++++++++++++ 4 files changed, 530 insertions(+), 7 deletions(-) create mode 100644 odk/examples/python/Spreadsheet/ChartTypeChange.py create mode 100644 odk/examples/python/Spreadsheet/EuroAdaption.py create mode 100644 odk/examples/python/Spreadsheet/SCalc.py (limited to 'odk') 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 @@ - - - - -
-

No Additional information

-
+ + + + + + + + + + + + + + + + + + + +
Spreadsheet Document ExamplesDescription
ChartTypeChangeThis class loads a LibreOffice %PRODUCT_RELEASE% Calc document and changes the type of the embedded chart.
EuroAdaptionThe application connects to the office server and gets + the multi component factory, 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. +
SCalcThe program connects to the office server and gets the + multi component factory. 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. +
+ + + + + + + + +
+

No Additional information

+
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: -- cgit v1.2.3