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)
