MoinMoin: attachment:sctable-1.3.5-3.py of ParserMarket/Sctable

   1  
   2 """
   3     MoinMoin - sctable a Processor for spread sheet calculations by sc
   4     @license: GNU GPL, see COPYING for details.
   5 
   6     PURPOSE:
   7         This processor is used to do some spread sheet calculation based on sc in a
   8 	regular wiki table. The first column/first line coordinate is A0.
   9 
  10     CALLING SEQUENCE:
  11        {{{
  12        #!sctable [-column_header, -row_header, -show_formular, -format  ]
  13        }}}
  14 
  15     OPTIONAL INPUTS:
  16        -column_header: additional in the result the column header is shown
  17        -row_header: additional in the result the line number header is shown
  18        -show_formular: if set the formular instead of the result is shown,
  19                        data is arranged in textmode. Blanks in formulars are removed
  20        -format: is used to the set the number of digits for the column values
  21 
  22 
  23     EXAMPLE:
  24 {{{
  25 SUM over columns}}}
  26 {{{
  27 #!sctable
  28 ||1||2||=A0+B0||
  29 ||10||20||=@sum(A1:B1)||
  30 }}}
  31 
  32 RESULT:
  33 ||<)>1.00||<)>2.00||<)>3.00||
  34 ||<)>10.00||<)>20.00||<)>30.00||
  35 ------
  36 {{{
  37 cell B1 no data}}}
  38 
  39 {{{
  40 #!sctable
  41 ||A||B||C||D||
  42 ||1||||2||=A1+C1||
  43 }}}
  44 
  45 RESULT:
  46 ||<(>A||<(>B||<(>C||<(>D||
  47 ||<)>1.00||<)>||<)>2.00||<)>3.00||
  48 
  49 -----
  50 {{{
  51 SUM over rows}}}
  52 {{{
  53 #!sctable
  54 ||1||2||=A0+B0||
  55 ||10||20||30||
  56 ||=@sum(A0:A1)||=@sum(B0:B1)||=@sum(C0:C1)||
  57 }}}
  58 
  59 RESULT:
  60 ||<)>1.00||<)>2.00||<)>3.00||
  61 ||<)>10.00||<)>20.00||<)>30.00||
  62 ||<)>11.00||<)>22.00||<)>33.00||
  63 
  64 -----
  65 {{{
  66 SUM over rows and columns}}}
  67 {{{
  68 #!sctable
  69 ||A||B||C||
  70 ||1||2||=A1+B1||
  71 ||10||20||=@sum(A2:B2)||
  72 ||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
  73 }}}
  74 
  75 RESULT:
  76 ||<(>A||<(>B||<(>C||
  77 ||<)>1.00||<)>2.00||<)>3.00||
  78 ||<)>10.00||<)>20.00||<)>30.00||
  79 ||<)>11.00||<)>22.00||<)>33.00||
  80 
  81 -----
  82 {{{
  83 -column_header}}}
  84 {{{
  85 #!sctable  -column_header
  86 ||1||2||
  87 ||3||4||
  88 ||5||6||
  89 }}}
  90 
  91 RESULT:
  92 ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
  93 ||<)>1.00||<)>2.00||
  94 ||<)>3.00||<)>4.00||
  95 ||<)>5.00||<)>6.00||
  96 
  97 
  98 -----
  99 {{{
 100 -row_header}}}
 101 {{{
 102 #!sctable -row_header
 103 ||1||2||
 104 ||3||4||
 105 ||5||6||
 106 }}}
 107 
 108 RESULT:
 109 ||<)5%#CCCCCC>'''0'''||<)>1.00||<)>2.00||
 110 ||<)5%#CCCCCC>'''1'''||<)>3.00||<)>4.00||
 111 ||<)5%#CCCCCC>'''2'''||<)>5.00||<)>6.00||
 112 
 113 -----
 114 {{{
 115 -column_header  -row_header}}}
 116 {{{
 117 #!sctable  -column_header  -row_header
 118 ||1||2||
 119 ||3||4||
 120 ||5||6||
 121 }}}
 122 
 123 RESULT:
 124 ||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
 125 ||<)5%#CCCCCC>'''0'''||<)>1.00||<)>2.00||
 126 ||<)5%#CCCCCC>'''1'''||<)>3.00||<)>4.00||
 127 ||<)5%#CCCCCC>'''2'''||<)>5.00||<)>6.00||
 128 
 129 -----
 130 {{{
 131 -show_formular  -column_header  -row_header}}}
 132 {{{
 133 #!sctable -show_formular  -column_header  -row_header
 134 ||m||p||
 135 ||1||=A1 * 5||
 136 ||2||=A2-3||
 137 ||3||4||
 138 }}}
 139 
 140 RESULT:
 141 ||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||
 142 ||<)5%#CCCCCC>'''0'''||<(>m||<(>p||
 143 ||<)5%#CCCCCC>'''1'''||<(>1||<(>=A1*5||
 144 ||<)5%#CCCCCC>'''2'''||<(>2||<(>=A2-3||
 145 ||<)5%#CCCCCC>'''3'''||<(>3||<(>4||
 146 
 147 -----
 148 {{{
 149 -column_header and blanks in cells}}}
 150 {{{
 151 #!sctable -column_header
 152 ||Name Vorname||  ||  || 3 || || 5||
 153 ||Name Vorname|| 1 || 2 ||  || 4 || 5||
 154 ||Name Vorname|| 1 || 2 || || || 5||
 155 }}}
 156 
 157 RESULT:
 158 ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||<:#CCCCCC>'''C'''||<:#CCCCCC>'''D'''||<:#CCCCCC>'''E'''||<:#CCCCCC>'''F'''||
 159 ||<(>Name Vorname||    ||    ||<)>3.00||    ||<)>5.00||
 160 ||<(>Name Vorname||<)>1.00||<)>2.00||    ||<)>4.00||<)>5.00||
 161 ||<(>Name Vorname||<)>1.00||<)>2.00||    ||    ||<)>5.00||
 162 
 163 -----
 164 {{{
 165 -format 1,1}}}
 166 {{{
 167 #!sctable -format 1,1
 168 ||1||2||
 169 ||3||4||
 170 ||=@sum(a0:a1)||=a2*4||
 171 }}}
 172 
 173 RESULT:
 174 ||<)>1.0||<)>2.0||
 175 ||<)>3.0||<)>4.0||
 176 ||<)>4.0||<)>16.0||
 177 
 178 -----
 179 {{{ useage of variable names -show_formular -column_header  -row_header
 180 }}}
 181 {{{#!sctable -show_formular -column_header  -row_header
 182 ||A||B||C||
 183 ||1||{two}2||=A1+two||
 184 ||10||20||=@sum(A2:B2)||
 185 ||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
 186 }}}
 187 
 188 
 189 RESULT:
 190 ||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||<:#CCCCCC>'''C'''||
 191 ||<)5%#CCCCCC>'''0'''||<(>A||<(>B||<(>C||
 192 ||<)5%#CCCCCC>'''1'''||<(>1||<(>{two}2||<(>=A1+two||
 193 ||<)5%#CCCCCC>'''2'''||<(>10||<(>20||<(>=@sum(A2:B2)||
 194 ||<)5%#CCCCCC>'''3'''||<(>=@sum(A1:A2)||<)>=@sum(B1:B2)||<(>=@sum(C1:C2)||
 195 
 196 and if we calculate [[BR]]
 197 RESULT:
 198 {{{#!sctable -column_header  -row_header
 199 ||A||B||C||
 200 ||1||{two}2||=A1+two||
 201 ||10||20||=@sum(A2:B2)||
 202 ||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
 203 }}}
 204 -----
 205 {{{color in cells
 206 }}}
 207 {{{#!sctable
 208 ||<:rowbgcolor=lightcyan>'''A'''||<:>'''B'''||<:>'''C'''||
 209 ||<)#dddddd>1||<)#dddddd>{two}2||<)#cccccc>=A1+two||
 210 ||<(>10||<)>20||<:#dddddd>=@sum(A2:B2)||
 211 ||<rowbgcolor="#cc99ff">=@sum(A1:A2)||=@sum(B1:B2)||<bgcolor=magenta>=@sum(C1:C2)||
 212 }}}
 213 
 214 RESULT:
 215 ||<:rowbgcolor=lightcyan>'''A'''||<:>'''B'''||<:>'''C'''||
 216 ||<)#dddddd>1.00||<)#dddddd>2.00||<)#cccccc>3.00||
 217 ||<(>10.00||<)>20.00||<:#dddddd>30.00||
 218 ||<rowbgcolor="#cc99ff">11.00||22.00||<bgcolor=magenta>33.00||
 219 -----
 220 
 221 
 222     PROCEDURE:
 223       This processor needs the external sc (http://freshmeat.net/projects/sc/) routine.
 224       It is necessary to have a tmp directory in the wiki data dir.
 225       All formulars have to start by a "=" sign.
 226 
 227       Please remove the version number from the routine name!
 228 
 229     RESTRICTIONS:
 230       * german umlauts e.g. äöü are not implemented by now. 
 231         they need to be masked because sc is not able to proceed calculation with such signs.
 232 	 
 233       
 234 
 235     MODIFICATION:
 236        @copyright: 2004-09-19 by Reimar Bauer (R.Bauer@fz-juelich.de) sctable-1.2.3-1
 237        1.2.3-2 : (RB) bug fixed line #!sctable was not found by giving input parameters
 238        1.2.3-3 : (RB) input parameter -show_formular added, column width is set to 200 chars
 239                :      if this parameter is used
 240                : (RB) bug removed (already) #!sctable position could be different from 0
 241 	       :      but always greater -1
 242       1.2.3-4  : 2004-10-05 RB format always extended for sc call to 200 signs.
 243                  bug with blanks in names removed, more as one blank in a cell handled as one blank.
 244       1.2.3-5  : 2004-10-16 RB format codes for colors and cells removed before calculations
 245                  -format optional input var added
 246 
 247       1.3      : 2004-11-13 RB changed to PARSER
 248                  bug fixed: strings with blanks are formatted to the left
 249 		 feature added: column width of row numbers is set to 5%
 250 		 some examples fixed
 251 
 252       1.3.3-2 patch and examples from towi AT geocities DOT SPAM com implemented
 253               : format and colors are used by now!!
 254 
 255       	FUNCTIONAL ADDITIONS
 256 
 257   	* a cell can now start with an wiki format string that will format
 258     	  the cell according to wiki table formatting,
 259           examples:
 260           <:>@sum(A3:a6)
 261           <rowbgcolor=cyan>Title
 262 
 263        * you can define a name for a cell and use it later (no range defines yet):
 264          when combinign with <format> the format must come first.
 265          examples:
 266          {income}20000
 267          <:>{outcome}=income/2
 268 
 269     DISCUSSION (need help):
 270       * we need a better routine to destinguish between strings and numbers, i have still some problems with e.g
 271         def is_number(txt):
 272         	ItIs = True
 273         try:
 274                 f =float(txt)
 275         except TypeError:
 276                 ItIs = False
 277 
 278         return(ItIs)
 279 
 280 	This doesn't work with formulars =@sum(a0:a2)
 281 
 282         what I do at the moment is to scan digits and signs from a string. This does not right identify a dot (.)
 283 	in a string. This will be formatted as number. Thats the only bad thing I know on.
 284 
 285 	ideas are welcome.
 286 
 287         1.3.5-3: 2005-08-05 RB tmp path set relativ to installation and created if it isn't there
 288 	                       space before and after a cell entry by now ignored
 289                 
 290 
 291 
 292 """
 293 
 294 Dependencies = []
 295 import sys, os, re, sha
 296 from MoinMoin.parser import wiki
 297 from MoinMoin.action import AttachFile
 298 from MoinMoin.Page import Page
 299 import string,os
 300 
 301 config_external_sc="/usr/bin/sc"
 302 
 303 def table2sc(lines,format,show_formular,request):
 304 
 305 	result=[]
 306 	formats = {} # { (row,col): '<wikiformat>', ... }
 307 
 308 	r=0
 309 	name="=" # searchstring
 310 	col_names=' ABCDEFGHIJKLMNOPQRSTUVWXYZ'
 311 
 312 	first_line=lines[0]
 313 	col_list=first_line.split('||')
 314         digit="2"
 315 	c=0
 316 	form=[]
 317 
 318 	for value in col_list:
 319 		if (len(value.lstrip()) > 0):
 320   			if (format=="") :
 321               			digit="2"
 322 	   		else:
 323 	      			digit=format[c-1]
 324 
 325            		f="%(command)s %(column)s %(arg)s" %{
 326 	     			"command":"format",
 327 	     			"column":col_names[c],
 328 	     			"arg":"200 " + digit + " 0"
 329 	     		}
 330 	   		form.append(f)
 331 
 332         	c=c+1
 333     	result.append(form)
 334 
 335 
 336 	for txt in lines:
 337 		n_name=txt.count(name)
 338 		txt=txt.lstrip()
 339 		
 340 		sargs=txt.split('||')
 341 		n=len(sargs)
 342 		sargs=sargs[0:n-1]
 343                 
 344 		c=0      # linecounter
 345 		digits="=-.0123456789"
 346 		for arg in sargs:
 347 		        
 348 		        arg=str(arg.strip())
 349 			
 350 			# check for wiki formatting string at beginning of sc data: "<format>..."
 351          		if arg.startswith('<'):
 352             			p = arg.find('>')+1
 353             			if p>1:
 354                 			formats[(r,c)] , arg = arg[:p] , arg[p:]
 355 					arg=arg.strip()
 356 			# check for a sc column name define: "{defname}..."
 357          		defname = None
 358 			if (show_formular == 0):
 359          			if arg.startswith('{'):
 360             				p = arg.find('}')
 361             				if p>0:
 362                 				defname , arg = arg[1:p] , arg[p+1:]
 363 						arg=arg.strip()
 364 
 365 			# sc data
 366         		if (len(arg) > 0):
 367 	    			if arg[0] in digits:
 368 	       				if (arg.find(name) == 0):
 369                   				if (show_formular == 0):
 370                       					sargs[c]="%(command)s %(column)s %(arg)s" %{
 371 						        	"command":'let ',
 372 					      			"column":col_names[c]+str(r),
 373 			      					"arg":string.strip(str(arg))
 374 			    				}
 375                   				else:
 376 		      					arg=string.replace(arg,"="," =")
 377                       					sargs[c]="%(command)s %(column)s=%(arg)s" %{
 378 		              					"command":'leftstring',
 379 			      					"column":col_names[c]+str(r),
 380 			      					"arg":'"'+string.replace(str(arg),' ','')+'"'
 381 			    				}
 382 
 383 	       				else:
 384 	         				if (show_formular == 0):
 385                     					sargs[c]="%(command)s %(column)s=%(arg)s" %{
 386 		              						"command":'let ',
 387 			      						"column":col_names[c]+str(r),
 388 			      						"arg":string.strip(str(arg))
 389 			     				}
 390 		 				else:
 391                     					sargs[c]="%(command)s %(column)s=%(arg)s" %{
 392 			  						"command":'leftstring',
 393 									"column":col_names[c]+str(r),
 394 			      						"arg":'"'+string.replace(str(arg),' ','')+'"'
 395 								}
 396 
 397 
 398             			else:
 399 	       				if (c > 0):
 400 						if (string.strip(arg) == ""):
 401 		      					arg="Blank!@"
 402 		  				else:
 403 		      					arg=string.strip(arg)
 404 		      					arg=string.replace(arg," ","Blank!@")
 405 
 406                   				sargs[c]="%(command)s %(column)s=%(arg)s" %{
 407 		              				"command":'leftstring ',
 408 			      				"column":col_names[c]+str(r),
 409 			      				"arg":'"'+str(arg)+'"'
 410 			    			}
 411 
 412 			else:
 413 				if (c > 0):
 414 					sargs[c]="%(command)s %(column)s=%(arg)s" %{
 415 		              			"command":'leftstring ',
 416 				      		"column":col_names[c]+str(r),
 417 				      		"arg":'" Blank!@"'
 418 			    		}
 419 			if defname:
 420 				sargs[c] = '%s\ndefine "%s" %s%s' % (sargs[c], defname, col_names[c], r)
 421 			c=c+1
 422 		result.append(sargs)
 423 		r=r+1
 424 
 425 	return(result,formats)
 426 
 427 class Parser:
 428 
 429         extensions = ['.sc']
 430 	
 431 	
 432 	
 433 	def __init__(self, raw, request, **kw):
 434 
 435         	self.raw = raw
 436         	self.request = request
 437         	self.form = request.form
 438         	self._ = request.getText
 439 		self.kw = []
 440 		for arg in kw.get('format_args','').split():
 441 		   self.kw.append(arg)
 442 
 443 
 444 
 445 
 446 
 447 	def format(self, formatter):
 448                 config_sc_vartmp_dir = os.path.join(self.request.rootpage.getPagePath(),'tmp')
 449 		
 450 		if not os.path.exists(config_sc_vartmp_dir):
 451 		   os.mkdir(config_sc_vartmp_dir)
 452 
 453 		   
 454 		
 455 		lines = self.raw.split('\n')
 456 		
 457 		zt=0
 458 		for txt in lines:
 459 		    
 460 		    txt=txt.replace(u'\xc3\x84','AE')
 461 		    lines[zt]=txt
 462 		    zt=zt+1
 463 		
 464 		kw=self.kw
 465 
 466     		column_header=0
 467     		row_header=0
 468     		show_formular=0
 469     		format=''
 470 
 471     		zt=0
 472 
 473     		for test in kw:
 474       			if (test == '-column_header') : column_header=1
 475       			if (test == '-row_header') : row_header=1
 476       			if (test == '-show_formular') : show_formular=1
 477       			if (test == '-format') : format=string.split(kw[zt+1],",")
 478       			zt=zt+1
 479 
 480 
 481     		matrix = []
 482 
 483     		textstr = '\n'.join(lines).strip()
 484 
 485     		tmpname = re.sub('\s+', ' ', textstr)
 486 		
 487 		
 488 		tmpname=sha.new(tmpname).hexdigest().upper()
 489     		
 490 		#tmpname=sha.new(tmpname).hexdigest()
 491 		
 492 		
 493     		tmpname = tmpname + "_sc"
 494     		tmpfile = "%s/%s.sc" % (config_sc_vartmp_dir, tmpname)
 495 		
 496 		
 497 		textstr, formats=table2sc(lines,format,show_formular,self.request)
 498 		
 499 
 500 
 501     		data = open(tmpfile, "w")
 502     		
 503     		i=0
 504     		for txt in textstr:
 505         		
 506 			if (len(textstr[i]) > 0):
 507             			tmpstr=string.join(textstr[i],'\n')
 508 			        data.write('%s\n' % (tmpstr))
 509         		i=i+1
 510     		data.close()
 511 		
 512 
 513     		cmd = "%(external_sc)s %(argument)s %(file)s " % {
 514              		"external_sc": config_external_sc,
 515              		"argument": " -W% ",
 516 	     		"file":tmpfile
 517           		}
 518 
 519 
 520     		f=os.popen(cmd,'r')# popen to get the result of the calculation
 521     		result=f.readlines()
 522     		f.flush()
 523     		os.unlink(tmpfile) # remove the tmpfile
 524 
 525     		right_format='<)>'
 526     		left_format='<(>'
 527 
 528     		for txt in result:
 529 		        
 530       			txt=string.join(txt,'')
 531       			cells=string.split(txt,' ')
 532       			zres=[]
 533       			c=1
 534       			for value in cells:
 535         			value=string.join(value,'')
 536         			value=string.strip(value)
 537         			strlen=len(value)
 538 
 539 				if (strlen > 0) :
 540 					v = ord(value[0])
 541 	   				if ord('a')<=v<=ord('z') or ord('A')<=v<=ord('Z') or value[0] in " ":
 542 						format=left_format
 543 	   				else:
 544 	      					if (show_formular == 0) :
 545 	         					format=right_format
 546 	      					else:
 547               			 			format=left_format
 548 
 549            				if (value == "Blank!@"):
 550 						value=" "
 551 
 552 	   				if string.find(value,"Blank!@"):
 553 	      					value=string.replace(value,"Blank!@", " ")
 554 
 555 					zres.append( [format, value] )
 556 
 557 					c=c+1
 558       			matrix.append(zres)
 559 			# post formatting of cells according to cut out wiki format strings
 560     			# - matrix is here: [ [  [format,content], ... ], ... ]
 561     		for cellname, cellformat in formats.items():
 562         		row,col = cellname
 563         		m = matrix[row][col-1]
 564 			m[0] = cellformat
 565     		matrix = map(lambda row: "||" + "||".join([f+c for f,c in row]) + "||", matrix)
 566 
 567     		# post processing of over all matrix
 568     		# - matrix is here: [ "||cell||cell||...||", "||cell||...||", ... ]
 569 
 570     		if (len(matrix[0]) == 2):  # das ist noch nicht die beste Loesung (workaround)
 571        			matrix=matrix[1:]
 572 
 573     		if (row_header == 1):
 574       			y=[]
 575       			r=len(matrix)
 576       			lines=range(r)
 577 
 578       			i=0
 579 
 580       			for no in lines:
 581 			        matrix[i]="||<)5%#CCCCCC>'''"+str(no)+"'''"+matrix[i]
 582 		 		i=i+1
 583 
 584     		if (column_header == 1):
 585        			col_names=' ABCDEFGHIJKLMNOPQRSTUVWXYZ'
 586        			x='||'
 587        			if(row_header == 1):
 588           			start=0
 589        			else:
 590           			start=1
 591        			for name in col_names[start:c]:
 592            			x = x + "%(format)s %(value)s %(tab)s" % {
 593 	                    		"format": '<:#CCCCCC>',
 594 	                    		"value": "'''"+name+"'''",
 595 		            		"tab":'||'}
 596 
 597        			matrix.insert(0,x)
 598 
 599 		wikiizer = wiki.Parser(string.join(matrix,"\n"),self.request) # parser for wiki tabular
 600 		wikiizer.format(formatter)