Databases
StackOverflow2013
Postsdbo
POExporting Data to a Specific Row in Excel using Python
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExporting Data to a Specific Row in Excel using Python
    primarykey
    data
    text
    <p>I am writing a bunch of data to an excel file and as of now the script I have exports all the data to every row from row 1 to row 36. Each row contains 15 columns worth of data as can be seen in the code below. I need to somehow send the data from the first city in the loop to row1, the data from the second city to row 3, the data from the third city to row 6, etc... I can't do something like </p> <pre><code>row = row + 2 </code></pre> <p>because they need to be assigned to predetermined rows that are not evenly spaced.</p> <p>This is the script that sends the data to excel:</p> <pre><code>#!/usr/bin/env python from xlutils.copy import copy from xlrd import open_workbook import canada 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_names = ["high0", "low1", "high1", "low2", "high2", "low3", "high3", "low4", "high4", "low5", "high5", "low6", "high6", "low7", "high7"] 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 row, city in enumerate(cities, start=1): for column, col_name in enumerate(column_names, start=2): sheet.write(row, column, getattr(city, col_name)) workbook.save(new_file_name) </code></pre> <p>I have 36 cities that are all listed in a separate script (below) This is part of 'canada', what is imported into the script above:</p> <pre><code>class city(object): 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() filehandle.close() # (for each) loop through each line in lines for line_number, line in enumerate(lines, start=1): # 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 def getCities(): return [ #BRITISH COLUMBIA CITIES city('Prince George', 'http://www.weatheroffice.gc.ca/city/pages/bc-79_metric_e.html'), city('Kamloops', 'http://www.weatheroffice.gc.ca/city/pages/bc-45_metric_e.html'), city('Blue River', 'http://www.weatheroffice.gc.ca/city/pages/bc-22_metric_e.html'), # Alberta city('High Level', 'http://www.weatheroffice.gc.ca/city/pages/ab-24_metric_e.html'), city('Peace River', 'http://www.weatheroffice.gc.ca/city/pages/ab-25_metric_e.html'), city('Jasper', 'http://www.weatheroffice.gc.ca/city/pages/ab-70_metric_e.html'), city('Edmonton', 'http://www.weatheroffice.gc.ca/city/pages/ab-50_metric_e.html'), city('Calgary', 'http://www.weatheroffice.gc.ca/city/pages/ab-52_metric_e.html'), #SASKATCHEWAN CITIES city('Biggar', 'http://www.weatheroffice.gc.ca/city/pages/sk-2_metric_e.html'), city('Saskatoon', 'http://www.weatheroffice.gc.ca/city/pages/sk-40_metric_e.html'), city('Melville', 'http://www.weatheroffice.gc.ca/city/pages/sk-8_metric_e.html'), city('Canora', 'http://www.weatheroffice.gc.ca/city/pages/sk-3_metric_e.html'), city('Yorkton', 'http://www.weatheroffice.gc.ca/city/pages/sk-33_metric_e.html'), #MANITOBA CITIES city('Winnipeg', 'http://www.weatheroffice.gc.ca/city/pages/mb-38_metric_e.html'), city('Sprague', 'http://www.weatheroffice.gc.ca/city/pages/mb-23_metric_e.html'), #ONTARIO CITIES city('Thunder Bay', 'http://www.weatheroffice.gc.ca/city/pages/on-100_metric_e.html'), city('Sioux Lookout', 'http://www.weatheroffice.gc.ca/city/pages/on-135_metric_e.html'), city('Armstrong', 'http://www.weatheroffice.gc.ca/city/pages/on-111_metric_e.html'), city('Hornepayne', 'http://www.weatheroffice.gc.ca/city/pages/on-78_metric_e.html'), city('Sudbury', 'http://www.weatheroffice.gc.ca/city/pages/on-40_metric_e.html'), city('South Parry', 'http://www.weatheroffice.gc.ca/city/pages/on-103_metric_e.html'), city('Toronto', 'http://www.weatheroffice.gc.ca/city/pages/on-143_metric_e.html'), city('Kingston', 'http://www.weatheroffice.gc.ca/city/pages/on-69_metric_e.html'), city('Cornwall', 'http://www.weatheroffice.gc.ca/city/pages/on-152_metric_e.html'), city('Sarnia', 'http://www.weatheroffice.gc.ca/city/pages/on-147_metric_e.html'), #QUEBEC CITIES city('Montreal', 'http://www.weatheroffice.gc.ca/city/pages/qc-147_metric_e.html'), city('Quebec', 'http://www.weatheroffice.gc.ca/city/pages/qc-133_metric_e.html'), city('La Tuque', 'http://www.weatheroffice.gc.ca/city/pages/qc-154_metric_e.html'), city('Saguenay', 'http://www.weatheroffice.gc.ca/city/pages/qc-166_metric_e.html'), city('Riviere-du-loup', 'http://www.weatheroffice.gc.ca/city/pages/qc-108_metric_e.html'), #NOVA SCOTIA CITIES city('Truro', 'http://www.weatheroffice.gc.ca/city/pages/ns-25_metric_e.html'), city('Halifax', 'http://www.weatheroffice.gc.ca/city/pages/ns-19_metric_e.html'), #NEW BRUNSWICK CITIES city('Edmundston', 'http://www.weatheroffice.gc.ca/city/pages/nb-32_metric_e.html'), city('Moncton', 'http://www.weatheroffice.gc.ca/city/pages/nb-36_metric_e.html'), ] </code></pre> <p>Any suggestions would be appreciated. Thank you!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. UShunter21188
    1. UShunter21188
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COWhat determines what line a given city's data should be written to? If there's no way to know that, there's no way to solve this problem.
      singulars
      1. POExporting Data to a Specific Row in Excel using Python
      1. USBlckknght
    2. COI am importing to a base excel spreadsheet and every city has its own assigned row. I just only showed a couple of cities and their respective rows, but they are all pre-determined.
      singulars
      1. POExporting Data to a Specific Row in Excel using Python
      1. UShunter21188
    3. COSo is the city going to the row "random" as you say or now they are predetermined? You are being contradictory
      singulars
      1. POExporting Data to a Specific Row in Excel using Python
      1. USRyan Saxe
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload