MoinMoin: attachment:sctable-1.3.py of ParserMarket/Sctable

   1 """
   2     MoinMoin - sctable a Processor for spread sheet calculations by sc
   3     @license: GNU GPL, see COPYING for details.
   4 
   5     PURPOSE:
   6         This processor is used to do some spread sheet calculation based on sc in a
   7 	regular wiki table. The first column/first line coordinate is A0.
   8 
   9     CALLING SEQUENCE:
  10        {{{
  11        #!sctable [-column_header, -row_header, -show_formular, -format  ]
  12        }}}
  13 
  14     OPTIONAL INPUTS:
  15        -column_header: additional in the result the column header is shown
  16        -row_header: additional in the result the line number header is shown
  17        -show_formular: if set the formular instead of the result is shown,
  18                        data is arranged in textmode. Blanks in formulars are removed
  19        -format: is used to the set the number of digits for the column values
  20 
  21 
  22     EXAMPLE:
  23 {{{
  24 SUM over columns}}}
  25 {{{
  26 #!sctable
  27 ||1||2||=A0+B0||
  28 ||10||20||=@sum(A1:B1)||
  29 }}}
  30 
  31 RESULT:
  32 ||<)>1.00||<)>2.00||<)>3.00||
  33 ||<)>10.00||<)>20.00||<)>30.00||
  34 ------
  35 {{{
  36 cell B1 no data}}}
  37 
  38 {{{
  39 #!sctable
  40 ||A||B||C||D||
  41 ||1||||2||=A1+C1||
  42 }}}
  43 
  44 RESULT:
  45 ||<(>A||<(>B||<(>C||<(>D||
  46 ||<)>1.00||<)>||<)>2.00||<)>3.00||
  47 
  48 -----
  49 {{{
  50 SUM over rows}}}
  51 {{{
  52 #!sctable
  53 ||1||2||=A0+B0||
  54 ||10||20||30||
  55 ||=@sum(A0:A1)||=@sum(B0:B1)||=@sum(C0:C1)||
  56 }}}
  57 
  58 RESULT:
  59 ||<)>1.00||<)>2.00||<)>3.00||
  60 ||<)>10.00||<)>20.00||<)>30.00||
  61 ||<)>11.00||<)>22.00||<)>33.00||
  62 
  63 -----
  64 {{{
  65 SUM over rows and columns}}}
  66 {{{
  67 #!sctable
  68 ||A||B||C||
  69 ||1||2||=A1+B1||
  70 ||10||20||=@sum(A2:B2)||
  71 ||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
  72 }}}
  73 
  74 RESULT:
  75 ||<(>A||<(>B||<(>C||
  76 ||<)>1.00||<)>2.00||<)>3.00||
  77 ||<)>10.00||<)>20.00||<)>30.00||
  78 ||<)>11.00||<)>22.00||<)>33.00||
  79 
  80 -----
  81 {{{
  82 -column_header}}}
  83 {{{
  84 #!sctable  -column_header
  85 ||1||2||
  86 ||3||4||
  87 ||5||6||
  88 }}}
  89 
  90 RESULT:
  91 ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
  92 ||<)>1.00||<)>2.00||
  93 ||<)>3.00||<)>4.00||
  94 ||<)>5.00||<)>6.00||
  95 
  96 
  97 -----
  98 {{{
  99 -row_header}}}
 100 {{{
 101 #!sctable -row_header
 102 ||1||2||
 103 ||3||4||
 104 ||5||6||
 105 }}}
 106 
 107 RESULT:
 108 ||<)5%#CCCCCC>'''0'''||<)>1.00||<)>2.00||
 109 ||<)5%#CCCCCC>'''1'''||<)>3.00||<)>4.00||
 110 ||<)5%#CCCCCC>'''2'''||<)>5.00||<)>6.00||
 111 
 112 -----
 113 {{{
 114 -column_header  -row_header}}}
 115 {{{
 116 #!sctable  -column_header  -row_header
 117 ||1||2||
 118 ||3||4||
 119 ||5||6||
 120 }}}
 121 
 122 RESULT:
 123 ||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
 124 ||<)5%#CCCCCC>'''0'''||<)>1.00||<)>2.00||
 125 ||<)5%#CCCCCC>'''1'''||<)>3.00||<)>4.00||
 126 ||<)5%#CCCCCC>'''2'''||<)>5.00||<)>6.00||
 127 
 128 -----
 129 {{{
 130 -show_formular  -column_header  -row_header}}}
 131 {{{
 132 #!sctable -show_formular  -column_header  -row_header
 133 ||m||p||
 134 ||1||=A1 * 5||
 135 ||2||=A2-3||
 136 ||3||4||
 137 }}}
 138 
 139 RESULT:
 140 ||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
 141 ||<)5%#CCCCCC>'''0'''||<(>m||<(>p||
 142 ||<)5%#CCCCCC>'''1'''||<(>1||<(>=A1*5||
 143 ||<)5%#CCCCCC>'''2'''||<(>2||<(>=A2-3||
 144 ||<)5%#CCCCCC>'''3'''||<(>3||<(>4||
 145 
 146 -----
 147 {{{
 148 -column_header and blanks in cells}}}
 149 {{{
 150 #!sctable -column_header
 151 ||Name Vorname||  ||  || 3 || || 5||
 152 ||Name Vorname|| 1 || 2 ||  || 4 || 5||
 153 ||Name Vorname|| 1 || 2 || || || 5||
 154 }}}
 155 
 156 RESULT:
 157 ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||<:#CCCCCC>'''C'''||<:#CCCCCC>'''D'''||<:#CCCCCC>'''E'''||<:#CCCCCC>'''F'''||
 158 ||<(>Name Vorname||    ||    ||<)>3.00||    ||<)>5.00||
 159 ||<(>Name Vorname||<)>1.00||<)>2.00||    ||<)>4.00||<)>5.00||
 160 ||<(>Name Vorname||<)>1.00||<)>2.00||    ||    ||<)>5.00||
 161 
 162 -----
 163 {{{
 164 -format 1,1}}}
 165 {{{
 166 #!sctable -format 1,1
 167 ||<#AA0000> 1||2||
 168 ||3||4||
 169 ||=@sum(a0:a1)||=a2*4||
 170 }}}
 171 
 172 RESULT:
 173 ||<)>1.0||<)>2.0||
 174 ||<)>3.0||<)>4.0||
 175 ||<)>4.0||<)>16.0||
 176 
 177 
 178 
 179     PROCEDURE:
 180       This processor needs the external sc (http://freshmeat.net/projects/sc/) routine.
 181       It is necessary to have a tmp directory in the wiki data dir.
 182       All formulars have to start by a "=" sign.
 183 
 184       Please remove the version number from the routine name!
 185 
 186     RESTRICTIONS:
 187 
 188 
 189     MODIFICATION:
 190        @copyright: 2004-09-19 by Reimar Bauer (R.Bauer@fz-juelich.de) sctable-1.2.3-1
 191        1.2.3-2 : (RB) bug fixed line #!sctable was not found by giving input parameters
 192        1.2.3-3 : (RB) input parameter -show_formular added, column width is set to 200 chars
 193                :      if this parameter is used
 194                : (RB) bug removed (already) #!sctable position could be different from 0
 195 	       :      but always greater -1
 196       1.2.3-4  : 2004-10-05 RB format always extended for sc call to 200 signs.
 197                  bug with blanks in names removed, more as one blank in a cell handled as one blank.
 198       1.2.3-5  : 2004-10-16 RB format codes for colors and cells removed before calculations
 199                  -format optional input var added
 200 
 201       1.3      : 2004-11-13 RB changed to PARSER
 202                  bug fixed: strings with blanks are formatted to the left
 203 		 feature added: column width of row numbers is set to 5%
 204 		 some examples fixed
 205 
 206 
 207     DISCUSSION:
 208       * we need a better routine to destinguish between strings and numbers
 209 
 210 """
 211 
 212 Dependencies = []
 213 import sys, os, re, sha
 214 from MoinMoin.parser import wiki
 215 from MoinMoin.action import AttachFile
 216 from MoinMoin.Page import Page
 217 import string,os
 218 
 219 config_sc_vartmp_dir = "./data/tmp"
 220 config_external_sc="/usr/bin/sc"
 221 
 222 def table2sc(lines,format,show_formular,request):
 223 
 224 	result=[]
 225 
 226 	r=0
 227 	name="=" # searchstring
 228 	col_names=' ABCDEFGHIJKLMNOPQRSTUVWXYZ'
 229 
 230 	first_line=lines[0]
 231 	col_list=first_line.split('||')
 232         digit="2"
 233 	c=0
 234 	form=[]
 235 
 236 	for value in col_list:
 237 		if (len(value.lstrip()) > 0):
 238   			if (format=="") :
 239               			digit="2"
 240 	   		else:
 241 	      			digit=format[c-1]
 242 
 243            		f="%(command)s %(column)s %(arg)s" %{
 244 	     			"command":"format",
 245 	     			"column":col_names[c],
 246 	     			"arg":"200 " + digit + " 0"
 247 	     		}
 248 	   		form.append(f)
 249 
 250         	c=c+1
 251     	result.append(form)
 252 
 253 
 254 	for txt in lines:
 255 		n_name=txt.count(name)
 256 		txt=txt.lstrip()
 257 		sargs=txt.split('||')
 258 		n=len(sargs)
 259 		sargs=sargs[0:n-1]
 260 
 261 		c=0      # linecounter
 262 		digits="=-.0123456789"
 263 		for arg in sargs:
 264 			arg=arg.lstrip()
 265          		pos1=string.find(arg,'<')
 266          		pos2=string.find(arg,'>')
 267 
 268 	 		if (pos2 > 0):
 269 	    			arg=(string.split(arg,'>'))[1]
 270 	    			arg=arg.lstrip()
 271 
 272         		if (len(arg) > 0):
 273 	    			if arg[0] in digits:
 274 	       				if (arg.find(name) == 0):
 275                   				if (show_formular == 0):
 276                       					sargs[c]="%(command)s %(column)s %(arg)s" %{
 277 						        	"command":'let ',
 278 					      			"column":col_names[c]+str(r),
 279 			      					"arg":string.strip(str(arg))
 280 			    				}
 281                   				else:
 282 		      					arg=string.replace(arg,"="," =")
 283                       					sargs[c]="%(command)s %(column)s=%(arg)s" %{
 284 		              					"command":'leftstring',
 285 			      					"column":col_names[c]+str(r),
 286 			      					"arg":'"'+string.replace(str(arg),' ','')+'"'
 287 			    				}
 288 
 289 	       				else:
 290 	         				if (show_formular == 0):
 291                     					sargs[c]="%(command)s %(column)s=%(arg)s" %{
 292 		              						"command":'let ',
 293 			      						"column":col_names[c]+str(r),
 294 			      						"arg":string.strip(str(arg))
 295 			     				}
 296 		 				else:
 297                     					sargs[c]="%(command)s %(column)s=%(arg)s" %{
 298 			  						"command":'leftstring',
 299 									"column":col_names[c]+str(r),
 300 			      						"arg":'"'+string.replace(str(arg),' ','')+'"'
 301 								}
 302 
 303 
 304             			else:
 305 	       				if (c > 0):
 306 						if (string.strip(arg) == ""):
 307 		      					arg="Blank!@"
 308 		  				else:
 309 		      					arg=string.strip(arg)
 310 		      					arg=string.replace(arg," ","Blank!@")
 311 
 312                   				sargs[c]="%(command)s %(column)s=%(arg)s" %{
 313 		              				"command":'leftstring ',
 314 			      				"column":col_names[c]+str(r),
 315 			      				"arg":'"'+str(arg)+'"'
 316 			    			}
 317 			else:
 318 				if (c > 0):
 319 					sargs[c]="%(command)s %(column)s=%(arg)s" %{
 320 		              			"command":'leftstring ',
 321 				      		"column":col_names[c]+str(r),
 322 				      		"arg":'" Blank!@"'
 323 			    		}
 324 			c=c+1
 325 		result.append(sargs)
 326 		r=r+1
 327 
 328 	return(result)
 329 
 330 class Parser:
 331 
 332         extensions = ['.sc']
 333 
 334 	def __init__(self, raw, request, **kw):
 335 
 336         	self.raw = raw
 337         	self.request = request
 338         	self.form = request.form
 339         	self._ = request.getText
 340 		self.kw = []
 341 		for arg in kw.get('format_args','').split():
 342 		   self.kw.append(arg)
 343 
 344 
 345 
 346 
 347 
 348 	def format(self, formatter):
 349 
 350 		lines = self.raw.split('\n')
 351 
 352 
 353 		kw=self.kw
 354 
 355     		column_header=0
 356     		row_header=0
 357     		show_formular=0
 358     		format=''
 359 
 360     		zt=0
 361 
 362     		for test in kw:
 363       			if (test == '-column_header') : column_header=1
 364       			if (test == '-row_header') : row_header=1
 365       			if (test == '-show_formular') : show_formular=1
 366       			if (test == '-format') : format=string.split(kw[zt+1],",")
 367       			zt=zt+1
 368 
 369 
 370     		matrix = []
 371 
 372     		textstr = '\n'.join(lines).strip()
 373 
 374     		tmpname = re.sub('\s+', ' ', textstr)
 375     		tmpname = sha.new(tmpname).hexdigest()
 376     		tmpname = tmpname + "_sc"
 377     		tmpfile = "%s/%s.sc" % (config_sc_vartmp_dir, tmpname)
 378 
 379 		textstr=table2sc(lines,format,show_formular,self.request)
 380 
 381 
 382     		data = open(tmpfile, "w")
 383     		i=0
 384     		for txt in textstr:
 385         		if (len(textstr[i]) > 0):
 386             			tmpstr=string.join(textstr[i],'\n')
 387 			        data.write('%s\n' % (tmpstr))
 388         		i=i+1
 389     		data.close()
 390 
 391     		cmd = "%(external_sc)s %(argument)s %(file)s " % {
 392              		"external_sc": config_external_sc,
 393              		"argument": " -W% ",
 394 	     		"file":tmpfile
 395           		}
 396 
 397 
 398     		f=os.popen(cmd,'r')# popen to get the result of the calculation
 399     		result=f.readlines()
 400     		f.flush()
 401 #    		os.unlink(tmpfile) # remove the tmpfile
 402 
 403     		right_format='<)>'
 404     		left_format='<(>'
 405 
 406     		for txt in result:
 407       			txt=string.join(txt,'')
 408       			cells=string.split(txt,' ')
 409       			zres='||'
 410       			c=1
 411       			for value in cells:
 412         			value=string.join(value,'')
 413         			value=string.strip(value)
 414         			strlen=len(value)
 415 				if (strlen > 0) :
 416 	   				if ((value in " AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz") or (string.find(value,"Blank!@") > -1 )):
 417 	      					format=left_format
 418 	   				else:
 419 	      					if (show_formular == 0) :
 420 	         					format=right_format
 421 	      					else:
 422               			 			format=left_format
 423 
 424            				if (value == "Blank!@"):
 425 						value=" "
 426 
 427 
 428 
 429            				zres = zres + "%(format)s %(value)s %(tab)s" % {
 430 							"format": format,
 431 	                    				"value":  value,
 432 		            				"tab":'||'
 433 		         			}
 434 	   				if string.find(zres,"Blank!@"):
 435 	      					zres=string.replace(zres,"Blank!@", " ")
 436 
 437 					c=c+1
 438       			matrix.append(zres)
 439 
 440     		if (len(matrix[0]) == 2):  # das ist noch nicht die beste Loesung (workaround)
 441        			matrix=matrix[1:]
 442 
 443     		if (row_header == 1):
 444       			y=[]
 445       			r=len(matrix)
 446       			lines=range(r)
 447 
 448       			i=0
 449 
 450       			for no in lines:
 451 			        matrix[i]="||<)5%#CCCCCC>'''"+str(no)+"'''"+matrix[i]
 452 		 		i=i+1
 453 
 454     		if (column_header == 1):
 455        			col_names=' ABCDEFGHIJKLMNOPQRSTUVWXYZ'
 456        			x='||'
 457        			if(row_header == 1):
 458           			start=0
 459        			else:
 460           			start=1
 461        			for name in col_names[start:c]:
 462            			x = x + "%(format)s %(value)s %(tab)s" % {
 463 	                    		"format": '<:#CCCCCC>',
 464 	                    		"value": "'''"+name+"'''",
 465 		            		"tab":'||'}
 466 
 467        			matrix.insert(0,x)
 468 
 469 		wikiizer = wiki.Parser(string.join(matrix,"\n"),self.request) # parser for wiki tabular
 470 		wikiizer.format(formatter)