# -*- coding: utf-8 -*- """/**************************************************************************** Copyright (c) 2007- 2009 Norman Bakker This library is free software; you can redistribute it and/or modify it under the terms of the GNU Library General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Library General Public License for more details. You should have received a copy of the GNU Library General Public License along with this library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA. ****************************************************************************/""" # -*- coding: utf-8 -*- import uno import unohelper #for WIN32 #import sys #sys.path.append('C:\\Python26\\Lib\\site-packages') #end for WIN32 #for LINUX #import sys #sys.path.append('.') #import transfer from math import * import itertools from com.sun.star.task import XJobExecutor #based on the initial example provided by: #http://www.linuxjournal.com/content/python-pyuno-hello-world-addon-openoffice 20090206 Mitch Frazier # # Implement a UNO component by deriving from the standard # unohelper.Base class and from the interface(s) you want to implement. from string import ascii_letters, ascii_uppercase, digits, upper import cPickle class Ant(): #trial of inclusion of another class for transferdemoJob to inherit def __init__(self) : pass def isAlpha(self,x) : """returns True if first character is alph a-z;A-Z False otherwise. - using str.isalpha() builtin gives an error spliced list elements """ #trap a '' as this would return True otherwise if x[:1] == '' : return False return x[:1] in ascii_letters def isNum(self,x) : """returns True if first character is a digit (0..9) False otherwise. """ #trap a '' as this would return True otherwise if x[:1] == '' : return False return x[:1] in digits def zeroNullsInList(self,x) : """replaces a '' in list x with a 0""" for i,y in enumerate(x): if not y : x[i] = 0 def sum_list(self,ll) : """sums the elements in a list, replacing null entries with 0""" total = 0 for n in range(0,len(ll)) : if ll[n] : total += ll[n] return total def cumsum(self,x): """return the cumulative sums of x in a list where first entry by definition is zero l[0] = 0""" #faster than return [sum(x[:i+1] for i in len(x)] by factor of 2 for 10 element list but see c++ s=0 l=[] for i in x: if i == '' : i = 0 l.append(s) s += i return l def erat2(self,n): """generates n number of primes starting with 2""" #from python cookbook o'reilly 2nd ed: p670 D = {} o = [2] c = 1 if n == 1 : return o #yield 2 for q in itertools.islice(itertools.count(3),0,None,2) : p = D.pop(q, None) if p is None: c += 1 D[q*q] = q if c > n : return o o.append(q) #yield q else: x = p + q while x in D or not (x&1): x += p D[x] = p def primes_wang(self,n): """generate primes between 2 and n""" #http://code.activestate.com/recipes/366178/ wensheng wang fastest on athlon if n==2: return [2] elif n<2: return [] s=range(3,n+1,2) mroot = n ** 0.5 half=(n+1)/2-1 i=0 m=3 while m <= mroot: if s[i]: j=(m*m-3)/2 s[j]=0 while j 1 : xin = x[istart] xout = x[iend] delx = xout - xin y_total = cumy[iend]-cumy[istart] for j in range(istart+1,iend): if y_total : x[j] = xin + delx * (cumy[j]-cumy[istart])/ y_total else : x[j] = x[j-1] istart = iend def strip_allold(self,x, char= ' ') : """Strip all char (default blanks) fr tuple x""" return ''.join(str(x).split(char)) def strip_all(self,x) : """Strip all blanks fr list x; can handle non string elements in x """ y=[] for row in x : y.append(''.join(str(row).split())) return y def sortedLofL(self,l_of_l,col, reverse = False) : """sort a list of lists according to the contents of column col, default is ascending order""" try: return sorted(l_of_l,key = lambda x: x[col]) except IndexError: print 'sortedLofL error: col index outside of range of list of list columns' return [] def convert_Seq_to_Seq_of_Tuples_rows(self,tt,cols) : """convert a sequence to a sequence of tuples filling across the rows each containing len(tt)/cols elements - eg to convert tt to a tuple of tuples supply argument as a tuple to convert tt to a list of tuples supply argument as a list""" rows = len(tt) /cols z = ['']*rows for n in range(0,rows) : s = n * cols z[n] = tt[s : s + cols] return tuple(z) def convert_List_of_Lists_to_Tuple_of_Tuples(self,ll) : """convert a list of lists to a tuple of tuples""" return tuple([tuple(ll[n]) for n in range(0,len(ll))]) def putCell(self, addr, message): """show the message at cell ref provided (by addr)""" cells = self.sheet.getCellRangeByName(addr) cells.String = message def putCellVal(self, addr, val): """show the message at cell ref provided (by addr)""" cells = self.sheet.getCellRangeByName(addr) cells.Value = val def getCell(self, addr): """gets the contents of cell ref provided (by addr)""" cells = self.sheet.getCellRangeByName(addr) return cells.String def getCellVal(self, addr): """gets the contents of cell ref provided (by addr)""" cells = self.sheet.getCellRangeByName(addr) return cells.Value def putCellPos(self, col, row, message): """show the message at cell ref provided (by column and row numbers)""" cells = self.sheet.getCellByPosition(col,row) cells.String = message def putCellPosVal(self, col,row,val): """gets the contents of cell ref provided (by column and row numbers)""" cells = self.sheet.getCellByPosition(col,row) cells.Value = val def getCellPos(self, col,row): """gets the contents of cell ref provided (by column and row numbers)""" cells = self.sheet.getCellByPosition(col,row) return cells.String def getCellPosVal(self, col,row): """gets the contents of cell ref provided (by column and row numbers)""" cells = self.sheet.getCellByPosition(col,row) return cells.Value def getSelectedRange(self): """returns tuple of startrow, col and endrow, col of selected range on sheets as 0 origin numbers""" ref = self.spreadsheet.CurrentSelection.getRangeAddress() return ref.StartColumn,ref.StartRow,ref.EndColumn,ref.EndRow def getLastActiveCell(self): """returns the last used column and row of the current sheet (ie the last cell in the range containing something other than '')""" #create a cursor for the whole sheet using OO interface XSheetCellRange cursor = self.sheet.createCursor() cursor.gotoEndOfUsedArea(True) address = cursor.RangeAddress endcol = address.EndColumn endrow = address.EndRow return endcol,endrow def intToColLabel(self,n) : """converts integer column index to alpha label up to 18277 = 26*26*26 + 26*26+25 ('ZZZ') - after that returns str(n) """ #messy! if n <= 25 : return ascii_uppercase[n] elif n <= 701: return ascii_uppercase[(n-1)/26 -1] + ascii_uppercase[n % 26] elif n <= 18277: f = n /677 -1 g = (n - (f+1)*676)/26 -1 return ascii_uppercase[f] + ascii_uppercase[g] + ascii_uppercase[n % 26] else : return str(n) def colLabeltoInt(self,label) : """converts columlabel to int up to ZZZ (= 18277)""" #messy! au = list(ascii_uppercase) ul = upper(label) l = len(ul) if l == 1 : return au.index(ul) elif l == 2: return (1+au.index(ul[0]))*26 + au.index(ul[1]) elif l == 3: return ((1+au.index(ul[0])))*676 + (1+au.index(ul[1]))*26 + au.index(ul[2]) else : return ul def ClearSelectedArea(self): """clears selected sheet area by filling with tot full of '' """ print 'clearing selection of data' c0,r0,c1,r1 = self.getSelectedRange() oCellRange = self.sheet.getCellRangeByPosition(c0,r0,c1,r1) #create a tuple of tuples of the same size and shape as oCellRange oCellRange.setDataArray((('',)*(1+c1-c0),)* (1+r1-r0)) self.putCell(self.msgcell,'clear selection of data') class transferdemoJob(unohelper.Base, XJobExecutor, Ant): # # class globals specified here #cell name indices A_,B_,C_,D_,E_,F_,G_,H_,I_,J_,K_,L_,M_,N_,O_,P_,Q_,R_,S_,T_,U_,V_,W_,X_,Y_,Z_ = range(0,26) C0 = A_ # use this to setup a work area R0 = 5 # ditto lotcol = '' #list of tuples : array of totrow data rearranged by columns totrow = '' #tuple of tuple data as read in by getDataFromFileArraY() : row arrays #these can be converted each to the other using : # self.totrow = tuple(zip(*self.lotcol)) # or self.lotcol = zip(*self.totrow) msgcell = 'a1' #data variables if any def __init__(self, ctx): #initiate various actions common to all the following members # Retrieve the desktop object self.desktop = ctx.ServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", ctx) # Get the current spreadsheet. self.spreadsheet = self.desktop.getCurrentComponent() # Get the collection of sheets in the spreadsheet. self.sheets = self.spreadsheet.getSheets() #get a specific sheet (the first in this case, it could be any) self.sheet = self.sheets.getByIndex(0) # include Ant class ,and provide the cell position of any message Ant.__init__(self) #============================================================================================== def trigger(self, args): #trigger acts as a main function, and #other functions can be called from within its scope (see examples below) #haven't worked out how to pass an argument on calling the Add-on #get run instructions from cell 'I1' by inserting the INDEX shown below """ For testing INDEX = 0 get data from file 1 save sheet data to file 2 update internal arrays 3 fill cells with numbers 5 interpolate / fill data on sheet 6 print all in selected range 7 clear a selected area on the sheet 8 multiply the range by a number in j1 20 """ #determine which program to run runno = self.getCellVal('i1') print "Run no read from i1", runno #get data file details from sheet cell B30,B31 in case needed path,filename = self.getCell('b2'),self.getCell('b3') fullfilename = ''.join([path,filename]) print '\nfile path and name',fullfilename print '\nrun no read in from sheet row', runno #do something based on index in cell i1 if runno == 0 : #used for initial testing #self.putCell(self.msgcell,''.join(['started transferdemo_oocalc - args : '])) self.putCell(self.msgcell,'testing') self.fillRange_with_primes(0) elif runno == 1: #read in totrow data from file in path and name shown on sheet #to internal array tuple of tuples in row format : totrow #any exception in getDataFromFile stops further execution self.totrow = self.getDataFromFile(path+filename) endcol = len(self.totrow[0])-1 endrow = len(self.totrow)-1 self.putCell(self.msgcell,''.join(['read ',str(len(self.totrow)),'data lines'])) print 'read ',len(self.totrow),'data lines' #update sheet as well #place cursor on sheet to select starting cell to write data to (minimum is self.C0,self.R0) c0,r0,c1,r1 = self.getSelectedRange() if c0 < self.C0 : c0 = self.C0 if r0 < self.R0 : r0 = self.R0 self.UpdateSheet(c0,r0,c0+endcol,r0+endrow) elif runno == 2: #save data as specified in workspace area (starting at A_,R0) on sheet to file with path+filename endcol,endrow = self.getLastActiveCell() c0,r0,c1,r1 = self.C0,self.R0,endcol,endrow self.transferDataRange_to_totrow(0,0,c1,r1) starting_at_cell = ''.join([ascii_uppercase[c0],str(r0+1)]) self.saveDataToFile(fullfilename,self.totrow) self.putCell(self.msgcell,''.join(['saved ',str(c1-c0+1),' cols & ',str(r1-r0+1),' rows starting at ', starting_at_cell,' to file : ',fullfilename])) elif runno == 3: endcol,endrow = self.getLastActiveCell() print 'last active col,row', endcol,endrow self.putCell(self.msgcell,''.join(['last active cell : ',self.intToColLabel(endcol),str(endrow+1)])) elif runno == 4: pass elif runno == 5: #fill cells selected with their index numbers (assuming counting across the rows) self.fillcells(0, option = 1, startvalue = 0, step = 1) elif runno == 6 : #interpolate data to fill in any empty cell in the rows and columns in the selected rectangular range #leaves existing data in place and starts with first and last columns first - then fills in rows #any empty border cells c0,r0; c0,r1; c1,r0; c1,r1 are assumed to contain 0 c0,r0,c1,r1 = self.getSelectedRange() #now get selected rectangle and interpolate/fill it self.transferDataRange_to_totrow_lotcol(c0,r0,c1,r1) #create a dummy intervals tuple for strict linear interpolation (can change here) intervals = (1,) * (1+r1-r0) #provide first column as a list to linInterpolation- for this we need lotcol cc = list(self.lotcol[0]) #use python 'transfer' extension function linInterpolation self.linInterpolation(cc,intervals) #show this first column on the sheet (convert cc to a tuple first)] self.transferATupleToASheetColumn(tuple(cc), c0 , r0) #provide last column as a list to linInterpolation cc = list(self.lotcol[c1-c0]) self.linInterpolation(cc,intervals) #show this on the sheet (convert cc to a tuple first)] self.transferATupleToASheetColumn(tuple(cc), c1 , r0) #now interpolate across the columns by row self.transferDataRange_to_totrow(c0,r0,c1,r1) #for this we can make do with totrow #create a dummy intervals tuple for strict linear interpolation (free to change here) intervals = (1,) * (1+c1-c0) for n in range(0,r1-r0+1): #need to provide current row as a list to linInterpolation cr = list(self.totrow[n]) self.linInterpolation(cr,intervals) #next function needs data as a tuple self.transferATupleToASheetRow(tuple(cr),c0,r0+n) self.putCell(self.msgcell,'Range interpolated data done') elif runno == 7 : #set range c0,r0,c1,r1 = self.getSelectedRange() #get range into totrow self.transferDataRange_to_totrow(c0,r0,c1,r1) #print range self.printArray(self.totrow) self.putCell(self.msgcell,''.join(['Numbers from spreadsheet in range ',self.intToColLabel(c0),str(r0+1), \ ' to ',self.intToColLabel(c1),str(r1+1)])) elif runno == 8: self.ClearSelectedArea() elif runno == 9: pass elif runno == 10 : pass elif runno == 20: #multiply cell range selected by a constant factor; default 2 c0,r0,c1,r1 = self.getSelectedRange() #read range into lotcol self.transferDataRange_to_totrow(c0,r0,c1,r1) #read factor from column j1 (or default to 2) factor = self.getCellVal('j1') if factor == 0 : factor = 2 print 'factor', factor for n in range(0,r1-r0+1): #as this will be modified in place need a list sequence cc = list(self.totrow[n]) ll = [cc[i] * factor for i in range(0,len(cc))] self.transferATupleToASheetRow(tuple(ll), c0 , r0 + n) else : print self.getSelectedRange() print 'done' def printArray(self,ray) : """ Prints the totrow array on the console """ print print 'Current totrow array' print for l in ray : for c in l : print c, print print def transferDataRange_to_totrow(self, C0, R0, C1, R1) : """read in all the data from the defined range on the sheet to totrow, a tuple of tuples in row format""" oCellRange = self.sheet.getCellRangeByPosition(C0,R0,C1,R1) #fetch all the data in the range self.totrow = oCellRange.getDataArray() self.noDataRows = len(self.totrow) print 'no of rows read from sheet : ', len(self.totrow) self.putCell(self.msgcell,'data placed in totrow') def transferDataRange_to_totrow_lotcol(self, C0, R0, C1, R1): """read in all the data from the defined range to internal python arrays totrow and lotcol""" self.transferDataRange_to_totrow(C0, R0, C1, R1) #convert data to column format and store in global lotcol self.lotcol = zip(*self.totrow) #using lotcol[n] lot faster than list comprehension : [self.totrow[m][n] for m in range(0,len(self.totrow))] print 'no of rows read from sheet : ', len(self.totrow) self.putCell(self.msgcell,'data placed in totrow, lotcol') def convertTupletoLotcol(tt,cols) : """convert a tuple to a tuple of tuples in column arrangement each row containing len(tt)/cols elements""" rows = len(tt) /cols z = ['']*rows for n in range(0,rows) : s = n * cols z[n] = tt[s : s + cols] return zip(*z) def transferTupleToSheetRange(self, tupledata, c0,r0,c1,r1) : """generic : displays contents of tupledata on the sheet in range shown""" oCellRange = self.sheet.getCellRangeByPosition(c0,r0,c1,r1) #tuple data needs to be in tuple of tuple format for setDataArray- convertTuple does this oCellRange.setDataArray(self.convert_Seq_to_Seq_of_Tuples_rows(tupledata,1 + c1 -c0)) def fillinColumnStaticData(self, tupdata, name = 'variable', default = 0) : """fills in all rows of a column of data, filling in empty rows with a pre-existing value in an earlier row and accepting any row with a valid entry. Returns a tuple. """ data = list(tupdata) dc = data[0] # always provide a default value for first row (0 or other) if dc == '' : dc = default for d in data: if d == '' : data[data.index(d)] = dc else : dc = d return tuple(data) def transferATupleToASheetColumn(self, tupledata, col0,row0) : """displays contents of a data tuple in the relevant column on the sheet""" oCellRange = self.sheet.getCellRangeByPosition(col0,row0,col0,row0+len(tupledata)-1) #convert tupledata to format acceptable for a column for setDataArray ie a tuple of tuples ((219,),(219,), ...)) tzt = tuple(zip(tupledata)) oCellRange.setDataArray(tzt) self.putCell(self.msgcell,'data written to column') def transferATupleToASheetRow(self, tp, c0,r0) : """generic : writes a row of data in single tuple on the sheet starting at c0, r0""" r1 = r0 c1 = c0 + len(tp) -1 oCellRange = self.sheet.getCellRangeByPosition(c0,r0,c1,r1) #tuple data needs to be in tuple of tuple format for setDataArray- convertTuple does this oCellRange.setDataArray(tuple([tuple(tp) for n in range(1)])) self.putCell(self.msgcell,'data written to row') def UpdateSheet(self, c0,r0,c1,r1 ) : """displays contents of all the internal tuple array (tupledata=totrow) on the sheet""" #currently columns of interest are A to Z oCellRange = self.sheet.getCellRangeByPosition(c0,r0,c1,r1) oCellRange.setDataArray(self.totrow) self.putCell(self.msgcell,'sheet updated') def transferTupleOfTuples_to_SheetSelection(self, tupledata, c0,r0,c1,r1) : """generic : displays contents of tuple of tupledata on the sheet in range shown""" oCellRange = self.sheet.getCellRangeByPosition(c0,r0,c1,r1) #tuple data needs to be in tuple of tuple format for setDataArray- convertTuple does this oCellRange.setDataArray(tupledata) def PlaceDataInCell(self, cr, tupledata) : """places contents of tupledata of form : ((1,2,3,...),) in cell c,r""" oCellRange = self.sheet.getCellRangeByName(cr) oCellRange.setDataArray(tupledata) self.putCell(self.msgcell,'data written to cell') def saveDataToFile(self, name, data) : """save data in totrow in file named name""" print 'name of savefile', name try: f = open(name,'wb') cPickle.dump(data,f) except IOError : print '\nunable to open file', name, 'for saving' finally: f.close() self.putCell(self.msgcell,'saved data to file' + name) def getDataFromFile(self, name) : """save data in totrow in file named name""" try: f = open(name,'rb') data = cPickle.load(f) except IOError : print '\nunable to open file', name, 'for reading' pass finally: f.close() print '\nread data from file', name return data def getColumn(self, col) : """return contents of sheet data column col in list format""" l = [] for c in self.lotcol[col]: l.append(c) return l def fillcells(self, specifiedrange, option = 0, startvalue = 0, step = 1): """fill a range of cells cell by cell with dummy data option = 0, string,option = 1 numbers; These two options start with startvalue = as set ,and step = as set. """ if specifiedrange == 0 : c0,r0,c1,r1 = self.getSelectedRange() else : c0,r0,c1,r1 = specifiedrange for r in range(r0, r1+1): for c in range(c0, c1+1): cells = self.sheet.getCellByPosition(c, r) if (option == 0 ) : cells.String = 'a-' + str(startvalue) #note use of String attribute, could also try Value for numerical data elif (option == 1) : cells.Value = startvalue startvalue += step def fillRange_with_primes(self, specifiedrange) : """fills selected range with prime numbers starting from 2""" if specifiedrange == 0 : c0,r0,c1,r1 = self.getSelectedRange() else : c0,r0,c1,r1 = specifiedrange ll = self.erat2((1+c1-c0)* (1+r1-r0)) self.transferTupleToSheetRange(tuple(ll),c0,r0,c1,r1) # pythonloader looks for a static g_ImplementationHelper variable : g_ImplementationHelper = unohelper.ImplementationHelper() g_ImplementationHelper.addImplementation( \ transferdemoJob, # UNO object class "org.openoffice.comp.pyuno.demo.transferdemo", # Implementation name ("com.sun.star.task.Job",),) # List of implemented services