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