Excel to XML Conversion Issue

[Shaan Shar]

Hello Everyone - I was trying to use a very basic excel (literally very basic) to create a well formed XML. I was able to create 90% of XML but where there multiple tags in XML, at that point of time, I was failing.

Request you to review it and let me know, if I am doing something terribly wrong or any help/suggestions? I am attaching sample excel and XML which I am trying to created with this program.

This is how my excel looks like



import xml.etree.ElementTree as ET from openpyxl import load_workbook from yattag import Doc, indent # Load our Excel File wb = load_workbook("Input.xlsx") # Getting an object of active sheet 1 ws = wb.worksheets[0] # Returning returns a triplet doc, tag, text = Doc().tagtext() xml_header = '<?xml version="1.0" encoding="UTF-8"?>' xml_schema = '<PromotionPolicies xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">' xml_footer = '</GroupMultibuyEligibility></PriceDerivationRuleEligibility></PromotionPolicy></PromotionPolicies>' xml_promo = '<PromotionPolicy>' xml_promotion_id = '<PromotionID>unique-id-1</PromotionID>' xml_promotion_type_id = '<PromotionTypeID>2</PromotionTypeID>' xml_promotion_type = '<PromotionType>MULTIBUYGROUP</PromotionType>' xml_effective_start_date = '<EffectiveStartDate>2019-01-01T00:00:00</EffectiveStartDate>' xml_effective_end_date = '<EffectiveEndDate>2026-03-26T00:00:00</EffectiveEndDate>' xml_oper_display_text = '<OperatorDisplayText>Buy 1 item and get one free</OperatorDisplayText>' xml_cust_display_text = '<CustomerDisplayText>Buy 1 item and get one free</CustomerDisplayText>' xml_receipt_print_text = '<ReceiptPrintText>Buy 1 item and get one free</ReceiptPrintText>' xml_distributed_savings = '<DistributedSavings>ON_REWARDS_ONLY</DistributedSavings>' xml_applicable_business_unit_ids = '<ApplicableBusinessUnitIds><ApplicableBusinessUnitId>1</ApplicableBusinessUnitId><ApplicableBusinessUnitId>2</ApplicableBusinessUnitId><ApplicableBusinessUnitId>3</ApplicableBusinessUnitId><ApplicableBusinessUnitId>4</ApplicableBusinessUnitId><ApplicableBusinessUnitId>5</ApplicableBusinessUnitId><ApplicableBusinessUnitId>6</ApplicableBusinessUnitId><ApplicableBusinessUnitId>7</ApplicableBusinessUnitId><ApplicableBusinessUnitId>8</ApplicableBusinessUnitId><ApplicableBusinessUnitId>9</ApplicableBusinessUnitId><ApplicableBusinessUnitId>10</ApplicableBusinessUnitId><ApplicableBusinessUnitId>11</ApplicableBusinessUnitId><ApplicableBusinessUnitId>12</ApplicableBusinessUnitId><ApplicableBusinessUnitId>13</ApplicableBusinessUnitId><ApplicableBusinessUnitId>14</ApplicableBusinessUnitId><ApplicableBusinessUnitId>15</ApplicableBusinessUnitId><ApplicableBusinessUnitId>16</ApplicableBusinessUnitId><ApplicableBusinessUnitId>17</ApplicableBusinessUnitId><ApplicableBusinessUnitId>18</ApplicableBusinessUnitId><ApplicableBusinessUnitId>19</ApplicableBusinessUnitId><ApplicableBusinessUnitId>20</ApplicableBusinessUnitId><ApplicableBusinessUnitId>21</ApplicableBusinessUnitId><ApplicableBusinessUnitId>22</ApplicableBusinessUnitId><ApplicableBusinessUnitId>23</ApplicableBusinessUnitId><ApplicableBusinessUnitId>24</ApplicableBusinessUnitId><ApplicableBusinessUnitId>25</ApplicableBusinessUnitId><ApplicableBusinessUnitId>26</ApplicableBusinessUnitId><ApplicableBusinessUnitId>27</ApplicableBusinessUnitId><ApplicableBusinessUnitId>28</ApplicableBusinessUnitId><ApplicableBusinessUnitId>29</ApplicableBusinessUnitId><ApplicableBusinessUnitId>30</ApplicableBusinessUnitId><ApplicableBusinessUnitId>31</ApplicableBusinessUnitId><ApplicableBusinessUnitId>32</ApplicableBusinessUnitId><ApplicableBusinessUnitId>33</ApplicableBusinessUnitId><ApplicableBusinessUnitId>34</ApplicableBusinessUnitId><ApplicableBusinessUnitId>35</ApplicableBusinessUnitId><ApplicableBusinessUnitId>36</ApplicableBusinessUnitId><ApplicableBusinessUnitId>37</ApplicableBusinessUnitId><ApplicableBusinessUnitId>38</ApplicableBusinessUnitId><ApplicableBusinessUnitId>39</ApplicableBusinessUnitId><ApplicableBusinessUnitId>40</ApplicableBusinessUnitId><ApplicableBusinessUnitId>41</ApplicableBusinessUnitId><ApplicableBusinessUnitId>42</ApplicableBusinessUnitId><ApplicableBusinessUnitId>43</ApplicableBusinessUnitId><ApplicableBusinessUnitId>44</ApplicableBusinessUnitId><ApplicableBusinessUnitId>45</ApplicableBusinessUnitId><ApplicableBusinessUnitId>46</ApplicableBusinessUnitId><ApplicableBusinessUnitId>47</ApplicableBusinessUnitId><ApplicableBusinessUnitId>48</ApplicableBusinessUnitId><ApplicableBusinessUnitId>49</ApplicableBusinessUnitId><ApplicableBusinessUnitId>50</ApplicableBusinessUnitId><ApplicableBusinessUnitId>51</ApplicableBusinessUnitId><ApplicableBusinessUnitId>52</ApplicableBusinessUnitId><ApplicableBusinessUnitId>53</ApplicableBusinessUnitId><ApplicableBusinessUnitId>54</ApplicableBusinessUnitId><ApplicableBusinessUnitId>55</ApplicableBusinessUnitId><ApplicableBusinessUnitId>56</ApplicableBusinessUnitId><ApplicableBusinessUnitId>57</ApplicableBusinessUnitId><ApplicableBusinessUnitId>58</ApplicableBusinessUnitId><ApplicableBusinessUnitId>59</ApplicableBusinessUnitId><ApplicableBusinessUnitId>60</ApplicableBusinessUnitId><ApplicableBusinessUnitId>61</ApplicableBusinessUnitId><ApplicableBusinessUnitId>62</ApplicableBusinessUnitId><ApplicableBusinessUnitId>63</ApplicableBusinessUnitId><ApplicableBusinessUnitId>64</ApplicableBusinessUnitId><ApplicableBusinessUnitId>65</ApplicableBusinessUnitId><ApplicableBusinessUnitId>66</ApplicableBusinessUnitId><ApplicableBusinessUnitId>67</ApplicableBusinessUnitId></ApplicableBusinessUnitIds><DiscountAppliesTo>ALST</DiscountAppliesTo>' xml_price_derivation_rule_eligibility = '<PriceDerivationRuleEligibility>' xml_group_multibuy_eligibility = '<GroupMultibuyEligibility>' xml_rule = '<ThresholdTypeCode>GROUP_INTERVAL_QTY_THRESHOLD</ThresholdTypeCode><GroupTriggerCombinationCode>OR</GroupTriggerCombinationCode><RewardCombinationCode>OR</RewardCombinationCode><GroupThresholdQuantity>2</GroupThresholdQuantity><GroupRewardQuantity>1</GroupRewardQuantity><MaxTriggersPerTransaction>1</MaxTriggersPerTransaction><Benefit><PriceModificationMethodCode>PERCENTAGE_OFF</PriceModificationMethodCode><PriceModificationPercent>100.00</PriceModificationPercent></Benefit>' xml_trigger = '<Trigger>TRUE</Trigger><GetReward>TRUE</GetReward>' # Appends the String to document doc.asis(xml_header) doc.asis(xml_schema) doc.asis(xml_promo) doc.asis(xml_promotion_id) doc.asis(xml_promotion_type_id) doc.asis(xml_promotion_type) doc.asis(xml_effective_start_date) doc.asis(xml_effective_end_date) doc.asis(xml_oper_display_text) doc.asis(xml_cust_display_text) doc.asis(xml_receipt_print_text) doc.asis(xml_distributed_savings) doc.asis(xml_applicable_business_unit_ids) doc.asis(xml_price_derivation_rule_eligibility) doc.asis(xml_group_multibuy_eligibility) doc.asis(xml_rule) #with tag('People'): for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=ws.max_column): row = [cell.value for cell in row] with tag("MultibuyProduct"): with tag("ProductID"): text(row[0]) #        with tag('Trigger'): #           text('TRUE') #        with tag('GetReward'): #           text('TRUE')         doc.asis(xml_footer) result = indent( doc.getvalue(), indentation='', indent_text=False ) with open("output.xml", "w") as f: f.write(result) tree = ET.parse('output.xml') root = tree.getroot() print(root)

[Shaan Shar]

Here's an example of the Excel sheet

[Shaan Shar]

for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=ws.max_column): row = [cell.value for cell in row] with tag("MultibuyProduct"): with tag("ProductID"): text(row[0])        with tag('Trigger'):           text('TRUE')        with tag('GetReward'):           text('TRUE')

When I tried to enable second "with tag" and run it in Python, it was throwing following error in console. Any idea, where I am doing incorrect indentation.

 File "C:\Users\shaan\Desktop\Python Utility\Excel.py", line 57
   with tag('Trigger'):
TabError: inconsistent use of tabs and spaces in indentation