1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
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:
|
