def sheet_to_inventory(group_by_col, hostname_col, sheet):
if isinstance(group_by_col, six.string_types):
group_by_col = column_index_from_string(
coordinate_from_string(group_by_col + '1')[0]) - 1
if isinstance(hostname_col, six.string_types):
hostname_col = column_index_from_string(
coordinate_from_string(hostname_col + '1')[0]) - 1
groups = {'_meta': {'hostvars': {}}}
rows = list(sheet.rows)
for row in rows[1:]:
host = row[hostname_col].value
if host is None:
continue
group = row[group_by_col].value
if group is None:
group = default_group
if group not in groups.keys():
groups[group] = {'hosts': [], 'vars': {}}
groups[group]['hosts'].append(row[hostname_col].value)
groups['_meta']['hostvars'][row[hostname_col].value] = {}
for xlsx_head in rows[:1]:
for idx, var_name in enumerate(xlsx_head):
if var_name.value is None:
var_name.value = "xlsx_" + var_name.coordinate
if row[idx].value is not None:
groups['_meta']['hostvars'][row[0].value][
var_name.value.lower().replace(' ',
'_')] = row[idx].value
return groups
def style_range(ws, cell_range, alignment=None, font=None, fill=None, border=None):
"""
:param ws: Excel worksheet instance
:param range: An excel range to style (e.g. A1:F20)
:param alignment: An openpyxl Alignment object
:param font: An openpyxl Font object
:param fill: An openpyxl Fill object
:param border: An openpyxl Border object
"""
start_cell, end_cell = cell_range.split(':')
start_coord = coordinate_from_string(start_cell)
start_row = start_coord[1]
start_col = column_index_from_string(start_coord[0])
end_coord = coordinate_from_string(end_cell)
end_row = end_coord[1]
end_col = column_index_from_string(end_coord[0])
for row in range(start_row, end_row + 1):
for col_idx in range(start_col, end_col + 1):
col = get_column_letter(col_idx)
if alignment:
ws.cell('%s%s' % (col, row)).alignment = alignment
if font:
ws.cell('%s%s' % (col, row)).font = font
if fill:
ws.cell('%s%s' % (col, row)).fill = fill
if border:
ws.cell('%s%s' % (col, row)).border = border
def __init__(self, filename=filename):
with open(filename, 'r+') as f:
self.func_list = f.readlines()
print('range는 ' + self.func_list[0].strip('\n')+ ' 입니다.')
self.range = self.func_list[2].strip('\n')
self.func_list = self.func_list[3:]
input_range = self.range # 'a1:d5'
_ranges = input_range.split(':')
_ranges_start = _ranges[0]
_ranges_end = _ranges[1]
start_xy = coordinate_from_string(_ranges_start)
start_col = column_index_from_string(start_xy[0]) # returns 1
start_row = start_xy[1]
end_xy = coordinate_from_string(_ranges_end)
end_col = column_index_from_string(end_xy[0]) # returns 1
end_row = end_xy[1]
self.start_col = start_col
self.start_row = start_row
self.end_col = end_col
self.end_row = end_row
self.alist = [ start_col, start_row, end_col, end_row]
# print('TODO : 엑셀에서 범위를 읽어 와야 합니다.')
print(self.range)
print('range 를 숫자로 하면')
print(self.alist)
def load_from_wb(workbook_name):
"""
Function used to load the matrix data from a xlsx archive. It is faster then generating it from text
:param workbook_name: path and name of the xlsx file to use to extract information
:return: Returns a dictionary containing the information to create a 'CoocMatrix' object
"""
# Load workbook
wb = load_workbook(filename=workbook_name, read_only=True)
print('wb loaded')
# Load worksheets
ws = wb['cooc_matrix_full']
ws_soc_pmi = wb['soc_pmi_matrix']
# Calculate the matrix dimensions and transform the excel's coordinates to matrix coordinates (to only integers)
rows = ws.rows
matrix_dim = ws.calculate_dimension().split(':')
rows_count = coordinate_from_string(matrix_dim[1])[1] - 1
column_count = column_index_from_string(
coordinate_from_string(matrix_dim[1])[0]) - 1
matrix = np.empty((rows_count, column_count))
print('matrix allocated')
noun_rows = {}
verb_columns = {}
get_column_names(rows, verb_columns)
print('get_column_names completed')
complete_the_loading(rows, noun_rows, matrix)
print('complete_the_loading completed')
rows = ws_soc_pmi.rows
matrix_dim = ws_soc_pmi.calculate_dimension().split(':')
rows_count = coordinate_from_string(matrix_dim[1])[1] - 1
column_count = column_index_from_string(
coordinate_from_string(matrix_dim[1])[0]) - 1
soc_pmi_matrix = np.empty((rows_count, column_count))
soc_pmi_noun_rows = {}
soc_pmi_verb_columns = {}
get_column_names(rows, soc_pmi_verb_columns)
print('get_column_names completed 2')
complete_the_loading(rows, soc_pmi_noun_rows, soc_pmi_matrix)
print('complete_the_loading completed 2')
content = {
'matrix': matrix,
'noun_rows': noun_rows,
'verb_columns': verb_columns,
'soc_pmi_matrix': soc_pmi_matrix,
'soc_pmi_noun_rows': soc_pmi_noun_rows,
'soc_pmi_verb_columns': soc_pmi_verb_columns
}
return content
def check_location(cell_location):
logger.debug("Checking location for " + str(cell_location))
try:
coordinate_from_string(cell_location)
return cell_location
except Exception:
logger.error("Not a valid cell location '" + str(cell_location) + "'")
sys.exit()
def Crawler(wb, writer, name):
print name
startRow = 0
endRow = 0
startCol = 0
endCol = 0
for sheet in wb.worksheets:
title = sheet.title
bEndRowFound = False
for row in sheet.iter_rows():
for cell in row:
cellValue = cell.value
if cellValue == 'SL #':
coord = cell.coordinate
t = utils.coordinate_from_string(coord)
startCol = utils.column_index_from_string(t[0]) - 1
startRow = t[1] + 3
if type(cellValue) is unicode:
if cellValue.strip() == 'Total':
bEndRowFound = True
coord = cell.coordinate
t = utils.coordinate_from_string(coord)
endCol = utils.column_index_from_string(t[0]) + 8
endRow = t[1] - 1
if (bEndRowFound):
break
for irow in range(startRow, endRow):
print name
row = sheet[irow]
rowOut = []
rowOut.append(title)
rowOut.append(name)
for icell in range(startCol, endCol):
cell = row[icell]
cellValue = cell.value
coord = cell.coordinate
t = utils.coordinate_from_string(coord)
cellColumn = t[0]
cellColumNumber = utils.column_index_from_string(cellColumn)
if cellColumNumber == startCol + 2:
rowOut.append(cellValue)
print coord
print cellValue
elif cellColumNumber == startCol + 5:
rowOut.append(cellValue)
print coord
print cellValue
elif cellColumNumber == startCol + 10:
rowOut.append(cellValue)
print coord
print cellValue
writer.writerow(rowOut)
def getColRowSpan(mergeDef):
#merge def is like A1:B2
cells = mergeDef.split(":")
sC, sR = coordinate_from_string(cells[0])
sC = column_index_from_string(sC)
eC, eR = coordinate_from_string(cells[1])
eC = column_index_from_string(eC)
colspan = int(eC) - int(sC) + 1
rowspan = int(eR) - int(sR) + 1
if colspan == 1:
colspan = None
if rowspan == 1:
rowspan = None
return colspan, rowspan
def getColRowSpan(mergeDef):
#merge def is like A1:B2
cells = mergeDef.split(":")
sC,sR=coordinate_from_string(cells[0])
sC = column_index_from_string(sC)
eC,eR=coordinate_from_string(cells[1])
eC = column_index_from_string(eC)
colspan= int(eC) - int(sC) + 1
rowspan = int(eR) - int(sR) + 1
if colspan == 1:
colspan=None
if rowspan == 1:
rowspan=None
return colspan, rowspan
def parse_cell(self, element):
value = element.find(self.VALUE_TAG)
if value is not None:
value = value.text
formula = element.find(self.FORMULA_TAG)
data_type = element.get('t', 'n')
coordinate = element.get('r')
style_id = element.get('s')
# assign formula to cell value unless only the data is desired
if formula is not None and not self.data_only:
data_type = 'f'
if formula.text:
value = "=" + formula.text
else:
value = "="
formula_type = formula.get('t')
if formula_type:
self.ws.formula_attributes[coordinate] = {'t': formula_type}
si = formula.get(
'si') # Shared group index for shared formulas
if si:
self.ws.formula_attributes[coordinate]['si'] = si
ref = formula.get('ref') # Range for shared formulas
if ref:
self.ws.formula_attributes[coordinate]['ref'] = ref
style = {}
if style_id is not None:
style_id = int(style_id)
style = self.styles[style_id]
column, row = coordinate_from_string(coordinate)
cell = Cell(self.ws, column, row, **style)
self.ws._add_cell(cell)
if value is not None:
if data_type == 'n':
value = cell._cast_numeric(value)
elif data_type == 'b':
value = bool(int(value))
elif data_type == 's':
value = self.shared_strings[int(value)]
elif data_type == 'str':
data_type = 's'
else:
if data_type == 'inlineStr':
data_type = 's'
child = element.find(self.INLINE_STRING)
if child is None:
child = element.find(self.INLINE_RICHTEXT)
if child is not None:
value = child.text
if self.guess_types or value is None:
cell.value = value
else:
cell._value = value
cell.data_type = data_type
def write_comments_vml(self):
root = Element("xml")
shape_layout = SubElement(root, "{%s}shapelayout" % officens,
{"{%s}ext" % vmlns: "edit"})
SubElement(shape_layout, "{%s}idmap" % officens, {
"{%s}ext" % vmlns: "edit",
"data": "1"
})
shape_type = SubElement(
root, "{%s}shapetype" % vmlns, {
"id": "_x0000_t202",
"coordsize": "21600,21600",
"{%s}spt" % officens: "202",
"path": "m,l,21600r21600,l21600,xe"
})
SubElement(shape_type, "{%s}stroke" % vmlns, {"joinstyle": "miter"})
SubElement(shape_type, "{%s}path" % vmlns, {
"gradientshapeok": "t",
"{%s}connecttype" % officens: "rect"
})
for idx, comment in enumerate(self.comments, 1026):
shape = _shape_factory()
col, row = coordinate_from_string(comment.ref)
row -= 1
column = column_index_from_string(col) - 1
shape.set('id', "_x0000_s%04d" % idx)
client_data = shape.find("{%s}ClientData" % excelns)
client_data.find("{%s}Row" % excelns).text = str(row)
client_data.find("{%s}Column" % excelns).text = str(column)
root.append(shape)
return tostring(root)
def __init__(self, formula, origin):
# Excel errors out when a workbook has formulae in R1C1 notation,
# regardless of the calcPr:refMode setting, so I'm assuming the
# formulae stored in the workbook must be in A1 notation.
col, self.row = coordinate_from_string(origin)
self.col = column_index_from_string(col)
self.tokenizer = Tokenizer(formula)
def parse_cell(self, element):
value = element.find(self.VALUE_TAG)
if value is not None:
value = value.text
formula = element.find(self.FORMULA_TAG)
data_type = element.get('t', 'n')
coordinate = element.get('r')
style_id = element.get('s')
# assign formula to cell value unless only the data is desired
if formula is not None and not self.data_only:
data_type = 'f'
if formula.text:
value = "=" + formula.text
else:
value = "="
formula_type = formula.get('t')
if formula_type:
self.ws.formula_attributes[coordinate] = {'t': formula_type}
si = formula.get('si') # Shared group index for shared formulas
if si:
self.ws.formula_attributes[coordinate]['si'] = si
ref = formula.get('ref') # Range for shared formulas
if ref:
self.ws.formula_attributes[coordinate]['ref'] = ref
style = {}
if style_id is not None:
style_id = int(style_id)
style = self.styles[style_id]
column, row = coordinate_from_string(coordinate)
cell = Cell(self.ws, column, row, **style)
self.ws._add_cell(cell)
if value is not None:
if data_type == 'n':
value = cell._cast_numeric(value)
elif data_type == 'b':
value = bool(int(value))
elif data_type == 's':
value = self.shared_strings[int(value)]
elif data_type == 'str':
data_type = 's'
else:
if data_type == 'inlineStr':
data_type = 's'
child = element.find(self.INLINE_STRING)
if child is None:
child = element.find(self.INLINE_RICHTEXT)
if child is not None:
value = child.text
if self.guess_types or value is None:
cell.value = value
else:
cell._value=value
cell.data_type=data_type
def collapse_cell_addresses(cells, input_ranges=()):
""" Collapse a collection of cell co-ordinates down into an optimal
range or collection of ranges.
E.g. Cells A1, A2, A3, B1, B2 and B3 should have the data-validation
object applied, attempt to collapse down to a single range, A1:B3.
Currently only collapsing contiguous vertical ranges (i.e. above
example results in A1:A3 B1:B3).
"""
keyfunc = lambda x: x[0]
# Get the raw coordinates for each cell given
raw_coords = [coordinate_from_string(cell) for cell in cells]
# Group up as {column: [list of rows]}
grouped_coords = OrderedDict((k, [c[1] for c in g]) for k, g in
groupby(sorted(raw_coords, key=keyfunc), keyfunc))
ranges = list(input_ranges)
# For each column, find contiguous ranges of rows
for column in grouped_coords:
rows = sorted(grouped_coords[column])
grouped_rows = [[r[1] for r in list(g)] for k, g in
groupby(enumerate(rows),
lambda x: x[0] - x[1])]
for rows in grouped_rows:
if not rows:
continue
fmt = "{0}{1}:{2}{3}"
if len(rows) == 1:
fmt = "{0}{1}"
ranges.append(fmt.format(column, rows[0], column, rows[-1]))
return " ".join(ranges)
def __init__(self, formula, origin):
# Excel errors out when a workbook has formulae in R1C1 notation,
# regardless of the calcPr:refMode setting, so I'm assuming the
# formulae stored in the workbook must be in A1 notation.
col, self.row = coordinate_from_string(origin)
self.col = column_index_from_string(col)
self.tokenizer = Tokenizer(formula)
def _get_row(self, element, min_col=1, max_col=None):
"""Return cells from a particular row"""
col_counter = min_col
for cell in safe_iterator(element, CELL_TAG):
coord = cell.get('r')
column_str, row = coordinate_from_string(coord)
column = column_index_from_string(column_str)
if max_col is not None and column > max_col:
break
if min_col <= column:
if col_counter < column:
for col_counter in range(max(col_counter, min_col), column):
# pad row with missing cells
yield EMPTY_CELL
data_type = cell.get('t', 'n')
style_id = int(cell.get('s', 0))
formula = cell.findtext(FORMULA_TAG)
value = cell.find(VALUE_TAG)
if value is not None:
value = value.text
if formula is not None:
if not self.parent.data_only:
data_type = 'f'
value = "=%s" % formula
yield ReadOnlyCell(self, row, column_str,
value, data_type, style_id)
col_counter = column + 1
if max_col is not None:
for _ in range(col_counter, max_col+1):
yield EMPTY_CELL
def write_comments_vml(self):
root = Element("xml")
shape_layout = SubElement(root, "{%s}shapelayout" % officens,
{"{%s}ext" % vmlns: "edit"})
SubElement(shape_layout,
"{%s}idmap" % officens,
{"{%s}ext" % vmlns: "edit", "data": "1"})
shape_type = SubElement(root,
"{%s}shapetype" % vmlns,
{"id": "_x0000_t202",
"coordsize": "21600,21600",
"{%s}spt" % officens: "202",
"path": "m,l,21600r21600,l21600,xe"})
SubElement(shape_type, "{%s}stroke" % vmlns, {"joinstyle": "miter"})
SubElement(shape_type,
"{%s}path" % vmlns,
{"gradientshapeok": "t",
"{%s}connecttype" % officens: "rect"})
for idx, comment in enumerate(self.comments, 1026):
shape = _shape_factory()
col, row = coordinate_from_string(comment.ref)
row -= 1
column = column_index_from_string(col) - 1
shape.set('id', "_x0000_s%04d" % idx)
client_data = shape.find("{%s}ClientData" % excelns)
client_data.find("{%s}Row" % excelns).text = str(row)
client_data.find("{%s}Column" % excelns).text = str(column)
root.append(shape)
return tostring(root)
def split_cell_story(string):
if args.debug:
log.info("#=coordinate=> [ %s ]" % (string))
char_split_1 = '='
char_split_2 = ','
string_coordinate = string.split(char_split_1)[0]
string_cell_story = string.split(char_split_1)[1:]
coordinate = string_coordinate.split(char_split_2)
cell_story = char_split_1.join(string_cell_story)
coordinate_XY = None
if len(coordinate) == 1:
from openpyxl.utils import coordinate_from_string, column_index_from_string
x_y = coordinate_from_string(coordinate[0]) # returns ('A',4)
col = column_index_from_string(x_y[0]) # returns 1
row = x_y[1]
coordinate_XY = coordinate[0]
coordinate[0] = row
coordinate.append(col)
if args.debug:
log.info("#=coordinate=> [ %s | %s ]=[%s]" %
(coordinate[0], coordinate[1], cell_story))
return (coordinate[0], coordinate[1], coordinate_XY, cell_story)
def __init__(self, row, WBCOLORS, ws, preserve_width=True):
celllist = []
styleList = []
for cell in row:
wcell = wikiCell(cell, WBCOLORS, ws)
celllist.append(wcell)
styleList.append(wcell.style)
# resolve common styles
self.style = commonStyle(styleList)
col, rownum = coordinate_from_string(cell.coordinate)
if preserve_width == True and rownum == 1:
colwidths = getColumnWidths(ws)
if col in colwidths:
width = colwidths[col]
else:
colwidths = None
self.rowwiki = "| "
cellList = []
for cell in celllist:
if not cell.merged:
cellList.append(
cell.getWikiStr(list(self.style.keys()), colwidths))
# also handle the special case of using single pipe when a return character is encountered
self.rowwiki += ("|| ".join(cellList) + "\n").replace("\n||", "\n|")
def plot_graphs(wb_names, title_parts, cell_starts, dirs):
'''Побудова контурних графіків
'''
for i, wb_name in enumerate(wb_names):
wb = load_workbook(filename=os.path.join(DIR_WORK,
wb_name), read_only=True, data_only=True)
title_part = title_parts[i]
print((' Побудова контурних графіків для "{}": '.format(title_part)
.center(80, '*')))
cell_start = cell_starts[i]
cell_start_column, cell_start_row = coordinate_from_string(cell_start)
cell_start_column_index = column_index_from_string(cell_start_column)
target_dir = os.path.join(DIR_WORK, dirs[i])
try:
# Спочатку видаляється папка.
shutil.rmtree(target_dir)
except FileNotFoundError: pass
os.mkdir(target_dir) # Потім створюється папка.
sheets = wb.get_sheet_names()
sheets_count = len(sheets)
for i, sheet_name in enumerate(sheets):
print(u'{}/{} = {:.1%} => WB: {}, WS: {}'.format(i+1, sheets_count,
(i+1)/sheets_count,
title_part, sheet_name))
sheet_name_data = dict(re_pattern_sheet_data.findall(sheet_name))
if 'l' in sheet_name_data.keys():
sheet_name_data['L'] = sheet_name_data.pop('l')
subtitle = (r'd = {d} мм, L = {L} мм, x = {x} см, v = {v} л/(хв$\cdot$м${{}}^2$), '
r'$V$ = {V} м${{}}^3$, $I$ = {I} Вт/м${{}}^2$'.format(
**sheet_name_data) )
ws = wb.get_sheet_by_name(sheet_name)
ci = 0 # Індекс стовпця на робочому аркуші.
while True:
# прохід по стовпцях робочого аркуша.
t = []
cell_start_column_new = get_column_letter(cell_start_column_index+ci)
cell_start_value_begin = '{}{}'.format(cell_start_column_new,
cell_start_row+1)
cell_start_value_end = '{}{}'.format(cell_start_column_new,
cell_start_row+30)
for k, c in enumerate(ws[cell_start_value_begin: cell_start_value_end]):
cell_value = c[0].value
if not k and (cell_value is None):
# Припинити читання, якщо перша кл. стовпця - нішо.
break
t.append(float(cell_value))
if not t:
break # Перехід до наступного аркуша, якщо нічого будувати.
tau = ws['{}{}'.format(cell_start_column_new, cell_start_row)].value
gen_contour(t, plot_type.imshow_p, r'{} для $\tau$ = {}'.format(
title_part, tau), subtitle, out_fname=os.path.join(target_dir,
'{}-TAU={}'.format(sheet_name, tau)))
ci += 1
def translate_formula(self, dest):
"""
Convert the formula into A1 notation.
The formula is converted into A1 assuming it is assigned to the cell
whose address is `dest` (no worksheet name).
"""
tokens = self.get_tokens()
if not tokens:
return ""
elif tokens[0].type == Token.LITERAL:
return tokens[0].value
out = ['=']
# per the spec:
# A compliant producer or consumer considers a defined name in the
# range A1-XFD1048576 to be an error. All other names outside this
# range can be defined as names and overrides a cell reference if an
# ambiguity exists. (I.18.2.5)
dcol, drow = coordinate_from_string(dest)
dcol = column_index_from_string(dcol)
row_delta = drow - self.row
col_delta = dcol - self.col
for token in tokens:
if token.type == Token.OPERAND and token.subtype == Token.RANGE:
out.append(self.translate_range(token.value, row_delta,
col_delta))
else:
out.append(token.value)
return "".join(out)
def _move(cellstr, nrows, ncols):
"""Internal method for adding rows/columns to cell coordinate.
'A1' moved by 1 row, 1 column => 'B2'
Args:
cellstr (str): Cell coordinate (A1,B2)
nrows (int): Number of rows to move (usually down)
ncols (int): Number of columns to move (usually right)
Returns:
str: New cell coordinate.
"""
# WARNING! This will only work up to column Z!
# colidx is a string, rowidx is a number
col_str_idx, rowidx = utils.coordinate_from_string(cellstr)
letters = string.ascii_uppercase
try:
colidx = letters.index(col_str_idx)
newcolidx = colidx + ncols
newrowidx = rowidx + nrows
newcellstr = '%s%i' % (letters[newcolidx], newrowidx)
return newcellstr
except ValueError:
raise ValueError('Could not add %i columns to column %s.' %
(ncols, col_str_idx))
def translate_formula(self, dest):
"""
Convert the formula into A1 notation.
The formula is converted into A1 assuming it is assigned to the cell
whose address is `dest` (no worksheet name).
"""
tokens = self.get_tokens()
if not tokens:
return ""
elif tokens[0].type == Token.LITERAL:
return tokens[0].value
out = ['=']
# per the spec:
# A compliant producer or consumer considers a defined name in the
# range A1-XFD1048576 to be an error. All other names outside this
# range can be defined as names and overrides a cell reference if an
# ambiguity exists. (I.18.2.5)
dcol, drow = coordinate_from_string(dest)
dcol = column_index_from_string(dcol)
row_delta = drow - self.row
col_delta = dcol - self.col
for token in tokens:
if token.type == Token.OPERAND and token.subtype == Token.RANGE:
out.append(
self.translate_range(token.value, row_delta, col_delta))
else:
out.append(token.value)
return "".join(out)
def _readMatrix (self,xlsxFilename,xlsxSheetname,**kwargs):
workBook = load_workbook(xlsxFilename)
inSheet = workBook[xlsxSheetname]
returnMatrix = []
if 'range' in kwargs.keys():
rangeString = kwargs["range"]
startRange, endRange = rangeString.split (":")
startColumn, startRow = coordinate_from_string (startRange)
endColumn, endRow = coordinate_from_string (endRange)
for row in range (startRow,endRow + 1):
lineArray = []
for column in range (column_index_from_string(startColumn),column_index_from_string(endColumn) + 1):
#print inSheet['%s%s'%(get_column_letter(column), row)].value
lineArray.append(inSheet['%s%s'%(get_column_letter(column), row)].value)
column += 1
returnMatrix.append(lineArray)
column = 1
row += 1
return returnMatrix
else:
if 'start' in kwargs.keys():
startCell = kwargs["start"]
startColumnLetter, startRow = coordinate_from_string (startCell)
startColumn = column_index_from_string(startColumnLetter)
else:
startColumn, startRow = 1,1
if 'cellsPerRow' in kwargs.keys():
numberOfCellsPerRow = kwargs["cellsPerRow"]
row = startRow
column = startColumn
while inSheet['%s%s'%(get_column_letter(column), row)].value:
lineArray = []
if numberOfCellsPerRow:
while column <= startColumn + numberOfCellsPerRow - 1:
#print inSheet['%s%s'%(get_column_letter(column), row)].value
lineArray.append(inSheet['%s%s'%(get_column_letter(column), row)].value)
column += 1
else:
while inSheet['%s%s'%(get_column_letter(column), row)].value:
#print inSheet['%s%s'%(get_column_letter(column), row)].value
lineArray.append(inSheet['%s%s'%(get_column_letter(column), row)].value)
column += 1
returnMatrix.append(lineArray)
column = startColumn
row += 1
return returnMatrix
def _get_cell(self, coordinate):
"""Cells are returned by a generator which can be empty"""
col, row = coordinate_from_string(coordinate)
col = column_index_from_string(col)
cell = tuple(self.get_squared_range(col, row, col, row))[0]
if cell:
return cell[0]
return EMPTY_CELL
def add_shape_vml(self, root, idx, comment):
col, row = coordinate_from_string(comment.ref)
row -= 1
column = column_index_from_string(col) - 1
shape = _shape_factory(row, column)
shape.set('id', "_x0000_s%04d" % idx)
root.append(shape)
def add_comment_shape(self, root, idx, coord):
col, row = coordinate_from_string(coord)
row -= 1
column = column_index_from_string(col) - 1
shape = _shape_factory(row, column)
shape.set('id', "_x0000_s%04d" % idx)
root.append(shape)
def _get_cell(self, coordinate):
"""Cells are returned by a generator which can be empty"""
col, row = coordinate_from_string(coordinate)
col = column_index_from_string(col)
cell = tuple(self.get_squared_range(col, row, col, row))[0]
if cell:
return cell[0]
return EMPTY_CELL
def add_comment_shape(self, root, idx, coord, height, width):
col, row = coordinate_from_string(coord)
row -= 1
column = column_index_from_string(col) - 1
shape = _shape_factory(row, column, height, width)
shape.set('id', "_x0000_s%04d" % idx)
root.append(shape)
def read_excel_adv(self, io, is_preview=False, **kwds):
"""
# TODO: Handle multiple sheets at once. Each sheet may have difference col and rows range.
Args:
io (string): excel file name or pandas ExcelFile object
is_preview (boolean): to get the dataframe with preview rows only.
Returns:
dataframe
"""
header = []
if self.header_xls_start and self.header_xls_end:
scol, srow = coordinate_from_string(self.header_xls_start)
ecol, erow = coordinate_from_string(self.header_xls_end)
# header, skiprows, usecols
header = [x for x in range(erow - srow + 1)]
usecols = scol + ":" + ecol
skiprows = srow - 1
if is_preview:
workbook = pd.ExcelFile(io)
rows = workbook.book.sheet_by_index(0).nrows
# Get only preview rows. Way to implement nrows (in read_csv)
skip_footer = (rows - skiprows - self.nrows_preview)
df = pd.read_excel(io, header=header, skiprows=skiprows, usecols=usecols,
skip_footer=skip_footer, **kwds)
else:
df = pd.read_excel(io, header=header, skiprows=skiprows, usecols=usecols, **kwds)
else:
df = pd.read_excel(io, **kwds)
if self.remove_blank_cols:
df = df.dropna(axis='columns', how='all')
if self.remove_blank_rows:
df = df.dropna(axis='rows', how='all')
if self.collapse_header:
if len(header) > 1:
df.columns = [' '.join([s for s in col if not 'Unnamed' in s]).strip().replace("\n", ' ')
for col in df.columns.values]
else:
df.rename(columns=lambda x: x.strip().replace("\n", ' '), inplace=True)
return df
def row_col_from_cell(cell):
"""
:param cell: a cell string e.g. 'B12'
:return: row, col in numerical index
"""
from openpyxl.utils import column_index_from_string, coordinate_from_string
(col,row) = coordinate_from_string(cell)
return row, column_index_from_string(col)
def surround_border(ws, top_left, bottom_right):
"""
This method surrounds the range with a thin border because openpyxl
can't apply a style to a range of cells.
This only works for a range of cells whose width is greater than 1
and height is greater than 1 :(
:param ws: (Object) Worksheet
:param top_left: (String) Cell location ie 'A1'
:param bottom_right: (String) Cell location ie 'A1'
:return: (Object) Worksheet
"""
top_left = list(coordinate_from_string(top_left))
bottom_right = list(coordinate_from_string(bottom_right))
top_left[0] = column_index_from_string(top_left[0])
bottom_right[0] = column_index_from_string(bottom_right[0])
for column in range(top_left[0], bottom_right[0] + 1):
for row in range(top_left[1], bottom_right[1] + 1):
if column == top_left[0]:
ws.cell(row=row, column=column).border = Border(left=Side(border_style='thin'))
if column == bottom_right[0]:
ws.cell(row=row, column=column).border = Border(right=Side(border_style='thin'))
if row == top_left[1]:
ws.cell(row=row, column=column).border = Border(top=Side(border_style='thin'))
if row == bottom_right[1]:
ws.cell(row=row, column=column).border = Border(bottom=Side(border_style='thin'))
# the four corners
if column == top_left[0] and row == top_left[1]:
ws.cell(row=row, column=column).border = Border(left=Side(border_style='thin'),
top=Side(border_style='thin'))
if column == top_left[0] and row == bottom_right[1]:
ws.cell(row=row, column=column).border = Border(left=Side(border_style='thin'),
bottom=Side(border_style='thin'))
if column == bottom_right[0] and row == bottom_right[1]:
ws.cell(row=row, column=column).border = Border(right=Side(border_style='thin'),
bottom=Side(border_style='thin'))
if column == bottom_right[0] and row == top_left[1]:
ws.cell(row=row, column=column).border = Border(right=Side(border_style='thin'),
top=Side(border_style='thin'))
return ws
def _get_cell(self, coordinate):
"""
Internal method for getting a cell from a worksheet.
Will create a new cell if one doesn't already exist.
"""
coordinate = coordinate.upper()
if not coordinate in self._cells:
column, row = coordinate_from_string(coordinate)
self._new_cell(column, row)
return self._cells[coordinate]
def row_and_column(cells_address: str) -> (int, int):
"""
Converts the string address of the cell (e.g. A1) into the row's number (starting from 1) and column's number
(starting from 1).
"""
column_as_letter, row_number = coordinate_from_string(cells_address)
column_number = column_index_from_string(column_as_letter)
return row_number, column_number
def _update_style(ws, cell_range):
start_cell, end_cell = str(cell_range).split(':')
start_coord = coordinate_from_string(start_cell)
end_coord = coordinate_from_string(end_cell)
start_row = start_coord[1]
end_row = end_coord[1]
start_col = column_index_from_string(start_coord[0])
end_col = column_index_from_string(end_coord[0])
border_style = ws.cell(row=start_row, column=start_col).border.left.style
color = ws.cell(row=start_row, column=start_col).border.left.color
cellborder = _get_border(border_style, color)
for row in range(start_row, end_row + 1):
for col in range(start_col, end_col + 1):
ws.cell(row=row, column=col).border = cellborder
def coordinate_to_index(coordinate_string, zero_based=True):
# returns ('A',4) for example
xy = coordinate_from_string(coordinate_string)
# openpyxl will assume column A returns 1
col = column_index_from_string(xy[0])
row = xy[1]
if zero_based:
col = col - 1
row = row - 1
return (col, row)
def last_column_of_table(self, format):
for i, table in enumerate(self.sheet._tables):
if table.displayName == self.tableName:
old_table_reference = table.ref
last_cell_of_table = old_table_reference[old_table_reference.find(':') + 1:]
last_column_letter = coordinate_from_string(last_cell_of_table)[0]
if format == 'letter':
return last_column_letter
elif format == 'number':
return column_index_from_string(last_column_letter)
def _get_cell(self, coordinate):
"""
Internal method for getting a cell from a worksheet.
Will create a new cell if one doesn't already exist.
"""
coordinate = coordinate.upper()
if not coordinate in self._cells:
column, row = coordinate_from_string(coordinate)
self._new_cell(column, row)
return self._cells[coordinate]
def calc_and_limit_sheet_dim(sheet, max_col, max_row):
dim = sheet.calculate_dimension()
dim = dim.split(':')
xy = coordinate_from_string(dim[1])
count_col = column_index_from_string(xy[0])
count_row = xy[1]
if max_col and (count_col > max_col):
count_col = max_col
if max_row and (count_row > max_row):
count_row = max_row
return (count_col, count_row)
def convert_coordinates_alphanum_to_num(alphanum_coordinates_str, zero_indexed=False):
"""
Function that converts coordinates from a alphanumeric inde to a numeric one (e.g. 'A1' -> [1,1])
"""
xy = coordinate_from_string(alphanum_coordinates_str) # returns ('A',1)
if zero_indexed:
col = column_index_from_string(xy[0]) - 1
row = xy[1] - 1
else:
col = column_index_from_string(xy[0])
row = xy[1]
return [row, col]
def mergedcell_info(merged_ranges):
'''
returns head merged cells and hidden cells under the merged ones
'''
global cell_dict
global head_cells
global hidden_cells
ind = 0
for mrange in merged_ranges:
'''
to convert merged range, say A1:B3 to (0,0) to (1,2)
'''
mrange = str(mrange)
cell = mrange.split(':')
xy = coordinate_from_string(cell[0])
col1 = column_index_from_string(xy[0])
row1 = xy[1]
head_cells[str((row1 - 1, col1 - 1))] = str(ind)
xy = coordinate_from_string(cell[1])
col2 = column_index_from_string(xy[0])
row2 = xy[1]
for i in range(row1 - 1, row2):
for j in range(col1 - 1, col2):
hidden_cells.append((i, j))
rowspan = (row2 - row1) + 1
colspan = (col2 - col1) + 1
cell_dict[str(ind)] = {}
cell_dict[str(ind)]["rowspan"] = str(rowspan)
cell_dict[str(ind)]["colspan"] = str(colspan)
ind += 1
def __try_use_cell(self, line):
if not line.startswith('['):
return False
test = line.replace('[', '')
test = test.replace(']', '')
test = test.replace(' ', '')
try:
xy = coordinate_from_string(test)
self.r = xy[1]
self.c = column_index_from_string(xy[0])
return True
except:
return False
def applyFmt(tblStyle, trStyle, tdStyle, cell, ws):
# resolve all the styles
finalStyle = deepcopy(tblStyle)
if finalStyle == None:
finalStyle = {}
for s in [trStyle, tdStyle]:
if s == None:
continue
for k, v in s.iteritems():
if v == False:
continue
finalStyle[k] = v
font = Font()
for k, v in finalStyle.iteritems():
if k == "italic" and v != False:
font.i = True
if k == "underline" and v != False:
font.u = Font.UNDERLINE_SINGLE
if k == "line-through" and v != False:
font.strikethrough = True
if k == "font_name" and v != False:
font.name = v
if k == "bold" and v == True:
font.bold = True
if k == 'width' and v != "" and v != False:
c, r = coordinate_from_string(cell.coordinate)
m = re.match("([\d\.]+)(\D+)", v)
if m != None:
w = m.group(1)
units = m.group(2)
if units == "in":
w = float(w) * 12
ws.column_dimensions[c].width = w
if k == "color" and v != False:
if v[1] == "#":
font.color = v[1:]
else:
try:
hxcol = webcolors.name_to_hex(v)
font.color = hxcol[1:]
except:
pass
if k == "background-color" and v != False:
c = Color(v[1:])
fill = PatternFill(patternType=fills.FILL_SOLID, fgColor=c)
cell.fill = fill
cell.font = font
def applyFmt(tblStyle, trStyle,tdStyle, cell,ws):
# resolve all the styles
finalStyle=deepcopy(tblStyle)
if finalStyle == None:
finalStyle ={}
for s in [trStyle,tdStyle]:
if s==None:
continue
for k,v in s.iteritems():
if v == False:
continue
finalStyle[k]=v
font=Font()
for k,v in finalStyle.iteritems():
if k == "italic" and v!=False:
font.i=True
if k == "underline" and v!=False:
font.u=Font.UNDERLINE_SINGLE
if k == "line-through" and v!=False:
font.strikethrough=True
if k == "font_name" and v!=False:
font.name=v
if k=="bold" and v==True:
font.bold=True
if k=='width' and v != "" and v != False:
c,r=coordinate_from_string(cell.coordinate)
m=re.match("([\d\.]+)(\D+)",v)
if m != None:
w=m.group(1)
units=m.group(2)
if units == "in":
w=float(w)*12
ws.column_dimensions[c].width=w
if k == "color" and v != False:
if v[1]=="#":
font.color = v[1:]
else:
try:
hxcol=webcolors.name_to_hex(v)
font.color=hxcol[1:]
except:
pass
if k == "background-color" and v != False:
c=Color(v[1:])
fill=PatternFill(patternType=fills.FILL_SOLID,fgColor=c)
cell.fill = fill
cell.font=font
def _fill_bi(self, workbook, data_dict, worksheet_range):
for sheet_name in data_dict:
worksheet = data_dict[sheet_name]
if isinstance(sheet_name, str):
st = get_sheet_by_name(workbook, sheet_name)
elif isinstance(sheet_name, int):
st = workbook.worksheets[sheet_name - 1]
if not st:
raise ValidationError(
_('Sheet %s not found!') % sheet_name)
if not worksheet.get('_BI_', False):
continue
for rc, bi_dict in worksheet.get('_BI_', {}).iteritems():
req_field = ['df', 'oper_code']
key_field = bi_dict.keys()
if set(req_field) != set(key_field):
raise ValidationError(
_('_BI_ requires \n'
' - df: initial DataFrame from worksheet\n'
' - oper_code: pandas operation code'))
# Get dataframe
src_df = bi_dict['df']
src_st = get_sheet_by_name(workbook, src_df)
df = load_workbook_range(worksheet_range[src_df], src_st)
eval_context = {'df': df, 'pd': pd, 'np': np}
# Get DF using safe_eval method
df = safe_eval(bi_dict['oper_code'], eval_context,
mode="exec", nocopy=True)
if 'result' in eval_context: # use result=...
df = eval_context['result']
if df is None:
df = eval(bi_dict['oper_code'], eval_context)
if df.empty:
continue
df = df.reset_index()
rows = dataframe_to_rows(df, index=False, header=False)
# Get init cell index
xy = coordinate_from_string(rc)
c = column_index_from_string(xy[0])
r = xy[1]
for r_idx, row in enumerate(rows, r):
for c_idx, value in enumerate(row, c):
st.cell(row=r_idx, column=c_idx, value=value)
def cell(self, coordinate=None, row=None, column=None, value=None):
"""Returns a cell object based on the given coordinates.
Usage: cell(coodinate='A15') **or** cell(row=15, column=1)
If `coordinates` are not given, then row *and* column must be given.
Cells are kept in a dictionary which is empty at the worksheet
creation. Calling `cell` creates the cell in memory when they
are first accessed, to reduce memory usage.
:param coordinate: coordinates of the cell (e.g. 'B12')
:type coordinate: string
:param row: row index of the cell (e.g. 4)
:type row: int
:param column: column index of the cell (e.g. 3)
:type column: int
:raise: InsufficientCoordinatesException when coordinate or (row and column) are not given
:rtype: :class:openpyxl.cell.Cell
"""
if coordinate is None:
if (row is None or column is None):
msg = "You have to provide a value either for " \
"'coordinate' or for 'row' *and* 'column'"
raise InsufficientCoordinatesException(msg)
else:
column = get_column_letter(column)
coordinate = '%s%s' % (column, row)
else:
coordinate = coordinate.upper().replace('$', '')
if coordinate not in self._cells:
if row is None or column is None:
column, row = coordinate_from_string(coordinate)
self._new_cell(column, row, value)
return self._cells[coordinate]
def _writeMatrix (self,xlsxFilename,xlsxSheetname,contentMatrix,**kwargs):
def checkFileAndSheet():
if os.path.isfile(xlsxFilename):
outWorkbook = load_workbook(xlsxFilename)
if xlsxSheetname in outWorkbook.get_sheet_names():
outSheet = outWorkbook[xlsxSheetname]
else:
outSheet = outWorkbook.create_sheet()
outSheet.title = xlsxSheetname
else:
outWorkbook = Workbook()
outWorkbook.remove_sheet(outWorkbook.worksheets[0])
outSheet = outWorkbook.create_sheet()
outSheet.title = xlsxSheetname
return outWorkbook, outSheet
def writeLine(lineRow,columnOffset,contentList):
for myColumn in range (1,len(contentList) +1 ):
myCell = self.outSheet['%s%s'%(get_column_letter(myColumn + columnOffset), lineRow)]
#print contentList[myColumn - 1 ] ###FIXME###
myCell.value = contentList[myColumn - 1 ]
#myCell.style = cellStyle
self.outWorkbook, self.outSheet = checkFileAndSheet()
if 'start' in kwargs.keys():
startCell = kwargs["start"]
startColumn, startRow = coordinate_from_string (startCell)
rowOffset = startRow - 1
columnOffset = column_index_from_string(startColumn) - 1
else:
rowOffset = 0
columnOffset = 0
for myRowIndex in range (1 ,len(contentMatrix) +1 ):
writeLine (rowOffset + myRowIndex,columnOffset,contentMatrix[myRowIndex - 1])
self.outWorkbook.save(filename = xlsxFilename)
return True
def freeze_panes(self, topLeftCell):
if not topLeftCell:
topLeftCell = None
elif isinstance(topLeftCell, str):
topLeftCell = topLeftCell.upper()
else: # Assume a cell
topLeftCell = topLeftCell.coordinate
if topLeftCell == 'A1':
topLeftCell = None
if not topLeftCell:
self.sheet_view.pane = None
return
if topLeftCell is not None:
colName, row = coordinate_from_string(topLeftCell)
column = column_index_from_string(colName)
view = self.sheet_view
view.pane = Pane(topLeftCell=topLeftCell,
activePane="topRight",
state="frozen")
view.selection[0].pane = "topRight"
if column > 1:
view.pane.xSplit = column - 1
if row > 1:
view.pane.ySplit = row - 1
view.pane.activePane = 'bottomLeft'
view.selection[0].pane = "bottomLeft"
if column > 1:
view.selection[0].pane = "bottomRight"
view.pane.activePane = 'bottomRight'
if row > 1 and column > 1:
sel = list(view.selection)
sel.insert(0, Selection(pane="topRight", activeCell=None, sqref=None))
sel.insert(1, Selection(pane="bottomLeft", activeCell=None, sqref=None))
view.selection = sel
def __init__(self,row, WBCOLORS, ws,preserve_width=True):
celllist=[]
styleList=[]
for cell in row:
wcell=wikiCell(cell,WBCOLORS,ws)
celllist.append(wcell)
styleList.append(wcell.style)
# resolve common styles
self.style = commonStyle(styleList)
col,rownum=coordinate_from_string(cell.coordinate)
if preserve_width ==True and rownum==1:
colwidths = getColumnWidths(ws)
if colwidths.has_key(col):
width=colwidths[col]
else:
colwidths=None
self.rowwiki="| "
cellList=[]
for cell in celllist:
if not cell.merged:
cellList.append(cell.getWikiStr(self.style.keys(),colwidths))
# also handle the special case of using single pipe when a return character is encountered
self.rowwiki+=("|| ".join(cellList)+"\n").replace("\n||","\n|")
def _write_comment_shape(self, comment, idx):
# get zero-indexed coordinates of the comment
col, row = coordinate_from_string(comment._parent.coordinate)
row -= 1
column = column_index_from_string(col) - 1
style = ("position:absolute; margin-left:59.25pt;"
"margin-top:1.5pt;width:%(width)s;height:%(height)s;"
"z-index:1;visibility:hidden") % {'height': comment._height,
'width': comment._width}
attrs = {
"id": "_x0000_s%04d" % idx ,
"type": "#_x0000_t202",
"style": style,
"fillcolor": "#ffffe1",
"{%s}insetmode" % officens: "auto"
}
shape = Element("{%s}shape" % vmlns, attrs)
SubElement(shape, "{%s}fill" % vmlns,
{"color2": "#ffffe1"})
SubElement(shape, "{%s}shadow" % vmlns,
{"color": "black", "obscured": "t"})
SubElement(shape, "{%s}path" % vmlns,
{"{%s}connecttype" % officens: "none"})
textbox = SubElement(shape, "{%s}textbox" % vmlns,
{"style": "mso-direction-alt:auto"})
SubElement(textbox, "div", {"style": "text-align:left"})
client_data = SubElement(shape, "{%s}ClientData" % excelns,
{"ObjectType": "Note"})
SubElement(client_data, "{%s}MoveWithCells" % excelns)
SubElement(client_data, "{%s}SizeWithCells" % excelns)
SubElement(client_data, "{%s}AutoFill" % excelns).text = "False"
SubElement(client_data, "{%s}Row" % excelns).text = "%d" % row
SubElement(client_data, "{%s}Column" % excelns).text = "%d" % column
return shape
def translate_template(self, xml_path, params):
#Counter for the input parameters
param_counter = 0
#Counter for the segments within pages
segment_counter = 0
#Wild Card Counter
wc_counter = 0
#Clear the sheets out of the workbook
sn = self.wb.get_sheet_names()
for s in sn:
self.wb.remove_sheet(self.wb.get_sheet_by_name(s))
self.wb.create_sheet('Header', 0)
#Connect to the DB
self.con = lite.connect(self.db_path)
self.cur = self.con.cursor()
with self.con:
#Read the XML Template
self.tree = ET.parse(xml_path)
self.root = self.tree.getroot()
header_ws = self.wb.get_sheet_by_name('Header')
for child in self.root:
if child.tag == 'Header':
for row in child:
#Process each header row
for element in row:
#Process each element
#We expect two property values:
#cell, the first cell of the merge
#end_cell, the last cell in the merge
if '?' not in element.text:
header_ws[element.attrib['start_cell']] = element.text
#If a wildcard is encountered, we need to replace it with the
#correct parameter
else:
text = list(element.text)
text = self.process_for_wildcard(text, params, wc_counter, param_counter)
header_ws[element.attrib['start_cell']] = ''.join(text)
self.set_header_font(header_ws[element.attrib['start_cell']])
header_ws.merge_cells('%s:%s' % (element.attrib['start_cell'], element.attrib['end_cell']))
print('Header element placed')
elif child.tag == 'Body':
#Process the body segment
for page in child:
#Process each page
segment_counter = 0
body_ws = self.wb.create_sheet(page.attrib['name'])
for segment in page:
if segment.tag == 'TestScript':
#Execute the specialized Test Script Steps Export
#This segment needs to be hardcoded due to the ability to construct
#nonlinear workflows.
#Here, we process a full testscript and output each workflow in an optimized state
start_cell = segment.attrib['cell']
flow_list=[]
header_list=[]
db_column_list=[]
for query_segment in segment:
if query_segment.tag == 'Query':
#Execute the query and place the results into the flow list,
#we need the first column in the query to be workflow.id and this should be unique
if '?' not in query_segment.text:
self.cur.execute(query_segment.text)
#If a wildcard is encountered, we need to replace it with the
#correct parameter
else:
text = list(query_segment.text)
text = self.process_for_wildcard(text, params, wc_counter, param_counter)
self.cur.execute(text)
print('query %s executed' % (text))
workflows = self.cur.fetchall()
#Find the flow list for the testscript
for workflow in workflows:
flow = self.generate_workflow_export(workflow[0])
flow_list.append(flow)
if query_segment.tag == 'KeyActionBlock':
for header_segment in query_segment:
if header_segment.tag == 'Column':
header_list.append(header_segment.text)
db_column_list.append(header_segment.text)
elif header_segment.tag == 'Blank':
header_list.append(header_segment.text)
#Find the row and col of the start cell
col = Utils.column_index_from_string(start_cell[0])
row = int(float(start_cell[1]))
for f in flow_list:
#Build the key action trees and store them in memory
f.build_keyactiontree()
#TO-DO: Write the workflow to the sheet
#Write the header
self.write_workflow_header(header_list, row, col)
#TO-DO: Call the Iterator and write out the key actions
else:
for child in segment:
if child.tag == 'Title':
if '?' not in child.text:
body_ws[segment.attrib['cell']] = child.text
#If a wildcard is encountered, we need to replace it with the
#correct parameter
else:
text = list(child.text)
text = self.process_for_wildcard(text, params, wc_counter, param_counter)
body_ws[segment.attrib['cell']] = ''.join(text)
self.set_header_font(body_ws[segment.attrib['cell']])
print('Data Title element %s placed in cell %s' % (child.text, segment.attrib['cell']))
segment_counter+=1
elif child.tag == 'Header':
i=0
for column in child:
#Place the column header for each query column
cell = Utils.coordinate_from_string(segment.attrib['cell'])
col = Utils.column_index_from_string(cell[0])
body_ws['%s%s' % (Utils.get_column_letter(col+i), 1 + segment_counter)] = column.text
self.set_base_font(body_ws['%s%s' % (Utils.get_column_letter(col+i), 1 + segment_counter)])
print('Data Header element %s placed in cell %s%s' % (column.text, Utils.get_column_letter(col+i), 2))
i+=1
segment_counter+=1
elif child.tag == 'Query':
#Execute the query and place the results into the page
if '?' not in child.text:
self.cur.execute(child.text)
#If a wildcard is encountered, we need to replace it with the
#correct parameter
else:
text = list(child.text)
text = self.process_for_wildcard(text, params, wc_counter, param_counter)
self.cur.execute(text)
data = self.cur.fetchall()
print('query %s executed' % (text))
i=3
for row in data:
j=0
segment_counter+=1
#Place the data into the report
for e in row:
cell = Utils.coordinate_from_string(segment.attrib['cell'])
col = Utils.column_index_from_string(cell[0])
body_ws['%s%s' % (Utils.get_column_letter(col+j), i)] = e
self.set_base_font(body_ws['%s%s' % (Utils.get_column_letter(col+j), i)])
print('Data Element %s placed in column %s%s' % (e, Utils.get_column_letter(col+i), j))
j+=1
i+=1
self.wb.save('Export.xlsx')
def __init__(self,cell,WBCOLORS,ws):
colspan=None
rowspan=None
self.col,self.rownum=coordinate_from_string(cell.coordinate)
self.style={}
self.width=None
# if rownum == 1:
# if colwidths.has_key(col):
# self.width=colwidths[col]
if cell.coordinate in ws.merged_cells:
for mrange in ws.merged_cell_ranges:
if mrange.startswith(cell.coordinate):
colspan,rowspan=getColRowSpan(mrange)
break
else:
self.merged=True
return
self.cell = cell
cval=""
try:
if cell.value == None:
cval=" "
else:
if isinstance(cell.value, unicode):
cval=cell.value.strip()
if cell.is_date:
cval=self.__doDateFmt()
elif cell.number_format.endswith("%"):
dotind = cell.number_format.find(".")
if dotind == -1:
fmtStr = "%0d%%"
else:
fmtStr = "%" + str(dotind) + ".%d"%(len(cell.number_format) - dotind - 2) + "f%%"
cval = unicode(fmtStr % (cell.value * 100),'utf-8')
else:
cval=unicode(str(cell.value),'utf-8').strip()
except:
cval="" # not sure what to do here
# if value starts with http, then wrap it in []s,otherwise it is used verbatim
if cval.startswith("http://"):
cval="["+cval+"]"
# cval=cval.replace(u"\n", "<br/>")
self.value = cval
self.bg=getCellColor(cell.fill.fgColor, WBCOLORS)
self.fg=getCellColor(cell.font.color, WBCOLORS)
if self.fg== "#000000":
self.fg=None
self.bold = cell.font.b
self.italics = cell.font.i
self.underline = cell.font.u
self.strike=cell.font.strikethrough
self.font_name= cell.font.name
self.style["bg"]=self.bg
self.style["fg"]=self.fg
self.style["underline"]=self.underline
self.style["strike"]=self.strike
self.style["italics"]=self.italics
self.style["font_name"]=self.font_name
self.style["bold"]=self.bold
self.style["colspan"]=colspan
self.style["rowspan"]=rowspan
self.style["width"]=self.width
self.style["align"]=self.cell.alignment.horizontal
def test_roundtrip(self, value):
wb = Workbook()
ws = wb.active
assert ws.point_pos(*ws.cell(value).anchor) == coordinate_from_string(value)
def translate_template(self, xml_path, params):
#Counter for the input parameters
param_counter = 0
#Counter for the segments within pages
segment_counter = 0
#Wild Card Counter
wc_counter = 0
#Clear the sheets out of the workbook
sn = self.wb.get_sheet_names()
for s in sn:
self.wb.remove_sheet(self.wb.get_sheet_by_name(s))
self.wb.create_sheet('Header', 0)
#Connect to the DB
self.con = lite.connect(self.db_path)
self.cur = self.con.cursor()
with self.con:
#Read the XML Template
self.tree = ET.parse(xml_path)
self.root = self.tree.getroot()
header_ws = self.wb.get_sheet_by_name('Header')
for child in self.root:
if child.tag == 'Header':
for row in child:
#Process each header row
for element in row:
#Process each element
#We expect two property values:
#cell, the first cell of the merge
#end_cell, the last cell in the merge
if '?' not in element.text:
header_ws[element.attrib['start_cell']] = element.text
#If a wildcard is encountered, we need to replace it with the
#correct parameter
else:
text = list(element.text)
wc_counter = 0
for i in text:
if i == '?':
text[wc_counter] = params[int(text[wc_counter + 1])]
print('Parameter %s used' % (params[int(text[wc_counter + 1])]))
del text[wc_counter + 1]
param_counter+=1
wc_counter+=1
header_ws[element.attrib['start_cell']] = ''.join(text)
if platform.system() == 'Windows':
header_ws[element.attrib['start_cell']].font = self.header_font
header_ws[element.attrib['start_cell']].fill = self.header_fill
header_ws[element.attrib['start_cell']].border = self.header_border
header_ws[element.attrib['start_cell']].alignment = self.header_alignment
header_ws[element.attrib['start_cell']].number_format = self.header_number_format
header_ws.merge_cells('%s:%s' % (element.attrib['start_cell'], element.attrib['end_cell']))
print('Header element placed')
elif child.tag == 'Body':
#Process the body segment
for page in child:
#Process each page
segment_counter = 0
body_ws = self.wb.create_sheet(page.attrib['name'])
for segment in page:
if segment.tag == 'TestScriptSteps':
#Execute the specialized Test Script Steps Export
#This segment needs to be hardcoded due to the ability to construct
#nonlinear workflows.
#Here, we process a full testscript and output each workflow in an optimized state
#We expect the first input parameter to be Test Script, then Project, then Client,
#After those, others can be used and added to the template
start_cell = segment.attrib['cell']
#Set the parameter counter so that it doesn't break after running this
param_counter+=3
#Find the workflows associated with the test script
self.cur.execute('select wf.id, wf.name from workflow wf left join testscript ts on ts.id = wf.testscriptid) left join project p on ts.projectid = p.id) left join client c on p.clientid = c.id where ts.name = %s and p.name = %s and c.name = %s order by w.id;' % (params[0], params[1], params[2]))
workflows = self.cur.fetchall()
#Iterate over the cells
col = Utils.column_index_from_string(start_cell[0])
row = int(float(start_cell[1]))
flow_counter = 0
for workflow in workflows:
w_row = row + flow_counter
w_col = col
flow_counter += self.generate_workflow_export(workflow[1], params[0], params[1], params[2], body_ws, w_row, w_col)
elif segment.tag == 'WorkflowSteps':
#Execute a single workflow export
start_cell = segment.attrib['cell']
col = Utils.column_index_from_string(start_cell[0])
row = int(float(start_cell[1]))
#We expect the first input parameter to be Test Script, then Project, then Client, then Workflow
#After those, others can be used and added to the template
self.generate_workflow_export(params[3], params[0], params[1], params[2], row, col)
else:
for child in segment:
if child.tag == 'Title':
if '?' not in child.text:
body_ws[segment.attrib['cell']] = child.text
#If a wildcard is encountered, we need to replace it with the
#correct parameter
else:
text = list(child.text)
wc_counter = 0
for i in text:
if i == '?':
text[wc_counter] = params[int(text[wc_counter + 1])]
print('Parameter %s used' % (params[int(text[wc_counter + 1])]))
del text[wc_counter + 1]
param_counter+=1
wc_counter+=1
body_ws[segment.attrib['cell']] = ''.join(text)
if platform.system() == 'Windows':
body_ws[segment.attrib['cell']].font = self.header_font
body_ws[segment.attrib['cell']].fill = self.header_fill
body_ws[segment.attrib['cell']].border = self.header_border
body_ws[segment.attrib['cell']].alignment = self.header_alignment
body_ws[segment.attrib['cell']].number_format = self.header_number_format
print('Data Title element %s placed in cell %s' % (child.text, segment.attrib['cell']))
segment_counter+=1
elif child.tag == 'Header':
i=0
for column in child:
#Place the column header for each query column
cell = Utils.coordinate_from_string(segment.attrib['cell'])
col = Utils.column_index_from_string(cell[0])
body_ws['%s%s' % (Utils.get_column_letter(col+i), 1 + segment_counter)] = column.text
if platform.system() == 'Windows':
body_ws['%s%s' % (Utils.get_column_letter(col+i), 1 + segment_counter)].font = self.base_font
body_ws['%s%s' % (Utils.get_column_letter(col+i), 1 + segment_counter)].fill = self.base_fill
body_ws['%s%s' % (Utils.get_column_letter(col+i), 1 + segment_counter)].border = self.base_border
body_ws['%s%s' % (Utils.get_column_letter(col+i), 1 + segment_counter)].alignment = self.base_alignment
body_ws['%s%s' % (Utils.get_column_letter(col+i), 1 + segment_counter)].number_format = self.base_number_format
print('Data Header element %s placed in cell %s%s' % (column.text, Utils.get_column_letter(col+i), 2))
i+=1
segment_counter+=1
elif child.tag == 'Query':
#Execute the query and place the results into the page
if '?' not in child.text:
self.cur.execute(child.text)
#If a wildcard is encountered, we need to replace it with the
#correct parameter
else:
text = list(child.text)
wc_counter = 0
for i in text:
if i == '?':
text[wc_counter] = params[int(text[wc_counter + 1])]
print('Parameter %s used' % (params[int(text[wc_counter + 1])]))
del text[wc_counter + 1]
param_counter+=1
wc_counter+=1
self.cur.execute(''.join(text))
data = self.cur.fetchall()
print('query %s executed' % (child.text))
i=3
for row in data:
j=0
segment_counter+=1
#Place the data into the report
for e in row:
cell = Utils.coordinate_from_string(segment.attrib['cell'])
col = Utils.column_index_from_string(cell[0])
body_ws['%s%s' % (Utils.get_column_letter(col+j), i)] = e
if platform.system() == 'Windows':
body_ws['%s%s' % (Utils.get_column_letter(col+j), i)].font = self.base_font
body_ws['%s%s' % (Utils.get_column_letter(col+j), i)].fill = self.base_fill
body_ws['%s%s' % (Utils.get_column_letter(col+j), i)].border = self.base_border
body_ws['%s%s' % (Utils.get_column_letter(col+j), i)].alignment = self.base_alignment
body_ws['%s%s' % (Utils.get_column_letter(col+j), i)].number_format = self.base_number_format
print('Data Element %s placed in column %s%s' % (e, Utils.get_column_letter(col+i), j))
j+=1
i+=1
self.wb.save('Export.xlsx')
def Home(request,mid):
form = TestForm(request.POST or None)
if form.is_valid():
n = request.POST['bins']
s = request.POST['binint']
try:
freeze = request.POST['freeze']
except:
freeze = False
bins = []
if n:
k = 100/int(n)
for i in range(0,100,k):
bins.append(i)
if not (k*int(n)) == 100:
bins[-1] = 100
else:
bins += [100]
elif s:
a = s.split(',')
arr = map(int,a)
bins = [0]
bins +=arr
bins.append(100)
else:
raise Http404
print bins
m = Marksheet.objects.get(pk = mid)
wb = o.load_workbook(filename = m.f.url[1:],data_only=True)
#sheet = wb.get_sheet_by_name('Sheet1')
sheet = wb.active
marks = []
xy = coordinate_from_string(m.c) # returns ('A',4)
print xy
col = column_index_from_string(xy[0]) # returns 1
row = xy[1]
for i in range(row,row + m.n):
if sheet.cell(row = i,column = col).value:
val = int(sheet.cell(row = i,column = col).value)
marks.append(val)
else:
marks.append(0)
print "Before3 Plot"
print marks
if not freeze:
plt.hist(marks,bins,histtype= 'bar',rwidth='0.9',alpha = 0.7)
plt.xlabel('mark')
plt.ylabel('frequency')
r = randint(10000,99999)
plt.savefig('media/images/' + str(mid) + '.png')
plt.hold(False)
return HttpResponseRedirect('/media/images/' + str(mid) + '.png')
else:
grade = []
if not len(bins) == 10:
return render_to_response("error.html",{'mid':mid },context_instance = RequestContext(request))
for mark in marks:
if mark <= bins[1]:
grade.append('NC')
elif mark <= bins[2]:
grade.append('E')
elif mark <= bins[3]:
grade.append('D')
elif mark <= bins[4]:
grade.append('C-')
elif mark <= bins[5]:
grade.append('C')
elif mark <= bins[6]:
grade.append('B-')
elif mark <= bins[7]:
grade.append('B')
elif mark <= bins[8]:
grade.append('A-')
elif mark <= bins[9]:
grade.append('A')
print len(marks),len(grade)
sheet.cell(row=row-1,column=col+1,value= "Grades")
for i,g in enumerate(grade):
sheet.cell(row = row+i,column = col + 1, value = g)
wb.save("media/files/graded"+str(mid) + ".xlsx")
return HttpResponseRedirect('/media/files/graded'+str(mid) +'.xlsx')
context = {'form': form}
return render_to_response("home.html",context,context_instance = RequestContext(request))