Excel Styles and Chart from Pandas Dataframe in Single Excel

Question

I am having a Pandas dataframe, which I need to write it to an excel and then do color formating and plot a chart on the same sheet.

I have used StyleFrame to do the Coloring & Borders to my Dataframe, but this StyleFrame not works with Pandas XlsxWriter Object. And in turn, Plotting of chart from this styled dataframe is not working.

Can any one please share the solution for this!?

import pandas as pd
import xlfunc
import genfunc
from StyleFrame import StyleFrame, Styler, utils

def applystyle(df):
    sf=StyleFrame(df)

    sf.apply_column_style(cols_to_style=df.columns,
                          styler_obj=Styler(bg_color=utils.colors.white, bold=True, font=utils.fonts.calibri,
                                            font_size=8), style_header=True)
    sf.apply_headers_style(
        styler_obj=Styler(bg_color=utils.colors.blue, bold=True, font_size=8, font_color=utils.colors.white,
                          number_format=utils.number_formats.general, protection=False))

    sf.set_column_width(columns=sf.columns, width=15)
    sf.set_row_height(rows=sf.row_indexes, height=12)
    return sf


def createchart(regionname,workbook,outxl,sheetname,cellid,charttitle,startrow,startcol,endrow,endcol):

    worksheet = outxl.sheets[sheetname]
    kpichart = workbook.add_chart({'type': 'line'})
    bsckpi = workbook.add_chart({'type': 'column'})

    for col_num in range(startcol+1, endcol-1):
        kpichart.set_title(
            {'name': charttitle, 'name_font': {'name': 'Cambria', 'size': 18, 'color': '#000000'}})
        kpichart.add_series({
            'name': [sheetname, startrow, col_num],
            'categories': [sheetname, startrow+1, 1, endrow, 1],
            'values': [sheetname, startrow+1, col_num, endrow, col_num],
        })
    nodekpi.add_series({
        'name': [sheetname, startrow, endcol-1],
        'categories': [sheetname,startrow+1,1,endrow,1],
        'values': [sheetname,startrow+1,endcol-1,endrow,endcol-1],
        'y2_axis': True,
    })
    kpichart.combine(nodekpi)
    kpichart.set_x_axis({'name': 'Date'})
    kpichart.set_x_axis({'num_font': {'name': 'Cambria', 'size': 10, 'color': '#000000'}})
    kpichart.set_y_axis({'name': charttitle, 'minor_gridlines': {'visible': True, 'color': '#FFFFFF'}})
    kpichart.set_y_axis({'num_font': {'name': 'Cambria', 'size': 10, 'color': '#000000'}})

    worksheet.insert_chart(cellid, kpichart, {'x_scale': 1.75, 'y_scale': 1.25})


def df_to_xl(regionname,hostlist,timerdf,hostData):

    outxl = pd.ExcelWriter("timer_audit_data.xlsx", engine='xlsxwriter')
    stylexl = StyleFrame.ExcelWriter("timer_audit_data_styled.xlsx")
    outxl.sheets.clear()
    workbook = outxl.book
    stylebook = stylexl.book

    style_timerdf = applystyle(timerdf)
    style_timerdf.to_excel(stylexl, sheet_name='Parameters', index=False)


    timerdfnew = timerdf

    # Pivot
    timer_summary = timerdfnew.pivot_table(values='NODE', index='HOST', columns='timer', aggfunc="count")
    timer_summary = applystyle(timer_summary)
    timer_summary.to_excel(stylexl, sheet_name='Parameters', startrow=0, startcol=15)

    for hostname in hostlist:
        hostname = genfunc.toString(hostname)
        hostData.to_excel(outxl,sheet_name=hostname,startrow=1,startcol=1,index=False)        
        createchart(regionname,workbook,outxl,hostname,'G2',"Timer Performance"+hostname,1,1,tr+1,tc+1)
        #In this Section I am pasting the dataframe to excel workbook & I am preparing a chart in the same sheet
        #But here i would like to apply some colors and borders to hostData pasted in excel and then I want to prepare chart in the same sheet

    outxl.save()
    stylexl.save()

Answer 1

It would be much better if you draw your chart using openpyxl package since StyleFrame uses openpyxl engine and xlsxwriter package does not work with existing files as far as I know.

This example works for me with openpyxl version 2.2.5 and StyleFrame version 1.2

import pandas as pd
from openpyxl.charts import BarChart, Reference, Series
from StyleFrame import StyleFrame, Styler, utils


def apply_style(sf):
    sf.apply_headers_style(styler_obj=Styler(bg_color=utils.colors.black, font_color=utils.colors.white, bold=True))
    sf.apply_column_style(cols_to_style=sf.columns, styler_obj=Styler(font_color=utils.colors.blue))

    return sf


def draw_chart_openpyxl(worksheet):
    # pos: (row, column)
    chart = BarChart()
    labels = Reference(worksheet, pos1=(2, 1), pos2=(4, 1))

    valuesA = Reference(worksheet, pos1=(2, 2), pos2=(4, 2))
    seriesA = Series(valuesA, title='A', labels=labels)
    chart.append(seriesA)

    valuesB = Reference(worksheet, pos1=(2, 3), pos2=(4, 3))
    seriesB = Series(valuesB, title='B', labels=labels)
    chart.append(seriesB)

    chart.drawing.top = 100
    chart.drawing.left = 200
    chart.drawing.width = 300
    chart.drawing.height = 200
    worksheet.add_chart(chart)
    return worksheet


if __name__ == '__main__':
    ew = StyleFrame.ExcelWriter('output.xlsx')
    df = pd.DataFrame({
        'A': [1, 2, 3],
        'B': [4, 5, 6]
    }, columns=['A', 'B'], index=['Row 1', 'Row 2', 'Row 3'])

    sf = StyleFrame(df)
    sf = apply_style(sf)
    sf.to_excel(excel_writer=ew, sheet_name='Sheet1')

    draw_chart_openpyxl(ew.book.get_sheet_by_name('Sheet1'))
    ew.save()

You can find here many usage examples.