def _get_column_as_letter(self, sheet, column_to_convert, startcol=0):
if not isinstance(column_to_convert,
(int, str_type, unicode_type, Container)):
raise TypeError(
"column must be an index, column letter or column name")
column_as_letter = None
if column_to_convert in self.data_df.columns: # column name
column_index = self.data_df.columns.get_loc(
column_to_convert
) + startcol + 1 # worksheet columns index start from 1
column_as_letter = cell.get_column_letter(column_index)
# column index
elif isinstance(column_to_convert, int) and column_to_convert >= 1:
column_as_letter = cell.get_column_letter(startcol +
column_to_convert)
# assuming we got column letter
elif isinstance(column_to_convert,
(str_type, unicode_type)) < get_column_letter(
sheet.max_column):
column_as_letter = column_to_convert
if column_as_letter is None or column_as_letter > get_column_letter(
sheet.max_column):
raise IndexError("column: %s is out of columns range." %
column_to_convert)
return column_as_letter
def _generateTraceabilitySummary(sheet, reqMap, args):
''' Generates a summary sheet with summary details
for each module and overall summary'''
sheet.title = 'Summary'
sheet.column_dimensions['A'].width = 50
sheet.column_dimensions['B'].width = 30
sheet.column_dimensions['C'].width = 15
sheet.column_dimensions['D'].width = 3
sheet.column_dimensions['E'].width = 15
sheet.column_dimensions['F'].width = 3
sheet.column_dimensions['G'].width = 15
cellRow = 1
for moduleName in reqMap:
cellRow += TraceabilityGenerator._generateModuleSummary(
sheet, moduleName, cellRow, 1, args)
cellRow += 1
# set summary of module summaries
cell = sheet.cell(row=cellRow, column=1)
cell.value = 'Summary'
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT
cellRow += 1
moduleCol = 3
expectedCount = TraceabilityGenerator._getSummaryFormula(reqMap, 'A')
if (True == args.checkSrcLinks):
# set summary of source links
actualCount = TraceabilityGenerator._getSummaryFormula(
reqMap, get_column_letter(moduleCol))
TraceabilityGenerator._generateSummaryColSummary(
sheet, 'Source Links:', actualCount, expectedCount, cellRow, 2)
cellRow += 1
moduleCol += 1
if (True == args.checkTestLinks):
# set summary of test links
actualCount = TraceabilityGenerator._getSummaryFormula(
reqMap, get_column_letter(moduleCol))
TraceabilityGenerator._generateSummaryColSummary(
sheet, 'Test Links:', actualCount, expectedCount, cellRow, 2)
cellRow += 1
moduleCol += 1
def _generateSummaryColSummary(sheet, colName, countFormula, totalFormula,
rowOffset, colOffset):
''' Generates a column summary details for all the modules in the format
<summary_description> | <actual_value> / <expected_value> = <percent_value>'''
# set summary type
cell = sheet.cell(row=rowOffset, column=colOffset)
cell.value = colName
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT
# set actual summary count
cell = sheet.cell(row=rowOffset, column=colOffset + 1)
cell.value = countFormula
cell.font = TraceabilityGenerator.SUMMARY_FONT
alignment = copy(TraceabilityGenerator.SUMMARY_ALIGNMENT)
alignment.horizontal = 'right'
cell.alignment = alignment
cell = sheet.cell(row=rowOffset, column=colOffset + 2)
cell.value = '/'
font = copy(TraceabilityGenerator.SUMMARY_FONT)
font.bold = True
cell.font = font
cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT
# set expected summary count
cell = sheet.cell(row=rowOffset, column=colOffset + 3)
cell.value = totalFormula
cell.font = TraceabilityGenerator.SUMMARY_FONT
alignment = copy(TraceabilityGenerator.SUMMARY_ALIGNMENT)
alignment.horizontal = 'left'
cell.alignment = alignment
cell = sheet.cell(row=rowOffset, column=colOffset + 4)
cell.value = '='
font = copy(TraceabilityGenerator.SUMMARY_FONT)
font.bold = True
cell.font = font
cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT
# set percentage of actual/expected
cell = sheet.cell(row=rowOffset, column=(colOffset + 5))
cell.value = TraceabilityGenerator.PERCENT_FORMULA % (\
get_column_letter(colOffset+3), rowOffset,
get_column_letter(colOffset+1), rowOffset,
get_column_letter(colOffset+3), rowOffset)
cell.font = TraceabilityGenerator.SUMMARY_FONT
cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT
cell.number_format = '0.00%'
def _get_column_as_letter(self, sheet, column_to_convert, startcol=0):
col = column_to_convert.value if isinstance(
column_to_convert, Container) else column_to_convert
if not isinstance(col, (int, str)):
raise TypeError(
"column must be an index, column letter or column name")
column_as_letter = None
if col in self.data_df.columns: # column name
column_index = self.data_df.columns.get_loc(
col) + startcol + 1 # worksheet columns index start from 1
column_as_letter = cell.get_column_letter(column_index)
# column index
elif isinstance(col, int) and col >= 1:
column_as_letter = cell.get_column_letter(startcol + col)
# assuming we got column letter
elif isinstance(col,
str) and col <= get_column_letter(sheet.max_column):
column_as_letter = col
if column_as_letter is None or cell.column_index_from_string(
column_as_letter) > sheet.max_column:
raise IndexError("column: %s is out of columns range." %
column_to_convert)
return column_as_letter
def _generateModuleColSummary(sheet, moduleName, colName, countCol,
totalCol, rowOffset, colOffset):
''' Generates a column summary details for the specified module in the format
<summary_description> | <actual_value> / <expected_value> = <percent_value>'''
# set column summary type
cell = sheet.cell(row=rowOffset, column=colOffset)
cell.value = colName
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT
# set column actual count
cell = sheet.cell(row=rowOffset, column=(colOffset + 1))
cell.value = '=' + TraceabilityGenerator.COL_COUNT_FORMULA % (
moduleName, get_column_letter(countCol),
get_column_letter(countCol))
alignment = copy(TraceabilityGenerator.SUMMARY_ALIGNMENT)
alignment.horizontal = 'right'
cell.alignment = alignment
cell = sheet.cell(row=rowOffset, column=(colOffset + 2))
cell.value = '/'
font = copy(TraceabilityGenerator.SUMMARY_FONT)
font.bold = True
cell.font = font
cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT
# set column expected count
cell = sheet.cell(row=rowOffset, column=(colOffset + 3))
cell.value = '=' + TraceabilityGenerator.COL_COUNT_FORMULA % (
moduleName, get_column_letter(totalCol),
get_column_letter(totalCol))
cell.font = TraceabilityGenerator.SUMMARY_FONT
alignment = copy(TraceabilityGenerator.SUMMARY_ALIGNMENT)
alignment.horizontal = 'left'
cell.alignment = alignment
cell = sheet.cell(row=rowOffset, column=(colOffset + 4))
cell.value = '='
font = copy(TraceabilityGenerator.SUMMARY_FONT)
font.bold = True
cell.font = font
cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT
# set actual / expected percentage
cell = sheet.cell(row=rowOffset, column=(colOffset + 5))
cell.value = TraceabilityGenerator.PERCENT_FORMULA % (\
get_column_letter(colOffset+3), rowOffset,
get_column_letter(colOffset+1), rowOffset,
get_column_letter(colOffset+3), rowOffset)
cell.font = TraceabilityGenerator.SUMMARY_FONT
cell.alignment = TraceabilityGenerator.SUMMARY_ALIGNMENT
cell.number_format = '0.00%'
driver.quit()
print("Closing browser")
# paymentsNZ()
excel = "datafile.xlsx"
wb = Workbook()
ws = wb.active
f = open('PNZ.txt')
csv.register_dialect('slash', delimiter='\\')
reader = csv.reader(f, dialect='slash')
wb = Workbook()
ws = wb.worksheets[0]
for row_index, row in enumerate(reader):
for column_index, cell in enumerate(row):
column_letter = get_column_letter((column_index + 1))
ws['%s%s' % (column_letter, (row_index + 1))].value = cell
ws.insert_rows(1)
a = ws['A1'] = "MANUFACTURER & MODEL"
b = ws['B1'] = "PCI APPROVAL NUMBER"
c = ws['C1'] = "APPROVAL VERSION/CLASS"
d = ws['D1'] = "PAYMENTS NZ DATE OF NO NEW CONNECTION"
e = ws['E1'] = "PAYMENTS NZ SUNSET DATE"
wb.save(excel)
df = pd.read_excel(excel)
print("loading into pandas")
ndf = pd.DataFrame(df, columns=['MANUFACTURER & MODEL', 'PCI APPROVAL NUMBER', 'APPROVAL VERSION/CLASS',
'PAYMENTS NZ DATE OF NO NEW CONNECTION', 'PAYMENTS NZ SUNSET DATE'])
# NOME DOS CAMPOS
r1 = ['primeiro', 'segundo', 'terceiro', 'quarto', 'quinto']
# ESTILOS
headFont = openpyxl.styles.Font(name='Consolas', bold=True, color="434343")
headAlign = openpyxl.styles.Alignment(horizontal='center')
headFill = openpyxl.styles.PatternFill('solid', fgColor="f3f3f3")
thin = openpyxl.styles.Side(border_style="thin", color="000000")
headborder = openpyxl.styles.Border(top=thin,
left=thin,
right=thin,
bottom=thin)
# LOOP para cada coluna
for i in range(len(r1)):
# COORDENADAS
letter = get_column_letter(i + 1)
cellCoord = letter + str(1)
ws.column_dimensions[letter].width = 20 # LARGURA dos campos
ws[cellCoord].font = headFont
ws[cellCoord].alignment = headAlign
ws[cellCoord].fill = headFill
ws[cellCoord].border = headborder
ws[cellCoord] = r1[i]
wb.save(r'.\primeiro_xlsx.xlsx')
logger.info('input file: %s' % input_file)
logger.info('input file: %s' % output_file)
ws = wb.worksheets[0]
if args.sheet_name is not None:
ws.title = args.sheet_name
with open(input_file, 'r', encoding=args.encoding) as f:
dataReader = csv.reader(f)
line_num = 0
for line in dataReader:
line_num += 1
f.seek(0)
for cnt_row, line in enumerate(dataReader):
for cnt_col, word in enumerate(line):
if word.__len__() != 0 and word[0] == '=':
word = '\'' + word
ws[get_column_letter(cnt_col + 1).__str__() +
(cnt_row + 1).__str__()] = word
if cnt_row % 1000 == 0:
logger.debug(cnt_row.__str__() + '/' +
line_num.__str__() + ' lines were converted.')
logger.debug('saving...')
wb.save(filename=output_file)
logger.info('done.')
def _generateTraceabilitySheet(workbook, moduleName, module, args):
''' Generates a sheet with all the requirements, requirements
details, and requirement links for the specified module'''
# create sheet for module
moduleSheet = workbook.create_sheet(title=moduleName)
moduleSheet.fill = TraceabilityGenerator.REQ_MET_FILL
# set column dimensions for requirement name
moduleSheet.column_dimensions['A'].width = 30
# set column dimensions for requirement text
moduleSheet.column_dimensions['B'].width = 70
# set header and requirement text as fixed
moduleSheet.freeze_panes = 'B2'
# set column for requirement satisfaction
moduleSheet.column_dimensions['C'].hidden = True
cellRow = 1
# setup header
cellCol = 1
# set start column for conditional formatting
startCol = get_column_letter(cellCol)
# add requirement name column
cell = moduleSheet.cell(row=cellRow, column=cellCol)
cell.value = 'Requirement Name'
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT
cellCol += 1
# add requirement text column
cell = moduleSheet.cell(row=cellRow, column=cellCol)
cell.value = 'Requirement Text'
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT
cellCol += 1
areReqLinksChecked = \
(True == args.checkSrcLinks) or \
(True == args.checkTestLinks)
if (True == areReqLinksChecked):
# add requirement satisfied column
cell = moduleSheet.cell(row=cellRow, column=cellCol)
cell.value = 'SATISFIED'
cellCol += 1
if (True == args.checkSrcLinks):
# set column dimension for test links
moduleSheet.column_dimensions[get_column_letter(
cellCol)].width = 75
cell = moduleSheet.cell(row=cellRow, column=cellCol)
cell.value = 'Source Code Links'
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT
cellCol += 1
if (True == args.checkTestLinks):
# set column dimension for test links
moduleSheet.column_dimensions[get_column_letter(
cellCol)].width = 75
cell = moduleSheet.cell(row=cellRow, column=cellCol)
cell.value = 'Test Links'
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT
cellCol += 1
# get end column for conditional formatting
endCol = get_column_letter(cellCol - 1)
cellRow += 1
# add row for each requirement in module
for req, reqValue in six.iteritems(module):
rowCol = 1
# requirement name
cell = moduleSheet.cell(row=cellRow, column=rowCol)
cell.value = req
cell.font = TraceabilityGenerator.HEADER_FONT
cell.alignment = TraceabilityGenerator.HEADER_ALIGNMENT
rowCol += 1
# requirement text
cell = moduleSheet.cell(row=cellRow, column=rowCol)
cell.value = reqValue.reqText
cell.font = TraceabilityGenerator.CELL_FONT
cell.alignment = TraceabilityGenerator.CELL_ALIGNMENT
rowCol += 1
if (True == areReqLinksChecked):
# requirement satisfied
reqMetCell = moduleSheet.cell(row=cellRow, column=rowCol)
reqMetFormula = 'False'
rowCol += 1
# TODO add requirements as hyperlinks
'=HYPERLINK(<URL>, <text>)'
if (True == args.checkSrcLinks):
# add source links
linksText = ''
for link in reqValue.reqLinks:
if (tLinkType.LINK_TYPE__SRC == link.linkType):
linksText += link.linkName
if ((link.linkFile is not None)
and (link.linkFileLineNum is not None)):
if (True == args.basename):
linksText += ' - (%s line %s)' % (
os.path.basename(link.linkFile),
link.linkFileLineNum)
else:
linksText += ' - (%s line %s)' % (
link.linkFile, link.linkFileLineNum)
linksText += '\n'
cell = moduleSheet.cell(row=cellRow, column=rowCol)
cell.value = linksText
cell.font = TraceabilityGenerator.CELL_FONT
cell.alignment = TraceabilityGenerator.CELL_ALIGNMENT
reqMetFormula = TraceabilityGenerator.REQ_IS_MET_FORMULA % (
reqMetFormula, get_column_letter(rowCol), cellRow)
rowCol += 1
if (True == args.checkTestLinks):
# add test links
linksText = ''
for link in reqValue.reqLinks:
if (tLinkType.LINK_TYPE__TEST == link.linkType):
linksText += link.linkName + '\n'
cell = moduleSheet.cell(row=cellRow, column=rowCol)
cell.value = linksText
cell.font = TraceabilityGenerator.CELL_FONT
cell.alignment = TraceabilityGenerator.CELL_ALIGNMENT
reqMetFormula = TraceabilityGenerator.REQ_IS_MET_FORMULA % (
reqMetFormula, get_column_letter(rowCol), cellRow)
rowCol += 1
# set requirement met column value
reqMetCell.value = '=IF(NOT(%s), "PASS", "FAIL")' % (
reqMetFormula)
# set conditional formatting for if requirement is met
moduleSheet.conditional_formatting.add(
'%s%d:%s%d' % (startCol, cellRow, endCol, cellRow),
formatting.rule.FormulaRule(
formula=[reqMetFormula],
stopIfTrue=True,
fill=TraceabilityGenerator.REQ_NOT_MET_FILL))
cellRow += 1
BASE_DIR = os.path.curdir + os.path.sep + '12134testFile'
if len(sys.argv) > 1:
try:
wb = openpyxl.load_workbook(sys.argv[1])
except Exception as err:
print('[-]Error: %s' % err)
sys.exit()
try:
ws = wb.active
try:
os.mkdir(BASE_DIR)
except FileExistsError:
pass
for x in range(1, ws.max_column + 1):
with open(os.path.join(BASE_DIR, '%s.txt' % get_column_letter(x)),
'w') as fio:
for y in range(1, ws.max_row + 1):
v = ws.cell(y, x).value
fio.write('%s\n' % str('' if v is None else v))
finally:
wb.close()
print('[+]Done')
else:
print(__doc__)
from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.cell.cell import get_column_letter
wb = Workbook()
dest_filename = 'empty_book2.xlsx'
ws1 = wb.active # 第一个表
ws1.title = "range names" # 第一个表命名
# 遍历第一个表的1到40行，赋值一个600内的随机数
for row in range(1, 40):
ws1.append(range(60))
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
for col in range(27, 54):
_ = ws3.cell(column=col, row=row, value="%s" % get_column_letter(col))
wb.save(filename=dest_filename)
def within_sheet_boundaries(row=1, column='A'):
return (1 <= int(row) <= sheet.max_row
and 'A' <= column <= get_column_letter(sheet.max_column))
def load_network_from_XLSX(file):
"""
This method will parse a file .xls_ to create a network.
:param file: the path of the file containing the network. Must be of ".xlsx" extension.
:return: network: the one loaded from the Excel file.
"""
from openpyxl import load_workbook
from openpyxl.cell import cell
network = Network()
# Select a workbook
workbook = load_workbook(file, data_only=True)
worksheet = workbook.worksheets[0]
# Loading from file: we will iterate over each row/column, considering the XL file as a matrix
# And then creating nodes to add them to the network.
# Row Counter
i = 0
# Values of all the rows
full_nodes = []
row_count = worksheet.max_row
col_count = worksheet.max_column
max_col = cell.get_column_letter(col_count)
range = "A1:"+max_col+str(row_count)
# Iterate over XL file
for rowOfCellObjects in worksheet[range]:
i += 1
# Column Counter
j = 0
# Values of the current row
row_node = []
for cellObj in rowOfCellObjects:
j += 1
# If we are on the first row - Which means we will be reading column headers, we pass to the next row
if i == 1 and j == 1:
break
row_node.append(cellObj.value)
if i != 1 and j != 1:
# if not, we add the row to the stack that contains our XL rows
full_nodes.append(row_node)
# Now that we've had retrieved the data into an array, we will iterate over it to create a full network
# First, we instanciate a new Bracket
for node_in_file in full_nodes:
node = NetworkNode()
branch = NetworkBranch()
user = NetworkUser([1,1,1], None, [0,0,0])
# We then set its bracket's ID.
node.set_id(int(node_in_file[0][5:]))
# Same as the parent.
node.set_parent_id(int(node_in_file[1]))
# Now, we're on to create the Branch.
# First, we're setting the phases that's on the branch.
branch.set_phases(node_in_file[2:5])
# Then, the representation types (For later, that's why it's in comment)
# bracket.set_types(node_in_file[5:8])
# Setting the length
branch.set_length(node_in_file[8])
# Finally, we instanciate R and X: vectors of floating point numbers.
# Their structure is as follows
# ## Note that we have 4 values for each possible phases: A, B, C and N.
# R = [RA, RB, RC, RD]
# Examples: RA = [R_AA, R_AB, R_AC, R_AN]
import math
RA = [node_in_file[9], node_in_file[15], node_in_file[17], math.inf]
RB = [node_in_file[15], node_in_file[11], node_in_file[19], math.inf]
RC = [node_in_file[17], node_in_file[19], node_in_file[13], math.inf]
RN = [math.inf, math.inf, math.inf, math.inf]
branch.set_R([RA, RB, RC, RN])
# Same as for R
XA = [node_in_file[10], node_in_file[16], node_in_file[18], math.inf]
XB = [node_in_file[16], node_in_file[12], node_in_file[20], math.inf]
XC = [node_in_file[18], node_in_file[20], node_in_file[14], math.inf]
XN = [math.inf, math.inf, math.inf, math.inf]
branch.set_X([XA, XB, XC, XN])
"""bracket.set_index(node_in_file[21:24])
bracket.set_EAN(node_in_file[24:27])
bracket.set_EAN(node_in_file[27:29])"""
# Now we are setting the Power that circulates through the branch.
pTest = node_in_file[30]
powers = []
powers.append(pTest)
user.set_P(powers)
node.set_users([user, user, user])
if node_in_file[1] != 0:
node.set_parent(network.find_node(node_in_file[1]))
bracket = Bracket(node, branch)
network.add_bracket(bracket)
# if we are not on the first real node of the network
if bracket.get_node().get_id() != 1:
network.set_parent_of_bracket(bracket, node.get_parent_id()-1)
# Return the network created from the XL file.
return network
def __xl__codebook(self):
blocks = next(
filter(lambda x: x.get('Element') == 'BL', self.SurveyElements))
payload = blocks.Payload
block_items = filter(
lambda x: x.get('Type') in ("Default", "Standard"),
payload if isinstance(payload, list) else payload.values())
trash = next(
filter(lambda x: x.get('Type') == 'Trash',
payload if isinstance(payload, list) else payload.values()))
trash_ids = [
x.QuestionID for x in trash.BlockElements
if hasattr(x, 'QuestionID')
]
wb = openpyxl.Workbook()
for i, block in enumerate(block_items):
ws = wb.active if i == 0 else wb.create_sheet()
title = self._sanitize_for_spss_(block.Description)
title = title if len(title) < 31 else title[:30]
ws.title = title
for key, value in self.WIDTHS.items():
ws.column_dimensions[key].width = value
ws.append(self.COL_HEADERS)
questions = filter(
lambda x: x.Type == "Question" and x.QuestionID not in
trash_ids, block.BlockElements)
for q in questions:
question_id = q.QuestionID
question = next(
filter(
lambda x: x.Element == 'SQ' and x.Payload.QuestionID ==
question_id, self.SurveyElements))
try:
for row in question.variable_info():
ws.append(row)
except Exception as err:
print(
f"Unable to insert data for question {question_id}. Error was \n{err}"
)
# Make a table
"""
max_row = ws.max_row
if max_row > 2:
tbl = Table(displayName=title, ref=f"A1:D{max_row}")
tbl.tableStyleInfo = self.TBL_STYLE
ws.add_table(tbl)
"""
# Styles have to be set on cells one at a time or they won't take
for row_index, row in enumerate(ws.iter_rows(), 1):
for column_index, cell in enumerate(row, 1):
column_letter = get_column_letter(column_index)
ws.cell(row_index,
column_index).alignment = self.ALIGNMENTS.get(
column_letter, openpyxl.styles.Alignment())
return wb