Tired of Excel Charts? Create a Dynamic Dashboard with Just Three Lines of Python

Dashboard Layout

Top row: four KPI cards (total sales, order count, average order value, region count).

Middle left: bar chart comparing sales by region.

Middle right: line chart showing yearly sales trend.

Bottom left: donut pie chart of product‑category share.

Bottom right: table summarising regional sales.

Footer: gauge indicating performance‑target achievement rate.

Preparation

Install the required libraries with a single command:

pip install pandas plotly

Sample Data Generation (optional)

import pandas as pd
import numpy as np
# generate 200 rows of simulated sales data
np.random.seed(42)
dates = pd.date_range('2024-01-01', periods=100, freq='D')
regions = ['华东', '华南', '华北', '西部']
products = ['食品', '饮料', '电子', '服装']
data = {
    '日期': np.random.choice(dates, 200),
    '区域': np.random.choice(regions, 200),
    '产品类别': np.random.choice(products, 200),
    '销售额': np.random.randint(5000, 50000, 200)
}
df = pd.DataFrame(data)
df.to_excel('销售明细.xlsx', index=False)
print("示例数据已生成：销售明细.xlsx")

Main Program

import pandas as pd
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import webbrowser

# ---------- Read data ----------
df = pd.read_excel("销售明细.xlsx", sheet_name="Sheet1")
# Core metrics
总销售额 = df['销售额'].sum()
总订单数 = len(df)
平均客单价 = 总销售额 / 总订单数
区域数 = df['区域'].nunique()
# Aggregated data
区域汇总 = df.groupby('区域', as_index=False)['销售额'].sum()
月度趋势 = df.groupby('日期', as_index=False)['销售额'].sum()
产品占比 = df.groupby('产品类别', as_index=False)['销售额'].sum()

# ---------- Build layout ----------
fig = make_subplots(
    rows=4, cols=4,
    row_heights=[0.15, 0.4, 0.3, 0.15],
    column_widths=[0.25, 0.25, 0.25, 0.25],
    specs=[
        [{"type": "indicator"}, {"type": "indicator"}, {"type": "indicator"}, {"type": "indicator"}],
        [{"type": "bar", "colspan": 2}, None, {"type": "scatter", "colspan": 2}, None],
        [{"type": "pie", "colspan": 2}, None, {"type": "table", "colspan": 2}, None],
        [{"type": "indicator", "colspan": 4}, None, None, None]
    ],
    subplot_titles=("总销售额", "总订单数", "平均客单价", "覆盖区域",
                    "各区域销售额", "月度趋势", "产品占比", "销售明细",
                    "业绩达成率")
)

# ---------- Populate charts ----------
# KPI cards
fig.add_trace(go.Indicator(mode="number", value=总销售额,
                         number={'prefix':'¥','valueformat':',.0f'},
                         title="总销售额"), row=1, col=1)
fig.add_trace(go.Indicator(mode="number", value=总订单数, title="总订单数"), row=1, col=2)
fig.add_trace(go.Indicator(mode="number", value=平均客单价,
                         number={'prefix':'¥','valueformat':'.0f'},
                         title="平均客单价"), row=1, col=3)
fig.add_trace(go.Indicator(mode="number", value=区域数, title="覆盖区域"), row=1, col=4)

# Bar chart (regional sales)
fig.add_trace(go.Bar(x=区域汇总['区域'], y=区域汇总['销售额'], marker_color='#636EFA'), row=2, col=1)

# Line chart (monthly trend)
fig.add_trace(go.Scatter(x=月度趋势['日期'], y=月度趋势['销售额'],
                         mode='lines+markers', line_color='#EF553B'), row=2, col=3)

# Pie chart (product share)
fig.add_trace(go.Pie(labels=产品占比['产品类别'], values=产品占比['销售额'], hole=0.4), row=3, col=1)

# Table (regional summary)
fig.add_trace(go.Table(
    header=dict(values=['区域', '销售额'], fill_color='royalblue', font=dict(color='white')),
    cells=dict(values=[区域汇总['区域'], 区域汇总['销售额']], fill_color='lavender')
), row=3, col=3)

# Gauge (achievement rate)
目标销售额 = 总销售额 * 1.2  # assumed target is 1.2× actual sales
完成率 = 总销售额 / 目标销售额 * 100
fig.add_trace(go.Indicator(
    mode="gauge+number+delta",
    value=完成率,
    delta={'reference': 100},
    gauge={'axis': {'range': [0, 100]}, 'bar': {'color': "darkblue"}},
    title={'text': "业绩达成率 (%)"}
), row=4, col=1)

# Styling
fig.update_layout(
    template="plotly_dark",
    title_text="销售数据可视化大屏 · 2024年度",
    title_font_size=22,
    height=900,
    showlegend=False
)

# Save and open
output_file = "销售大屏.html"
fig.write_html(output_file)
webbrowser.open(output_file)
print(f"大屏已生成：{output_file}")

Customization

Replace the data source by editing pd.read_excel("销售明细.xlsx") to your file name.

Adjust column names (e.g., "区域", "销售额", "产品类别", "日期") to match your Excel headers.

Modify the target sales calculation (currently 目标销售额 = 总销售额 * 1.2) to a fixed value if required.

Packaging as Executable

Install PyInstaller: pip install pyinstaller Package the script: pyinstaller -F -w 数据大屏.py The command creates a single .exe in the dist folder. Placing the executable next to the Excel file allows double‑click execution without a Python environment.

FAQ

Q: The generated HTML shows no content on another computer. A: Send the 销售大屏.html file itself; it contains all data and works in any browser.

Q: My Excel has multiple sheets. A: Add sheet_name='YourSheetName' to pd.read_excel().

Q: I prefer a light theme. A: Replace template="plotly_dark" with template="plotly_white" in the layout.

Q: How to add more charts (e.g., maps, scatter). A: Modify the subplot layout and add additional fig.add_trace() calls.