In the world of data analysis and visualization, Excel has long been a go-to tool for many professionals. However, as data sets grow larger and more complex, the limitations of Excel become apparent. Python, with its powerful libraries like pandas, openpyxl, and Dash, offers a compelling alternative for automating tasks, enhancing visualizations, and creating interactive dashboards.

This article explores how Python can be used to enhance Excel automation, focusing on styling, color, and function. We’ll delve into how Python can be leveraged to create visually appealing Excel sheets with custom styles and colors. Additionally, we’ll demonstrate how to use Python to automate tasks such as data extraction, transformation, and visualization, culminating in the creation of a live Dash app that interacts with an Excel data source.

By the end of this article, you’ll have a solid understanding of how Python can be integrated with Excel to streamline workflows, add aesthetic appeal, and create dynamic, interactive dashboards.

Install packages

Importing the required packages.

#import libraries and give them an alias for ease of use
import pandas as pd 
import plotly.express as px 
import plotly.io as pio

Let us now read the data set that I have already created Book 1.xlsx you can create your own

#reading the dataset and storing in a variable df
df = pd.read_excel('Book1.xlsx')

Now let’s perform some operations on the above sheet. You see i have kept the total sales column empty. So how to calculate that with python

# Convert 'Units Sold' and 'Price per Unit' columns to numeric
df['Unit Sold'] = pd.to_numeric(df['Unit Sold'], errors='coerce')
df['Price per Unit'] = pd.to_numeric(df['Price per Unit'], errors='coerce')

# calculating the total sales and adding a new column for the same
df['Total Sales'] = df['Unit Sold'] * df['Price per Unit']

Now Let’s Plot the Chart:

# Create a bar chart of Product vs Total Sales
fig = px.bar(df, x='Product Name', y='Total Sales', title='Product Sales')
# Set the border and background color of the chart area
fig.update_layout(
    plot_bgcolor='white',
    paper_bgcolor='lightgray',
    width=800,
    height=500,
    shapes=[dict(type='rect', xref='paper',
            yref='paper',
            x0=0,
            y0=0,
            x1=1,
            y1=1,
            line=dict(
                color='black',
                width=2,
            ),
        )
    ]
)
# Display the graph
fig.show()
# Alternatively you can save the bar graph to an image using below line of code
pio.write_image(fig, 'bar_graph.png')

Now let’s generate the report:

import pandas as pd 
import plotly.express as px
import plotly.io as pio
from openpyxl import load_workbook
from openpyxl.drawing.image import Image

# Reading the dataset and storing it in a variable df
df = pd.read_excel('Book1.xlsx')

# Convert 'Unit Sold' and 'Price per Unit' columns to numeric
df['Unit Sold'] = pd.to_numeric(df['Unit Sold'], errors='coerce')
df['Price per Unit'] = pd.to_numeric(df['Price per Unit'], errors='coerce')

# Calculating the total sales and adding a new column for the same
df['Total Sales'] = df['Unit Sold'] * df['Price per Unit']

# Create a bar chart of Product vs Total Sales
fig = px.bar(df, x='Product Name', y='Total Sales', title='Product Sales')
# Set the border and background color of the chart area
fig.update_layout(
    plot_bgcolor='white',
    paper_bgcolor='lightgray',
    width=800,
    height=500,
    shapes=[dict(type='rect', xref='paper',
            yref='paper',
            x0=0,
            y0=0,
            x1=1,
            y1=1,
            line=dict(
                color='black',
                width=2,
            ),
        )
    ]
)
# Save the bar chart to a temporary image file
chart_image_path = 'bar_graph.png'
pio.write_image(fig, chart_image_path)

# Load the existing Excel file
wb = load_workbook('Book1.xlsx')
ws = wb.active

# Add the chart image to the Excel sheet
img = Image(chart_image_path)
ws.add_image(img, 'E1')

# Save the updated Excel file
wb.save('Book1.xlsx')

Note: Don’t forget pip install Kaleido to run the above code

You can Automate alignment to your Excel sheet

# set the width of all columns to 20
for col in ws.columns: 
    ws.column_dimensions[col[0].column_letter].width = 20

# Set the text in cells left-aligned
for row in ws.iter_rows(min_row=1, max_row=6, min_col=1, max_col=ws.max_column): 
     for cell in row: 
         cell.alignment = Alignment(horizontal='left')

# save the modified workbook
wb.save('Book1.xlsx')

The above code will make changes to your header:

Adding Borders:

# create a new workbook and select the active worksheet
wb = load_workbook('Book1.xlsx') 
ws = wb.active

# set a border around a specific range of cells
rrange_border = Border(left=Side(style='medium'),     
                       right=Side(style='medium'), 
                       top=Side(style='medium'),  
                       bottom=Side(style='medium'))

for row in ws.iter_rows(min_row=1, max_row=6, min_col=1, max_col=ws.max_column): 
       for cell in row: 
         cell.border = range_border

# save the workbook to a new file
wb.save('Book1.xlsx')

Now if you wish to Highlight the Column:

# define the fill pattern to highlight the column
fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

# highlight the first column of the sheet
for cell in ws[ws.max_column]: 
    cell.fill = fill

# save the modified workbook to a new Excel file
wb.save('Book1.xlsx')

If You Want to Add Some Styling to the Font of the Header:

# set font styles for first row
font = Font(name='Times New Roman', bold=True)

# set background color for the first row
fill = PatternFill(start_color='23C4ED', end_color='23C4ED', fill_type='solid') 
for row in ws.iter_rows(min_row=1, max_row=1): 
    if row[0].row == 1: 
       for cell in row: 
           cell.fill = fill 
           cell.font = font

# save the workbook
wb.save('Book1.xlsx')

import pandas as pd
import plotly.express as px
import plotly.io as pio
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Font

#Function for automating the spreadsheet
def automate_excel_report(inputFilename, outputFilename):
    df = pd.read_excel(inputFilename)

    # Add a new column for Total Sales
    df['Total Sales'] = df['Unit Sold'] * df['Price per Unit']

    # Create a bar graph of units sold using Plotly
    fig = px.bar(df, x='Product Name', y='Total Sales', title='Product Sales')
    # set the border and background color of the chart area
    fig.update_layout(
        plot_bgcolor='white',
        paper_bgcolor='lightgray',

        shapes=[dict(
                type='rect',
                xref='paper',
                yref='paper',
                x0=0,
                y0=0,
                x1=1,
                y1=1,
                line=dict(color='black', width=2))])

    # Save the bar graph as an image file
    pio.write_image(fig, 'bar_graph.png')
    writer = pd.ExcelWriter(outputFilename, engine='xlsxwriter')
    df.to_excel(writer, index=False, sheet_name='Sales Data')
    worksheet = writer.sheets['Sales Data']
    worksheet.set_column('F:F', 12)
    worksheet.insert_image('H1', 'bar_graph.png')
    # save the file
    writer.close()

#Function for automating the reports
def automate_excel_formatting(inputFilename, outputFilename):
    # # load the Excel file
    wb = load_workbook(inputFilename)
    ws = wb.active

    # # define the fill pattern to highlight the column
    fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

    # highlight the 'Product' column of the sheet
    for cell in ws['A:A']:
        cell.fill = fill

    # =========================Borders======================
    # set a border around a specific range of cells
    range_border = Border(left=Side(style='medium'), 
                        right=Side(style='medium'), 
                        top=Side(style='medium'), 
                        bottom=Side(style='medium'))

    for row in ws.iter_rows(min_row=1, max_row=6, min_col=1, max_col=ws.max_column):
        for cell in row:
            cell.border = range_border

    # =====================column alignmnet====================
    # # set the width of all columns to 20
    for col in ws.columns:
        ws.column_dimensions[col[0].column_letter].width = 20

    # add some text to the worksheet and set it left-aligned
    for row in ws.iter_rows(min_row=1, max_row=6, min_col=1, max_col=ws.max_column):
        for cell in row:
            # cell.value = f'Text {cell.column}{cell.row}'
            cell.alignment = Alignment(horizontal='left')

    #=======================font styles===============
    # set font styles for different rows
    font_name = Font(name='Times New Roman', bold=True)

    # set background color for the first row
    fill = PatternFill(start_color='23C4ED', end_color='23C4ED', fill_type='solid')
    for row in ws.iter_rows(min_row=1, max_row=1):
        if row[0].row == 1:
            for cell in row:
                cell.fill = fill
                cell.font = font_name

    # save the workbook 
    wb.close(outputFilename)

#Function for automating the pivot table
def automate_excel_pivot_table(inputFilename, outputFilename):
    # Load the Excel file into a pandas DataFrame
    # df = pd.read_excel("pivot_dataset.xlsx")
    df = pd.read_excel(inputFilename)

    # Create a pivot table
    pivot_df = pd.pivot_table(df, values='Total',
                              index='Gender', columns='Payment', aggfunc='sum')

    # Export the pivot table to an Excel file
    pivot_df.to_excel(outputFilename, sheet_name='Sheet1', index=True)

    # Create a Plotly figure
    fig = px.imshow(pivot_df)

    # Show the figure
    fig.write_image('fig.png')

The above Code also creates a Pivot Tabel

Live Dash App:

Create an Excel Sheet:

# Import all the required packages
from dash import Dash, html, dcc, Output, Input
import plotly.express as px
import pandas as pd

# Read the dataset
df = pd.read_excel("Book1.xlsx")

# Instantiate Dash
app = Dash(__name__)

# Define the web layout
app.layout = html.Div([
    html.H1("Excel to Python App"),
    dcc.RadioItems(id='col-choice', options=['Gender', 'Customer type', 'City'], value='Gender'),
    dcc.Graph(id='our-graph', figure={}),
])

# Define callback to update the graph based on user input
@app.callback(
    Output('our-graph', 'figure'),
    Input('col-choice', 'value')
)
def update_graphs(column_selected):
    pivot_df = pd.pivot_table(df, values='Total', index=column_selected, columns='Payment', aggfunc='sum')
    fig = px.imshow(pivot_df)
    return fig

# Run the app
if __name__ == '__main__':
    app.run_server()

In conclusion, Python offers a versatile and powerful set of tools for enhancing Excel automation, especially in terms of styling, color, function, and live dashboards. By leveraging libraries such as pandas, openpyxl, and Dash, Python can transform static Excel sheets into dynamic, visually appealing, and interactive data visualizations.

Styling and color customization allow users to create Excel sheets that are not only informative but also visually engaging. Python’s ability to automate tasks such as data extraction, transformation, and visualization streamlines workflows and improves efficiency.

Furthermore, the integration of Dash enables the creation of live, interactive dashboards that can directly interact with Excel data. This opens up a world of possibilities for creating dynamic reports and presentations that can be easily shared and updated.

Overall, Python’s integration with Excel offers a powerful combination of automation and customization, making it a valuable tool for anyone looking to enhance their Excel-based data analysis and visualization workflows.