Use Selenium Automates Firefox to Download Reports from Website

#!c:/python27/python
#filename : GetPaetecReport.py
#Version 1.3

'''
Created on Oct 18, 2012
@author: chu
'''

import time, os, getpass, win32com.client
from selenium import webdriver
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.support.ui import Select, WebDriverWait



def endprg():
    print ""
    raw_input ("Press Enter to close.")
 

 
#Download Reports
def getrept(accounts):
    id = raw_input("Login ID:")
    pw = getpass.getpass()
    filepath=os.path.join(os.getcwd(),'Paetec')
# Setup FirefoxProfile.
    fp = webdriver.FirefoxProfile()
    fp.set_preference("browser.download.folderList",2)
    fp.set_preference("browser.download.manager.showWhenStarting",False)
    fp.set_preference("browser.download.manager.closeWhenDone",True)
    fp.set_preference("browser.helperApps.alwaysAsk.force",False)
    fp.set_preference("browser.download.dir",filepath)
    fp.set_preference("browser.helperApps.neverAsk.saveToDisk","application/vnd.ms-excel")
# use Firefox to download reports
    print ""
    print "Downloading reports from wxxxstreamonline.com....."
    print ""
    driver = webdriver.Firefox(firefox_profile=fp)
    driver.get('https://www.wxxxstreamonline.com/pol/Home.action')
    time.sleep(2)
# Login to website
    driver.find_element_by_id("username").clear()
    driver.find_element_by_id("username").send_keys(id)
    driver.find_element_by_id("password").clear()
    driver.find_element_by_id("password").send_keys(pw)
    driver.find_element_by_id("submitButton").click()
    time.sleep(3)
    driver.find_element_by_link_text('View My Billing Reports').click()
    time.sleep(1)
# Get into different accounts
    for account in accounts:
        Select(driver.find_element_by_name("selectedCustomer")).select_by_visible_text(account)
        time.sleep(1)
        Select(driver.find_element_by_id("reportList")).select_by_visible_text("Usage Billed Outbound")
        driver.find_element_by_id("viewReport").click()
        time.sleep(15)
        for handle in driver.window_handles:
            driver.switch_to_window(handle)
            if driver.title == 'Wxxxstream Online' :
                break
        Select(driver.find_element_by_id("ReportViewer1_ctl01_ctl05_ctl00")).select_by_visible_text("Excel")
        driver.find_element_by_link_text('Export').click()
        time.sleep(10)
        while True:
            if len(driver.window_handles) > 2:
                time.sleep(5)
            else:
                break
        driver.close()
        driver.switch_to_window(driver.window_handles[0])
        time.sleep(8)
#rename downloaded file
        if os.path.exists(os.path.join(filepath,'Billed Usage.xls')):
            filename = account[-7:] + '.xls'
            os.rename(os.path.join(filepath,'Billed Usage.xls'), os.path.join(filepath,filename))
            time.sleep(2)
#Logout from Paeteconline.com      
    driver.find_element_by_partial_link_text('Logout').click()
    time.sleep(1)
    driver.close()



#Generate Excel Report
def genrept(accounts):
    xl=win32com.client.Dispatch('Excel.Application')
    xl.Visible=0
    filepath=os.path.join(os.getcwd(),'Paetec')
    reportname = time.strftime("%Y%m") + "01"
#Copy sheets
    i=1
    for account in accounts:
        filename=os.path.join(filepath,(account[-7:]+'.xls'))
        if account[-7:] == '4xxxx91':
            wb=xl.Workbooks.Open(filename)
            sh=wb.Worksheets(i)
            sh.Name=account[-7:]
        else:
            wbt=xl.Workbooks.Open(filename)
            sht=wbt.Worksheets(1)
            sht.Copy (None,wb.Worksheets(i-1))
            wbt.Close()
            sh=wb.Worksheets(i)
            sh.Name=account[-7:]
        i+=1
    shnew=wb.Worksheets.Add(None,wb.Worksheets(i-1))
    shnew.Name=reportname
#Copy Cells
    for i in range(1,wb.Worksheets.Count):
        sh=wb.Worksheets(i)
        if i==1:
            startrow=5
        else:
            startrow=6
        sh.Range(sh.Cells(startrow,1),sh.Cells(sh.UsedRange.Rows.Count,sh.UsedRange.Columns.Count)).Copy()
        shnew.Paste(shnew.Cells(shnew.UsedRange.Rows.Count+startrow-5,1))
#Adjust new sheet
    shnew.Columns("D:D").Delete()
    shnew.UsedRange.WrapText=False
    shnew.Columns("A:I").AutoFit()
    shnew.Sort.SortFields.Clear()
    shnew.Range(shnew.Cells(2,1),shnew.Cells(shnew.UsedRange.Rows.Count,shnew.UsedRange.Columns.Count)).Sort(Key1=shnew.Range("A2"), Order1=1, Key2=shnew.Range("F2"), Order2=1,Key3=shnew.Range("C2"), Order3=1)
    newreportpath = os.path.join(filepath,(reportname+'.xls'))
    wb.SaveAs(newreportpath)
    wb.Close()
    xl.Application.Quit()
#Print to console
    print ""
    print "New report is saved on %s." %newreportpath
    print ""


print "*******************************************************************"
print "**    This program will download the last month                  **"
print "**    Usage Billed Outbound reports from wxxxxstreamonline.com    **"
print "**    And will generate a new Excel Report.                      **"
print "**                                                Version 1.3    **"
print "*******************************************************************"
print ""

accounts = ["Mxxx - 4xxxx91", "Mxxx - Hoboken, NJ - 4xxxx36", "Mxxx - Kent, WA - 4xxxx11","Mxxx - NY, NY - 4xxxx34"]
goodtogo = 'yes'

try:
    getrept(accounts)
except:
    print "Failed to download reports from wxxxstreamonline.com."
    goodtogo = "no"

if goodtogo == 'yes':
    try:
        genrept(accounts)
    except:
        print "Failed to generate new excel reports."

endprg()