diff options
Diffstat (limited to 'interoperability')
| -rw-r--r-- | interoperability/__init__.py | 1 | ||||
| -rw-r--r-- | interoperability/__init__.pyo | bin | 0 -> 165 bytes | |||
| -rw-r--r-- | interoperability/lotus_wk4.py | 291 | ||||
| -rw-r--r-- | interoperability/lotus_wk4.pyo | bin | 0 -> 4727 bytes | |||
| -rw-r--r-- | interoperability/xls/__init__.py | 1 | ||||
| -rw-r--r-- | interoperability/xls/__init__.pyo | bin | 0 -> 169 bytes | |||
| -rw-r--r-- | interoperability/xls/compound.py | 127 | ||||
| -rw-r--r-- | interoperability/xls/compound.pyo | bin | 0 -> 3718 bytes | |||
| -rw-r--r-- | interoperability/xls/convert_to_scalcstring.py | 372 | ||||
| -rw-r--r-- | interoperability/xls/convert_to_scalcstring.pyo | bin | 0 -> 7959 bytes | |||
| -rw-r--r-- | interoperability/xls/function.py | 246 | ||||
| -rw-r--r-- | interoperability/xls/function.pyo | bin | 0 -> 8142 bytes | |||
| -rw-r--r-- | interoperability/xls/function.txt | 249 | ||||
| -rw-r--r-- | interoperability/xls/workbook.py | 432 | ||||
| -rw-r--r-- | interoperability/xls/workbook.pyo | bin | 0 -> 14566 bytes |
15 files changed, 1719 insertions, 0 deletions
diff --git a/interoperability/__init__.py b/interoperability/__init__.py new file mode 100644 index 0000000..2ae2839 --- /dev/null +++ b/interoperability/__init__.py @@ -0,0 +1 @@ +pass diff --git a/interoperability/__init__.pyo b/interoperability/__init__.pyo Binary files differnew file mode 100644 index 0000000..e73f1a9 --- /dev/null +++ b/interoperability/__init__.pyo diff --git a/interoperability/lotus_wk4.py b/interoperability/lotus_wk4.py new file mode 100644 index 0000000..9dacd58 --- /dev/null +++ b/interoperability/lotus_wk4.py @@ -0,0 +1,291 @@ +import struct +# Ability to read single sheet Lotus .wk4 files in SocialCalc added. Developed by Vijit Singh, +# Software Engineer at SEETA (http://seeta.in) under the guidance of Manusheel Gupta. +# For getting involved in the development of SocialCalc on Sugar, or on any questions related to it, please e-mail at socialcalc<dot>sugar<at>seeta<dot>in. +# For posting a feature request/bug, please use http://testtrack.seeta.in. +# File Author - Vijit Singh + +def col_no_to_alphabet(no): + + alpha_1=chr(ord('A')+(no/26)-1) + alpha_0=chr(ord('A')+(no%26)) + + #print 'no received is',no + + if no/26==0: + return alpha_0 + + else: + return alpha_1+alpha_0 + + + + + + +def wk4_to_scalc(byte_string): + i=0 # pointer to the character to be read + l=len(byte_string) # length of the byte array + # print byte_string + + #### Creating the strings to be sent back as result- converted strings###### + + result_string='' # main string + sheet_string=''## I think this string contains the values of various cells and it is what is loaded into the spreadsheet + edit_string='' ## This string tells the positions(parts) of spreadsheet which are visible at the time of saving, I mean the cell at which mouse was there et al + audit_string='' ## it contains the actions which were taken, it is req. to maintain undo stack, not sure whether req at this juncture, still adding.... + ## ....becoz don't know whether spreadsheet can be loaded without it or not. + multipartBoundary_string="SocialCalcSpreadsheetControlSave" + + #### Initiating some of these strings###### + sheet_string+='version:1.5\n' + + + #### Some constants which will be used during string preparation###### + add_center_string_flag=False ## These 2 flags take care whether we should add the following string cellformat:2:right or cellformat:1:middle in the + add_right_string_flag=False ## sheet_string, we might not require them over the time, and might just add these strings everytime. + + max_col=0 ## Takes care of the max. value of row and col till which sheet is there, max dimensions of the sheet + max_row=0 + + while(i<l): + + #read instantly the header on the basis of the value of i + header=byte_string[i:i+4] + + #type + record_type=header[0:2] + # print record_type + # print 'should be equal to',"\x16\x00" + + #length + record_length=header[2:4] + r_length=struct.unpack("i",record_length[0:2]+"\x00\x00") # converting it to int value + r_length=r_length[0] + + i=i+4 + + #fetching the record body and incrementing i + record_body=byte_string[i:i+r_length] # as r_length is a tuple + + i=i+r_length + + + + ######### Starting the switch cases- if elif in python to test the record_type and take appropriate actions############## + + ## testing it for BOF + if record_type=="\x00\x00": ## NOTE: not doing anything for now, will take steps when complexity will inc + #print 'it is BOF and the length is ',r_length + + ## To decode the max dimensions of the sheet + if max_row==0: # so that we are not setting it again when bof is encountered again, might also check the length instead + max_row=(struct.unpack("i",record_body[8]+record_body[9]+"\x00\x00")[0])+1 ## it reverses on its own + max_col=struct.unpack("i",record_body[11:12]+"\x00\x00\x00")[0] + max_col=col_no_to_alphabet(max_col) + + pass + + ## testing it for TREAL nos. + elif record_type=="\x17\x00": + + #print 'in real no test, should be equal to',"\x16\x00" + + # To deode row and column + + row=struct.unpack("i",record_body[0:2]+"\x00\x00")[0] + row=row+1 # as row starts from 1 but it returns according to index 0 , so to compensate + worksheet=struct.unpack("i",record_body[2:3]+"\x00\x00\x00")[0] + if worksheet>0: # NOTE: Decodes only the first spreadsheet in case multiple worksheets are present; need to find a proper solution for this + break + col=struct.unpack("i",record_body[3:4]+"\x00\x00\x00")[0] + col_alphabetical=col_no_to_alphabet(col) + + #print "row ",row,"worksheet ",worksheet,"col ",col + + record_body=record_body[4:len(record_body)] + + + record_body=record_body[::-1] ## reverse the hex string leaving each byte intact + + exponent=struct.unpack("i",record_body[1:2]+record_body[0:1]+"\x00\x00") + exponent=exponent[0] + #print "exponent is",exponent + sign=exponent & 32768 ## because 0x8000= 32768 ## getting the sign bit + exponent=exponent & 32767 + + mantissa1=struct.unpack("i",record_body[3:4]+record_body[2:3]+"\x00\x00") + mantissa2=struct.unpack("i",record_body[5:6]+record_body[4:5]+"\x00\x00") + mantissa3=struct.unpack("i",record_body[7:8]+record_body[6:7]+"\x00\x00") + mantissa4=struct.unpack("i",record_body[9:10]+record_body[8:9]+"\x00\x00") + mantissa1=mantissa1[0] + mantissa2=mantissa2[0] + mantissa3=mantissa3[0] + mantissa4=mantissa4[0] +## print 'man1=',mantissa1 +## print 'man2=',mantissa2 + + + mantissa_value=1.0 + mantissa1=mantissa1<<1 # to remove the not needed 1st one + #mantissa1>>1 + + for j in range(1,15): + #print "mantissa is ",mantissa1<<1 + #print + + bit=(mantissa1)&32768 + mantissa1=mantissa1<<1 +## print 'bit is',bit + if bit==32768: + # print 'in mantissa addn' + # print (1.0/pow(2,j)) + mantissa_value+=(1.0/pow(2,j)) + + + for j in range(1,16): + + bit=(mantissa2)&32768 + mantissa2=mantissa2<<1 +## print 'bit is',bit + if bit==32768: + mantissa_value+=(1.0/pow(2,15+j)) ## As, the distance should be more now + + for j in range(1,16): + bit=(mantissa3)&32768 + mantissa3=mantissa3<<1 +## print 'bit is',bit + if bit==32768: + mantissa_value+=(1.0/pow(2,31+j)) ## As, the distance should be more now + + for j in range(1,16): + bit=(mantissa4)&32768 + mantissa4=mantissa4<<1 +## print 'bit is',bit + if bit==32768: + mantissa_value+=(1.0/pow(2,47+j)) ## As, the distance should be more now + + # print mantissa_value + # Calculating the value of the TREAL number + #print "sign ",pow(-1,sign)," exp ",pow(2,(exponent-16383))," manti ",mantissa_value + treal_no=pow(-1,sign)*pow(2,(exponent-16383))*(mantissa_value) + + + ############################ Saving the no and its coordinates in the string format####################################### + #### The format for sheet string would be cell:A1:v:1 ,i.e. cell:colrow:v:value_at_that_cell + #### The format for audit string would be set AB24 value n 333.9898989, i.e. set colrow value n value_at_that_cell + + sheet_string+='cell:'+str(col_alphabetical)+str(row)+':v:'+str(treal_no)+'\n' + audit_string+='set '+str(col_alphabetical)+str(row)+' value n '+str(treal_no)+'\n' + + #print " no is ",treal_no," present at : row ",row,"worksheet ",worksheet,"col ",col + + ### for testing labels (strings) + elif record_type=="\x16\x00": + + # To deode row and column + + row=struct.unpack("i",record_body[0:2]+"\x00\x00")[0] + row=row+1 # as row starts from 1 but it returns according to index 0 , so to compensate + worksheet=struct.unpack("i",record_body[2:3]+"\x00\x00\x00")[0] + if worksheet>0: + break + col=struct.unpack("i",record_body[3:4]+"\x00\x00\x00")[0] + col_alphabetical=col_no_to_alphabet(col) + #print "row ",row,"worksheet ",worksheet,"col ",col + + record_body=record_body[4:len(record_body)] + + ## justification + #print "just as such is ",record_body[0] + justification=ord(record_body[0]) + + ##label +# label=record_body[1:len(record_body)] ## NOTE: using this assignment will transfer the hex-string itself to the browser to display. In case, + ## browser gives error to display it , comment this line and uncomment the loop below + label='' + for j in range(1,len(record_body)-1): ## seems to be needed. + + c=chr(ord(record_body[j])) + if c==':': # IMP: because these strings are used in javascript part of socialcalc for other purposes + c='\c' + elif c=='\\': + c='\\b' ## NOTE: made changes here according to .xls, just in case lotus interoperability shows some kind of problem, reverse this change + elif c=='\n': + c='\\n' + label+=c + + ############################ Saving the no and its coordinates in the string format####################################### + #### The format for sheet string would be cell:A1:v:1 ,i.e. cell:colrow:v:value_at_that_cell + #### The format for audit string would be set AB24 value n 333.9898989, i.e. set colrow value n value_at_that_cell + + sheet_string+='cell:'+str(col_alphabetical)+str(row)+':t:'+label + if justification=="'": + sheet_string+='\n' + elif justification=='^': + sheet_string+=':cf:1\n' + audit_string+='set '+str(col_alphabetical)+str(row)+' cellformat center'+'\n' # this line is added afterwards, so incase the code creates some problem , check this line + add_center_string_flag=True + elif justification=='"': + sheet_string+=':cf:2\n' + audit_string+='set '+str(col_alphabetical)+str(row)+' cellformat right'+'\n' # this line is added afterwards, so incase the code creates some problem , check this line + + add_right_string_flag=True + else : + sheet_string+='\n' + audit_string+='set '+str(col_alphabetical)+str(row)+' value t '+label+'\n' + + #print "just ",justification," label ",label, " present at : row ",row,"worksheet ",worksheet,"col ",col + + + + ###### Finalizing the strings before returning######### + sheet_string+='sheet:c:'+str(max_col)+':r:'+str(max_row)+'\n' + if add_center_string_flag: + sheet_string+='cellformat:1:center\n' + if add_right_string_flag: + sheet_string+='cellformat:2:right\n' + + ## This edit_string is made like this just for now, over the time, atleast the sort thing would be needed to be dynamically assigned. + ## The colpane, ecell and rowpane, need not be changed because, it tells when to keep the focus when restarted, and we don't have any such requirements. + + edit_string='version:1.0\nrowpane:0:1:26\ncolpane:0:1:10\necell:A4\nsort::-1:up::::\ngraph:range:undefined:type::minmax:undefined,undefined,undefined,undefined' + + result_string+="socialcalc:version:1.0\n" + "MIME-Version: 1.0\nContent-Type: multipart/mixed; boundary="+multipartBoundary_string+ "\n" +\ + "--" + multipartBoundary_string + "\nContent-type: text/plain; charset=UTF-8\n\n" +\ + "# SocialCalc Spreadsheet Control Save\nversion:1.0\npart:sheet\npart:edit\npart:audit\n" +\ + "--" + multipartBoundary_string + "\nContent-type: text/plain; charset=UTF-8\n\n" +\ + sheet_string +\ + "--" + multipartBoundary_string + "\nContent-type: text/plain; charset=UTF-8\n\n" +\ + edit_string +\ + "--" + multipartBoundary_string + "\nContent-type: text/plain; charset=UTF-8\n\n" +\ + audit_string +\ + "--" + multipartBoundary_string + "--\n" + + return result_string + + + + + + +######## For debugging and testing purposes only, the following code should be commented when using in sync with the socialcalc code############# + + +def main(): + a="\x17\x00\x0e\x00\x2f\x00\x01\x01\x84\x12\x64\x1b\x7b\xf4\x06\xca\x05\x40\x16\x00\x0d\x00\x2f\x00\x01\x01\x27\x20\x42\x41\x53\x4b\x45\x54\x00" + #a="\x17\x00\x0a\x00\xcb\xa1\x45\xb6\xf3\x7d\x9c\xad\x0c\x40" + #f=open('check5.wk4','rb') + #a=f.read() + s=wk4_to_scalc(a) + print s + #f.close() + #print "no is", no + + +#main() + + + + + diff --git a/interoperability/lotus_wk4.pyo b/interoperability/lotus_wk4.pyo Binary files differnew file mode 100644 index 0000000..68f6a30 --- /dev/null +++ b/interoperability/lotus_wk4.pyo diff --git a/interoperability/xls/__init__.py b/interoperability/xls/__init__.py new file mode 100644 index 0000000..2ae2839 --- /dev/null +++ b/interoperability/xls/__init__.py @@ -0,0 +1 @@ +pass diff --git a/interoperability/xls/__init__.pyo b/interoperability/xls/__init__.pyo Binary files differnew file mode 100644 index 0000000..e9c71cf --- /dev/null +++ b/interoperability/xls/__init__.pyo diff --git a/interoperability/xls/compound.py b/interoperability/xls/compound.py new file mode 100644 index 0000000..13c02f3 --- /dev/null +++ b/interoperability/xls/compound.py @@ -0,0 +1,127 @@ +#from os import abort +def hexa(s): # return the hexadecimal euvalent of sring + p=1 + ans=0 + for c in s: + ans=ans+p*ord(c) + p*=256 + return ans +def integer(s): # return the integer eqvalent in big-endian signed format + p=1 + ans=0 + for c in s: + ans=ans+p*ord(c) + p*=256 + if ord(s[-1])<=0x7f:return ans + return int(-1*((256**len(s))-ans)) + +def offset(sid, sec_size): + return 512 + sec_size*sid # 512 header size + +def readname(st): + b=integer(st[64:66]) - 2 + return st[:b:2] + +def soffset(sid, short_sec_size): + return short_sec_size*sid + +def return_wbk(s): + if hexa(s[:8])!=0xe11ab1a1e011cfd0: # unique indentifier for CDFF + return 'file not in correct format' + + # reading the header + sec_size = 2**integer(s[30:32]) # typically 512 + short_sec_size = 2**integer(s[32:34]) # typiclly 64 + sat_sectors = integer(s[44 : 48]) + dir_secid = integer(s[48 : 52]) + standard_strm_size = integer(s[56 : 60]) + ssat_secid = integer(s[60 : 64]) + ssat_sectors=integer(s[64 : 68]) + msat_secid = integer(s[68 : 72]) + msat_sectors = integer(s[72 : 76]) + init_msat = s[76 : 512] + + + # building MSAT + msat=[] + for i in range(0, 436, 4): + msat+=[integer(init_msat[i : i+4])] + for i in range(0, msat_sectors): + t=offset(msat_secid, sec_size) + sec=s[t : t+sec_size] + for j in range(0, sec_size-4, 4): + msat+=[integer(sec[j : j+4])] + msat_secid=integer(sec[508 : 512]) + + #building SAT + sat=[] + for i in range (0, sat_sectors): + t = offset(msat[i], sec_size) + sec = s[t : t+sec_size] + for j in range(0, sec_size, 4): + sat+=[integer(sec[j : j+4])] + + + #building SSAT + ssat=[] + for i in range (0, ssat_sectors): + t = offset(ssat_secid, sec_size) + if t==-2:break + sec = s[t : t+sec_size] + for j in range(0, sec_size, 4): + ssat+=[integer(sec[j : j+4])] + ssat_secid=sat[ssat_secid] + + + # building directory stream + dir_stream='' # size of this stream is stored in root entry + while dir_secid!=-2: + t = offset(dir_secid, sec_size) + dir_stream += s[t : t+sec_size] + dir_secid=sat[dir_secid] + + + + # reading dir_stream + + + # (1) reading root storage entry, only data of use is scaned + rootname_len=integer(dir_stream[64:66]) + rootname = dir_stream[:rootname_len] + rootnode_dirid = integer(dir_stream[76 : 80]) + sstream_container_secid = integer(dir_stream[116 : 120]) + sstream_size = integer(dir_stream[120 : 124]) + + sstream_container='' #building short sector stream container + while sstream_container_secid!=-2: + t=offset(sstream_container_secid, sec_size) + sstream_container += s[t : t+ sec_size] + sstream_container_secid=sat[sstream_container_secid] + + # (2) Reaching to workbook directory + dir_entry_count = len(dir_stream)/128 + for i in range(1, dir_entry_count): + dir_entry = dir_stream[i*128 : (i+1)*128] + if readname(dir_entry) == 'Workbook': + wbk_entry = dir_entry + break + + # (3) Reading the workbook entry + wbk_size = integer(wbk_entry[120 : 124]) + sec_id = integer(wbk_entry[116 : 120]) + wbk = '' + if wbk_size < standard_strm_size: # wbk is saved in short stream container + while sec_id != -2: + t = soffset(sec_id, short_sec_size) + wbk += sstream_container[t : t + short_sec_size] + sec_id = ssat[sec_id] + + else: + while sec_id != -2: + t = offset(sec_id, sec_size) + wbk += s[t : t + sec_size] + sec_id = sat[sec_id] + return wbk + + # wbk is the user stresm (workbook + diff --git a/interoperability/xls/compound.pyo b/interoperability/xls/compound.pyo Binary files differnew file mode 100644 index 0000000..7f2ecf1 --- /dev/null +++ b/interoperability/xls/compound.pyo diff --git a/interoperability/xls/convert_to_scalcstring.py b/interoperability/xls/convert_to_scalcstring.py new file mode 100644 index 0000000..66cd99d --- /dev/null +++ b/interoperability/xls/convert_to_scalcstring.py @@ -0,0 +1,372 @@ +# Ability to read single sheet Excel .xls files in SocialCalc added. Developed by Mahesh Chand Sharma from SEETA (http://seeta.in) with pointers from Vijit Singh, +# Software Engineer at SEETA (http://seeta.in) under the guidance of Manusheel Gupta. +# For getting involved in the development of SocialCalc on Sugar, or on any questions related to it, please e-mail at socialcalc<dot>sugar<at>seeta<dot>in. +#For posting a feature request/bug, please use http://testtrack.seeta.in. +# File Author - Vijit Singh + + +#### Creating the strings to be sent back as result- converted strings###### + +result_string='' # main string +sheet_string=''## I think this string contains the values of various cells and it is what is loaded into the spreadsheet +edit_string='' ## This string tells the positions(parts) of spreadsheet which are visible at the time of saving, I mean the cell at which mouse was there et al +audit_string='' ## it contains the actions which were taken, it is req. to maintain undo stack, not sure whether req at this juncture, still adding.... + ## ....becoz don't know whether spreadsheet can be loaded without it or not. +multipartBoundary_string="SocialCalcSpreadsheetControlSave" + + +# dictionaries and list for maintianig the format_order and which of the formats are to be included +font_align={} +font_align_sorted=[] +ver_align={'bottom':0,'middle':0,'top':0} +ver_align_sorted=[] +hor_align={'center':0,'left':0,'right':0} +hor_align_sorted=[] + +def col_no_to_alphabet(no): + + alpha_1=chr(ord('A')+(no/26)-1) + alpha_0=chr(ord('A')+(no%26)) + + #print 'no received is',no + + if no/26==0: + return alpha_0 + + else: + return alpha_1+alpha_0 + + +def add_cell_format(format_info,font_size,r,col_alphabetical): + + global sheet_string + global audit_string + + font_family='' # NULL for now will take the value accordingly when code would be written for it + + font_changed=False + if font_size!=10: + font_changed=True + if font_size==26: + font_size=24 + + + + # checking vertical alignment + vert_align_chars=format_info[5:7] + if vert_align_chars!='00': + if vert_align_chars=='01': # top + if ver_align['top']==0: + local_len=len(ver_align_sorted) + ver_align_sorted.append('top') + local_len+=1 + ver_align['top']=local_len + + sheet_string+=':l:'+str(ver_align['top']) + audit_string+='set '+str(col_alphabetical)+str(r+1)+' layout padding:* * * *;vertical-align:top;'+'\n' + + elif vert_align_chars=='10': # middle + if ver_align['middle']==0: + local_len=len(ver_align_sorted) + ver_align_sorted.append('middle') + local_len+=1 + ver_align['middle']=local_len + + sheet_string+=':l:'+str(ver_align['middle']) + audit_string+='set '+str(col_alphabetical)+str(r+1)+' layout padding:* * * *;vertical-align:middle;'+'\n' + + elif vert_align_chars=='11': # bottom + if ver_align['bottom']==0: + local_len=len(ver_align_sorted) + ver_align_sorted.append('bottom') + local_len+=1 + ver_align['bottom']=local_len + + sheet_string+=':l:'+str(ver_align['bottom']) + audit_string+='set '+str(col_alphabetical)+str(r+1)+' layout padding:* * * *;vertical-align:bottom;'+'\n' + + + # checking font + font_chars=format_info[0:2] # 0th for bold and 1st for italic + if font_chars!='00': # normal font_type + + font_string=make_font_string(font_family,'',font_size) + if font_string not in font_align: + local_len=len(font_align_sorted) + font_align_sorted.append(font_string) + local_len+=1 + font_align[font_string]=local_len + + sheet_string+=':f:'+str(font_align[font_string]) + audit_string+='set '+str(col_alphabetical)+str(r+1)+' font '+font_string+'\n' + + if font_chars=='01': # italic + + font_string=make_font_string(font_family,'italic normal',font_size) + if font_string not in font_align: + local_len=len(font_align_sorted) + font_align_sorted.append(font_string) + local_len+=1 + font_align[font_string]=local_len + + sheet_string+=':f:'+str(font_align[font_string]) + audit_string+='set '+str(col_alphabetical)+str(r+1)+' font '+font_string+'\n' + + elif font_chars=='10': # bold + font_string=make_font_string(font_family,'normal bold',font_size) + if font_string not in font_align: + local_len=len(font_align_sorted) + font_align_sorted.append(font_string) + local_len+=1 + font_align[font_string]=local_len + + sheet_string+=':f:'+str(font_align[font_string]) + audit_string+='set '+str(col_alphabetical)+str(r+1)+' font '+font_string+'\n' + + + elif font_chars=='11': # italic bold + font_string=make_font_string(font_family,'italic bold',font_size) + if font_string not in font_align: + local_len=len(font_align_sorted) + font_align_sorted.append(font_string) + local_len+=1 + font_align[font_string]=local_len + + sheet_string+=':f:'+str(font_align[font_string]) + audit_string+='set '+str(col_alphabetical)+str(r+1)+' font '+font_string+'\n' + + + # checking horizontal alignment + hor_align_chars=format_info[3:5] # 01 for left, 10 for center and 11 for right + if hor_align_chars!='00': + if hor_align_chars=='01': # left + if hor_align['left']==0: + local_len=len(hor_align_sorted) + hor_align_sorted.append('left') + local_len+=1 + hor_align['left']=local_len + + sheet_string+=':cf:'+str(hor_align['left']) + audit_string+='set '+str(col_alphabetical)+str(r+1)+' cellformat left'+'\n' + + elif hor_align_chars=='10': # center + if hor_align['center']==0: + local_len=len(hor_align_sorted) + hor_align_sorted.append('center') + local_len+=1 + hor_align['center']=local_len + + sheet_string+=':cf:'+str(hor_align['center']) + audit_string+='set '+str(col_alphabetical)+str(r+1)+' cellformat center'+'\n' + + elif hor_align_chars=='11': # right + if hor_align['right']==0: + local_len=len(hor_align_sorted) + hor_align_sorted.append('right') + local_len+=1 + hor_align['right']=local_len + + sheet_string+=':cf:'+str(hor_align['right']) + audit_string+='set '+str(col_alphabetical)+str(r+1)+' cellformat right'+'\n' + + +def make_font_string(font_family,font_type,font_size): + font_string='' + if font_type=='': + font_string+='* ' + else: + font_string+=font_type+' ' + + if font_size==10: + font_string+='* ' + else: + font_string+=str(font_size)+'pt ' + + if font_family=='': + font_string+='*' + else: + font_string+=font_family + + return font_string + + +def add_cell_format_abbr(): + + global sheet_string + global audit_string + + # adding abbreviations for horizontal alignment + for i in range(len(hor_align_sorted)): + sheet_string+='cellformat:'+str(i+1)+':'+hor_align_sorted[i]+'\n' + + # adding abbreviations for font + for i in range(len(font_align_sorted)): + sheet_string+='font:'+str(i+1)+':'+font_align_sorted[i]+'\n' + + # adding abbreviations for vertical alignment + for i in range(len(ver_align_sorted)): + sheet_string+='layout:'+str(i+1)+':padding:* * * *;vertical-align:'+ver_align_sorted[i]+';\n' + + +def add_col_width(workbook_col_widths,sheet_no): + + global sheet_string + global audit_string + + for c in range(len(workbook_col_widths[sheet_no])): + + if workbook_col_widths[sheet_no][c]!=8: + col_alphabetical=str(col_no_to_alphabet(c)) + width=str((workbook_col_widths[sheet_no][c])*10) + sheet_string+='col:'+col_alphabetical+':w:'+width+'\n' + audit_string+='set '+ col_alphabetical+' width '+width+'\n' + +def workbook_data_to_scalc_string(workbook_data,workbook_col_widths,sheet_no=0): + ## the sheet_no starts from 0 with the 1st sheet placed at 0th position, and it denotes the sheet whose data is to be shown + + + global result_string + global sheet_string + global audit_string + global edit_string + global multipartBoundary_string + + #### Initiating some of these strings###### + sheet_string+='version:1.5\n' + + #### Some constants which will be used during string preparation###### + add_center_string_flag=False ## These 2 flags take care whether we should add the following string cellformat:2:right or cellformat:1:middle in the + add_right_string_flag=False ## sheet_string, we might not require them over the time, and might just add these strings everytime. + + + max_col=0 ## Takes care of the max. value of row and col till which sheet is there, max dimensions of the sheet + max_row=0 + + ## to know the max rows and col + max_row=len(workbook_data[sheet_no]) + max_col=len(workbook_data[sheet_no][max_row-1]) + + + for r in range(len(workbook_data[sheet_no])): + for c in range(len(workbook_data[sheet_no][r])): + info=workbook_data[sheet_no][r][c] + if info==(): # for empty cell + continue + elif info[1]=='i': # for integer + ############################ Saving the no and its coordinates in the string format####################################### + #### The format for sheet string would be cell:A1:v:1 ,i.e. cell:colrow:v:value_at_that_cell + #### The format for audit string would be set AB24 value n 333.9898989, i.e. set colrow value n value_at_that_cell + + col_alphabetical=col_no_to_alphabet(c) + sheet_string+='cell:'+str(col_alphabetical)+str(r+1)+':v:'+str(info[0]) ## here the row needs to be incremented by 1 as it starts from 1 in scalc + audit_string+='set '+str(col_alphabetical)+str(r+1)+' value n '+str(info[0])+'\n' + + elif info[1]=='f': # for float, same as integer + ############################ Saving the no and its coordinates in the string format####################################### + #### The format for sheet string would be cell:A1:v:1 ,i.e. cell:colrow:v:value_at_that_cell + #### The format for audit string would be set AB24 value n 333.9898989, i.e. set colrow value n value_at_that_cell + + col_alphabetical=col_no_to_alphabet(c) + sheet_string+='cell:'+str(col_alphabetical)+str(r+1)+':v:'+str(info[0]) ## here the row needs to be incremented by 1 as it starts from 1 in scalc + audit_string+='set '+str(col_alphabetical)+str(r+1)+' value n '+str(info[0])+'\n' + + elif info[1]=='s': # for string + + + col_alphabetical=col_no_to_alphabet(c) + # replacing the character ':' with '\c' and '\\' with '\\b' and '\n' with '\\n' as these characters are used internally in socialcalc + label='' + for j in range(len(info[0])): ## seems to be needed. + + ch=chr(ord(info[0][j])) + if ch==':': # IMP: because these strings are used in javascript part of socialcalc for other purposes + ch='\c' + elif ch=='\\': + ch='\\b' + elif ch=='\n': + ch='\\n' + label+=ch + + + sheet_string+='cell:'+str(col_alphabetical)+str(r+1)+':t:'+label + audit_string+='set '+str(col_alphabetical)+str(r+1)+' value t '+label+'\n' + + + elif info[1]=='for': #for formula + + col_alphabetical=col_no_to_alphabet(c) + formula_type=info[3] + formula_string2=info[4] + formula_result=info[0] + + formula_string='' + for j in range(len(formula_string2)): ## seems to be needed. + + ch=chr(ord(formula_string2[j])) + if ch==':': # IMP: because these strings are used in javascript part of socialcalc for other purposes + ch='\c' + elif ch=='\\': + ch='\\b' + elif ch=='\n': + ch='\\n' + formula_string+=ch + + if formula_type=='f' or formula_type=='i': + formula_type='n' + elif formula_type=='s': + formula_type='t' + elif formula_type=='b': + formula_type='nl' + elif formula_type=='e': + formula_type='e'+str(formula_result) + formula_string+=':e:'+str(formula_result) + formula_result='' + sheet_string+='cell:'+str(col_alphabetical)+str(r+1)+':vtf:'+formula_type+':'+str(formula_result)+':'+formula_string ## TODO: code for date/time fns + audit_string+='set '+str(col_alphabetical)+str(r+1)+' formula '+formula_string+'\n' + + + + ## call to extract format information for each cell + add_cell_format(info[2][0],info[2][1],r,col_alphabetical) + + # adding '\n' at the last of the sheet_string since it is required for each cell + sheet_string+='\n' + + ####### End of looping over each cell element##### + + ###### Finalizing the strings before returning######### + + # to add the width of the columns + add_col_width(workbook_col_widths,sheet_no) + + # to add the no. of columns and rows in the sheet + sheet_string+='sheet:c:'+str(max_col)+':r:'+str(max_row)+'\n' + + # to add abbreviations of the type of format used + add_cell_format_abbr() + + ## This edit_string is made like this just for now, over the time, atleast the sort thing would be needed to be dynamically assigned. + ## The colpane, ecell and rowpane, need not be changed because, it tells when to keep the focus when restarted, and we don't have any such requirements. + + edit_string='version:1.0\nrowpane:0:1:26\ncolpane:0:1:10\necell:A4\nsort::-1:up::::\ngraph:range:undefined:type::minmax:undefined,undefined,undefined,undefined' + + result_string+="socialcalc:version:1.0\n" + "MIME-Version: 1.0\nContent-Type: multipart/mixed; boundary="+multipartBoundary_string+ "\n" +\ + "--" + multipartBoundary_string + "\nContent-type: text/plain; charset=UTF-8\n\n" +\ + "# SocialCalc Spreadsheet Control Save\nversion:1.0\npart:sheet\npart:edit\npart:audit\n" +\ + "--" + multipartBoundary_string + "\nContent-type: text/plain; charset=UTF-8\n\n" +\ + sheet_string +\ + "--" + multipartBoundary_string + "\nContent-type: text/plain; charset=UTF-8\n\n" +\ + edit_string +\ + "--" + multipartBoundary_string + "\nContent-type: text/plain; charset=UTF-8\n\n" +\ + audit_string +\ + "--" + multipartBoundary_string + "--\n" + + return result_string + + + + + + + + diff --git a/interoperability/xls/convert_to_scalcstring.pyo b/interoperability/xls/convert_to_scalcstring.pyo Binary files differnew file mode 100644 index 0000000..36d16a7 --- /dev/null +++ b/interoperability/xls/convert_to_scalcstring.pyo diff --git a/interoperability/xls/function.py b/interoperability/xls/function.py new file mode 100644 index 0000000..4bef80b --- /dev/null +++ b/interoperability/xls/function.py @@ -0,0 +1,246 @@ +# Ability to read single sheet Excel .xls files in SocialCalc added. Developed by Mahesh Chand Sharma from SEETA (http://seeta.in) with pointers from Vijit Singh, +# Software Engineer at SEETA (http://seeta.in) under the guidance of Manusheel Gupta. +# For getting involved in the development of SocialCalc on Sugar, or on any questions related to it, please e-mail at socialcalc<dot>sugar<at>seeta<dot>in. +# For posting a feature request/bug, please use http://testtrack.seeta.in. +# File Author - Mahesh Chand Sharma + + +from sugar.activity import activity +from compound import integer, hexa +global err_value +err_value = ['']*(0x2A +1) +err_value[0x00] = '#NULL!' +err_value[0x07] = '#DIV/0!' +err_value[0x0F] = '#VALUE' +err_value[0x17] = '#REF!' +err_value[0x1D] = '#NAME?' +err_value[0x24] = '#NUM!' +err_value[0x2A] = '#N/A!' +def read_string(wbk, pos, len_size): + save=pos + ln=integer(wbk[pos:pos+len_size]) + pos+=len_size + flag=integer(wbk[pos:pos+1]) + pos+=1 + rt = 0 + sz = 0 + if flag&8==8: + rt = integer(wbk[pos : pos+2]) + pos+=2 + if flag&4==4: + sz = integer(wbk[pos : pos+4]) + pos+=4 + if flag&1==1: + w = wbk[pos:pos+2*ln:2] + pos = pos+2*ln + else: + w = wbk[pos:pos+ln] + pos = pos + ln + pos += 4*rt + pos += sz + return w, pos - save +def i2b(n): + w='' + while n>0: + w=str(n%2)+w + n/=2 + if len(w)<64: + return '0'*(64-len(w))+w + return w +def b2i(w): + ans = 0 + p2 = 1 + for i in w[::-1]: + ans+=p2*int(i) + p2*=2 + return ans +def get_label(n): + if n<26: + return chr(n%26+65) + return chr(n/26-1+65)+chr(n%26+65) +def floating(n): + w = i2b(n) + sign = (-1)**int(w[0]) + e = b2i(w[1:12]) + f = b2i(w[12:])/2.0**52 + if e==2047 and f!=0: + return 'NaN' + if e==2047 and f==0 and sign==-1: + return '-INF' + if e==2047 and f==0 and sign==1: + return 'INF' + if e==0: + return '0.0' + return str(sign * 2**(e-1023)*(1+f)) +def read_cell_address(w): + r=str(1+integer(w[:2])) + i=(integer(w[2:]))&0x00FF + c =get_label(i) + return c + r +def read_cellrange_address(w): + r1= str(1+integer(w[:2])) + r2= str(1+integer(w[2:4])) + i= (integer(w[4:6])) & 0x00FF + c1= get_label(i) + i= (integer(w[6:8])) & 0x00FF + c2= get_label(i) + return c1+r1+ ':' +c2+r2 +def skip_attr(s, pos): # tAttr token: this token has many different tokens + t = integer(s[pos+1]) # tAttrChoose token ,# IF control, choose, skip etc + if t == 0x04: + nc=integer(s[pos+2:pos+4]) + return nc*2 + 6 ,t + return 4, t + +def read_additional(s, pos, tid): + ans='' + np=pos + if tid == 0x20 or tid == 0x40 or tid == 0x60: + ans+='{' + nc= integer(s[pos])+1 + nr= integer(s[pos+1:pos+3])+1 + np+=3 + for i in range(0, nr): + for j in range(0, nc): + if integer(s[np])==0x00: # empty + ans += ',' + np += 9 + elif integer(s[np])==0x01: # float + ans += str(floating(hexa(s[np+1:np+9])))+',' + np+=9 + elif integer(s[np])==0x02: # string + st,bytes = read_string(s, np+1, 2) + ans+= '"' + st + '"' + ',' + np+=1+bytes + elif integer(s[np])==0x04: # boolean + if integer(s[np+1])==1: + ans+='TRUE,' + else: + ans+='FALSE,' + np+=9 + elif integer(s[np])==0x10: # error value + ans+=err_value[integer(s[np+1])]+',' + np+=9 + else: + pass + ans=ans[:len(ans)-1]+';' + ans=ans[:len(ans)-1]+'}' + return ans, np-pos + return '', 0 + + +global fn_list +fn_list=['']*368 +fp=open(activity.get_bundle_path()+'/interoperability/xls/function.txt','r') +for i in range(0, 249): + s = fp.readline() + l=s.split() + if l[2]==l[3]: + arg = int(l[2]) + else: + arg = -1 + fn_list[int(l[0])] = l[1], arg + +token_list=['','','','+','-','*','/','^','&','<','<=','=','>=','>'] +token_list+=['<>', ' ', ',' ,':', 'u-', 'u+','%', '()', ''] +def read_formula(s): + rpn=[] # rpn array + add_list=[] + size=integer(s[:2]) + pos = 2 + while pos != 2+size: + tid = integer(s[pos]) + if tid>=0x00 and tid<=0x02: return 'UNKNOWN' + + elif tid>=0x03 and tid<=0x16: + rpn+=[token_list[tid]] + pos+=1 + elif tid==0x17: # string + st, bytes = read_string(s, pos+1, 1) + pos=pos+1+bytes + rpn+=['"'+st+'"'] + elif tid==0x1C: # error value + er=err_value[integer(s[pos+1])] + rpn+=[er] + pos+=2 + elif tid==0x1D: # bool + nt=integer(s[pos+1]) + if nt==0: rpn+=['FALSE'] + else: rpn+=['TRUE'] + pos+=2 + elif tid==0x1E: # integer + rpn+=[str(integer(s[pos+1:pos+3]))] + pos+=3 + elif tid==0x1F: # float + fv = floating(hexa(s[pos+1:pos+9])) + pos+=9 + rpn+=[str(fv)] + elif tid==0x21 or tid==0x41 or tid==0x61: # const argc function name + ( fn, argc )= fn_list[integer(s[pos+1:pos+3])] + rpn+=[(fn, argc)] + pos+=3 + elif tid==0x22 or tid==0x42 or tid==0x62: # variable argc function name + try: + fn, argc = fn_list[integer(s[pos+2:pos+4])] + except: + return 'UNKNOWN' + argc=integer(s[pos+1]) + rpn+=[(fn, argc)] + pos+=4 + elif tid == 0x24 or tid==0x44 or tid==0x64: # tRef + rpn+=[read_cell_address(s[pos+1:pos+5])] + pos+=5 + elif tid == 0x25 or tid==0x45 or tid==0x65: # tArea + rpn+=[read_cellrange_address(s[pos+1:pos+9])] + pos+=9 + elif tid == 0x20 or tid == 0x40 or tid == 0x60: # tArray + add_list+=[ (len(rpn), tid) ] + pos+=8 + elif tid == 0x26 or tid == 0x46 or tid == 0x66: + pos+=7 + elif tid == 0x19: + bytes, t = skip_attr(s, pos) + pos += bytes + if t==0x10: + rpn+=[('SUM', 1)] # tAttrSum replaces the tFuncVar token here + else: + return 'UNKNOWN' # has to take care of some other tokens too + + for i in range(0, len(add_list)): # additional data + index, tid = add_list[i] + dt, bytes = read_additional(s, pos, tid) + pos+=bytes + rpn.insert(index, dt) + + l=len(rpn) + stk=[] + for i in range(0,l): # converting RPN to formula using stack + if rpn[i] in token_list[:18]: + t2=stk.pop() + t1=stk.pop() + stk+=[t1+rpn[i]+t2] + elif rpn[i]=='u+' or rpn[i]=='u-': + t1=stk.pop() + ch=rpn[i] + stk+=[ch[1]+t1] + elif rpn[i]=='%': + t1=stk.pop() + stk+=[t1+'%'] + elif rpn[i]=='()': + t1=stk.pop() + stk+=['('+t1+')'] + elif type(rpn[i])==tuple: + fname, argc = rpn[i] + fnstr=')' + for j in range(0, argc): + fnstr=',' + stk.pop() + fnstr + if argc != 0: + fnstr=fnstr[1:] + fnstr= fname+'('+fnstr + stk+=[fnstr] + else: + stk+=[rpn[i]] + return stk[0] + + + diff --git a/interoperability/xls/function.pyo b/interoperability/xls/function.pyo Binary files differnew file mode 100644 index 0000000..8015417 --- /dev/null +++ b/interoperability/xls/function.pyo diff --git a/interoperability/xls/function.txt b/interoperability/xls/function.txt new file mode 100644 index 0000000..cd21337 --- /dev/null +++ b/interoperability/xls/function.txt @@ -0,0 +1,249 @@ +0 COUNT 0 30 +27 ROUND 2 2 +1 IF 2 3 +28 LOOKUP 2 3 +2 ISNA 1 1 +29 INDEX 2 4 +3 ISERROR 1 1 +30 REPT 2 2 +4 SUM 0 30 +31 MID 3 3 +5 AVERAGE 1 30 +32 LEN 1 1 +6 MIN 1 30 +33 VALUE 1 1 +7 MAX 1 30 +34 TRUE 0 0 +8 ROW 0 1 +35 FALSE 0 0 +9 COLUMN 0 1 +36 AND 1 30 +10 NA 0 0 +37 OR 1 30 +11 NPV 2 30 +38 NOT 1 1 +12 STDEV 1 30 +39 MOD 2 2 +13 DOLLAR 1 2 +40 DCOUNT 3 3 +14 FIXED 2 2 +41 DSUM 3 3 +15 SIN 1 1 +42 DAVERAGE 3 3 +16 COS 1 1 +43 DMIN 3 3 +17 TAN 1 1 +44 DMAX 3 3 +18 ATAN 1 1 +45 DSTDEV 3 3 +19 PI 0 0 +46 VAR 1 30 +20 SQRT 1 1 +47 DVAR 3 3 +21 EXP 1 1 +48 TEXT 2 2 +22 LN 1 1 +49 LINEST 1 2 +23 LOG10 1 1 +50 TREND 1 3 +24 ABS 1 1 +51 LOGEST 1 2 +25 INT 1 1 +52 GROWTH 1 3 +26 SIGN 1 1 +56 PV 3 5 +57 FV 3 5 +116 RIGHT 1 2 +58 NPER 3 5 +117 EXACT 2 2 +59 PMT 3 5 +118 TRIM 1 1 +60 RATE 3 6 +119 REPLACE 4 4 +61 MIRR 3 3 +120 SUBSTITUTE 3 4 +62 IRR 1 2 +121 CODE 1 1 +63 RAND 0 0 +124 FIND 2 3 +64 MATCH 2 3 +125 CELL 1 2 +65 DATE 3 3 +126 ISERR 1 1 +66 TIME 3 3 +127 ISTEXT 1 1 +67 DAY 1 1 +128 ISNUMBER 1 1 +68 MONTH 1 1 +129 ISBLANK 1 1 +69 YEAR 1 1 +130 T 1 1 +70 WEEKDAY 1 1 +131 N 1 1 +71 HOUR 1 1 +140 DATEVALUE 1 1 +72 MINUTE 1 1 +141 TIMEVALUE 1 1 +73 SECOND 1 1 +142 SLN 3 3 +74 NOW 0 0 +143 SYD 4 4 +75 AREAS 1 1 +144 DDB 4 5 +76 ROWS 1 1 +148 INDIRECT 1 2 +77 COLUMNS 1 1 +162 CLEAN 1 1 +78 OFFSET 3 5 +163 MDETERM 1 1 +82 SEARCH 2 3 +164 MINVERSE 1 1 +83 TRANSPOSE 1 1 +165 MMULT 2 2 +86 TYPE 1 1 +167 IPMT 4 6 +97 ATAN2 2 2 +168 PPMT 4 6 +98 ASIN 1 1 +169 COUNTA 0 30 +99 ACOS 1 1 +183 PRODUCT 0 30 +100 CHOOSE 2 30 +184 FACT 1 1 +101 HLOOKUP 3 3 +189 DPRODUCT 3 3 +102 VLOOKUP 3 3 +190 ISNONTEXT 1 1 +105 ISREF 1 1 +193 STDEVP 1 30 +109 LOG 1 2 +194 VARP 1 30 +111 CHAR 1 1 +195 DSTDEVP 3 3 +112 LOWER 1 1 +196 DVARP 3 3 +113 UPPER 1 1 +197 TRUNC 1 1 +114 PROPER 1 1 +198 ISLOGICAL 1 1 +115 LEFT 1 2 +199 DCOUNTA 3 3 +49 LINEST 1 4 +215 JIS 1 1 +50 TREND 1 4 +219 ADDRESS 2 5 +51 LOGEST 1 4 +220 DAYS360 2 2 +52 GROWTH 1 4 +221 TODAY 0 0 +197 TRUNC 1 2 +222 VDB 5 7 +204 YEN 1 2 +227 MEDIAN 1 30 +205 FINDB 2 3 +228 SUMPRODUCT 1 30 +206 SEARCHB 2 3 +229 SINH 1 1 +207 REPLACEB 4 4 +230 COSH 1 1 +208 LEFTB 1 2 +231 TANH 1 1 +209 RIGHTB 1 2 +232 ASINH 1 1 +210 MIDB 3 3 +233 ACOSH 1 1 +211 LENB 1 1 +234 ATANH 1 1 +212 ROUNDUP 2 2 +235 DGET 3 3 +213 ROUNDDOWN 2 2 +244 INFO 1 1 +214 ASC 1 1 +14 FIXED 2 3 +298 ODD 1 1 +204 USDOLLAR 1 2 +299 PERMUT 2 2 +215 DBCS 1 1 +300 POISSON 3 3 +216 RANK 2 3 +301 TDIST 3 3 +247 DB 4 5 +302 WEIBULL 4 4 +252 FREQUENCY 2 2 +303 SUMXMY2 2 2 +261 ERROR.TYPE 1 1 +304 SUMX2MY2 2 2 +269 AVEDEV 1 30 +305 SUMX2PY2 2 2 +270 BETADIST 3 5 +306 CHITEST 2 2 +271 GAMMALN 1 1 +307 CORREL 2 2 +272 BETAINV 3 5 +308 COVAR 2 2 +273 BINOMDIST 4 4 +309 FORECAST 3 3 +274 CHIDIST 2 2 +310 FTEST 2 2 +275 CHIINV 2 2 +311 INTERCEPT 2 2 +276 COMBIN 2 2 +312 PEARSON 2 2 +277 CONFIDENCE 3 3 +313 RSQ 2 2 +278 CRITBINOM 3 3 +314 STEYX 2 2 +279 EVEN 1 1 +315 SLOPE 2 2 +280 EXPONDIST 3 3 +316 TTEST 4 4 +281 FDIST 3 3 +317 PROB 3 4 +282 FINV 3 3 +318 DEVSQ 1 30 +283 FISHER 1 1 +319 GEOMEAN 1 30 +284 FISHERINV 1 1 +320 HARMEAN 1 30 +285 FLOOR 2 2 +321 SUMSQ 0 30 +286 GAMMADIST 4 4 +322 KURT 1 30 +287 GAMMAINV 3 3 +323 SKEW 1 30 +288 CEILING 2 2 +324 ZTEST 2 3 +289 HYPGEOMDIST 4 4 +325 LARGE 2 2 +290 LOGNORMDIST 3 3 +326 SMALL 2 2 +291 LOGINV 3 3 +327 QUARTILE 2 2 +292 NEGBINOMDIST 3 3 +328 PERCENTILE 2 2 +293 NORMDIST 4 4 +329 PERCENTRANK 2 3 +294 NORMSDIST 1 1 +330 MODE 1 30 +295 NORMINV 3 3 +331 TRIMMEAN 2 2 +296 NORMSINV 1 1 +332 TINV 2 2 +297 STANDARDIZE 3 3 +70 WEEKDAY 1 2 +345 SUMIF 2 3 +101 HLOOKUP 3 4 +346 COUNTIF 2 2 +102 VLOOKUP 3 4 +347 COUNTBLANK 1 1 +220 DAYS360 2 3 +350 ISPMT 4 4 +336 CONCATENATE 0 30 +351 DATEDIF 3 3 +337 POWER 2 2 +352 DATESTRING 1 1 +342 RADIANS 1 1 +353 NUMBERSTRING 2 2 +343 DEGREES 1 1 +354 ROMAN 1 2 +344 SUBTOTAL 2 30
\ No newline at end of file diff --git a/interoperability/xls/workbook.py b/interoperability/xls/workbook.py new file mode 100644 index 0000000..4398989 --- /dev/null +++ b/interoperability/xls/workbook.py @@ -0,0 +1,432 @@ +# Ability to read single sheet Excel .xls files in SocialCalc added. Developed by Mahesh Chand Sharma from SEETA (http://seeta.in) with pointers from Vijit Singh, +# Software Engineer at SEETA (http://seeta.in) under the guidance of Manusheel Gupta. +# For getting involved in the development of SocialCalc on Sugar, or on any questions related to it, please e-mail at socialcalc<dot>sugar<at>seeta<dot>in. +# For posting a feature request/bug, please use http://testtrack.seeta.in. +# File Author - Mahesh Chand Sharma + +from convert_to_scalcstring import workbook_data_to_scalc_string +from compound import return_wbk, integer, hexa +from function import read_formula +global err_value + +err_value = ['']*(0x2A +1) +err_value[0x00] = '#NULL!' +err_value[0x07] = '#DIV/0!' +err_value[0x0F] = '#VALUE' +err_value[0x17] = '#REF!' +err_value[0x1D] = '#NAME?' +err_value[0x24] = '#NUM!' +err_value[0x2A] = '#N/A!' + +def next_record_info(): + global ptr + identity = hexa(wbk[ptr : ptr+2]) + off = ptr + 4 + size = integer(wbk[ptr+2 : ptr+4]) + ptr = ptr +4 +size + return (identity, off, size) +def i2b(n): + s='' + while n>0: + s=str(n%2)+s + n/=2 + if len(s)<64: + return '0'*(64-len(s))+s + return s +def b2i(s): + ans = 0 + p2 = 1 + for i in s[::-1]: + ans+=p2*int(i) + p2*=2 + return ans +def read_string(pos, len_size): + save=pos + ln=integer(wbk[pos:pos+len_size]) + pos+=len_size + flag=integer(wbk[pos:pos+1]) + pos+=1 + rt = 0 + sz = 0 + if flag&8==8: + rt = integer(wbk[pos : pos+2]) + pos+=2 + if flag&4==4: + sz = integer(wbk[pos : pos+4]) + pos+=4 + if flag&1==1: + s = wbk[pos:pos+2*ln:2] + pos = pos+2*ln + else: + s = wbk[pos:pos+ln] + pos = pos + ln + pos += 4*rt + pos += sz + return s, pos-save +def read_index_record(off, size): + rf = integer(wbk[off+4:off+8]) + rl = integer(wbk[off+8:off+12]) + l=[] + for i in range(0, (size - 16) / 4): + l.append(integer(wbk[off+16+4*i:off+16+4*(i+1)])) + return rf, rl, l + +def read_dimension_record(off, size): + rf = integer(wbk[off:off+4]) + rl = integer(wbk[off+4:off+8]) + cf = integer(wbk[off+8:off+10]) + cl = integer(wbk[off+10:off+12]) + return rf,rl,cf,cl + +def read_row_record(off, size): + r = integer(wbk[off:off+2]) + c1 = integer(wbk[off+2:off+4]) + c2 = integer(wbk[off+4:off+6]) + return r, c1, c2 + +def read_dbcell_record(off, size): + l=[] + for i in range(0, (size-4)/2): + l.append(integer(wbk[off+4+2*i : off+4+2*i+2])) + return l + +def read_sst_record(off, size): + nm = integer(wbk[off+4:off+8]) + off += 8 + bytes = 0 + l=[] + s='' + for i in range(0, nm): + (s, bytes) = read_string(off, 2) + off += bytes + l.append(s) + return l + +def read_blank_record(off, size): + r = integer(wbk[off : off+2]) + c = integer(wbk[off+2 : off+4]) + return r, c, '', 's' + +def read_labelsst_record(off, size): + r = integer(wbk[off : off+2]) + c = integer(wbk[off+2 : off+4]) + j = integer(wbk[off+4 : off+6]) + fs=xf[j] + i = integer(wbk[off+6 : off+10]) + return r, c, sst[i], 's', fs + +def read_label_record(off, size): + r = integer(wbk[off : off+2]) + c = integer(wbk[off+2 : off+4]) + i = integer(wbk[off+4 : off+6]) + fs=xf[i] + s, bytes = read_string(off+6, 2) + return r, c, s, 's', fs + +def read_float_record(off, size): + r = integer(wbk[off : off+2]) + c = integer(wbk[off+2 : off+4]) + i = integer(wbk[off+4 : off+6]) + fs=xf[i] + fv = floating(hexa(wbk[off+6:off+14])) + return r, c, fv, 'f', fs +def read_rk_value(val): + change = val & 0x00000001 + tp = val & 0x00000002 + val = val & 0xfffffffc + if tp==0: + val= val*2**32 + if change == 1: return float(floating(val))/100.0, 'f' + else: return float(floating(val)), 'f' + if tp==2: + val=val>>2 + if val > 536870911: val = (2**30-val)*(-1) + if change==1: + return val/100, 'i' + else: + return val, 'i' + +def read_rk_record(off, size): + r = integer(wbk[off : off+2]) + c = integer(wbk[off+2 : off+4]) + i = integer(wbk[off+4 : off+6]) + fs=xf[i] + rk_val = hexa(wbk[off+6 : off+10]) + val, typ = read_rk_value(rk_val) + if int(val) == val: + val = int(val) + typ = 'i' + return r, c, str(val), typ, fs + + +def read_boolerr_record(off, size): + r = integer(wbk[off : off+2]) + c = integer(wbk[off+2 : off+4]) + i = integer(wbk[off+4 : off+6]) + fs=xf[i] + typ = integer(wbk[off+7 : off+8]) + val = integer(wbk[off+6 : off+7]) + if typ == 0: # 0 for boolean + return r, c, bool(val), 'b', fs + else: + return r, c, err_value[val], 'e', fs +def read_mulrk_record(off, size): + r = integer(wbk[off : off+2]) + fc = integer(wbk[off+2 : off+4]) + nc = (size - 6) / 6 + l = [] + for i in range(0, nc): + j = integer(wbk[off+4+6*i : off+4+6*i+2]) + fs=xf[j] + rk_val = integer(wbk[off + 4+6*i+2 : off+ 4+6*i+2+4]) + val, typ = read_rk_value(rk_val) + if int(val) == val: + val = int(val) + typ = 'i' + c = fc + i + l.append( (r, c, val, typ, fs) ) + return l + + +def floating(n): + s = i2b(n) + sign = (-1)**int(s[0]) + e = b2i(s[1:12]) + f = b2i(s[12:])/2.0**52 + if e==2047 and f!=0: + return 'NaN' + if e==2047 and f==0 and sign==-1: + return '-INF' + if e==2047 and f==0 and sign==1: + return 'INF' + if e==0: + return '0.0' + return str(sign * 2**(e-1023)*(1+f)) +def calc_formula_result(off, size): + i = integer(wbk[off : off+1]) + if i == 0x01: + return bool(integer(wbk[off+2 : off+3])), 'b' + if i == 0x02: + ec = integer(wbk[off+2 : off+3]) + return err_value[ec], 'e' + if i == 0x03: + return '', 's' + else: + return floating( integer(wbk[off : off+8]) ), 'f' + +def read_formula_record(off, size): + r = integer(wbk[off : off+2]) + c = integer(wbk[off+2 : off+4]) + i = integer(wbk[off+4 : off+6]) + fs=xf[i] + val, typ = calc_formula_result(off+6, 8) + formula = read_formula(wbk[off+20 : off + size]) + return r, c, val, fs, typ, formula + +def read_font_record(off, size): + h = integer(wbk[off : off+2])/20 + i=integer(wbk[off+2 : off+4]) + bold = ((i & 0x0001)==1) + italic = ((i & 0x0002)==2) + underlined = ((i & 0x0004)==4) + return int(bold), int(italic), int(underlined), h + + +def read_xf_record(off, size): + ans='' + i=integer(wbk[off : off+2]) + (b, it, u, h) = fonts[i] + ans+=str(b) + ans+=str(it) + ans+=str(u) + i=integer(wbk[off+6 : off+7]) + n = i & 0x07 + if n==2: + ans+='10' # center + elif n==3: + ans+='11' # right + else: + ans+='01' #left + n = (i & 0x70)/16 + if n==1: + ans+='10' #center + elif n==2: + ans+='11' # bottom + else: + ans+='01' # top + n = (i & 0x08)/8 + ans+=str(n) # 1 for word wrap + return ans, h + +def read_defcol_record(off, size): + return integer(wbk[off:off+2]) + +def read_colinfo_record(off, size): + cf = integer(wbk[off : off+2]) + cl = integer(wbk[off+2 : off+4]) + w = integer(wbk[off+4 : off+6])/256 + 1 + return cf, cl, w + +def read_standardwidth_record(off, size): + return integer(wbk[off:off+2])/16 + 1 + +def bin2data(s): + global wbk, ptr, fonts, xf + wbk = return_wbk(s) + ptr=0 + if wbk == 'file not in correct format': + return wbk + + # reading the workbook global substream + (f, off, size) = next_record_info()# BOF record +##################################################### + while f != 0x0031: # reaching to FONT Records + (f, off, size) = next_record_info() + fonts=[] + while f == 0x0031: # reading font records + tpl=read_font_record(off, size) + fonts.append(tpl) + if len(fonts)==4: # since font with index 4 has been ommited + fonts.append((0, 0, 0, 0)) # by excel :) + (f, off, size) = next_record_info() + + while f != 0x00E0: # reaching to XF record + (f, off, size) = next_record_info() + xf=[] + while f == 0x00E0: # reading XF records + fs = read_xf_record(off, size) # fn is formatting no. + xf.append(fs) + (f, off, size) = next_record_info() +##################################################### + while f != 0x0085: #reaching to SHEET Records + (f, off, size) = next_record_info() + + sheets = [] # contains the info of all the worksheets + while f == 0x0085: #reading the SHEET records + record = wbk[off : off+size] + + pos = integer(record[:4]) + sheet_type = integer(record[5:6]); + (name, bytes) = read_string(off + 6, 1) + if sheet_type==0: # sheettype 0 for workssheet + sheets.append((pos, sheet_type, name)) + (f, off, size) = next_record_info() + #building the SST shared string table + while f != 0x00FC: #reaching to SST record + (f, off, size) = next_record_info() + global sst + sst = [] # list of used strings + sst = read_sst_record(off, size) + workbook_data=[] + workbook_col_widths=[] + for sheet in sheets: + ptr = sheet[0] # offset of 1st worksheet + sheet_name = sheet[2] + + next_record_info() # skipping the BOF record + (f, off, size) = next_record_info() + if f == 0x020B: # INDEX record + (rf, rl, dbcell_posns) = read_index_record(off, size) +################################################### + colinfo=[] + def_width = 0 + standard_width = 0 + while f != 0x0200: #reaching to DIMENSION record + if f==0x0055: #reading DEFCOLWIDTH + def_width = read_defcol_record(off, size) + if f==0x007D: #reading COLINFO record + cnf = read_colinfo_record(off, size) + colinfo.append( cnf ) + (f, off, size) = next_record_info() +##################################################### + rf,rl,cf,cl = read_dimension_record(off, size) + if (rl-rf)%32==0: row_block_count = (rl-rf)/32 + else: row_block_count = int((rl-rf)/32)+1 + + if row_block_count == 0: # sheet is empty + continue + + #reading the data now + sheet_data = [[()]*cl]*rl # empty database + for i in range(0, row_block_count): + (f, off, size) = next_record_info() + while f == 0x0208: + (f, off, size) = next_record_info() # skipping the ROW record + + while f != 0x00D7: # f==0x00D7 for DBCELL record + if f == 0x0205: + x, y, val, typ, fs = read_boolerr_record(off, size) + elif f == 0x0204: + x, y, val, typ, fs = read_label_record(off, size) + elif f == 0x00FD: + x, y, val, typ, fs = read_labelsst_record(off, size) + elif f == 0x0203: + x, y, val, typ, fs = read_float_record(off, size) + + elif f == 0x027E: + x, y, val, typ, fs = read_rk_record(off, size) + + elif f == 0x0006: # formula record + x, y, val, fs, typ, formula= read_formula_record(off, size) + buf = list(sheet_data[x]) + buf[y] = (val, 'for', fs, typ, formula) + sheet_data[x] = buf + (f, off, size) = next_record_info() + continue + elif f == 0x00BD: + mul_cell_data = read_mulrk_record(off, size) # list of tuples + for tple in mul_cell_data: + x, y, val, typ, fs = tple + buf = list(sheet_data[x]) + buf[y] = (val, typ, fs) + sheet_data[x] = buf + (f, off, size) = next_record_info() + continue + else: + (f, off, size) = next_record_info() + continue + + buf = list(sheet_data[x]) + buf[y] = (val, typ, fs) + sheet_data[x] = buf + (f, off, size) = next_record_info() + + workbook_data.append(sheet_data) +######################################################### + while f != 0x000A: # 000A for EOF record + if f==0x0099: # standardwidth record + standard_width = read_standardwidth_record(off, size) + (f, off, size) = next_record_info() + + col_widths = [0]*cl + for tpl in colinfo: + for j in range(tpl[0], tpl[1]+1): + col_widths[j] = tpl[2] + if standard_width!=0: + default = standard_width + else: + default = def_width + for j in range(0, cl): + if col_widths[j] == 0: + col_widths[j]=default + + workbook_col_widths.append(col_widths) +########################################################### + return workbook_data_to_scalc_string(workbook_data, workbook_col_widths,0) + + + +#f=open('test.xls', 'rb') +#s=f.read() +#workbook_data = bin2data(s) +############### END OF CODE ############################# +# NOW THE WORKBOOK_DATA CONTAINS THE DATA OF ALL THE SHEETS +# IT IS A 3D LIST OF DATA (ROW*COL*SHEET) and each cell has two +#fields (value, type) +# WE CAN CHECK IT- +#for sheet_data in workbook_data: +# for row_data in sheet_data: +# print row_data +# print '\n\n' + +# NOW THIS DATA CAN BE USED TO LOAD TO THE SOCIALCALC diff --git a/interoperability/xls/workbook.pyo b/interoperability/xls/workbook.pyo Binary files differnew file mode 100644 index 0000000..a0a0d50 --- /dev/null +++ b/interoperability/xls/workbook.pyo |