def writeHtml(w_filename, r_filename,sheetname):
book = open_workbook(r_filename)
sheet = book.sheet_by_name(sheetname)
print sheet.nrows, 'rows and ' , sheet.ncols , 'cols in this sheet.'
for col_index in range(sheet.ncols):
print cellname(0,col_index),'-',
print sheet.cell(0,col_index).value
print
f = open(w_filename,'w')
f.write('<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>')
f.write('<table>')
for row_index in range(sheet.nrows):
f.write('<tr>')
for col_index in range(sheet.ncols):
f.write('<td>')
if sheet.cell(row_index,col_index).ctype == 1:
value = sheet.cell(row_index,col_index).value.encode('UTF-8')
else:
value = ''
print value
# f.write(value)
f.write('</td>')
f.write('</tr>')
f.write('</table>')
f.close()
def data_amend_report(workbook, gl, ibm, nc_sp, pvs_sp, fm_sp):
# Sheet 1
sheet = workbook.get_sheet(0)
# Download hyperlink:
sheet.write(1, 0, Formula('HYPERLINK("{}")'.format(settings.IBM_DATA_AMEND_URI)))
# Padded zeroes number format
pad2, pad3, pad4 = XFStyle(), XFStyle(), XFStyle()
pad2.num_format_str = '00'
pad3.num_format_str = '000'
pad4.num_format_str = '0000'
current_row = 3
code_id = ''
for row, data in enumerate(gl, current_row):
# Only insert GLPivDownload objects with a matching IBMData object.
if ibm.filter(ibmIdentifier=data.codeID).exists():
# We have to aggregate all the GLPivotDownload objects with
# matching codeID values, and insert one row with total
# ytdActual and fyBudget values.
if code_id != data.codeID: # Reached the next codeID value.
current_row += 1 # Advance one row.
code_id = data.codeID
i = ibm.get(ibmIdentifier=data.codeID)
sheet.write(current_row, 0, data.codeID)
sheet.write(current_row, 1, int(data.costCentre), pad3)
sheet.write(current_row, 2, data.account, pad2)
sheet.write(current_row, 3, data.service, pad2)
sheet.write(current_row, 4, data.activity, pad3)
try:
sheet.write(current_row, 5, int(data.project), pad4)
except ValueError:
sheet.write(current_row, 5, data.project, pad4)
try:
sheet.write(current_row, 6, int(data.job), pad3)
except ValueError:
sheet.write(current_row, 6, data.job, pad3)
sheet.write(current_row, 7, data.jobName)
sheet.write(current_row, 8, data.activityName)
sheet.write(current_row, 9, data.projNameNo)
sheet.write(current_row, 10, i.budgetArea)
sheet.write(current_row, 11, i.projectSponsor)
sheet.write(current_row, 14, i.regionalSpecificInfo)
sheet.write(current_row, 15, i.servicePriorityID)
sheet.write(current_row, 18, i.annualWPInfo)
sheet.write(current_row, 19, data.mPRACategory)
ytd = gl.filter(codeID=code_id).aggregate(Sum('ytdActual'))
fy = gl.filter(codeID=code_id).aggregate(Sum('fybudget'))
sheet.write(current_row, 20, ytd['ytdActual__sum'])
sheet.write(current_row, 21, fy['fybudget__sum'])
# Insert the footer row formulae and '#END OF INPUT'
sheet.write(current_row+2, 0, '#END OF INPUT')
sheet.write(current_row+2, 20, Formula('SUM({}:{})'.format(cellname(4, 20), cellname(current_row, 20))))
sheet.write(current_row+2, 21, Formula('SUM({}:{})'.format(cellname(4, 21), cellname(current_row, 21))))
# Sheet 2 - Service priority checkboxes.
sheet = workbook.get_sheet(1)
write_service_priorities(sheet, nc_sp, pvs_sp, fm_sp)
def excelToArray(s, x, y, colMieng, col15Phut, colMotTiet, arrMark, arrTime, timeNow, timeToEdit, position, isNx,
diffMessage):
lengthCol = [colMieng, col15Phut, colMotTiet]
for t in range(3):
for i in range(lengthCol[t]):
value = unicode(s.cell(x, y + i).value)
value = value.replace(' ', '')
value = value.replace(',', '.')
if isNx:
value = convertCharToDigit(value)
if (isDifference(value, arrMark[t * MAX_COL + i + 1])) & (arrTime[t * MAX_COL + i + 1] != ''):
diffMessage[0] += cellname(x, y + i) + ' '
if ((arrTime[t * MAX_COL + i + 1] != '') & (position != 4)):
if (timeNow - int(arrTime[t * MAX_COL + i + 1])) > timeToEdit:
if isDifference(value, arrMark[t * MAX_COL + i + 1]):
return u" Ô " + cellname(x, y + i) + u' không được sửa điểm.'
if (value == ""):
arrTime[t * MAX_COL + i + 1] = ""
elif isDifference(value, arrMark[t * MAX_COL + i + 1]):
arrTime[t * MAX_COL + i + 1] = str(timeNow)
arrMark[t * MAX_COL + i + 1] = value
y += lengthCol[t]
return ""
def validate(s, isNx):
x = 11
y = 4
if isNx == False:
for i in range(x, s.nrows):
for j in range(4, s.ncols):
try:
value = unicode(s.cell(i, j).value)
value = value.replace(' ', '')
value = value.replace(',', '.')
if len(value) != 0:
value1 = float(value)
if (value1 < 0) | (value1 > 10):
return u'Điểm ở ô ' + cellname(i, j) + u' không nằm trong [0,10] '
except Exception as e:
# print "aaaaaa"
return u'Điểm ở ô ' + cellname(i, j) + u' không hợp lệ'
else:
for i in range(x, s.nrows):
for j in range(4, s.ncols):
try:
value = unicode(s.cell(i, j).value)
value = value.replace(' ', '').lower()
if len(value) != 0:
if (value != u'đ') & (value != u'cđ') & (value != u'd') & (value != u'cd'):
return u'Lỗi ở ô ' + cellname(i, j) + u'. Chỉ dùng các kí tự sau để cho điểm D,Đ,CD,CĐ.'
except Exception as e:
# print e
return u'Lỗi ở ô ' + cellname(i, j) + u'. Chỉ dùng các kí tự sau để cho điểm D,Đ,CD,CĐ.'
return ''
def validate(s,isNx):
x=11
y=4
if isNx==False:
for i in range(x,s.nrows):
for j in range(4,20):
try:
value = str(s.cell(i,j).value)
value = value.replace(' ','')
if len(value) !=0:
value1 = float(value)
if (value1<0) | (value1>10):
return u'Điểm ở ô '+cellname(i,j)+u' không nằm trong [0,10] '
except Exception as e:
return u'Điểm ở ô '+cellname(i,j)+u' không hợp lệ'
return ''
else:
for i in range(x,s.nrows):
for j in range(4,23):
try:
value = str(s.cell(i,j).value)
value = value.replace(' ','').lower()
if len(value)!=0:
if (value!='g') & (value!='k') & (value!='tb') & (value!='y') & (value!='kem') & (value!='kém') :
return u'Lỗi ở ô '+cellname(i,j)+u'. Chỉ dùng các kí tự sau để cho điểm G,K,TB,Y,Kem.'
except Exception as e:
return u'Lỗi ở ô '+cellname(i,j)+ u'. Chỉ dùng các kí tự sau để cho điểm G,K,TB,Y,Kem.'
return ''
def print_labels(sh, labs, title):
if not labs:return
for rlo, rhi, clo, chi in labs:
print("%s label range %s:%s contains:"
% (title, xlrd.cellname(rlo, clo), xlrd.cellname(rhi-1, chi-1)))
for rx in xrange(rlo, rhi):
for cx in xrange(clo, chi):
print(" %s: %r" % (xlrd.cellname(rx, cx), sh.cell_value(rx, cx)))
def introspect_surveysheet(sheet):
print sheet.name
print sheet.nrows
print sheet.ncols
for row_index in range(sheet.nrows):
for col_index in range(sheet.ncols):
print xlrd.cellname(row_index, col_index), '-', #nur den index-namen einer zelle e.g. A1 B4 etc.
print sheet.cell(row_index, col_index).value #wert einer zelle e.g. 'steuer'
return
def map_batsmen_stats():
player_stats = {}
player_matchid = {}
for row_index in range(2,sheet.nrows):
for col_index in range(1,sheet.ncols):
if cellname(row_index,col_index)[0] == 'E':
curr_batsmen = sheet.cell(row_index,col_index).value
if curr_batsmen not in player_matchid:
player_matchid[curr_batsmen] = []
if curr_batsmen in player_stats:
player_stats[curr_batsmen][0]+=sheet.cell(row_index,col_index+3).value
else:
player_stats[curr_batsmen] = [sheet.cell(row_index,col_index+3).value,0,0]
curr_match_id = sheet.cell(row_index,col_index-2).value
if curr_match_id not in player_matchid[curr_batsmen]:
player_stats[curr_batsmen][2]+=1
player_matchid[curr_batsmen].append(curr_match_id)
if cellname(row_index,col_index)[0] == 'N':
dismissed_batsmen = sheet.cell(row_index,col_index).value
if dismissed_batsmen in player_stats:
player_stats[dismissed_batsmen][1]+=1
else:
player_stats[dismissed_batsmen] = [0,1,1]
curr_match_id = sheet.cell(row_index,col_index-11).value
if dismissed_batsmen in player_matchid:
player_matchid[dismissed_batsmen].append(curr_match_id)
else:
player_matchid[dismissed_batsmen]=[]
player_matchid[dismissed_batsmen].append(curr_match_id)
#Create new sheet
workbook,batsmen_stat_sheet = create_new_sheet("batsmen_stats.xls")
#Initialize rows,columns
row_count = 0
batsmen_stat_sheet.write(0,0,"Player Name")
batsmen_stat_sheet.write(0,1,"Number of Innings")
batsmen_stat_sheet.write(0,2,"Total Runs")
batsmen_stat_sheet.write(0,3,"Not Outs")
batsmen_stat_sheet.write(0,4,"Batting Average")
row_count+=1
for x in player_stats:
batsmen_stat_sheet.write(row_count,0,x)
batsmen_stat_sheet.write(row_count,1,player_stats[x][2])
batsmen_stat_sheet.write(row_count,2,player_stats[x][0])
batsmen_stat_sheet.write(row_count,3,player_stats[x][2]-player_stats[x][1])
if (player_stats[x][1]):
batsmen_stat_sheet.write(row_count,4,
"%.2f"%float((player_stats[x][0])/(player_stats[x][1])))
else:
batsmen_stat_sheet.write(row_count,4,0)
row_count+=1
workbook.close()
def complete_batsmen_performance():
sheet1 = open("grey_relation_grades2011.xls")
sheet2 = open("grey_relation_grades2012.xls")
sheet3 = open("grey_relation_grades2013.xls")
sheet4 = open("grey_relation_grades2014.xls")
total_batsmen_grd = {}
for row_index in range(1,sheet1.nrows):
for col_index in range(sheet1.ncols):
if cellname(row_index,col_index)[0] == 'A':
curr_batsmen = sheet1.cell(row_index,col_index).value
curr_grade = sheet1.cell(row_index,col_index+1).value
if curr_batsmen not in total_batsmen_grd:
total_batsmen_grd[curr_batsmen] = 5*curr_grade
for row_index in range(1,sheet2.nrows):
for col_index in range(sheet2.ncols):
if cellname(row_index,col_index)[0] == 'A':
curr_batsmen = sheet2.cell(row_index,col_index).value
curr_grade = sheet2.cell(row_index,col_index+1).value
if curr_batsmen not in total_batsmen_grd:
total_batsmen_grd[curr_batsmen] = curr_grade
else:
total_batsmen_grd[curr_batsmen] += 35*curr_grade
for row_index in range(1,sheet3.nrows):
for col_index in range(sheet3.ncols):
if cellname(row_index,col_index)[0] == 'A':
curr_batsmen = sheet3.cell(row_index,col_index).value
curr_grade = sheet3.cell(row_index,col_index+1).value
if curr_batsmen not in total_batsmen_grd:
total_batsmen_grd[curr_batsmen] = curr_grade
else:
total_batsmen_grd[curr_batsmen] += 45*curr_grade
for row_index in range(1,sheet4.nrows):
for col_index in range(sheet4.ncols):
if cellname(row_index,col_index)[0] == 'A':
curr_batsmen = sheet4.cell(row_index,col_index).value
curr_grade = sheet4.cell(row_index,col_index+1).value
if curr_batsmen not in total_batsmen_grd:
total_batsmen_grd[curr_batsmen] = curr_grade
else:
total_batsmen_grd[curr_batsmen] += 15*curr_grade
#Create new sheet
workbook,grey_relation_grades_sheet = create_new_sheet(
"final_grey_relation_grades.xls"
)
#Initialize rows,columns
row_count = 0
grey_relation_grades_sheet.write(0,0,"Player Name")
grey_relation_grades_sheet.write(0,1,"Grade")
row_count+=1
for x in total_batsmen_grd:
grey_relation_grades_sheet.write(row_count,0,x)
grey_relation_grades_sheet.write(row_count,1,total_batsmen_grd[x]/100.0)
row_count+=1
workbook.close()
def read_xls(file_path,sheet_name):
"""读取xls中的数据
:param file_path 文件路径
:param sheet_name sheet名
:return header,rows
"""
workbook = xlrd.open_workbook('成绩单.xls')
for booksheet in workbook.sheets():
print booksheet.name
for row in xrange(booksheet.nrows):
for col in xrange(booksheet.ncols):
print xlrd.cellname(row, col)
print booksheet.cell(row, col).value
def grades_dictionary(book_name):
book=open_workbook(book_name)
sheet=book.sheet_by_index(0)
gradebook={}
# Temporary lists to store our course codes,names and grades after iteration
templist1=[]
templist2=[]
templist3=[]
i=1
for row_index in range(sheet.nrows):
for col_index in range(sheet.ncols):
# 'A' represent course code
if 'A' in cellname(row_index,col_index):
# We don't care about the first row so we skip it
if i==1:
i=i+1
continue
a=sheet.cell(row_index,col_index).value
a=a.encode('ascii') # This is to get rid of the unicode character
templist1.append(a)
if 'B' in cellname(row_index,col_index):
if i==2:
i=i+1
continue
b=sheet.cell(row_index,col_index).value
b=b.encode('ascii')
templist2.append(b)
if 'C' in cellname(row_index,col_index):
if i==3:
i=i+1
continue
c=sheet.cell(row_index,col_index).value
c=c.encode('ascii')
templist3.append(c)
subject_list=[]
# We now combine the course code and course name into one name
for index, item in enumerate(templist2):
subject_list.append(templist1[index]+' '+templist2[index])
# We call a different function to convert letter grades to their numeric value
grades_to_digits=convert_letter_to_dig(templist3)
# We append the subject and numeric grade to the gradebook then return it
for index,item in enumerate(subject_list):
gradebook[item]=grades_to_digits[index]
return gradebook
def check_total_runs(): total_runs=0 for row_index in range(2,sheet.nrows): for col_index in range(1,sheet.ncols): if cellname(row_index,col_index)[0] == 'H': total_runs+=sheet.cell(row_index,col_index).value return total_runs
def parseSheet(infile,outfile):
"""
Taken From: https://github.com/willu47/git_diff_xlsx/blob/master/git_diff_xlsx.py
Converts an Excel file into text
Returns a formatted text file for comparison using git diff.
"""
book = xl.open_workbook(infile)
num_sheets = book.nsheets
print( book.sheet_names())
# print "File last edited by " + book.user_name + "\n"
outfile.write("File last edited by " + book.user_name + "\n")
def get_cells(sheet, rowx, colx):
return sheet.cell_value(rowx, colx)
# loop over worksheets
for index in range(0,num_sheets):
# find non empty cells
sheet = book.sheet_by_index(index)
outfile.write("=================================\n")
outfile.write("Sheet: " + sheet.name + "[ " + str(sheet.nrows) + " , " + str(sheet.ncols) + " ]\n")
outfile.write("=================================\n")
for row in range(0,sheet.nrows):
for col in range(0,sheet.ncols):
content = get_cells(sheet, row, col)
if content != "":
outfile.write(" " + str(xl.cellname(row,col)) + ": " + str(content) + "\n")
print( "\n")
def most_catches_stumps(sheet_name):
catches = {}
stumps = {}
sheet = open(sheet_name)
run_outs = 0
rvic = {}
for row_index in range(1,sheet.nrows):
for col_index in range(0,sheet.ncols):
if cellname(row_index,col_index)[0] == 'Y':
wicket_kind = sheet.cell(row_index,col_index).value
fielder = sheet.cell(row_index,col_index+1).value
victim = sheet.cell(row_index,col_index+2).value
if wicket_kind == "run out":
run_outs+=1
if victim not in rvic:
rvic[victim] = 0
rvic[victim]+=1
if wicket_kind == "caught":
if fielder not in catches:
catches[fielder] = 0
catches[fielder]+=1
if wicket_kind == "stumps":
if fielder not in stumps:
stumps[fielder] = 0
stumps[fielder]+=1
print(run_outs)
s_rvic = sorted(rvic.items(), key=operator.itemgetter(1))
s_catches = sorted(catches.items(), key=operator.itemgetter(1))
s_stumps = sorted(stumps.items(), key=operator.itemgetter(1))
print(s_rvic)
print(s_catches)
print(s_stumps)
def ducks_stats(sheet_name):
sheet = open(sheet_name)
batsmen_duck_count = {}
for row_index in range(1,sheet.nrows):
for col_index in range(0,sheet.ncols):
if cellname(row_index,col_index)[0] == 'A':
curr_player = sheet.cell(row_index,col_index+1).value
player_runs = sheet.cell(row_index,col_index+2).value
is_notout = sheet.cell(row_index,col_index+4).value
if player_runs == 0 and is_notout == "NO":
if curr_player not in batsmen_duck_count:
batsmen_duck_count[curr_player]=0
batsmen_duck_count[curr_player]+=1
#Create new sheet
workbook,ducks_stats_sheet = create_new_sheet("ducks_stats.xls")
#Initialize rows,columns
row_count = 0
ducks_stats_sheet.write(0,0,"Player Name")
ducks_stats_sheet.write(0,1,"Ducks Count")
row_count+=1
for x in batsmen_duck_count:
ducks_stats_sheet.write(row_count,0,x)
ducks_stats_sheet.write(row_count,1,batsmen_duck_count[x])
row_count+=1
workbook.close()
def parse(infile, outfile):
"""
Converts an Excel file into text
Returns a formatted text file for comparison using git diff.
"""
book = xl.open_workbook(infile)
num_sheets = book.nsheets
print book.sheet_names()
# print "File last edited by " + book.user_name + "\n"
outfile.write("File last edited by " + book.user_name + "\n")
def get_cells(sheet, rowx, colx):
return sheet.cell_value(rowx, colx)
# loop over worksheets
for index in range(0, num_sheets):
# find non empty cells
sheet = book.sheet_by_index(index)
outfile.write("=================================\n")
outfile.write("Sheet: " + sheet.name + "[ " + str(sheet.nrows) + " , " + str(sheet.ncols) + " ]\n")
outfile.write("=================================\n")
for row in range(0, sheet.nrows):
for col in range(0, sheet.ncols):
content = get_cells(sheet, row, col)
if content <> "":
outfile.write(" " + unicode(xl.cellname(row, col)) + ": " + unicode(content) + "\n")
print "\n"
def convert_xl_row(xlrdbook, xlrdsheetname, rownum):
'''Converts an xlrd excel worksheet row into a standard python format.
Empty or blank cells -> None
Number or text -> float or string
Boolean -> True or False
Date -> date string as rendered by datetime.datetime
Raises ValueError if a cell has an error.'''
xlrdsheet = xlrdbook.sheet_by_name(xlrdsheetname)
ret = []
for i, cell in enumerate(xlrdsheet.row(rownum)):
if (cell.ctype == _xlrd.XL_CELL_EMPTY or
cell.ctype == _xlrd.XL_CELL_BLANK):
ret.append(None)
elif (cell.ctype == _xlrd.XL_CELL_NUMBER or
cell.ctype == _xlrd.XL_CELL_TEXT):
ret.append(cell.value)
elif cell.ctype == _xlrd.XL_CELL_BOOLEAN:
ret.append(bool(cell.value))
elif cell.ctype == _xlrd.XL_CELL_DATE:
dt = _xlrd.xldate_as_tuple(cell.value, xlrdbook.datemode)
d = str(_datetime.datetime(*dt))
ret.append(d)
elif cell.ctype == _xlrd.XL_CELL_ERROR:
raise ValueError(
' '.join(['Cell', _xlrd.cellname(rownum, i), 'in sheet',
xlrdsheet.name, 'has an error']))
else:
raise ValueError('Unknown cell type') # error in xlrd
return ret
def dump_sheet(sheet, sheet_index, max_rows):
dump_record("s", {
"index": sheet_index,
"name": sheet.name,
"rows": sheet.nrows,
"columns": sheet.ncols,
"visibility": sheet.visibility
})
for rowx in range(max_rows or sheet.nrows):
for colx in range(sheet.ncols):
try:
cell = sheet.cell(rowx, colx)
dump_record("c", {
"r": rowx,
"c": colx,
"a": cellname(rowx, colx),
"v": parse_cell_value(sheet, cell)
})
except:
e0, e1 = sys.exc_info()[:2]
dump_record("id", {
"type": "dump_sheet_failed",
"sheet_name": sheet.name,
"row": rowx,
"column": colx,
"exception": e0.__name__,
"details": e1.message
})
def parse(file,outfile):
book = xl.open_workbook(file)
num_sheets = book.nsheets
print book.sheet_names()
# print "File last edited by " + book.user_name + "\n"
outfile.write("File last edited by " + book.user_name + "\n")
def get_cells(sheet, rowx, colx):
return sheet.cell_value(rowx, colx)
# loop over worksheets
for index in range(0,num_sheets):
# find non empty cells
sheet = book.sheet_by_index(index)
outfile.write("=================================\n")
outfile.write("Sheet: " + sheet.name + "[ " + str(sheet.nrows) + " , " + str(sheet.ncols) + " ]\n")
outfile.write("=================================\n")
for row in range(0,sheet.nrows):
for col in range(0,sheet.ncols):
content = get_cells(sheet, row, col)
if content <> "":
outfile.write(" " + str(xl.cellname(row,col)) + ": " + str(content) + "\n")
print "\n"
def get_sheet_values(self, sheetname, includeEmptyCells=True):
"""
Returns the values from the sheet name specified.
Arguments:
| Sheet Name (string) | The selected sheet that the cell values will be returned from. |
| Include Empty Cells (default=True) | The empty cells will be included by default. To deactivate and only return cells with values, pass 'False' in the variable. |
Example:
| *Keywords* | *Parameters* |
| Open Excel | C:\\Python27\\ExcelRobotTest\\ExcelRobotTest.xls |
| Get Sheet Values | TestSheet1 |
"""
my_sheet_index = self.sheetNames.index(sheetname)
sheet = self.wb.sheet_by_index(my_sheet_index)
data = {}
for row_index in range(sheet.nrows):
for col_index in range(sheet.ncols):
cell = cellname(row_index, col_index)
value = sheet.cell(row_index, col_index).value
data[cell] = value
if includeEmptyCells is True:
sortedData = natsort.natsorted(data.items(), key=itemgetter(0))
return sortedData
else:
data = dict([(k, v) for (k, v) in data.items() if v])
OrderedData = natsort.natsorted(data.items(), key=itemgetter(0))
return OrderedData
def map_match_bowler_stats():
match_bowler_stats = {}
for row_index in range(2,sheet.nrows):
for col_index in range(1,sheet.ncols):
if cellname(row_index,col_index)[0] == 'C':
curr_match_id = sheet.cell(row_index,col_index).value
curr_bowler = sheet.cell(row_index,col_index+14).value
curr_wides = sheet.cell(row_index,col_index+17).value
curr_nb = sheet.cell(row_index,col_index+18).value
curr_byes = sheet.cell(row_index,col_index+19).value
curr_lbyes = sheet.cell(row_index,col_index+20).value
total_runs = sheet.cell(row_index,col_index+21).value
curr_runs = total_runs - curr_byes - curr_lbyes;
if curr_match_id in match_bowler_stats:
if curr_bowler in match_bowler_stats[curr_match_id]:
if curr_wides == 0 and curr_nb == 0:
match_bowler_stats[curr_match_id][curr_bowler][0]+=1
match_bowler_stats[curr_match_id][curr_bowler][1]+=curr_runs
is_wicket = sheet.cell(row_index,col_index+22).value
if is_wicket in ["caught","caught and bowled","stumped","bowled","lbw"]:
match_bowler_stats[curr_match_id][curr_bowler][2]+=1
else:
match_bowler_stats[curr_match_id][curr_bowler]= [0,0,0]
if curr_wides == 0 and curr_nb == 0:
match_bowler_stats[curr_match_id][curr_bowler][0]+=1
match_bowler_stats[curr_match_id][curr_bowler][1]+=curr_runs
is_wicket = sheet.cell(row_index,col_index+22).value
if is_wicket in ["caught","caught and bowled","stumped","bowled","lbw"]:
match_bowler_stats[curr_match_id][curr_bowler][2]+=1
else:
match_bowler_stats[curr_match_id] = {}
match_bowler_stats[curr_match_id][curr_bowler]= [0,0,0]
if curr_wides == 0 and curr_nb == 0:
match_bowler_stats[curr_match_id][curr_bowler][0]+=1
match_bowler_stats[curr_match_id][curr_bowler][1]+=curr_runs
is_wicket = sheet.cell(row_index,col_index+22).value
if is_wicket in ["caught","caught and bowled","stumped","bowled","lbw"]:
match_bowler_stats[curr_match_id][curr_bowler][2]+=1
#Create new sheet
workbook,match_bowler_stats_sheet = create_new_sheet("match_bowler_stats.xls")
#Initialize rows,columns
row_count = 0
match_bowler_stats_sheet.write(0,0,"Match ID")
match_bowler_stats_sheet.write(0,1,"Bowler")
match_bowler_stats_sheet.write(0,2,"Balls")
match_bowler_stats_sheet.write(0,3,"Runs")
match_bowler_stats_sheet.write(0,4,"Wickets")
row_count+=1
#Write into workbook
for x in match_bowler_stats:
for y in match_bowler_stats[x]:
match_bowler_stats_sheet.write(row_count,0,x)
match_bowler_stats_sheet.write(row_count,1,y)
match_bowler_stats_sheet.write(row_count,2,match_bowler_stats[x][y][0])
match_bowler_stats_sheet.write(row_count,3,match_bowler_stats[x][y][1])
match_bowler_stats_sheet.write(row_count,4,match_bowler_stats[x][y][2])
row_count+=1
workbook.close()
def get_cellname(self,rowx,colx):
'''
输入坐标，返回单元格名称 (5, 7) => 'H6'
:param rowx: 行坐标
:param colx: 列坐标
'''
return xlrd.cellname(rowx, colx)
def score_batsmen_match():
matchid_players_run = {}
for row_index in range(2,sheet.nrows):
for col_index in range(1,sheet.ncols):
if cellname(row_index,col_index)[0] == 'C':
curr_match_id = sheet.cell(row_index,col_index).value
dismissed_batsmen = sheet.cell(row_index,col_index+11).value
if curr_match_id not in matchid_players_run:
matchid_players_run[curr_match_id]={}
curr_batsmen = sheet.cell(row_index,col_index+2).value
nonstricker_batsmen = sheet.cell(row_index,col_index+4).value
curr_runs = sheet.cell(row_index,col_index+5).value
curr_ball = sheet.cell(row_index,col_index+6).value
if curr_ball > 0:
wide = 1
else:
wide = 0
if curr_batsmen in matchid_players_run[curr_match_id]:
matchid_players_run[curr_match_id][curr_batsmen][0]+=curr_runs
matchid_players_run[curr_match_id][curr_batsmen][1]+=(1-wide)
else:
matchid_players_run[curr_match_id][curr_batsmen]=[curr_runs,1-wide,0]
if nonstricker_batsmen not in matchid_players_run[curr_match_id]:
matchid_players_run[curr_match_id][nonstricker_batsmen] = [0,0,0]
if dismissed_batsmen!="" and dismissed_batsmen in matchid_players_run[curr_match_id]:
matchid_players_run[curr_match_id][dismissed_batsmen][2] = 1
#Create new sheet
workbook,batsmen_match_stat_sheet = create_new_sheet("batsmen_match_stats.xls")
#Initialize rows,columns
row_count = 0
batsmen_match_stat_sheet.write(0,0,"Match ID")
batsmen_match_stat_sheet.write(0,1,"Player")
batsmen_match_stat_sheet.write(0,2,"Runs")
batsmen_match_stat_sheet.write(0,3,"Balls Faced")
batsmen_match_stat_sheet.write(0,4,"Strike Rate")
batsmen_match_stat_sheet.write(0,4,"NO")
row_count+=1
for x in matchid_players_run:
for y in matchid_players_run[x]:
batsmen_match_stat_sheet.write(row_count,0,x)
batsmen_match_stat_sheet.write(row_count,1,y)
batsmen_match_stat_sheet.write(row_count,2,matchid_players_run[x][y][0])
batsmen_match_stat_sheet.write(row_count,3,matchid_players_run[x][y][1])
if (matchid_players_run[x][y][1]):
batsmen_match_stat_sheet.write(row_count,4,
"%.2f"%(float((matchid_players_run[x][y][0])/
(matchid_players_run[x][y][1]))*100)
)
else:
batsmen_match_stat_sheet.write(row_count,4,0)
if matchid_players_run[x][y][2] == 1:
batsmen_match_stat_sheet.write(row_count,4,"NO")
else:
batsmen_match_stat_sheet.write(row_count,4,"YES")
row_count+=1
workbook.close()
def loadOriginal(path = 'Original.xls'):
wb = open_workbook(path)
#print dir(wb)
s = wb.sheet_by_index(0)
for row in range(s.nrows):
for col in range(s.ncols):
v = s.cell(row,col).value
if (v != ''):
val.append((cellname(row,col), row, col, v))
def C(start, end=None):
""" Returns excel cells cpecified between [start; end] or only at start position.
:param start: first cell
:param end: last cell
:return: cells to return
"""
start = get_indices_from_name(start)
if end is None:
return [xlrd.cellname(*start)]
end = get_indices_from_name(end)
start, end = min(start, end), max(start, end)
return [
xlrd.cellname(row, col)
for row in range(start[0], end[0]+1)
for col in range(start[1], end[1]+1)
]
def _retrieve_dataset_assesments(self):
self._log.info("\tRetrieving dataset assesments")
dataset_obs_sheets = self._get_dataset_obs_sheets()
indicator_code_error_cache = {}
for dataset_obs_sheet in dataset_obs_sheets: # Per year
sheet_year = re.match(self._config.get("DATASET_OBSERVATIONS", "SHEET_NAME_PATTERN"),
dataset_obs_sheet.name).group("year")
year_column = get_column_number(
self._config_get("DATASET_OBSERVATIONS", "OBSERVATION_YEAR_COLUMN", sheet_year))
iso3_column = get_column_number(
self._config_get("DATASET_OBSERVATIONS", "OBSERVATION_ISO3_COLUMN", sheet_year))
indicator_column = get_column_number(
self._config_get("DATASET_OBSERVATIONS", "OBSERVATION_INDICATOR_COLUMN", sheet_year))
observation_name_row = self._config_getint("DATASET_OBSERVATIONS", "OBSERVATION_NAME_ROW", sheet_year)
observation_start_row = self._config_getint("DATASET_OBSERVATIONS", "OBSERVATION_START_ROW", sheet_year)
observation_start_column = get_column_number(
self._config_get("DATASET_OBSERVATIONS", "OBSERVATION_START_COLUMN", sheet_year))
for column_number in range(observation_start_column, dataset_obs_sheet.ncols): # Per dataset indicator
dataset_indicator_code = dataset_obs_sheet.cell(observation_name_row, column_number).value
try:
dataset_indicator = self._indicator_repo.find_indicator_by_code(dataset_indicator_code)
except IndicatorRepositoryError:
if dataset_indicator_code not in indicator_code_error_cache:
self._log.warn(
"No indicator with code %s found while parsing %s[%s] (additional errors regarding this indicator will be omitted)" % (
dataset_indicator_code, dataset_obs_sheet.name, colname(column_number)))
indicator_code_error_cache[dataset_indicator_code] = True
continue
for row_number in range(observation_start_row, dataset_obs_sheet.nrows): # Per country and variable
year = int(dataset_obs_sheet.cell(row_number, year_column).value)
iso3 = dataset_obs_sheet.cell(row_number, iso3_column).value
try:
indicator_code = dataset_obs_sheet.cell(row_number, indicator_column).value
indicator = self._indicator_repo.find_indicator_by_code(indicator_code)
area = self._area_repo.find_by_iso3(iso3)
value_retrieved = dataset_obs_sheet.cell(row_number, column_number).value
value = na_to_none(value_retrieved)
excel_dataset_observation = ExcelObservation(iso3=iso3, indicator_code=indicator_code,
value=value,
year=year,
dataset_indicator_code=dataset_indicator_code)
self._excel_dataset_observations.append(
(excel_dataset_observation, area, indicator, dataset_indicator))
except IndicatorRepositoryError:
if indicator_code not in indicator_code_error_cache:
self._log.warn(
"No indicator with code %s found while parsing %s[%s] (additional errors regarding this indicator will be omitted)" % (
indicator_code, dataset_obs_sheet.name, cellname(indicator_column, row_number)))
indicator_code_error_cache[indicator_code] = True
except AreaRepositoryError:
self._log.error("No area found with code %s while parsing %s" % (
iso3, dataset_obs_sheet.name))
def read_cell_data_by_name(self,SheetName,cell_name):
# Uses the cell name to return the data from that cell.
sheet = self.rb.sheet_by_name(SheetName)
for row_index in range(sheet.nrows):
for col_index in range(sheet.ncols):
cell = cellname(row_index,col_index)
if cell_name ==cell:
cellValue = sheet.cell(row_index,col_index).value
return cellValue
def fwkts(sheet_name):
fw = 0
sheet = open(sheet_name)
for row_index in range(1,sheet.nrows):
for col_index in range(0,sheet.ncols):
if cellname(row_index,col_index)[0] == 'E':
curr_wickets = sheet.cell(row_index,col_index).value
if curr_wickets >= 5:
fw+=1
print("Total 5 Wkt Hauls : ",fw)
def readcelltodict(key,sheet,zone=None):
allCellsinOneSheet={}
if zone:
rowstart=zone['startRow']
rowend=zone['endRow']+1
colstart=zone['startColumn']
colend=zone['endColumn']+1
for row in range(rowstart,rowend):
for col in range(colstart,colend):
try:
cellvalue=sheet.cell(row,col).value
allCellsinOneSheet[xlrd.cellname(row,col)]=cellvalue
except IndexError:
allCellsinOneSheet[xlrd.cellname(row,col)]=''
else:
for row in range(sheet.nrows):
for col in range(sheet.ncols):
allCellsinOneSheet[xlrd.cellname(row,col)]=sheet.cell(row,col).value
return allCellsinOneSheet
def toss_stats(sheet_name):
sheet = open(sheet_name)
team_toss_stats = {}
for row_index in range(1,sheet.nrows):
for col_index in range(0,sheet.ncols):
if cellname(row_index,col_index)[0] == 'B':
curr_team = sheet.cell(row_index,col_index).value
opp_team = sheet.cell(row_index,col_index+1).value
toss_winner = sheet.cell(row_index,col_index+2).value
winner_decision = sheet.cell(row_index,col_index+3).value
match_winner = sheet.cell(row_index,col_index+12).value
if curr_team not in team_toss_stats:
team_toss_stats[curr_team] = [0,0,0,0,0,0]
if opp_team not in team_toss_stats:
team_toss_stats[opp_team] = [0,0,0,0,0,0]
team_toss_stats[curr_team][0]+=1
team_toss_stats[opp_team][0]+=1
if toss_winner == curr_team:
team_toss_stats[curr_team][1]+=1
if winner_decision == "bat":
team_toss_stats[curr_team][2]+=1
if match_winner == curr_team:
team_toss_stats[curr_team][4]+=1
if winner_decision == "field":
team_toss_stats[curr_team][3]+=1
if match_winner == curr_team:
team_toss_stats[curr_team][5]+=1
if toss_winner == opp_team:
team_toss_stats[opp_team][1]+=1
if winner_decision == "bat":
team_toss_stats[opp_team][2]+=1
if match_winner == opp_team:
team_toss_stats[opp_team][4]+=1
if winner_decision == "feild":
team_toss_stats[opp_team][3]+=1
if match_winner == opp_team:
team_toss_stats[opp_team][5]+=1
#Create new sheet
workbook,toss_stats_sheet = create_new_sheet("toss_stats.xls")
#Initialize rows,columns
row_count = 0
toss_stats_sheet.write(0,0,"Team Name")
toss_stats_sheet.write(0,1,"Total No of Matches")
toss_stats_sheet.write(0,2,"Toss Wins")
toss_stats_sheet.write(0,3,"Toss wins bat")
toss_stats_sheet.write(0,4,"Toss wins bowl")
toss_stats_sheet.write(0,5,"Toss wins bat win")
toss_stats_sheet.write(0,6,"Toss wins bowl win")
row_count+=1
for x in team_toss_stats:
toss_stats_sheet.write(row_count,0,x)
for y in range(0,6):
toss_stats_sheet.write(row_count,y+1,team_toss_stats[x][y])
row_count+=1
workbook.close()
def show_name_object(book, nobj, show_contents=0, f=sys.stdout):
print(
"\nName: %s, scope: %s (%s)" %
(REPR(nobj.name), REPR(nobj.scope), scope_as_string(book, nobj.scope)),
file=f)
res = nobj.result
print("Formula eval result: %s" % REPR(res), file=f)
if res is None:
return
# result should be an instance of the Operand class
kind = res.kind
value = res.value
if kind >= 0:
# A scalar, or unknown ... you've seen all there is to see.
pass
elif kind == xlrd.oREL:
# A list of Ref3D objects representing *relative* ranges
for i in range(len(value)):
ref3d = value[i]
print("Range %d: %s ==> %s" % (i, REPR(
ref3d.coords), REPR(xlrd.rangename3drel(book, ref3d))),
file=f)
elif kind == xlrd.oREF:
# A list of Ref3D objects
for i in range(len(value)):
ref3d = value[i]
print("Range %d: %s ==> %s" %
(i, REPR(ref3d.coords), REPR(xlrd.rangename3d(book, ref3d))),
file=f)
if not show_contents:
continue
datemode = book.date_mode
for shx in range(ref3d.shtxlo, ref3d.shtxhi):
sh = book.sheet_by_index(shx)
print(" Sheet #%d (%s)" % (shx, sh.name), file=f)
rowlim = min(ref3d.rowxhi, sh.nrows)
collim = min(ref3d.colxhi, sh.ncols)
for rowx in range(ref3d.rowxlo, rowlim):
for colx in range(ref3d.colxlo, collim):
cty = sh.cell_type(rowx, colx)
if cty == xlrd.XL_CELL_EMPTY and show_contents == 1:
continue
cval = sh.cell_value(rowx, colx)
sval = showable_cell_value(cty, cval, datemode)
print(" (%3d,%3d) %-5s: %s" %
(rowx, colx, xlrd.cellname(rowx,
colx), REPR(sval)),
file=f)
def get_row_values(self, sheetname, row, includeEmptyCells=True):
'''获取某行的数据'''
my_sheet_index = self.sheetNames.index(sheetname)
sheet = self.wb.sheet_by_index(my_sheet_index)
data = {}
for col_index in range(sheet.ncols):
cell = cellname(int(row), col_index)
value = sheet.cell(int(row), col_index).value
data[cell] = value
if includeEmptyCells is True:
sortedData = natsort.natsorted(data.items(), key=itemgetter(0))
return sortedData
else:
data = dict([(k, v) for (k, v) in data.items() if v])
OrderedData = natsort.natsorted(data.items(), key=itemgetter(0))
return OrderedData
def largest_margin(sheet_name):
matchid = 0
max_margin = -1
sheet = open(sheet_name)
for row_index in range(1, sheet.nrows):
for col_index in range(0, sheet.ncols):
if cellname(row_index, col_index)[0] == 'H':
curr_matchid = sheet.cell(row_index, col_index - 7).value
score1 = sheet.cell(row_index, col_index).value
score2 = sheet.cell(row_index, col_index + 3).value
result = sheet.cell(row_index, col_index + 6).value
if result != "No Result":
if max_margin < abs(score1 - score2):
max_margin = abs(score1 - score2)
matchid = curr_matchid
print(matchid)
def worldcup_stadium_stats():
stadiums = {}
for row_index in range(2,sheet.nrows):
for col_index in range(sheet.ncols):
if cellname(row_index,col_index)[0] == 'P':
curr_stadium = sheet.cell(row_index,col_index).value
curr_country = sheet.cell(row_index,col_index+1).value
if curr_country != "":
if curr_stadium not in stadiums:
stadiums[curr_stadium]=[0,0,0,0,0,0]
stadiums[curr_stadium][0]+=1
curr_runs_1 = sheet.cell(row_index,col_index-8).value
curr_runs_2 = sheet.cell(row_index,col_index-5).value
batting_first = sheet.cell(row_index,col_index-10).value
winner = sheet.cell(row_index,col_index-2).value
if winner == "Tie" or winner == "No Result":
stadiums[curr_stadium][3]+=1
elif winner == batting_first:
stadiums[curr_stadium][1]+=1
else:
stadiums[curr_stadium][2]+=1
stadiums[curr_stadium][4]+=curr_runs_1
stadiums[curr_stadium][5]+=curr_runs_2
stadiums[curr_stadium].append(curr_country)
for x in stadiums:
stadiums[x][4] = int(stadiums[x][4]/stadiums[x][0])
stadiums[x][5] = int(stadiums[x][5]/stadiums[x][0])
#Create new sheet
workbook,worldcup_stadium_stats_sheet = create_new_sheet("worldcup_stadium_stats.xls")
#Initialize rows,columns
row_count = 0
worldcup_stadium_stats_sheet.write(0,0,"Stadium Name")
worldcup_stadium_stats_sheet.write(0,1,"Number of Matches")
worldcup_stadium_stats_sheet.write(0,2,"Batting First Wins")
worldcup_stadium_stats_sheet.write(0,3,"Batting Second Wins")
worldcup_stadium_stats_sheet.write(0,4,"No Winners")
worldcup_stadium_stats_sheet.write(0,5,"AVG SCR 1")
worldcup_stadium_stats_sheet.write(0,6,"AVG SCR 2")
worldcup_stadium_stats_sheet.write(0,7,"Country")
row_count+=1
for x in stadiums:
worldcup_stadium_stats_sheet.write(row_count,0,x)
for y in range(0,7):
worldcup_stadium_stats_sheet.write(row_count,y+1,stadiums[x][y])
row_count+=1
workbook.close()
def dump_sheet(sheet, sheet_index, max_rows):
dump_record(
"s", {
"index": sheet_index,
"name": sheet.name,
"rows": sheet.nrows,
"columns": sheet.ncols,
"visibility": sheet.visibility
})
for rowx in range(max_rows or sheet.nrows):
for colx in range(sheet.ncols):
cell = sheet.cell(rowx, colx)
dump_record("c", [
rowx, colx,
cellname(rowx, colx),
parse_cell_value(sheet, cell)
])
def total_c_hc(sheet_name):
sheet = open(sheet_name)
total_c = 0
total_hc = 0
total_balls = 0
for row_index in range(1, sheet.nrows):
for col_index in range(0, sheet.ncols):
if cellname(row_index, col_index)[0] == 'C':
curr_score = sheet.cell(row_index, col_index).value
curr_balls = sheet.cell(row_index, col_index + 1).value
if curr_score in range(50, 100):
total_hc += 1
if curr_score in range(100, 220):
total_c += 1
total_balls += curr_balls
print("Total balls : ", total_balls)
print("Total Centuries : ", total_c)
print("Total Half Centuries : ", total_hc)
def total_boundaries(sheet_name):
total_fours = 0
total_sixes = 0
total_dots = 0
sheet = open(sheet_name)
for row_index in range(1, sheet.nrows):
for col_index in range(0, sheet.ncols):
if cellname(row_index, col_index)[0] == 'S':
curr_runs = sheet.cell(row_index, col_index).value
if curr_runs == 6:
total_sixes += 1
if curr_runs == 4:
total_fours += 1
if curr_runs == 0:
total_dots += 1
print("Total 6's : ", total_sixes)
print("Total 4's : ", total_fours)
print("Total dots : ", total_dots)
def _chart2excel(writer, sheet, charts):
import xlrd
from openpyxl.chart import ScatterChart, Series
sn = writer.book.sheetnames
named_ranges = {
'%s!%s' % (sn[d.localSheetId], d.name): d.value
for d in writer.book.defined_names.definedName
}
m, h, w = 3, 7.94, 13.55
for i, (k, v) in enumerate(sorted(charts.items())):
chart = ScatterChart()
chart.height = h
chart.width = w
_map = {
('title', 'name'): ('title', ),
('y_axis', 'name'): ('y_axis', 'title'),
('x_axis', 'name'): ('x_axis', 'title'),
}
_filter = {
('legend', 'position'): lambda x: x[0],
}
it = {
s: _filter[s](o) if s in _filter else o
for s, o in sh.stack_nested_keys(v['set'])
}
for s, o in sh.map_dict(_map, it).items():
c = chart
for j in s[:-1]:
c = getattr(c, j)
setattr(c, s[-1], o)
for s in v['series']:
xvalues = named_ranges[_data_ref(s['x'])]
values = named_ranges[_data_ref(s['y'])]
series = Series(values, xvalues, title=s['label'])
chart.series.append(series)
n = int(i / m)
j = i - n * m
sheet.add_chart(chart, xlrd.cellname(15 * j, 8 * n))
def parse(infile,outfile):
"""
Converts an Excel file into text
Returns a formatted text file for comparison using git diff.
"""
book = xl.open_workbook(infile)
num_sheets = book.nsheets
print book.sheet_names()
# print "File last edited by " + book.user_name + "\n"
outfile.write("File last edited by " + book.user_name + "\n")
def get_cells(sheet, rowx, colx):
try:
value = unicode(sheet.cell_value(rowx, colx))
except:
value = ''
if (rowx,colx) in sheet.cell_note_map.keys():
value += ' <<' + unicodedata.normalize('NFKD', unicode(sheet.cell_note_map[rowx,colx].text)).encode('ascii', 'ignore') + '>>'
return value
# loop over worksheets
for index in range(0,num_sheets):
# find non empty cells
sheet = book.sheet_by_index(index)
outfile.write("=================================\n")
sheetname = unicodedata.normalize('NFKD', unicode(sheet.name)).encode('ascii', 'ignore')
outfile.write("Sheet: " + sheetname + "[ " + str(sheet.nrows) + " , " + str(sheet.ncols) + " ]\n")
outfile.write("=================================\n")
for row in range(0,sheet.nrows):
for col in range(0,sheet.ncols):
content = get_cells(sheet, row, col)
if content <> "":
output = ' ' + xl.cellname(row,col) + ':\n '
output += unicodedata.normalize('NFKD', unicode(content)).encode('ascii', 'ignore')
output += '\n'
outfile.write(output)
print "\n"
def total_runs_wkts_ties(sheet_name):
sheet = open(sheet_name)
total_runs = 0
total_wkts = 0
total_ties = 0
for row_index in range(1, sheet.nrows):
for col_index in range(0, sheet.ncols):
if cellname(row_index, col_index)[0] == 'H':
scr1 = sheet.cell(row_index, col_index).value
w1 = sheet.cell(row_index, col_index + 1).value
scr2 = sheet.cell(row_index, col_index + 3).value
w2 = sheet.cell(row_index, col_index + 4).value
result = sheet.cell(row_index, col_index + 6).value
if result == "Tie":
total_ties += 1
total_runs += (scr1 + scr2)
total_wkts += (w1 + w2)
print("Total Runs : ", total_runs)
print("Total Wkts : ", total_wkts)
print("Total Ties : ", total_ties)
def _get_cell(sheet, row_index, col_index, ctype, nullable=False):
value = sheet.cell_value(row_index, col_index)
cellname = xlrd.cellname(row_index, col_index)
current_ctype = sheet.cell_type(row_index, col_index)
if ctype == current_ctype == xlrd.XL_CELL_TEXT:
value = value.strip() or None
if not nullable and not value:
raise UserError(_("Null value in cell '%s'") % cellname)
elif ctype == current_ctype == xlrd.XL_CELL_NUMBER:
pass
else:
if current_ctype in MAP_TYPES:
current_ctype = MAP_TYPES[current_ctype]
if ctype in MAP_TYPES:
ctype = MAP_TYPES[ctype]
raise UserError(
_("Wrong format in cell '%s'. Expected '%s', found '%s'") %
(cellname, ctype, current_ctype))
return value
def calc_runs_per_match():
runs_per_match = {}
for row_index in range(2,sheet.nrows):
for col_index in range(sheet.ncols):
if cellname(row_index,col_index)[0] == 'X':
curr_match_id = sheet.cell(row_index,col_index-21).value
curr_runs = sheet.cell(row_index,col_index).value
is_wicket = sheet.cell(row_index,col_index+1).value
curr_inn = sheet.cell(row_index,col_index-20).value
is_wide = sheet.cell(row_index,col_index-3).value
is_nb = sheet.cell(row_index,col_index-4).value
if curr_match_id in runs_per_match:
if curr_inn == 1:
runs_per_match[curr_match_id][0]+=curr_runs
if not (is_wide or is_nb):
runs_per_match[curr_match_id][2]+=1
else:
runs_per_match[curr_match_id][3]+=curr_runs
if not (is_wide or is_nb):
runs_per_match[curr_match_id][5]+=1
if is_wicket != "":
if curr_inn == 1:
runs_per_match[curr_match_id][1]+=1
else:
runs_per_match[curr_match_id][4]+=1
else:
runs_per_match[curr_match_id]=[]
runs_per_match[curr_match_id].append(curr_runs)
if is_wicket != "":
runs_per_match[curr_match_id].append(1)
else:
runs_per_match[curr_match_id].append(0)
if not (is_wide or is_nb):
runs_per_match[curr_match_id].append(1)
else:
runs_per_match[curr_match_id].append(0)
runs_per_match[curr_match_id].append(0)
runs_per_match[curr_match_id].append(0)
runs_per_match[curr_match_id].append(0)
return runs_per_match
def do_sheets(fn):
obcine_lookup = get_obcine_lookup()
book = open_workbook(fn)
for n in xrange(book.nsheets - 1):
sheet = book.sheet_by_index(n)
print 'SHEET NAME', sheet.name
print 'SHEET ROWS', sheet.nrows
print 'SHEET COLS', sheet.ncols
obcine_col = get_obcine_column(sheet)
print 'COLUMN', obcine_col
legenda_row = get_legenda_row(sheet, data_start=obcine_col[1])
print 'LEGENDA', legenda_row
obcine = get_obcine(sheet, obcine_col[0], obcine_col[1], obcine_col[2])
postavke = get_postavke(sheet, legenda_row[0], legenda_row[1], legenda_row[2])
for x in xrange(legenda_row[1], legenda_row[2] + 1):
for y in xrange(obcine_col[1], obcine_col[2] + 1):
fn1 = os.path.basename(fn)
m = re.match('^(\w\w)-(\d{4})-', fn1)
if not m:
raise ValueError(fn1)
klasifikacija, leto = m.groups()
d = collections.OrderedDict()
d['postavka'] = postavke[x].strip()
d['obcina'] = obcine_lookup[obcine[y].strip()]
d['vrednost'] = sheet.cell(y, x).value
d['sheet'] = sheet.name
d['cell'] = cellname(y, x)
d['x'] = x
d['y'] = y
d['fn'] = fn1
d['klasifikacija'] = klasifikacija
d['leto'] = leto
yield d
def read_cell_data_by_name(self, sheetname, cell_name):
"""
Uses the cell name to return the data from that cell.
Arguments:
| Sheet Name (string) | The selected sheet that the cell value will be returned from. |
| Cell Name (string) | The selected cell name that the value will be returned from. |
Example:
| *Keywords* | *Parameters* |
| Open Excel | C:\\Python27\\ExcelRobotTest\\ExcelRobotTest.xls | |
| Get Cell Data | TestSheet1 | A2 |
"""
my_sheet_index = self.sheetNames.index(sheetname)
sheet = self.wb.sheet_by_index(my_sheet_index)
for row_index in range(sheet.nrows):
for col_index in range(sheet.ncols):
cell = cellname(row_index, col_index)
if cell_name == cell:
cellValue = sheet.cell(row_index, col_index).value
return cellValue
def extreme_totals(sheet_name):
sheet = open(sheet_name)
max_matchid = 0
min_matchid = 0
max_total = -1
min_total = 500
sheet = open(sheet_name)
for row_index in range(1, sheet.nrows):
for col_index in range(0, sheet.ncols):
if cellname(row_index, col_index)[0] == 'H':
curr_matchid = sheet.cell(row_index, col_index - 7).value
score1 = sheet.cell(row_index, col_index).value
score2 = sheet.cell(row_index, col_index + 3).value
result = sheet.cell(row_index, col_index + 6).value
if result != "No Result":
if max_total < max(score2, score1):
max_total = max(score2, score1)
max_matchid = curr_matchid
if min_total > min(score2, score1):
min_total = min(score2, score1)
min_matchid = curr_matchid
print(max_matchid, min_matchid)
def bowler_grd_calculation(yr):
sheet = open("bowler_grey_relation_coefficients"+str(yr)+".xls")
w_wptm = 30
w_avg = 15
w_sr = 35
w_eco = 10
w_ftw = 10
grd_bowler = {}
for row_index in range(1,sheet.nrows):
for col_index in range(sheet.ncols):
if cellname(row_index,col_index)[0] == 'A':
curr_bowler = sheet.cell(row_index,col_index).value
curr_wptm = float(sheet.cell(row_index,col_index+1).value)
curr_avg = float(sheet.cell(row_index,col_index+2).value)
curr_sr = float(sheet.cell(row_index,col_index+3).value)
curr_eco = float(sheet.cell(row_index,col_index+4).value)
curr_ftw = float(sheet.cell(row_index,col_index+5).value)
grd_bowler[curr_bowler] = (w_wptm * curr_wptm +
w_avg * curr_avg +
w_sr * curr_sr +
w_eco * curr_eco +
w_ftw * curr_ftw
)
#Create new sheet
workbook,grey_relation_grades_sheet = create_new_sheet(
"bowler_grey_relation_grades"+str(yr)+".xls"
)
#Initialize rows,columns
row_count = 0
grey_relation_grades_sheet.write(0,0,"Player Name")
grey_relation_grades_sheet.write(0,1,"Grade")
row_count+=1
for x in grd_bowler:
grey_relation_grades_sheet.write(row_count,0,x)
grey_relation_grades_sheet.write(row_count,1,grd_bowler[x])
row_count+=1
workbook.close()
def openFile(self):
self.entCellStart.delete(0, END)
self.entCellFinish.delete(0, END)
book = open_workbook(filename=self.path, encoding_override='cp1251')
self.cmbSheet['values'] = [(sheetID + 1)
for sheetID in range(book.nsheets)]
sheets = [
book.sheet_by_index(sheetID) for sheetID in range(book.nsheets)
]
self.xlsData = []
for sheetID, sheet in enumerate(sheets):
self.xlsData.append([])
for colID in range(sheet.nrows):
self.xlsData[sheetID].append(sheet.row_values(colID))
if self.cmbSheet['values']:
self.cmbSheet.set(self.cmbSheet['values'][0])
if sheets[0].nrows > 0 and sheets[0].ncols > 0:
self.entCellStart.insert(0, 'A1')
self.entCellFinish.insert(
0, cellname(sheets[0].nrows - 1, sheets[0].ncols - 1))
book, sheets = None, None
collect()
self.selectData()
def _get_corner(df, startcol=0, startrow=0, index=False, header=True, **kw):
import json
import xlrd
ref = {}
if header:
i = _index_levels(df.columns)
ref['header'] = list(range(i))
startrow += i
import pandas as pd
if index and isinstance(df.columns, pd.MultiIndex):
ref['skiprows'] = [i + 1]
startrow += 1
if index:
i = _index_levels(df.index)
ref['index_col'] = list(range(i))
startcol += i
landing = xlrd.cellname(startrow, startcol)
ref = json.dumps(ref, sort_keys=True)
ref = '{}(L):..(DR):LURD:["df", {}]'.format(landing, ref)
return startrow, startcol, ref
def batsmen_grd_calculation(yr):
sheet = open("grey_relation_coefficients"+str(yr)+".xls")
w_rpm = 35
w_avg = 30
w_sr = 10
w_hc = 10
w_c = 15
grd_batsmen = {}
for row_index in range(1,sheet.nrows):
for col_index in range(sheet.ncols):
if cellname(row_index,col_index)[0] == 'A':
curr_batsmen = sheet.cell(row_index,col_index).value
curr_rpm = float(sheet.cell(row_index,col_index+1).value)
curr_avg = float(sheet.cell(row_index,col_index+2).value)
curr_sr = float(sheet.cell(row_index,col_index+3).value)
curr_hc = float(sheet.cell(row_index,col_index+4).value)
curr_c = float(sheet.cell(row_index,col_index+5).value)
grd_batsmen[curr_batsmen] = (w_rpm * curr_rpm +
w_avg * curr_avg +
w_sr * curr_sr +
w_hc * curr_hc +
w_c * curr_c)
#Create new sheet
workbook,grey_relation_grades_sheet = create_new_sheet(
"grey_relation_grades"+str(yr)+".xls"
)
#Initialize rows,columns
row_count = 0
grey_relation_grades_sheet.write(0,0,"Player Name")
grey_relation_grades_sheet.write(0,1,"Grade")
row_count+=1
for x in grd_batsmen:
grey_relation_grades_sheet.write(row_count,0,x)
grey_relation_grades_sheet.write(row_count,1,grd_batsmen[x])
row_count+=1
workbook.close()
def mom_count(sheet_name):
sheet = open(sheet_name)
player_mom_count = {}
for row_index in range(1, sheet.nrows):
for col_index in range(0, sheet.ncols):
if cellname(row_index, col_index)[0] == 'O':
curr_player = sheet.cell(row_index, col_index).value
if curr_player not in player_mom_count and curr_player != "":
player_mom_count[curr_player] = 0
if curr_player != "":
player_mom_count[curr_player] += 1
#Create new sheet
workbook, mom_count_sheet = create_new_sheet("mom_count.xls")
#Initialize rows,columns
row_count = 0
mom_count_sheet.write(0, 0, "Player Name")
mom_count_sheet.write(0, 1, "MOM Count")
row_count += 1
for x in player_mom_count:
mom_count_sheet.write(row_count, 0, x)
mom_count_sheet.write(row_count, 1, player_mom_count[x])
row_count += 1
workbook.close()
print(table1.col_values(2)) # 返回包含第3列值的列表
'''五、特定单元格读取'''
print(table1.cell(1, 2).value) # 获取第2行，第3列单元格的值
print(table1.cell_value(1, 2))
print(table1.row(1)[2])
print(table1.row(1)[2].value)
print(table1.row(1)[2].ctype)
'''六、常用技巧：(0,0)转换成A1'''
print(xlrd.cellname(0, 0)) # A1 cellname方法把一对行和列索引转换为一个对应的Excel单元格引用
print(xlrd.cellnameabs(
0, 0)) # $A$1 cellnameabs方法把一对行和列索引转换为一个绝对的Excel单元格引用（如：$A$1）
print(xlrd.colname(0)) # A colname方法把一个列索引转换为Excel列名
'''七、获取表格内不同类型的name'''
def read_excel(table, row, col):
name = table.cell_value(row, col)
ctype = table.cell_type(row, col)
if ctype == 0:
name = "''"
elif ctype == 1:
name = name
values.append(s.cell(row, col).value)
print ','.join(values)
print
# example 2
from xlrd import cellname
book = open_workbook('test.xls')
sheet = book.sheet_by_index(0)
print sheet.name
print sheet.nrows
print sheet.ncols
for row_index in range(sheet.nrows):
for col_index in range(sheet.ncols):
print cellname(row_index, col_index), '-',
print sheet.cell(row_index, col_index).value
#example 3
from xlrd import XL_CELL_TEXT
book = open_workbook('test.xls')
sheet = book.sheet_by_index(0)
cell = sheet.cell(0, 0)
print cell
print cell.value
print cell.ctype == XL_CELL_TEXT
for i in range(sheet.ncols):
print sheet.cell_type(0, i), sheet.cell_value(0, i)
def GenerateAverageSheet(wb, sumsht, dnScalAlgos, upScalAlgos, ratio):
logger.info("start generate average sheet for ratio %2.2f" % ratio)
rdsht = sumsht
rdshtname = rdsht.get_name()
shtname = "AverageForRatio=%1.2f" % ratio
sht = wb.add_worksheet(shtname)
sht.write(1, 0, 'QualityMetrics')
sht.write(1, 1, 'Content Class')
sht.write(1, 2, 'Content Number')
interval = 1
step = len(StatsMetrics) + interval
startcol = 3
cols_avg = [startcol + step * i for i in range(len(dnScalAlgos))]
for col, dn_algo, up_algo in zip(cols_avg, dnScalAlgos, upScalAlgos):
algos = dn_algo + '--' + up_algo
sht.write(0, col, algos)
sht.write_row(1, col, StatsMetrics)
step = len(ContentsDict) + 1 # 1 extra row for total of each class
startrow = 2
rows_qtymtr = [startrow + step * i for i in range(len(QualityList))]
for qty, row_qm, y in zip(QualityList, rows_qtymtr, range(len(QualityList))):
sht.write(row_qm, 0, qty)
#charts = []
#titlename = 'Quality Statistics %s' % qty
#chart = CreateChart_Line(wb, titlename, qty)
#charts.append(chart)
totalnum_contents = 0
for (cls, contents), idx, rdrow_cls in zip(ContentsDict.items(),
range(len(ContentsDict)),
Rows_Class):
sht.write(row_qm + idx, 1, cls)
num_content = len(contents)
totalnum_contents = totalnum_contents + num_content
sht.write(row_qm + idx, 2, num_content)
for rdcol, wtcol in zip(ScalSumQty_WtCols, cols_avg):
startcell = xlrd.cellname(rdrow_cls, rdcol + y)
endcell = xlrd.cellname(rdrow_cls + num_content - 1, rdcol + y)
formula = '=MAX(\'%s\'!%s:%s)' % (rdshtname, startcell, endcell)
sht.write(row_qm + idx, wtcol, formula)
formula = '=SUM(\'%s\'!%s:%s)/%d' % (rdshtname, startcell,
endcell, num_content)
sht.write(row_qm + idx, wtcol + 2, formula)
formula = '=MIN(\'%s\'!%s:%s)' % (rdshtname, startcell, endcell)
sht.write(row_qm + idx, wtcol + 4, formula)
avgcell = xlrd.cellname(row_qm + idx, wtcol + 2)
formula = '= %s + _xlfn.STDEV.P(\'%s\'!%s:%s)'\
% (avgcell, rdshtname, startcell, endcell)
sht.write(row_qm + idx, wtcol + 1, formula)
formula = '= %s - _xlfn.STDEV.P(\'%s\'!%s:%s)'\
% (avgcell, rdshtname, startcell, endcell)
sht.write(row_qm + idx, wtcol + 3, formula)
#write total contents statistics
wtrow = row_qm + len(ContentsDict)
sht.write(wtrow, 1, 'Total')
sht.write(wtrow, 2, totalnum_contents)
for rdcol, wtcol in zip(ScalSumQty_WtCols, cols_avg):
startcell = xlrd.cellname(ScalQty_startRow, rdcol + y)
endcell = xlrd.cellname(ScalQty_startRow + totalnum_contents - 1,
rdcol + y)
formula = '=MAX(\'%s\'!%s:%s)' % (rdshtname, startcell, endcell)
sht.write(wtrow, wtcol, formula)
formula = '=SUM(\'%s\'!%s:%s)/%d'\
% (rdshtname, startcell, endcell, totalnum_contents)
sht.write(wtrow, wtcol + 2, formula)
formula = '=MIN(\'%s\'!%s:%s)' % (rdshtname, startcell, endcell)
sht.write(wtrow, wtcol + 4, formula)
avgcell = xlrd.cellname(wtrow, wtcol + 2)
formula = '= %s + _xlfn.STDEV.P(\'%s\'!%s:%s)'\
% (avgcell, rdshtname, startcell, endcell)
sht.write(wtrow, wtcol + 1, formula)
formula = '= %s - _xlfn.STDEV.P(\'%s\'!%s:%s)'\
% (avgcell, rdshtname, startcell, endcell)
sht.write(wtrow, wtcol + 3, formula)
logger.info("finish average sheet for ratio:%2.2f." % ratio)
def extract_all(self):
"method to extract the information from the cell"
# initialize the state
cur_row = 0
# create a single block for the entire sheet (this is a trailing
# part from another python script that had several blocks)
self.cur_block = xls_block(self.sheetname, "+0000.0000'H",
self.verbose)
# save the block in the list of blocks (trailing from old script)
self.blocks.append(self.cur_block)
# loop on all the rows of the sheet
while cur_row < self.wsheet.nrows:
# if the current row column 0 is not empty
if self.wsheet.cell_type(cur_row, 0) != xlrd.XL_CELL_EMPTY:
# retrieve the column 0 value
cur_value = self.wsheet.cell_value(cur_row, 0)
if cur_value == "Address":
# create a new register with name and address
self.cur_block.start_register(
self.wsheet.cell_value(cur_row, 3),
self.wsheet.cell_value(cur_row + 2, 0),
self.wsheet.cell_value(cur_row + 2, 1),
self.wsheet.cell_value(cur_row + 2, 2),
self.wsheet.cell_value(cur_row + 1, 3),
self.wsheet.cell_value(cur_row - 1, 1))
# pass the register line
cur_row += 1
# parse the bits of the register
if self.wsheet.cell_value(cur_row, 3) == 31:
width = 32
elif self.wsheet.cell_value(cur_row, 3) == 15:
width = 16
else:
width = 8
for cur_col in range(3, width + 3):
# check the bit number
try:
bitpos = int(
self.wsheet.cell_value(cur_row, cur_col))
except:
print(
"""ERROR: cell "%s", unexpected bit value""" %
(xlrd.cellname(cur_row, cur_col), ))
bitpos = 0
# check if there is a correct bit number at this column
if bitpos == (width + 2 - cur_col):
#search for number of reset values
reset_row = 1
while (1):
# retrieve the column 0 value
cur_value = self.wsheet.cell_value(
cur_row + 2 + reset_row, 0)
if (cur_value[0:5] != "Reset"):
break
reset_row += 1
# add the bit with the name, the bit position and reset value
self.cur_block.add_bit(
self.wsheet.cell_value(cur_row + 1, cur_col),
bitpos,
self.wsheet.cell_value(cur_row + 2, cur_col),
self.wsheet.cell_value(cur_row + reset_row + 3,
cur_col),
self.wsheet.cell_value(cur_row + reset_row + 4,
cur_col))
else:
print(
"""ERROR: cell "%s", unexpected bit value "%s" """
% (xlrd.cellname(cur_row, cur_col), bitpos))
# pass six rows
cur_row += 5 + reset_row
# finish the register
self.cur_block.end_register()
else:
print("""ERROR: cell "%s", unexpected value "%s" """ %
(xlrd.cellname(cur_row, 0), cur_value))
cur_row += 1
self.cur_block.end()
print(table1.row_slice(0, 0, 2))
print(table1.row_types(0, 0, 2))
#5.特定单元格读取
#取值
print()
print(table1.cell(1, 2).value)
print(table1.cell_value(1, 2))
print(table1.row(1)[2].value)
#取类型
print()
print(table1.cell(1, 2).ctype)
print(table1.cell_type(1, 2))
print(table1.row(1)[2].ctype)
#6.常用技巧：（0,0）转换成A1
print()
print(xlrd.cellname(0, 0))
print(xlrd.cellnameabs(0, 2))
print(xlrd.colname(30))
def read_excel(table, row, col):
name = table.cell_value(row, col)
type = table.cell_type(row, col)
if type == 0:
name = "'"
elif type == 1:
name = name
elif type == 2 and name % 1 == 0:
name = int(name)
elif type == 3:
#方法1 转换为日期时间
def extract_all(self):
"method to extract the information from the xls sheet"
# initialize the state
cur_row = 0
# create a single block for the entire sheet (this is a trailing
# part from another python script that had several blocks)
self.cur_block = xls_block(self.sheetname, "+0000.0000'H",
self.verbose)
# save the block in the list of blocks (trailing from old script)
self.blocks.append(self.cur_block)
# loop on all the rows of the sheet
while cur_row < self.wsheet.nrows:
# if the current row column 0 is not empty
if self.wsheet.cell_type(cur_row, 0) != xlrd.XL_CELL_EMPTY:
# retrieve the column 0 value
cur_value = self.wsheet.cell_value(cur_row, 0)
if cur_value == "Address":
# create a new register with name and address
self.cur_block.start_register(
self.wsheet.cell_value(cur_row, 3),
self.wsheet.cell_value(cur_row + 2, 0),
self.wsheet.cell_value(cur_row + 1, 3),
self.wsheet.cell_value(cur_row + 2, 1),
self.wsheet.cell_value(cur_row + 2, 2))
# pass the register line
cur_row += 1
# parse the bits of the register
cur_col = 3
while (self.wsheet.cell_value(cur_row, cur_col) != ""):
# check the bit number
try:
bitpos = int(
self.wsheet.cell_value(cur_row, cur_col))
except:
print(
"""ERROR: cell "%s", unparsable bit value""" %
(xlrd.cellname(cur_row, cur_col), ))
bitpos = 0
# check if there is a correct bit number at this column
if bitpos == (self.cur_block.cur_reg.msb + 3 -
cur_col):
# add the bit with the name, the bit position and reset value
self.cur_block.add_bit(
self.wsheet.cell_value(cur_row + 1, cur_col),
bitpos,
self.wsheet.cell_value(cur_row + 2, cur_col),
self.wsheet.cell_value(cur_row + 4, cur_col),
self.wsheet.cell_value(cur_row + 5, cur_col))
else:
print(
"""ERROR: cell "%s", unexpected bit value "%s" """
% (xlrd.cellname(cur_row, cur_col), bitpos))
# increment the column index
cur_col += 1
# pass six rows
cur_row += 6
# finish the register
self.cur_block.end_register()
else:
print("""ERROR: cell "%s", unexpected value "%s" """ %
(xlrd.cellname(cur_row, 0), cur_value))
cur_row += 1
self.cur_block.end()
def code_update_report(workbook_ro, workbook, gl, gl_codeids, nc_sp, pvs_sp, fm_sp, ibm):
"""This report reads from the readonly workbook in order to perform some
cell processing.
"""
# Sheet 1
sheet = workbook.get_sheet(0)
sheet_ro = workbook_ro.get_sheet(0)
# Download hyperlink:
bigfont = easyxf('font: bold 1,height 360;') # Font height is in "twips" (1/20 of a point)
url = Formula('HYPERLINK("{}")'.format(settings.IBM_CODE_UPDATER_URI))
sheet.write(1, 0, url, bigfont)
# Padded zeroes number format
pad2, pad3, pad4 = XFStyle(), XFStyle(), XFStyle()
pad2.num_format_str = '00'
pad3.num_format_str = '000'
pad4.num_format_str = '0000'
# For each of the GL code IDs, take a subset of the query
# and insert values as required.
row = 4
max_col_idx = 21 # Start at column V.
for codeID in gl_codeids:
gl_pivs = gl.filter(codeID=codeID)
g = gl_pivs[0]
# Fill the non-resource columns.
sheet.write(row, 0, g.codeID)
sheet.write(row, 1, int(g.costCentre), pad3)
sheet.write(row, 2, g.account, pad2)
sheet.write(row, 3, g.service, pad2)
sheet.write(row, 4, g.activity, pad3)
try:
sheet.write(row, 5, int(g.project), pad4)
except ValueError:
sheet.write(row, 5, g.project, pad4)
try:
sheet.write(row, 6, int(g.job), pad3)
except ValueError:
sheet.write(row, 6, g.job, pad3)
sheet.write(row, 7, g.jobName)
sheet.write(row, 8, g.activityName)
sheet.write(row, 9, g.projNameNo)
sheet.write(row, 19, g.mPRACategory)
# Write the SUM formula.
sheet.write(row, 20, Formula('ROUND(SUM(V{}:GP{}), 0)'.format(row+1, row+1)))
# First, find the maximum column index in the template headers (row 4).
blank_cell = False
while not blank_cell:
if not sheet_ro.cell_value(3, max_col_idx): # Blank cell
blank_cell = True
else:
max_col_idx += 1
# Write ytdActual values for matching resource columns.
# Find the correct cell index of a matching resource code.
# If no match found, use the '0000' column (the first).
for gl_piv in gl_pivs:
resource_idx = 21 # Column V, '0000'
match_resource_code = False
for i in range(resource_idx, max_col_idx + 1):
if sheet_ro.cell_value(3, i) and int(sheet_ro.cell_value(3, i)) == gl_piv.resource:
match_resource_code = True
break
resource_idx += 1
if not match_resource_code: # No match was found.
resource_idx = 21 # Insert the ytdActual into column V.
# Write the ytdActual to the sheet.
sheet.write(row, resource_idx, gl_piv.ytdActual)
row += 1 # Advance one row, to the next Code ID.
row += 1
# Insert the footer row formulae and '#END OF INPUT'
sheet.write(row, 0, '#END OF INPUT')
sheet.write(row, 20, Formula('ROUND(SUM(V{}:GP{}), 0)'.format(row+1, row+1)))
for i in range(21, max_col_idx):
# For cell V:<end> in the footer row, insert a SUM formula.
sheet.write(row, i, Formula('ROUND(SUM({}:{}), 0)'.format(cellname(4, i), cellname(row-1, i))))
# Sheet 2: Service priority checkboxes.
sheet = workbook.get_sheet(1)
write_service_priorities(sheet, nc_sp, pvs_sp, fm_sp)
# Sheet 3: Budget area & project sponsor lookup data.
# This is a list of unique budgetArea and projectSponsor values, written in
# as reference data for macros.
sheet = workbook.get_sheet(2)
write_budget_areas(sheet, ibm)
write_project_sponsors(sheet, ibm)
write_regional_spec_info(sheet, ibm)
# Select the first sheet.
sheet = workbook.get_sheet(0)
def normalize_complete_bowler_stats(yr):
innings = {}
wickets = {}
economy = {}
sr = {}
avg = {}
ftw = {}
wptm = {}
sheet = open_bowl("complete_bowler_stats"+str(yr)+".xls")
for row_index in range(1,sheet.nrows):
for col_index in range(sheet.ncols):
if cellname(row_index,col_index)[0] == 'A' and (sheet.cell(row_index,col_index+4).value >= 1
and sheet.cell(row_index,col_index+2).value >= 100
and sheet.cell(row_index,col_index+1).value >= 3):
curr_bowler = sheet.cell(row_index,col_index).value
innings[curr_bowler] = sheet.cell(row_index,col_index+1).value
wickets[curr_bowler] = sheet.cell(row_index,col_index+4).value
avg[curr_bowler] = 100.0/float(sheet.cell(row_index,col_index+5).value)
economy[curr_bowler] = 100.0/float(sheet.cell(row_index,col_index+6).value)
sr[curr_bowler] = 100.0/float(sheet.cell(row_index,col_index+7).value)
ftw[curr_bowler] = sheet.cell(row_index,col_index+9).value
for x in innings:
wptm[x] = (wickets[x]*10.0)/ innings[x]
ftw[x] = (ftw[x]*10.0)/innings[x]
max_wptm , min_wptm = min_max(wptm)
max_avg , min_avg = min_max(avg)
max_sr , min_sr = min_max(sr)
max_ftw , min_ftw = min_max(ftw)
max_eco , min_eco = min_max(economy)
#Create new sheet
workbook,normalized_complete_bowler_stats_sheet = create_new_sheet(
"normalized_complete_bowler_stats"+str(yr)+".xls"
)
#Initialize rows,columns
row_count = 0
normalized_complete_bowler_stats_sheet.write(0,0,"Player Name")
normalized_complete_bowler_stats_sheet.write(0,1,"Wickets per innings")
normalized_complete_bowler_stats_sheet.write(0,2,"AVG")
normalized_complete_bowler_stats_sheet.write(0,3,"SR")
normalized_complete_bowler_stats_sheet.write(0,4,"Economy")
normalized_complete_bowler_stats_sheet.write(0,5,"5W")
row_count+=1
for x in innings:
normalized_complete_bowler_stats_sheet.write(row_count,0,x)
if max_wptm == min_wptm:
normalized_complete_bowler_stats_sheet.write(row_count,1,0)
else:
normalized_complete_bowler_stats_sheet.write(row_count,1,
"%.2f"%((wptm[x] - min_wptm) / (max_wptm - min_wptm))
)
if max_avg == min_avg:
normalized_complete_bowler_stats_sheet.write(row_count,2,0)
else:
normalized_complete_bowler_stats_sheet.write(row_count,2,
"%.2f"%((float(avg[x]) - min_avg) / (max_avg - min_avg))
- )
if max_sr == min_sr:
normalized_complete_bowler_stats_sheet.write(row_count,3,0)
else:
normalized_complete_bowler_stats_sheet.write(row_count,3,
"%.2f"%((float(sr[x]) - min_sr) / (max_sr - min_sr))
)
if max_eco == min_eco:
normalized_complete_bowler_stats_sheet.write(row_count,4,0)
else:
normalized_complete_bowler_stats_sheet.write(row_count,4,
"%.2f"%((float(economy[x]) - min_eco) / (max_eco - min_eco))
)
if max_ftw == min_ftw:
normalized_complete_bowler_stats_sheet.write(row_count,5,0)
else:
normalized_complete_bowler_stats_sheet.write(row_count,5,
"%.2f"%((float(ftw[x]) - min_ftw) / (max_ftw - min_ftw))
)
row_count+=1
workbook.close()
def data_amend_report(workbook, gl, ibm, nc_sp, pvs_sp, fm_sp, ibm_filtered):
# Sheet 1
sheet = workbook.get_sheet(0)
# Download hyperlink:
bigfont = easyxf('font: bold 1,height 360;') # Font height is in "twips" (1/20 of a point)
sheet.write(1, 0, Formula('HYPERLINK("{}")'.format(settings.IBM_DATA_AMEND_URI)), bigfont)
# Padded zeroes number format
pad2, pad3, pad4 = XFStyle(), XFStyle(), XFStyle()
pad2.num_format_str = '00'
pad3.num_format_str = '000'
pad4.num_format_str = '0000'
current_row = 3
code_id = ''
for row, data in enumerate(gl, current_row):
# Only insert GLPivDownload objects with a matching IBMData object.
if ibm.filter(ibmIdentifier=data.codeID).exists():
# We have to aggregate all the GLPivotDownload objects with
# matching codeID values, and insert one row with total
# ytdActual and fyBudget values.
if code_id != data.codeID: # Reached the next codeID value.
current_row += 1 # Advance one row.
code_id = data.codeID
i = ibm.get(ibmIdentifier=data.codeID)
sheet.write(current_row, 0, data.codeID)
sheet.write(current_row, 1, int(data.costCentre), pad3)
sheet.write(current_row, 2, data.account, pad2)
sheet.write(current_row, 3, data.service, pad2)
sheet.write(current_row, 4, data.activity, pad3)
try:
sheet.write(current_row, 5, int(data.project), pad4)
except ValueError:
sheet.write(current_row, 5, data.project, pad4)
try:
sheet.write(current_row, 6, int(data.job), pad3)
except ValueError:
sheet.write(current_row, 6, data.job, pad3)
sheet.write(current_row, 7, data.jobName)
sheet.write(current_row, 8, data.activityName)
sheet.write(current_row, 9, data.projNameNo)
sheet.write(current_row, 10, i.budgetArea)
sheet.write(current_row, 11, i.projectSponsor)
sheet.write(current_row, 14, i.regionalSpecificInfo)
sheet.write(current_row, 15, i.servicePriorityID)
sheet.write(current_row, 18, i.annualWPInfo)
sheet.write(current_row, 19, data.mPRACategory)
ytd = gl.filter(codeID=code_id).aggregate(Sum('ytdActual'))
fy = gl.filter(codeID=code_id).aggregate(Sum('fybudget'))
sheet.write(current_row, 20, ytd['ytdActual__sum'])
sheet.write(current_row, 21, fy['fybudget__sum'])
# Insert the footer row formulae and '#END OF INPUT'
sheet.write(current_row+2, 0, '#END OF INPUT')
sheet.write(current_row+2, 20, Formula('SUM({}:{})'.format(cellname(4, 20), cellname(current_row, 20))))
sheet.write(current_row+2, 21, Formula('SUM({}:{})'.format(cellname(4, 21), cellname(current_row, 21))))
# Sheet 2 - Service priority checkboxes.
sheet = workbook.get_sheet(1)
write_service_priorities(sheet, nc_sp, pvs_sp, fm_sp)
# Sheet 3: Budget area & project sponsor lookup data.
# This is a list of unique budgetArea and projectSponsor values, written in
# as reference data for macros.
sheet = workbook.get_sheet(2)
write_budget_areas(sheet, ibm_filtered)
write_project_sponsors(sheet, ibm_filtered)
# Select the first sheet.
sheet = workbook.get_sheet(0)
def check_file(fname, verbose, do_punc=False, fmt_info=0, encoding='ascii', onesheet=''):
print()
print(fname)
if do_punc:
checker = ispunc
else:
checker = None
try:
book = open_workbook(fname, formatting_info=fmt_info, on_demand=True)
except TypeError:
try:
book = open_workbook(fname, formatting_info=fmt_info)
except TypeError:
# this is becoming ridiculous
book = open_workbook(fname)
totold = totnew = totnotnull = 0
if onesheet is None or onesheet == "":
shxrange = range(book.nsheets)
else:
try:
shxrange = [int(onesheet)]
except ValueError:
shxrange = [book.sheet_names().index(onesheet)]
for shx in shxrange:
sheet = book.sheet_by_index(shx)
ngoodrows = number_of_good_rows(sheet, checker)
ngoodcols = number_of_good_cols(sheet, checker, nrows=ngoodrows)
oldncells = sheet.nrows * sheet.ncols
newncells = ngoodrows * ngoodcols
totold += oldncells
totnew += newncells
nnotnull = 0
sheet_density_pct_s = ''
if verbose >= 2:
colxrange = range(ngoodcols)
for rowx in xrange(ngoodrows):
rowtypes = sheet.row_types(rowx)
for colx in colxrange:
if rowtypes[colx] not in null_cell_types:
nnotnull += 1
totnotnull += nnotnull
sheet_density_pct = (nnotnull * 100.0) / max(1, newncells)
sheet_density_pct_s = "; den = %5.1f%%" % sheet_density_pct
if verbose >= 3:
# which rows have non_empty cells in the right-most column?
lastcolx = sheet.ncols - 1
for rowx in xrange(sheet.nrows):
cell = sheet.cell(rowx, lastcolx)
if cell.ctype != XL_CELL_EMPTY:
print("%s (%d, %d): type %d, value %r" % (
cellname(rowx, lastcolx),
rowx, lastcolx, cell.ctype, cell.value
))
if (verbose
or ngoodrows != sheet.nrows
or ngoodcols != sheet.ncols
or (verbose >= 2 and sheet_density_pct < 90.0)
):
if oldncells:
pctwaste = (1.0 - float(newncells) / oldncells) * 100.0
else:
pctwaste = 0.0
shname_enc = safe_encode(sheet.name, encoding)
print(
"sheet #%2d: RxC %5d x %3d => %5d x %3d; %4.1f%% waste%s (%s)"
% (shx, sheet.nrows, sheet.ncols,
ngoodrows, ngoodcols, pctwaste,
sheet_density_pct_s, shname_enc))
if hasattr(book, 'unload_sheet'):
book.unload_sheet(shx)
if totold:
pctwaste = (1.0 - float(totnew) / totold) * 100.0
else:
pctwaste = 0.0
print("%d cells => %d cells; %4.1f%% waste" % (totold, totnew, pctwaste))
