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))