MoinMoin: attachment:sctable-1.3.3-2.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 ||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 {{{ useage of variable names -show_formular -column_header  -row_header
 179 }}}
 180 {{{#!sctable -show_formular -column_header  -row_header
 181 ||A||B||C||
 182 ||1||{two}2||=A1+two||
 183 ||10||20||=@sum(A2:B2)||
 184 ||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
 185 }}}
 186 
 187 
 188 RESULT:
 189 ||<:5%#CCCCCC> ||<:#CCCCCC>'''A'''||<:#CCCCCC>'''B'''||<:#CCCCCC>'''C'''||
 190 ||<)5%#CCCCCC>'''0'''||<(>A||<(>B||<(>C||
 191 ||<)5%#CCCCCC>'''1'''||<(>1||<(>{two}2||<(>=A1+two||
 192 ||<)5%#CCCCCC>'''2'''||<(>10||<(>20||<(>=@sum(A2:B2)||
 193 ||<)5%#CCCCCC>'''3'''||<(>=@sum(A1:A2)||<)>=@sum(B1:B2)||<(>=@sum(C1:C2)||
 194 
 195 and if we calculate [[BR]]
 196 RESULT:
 197 {{{#!sctable -column_header  -row_header
 198 ||A||B||C||
 199 ||1||{two}2||=A1+two||
 200 ||10||20||=@sum(A2:B2)||
 201 ||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
 202 }}}
 203 -----
 204 {{{color in cells
 205 }}}
 206 {{{#!sctable
 207 ||<:rowbgcolor=lightcyan>'''A'''||<:>'''B'''||<:>'''C'''||
 208 ||<)#dddddd>1||<)#dddddd>{two}2||<)#cccccc>=A1+two||
 209 ||<(>10||<)>20||<:#dddddd>=@sum(A2:B2)||
 210 ||<rowbgcolor="#cc99ff">=@sum(A1:A2)||=@sum(B1:B2)||<bgcolor=magenta>=@sum(C1:C2)||
 211 }}}
 212 
 213 RESULT:
 214 ||<:rowbgcolor=lightcyan>'''A'''||<:>'''B'''||<:>'''C'''||
 215 ||<)#dddddd>1.00||<)#dddddd>2.00||<)#cccccc>3.00||
 216 ||<(>10.00||<)>20.00||<:#dddddd>30.00||
 217 ||<rowbgcolor="#cc99ff">11.00||22.00||<bgcolor=magenta>33.00||
 218 -----
 219 
 220 
 221     PROCEDURE:
 222       This processor needs the external sc (http://freshmeat.net/projects/sc/) routine.
 223       It is necessary to have a tmp directory in the wiki data dir.
 224       All formulars have to start by a "=" sign.
 225 
 226       Please remove the version number from the routine name!
 227 
 228     RESTRICTIONS:
 229       * german umlauts e.g. äöü are not implemented by now
 230       * don't write a blank between the format code and a number
 231 
 232     MODIFICATION:
 233        @copyright: 2004-09-19 by Reimar Bauer (R.Bauer@fz-juelich.de) sctable-1.2.3-1
 234        1.2.3-2 : (RB) bug fixed line #!sctable was not found by giving input parameters
 235        1.2.3-3 : (RB) input parameter -show_formular added, column width is set to 200 chars
 236                :      if this parameter is used
 237                : (RB) bug removed (already) #!sctable position could be different from 0
 238 	       :      but always greater -1
 239       1.2.3-4  : 2004-10-05 RB format always extended for sc call to 200 signs.
 240                  bug with blanks in names removed, more as one blank in a cell handled as one blank.
 241       1.2.3-5  : 2004-10-16 RB format codes for colors and cells removed before calculations
 242                  -format optional input var added
 243 
 244       1.3      : 2004-11-13 RB changed to PARSER
 245                  bug fixed: strings with blanks are formatted to the left
 246 		 feature added: column width of row numbers is set to 5%
 247 		 some examples fixed
 248 
 249       1.3.3-2 patch and examples from towi AT geocities DOT SPAM com implemented
 250               : format and colors are used by now!!
 251 
 252       	FUNCTIONAL ADDITIONS
 253 
 254   	* a cell can now start with an wiki format string that will format
 255     	  the cell according to wiki table formatting,
 256           examples:
 257           <:>@sum(A3:a6)
 258           <rowbgcolor=cyan>Title
 259 
 260        * you can define a name for a cell and use it later (no range defines yet):
 261          when combinign with <format> the format must come first.
 262          examples:
 263          {income}20000
 264          <:>{outcome}=income/2
 265 
 266     DISCUSSION (need help):
 267       * we need a better routine to destinguish between strings and numbers, i have still some problems with e.g
 268         def is_number(txt):
 269         	ItIs = True
 270         try:
 271                 f =float(txt)
 272         except TypeError:
 273                 ItIs = False
 274 
 275         return(ItIs)
 276 
 277 	This doesn't work with formulars =@sum(a0:a2)
 278 
 279         what I do at the moment is to scan digits and signs from a string. This does not right identify a dot (.)
 280 	in a string. This will be formatted as number. Thats the only bad thing I know on.
 281 
 282 	ideas are welcome.
 283 
 284 
 285 
 286 
 287 """
 288 
 289 Dependencies = []
 290 import sys, os, re, sha
 291 from MoinMoin.parser import wiki
 292 from MoinMoin.action import AttachFile
 293 from MoinMoin.Page import Page
 294 import string,os
 295 
 296 config_sc_vartmp_dir = "./data/tmp"
 297 config_external_sc="/usr/bin/sc"
 298 
 299 def table2sc(lines,format,show_formular,request):
 300 
 301 	result=[]
 302 	formats = {} # { (row,col): '<wikiformat>', ... }
 303 
 304 	r=0
 305 	name="=" # searchstring
 306 	col_names=' ABCDEFGHIJKLMNOPQRSTUVWXYZ'
 307 
 308 	first_line=lines[0]
 309 	col_list=first_line.split('||')
 310         digit="2"
 311 	c=0
 312 	form=[]
 313 
 314 	for value in col_list:
 315 		if (len(value.lstrip()) > 0):
 316   			if (format=="") :
 317               			digit="2"
 318 	   		else:
 319 	      			digit=format[c-1]
 320 
 321            		f="%(command)s %(column)s %(arg)s" %{
 322 	     			"command":"format",
 323 	     			"column":col_names[c],
 324 	     			"arg":"200 " + digit + " 0"
 325 	     		}
 326 	   		form.append(f)
 327 
 328         	c=c+1
 329     	result.append(form)
 330 
 331 
 332 	for txt in lines:
 333 		n_name=txt.count(name)
 334 		txt=txt.lstrip()
 335 		sargs=txt.split('||')
 336 		n=len(sargs)
 337 		sargs=sargs[0:n-1]
 338 
 339 		c=0      # linecounter
 340 		digits="=-.0123456789"
 341 		for arg in sargs:
 342 			# check for wiki formatting string at beginning of sc data: "<format>..."
 343          		if arg.startswith('<'):
 344             			p = arg.find('>')+1
 345             			if p>1:
 346                 			formats[(r,c)] , arg = arg[:p] , arg[p:]
 347 			# check for a sc column name define: "{defname}..."
 348          		defname = None
 349 			if (show_formular == 0):
 350          			if arg.startswith('{'):
 351             				p = arg.find('}')
 352             				if p>0:
 353                 				defname , arg = arg[1:p] , arg[p+1:]
 354 
 355 			# sc data
 356         		if (len(arg) > 0):
 357 	    			if arg[0] in digits:
 358 	       				if (arg.find(name) == 0):
 359                   				if (show_formular == 0):
 360                       					sargs[c]="%(command)s %(column)s %(arg)s" %{
 361 						        	"command":'let ',
 362 					      			"column":col_names[c]+str(r),
 363 			      					"arg":string.strip(str(arg))
 364 			    				}
 365                   				else:
 366 		      					arg=string.replace(arg,"="," =")
 367                       					sargs[c]="%(command)s %(column)s=%(arg)s" %{
 368 		              					"command":'leftstring',
 369 			      					"column":col_names[c]+str(r),
 370 			      					"arg":'"'+string.replace(str(arg),' ','')+'"'
 371 			    				}
 372 
 373 	       				else:
 374 	         				if (show_formular == 0):
 375                     					sargs[c]="%(command)s %(column)s=%(arg)s" %{
 376 		              						"command":'let ',
 377 			      						"column":col_names[c]+str(r),
 378 			      						"arg":string.strip(str(arg))
 379 			     				}
 380 		 				else:
 381                     					sargs[c]="%(command)s %(column)s=%(arg)s" %{
 382 			  						"command":'leftstring',
 383 									"column":col_names[c]+str(r),
 384 			      						"arg":'"'+string.replace(str(arg),' ','')+'"'
 385 								}
 386 
 387 
 388             			else:
 389 	       				if (c > 0):
 390 						if (string.strip(arg) == ""):
 391 		      					arg="Blank!@"
 392 		  				else:
 393 		      					arg=string.strip(arg)
 394 		      					arg=string.replace(arg," ","Blank!@")
 395 
 396                   				sargs[c]="%(command)s %(column)s=%(arg)s" %{
 397 		              				"command":'leftstring ',
 398 			      				"column":col_names[c]+str(r),
 399 			      				"arg":'"'+str(arg)+'"'
 400 			    			}
 401 
 402 			else:
 403 				if (c > 0):
 404 					sargs[c]="%(command)s %(column)s=%(arg)s" %{
 405 		              			"command":'leftstring ',
 406 				      		"column":col_names[c]+str(r),
 407 				      		"arg":'" Blank!@"'
 408 			    		}
 409 			if defname:
 410 				sargs[c] = '%s\ndefine "%s" %s%s' % (sargs[c], defname, col_names[c], r)
 411 			c=c+1
 412 		result.append(sargs)
 413 		r=r+1
 414 
 415 	return(result,formats)
 416 
 417 class Parser:
 418 
 419         extensions = ['.sc']
 420 
 421 	def __init__(self, raw, request, **kw):
 422 
 423         	self.raw = raw
 424         	self.request = request
 425         	self.form = request.form
 426         	self._ = request.getText
 427 		self.kw = []
 428 		for arg in kw.get('format_args','').split():
 429 		   self.kw.append(arg)
 430 
 431 
 432 
 433 
 434 
 435 	def format(self, formatter):
 436 
 437 		lines = self.raw.split('\n')
 438 
 439 
 440 		kw=self.kw
 441 
 442     		column_header=0
 443     		row_header=0
 444     		show_formular=0
 445     		format=''
 446 
 447     		zt=0
 448 
 449     		for test in kw:
 450       			if (test == '-column_header') : column_header=1
 451       			if (test == '-row_header') : row_header=1
 452       			if (test == '-show_formular') : show_formular=1
 453       			if (test == '-format') : format=string.split(kw[zt+1],",")
 454       			zt=zt+1
 455 
 456 
 457     		matrix = []
 458 
 459     		textstr = '\n'.join(lines).strip()
 460 
 461     		tmpname = re.sub('\s+', ' ', textstr)
 462     		tmpname = sha.new(tmpname).hexdigest()
 463     		tmpname = tmpname + "_sc"
 464     		tmpfile = "%s/%s.sc" % (config_sc_vartmp_dir, tmpname)
 465 
 466 		textstr, formats=table2sc(lines,format,show_formular,self.request)
 467 
 468 
 469     		data = open(tmpfile, "w")
 470     		i=0
 471     		for txt in textstr:
 472         		if (len(textstr[i]) > 0):
 473             			tmpstr=string.join(textstr[i],'\n')
 474 			        data.write('%s\n' % (tmpstr))
 475         		i=i+1
 476     		data.close()
 477 
 478     		cmd = "%(external_sc)s %(argument)s %(file)s " % {
 479              		"external_sc": config_external_sc,
 480              		"argument": " -W% ",
 481 	     		"file":tmpfile
 482           		}
 483 
 484 
 485     		f=os.popen(cmd,'r')# popen to get the result of the calculation
 486     		result=f.readlines()
 487     		f.flush()
 488     		os.unlink(tmpfile) # remove the tmpfile
 489 
 490     		right_format='<)>'
 491     		left_format='<(>'
 492 
 493     		for txt in result:
 494       			txt=string.join(txt,'')
 495       			cells=string.split(txt,' ')
 496       			zres=[]
 497       			c=1
 498       			for value in cells:
 499         			value=string.join(value,'')
 500         			value=string.strip(value)
 501         			strlen=len(value)
 502 
 503 				if (strlen > 0) :
 504 					v = ord(value[0])
 505 	   				if ord('a')<=v<=ord('z') or ord('A')<=v<=ord('Z') or value[0] in " ":
 506 						format=left_format
 507 	   				else:
 508 	      					if (show_formular == 0) :
 509 	         					format=right_format
 510 	      					else:
 511               			 			format=left_format
 512 
 513            				if (value == "Blank!@"):
 514 						value=" "
 515 
 516 	   				if string.find(value,"Blank!@"):
 517 	      					value=string.replace(value,"Blank!@", " ")
 518 
 519 					zres.append( [format, value] )
 520 
 521 					c=c+1
 522       			matrix.append(zres)
 523 			# post formatting of cells according to cut out wiki format strings
 524     			# - matrix is here: [ [  [format,content], ... ], ... ]
 525     		for cellname, cellformat in formats.items():
 526         		row,col = cellname
 527         		m = matrix[row][col-1]
 528 			m[0] = cellformat
 529     		matrix = map(lambda row: "||" + "||".join([f+c for f,c in row]) + "||", matrix)
 530 
 531     		# post processing of over all matrix
 532     		# - matrix is here: [ "||cell||cell||...||", "||cell||...||", ... ]
 533 
 534     		if (len(matrix[0]) == 2):  # das ist noch nicht die beste Loesung (workaround)
 535        			matrix=matrix[1:]
 536 
 537     		if (row_header == 1):
 538       			y=[]
 539       			r=len(matrix)
 540       			lines=range(r)
 541 
 542       			i=0
 543 
 544       			for no in lines:
 545 			        matrix[i]="||<)5%#CCCCCC>'''"+str(no)+"'''"+matrix[i]
 546 		 		i=i+1
 547 
 548     		if (column_header == 1):
 549        			col_names=' ABCDEFGHIJKLMNOPQRSTUVWXYZ'
 550        			x='||'
 551        			if(row_header == 1):
 552           			start=0
 553        			else:
 554           			start=1
 555        			for name in col_names[start:c]:
 556            			x = x + "%(format)s %(value)s %(tab)s" % {
 557 	                    		"format": '<:#CCCCCC>',
 558 	                    		"value": "'''"+name+"'''",
 559 		            		"tab":'||'}
 560 
 561        			matrix.insert(0,x)
 562 
 563 		wikiizer = wiki.Parser(string.join(matrix,"\n"),self.request) # parser for wiki tabular
 564 		wikiizer.format(formatter)