def row_slice(wb,sheet_name,row,col_start,col_end): data_list = [] a = (_get_column_letter(col_start)) b = (_get_column_letter(col_end)) a += str(row) b += str(row) col_range = a+":"+b cell_list = tuple(sheet_name.iter_rows(col_range)) for cell_item in cell_list: for item in cell_item: data_list.append(item.value) data_list = filter(data_list) return data_list
def row_slice(wb, sheet_name, row, col_start, col_end): data_list = [] a = (_get_column_letter(col_start)) b = (_get_column_letter(col_end)) a += str(row) b += str(row) col_range = a + ":" + b cell_list = tuple(sheet_name.iter_rows(col_range)) for cell_item in cell_list: for item in cell_item: data_list.append(item.value) data_list = filter(data_list) return data_list
def output_data(self, data, timestamps): from openpyxl import Workbook from openpyxl.utils import (_get_column_letter) #create excel type item wb = Workbook() # select the active worksheet ws = wb.active #set up column headers as tag names column = 2 #for each tag for tag in data[0]: #get column Numbner column_letter = _get_column_letter(column) #write column tag name to row 1 ws[column_letter + str(1)] = tag[0] column +=1 #i = row #j = column #enter time values i = 1 j = 1 j_letter = _get_column_letter(j) for tsamp in timestamps: i +=1 ws[j_letter + str(i)] = tsamp i = 1 #enter data for row in data: i += 1 j = 1 for column in row: j += 1 j_letter = _get_column_letter(j) ws[j_letter + str(i)] = column[1] wb.save("sample.xlsx") self.status = 'Spreadsheet Created'
def __makeHeaderCells(self): # write average number of vertices # and trigger rate # # @return the next row index to be used self.avgXvalueCell = (1,2) self.avgXvalueCellName = coordToName(*self.avgXvalueCell, rowPrefix = '$') # B$1 if self.xvar == 'vtx': txt = 'avg. number of vertices' elif self.xvar == 'pu': txt = 'pileup' else: raise Exception("internal error " + self.xvar) self[(self.avgXvalueCell[0], self.avgXvalueCell[1] - 1)] = txt if self.xvar != 'vtx' or self.avgNumVertices == None: self[self.avgXvalueCell] = "unknown" else: self.makeNumericCell(self.avgXvalueCell, self.avgNumVertices, "0.0") self.ws.column_dimensions[_get_column_letter(self.avgXvalueCell[1] - 1)].width = 20 #---------- # trigger rate #---------- self.triggerRateCell = (1,9) self.triggerRateCellName = coordToName(*self.triggerRateCell, rowPrefix = "$", colPrefix = "$") self[(self.triggerRateCell[0], self.triggerRateCell[1] - 1)] = 'trigger rate [kHz]:' # H1 self.ws.column_dimensions[_get_column_letter(self.triggerRateCell[1] - 1)].width = 14 self[self.triggerRateCell] = str(self.triggerRateKHz) # 'I1' return 3
def getTestCaseValuesFromExcel(variable): # print variable # input_file_path = getExcelFilePath("\TestCase Repository.xlsx") sheet = openExcelSheet(wb, "TestData") counter = 0 for column in range(1, 20): column_letter = _get_column_letter(column) for row in range(1, sheet.max_row): if sheet[column_letter + str(row)].value == variable: cell = sheet[column_letter + str(row + 1)].value if (method == "SOAP"): return str(cell) else: return "\"" + str(cell) + "\""
def __fillInputData(self, topLeft): # fills the fitted data per FED group on which almost # any other numbers are based on firstRow, firstCol = topLeft #---------- # column headers #---------- self[(firstRow, 4)] = 'sum data sizes [kByte/ev]' # D3 # self.ws['F3'] = '=CONCATENATE("data size at ";CELL("contents";A2);" vertices") ' # self.ws['F3'] = '=\"data size at \"\&A2\&\" vertices\"' # does not work with POI # self.ws['F3'] = '="data size at "&A2&" vertices"' self[(firstRow + 2, firstCol)] = 'FED group' # A5 self[(firstRow + 2, firstCol + 1)] = 'number of feds' # B5 self[(firstRow + 2, firstCol + 2)] = 'subsys' # do we still need this ? # C5 for power in range(self.numCoeffs): self[(firstRow + 2, firstCol + 3 + power)] = utils.getPowerName(power) # D5 self.ws.column_dimensions[_get_column_letter(firstCol + 1)].width = 14 #---------- # fill the evolution data #---------- for rowOffset, data in enumerate(self.evolutionData): thisRow = rowOffset + firstRow + 3 self[(thisRow, firstCol)] = data['subsystem'] # A%d self[(thisRow, firstCol + 1)] = data['numFeds'] # B%d coeffs = data['coeffs'] for power in range(self.numCoeffs): self.makeNumericCell((thisRow, firstCol + 3 + power), data['coeffs'][power], "#,##0.000") # D%d
def generate_axes(domain, plot_objects): """ :param domain: Rows and ranges. :return: Dictionaries formatted {ExcelIndex (e.g. H23): Row or range value} to use as axes. """ row_max = max(domain[0]) row_min = min(domain[0]) ranges = [letter_to_number(e) for e in domain[1]] range_max = _get_column_letter(max(ranges)) range_min = _get_column_letter(min(ranges)) range_min_sub_one = _get_column_letter(letter_to_number(range_min) - 1) range_max_plus_one = _get_column_letter(letter_to_number(range_max) + 1) row_min_sub_one = row_min - 1 row_max_plus_one = row_max + 1 labels = {range_min_sub_one + str(row_min_sub_one): 'Rows/Ranges'} experiments = get_plot_experiments(plot_objects) row_axes = {} for e in xrange(row_min, row_max + 1): row_axes[range_min_sub_one + str(e)] = e row_axes[range_max_plus_one + str(e)] = e range_axes = {} for e in xrange(letter_to_number(range_min), letter_to_number(range_max) + 1): range_axes[_get_column_letter(e) + str(row_min_sub_one)] = e range_axes[_get_column_letter(e) + str(row_max_plus_one)] = e experiment_axes = {} current_row = row_max + 2 for exp in experiments: exp_string = 'EXP: {} - {}. Owner: {}. Field: {}. Purpose: {}. Comments: {}.'.format(exp.name, exp.start_date, exp.user, exp.field, exp.purpose, exp.comments) experiment_axes[range_min + str(current_row)] = exp_string current_row += 1 return row_axes, range_axes, labels, experiment_axes
def generate_axes(domain, plot_objects): """ :param domain: Rows and ranges. :return: Dictionaries formatted {ExcelIndex (e.g. H23): Row or range value} to use as axes. """ left_row = True top_range = True row_max = max(domain[0]) row_min = min(domain[0]) ranges = [letter_to_number(e) for e in domain[1]] range_max = _get_column_letter(max(ranges)) range_min = _get_column_letter(min(ranges)) range_max_plus_one = _get_column_letter(letter_to_number(range_max) + 1) try: range_min_sub_one = _get_column_letter(letter_to_number(range_min) - 1) except ValueError: range_min_sub_one = range_max_plus_one top_range = False row_max_plus_one = row_max + 1 row_min_sub_one = row_min - 1 if row_min_sub_one == 0: row_min_sub_one += 1 left_row = False # Generate basic labels labels = {} if top_range: labels = {range_min_sub_one + str(row_min_sub_one): "Rows/Ranges"} # Generate row axes labels row_axes = {} for e in xrange(row_min, row_max + 1): if left_row: row_axes[range_min_sub_one + str(e)] = e row_axes[range_max_plus_one + str(e)] = e # Generate range axes labels range_axes = {} for e in xrange(letter_to_number(range_min), letter_to_number(range_max) + 1): if top_range: range_axes[_get_column_letter(e) + str(row_min_sub_one)] = e range_axes[_get_column_letter(e) + str(row_max_plus_one)] = e # Generate experiment/script info labels experiments = get_plot_experiments(plot_objects) experiment_axes = { range_min + str(row_max + 2): "FieldMapper / [email protected] / FCPathology / Rendered: {}.".format(datetime.now()) } current_row = row_max + 3 for exp in experiments: exp_string = "EXP: {} - {}. Owner: {}. Field: {}. Purpose: {}. Comments: {}.".format( exp.name, exp.start_date, exp.user, exp.field, exp.purpose, exp.comments ) experiment_axes[range_min + str(current_row)] = exp_string current_row += 1 return row_axes, range_axes, labels, experiment_axes
print "Computing Best mapping" best_arr=linear_sum_assignment(cost_arr) for k in range(len(best_arr[0])): precinct=precinct_arr[best_arr[0][k]] gray_dict[cells[best_arr[1][k]]]=precinct adj=[isAdjacent(precinct,gray_dict) for precinct in precincts] print 'Adjacent precincts = '+str(len([x for x in adj if x==True])) print 'Non-adjacent precincts = '+str(len([x for x in adj if x==False])) ft=Font('Arial',size=6) redFill = PatternFill(start_color=colors.RED, end_color=colors.RED, fill_type='solid') wb=Workbook() ws1=wb.active for cell in gray_dict: ws1.cell(row=cell[0],column=cell[1]).value=gray_dict[cell] comment = Comment(gray_dict[cell], 'Mike Guidry') ws1.cell(row=cell[0],column=cell[1]).comment=comment ws1.cell(row=cell[0],column=cell[1]).font=ft precinct=gray_dict[cell] fill = PatternFill(start_color=color_dict[precinct], end_color=color_dict[precinct], fill_type='solid') ws1.cell(row=cell[0],column=cell[1]).fill=fill ws1.column_dimensions[_get_column_letter(cell[1])].width=2.8 ws1.row_dimensions[cell[0]].height=20 wb.save(r'GA06_block_map.xlsx')
def GetColumnLetter(i): return _get_column_letter(i)
def __fillLimit(self, topLeft, topLeftInputData, maxDataRate, maxDataRateCell, triggerRateCellName, divideByNumFeds, topLeftNumFeds = None, usePileup = False, ): # @param usePileup: if this is True, the limit is shown in terms of pileup (number of interactions # per bunch crossing) rather than number of vertices, assuming # one interaction leads to 0.7 vertices on average # # @return the number of solution columns added assert self.numCoeffs <= 3, "only polynomials up to degree two are supported" if divideByNumFeds: assert topLeftNumFeds != None limitColumn = topLeft[1] maxDataRateCellName = coordToName(*maxDataRateCell, rowPrefix = "$") #---------- # determine group title #---------- if divideByNumFeds: title = "per FED data rate limit" else: title = "group" if self.groupingName != None: mo = re.match("by (.*)$", self.groupingName) if mo: title = mo.group(1) title = title + " data rate limit" self[(maxDataRateCell[0], maxDataRateCell[1] - 1)] = "per " + title + " [MByte/s]" self.ws.column_dimensions[_get_column_letter(maxDataRateCell[1])].width = 27 self[maxDataRateCell] = str(maxDataRate) # P1 #---------- if usePileup: self[topLeft] = "pileup for " + title # O3 else: self[topLeft] = "# vertices for " + title # O3 # number of columns added retval = 1 # number of vertices where we cross the limit is # (data rate limit - data rate offset) / data rate slope # # where data rates are (fed size) * (trigger rate) # e.g. =(P$1-D7*I$1/B7)/(E7*I$1/B7) # # where I$1 is the trigger rate cell # and P$1 is the maximum data rate cell # D7 is the fragment/group size offset # E7 is the fragment/group size slope # B7 is the number of feds in this group # if we should display the pileup instead, we divide this by 0.7 for i in range(len(self.evolutionData)): thisRow = topLeft[0] + 3 + i inputRow = topLeftInputData[0] + i # solution for linear models: # # numVertices = (limit - data rate const) / data rate slope # # solution for quadratic models: # # (-b +/- sqrt( b^2 - 4ac) ) / 2a # # where # a = quadratic term # b = linear term # c = const term - limit # # for lack of a heuristic which of the two solutions is the # wanted one (b can be negative) we just put two columns for the moment if self.numCoeffs < 2: # no dependence on pileup self[(thisRow, limitColumn)] = 'N/A' continue # build expressions for the different coefficients, divided by number # or FEDS or not coeffExprs = [] for power in range(self.numCoeffs): coeffCell = coordToName(inputRow, topLeftInputData[1] + power) # D%d if divideByNumFeds: numFedsCell = coordToName(topLeftNumFeds[0] + i, topLeftNumFeds[1]) # B%d coeffExprs.append("%s * %s / %s" % (coeffCell, triggerRateCellName, numFedsCell)) else: coeffExprs.append("%s * %s" % (coeffCell, triggerRateCellName)) if self.numCoeffs == 2: # linear model exprs = [ "({maxDataRate} - ({coeff0})) / ({coeff1)".format( maxDataRate = maxDataRateCellName, # P$1 coeff0 = coeffExprs[0], coeff1 = coeffExprs[0], ) ] retval = 1 elif self.numCoeffs == 3: # quadratic model replacements = dict( a = coeffExprs[2], b = coeffExprs[1], c = "(%s - %s)" % (coeffExprs[0], maxDataRateCellName) ) # add a protection against negative values under the square root determinantExpr = "POWER({b},2) - 4 * ({a}) * ({c})" exprs = [] for sign in ('-', '+'): # solution of the quadratic equation solutionExpr = "(-({b}) " + sign + " SQRT(" + determinantExpr + ")) / (2 * ({a}))" if usePileup: solutionExpr = "(%s) / 0.7" % solutionExpr exprs.append( ("IF(" + determinantExpr + ">=0," + # determinant non-negative solutionExpr + "," + '"(no solution)"' + # determinant negative ")" ).format(**replacements) ) # end of loop over signs retval = 2 else: raise Exception("uncaught case - internal error") for colOffset, expr in enumerate(exprs): self.makeNumericCell((thisRow, limitColumn + colOffset), # O%d "=" + expr, "0.0") # loop over expressions # end of loop over rows # return number of columns added return retval
def coordToName(row, col, rowPrefix = "", colPrefix = ""): # row and col are one-based return colPrefix + _get_column_letter(col) + rowPrefix + str(row)