Ndtyjky

I have written this Python script to create a sheet with only the athletes from our sports club from the national rankings. At the moment I have to download the rankings file and then re-name it.

#import the writer

import xlwt

#import the reader

import xlrd

#open the rankings spreadsheet

book = xlrd.open_workbook('rankings.xls')

#open the first sheet

first_sheet = book.sheet_by_index(0)

#print the values in the second column of the first sheet

print first_sheet.col_values(1)





#open the spreadsheet

workbook = xlwt.Workbook()

#add a sheet named "Club BFA ranking"

worksheet1 = workbook.add_sheet("Club BFA ranking")

#in cell 0,0 (first cell of the first row) write "Ranking"

worksheet1.write(0, 0, "Ranking")

#in cell 0,1 (second cell of the first row) write "Name"

worksheet1.write(0, 1, "Name")    

#save and create the spreadsheet file

workbook.save("saxons.xls")



name = 

rank = 

for i in range(first_sheet.nrows):

    #print(first_sheet.cell_value(i,3)) 

    if('Saxon' in first_sheet.cell_value(i,3)):  

        name.append(first_sheet.cell_value(i,1))

        rank.append(first_sheet.cell_value(i,8))    

        print('a')

for j in range(len(name)):

    worksheet1.write(j+1,0,rank[j])

    worksheet1.write(j+1,1,name[j])





workbook.save("saxons.xls")

As a next iteration I would like it to go to a specific URL and download the latest spreadsheet to use as rankings.xls

How can I do that?

You could use the requests library. For example,

import requests



url = "YOUR_URL" 

downloaded_file = requests.get(url)



with open("YOUR_PATH/rankings.xls", 'wb') as file:  

    file.write(downloaded_file.content)

EDIT: You mentioned that you wanted to download the latest version of the file, you can use time as below to fill in the month & year.

time.strftime("https://www.britishfencing.com/wp-content/uploads/%Y/%m/ranking_file.xls")

as YOUR_URLto get the latest month's rankings.

I'm not sure, what you mean with "latest" spreadsheet, but you have various options to download files from the net. I'd suggest to use the famous requests library which is very, very easy to use.

Do a

pip install requests

before doing a

import requests

url = "http://foobar.com/rankings.xls"

r = requests.get(url)

then push the contents into a file

with open('./rankings.xls', 'w') as f:

    f.write(r.content)

So it would be possible to check if your recently downloaded rankings.xls is newer than a previously downloaded rankins.xls by comparing them using a hashcode or so.

EDIT: OP asked for a method to extract the latest xls file from the page. I'd suggest to parse the html for hrefs containing xls (as the page OP wants to parse is providing no common format for the xls files to be downloaded).

Best way to do this would be BeautifulSoup:

 pip install bs4



 from bs4 import BeautifulSoup

 import requests



 x=requests.get('https://www.britishfencing.com/results-rankings/mens-foil-ranking-archive/')

 soup = BeautifulSoup(x.content, 'html.parser')

 result = [ xls['href'] for xls in soup.find_all('a', href=True) if 'xls' in xls['href']]



 print(result[0])