Version 1.9 :
[macros-openoffice.git] / auf-poste-client-reflets / openoffice / CODA.py
1 #!
2 # -*- coding: utf-8 -*-
3 import re
4 import uno
5 import unohelper
6 from com.sun.star.awt import WindowDescriptor
7 from com.sun.star.awt.WindowClass import MODALTOP
8 from com.sun.star.awt.VclWindowPeerAttribute import OK, DEF_OK
9 from com.sun.star.uno import Exception as UnoException, RuntimeException
10 from com.sun.star.connection import NoConnectException
11 from com.sun.star.lang import Locale, IllegalArgumentException
12 from com.sun.star.beans import PropertyValue
13 #from com.sun.star.util.ParagraphProperties import CENTER
14 from com.sun.star.util.NumberFormat import CURRENCY, DATE
15 from com.sun.star.container import NoSuchElementException
16
17 ##############################################################################
18
19 def clipboard_data(ctx, mimetype='text/plain;charset=utf-16'):
20 clipboard = ctx.ServiceManager.createInstanceWithContext(
21 "com.sun.star.datatransfer.clipboard.SystemClipboard", ctx)
22 contents = clipboard.getContents()
23 #print "Contents:\n%s\n%s\n%s" % ("-" * 78, "* " + "\n* ".join(dir(contents)), "=" * 78)
24 #flavors = contents.getTransferDataFlavors()
25 #print "Flavors:\n%s\n%s\n%s" % ("-" * 78, "* " + "\n* ".join([flavor.MimeType for flavor in flavors]), "=" * 78)
26 found_flavor = None
27 for flavor in contents.getTransferDataFlavors():
28 if flavor.MimeType == mimetype:
29 found_flavor = flavor
30 break
31 if not found_flavor:
32 raise RuntimeError, u"Erreur : type de données '%s' non disponible.\n" \
33 u"\nAvez-vous bien sélectionné puis copié les données dans CODA ?" \
34 % mimetype
35 data = contents.getTransferData(found_flavor)
36 #print "Data:\n", "-" * 78, "\n", data
37 return data
38
39 ##############################################################################
40
41 def main(ctx, action):
42 data = clipboard_data(ctx)
43
44 numeric_pattern = re.compile('^[+-]?[0-9]+([\. ][0-9][0-9][0-9])*([\.,][0-9]+)?$')
45 date_pattern = re.compile('^([0-9]?[0-9])/([0-9]?[0-9])/([0-9]?[0-9]?[0-9]?[0-9])$')
46
47 if action == 'cumuls':
48 sheet_name = u'CODA-Cumuls'
49 # on découpe le texte reçu en lignes puis en colonnes
50 # on ne garde ici que les 10 premières colonnes
51 data = [row.split('\t')[:10] for row in data.splitlines()]
52 data[0][6] = '' # suppression de la 2nde colonne 'Engagement'
53 data[0][8] = '' # suppression de la colonne 'Colonne9'
54
55 elif action == 'details':
56 sheet_name = u'CODA-Détails'
57 # on découpe le texte reçu en lignes puis en colonnes
58 data = [row.split('\t') for row in data.splitlines()]
59
60 else:
61 raise RuntimeError, u"Action inconnue (erreur de programmation)."
62
63 # on efface la dernière ligne pleine de car. nuls, le cas échéant
64 last = data[len(data)-1]
65 if len(last) == 1 and last[0].strip('\x00') == '':
66 del data[len(data)-1]
67
68 # on détermine le type des données, par colonne
69 for column in range(len(data[0])):
70 if data[0][column] in ('Num doc','PCG'):
71 continue
72 flag_numeric = True
73 for row in range(1,len(data)):
74 value = data[row][column].strip().replace(u'\xa0','')
75 if value and value != '#DEV!' and not re.match(numeric_pattern, value):
76 flag_numeric = False
77 break
78 if flag_numeric:
79 for row in range(1,len(data)):
80 value = data[row][column].strip().replace(u'\xa0','')
81 if value and value != '#DEV!':
82 data[row][column] = float(value.replace('.','').replace(',','.'))
83
84 # on supprime les colonnes inutiles (en partant de la droite)
85 for column in range(len(data[0])-1,-1,-1):
86 diff = False
87 previous = data[1][column]
88 for row in range(2, len(data)):
89 if not diff and data[row][column] != previous:
90 diff = True
91 # s'il y a au moins 2 lignes et que les valeurs dans la colonne
92 # sont toutes identiques et qu'il n'y a pas d'intitulé sur la
93 # colonne alors on supprime toute la colonne
94 if len(data) > 2 and not diff and not data[0][column]:
95 for row in range(len(data)):
96 del data[row][column]
97
98 #print "Data:\n", "-" * 78, "\n", data
99
100 # récupération du document en cours, vérification du type Spreadsheet
101 desktop = ctx.ServiceManager.createInstanceWithContext(
102 "com.sun.star.frame.Desktop", ctx)
103 document = desktop.getCurrentComponent()
104 if not document.supportsService("com.sun.star.sheet.SpreadsheetDocument"):
105 raise RuntimeError, u"Ce n'est pas un document de type Spreadsheet (Calc)."
106
107 # basculement vers la feuille de calcul concernée, création au besoin
108 sheets = document.getSheets()
109 try:
110 sheet = sheets.getByName(sheet_name)
111 new_sheet = False
112 except NoSuchElementException, e:
113 sheets.insertNewByName(sheet_name, 0)
114 sheet = sheets.getByName(sheet_name)
115 new_sheet = True
116 controller = document.getCurrentController()
117 controller.setActiveSheet(sheet)
118
119 # récupération du format monétaire français
120 locale_fr_FR = Locale('fr', 'FR', '')
121 number_formats = document.getNumberFormats()
122 currency_format = number_formats.getStandardFormat(CURRENCY, locale_fr_FR)
123 date_format = number_formats.getStandardFormat(DATE, locale_fr_FR)
124
125 # définition de la zone de travail
126 cell_range = sheet.getCellRangeByPosition(1, 0, len(data[0]), len(data)-1)
127 cursor = sheet.createCursorByRange(cell_range)
128
129 # insertion des données
130 for row in range(1, len(data)):
131 for column in range(len(data[row])):
132 value = data[row][column]
133 cell = cursor.getCellByPosition(column, row)
134 if type(value) == float:
135 cell.Value = value
136 if new_sheet:
137 cell.NumberFormat = currency_format
138 else:
139 m = re.match(date_pattern, value)
140 if m:
141 (day, month, year) = [int(x) for x in m.groups()]
142 if year < 50:
143 year += 2000
144 elif year < 100:
145 year += 1900
146 value = '%02d/%02d/%04d' % (month, day, year)
147 if new_sheet:
148 cell.NumberFormat = date_format
149 cell.Formula = value
150 # insertion des en-têtes, après les données pour un autofit correct
151 for column in range(len(data[0])):
152 cell = cursor.getCellByPosition(column, 0)
153 cell.Formula = data[0][column]
154 if new_sheet:
155 cell.ParaAdjust = 3 # CENTER
156 sheet.Columns.getByIndex(1+column).OptimalWidth = True
157
158 ##############################################################################
159
160 # Show a message box with the UNO based toolkit
161 def messageBox(ctx, message):
162 document = XSCRIPTCONTEXT.getDocument()
163 window = document.CurrentController.Frame.ContainerWindow
164
165 aDescriptor = WindowDescriptor()
166 aDescriptor.Type = MODALTOP
167 aDescriptor.WindowServiceName = "infobox"
168 aDescriptor.ParentIndex = -1
169 aDescriptor.Parent = window
170 #aDescriptor.Bounds = Rectangle()
171 aDescriptor.WindowAttributes = OK
172
173 tk = window.getToolkit()
174 msgbox = tk.createWindow(aDescriptor)
175 msgbox.setCaptionText("Collage d'interrogation CODA")
176 msgbox.setMessageText(unicode(message))
177
178 return msgbox.execute()
179
180 def coller_cumuls(event=False):
181 u"""Coller une interrogation des cumuls CODA."""
182 ctx = uno.getComponentContext()
183 try:
184 main(ctx, 'cumuls')
185 except Exception, e:
186 messageBox(ctx, e)
187 return None
188
189 def coller_details(event=False):
190 u"""Coller une interrogation des détails CODA."""
191 ctx = uno.getComponentContext()
192 try:
193 main(ctx, 'details')
194 except Exception, e:
195 messageBox(ctx, e)
196 return None
197
198 g_exportedScripts = (coller_cumuls, coller_details)
199
200 ##############################################################################
201
202 from com.sun.star.task import XJobExecutor
203
204 class CollerCumulsJob(unohelper.Base, XJobExecutor):
205 def __init__(self, context):
206 self._context = context
207
208 def trigger(self, args):
209 try:
210 main(self._context, 'cumuls')
211 except Exception, e:
212 messageBox(self._context, e)
213
214 class CollerDetailsJob(unohelper.Base, XJobExecutor):
215 def __init__(self, context):
216 self._context = context
217
218 def trigger(self, args):
219 try:
220 main(self._context, 'details')
221 except Exception, e:
222 messageBox(self._context, e)
223
224 g_ImplementationHelper = unohelper.ImplementationHelper()
225 g_ImplementationHelper.addImplementation( \
226 CollerCumulsJob, "org.auf.openoffice.CODA.CollerCumuls", \
227 ("com.sun.star.task.Job",),)
228 g_ImplementationHelper.addImplementation( \
229 CollerDetailsJob, "org.auf.openoffice.CODA.CollerDetails", \
230 ("com.sun.star.task.Job",),)