Excel to XML convertor.

xlstoxmlconvertor.py

import xlrd
from lxml import etree
import xml.etree.ElementTree as ET

# create the file structure
data = ET.Element('openSCENERIO')  
wb = xlrd.open_workbook("sample.xlsx")
sh = wb.sheet_by_index(0)
conditiongroup = ""
condition  = ""
bystate = ""
startcondition=""
for row in range(0, sh.nrows):
    val = sh.row_values(row)
   
    
    if val[0] != "":
    	
         if ( val[0] != "Story Board" and val[0] == "scenarioID" or val[0] == "scenarioTitle" or val[0] == "scenarioVersionNumber" or val[0] =="scenarioTemplate" or val[0] == "scenarioSummary" or val[0] == "pictures" or val[0] == "useCaseAssociations" or val[0]=="ftpAssociations") :
            element = ET.SubElement(data, val[0]).text = str(val[1])
         elif (val[0] == "Decision Reasoning Framework:"):
            decision = ET.SubElement(data,"DecisionReasoningFramework")
         elif (val[0] == "HARA:") :
             hara = ET.SubElement(decision,"HARA")
         elif (val[0] == "Policy Links to Hazard and Risk Assessments:"):
              policy = ET.SubElement(hara,'PolicyLinkstoHazardandRiskAssessments',value="")
              element = ET.SubElement(policy,'LinkTo-Medini')
         elif (val[0] == "List of Safety Goals:") :
              goals = ET.SubElement(hara,'listofSafetyGoals')
              element = ET.SubElement(goals,'syncedSaftyGoals')
         elif (val[0] == "Policy Considerations:") :
              policyconsider = ET.SubElement(decision,'PolicyConsiderations')
         elif (val[0] == "Severity:") :
              severity = ET.SubElement(policyconsider,'Severity')
         elif (val[0] == "Exposure:") :
              severity = ET.SubElement(policyconsider,'Exposure')
         elif (val[0] == "Controllability:") :
              severity = ET.SubElement(policyconsider,'Controllability')
         elif (val[0] == "List of Modeled or Simulated Potential Outcomes:") :
              modeled = ET.SubElement(decision,'ListofModeledorSimulatedPotentialOutcomes',value="")
         elif (val[0] == "Test Run Model 1:") :
              runmodel = ET.SubElement(modeled,'TestRunModel').text="Link to model 1"
              runmodl2 = ET.SubElement(modeled,'TestRunModel').text= "Link to model 2"
              runmodl3 = ET.SubElement(modeled,'TestRunModel').text = "Link to model 3"
              runmodl4 = ET.SubElement(modeled,'TestRunModel').text = "Link to model 4"
              
         elif (val[0] == "Test Run Video 4:") :
              video = ET.SubElement(decision,'TestRunVideos').text="link to video1"
              video1 = ET.SubElement(decision,'TestRunVideos').text="link to video2"
              video2 = ET.SubElement(decision,'TestRunVideos').text="link to video3"
              video3 = ET.SubElement(decision,'TestRunVideos').text="link to video4"
            
         elif (val[0] == "Accuracy Required to Detect, Classify and Measure (Worst Case):") :
              accuracy = ET.SubElement(decision,'AccuracyRequiredtoDetectClassifyandMeasure')

         elif (val[0] == "Risk Calculation:"):
              video3 = ET.SubElement(decision,'RiskCalculation')
              
         elif (val[0] == "Policy Rational for the Decision:"):
              video3 = ET.SubElement(decision,'PolicyRationalfortheDecision')
         elif (val[0] == "Decision Summary:"):
              video3 = ET.SubElement(decision,'DecisionSummary')
         elif (val[0] == "Links:"):
              links = ET.SubElement(decision,'Links')
              video3 = ET.SubElement(links,'LinkstoPolarion')
              video3 = ET.SubElement(links,'LinkstoPolarionWorkItems')
              video3 = ET.SubElement(links,'FlagsProducedbyPolarionWorkItems')
              video3 = ET.SubElement(links,'LinkstoFARSDatabase')
              video3 = ET.SubElement(links,'LinkstoGoogleMaps')
             
            #element = ET.SubElement(data, val[0]).text = str(val[1])


    elif val[1] != "":
       
            if val[1] == "Story Owner":
               storyboard = ET.SubElement(data, 'Storyboard')
               story = ET.SubElement(storyboard, 'story',name="",owner=str(val[2]))
            if val[1] == "Act 1:" :
               Act = ET.SubElement(story, 'Act',name="1")
               condition = val[2].split(" ")
               item2 = ET.SubElement(Act,condition[0]).text=""
    elif val[2] != "":

            dataval2 = val[2].split(" ")
            datavalactor = val[3].split(" ")

            if dataval2[0] =="Sequence" :
              sequence = ET.SubElement(Act,dataval2[0],name=dataval2[1],noofexecution="1")
              element = ET.SubElement(sequence,datavalactor[0]).text= ""  
    elif val[3] != "":

            dataval3 = val[3].split(" ")
            namedata = val[4].split(" ")
            if dataval3[0] =="Maneuver" :  
               Maneuver = ET.SubElement(sequence,dataval3[0],name=dataval3[1])
               element = ET.SubElement(Maneuver,namedata[0]).text="" 

    elif val[4] != "":
          event = val[4].split(" ")

          
          if event[0] =="Event" :

          	
             encodedata = val[7].encode('cp437', 'ignore')
             afterdecode= encodedata.decode("utf-8") 
             decodeafter = afterdecode.split(" ")
             conditiongroup = decodeafter[0]
             condition  =decodeafter[1]
             bystate = decodeafter[2]+decodeafter[3]
             getevent = ET.SubElement(Maneuver,event[0],name=event[1],priority="overwrite")
             startcond = val[5].split(" ")
             startcondition = startcond[0]

    elif val[5] != "":
          Action = val[5].split(" ")
          Nameaction = val[6].split(" ")
          
          if Action[0] =="Action" :
            getAction = ET.SubElement(getevent,Action[0],name=Action[1])
            element1 = ET.SubElement(getAction,Nameaction[0])
            startconditiondata =ET.SubElement(getevent,startcondition)
            getcond = ET.SubElement(startconditiondata,conditiongroup)
            getgroup = ET.SubElement(getcond,condition,name="")
            getbystate = ET.SubElement(getgroup,bystate,)
            getstarttype =  ET.SubElement(getbystate,"AtStart",type="act",name="1")
                     
    
                
           
# items = ET.SubElement(data, 'items')  
# item1 = ET.SubElement(items, 'item')  
# item2 = ET.SubElement(items, 'item')  
# item1.set('name','item1')  
# item2.set('name','item2')  
# item1.text = 'item1abc'  
# item2.text = 'item2abc'

# create a new XML file with the results
tree = ET.ElementTree(data)
tree.write("file.xml")