title: Exporting All Data in a Loop to Excel in Python
body: <p>I am trying to write a list of data to an excel spreadsheet. I have 34 columns in excel that I need to input that data into. Column 1 is city1, column 2 is city 2, etc... </p>
  
  <p>This is the script I use to retrieve all the data I need for the spreadsheet.</p>
  
  <pre><code>#!usr/bin/env python
  
  import urllib
  from datetime import datetime
  from datetime import timedelta
  
  date = datetime.now()
  date1 = date + timedelta(days=1)
  date2 = date + timedelta(days=2)
  date3 = date + timedelta(days=3)
  date4 = date + timedelta(days=4)
  date5 = date + timedelta(days=5)
  date6 = date + timedelta(days=6)
  
  class city :
          def __init__(self, city_name, link) :
                  self.name = city_name
                  self.url = link
                  self.high0 = 0
                  self.high1 = 0
                  self.high2 = 0
                  self.high3 = 0
                  self.high4 = 0
                  self.high5 = 0
                  self.high6 = 0
                  self.high7 = 0
                  self.low1 = 0
                  self.low2 = 0
                  self.low3 = 0
                  self.low4 = 0
                  self.low5 = 0
                  self.low6 = 0
                  self.low7 = 0
  
          def retrieveTemps(self) :
                  filehandle = urllib.urlopen(self.url)
  
                  # get lines from result into array
                  lines = filehandle.readlines()
  
                  # (for each) loop through each line in lines
                  line_number = 0 # a counter for line number
                  for line in lines:
                          line_number = line_number + 1 # increment counter
  
                          # find string, position otherwise position is -1
  
                          position0 = line.rfind('title="{}"'.format(date.strftime("%A")))
                          position1 = line.rfind('title="{}"'.format(date1.strftime("%A")))
                          position2 = line.rfind('title="{}"'.format(date2.strftime("%A")))
                          position3 = line.rfind('title="{}"'.format(date3.strftime("%A")))
                          position4 = line.rfind('title="{}"'.format(date4.strftime("%A")))
                          position5 = line.rfind('title="{}"'.format(date5.strftime("%A")))
                          position6 = line.rfind('title="{}"'.format(date6.strftime("%A")))
                          if position0 &gt; 0 :
                                  self.high0 = lines[line_number + 4].split('&amp;')[0].split('&gt;')[-1]
                                  self.low1 = lines[line_number + 18].split('&amp;')[0].split('&gt;')[-1]
  
                          if position1 &gt; 0 :
                                  self.high1 = lines[line_number + 4].split('&amp;')[0].split('&gt;')[-1]
                                  self.low2 = lines[line_number + 19].split('&amp;')[0].split('&gt;')[-1]
  
                          if position2 &gt; 0 :
                                  self.high2 = lines[line_number + 4].split('&amp;')[0].split('&gt;')[-1]
                                  self.low3 = lines[line_number + 19].split('&amp;')[0].split('&gt;')[-1]
  
                          if position3 &gt; 0 :
                                  self.high3 = lines[line_number + 4].split('&amp;')[0].split('&gt;')[-1]
                                  self.low4 = lines[line_number + 19].split('&amp;')[0].split('&gt;')[-1]
  
                          if position4 &gt; 0 :
                                  self.high4 = lines[line_number + 4].split('&amp;')[0].split('&gt;')[-1]
                                  self.low5 = lines[line_number + 19].split('&amp;')[0].split('&gt;')[-1]
  
                          if position5 &gt; 0 :
                                  self.high5 = lines[line_number + 4].split('&amp;')[0].split('&gt;')[-1]
                                  self.low6 = lines[line_number + 19].split('&amp;')[0].split('&gt;')[-1]
                                  self.low7 = lines[line_number + 19].split('&amp;')[0].split('&gt;')[-1]
  
                          if position6 &gt; 0 :
                                  self.high6 = lines[line_number + 4].split('&amp;')[0].split('&gt;')[-1]
                                  self.high7 = lines[line_number + 4].split('&amp;')[0].split('&gt;')[-1]
  
                                  break # done with loop, break out of it
  
                  filehandle.close()
  
  #BRITISH COLUMBIA CITIES
  
  def getCities():
  
          c1 = city('Prince George', 'http://www.weatheroffice.gc.ca/city/pages/bc-79_metric_e.html')
          c2 = city('Kamloops', 'http://www.weatheroffice.gc.ca/city/pages/bc-45_metric_e.html')
          c3 = city('Blue River', 'http://www.weatheroffice.gc.ca/city/pages/bc-22_metric_e.html')
          c4 = city('High Level', 'http://www.weatheroffice.gc.ca/city/pages/ab-24_metric_e.html')
          c5 = city('Peace River', 'http://www.weatheroffice.gc.ca/city/pages/ab-25_metric_e.html')
          c6 = city('Jasper', 'http://www.weatheroffice.gc.ca/city/pages/ab-70_metric_e.html')
          c7 = city('Edmonton', 'http://www.weatheroffice.gc.ca/city/pages/ab-50_metric_e.html')
          c8 = city('Calgary', 'http://www.weatheroffice.gc.ca/city/pages/ab-52_metric_e.html')
  
  #SASKATCHEWAN CITIES
  
          c9 = city('Biggar', 'http://www.weatheroffice.gc.ca/city/pages/sk-2_metric_e.html')
          c10 = city('Saskatoon', 'http://www.weatheroffice.gc.ca/city/pages/sk-40_metric_e.html')
          c11 = city('Melville', 'http://www.weatheroffice.gc.ca/city/pages/sk-8_metric_e.html')
          c12 = city('Canora', 'http://www.weatheroffice.gc.ca/city/pages/sk-3_metric_e.html')
          c13 = city('Yorkton', 'http://www.weatheroffice.gc.ca/city/pages/sk-33_metric_e.html')
  
  #MANITOBA CITIES
  
          c14 = city('Winnipeg', 'http://www.weatheroffice.gc.ca/city/pages/mb-38_metric_e.html')
          c15 = city('Sprague', 'http://www.weatheroffice.gc.ca/city/pages/mb-23_metric_e.html')
  
  #ONTARIO CITIES
  
          c16 = city('Thunder Bay', 'http://www.weatheroffice.gc.ca/city/pages/on-100_metric_e.html')
          c17 = city('Sioux Lookout', 'http://www.weatheroffice.gc.ca/city/pages/on-135_metric_e.html')
          c18 = city('Armstrong', 'http://www.weatheroffice.gc.ca/city/pages/on-111_metric_e.html')
          c19 = city('Hornepayne', 'http://www.weatheroffice.gc.ca/city/pages/on-78_metric_e.html')
          c20 = city('Sudbury', 'http://www.weatheroffice.gc.ca/city/pages/on-40_metric_e.html')
          c21 = city('South Parry', 'http://www.weatheroffice.gc.ca/city/pages/on-103_metric_e.html')
          c22 = city('Toronto', 'http://www.weatheroffice.gc.ca/city/pages/on-143_metric_e.html')
          c23 = city('Kingston', 'http://www.weatheroffice.gc.ca/city/pages/on-69_metric_e.html')
          c24 = city('Cornwall', 'http://www.weatheroffice.gc.ca/city/pages/on-152_metric_e.html')
  
  #QUEBEC CITIES
  
          c25 = city('Montreal', 'http://www.weatheroffice.gc.ca/city/pages/qc-147_metric_e.html')
          c26 = city('Quebec', 'http://www.weatheroffice.gc.ca/city/pages/qc-133_metric_e.html')
          c27 = city('La Tuque', 'http://www.weatheroffice.gc.ca/city/pages/qc-154_metric_e.html')
          c28 = city('Saguenay', 'http://www.weatheroffice.gc.ca/city/pages/qc-166_metric_e.html')
          c29 = city('Riviere-du-loup', 'http://www.weatheroffice.gc.ca/city/pages/qc-108_metric_e.html')
  
  #NOVA SCOTIA CITIES
  
          c30 = city('Truro', 'http://www.weatheroffice.gc.ca/city/pages/ns-25_metric_e.html')
          c31 = city('Halifax', 'http://www.weatheroffice.gc.ca/city/pages/ns-19_metric_e.html')
  
  #NEW BRUNSWICK CITIES
  
          c32 = city('Edmundston', 'http://www.weatheroffice.gc.ca/city/pages/nb-32_metric_e.html')
          c33 = city('Moncton', 'http://www.weatheroffice.gc.ca/city/pages/nb-36_metric_e.html')
          c34 = city('Sarnia', 'http://www.weatheroffice.gc.ca/city/pages/on-147_metric_e.html')
  
          cities = []
          cities.append(c1)
          cities.append(c2)
          cities.append(c3)
          cities.append(c4)
          cities.append(c5)
          cities.append(c6)
          cities.append(c7)
          cities.append(c8)
          cities.append(c9)
          cities.append(c10)
          cities.append(c11)
          cities.append(c12)
          cities.append(c13)
          cities.append(c14)
          cities.append(c15)
          cities.append(c16)
          cities.append(c17)
          cities.append(c18)
          cities.append(c19)
          cities.append(c20)
          cities.append(c21)
          cities.append(c22)
          cities.append(c23)
          cities.append(c24)
          cities.append(c25)
          cities.append(c26)
          cities.append(c27)
          cities.append(c28)
          cities.append(c29)
          cities.append(c30)
          cities.append(c31)
          cities.append(c32)
          cities.append(c33)
          cities.append(c34)
  
  return (cities)
  </code></pre>
  
  <p>I then run this script to export all the data to an excel file:</p>
  
  <pre><code>#!/usr/bin/env python
  
  from xlutils.copy import copy
  from xlrd import open_workbook
  
  import canada
  
  #import os
  
  #os.chdir("/data/ops/Ops Documents/MexTemps")
  
  
  cities = canada.getCities()
  
  for c in cities :
          c.retrieveTemps()
  
  ## 
  # writing to excel
  ##
  file_name = 'fcst_hilo_TEST.xls'
  new_file_name = 'fcst_hilo.xls'
  row = 1
  
  # column constants
  high0_col = 1
  low1_col = 2
  high1_col = 3
  low2_col = 4
  high2_col = 5
  low3_col = 6
  high3_col = 7
  low4_col = 8
  high4_col = 9
  low5_col = 10
  high5_col = 11
  low6_col = 12
  high6_col = 13
  low7_col = 14
  high7_col = 15
  
  workbook_file = None
  try :
          # currently xlwt does not implement this option for xslx files
          workbook_file = open_workbook(file_name, formatting_info=True)
  except :
          workbook_file = open_workbook(file_name)
  
  workbook = copy(workbook_file)
  sheet = workbook.get_sheet(0)
  
  for c in cities :
          sheet.write(row, high0_col, c.high0)
          sheet.write(row, low1_col, c.low1)
          sheet.write(row, high1_col, c.high1)
          sheet.write(row, low2_col, c.low2)
          sheet.write(row, high2_col, c.high2)
          sheet.write(row, low3_col, c.low3)
          sheet.write(row, high3_col, c.high3)
          sheet.write(row, low4_col, c.low4)
          sheet.write(row, high4_col, c.high4)
          sheet.write(row, low5_col, c.low5)
          sheet.write(row, high5_col, c.high5)
          sheet.write(row, low6_col, c.low6)
          sheet.write(row, high6_col, c.high6)
          sheet.write(row, low7_col, c.low7)
          sheet.write(row, high7_col, c.high7)
  
  workbook.save(new_file_name)
  </code></pre>
  
  <p>When I run this script it inputs all the data for the last city (c34) into the first row (which is supposed to be for c1, the first city). It is running through the loop in the first script, but only exporting the last line it encounters into the excel spreadsheet. Any ideas on how to get it to enter the data for everything in the loop? Thanks!</p>
  
  <p>EDIT: </p>
  
  <p>The last thing I need to do is assign each city to a separate column. For example: "city1" would need to go to column 2, "city2" to column 4, "city3" to column 8, etc... Any ideas?</p>