CODEWITHSUNDEEP
pythonstringloops
Syntax Rommel
Syntax Rommel

Reputation: 942

String concatenation inside for loop is very slow

I'm trying to create xml output then generate excel file, On my loop script it's working on thousand of data but for now I'm having a million data and takes too long and sometimes it hang up, I just want to know if there is any way that I can use to optimize the loop here.

Usage:

loop = 10000 works fine

loop >= 100000 result too slow and stack

class ExcelHyperlink:
  def __init__(self,sheetName,displayName):
    self.sheetName = sheetName.replace(" ","_")
    self.displayName = displayName
  def toCellString(self):
    sheet = escapeHTML(self.sheetName)
    display = escapeHTML(self.displayName)
    return '<Cell ss:StyleID="s63" ss:HRef="#%s!A1"><Data ss:Type="String">%s</Data></Cell>\n' % (sheet,display)    

def getCellString(value):
  if isinstance(value,ExcelHyperlink):
    return value.toCellString()
  else:
    return "<Cell><Data ss:Type=\"String\">%s</Data></Cell>\n" % (value)

loop = 10000
data = [{u'test': 0, u'a': 0, u'b': 0},{u'test': 1, u'a': 1, u'b': 1},{u'test': 2, u'a': 2, u'b': 2},{u'test': 3, u'a': 3, u'b': 3},{u'test': 4, u'a': 4, u'b': 4}] * loop

headers = ['test', 'a', 'b']


xml = '''<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"><WindowHeight>600</WindowHeight><WindowWidth>800</WindowWidth><WindowTopX>0</WindowTopX><WindowTopY>0</WindowTopY><ProtectStructure>False</ProtectStructure><ProtectWindows>False</ProtectWindows></ExcelWorkbook>
<Styles><Style ss:ID="Default" ss:Name="Normal"><Alignment ss:Vertical="Bottom"/><Borders/><Font/><Interior/><NumberFormat/><Protection/></Style><Style ss:ID="s21"><NumberFormat ss:Format="General Date"/></Style><Style ss:ID="s63" ss:Name="Hyperlink"><Font ss:Family="Arial" ss:Color="#0563C1" ss:Underline="Single"/></Style><Style ss:ID="s23"><Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/><Font x:Family="Swiss" ss:Bold="1"/></Style></Styles>
'''

for row in data:  
  xml += "<Row>\n"
  for item in headers:    
    #xml += str(row)
    xml += getCellString(row[item])
  xml += "</Row>\n"
xml += '''</Table>                
  
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Selected/>
<Panes></Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
'''
xml += "</Workbook>"

Upvotes: 0

Views: 212

Answers (1)

cs95
cs95

Reputation: 402353

Strings in python are immutable. All these repeated string addition operations are wasteful, because new memory and objects must be allocated and created, and data copied - at each iteration.

I'd suggest putting everything inside a list and calling str.join at the end.

xml_artefacts = []
for row in data:  
    xml_artefacts.append("<Row>\n")
    for item in headers:    
        xml_artefacts.append(getCellString(row[item]))
    xml_artefacts.append("</Row>\n")

xml_artefacts.append('''</Table>  ...  </Workbook>''')

Now, perform concatenation with the xml string.

xml += ''.join(xml_artefacts)

Upvotes: 2

Related Questions