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
