odk/examples/python/Spreadsheet/EuroAdaption.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 traceback
  12
  13 import officehelper
  14 from com.sun.star.lang import Locale
  15 from com.sun.star.util import NumberFormat
  16 from com.sun.star.sheet import CellFlags
  17
  18
  19 def get_desktop():
  20     desktop = None
  21     try:
  22         remote_context = officehelper.bootstrap()
  23         srv_mgr = remote_context.getServiceManager()
  24         if srv_mgr is None:
  25             print("Can't create a desktop. No connection, no remote office servicemanager available!")
  26         else:
  27             desktop = srv_mgr.createInstanceWithContext("com.sun.star.frame.Desktop", remote_context)
  28     except Exception as e:
  29         print(f"Failed to get desktop: {e}")
  30         traceback.print_exc()
  31         sys.exit(1)
  32     return desktop
  33
  34
  35 def get_number_format_key(number_formats, format: str, language) -> int:
  36     """Get number format key, create one if not exist yet.
  37
  38     :param number_formats: object managing number formats. It implements XNumberFormats interface.
  39     :param str format: the number format.
  40     :param language: language locale.
  41     :type language: com.sun.star.lang.Locale
  42     :return: key of the number format specific to the given language and format.
  43     """
  44     key = 0
  45     try:
  46         # Try to query the existing number format
  47         if (key := number_formats.queryKey(format, language, True)) == -1:
  48             # If not exist, create a new one
  49             if (key := number_formats.addNew(format, language)) == -1:
  50                 key == 0
  51     except Exception as e:
  52         print(f"Failed to get key: {e}")
  53         traceback.print_exc()
  54     return key
  55
  56
  57 def create_example_data(sheet, number_formats):
  58     """Create example data with specific number format.
  59
  60     :param sheet: spreadsheet object. It implements XSpreadsheet interface.
  61     :param number_formats: object managing number formats. It implements XNumberFormats interface.
  62     """
  63     try:
  64         language = Locale()
  65         language.Country = "de"  # Germany -> DM
  66         language.Language = "de"  # German
  67
  68         # Numberformat string from DM
  69         simple = "0 [$DM]"
  70         number_format_key = get_number_format_key(number_formats, simple, language)
  71
  72         for counter in range(1, 10):
  73             cell = sheet[1 + counter, 2]
  74             cell.Value = float(counter * 2)
  75             cell.NumberFormat = number_format_key
  76             cell_range = sheet[counter + 1:counter + 2, 2:3]
  77             cell_range.NumberFormat = number_format_key
  78     except Exception as e:
  79         print(f"Failed to create example data: {e}")
  80         traceback.print_exc()
  81
  82
  83 def convert(sheet, number_formats, old_symbol: str, new_symbol: str, factor: float):
  84     """Convert the currency
  85
  86     :param sheet: spreadsheet object. It implements XSpreadsheet interface.
  87     :param number_formats: object managing number formats. It implements XNumberFormats interface.
  88     :param str old_symbol: the old number format symbol.
  89     :param str new_symbol: convert to this the new one.
  90     :param float factor: conversion factor.
  91     """
  92     try:
  93         language = Locale()
  94         language.Country = "de"  # Germany -> DM
  95         language.Language = "de"  # German
  96
  97         simple = f"0 [${new_symbol}]"
  98         simple_key = get_number_format_key(number_formats, simple, language)
  99
 100         for cell_range in sheet.CellFormatRanges:
 101             format = number_formats.getByKey(cell_range.NumberFormat)
 102             format_type = format.Type
 103             currency_symbol = format.CurrencySymbol
 104             if format_type & NumberFormat.CURRENCY and currency_symbol == old_symbol:
 105                 thousands_sep = format.ThousandsSeparator
 106                 negative_red = format.NegativeRed
 107                 decimals = format.Decimals
 108                 leading_zeros = format.LeadingZeros
 109                 locale = format.Locale
 110                 # create a new numberformat string
 111                 new_number_format = number_formats.generateFormat(
 112                     simple_key, locale, thousands_sep, negative_red, decimals, leading_zeros
 113                 )
 114                 # get the NumberKey from the numberformat
 115                 new_number_format_key = get_number_format_key(number_formats, new_number_format, locale)
 116                 # set the new numberformat to the cellrange DM->EUR
 117                 cell_range.NumberFormat = new_number_format_key
 118
 119                 # iterate over all cells from the cellrange with a
 120                 # content and use the DM/EUR factor
 121                 sheet_cell_ranges = cell_range.queryContentCells(CellFlags.VALUE)
 122                 if sheet_cell_ranges.getCount() > 0:
 123                     for cell in sheet_cell_ranges.getCells():
 124                         cell.Value = cell.Value / factor
 125     except Exception as e:
 126         print(f"Failed to convert currency: {e}")
 127         traceback.print_exc()
 128
 129
 130 def main():
 131     desktop = get_desktop()
 132     if desktop is None:
 133         return
 134
 135     try:
 136         doc = desktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, tuple())
 137         print("Create a new Spreadsheet")
 138     except Exception as e:
 139         print(f"Failed to load component from URL: {e}")
 140         traceback.print_exc()
 141         return
 142
 143     # the Action Interface provides methods to hide actions,
 144     # like inserting data, on a sheet, that increase the performance
 145     doc.addActionLock()
 146
 147     try:
 148         sheet = doc.Sheets[0]
 149     except Exception as e:
 150         print(f"Failed to get sheet: {e}")
 151         traceback.print_exc()
 152         return
 153
 154     # insert some example data in a sheet
 155     create_example_data(sheet, doc.NumberFormats)
 156     print("Insert example data and use the number format with the currency 'DM'")
 157
 158     # Change the currency from the cells from DM to Euro
 159     convert(sheet, doc.NumberFormats, "DM", "EUR", 1.95583)
 160     print("Change the number format to EUR and divide the values with the factor 1.95583")
 161
 162     # remove all locks, the user see all changes
 163     doc.removeActionLock()
 164     print("done")
 165
 166
 167 if __name__ == "__main__":
 168     main()
 169
 170 # vim: set shiftwidth=4 softtabstop=4 expandtab: