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