fork download
copy 
  1. import xlrd
  2. import re
  3. import os.path
  4. import SurveyObject
  5. from collections import OrderedDict
  6.  
  7. def run():
  8.  
  9.     # Open the workbook
  10.     xl_workbook = xlrd.open_workbook("quota.xls")
  11.  
  12.     xl_sheet,sheetName = getSheet(xl_workbook)
  13.  
  14.     filename = raw_input("What filename should I output to? Default = transform.py: ")
  15.     if not filename:
  16.         filename = 'transform.py'
  17.  
  18.     if os.path.isfile(filename):
  19.         yes =  raw_input('This file already exists do you want to overwrite this file?')
  20.         if yes != 'y':
  21.             return
  22.  
  23.     tables = separateTables(xl_sheet)
  24.  
  25.     newTables,tablenames = getDefines(tables)
  26.  
  27.  
  28.     alllists = makeDicts(newTables,xl_workbook)
  29.  
  30.     alllists = convertConds(alllists)
  31.  
  32.  
  33.     f = open(filename, 'w')
  34.  
  35.     output = '''
  36. def setMark(marker):
  37.     if not hasMarker(marker):
  38.         setMarker(marker)
  39. '''
  40.  
  41.     for lists,tablename in zip(alllists,tablenames):
  42.         output = output +writeCode(lists,sheetName,tablename)+'\n'
  43.  
  44.     f.write(output)
  45.  
  46.  
  47. #replaces .r1 with == 1
  48. #looks at survey to set cond correctly
  49. def convertConds(alllists):
  50.     surveyObject = SurveyObject.surveyObject()
  51.     for lists in alllists:
  52.         for list in lists:
  53.             for item in list:
  54.                 subitems = list[item].strip().split(' ')
  55.                 for subitem in subitems:
  56.                     if '.' not in subitem:
  57.                         continue
  58.                     subitem = subitem.strip(' ()')
  59.                     qlabel = subitem.split('.')[0].strip()
  60.                     rlabel = subitem
  61.                     #print qlabel
  62.                     if qlabel in surveyObject.allQuestions:
  63.                         if surveyObject.allQuestions[qlabel].checkbox:
  64.                             list[item] = re.sub(qlabel+r'\.([a-zA-Z_]\w*)($| |\))', qlabel+r'\1 == 1\2',list[item])
  65.                         elif surveyObject.allQuestions[qlabel].radio:
  66.                             #print rlabel
  67.                             if rlabel in surveyObject.allitemsdict:
  68.                                 element = surveyObject.allitemsdict[rlabel]
  69.                                 if element.value:
  70.                                     list[item] = re.sub(rlabel+r'($| |\))', qlabel+r' == '+element.value+r'\1',list[item])
  71.                                 else:
  72.                                     index = element.parent.objects[element.type].index(element) + 1
  73.                                     list[item] = re.sub(rlabel+r'($| |\))', qlabel+r' == '+str(index)+r'\1',list[item])
  74.                                     #list[item] = list[item].replace(rlabel,newlabel)
  75.                             else:
  76.                                 newlabel = re.sub(r'\.[a-zA-Z_]+(\d*)($| |\))', r' == \1\2',rlabel)
  77.                                 list[item] = list[item].replace(rlabel,newlabel)
  78.                     else:
  79.                         pass
  80.                         #just let it be
  81.                         #newlabel = re.sub(r'\.[a-zA-Z_]+(\d*)($| |\))', r' == \1\2',rlabel)
  82.                         #list[item] = list[item].replace(rlabel,newlabel)
  83.     return alllists
  84.  
  85. #Convert excel tables to a list of lists [[table1],[table2],[table3]]
  86. def separateTables(xl_sheet):
  87.     tables = []
  88.     newTable = []
  89.  
  90.     # Cell Types: 0=Empty, 1=Text, 2=Number, 3=Date, 4=Boolean, 5=Error, 6=Blank
  91.     for rx in range(xl_sheet.nrows):
  92.         #print xl_sheet.row(rx)
  93.         allempty = True
  94.         for ry,cell in enumerate(xl_sheet.row(rx)):
  95.             cell_type = xl_sheet.cell_type(rx, ry)
  96.             cell_value = xl_sheet.cell_value(rx, ry)
  97.             #print '    ', cell_type, ':', cell_value
  98.  
  99.             if cell_type != 0:
  100.                 allempty = False
  101.                 #append row
  102.                 newTable.append(getValue(xl_sheet,rx))
  103.                 break
  104.  
  105.         if allempty:
  106.             tables.append(newTable)
  107.             newTable = []
  108.  
  109.  
  110.     tables.append(newTable)
  111.     return tables
  112.  
  113. # ask what sheet to transform
  114. def getSheet(xl_workbook):
  115.     sheet_names = xl_workbook.sheet_names()
  116.     for index,sheet in enumerate(sheet_names):
  117.         print index,' Sheet', sheet
  118.  
  119.     sheetIndex = raw_input("What is the index of the sheet you want to transform? ")
  120.     if not sheetIndex.isdigit() or not (int(sheetIndex) in range(0,len(sheet_names))):
  121.         print 'Bad Index'
  122.         return
  123.  
  124.  
  125.     sheetIndex = int(sheetIndex)
  126.  
  127.     xl_sheet = xl_workbook.sheet_by_name(sheet_names[sheetIndex])
  128.  
  129.     return xl_sheet,sheet_names[sheetIndex]
  130.  
  131.  
  132. #makes a dictionary of defines and conds.  dict['Male'] = ['Q2.r1']
  133. def makeDicts(newTables,xl_workbook):
  134.     alllists = []
  135.     for table in newTables:
  136.         lists = []
  137.         for names in table:
  138.             dict1 = OrderedDict()
  139.             for name in names:
  140.                 dict1[str(name)] = str(getCond(xl_workbook,name))
  141.             lists.append(dict1)
  142.         alllists.append(lists)
  143.     return alllists
  144.  
  145.  
  146.  
  147. def writeCode(lists,sheetname,tablename):
  148.     output = '\n#='+tablename
  149.     #converting the dict object to a usable string
  150.     for index,d in enumerate(lists):
  151.         output = output + "\nd" + str(index) + " = {"
  152.         for index2,item in enumerate(d):
  153.             if index2!=0:
  154.                 output = output + ', '
  155.             output = output + "'"+item+"': " + d[item] #.replace("',",",").replace(": '",": ").replace("'}",'}')
  156.  
  157.         output = output + "}"
  158.     #Begin setup of loop code
  159.     output += "\n\nif hasMarker('qualified'):\n"
  160.  
  161.     spaces = 4
  162.  
  163.     marker = 'removeMarker("/'+sheetname
  164.     for index,d in enumerate(lists):
  165.         i = str(index)
  166.         marker = marker+'/"+item'+i+'+"'
  167.         output = output + ' '*spaces+'for item' + i + ' in d'+ i +':\n'
  168.         spaces = spaces+4
  169.  
  170.     output = output + ' ' *spaces + marker.rstrip('+"')+')\n'
  171.  
  172.     spaces = 4
  173.     marker = 'setMark("/'+sheetname
  174.     for index,d in enumerate(lists):
  175.         i = str(index)
  176.         marker = marker+'/"+item'+i+'+"'
  177.         output = output + ' '*spaces+'for item' + i + ' in d'+ i +':\n'
  178.         spaces = spaces+4
  179.         output = output + ' '*spaces+'if d'+i+'[item'+i+']:\n'
  180.         spaces = spaces+4
  181.  
  182.     #add set marker to the inside of the loop
  183.     output = output + ' ' *spaces + marker.rstrip('+"')+')\n'
  184.     return output
  185.  
  186.  
  187. #This changes the tables from their current formatting to only give me the defines sorted by each columnm
  188. # each column is a list [[male,female],[age1,age2,age3]]
  189. def getDefines(tables):
  190.     newtables = []
  191.     tablenames = []
  192.     for table in tables:
  193.         rows = []
  194.  
  195.  
  196.         tablenames.append(table[0][0].split('=')[1])
  197.  
  198.         topcell = table[0][0].split('=')[0]
  199.         topcell = topcell.replace('#','')
  200.         topcells = topcell.split(' ')
  201.         topcondition = None
  202.         for cell in topcells:
  203.             if cell and not cell.isdigit() and 'cells:' not in cell:
  204.                 topcondition = cell
  205.                 break
  206.  
  207.  
  208.  
  209.         t = []
  210.  
  211.         #if we have #US=US we need to add US as part of the table.  Order Matters
  212.         if topcondition:
  213.             newrow = set()
  214.             newrow.add(topcondition)
  215.             t.append(newrow)
  216.  
  217.         isgrid = False
  218.         for x in table[0]:
  219.             #if it's a grid the top row will be missing a #
  220.             if x and '#' not in x:
  221.                 isgrid=True
  222.                 break
  223.  
  224.         newtable = []
  225.         if isgrid:
  226.             #sets up the grid to be flat like we expect
  227.             newtable = getDefines2(table)
  228.         else:
  229.             #transpose table and remove top row
  230.             newtable = zip(*table[1:])
  231.  
  232.             #remove empty cols
  233.             newtable = removeEmpty(newtable)
  234.  
  235.  
  236.  
  237.  
  238.         #Removes empty columns
  239.         #Creates a set of defines
  240.         #Removes the limits which are at the end of the row
  241.         for row in newtable[:-1]:
  242.             newrow = []
  243.             for col in row:
  244.                 if col and col not in newrow:
  245.                     newrow.append(col)
  246.  
  247.             #add only if it has something
  248.             if len(newrow):
  249.                 t.append(newrow)
  250.  
  251.         newtables.append(t)
  252.  
  253.     return (newtables,tablenames)
  254.  
  255.  
  256. def getDefines2(table):
  257.     #find end so we can remove all the limits
  258.     end = 1
  259.     for col in table[0][1:]:
  260.         if col =='#':
  261.             end = end + 1
  262.         else:
  263.             break
  264.  
  265.     #remove top column with defines to put at very end
  266.     lastcolumn = table[0][end:]
  267.  
  268.     #create a new table without limits
  269.     table = table[1:]
  270.     newtable = []
  271.     for row in table:
  272.         newtable.append(row[:end])
  273.  
  274.     #transpose table and append top row to end
  275.     newtable = zip(*newtable)
  276.     newtable.append(tuple(lastcolumn))
  277.     newtable.append(('inf','inf'))
  278.     #remove empty cols
  279.     newtable = removeEmpty(newtable)
  280.  
  281.     return newtable
  282.  
  283.  
  284. #Returns an array of the rows values.  i.e. ['male','female']
  285. def getValue(xl_sheet,rx):
  286.     newrow = []
  287.     row = xl_sheet.row(rx)
  288.     for ry,cell in enumerate(row):
  289.         #cell_type = xl_sheet.cell_type(rx, ry)
  290.         cell_value = xl_sheet.cell_value(rx, ry)
  291.         newrow.append(cell_value)
  292.  
  293.     return newrow
  294.  
  295. #remove empty cols
  296. def removeEmpty(table):
  297.     table2 = []
  298.     for row in table:
  299.         good = False
  300.         for col in row:
  301.             if col:
  302.                 good = True
  303.                 break
  304.  
  305.         if good:
  306.             table2.append(row)
  307.  
  308.     return table2
  309.  
  310. #gets the cond from the defines sheet
  311. def getCond(xl_workbook,name):
  312.     xl_sheet = xl_workbook.sheet_by_index(0)
  313.     for rx in range(xl_sheet.nrows):
  314.         cell_value = xl_sheet.cell_value(rx, 0)
  315.         if cell_value == name:
  316.             return xl_sheet.cell_value(rx, 1)
  317.  
  318.  
  319. run()
  320.  
  321.  
  322. #xl_sheet = xl_workbook.sheet_by_index(0)
  323. #print ('Sheet name: %s' % xl_sheet.name)
  324.  
  325. # Pull the first row by index
  326. #  (rows/columns are also zero-indexed)
  327. #
  328. #row = xl_sheet.row(0)  # 1st row
  329.  
  330. # Print 1st row values and types
  331. #
  332. #from xlrd.sheet import ctype_text
  333.  
  334. #print('(Column #) type:value')
  335. #for idx, cell_obj in enumerate(row):
  336. #    cell_type_str = ctype_text.get(cell_obj.ctype, 'unknown type')
  337. #    print('(%s) %s %s' % (idx, cell_type_str, cell_obj.value))
  338.  
  339. # Print all values, iterating through rows and columns
  340. #
  341. #num_cols = xl_sheet.ncols   # Number of columns
  342. #for row_idx in range(0, xl_sheet.nrows):    # Iterate through rows
  343. #    print ('-'*40)
  344. #    print ('Row: %s' % row_idx)   # Print row number
  345. #    for col_idx in range(0, num_cols):  # Iterate through columns
  346. #        cell_obj = xl_sheet.cell(row_idx, col_idx)  # Get cell object by row, col
  347. #        print ('Column: [%s] cell_obj: [%s]' % (col_idx, cell_obj))
  348.  
Compilation error #stdin compilation error #stdout 0s 0KB
comments (?)
stdin
copy 
Standard input is empty
compilation info 
Traceback (most recent call last):
  File "/usr/lib/python3.7/py_compile.py", line 143, in compile
    _optimize=optimize)
  File "<frozen importlib._bootstrap_external>", line 791, in source_to_code
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "./prog.py", line 117
    print index,' Sheet', sheet
              ^
SyntaxError: Missing parentheses in call to 'print'. Did you mean print(index,' Sheet', sheet)?

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/usr/lib/python3.7/py_compile.py", line 147, in compile
    raise py_exc
py_compile.PyCompileError:   File "./prog.py", line 117
    print index,' Sheet', sheet
              ^
SyntaxError: Missing parentheses in call to 'print'. Did you mean print(index,' Sheet', sheet)?
stdout
copy 
Standard output is empty
https://ideone.com/kRti9I
language:
Python 3 (python  3.9.5)
created:
4 years ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!