python - 单个 Excel 中 Pandas Dataframe 的 Excel 样式和图表

Question

我有一个 Pandas 数据框，我需要将其写入 Excel，然后进行颜色格式化并在同一张纸上绘制图表。

我已经使用 StyleFrame 对我的 Dataframe 进行着色和边框，但此 StyleFrame 不适用于 Pandas XlsxWriter 对象。反过来，从这个样式的数据框中绘制图表不起作用。

谁能分享一下这个问题的解决方案吗？

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

如果您使用 openpyxl 包绘制图表会更好，因为据我所知，StyleFrame 使用 openpyxl 引擎，而 xlsxwriter 包不适用于现有文件。

此示例适用于 openpyxl 版本 2.2.5 和 StyleFrame 版本 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()

您可以找到here许多使用示例。