def pegcharting(self, args, writer, dfname):
"""
query() to make different kind of advanced selections, by chain-query
the query result can continue excute a nother query directy
this is more flexible than pure python filtering, which stay on the
original dataframe
"""
print("--pegCharting {} \n".format(dfname))
contentSheet = writer.sheets["Contents"]
# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
# Create a chart object.
chart = workbook.add_chart({'type': 'line'})
# Configure the series of the chart from the dataframe data.
length = len(self._df.index)
category_column = 0
seriesNameRow = 0 # data series names
start_row = 1 # start from 0, skip first category
stop_row = length + 1 # include all rows, exclude first row
start_column = 1 # start from 0, skip DateTime
stop_column = len(self._df.columns) + 1 # start from 0, skip DateTime
sheetName = "pmCounter" + str(self.sheetLinkRow) # nick name to avoid 31 chars conflict
for c in range(start_column, stop_column):
chart.add_series({
'name': ['{}'.format(sheetName), seriesNameRow, c],
'categories': ['{}'.format(sheetName), start_row, category_column, stop_row, category_column],
'values': ['{}'.format(sheetName), start_row, c, stop_row, c]
})
# Insert the chart into the worksheet.
chart.set_title({'name': "{}".format(dfname)})
# http://stackoverflow.com/questions/30073845/in-python-xlsxwriter-how-do-you-set-x-axis-with-dates-to-be-a-text-axis
chart.set_x_axis({'name': 'rop', 'text_axis': True, 'date_axis': False})
chart_cell = xl_rowcol_to_cell(stop_row + 2, 2)
# chart_cell = xl_rowcol_to_cell(stop_row, stop_column+2)
worksheet = writer.sheets['{}'.format(sheetName)]
worksheet.insert_chart(chart_cell, chart)
link_format = writer.book.add_format({'color': 'blue', 'underline': 1})
link_text = "Contents List"
worksheet.write_url(xl_rowcol_to_cell(stop_row + 1, 0),
"internal:'{}'!B{}".format("Contents", self.sheetLinkRow + 1), link_format, link_text)
# chartSheet = workbook.add_chartsheet('{}_c'.format(sheetName))
# chartSheet.set_chart(chart)
# link_format = writer.book.add_format({'color':'blue', 'underline':1})
# contentSheet.write_url(xl_rowcol_to_cell(self.sheetLinkRow,0), "internal:'{}_c'!A1".format(sheetName), link_format, "{}_chart".format(dfname) )
# self.sheetLinkRow += 1
return
def pdfcharting(self, args, writer, dfname):
"""
"""
print("--pdfCharting {} \n".format(dfname))
# dfnameFull = dfname
# nameLen = len(dfnameFull)
# if nameLen > 31: #ugly excel limit
# dfname = dfnameFull[nameLen-31:] # use last 31 chars to avoid possilbe name conflit
# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
# Create a chart object.
chart = workbook.add_chart({'type': 'line'})
# Configure the series of the chart from the dataframe data.
length = len(self._df.index)
category_column = 0
seriesNameRow = 0 # data series names
start_row = 1 # start from 0, skip first category
stop_row = length + 1 # include all rows, exclude first row
start_column = 1 # start from 0, skip DateTime
stop_column = len(self._df.columns) + 1 # start from 0, skip DateTime
sheetName = "pmCounter" + str(self.sheetLinkRow) # nick name to avoid 31 chars conflict
for c in range(start_column, stop_column):
chart.add_series({
'name': ['{}'.format(sheetName), seriesNameRow, c],
'categories': ['{}'.format(sheetName), start_row, category_column, stop_row, category_column],
'values': ['{}'.format(sheetName), start_row, c, stop_row, c]
})
# Insert the chart into the worksheet.
chart.set_title({'name': "{}".format(dfname)})
# http://stackoverflow.com/questions/30073845/in-python-xlsxwriter-how-do-you-set-x-axis-with-dates-to-be-a-text-axis
chart_cell = xl_rowcol_to_cell(stop_row + 2, 2)
# chart_cell = xl_rowcol_to_cell(stop_row, stop_column+2)
worksheet = writer.sheets['{}'.format(sheetName)]
worksheet.insert_chart(chart_cell, chart)
link_format = writer.book.add_format({'color': 'blue', 'underline': 1})
link_text = "Contents List"
worksheet.write_url(xl_rowcol_to_cell(stop_row + 1, 0),
"internal:'{}'!B{}".format("Contents", self.sheetLinkRow + 1), link_format, link_text)
return
def CreateSummary( filename) :
print filename
P_count = 0
F_count = 0
T_count = 0
NE_count = 0
E_count = 0
rb = open_workbook(filename)
r_sheet = rb.sheet_by_index(0)
num_rows = r_sheet.nrows
num_cols = r_sheet.ncols
workbook = xlsxwriter.Workbook('ExecutionDetails.xlsx')
worksheet2 = workbook.add_worksheet("Summary")
worksheet = workbook.add_worksheet("Test Case")
bold = workbook.add_format({'bold': True})
color_format = workbook.add_format()
color_format.set_font_color('red')
c1_index = 0
while c1_index < num_cols:
cell_value = r_sheet.cell_value(0, c1_index)
if (cell_value == "Status"):
status_col = c1_index
c1_index += 1
r1_index = 1
while r1_index < num_rows:
cell_value = r_sheet.cell_value(r1_index,status_col)
if (cell_value == "PASS"):
P_count += 1
elif (cell_value == "FAIL"):
F_count += 1
else:
NE_count += 1
r1_index += 1
E_count =P_count + F_count
T_count = E_count + NE_count
#-------------------------------------------------------
r_index = 0
while r_index < num_rows:
c_index = 0
while c_index < num_cols:
cell_value = r_sheet.cell_value(r_index,c_index)
if ( r_index == 0 ):
worksheet.write(r_index,c_index,cell_value,bold)
else :
if (cell_value == 'FAIL'):
worksheet.write(r_index,c_index,cell_value,color_format)
else:
worksheet.write(r_index,c_index,cell_value)
c_index += 1
r_index += 1
headings = ['Status', 'Count']
data = [
['Pass', 'Fail', 'Not Executed', 'Total'],
[P_count, F_count, NE_count, T_count],
]
worksheet2.write_row('A1', headings, bold)
worksheet2.write_column('A2', data[0])
worksheet2.write_column('B2', data[1])
chart1 = workbook.add_chart({'type': 'pie'})
chart1.add_series({
'categories': ['Summary', 1, 0, 2, 0],
'values': ['Summary', 1, 1, 2, 1],
'line': {'color': 'red'},
})
chart1.set_title({'name': 'Execution Report'})
chart1.set_style(10)
worksheet2.insert_chart('C2', chart1, {'x_offset': 25, 'y_offset': 10})
workbook.close()
return
