def getInfo(self): if self.activate == 1: wb = Workbook() sheet1 = wb.add_sheet('Sheet 1') sheet1.write(0, 0, self.email) sheet1.write(1, 0, self.TphoneNo) sheet1.write(2, 0, self.TapiKey) sheet1.write(3, 0, self.TapiSec) sheet1.write(4, 0, self.PhoneNo) sheet1.write(5, 0, self.Wapi) sheet1.write(6, 0, self.Wpsecret) wb.save('user.xls') else: loc = ("user.xls") wb = xlrd.open_workbook(loc) sheet = wb.sheet_by_index(0) self.email = sheet.cell_value(0, 0) self.TphoneNo = sheet.cell_value(1, 0) self.TapiKey = sheet.cell_value(2, 0) self.TapiSec = sheet.cell_value(3, 0) self.PhoneNo = sheet.cell_value(4, 0) self.Wapi = sheet.cell_value(5, 0) self.Wpsecret = sheet.cell_value(6, 0) return self.email, self.TphoneNo, self.TapiKey, self.TapiSec, self.PhoneNo, self.Wapi, self.Wpsecret
def test_report_general(self): # Prepare r = Report() r.caption = 'test_report_0' hs = r.cols hs.add_field('col 0') hs.add_field('col 1') hss = hs.add_section('col section 0') hss.add_field('col 2_1') hss.add_calc('col total', sum) hs.add_field('col 3') hs.add_calc('col total 0', func=lambda x: 1.0*sum(x)/len(x)) rs = r.rows rs.add_field('row 0') rss = rs.add_section('row section 0') rss.add_field('row 1_1') rss.add_field('row 1_2') rss.add_calc('row total 0', func=sum) rs.add_field('row 2') rs.add_field('row 3') rs.add_calc('row total 1', func=sum) book = Workbook() ws = book.add_sheet('test worksheet') data = (range(4) for i in range(5)) r.render(ws, data) compiled_report = StringIO() book.save(compiled_report) # Test book = xlrd.open_workbook(file_contents=compiled_report.getvalue()) ws = book.sheet_by_index(0) test_data = [ [0.0, 1.0, 2.0, 2.0, 3.0, 1.5], [0.0, 1.0, 2.0, 2.0, 3.0, 1.5], [0.0, 1.0, 2.0, 2.0, 3.0, 1.5], [0.0, 2.0, 4.0, 4.0, 6.0, 3.0], [0.0, 1.0, 2.0, 2.0, 3.0, 1.5], [0.0, 1.0, 2.0, 2.0, 3.0, 1.5], [0.0, 5.0, 10.0, 10.0, 15.0, 7.5] ] for i in range(3,10): for j in range(2,8): self.assertEquals(ws.cell(i,j).value, test_data[i-3][j-2])
def test_report_calc_2(self): """Test fields feature""" # Prepare r = Report() hs = r.cols c0 = hs.add_field('col 0') c1 = hs.add_field('col 1') hs.add_field('col ignore') hs.add_calc('col total', sum, fields=(c0,c1)) rs = r.rows r0 = rs.add_field('row 0') r1 = rs.add_field('row 1') rs.add_field('row ignore') rs.add_calc('row total', sum, fields=(r0,r1)) data = ((1,1,1),(2,2,2),(3,3,3)) book = Workbook() ws = book.add_sheet('test worksheet') r.render(ws, data) compiled_report = StringIO() book.save(compiled_report) #book.save(test_file) # Test book = xlrd.open_workbook(file_contents=compiled_report.getvalue()) ws = book.sheet_by_index(0) test_data = [ [1.0, 1.0, 1.0, 2.0], [2.0, 2.0, 2.0, 4.0], [3.0, 3.0, 3.0, 6.0], [3.0, 3.0, 3.0, 6.0], ] #system("oowriter %s" % test_file) for i in range(4): for j in range(4): self.assertEquals(ws.cell(i+1,j+1).value, test_data[i][j])
def test_report_calc_1(self): """Test cross_fields_ignore feature""" # Prepare r = Report() r.caption = 'test_report_calc_1' hs = r.cols hs.add_field('col 0') hs.add_field('col 1') col2 = hs.add_field('col ignore') hs.add_calc('col total', sum) rs = r.rows rs.add_field('row 0') rs.add_field('row 1') rs.add_field('row 2') rs.add_calc('row total', sum, cross_fields_ignore=(col2,)) data = ((1,1,1),(2,2,2),(3,3,3)) book = Workbook() ws = book.add_sheet('test worksheet') r.render(ws, data) compiled_report = StringIO() book.save(compiled_report) book.save(test_file) # Test book = xlrd.open_workbook(file_contents=compiled_report.getvalue()) ws = book.sheet_by_index(0) test_data = [ [1.0, 1.0, 1.0, 3.0], [2.0, 2.0, 2.0, 6.0], [3.0, 3.0, 3.0, 9.0], [6.0, 6.0, '', 12.0], ] for i in range(4): for j in range(4): self.assertEquals(ws.cell(i+2,j+1).value, test_data[i][j])
import xlrd #To read Excel File import csv # importing csv module import numpy as np # #To generate Random Number from xlwt import Workbook #To perforn Excel file I/O operation temp = 0 #Variable # create Workbook object from openpyxl wb=Workbook() # Give the location of the file loc = ("questions.xlsx") wb = xlrd.open_workbook(loc) #Opening Excel Workbook of Our Given Location sheet = wb.sheet_by_index(0) #Select the Sheet Number(0,1,2....) #No. of Questions are = qnlenth qnlenth=sheet.nrows #Length of Sheet Questions qnlist=[] #Array of Questions qnnumber=[] #Array of Questions Number optn1=[] #Array of Option 1 optn2=[] #Array of Option 2 optn3=[] #Array of Option 3 optn4=[] #Array of Option 4 optn5=[] #Array of Option 5 trait=[] #Array of Trait(Extraversion, Agreeableness, Conscientiousness, Emotional Stability, Intellect ) #Reading ALL Values of from file in array formate: for i in range (qnlenth): qnnumber=np.append(qnnumber,(sheet.cell_value(i, 0) )) qnlist=np.append(qnlist,(sheet.cell_value(i, 1) )) optn1=np.append(optn1,(sheet.cell_value(i, 2) ))
def operate(loc, key): # Workbook is created wb = Workbook() # To open Workbook wb = xlrd.open_workbook(loc) sheet = wb.sheet_by_index(0) # Keeps track of the type of data file that's being used oldFile = False newFile = False # Global variables that will be needed for making necessary calculations currRow = 0 currCol = 0 # New Files Variables undergradLoans = 0 undergradRecipients = 0 gradRecipients = 0 gradLoans = 0 # Old Files Variables numRecipients = 0 loanTotal = 0 # find correct row, currCol will remain as zero for currRow in range(sheet.nrows): if sheet.cell_value(currRow, currCol) == "OPE ID": currRow -= 1 break # find correct col for currCol in range(sheet.ncols): # Old Files Calculations if sheet.cell_value(currRow, currCol) == "DL UNSUBSIDIZED": oldFile = True # Finding the Recipients Row for currRow in range(sheet.nrows): if sheet.cell_value(currRow, currCol) == "Recipients": numRecipients = total_Number_Of_Recipients( currRow, currCol, wb) break # Finding the $ of Loans Originated Column currCol += 2 loanTotal = total_Amount_Of_Loans(currRow, currCol, wb) print("Number of recipients is:{:,.2f}".format(numRecipients)) print("Total Amount of loans is:${:,.2f}".format(loanTotal)) totalAverageLoansCollection[key] = loanTotal / numRecipients break # New Files Calculations elif sheet.cell_value( currRow, currCol ) == "DL UNSUBSIDIZED - UNDERGRADUATE" or sheet.cell_value( currRow, currCol) == "DL UNSUBSIDIZED- UNDERGRADUATE": newFile = True # Determines the loan cost and # of recipients for undergrad students for currRow in range(sheet.nrows): if sheet.cell_value(currRow, currCol) == "Recipients": undergradRecipients = total_Number_Of_Recipients( currRow, currCol, wb) break # Finding the $ of Loans Originated Column for undergrads currCol += 2 undergradLoans = total_Amount_Of_Loans(currRow, currCol, wb) # Checking to make sure that we have the correct values print("Number of undergrad recipients is:{:,.2f}".format( undergradRecipients)) print("Total Amount of undergrad loans is:${:,.2f}".format( undergradLoans)) undergradAverageLoansCollection[ key] = undergradLoans / undergradRecipients # Determines the loan cost and # of recipients for grad students currRow -= 1 for currCol in range(sheet.ncols): if sheet.cell_value( currRow, currCol ) == "DL UNSUBSIDIZED - GRADUATE" or sheet.cell_value( currRow, currCol) == "DL UNSUBSIDIZED- GRADUATE": # Finding number of grad recipients for currRow in range(sheet.nrows): if sheet.cell_value(currRow, currCol) == "Recipients": gradRecipients = total_Number_Of_Recipients( currRow, currCol, wb) break if gradRecipients > 0: break # Finding total loan cost for grad recipients currCol += 2 gradLoans = total_Amount_Of_Loans(currRow + 1, currCol, wb) print( "Number of grad recipients is:{:,.2f}".format(gradRecipients)) print("Total Amount of grad loans is:${:,.2f}".format(gradLoans)) gradAverageLoansCollection[key] = gradLoans / gradRecipients if newFile == True: averageLoan = (undergradLoans + gradLoans) / (undergradRecipients + gradRecipients) else: averageLoan = loanTotal / numRecipients print("The average loan is:${:,.2f}".format(averageLoan)) averageLoans[key] = averageLoan
def on_error(self, status): print(status) s = input("Enter search keyword - ") count = int(input("Enter Number of tweets to be analyzed - ")) query = "https://twitter.com/search?q=" + s webbrowser.open_new(query) twitterStream = Stream(auth, tweetlistener()) twitterStream.filter(track=[s], languages=["en"]) print() loc = (s + ".xls") wb = xlrd.open_workbook(loc) sheet = wb.sheet_by_index(0) country = [] for i in range(1, count + 1): if (sheet.cell_value(i, 2) != ""): try: country.append(getplace(sheet.cell_value(i, 2))) except: s = 0 x = set(country) print(len(country), len(x)) y = [] for i in x: