AI-Powered Excel Dashboard Generator

Turn any Excel file into an interactive dashboard using Gemini AI.

Introduction

30 Second Summary

Every team has that spreadsheet with hundreds of rows of sales data, survey responses, or project metrics that nobody has turned into charts yet. The data is there, but the dashboard is not.

In this project, you will build a web app that turns any Excel file into a professional, interactive dashboard in seconds. You upload a file, click one button, and AI decides which charts and summary metrics best represent your data.

What You'll Build

You drag an Excel file into your browser, click one button, and a polished dashboard appears with summary cards across the top, interactive charts arranged in a two-column grid, and a theme selector to restyle everything at once.

By the end of this project, you'll have:

  • A web app that accepts any Excel file and displays it as a searchable, sortable table with row count, column count, and missing-value metrics above it.
  • An AI-powered dashboard generator that sends your data schema to Google Gemini, which returns a structured JSON blueprint and renders it as KPI cards and interactive Plotly charts with no manual column picking or chart configuration.
  • Theme switching and one-click export that lets you restyle every chart from a single dropdown and download the finished dashboard as a self-contained HTML file or individual charts as PDFs.
  • An optional Secret Mission to push your skills further.

Are there any prerequisites?

You'll need Python installed on your machine, a Google account for the free Gemini API key, and a code editor like VS Code or Cursor.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Set Up Your Environment

Every tool in this project has a specific job. Streamlit builds the web interface. Plotly renders interactive charts. The Gemini SDK connects your app to Google's AI.

In this step, you'll install all the Python packages, grab a free API key from Google, and confirm everything works by running a starter app.

In this step, get ready to:
  • Install the Python packages for your web app, charts, AI integration, and data export.
  • Create a free Gemini API key from Google AI Studio.
  • Build and run a starter Streamlit app to confirm your setup works.
Create your project folder and install packages

Before writing any code, you need a dedicated folder for the project and five Python packages installed on your machine. Without these packages, nothing runs.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Terminal (macOS) or PowerShell (Windows) and press Enter to open it.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir ai-dashboard
cd ai-dashboard
  • Confirm you're inside the new folder by running:
pwd

You should see a path ending in ai-dashboard.

Seeing 'file exists' or a similar error?

If you already have an ai-dashboard folder on your Desktop from a previous attempt, delete it first or choose a different folder name.

help me fix a 'file exists' error when creating my project folder

  • Install the five Python packages your app needs by running this command:
pip install streamlit plotly google-genai openpyxl kaleido

What are these packages?

  • streamlit builds your interactive web interface.
  • plotly renders charts with hover, zoom, and pan.
  • google-genai connects to Google's Gemini AI.
  • openpyxl reads Excel files into Python.
  • kaleido exports charts as images and PDFs.
  • Look at the end of the terminal output. You should see Successfully installed followed by a list of package names.

Seeing 'command not found' for pip?

On some systems, the command is pip3 instead of pip. Try running the install command again with pip3 at the start.

If neither works, make sure Python is installed by running python3 --version. Streamlit requires Python 3.10 or higher.

help me fix pip installation issues for my Python project

Get your Gemini API key

Your app needs an API key to talk to Gemini. Google offers a free tier with no credit card required, so you can get started in under a minute.

  • Open Google AI Studio in your browser.
  • Sign in with your Google account.
  • Click Create API key.
  • Copy the key and save it somewhere safe, like a notes app or a text file on your Desktop. You'll paste it into the app later.

Why is this free?

Google's Gemini API has a free tier that requires no credit card. Your app only sends column names, data types, and a few sample rows to the AI. It never sends the full dataset, so each request stays well under the free tier limits.

Keep your key private

Never share your API key publicly or commit it to a Git repository. Treat it like a password. You'll enter it directly in the app's sidebar each time you run it.

Create the starter app and run Streamlit

With your packages installed and API key saved, it's time to create the app file and make sure Streamlit runs correctly.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type the name of your code editor (for example, VS Code or Cursor) and press Enter to open it.
  • Click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop, select the ai-dashboard folder, and click Open.
  • Create a new file called app.py by clicking the new file icon in the Explorer sidebar, typing app.py, and pressing Enter.
  • Paste the following starter code into app.py:
import streamlit as st

# Set the page title, icon, and wide layout
st.set_page_config(page_title="AI Dashboard Generator", page_icon="\ud83d\udcca", layout="wide")
st.title("AI Dashboard Generator")

What does this code do?

  • import streamlit as st loads the Streamlit library so you can build web pages with Python.
  • st.set_page_config sets the browser tab title, an icon, and tells Streamlit to use the full width of the page.
  • st.title adds a large heading at the top of your app.
  • Save the file with Cmd+S (macOS) or Ctrl+S (Windows).
  • Switch back to your terminal. Make sure you are still inside the ai-dashboard folder.

Before you run the next command, what do you think will happen when you start Streamlit for the first time?

  • Launch your Streamlit app by running this command:
streamlit run app.py

What happens when you run this?

Streamlit starts a local web server and automatically opens your browser to localhost:8501. The terminal stays busy running the server. Do not close the terminal or press Ctrl+C while you want the app to keep running.

Streamlit also watches your files. Every time you save app.py, the app reloads automatically in the browser.

Your browser should open to a page showing the heading AI Dashboard Generator at the top. The rest of the page is blank for now.

✔️ Awesome, I've got everything!

Great. Double check you've saved app.py and leave the Streamlit server running in your terminal.

ⓧ I'd like to double check the full code

Here is the complete app.py so far. Make sure yours matches exactly.

import streamlit as st

st.set_page_config(page_title="AI Dashboard Generator", page_icon="\ud83d\udcca", layout="wide")
st.title("AI Dashboard Generator")

Browser didn't open or seeing an error?

  • If the browser didn't open automatically, manually open http://localhost:8501 in your browser.
  • If you see a ModuleNotFoundError, the packages did not install correctly. Go back and rerun the pip install command.
  • Make sure your terminal is inside the ai-dashboard folder where app.py lives. Run pwd to check.

help me get Streamlit running on my machine

Your environment is ready and Streamlit is live. Next up, you'll add the file upload feature so users can drop in any Excel spreadsheet and see their data instantly.

Upload Excel and Preview Your Data

Your Streamlit app is running and showing a title on a blank page. That's the skeleton, but a dashboard generator that can't see any data isn't much of a dashboard generator.

Before the AI can analyze anything, the user needs to upload their data and confirm it was parsed correctly. In this step, you'll build the upload flow and render the raw Excel data as an interactive, sortable web table with summary statistics.

In this step, get ready to:
  • Add sidebar controls for the API key and dashboard theme.
  • Upload an Excel file and display it as an interactive, sortable table.
  • Show summary metrics for the uploaded data.
Set up your imports and sidebar

The app needs new libraries for data handling, charting, JSON parsing, and AI. It also needs a sidebar where the user enters their Gemini API key and picks a visual theme.

  • In app.py, add these imports directly below the existing import streamlit as st line:
import pandas as pd         # Data manipulation
import plotly.express as px  # Interactive charts
import json                  # Parse AI responses
import io                    # In-memory file handling
from google import genai     # Gemini AI client

What are these imports for?

  • pandas reads Excel files and manipulates tabular data as DataFrames.
  • plotly.express creates interactive charts with hover, zoom, and pan built in.
  • google.genai is the official SDK for calling the Gemini API.
  • json and io handle parsing AI responses and in-memory file buffers for export.
  • Now add the sidebar controls below the st.title line:
# Sidebar input for the Gemini API key (hidden with password type)
api_key = st.sidebar.text_input("Gemini API Key", type="password")

# Available Plotly themes for chart styling
THEMES = [
    "plotly_white",
    "plotly_dark",
    "ggplot2",
    "seaborn",
    "simple_white",
    "plotly",
]
# Dropdown to pick the active theme
selected_theme = st.sidebar.selectbox("Dashboard Theme", THEMES)

What does this sidebar code do?

  • st.sidebar.text_input creates a password-style input in the sidebar so the API key stays hidden as the user types.
  • THEMES is a list of built-in Plotly templates like plotly_dark and seaborn. Each one reskins every chart with a single parameter change.
  • st.sidebar.selectbox turns the theme list into a dropdown the user can switch at any time.
  • Save app.py.
  • Switch to your browser at localhost:8501. You should see a sidebar on the left with a password field labeled Gemini API Key and a dropdown labeled Dashboard Theme.

Don't see the sidebar?

  • Make sure you saved app.py. Streamlit auto-refreshes on save, but only if the file is actually written to disk.
  • If the page shows an error, check that all five import lines are above the st.set_page_config line. Streamlit requires st.set_page_config to be the first Streamlit call, but regular Python imports can come before it.
  • If the sidebar is collapsed, click the arrow icon at the top left of the page to expand it.

Why isn't my Streamlit sidebar showing up after saving app.py?

Add the file uploader and data preview

The sidebar is ready. Now the app needs a way to accept an Excel file and show its contents. Streamlit's file uploader widget handles the upload, and pandas parses the spreadsheet into a DataFrame the rest of the app can work with.

  • Add the file uploader and data display code below the selected_theme line:
# File uploader restricted to Excel formats
uploaded_file = st.file_uploader("Upload an Excel file", type=["xlsx", "xls"])

if uploaded_file is not None:
    # Read the Excel file into a pandas DataFrame
    df = pd.read_excel(uploaded_file, engine="openpyxl")
    # Store in session state so other parts of the app can access it
    st.session_state["df"] = df

    # Show the first 20 rows in an expandable preview
    with st.expander("Data Preview", expanded=True):
        st.dataframe(df.head(20), use_container_width=True)

What does this code do?

  • st.file_uploader creates a drag-and-drop upload widget that only accepts .xlsx and .xls files.
  • pd.read_excel with the openpyxl engine parses the uploaded file into a tabular DataFrame.
  • st.session_state["df"] stores the DataFrame so later steps (like AI analysis) can access it without re-reading the file.
  • st.expander wraps the data table in a collapsible section. st.dataframe renders the first 20 rows as an interactive, sortable table.
  • Save app.py.
  • Switch to your browser. You should see a file upload area below the title that says Upload an Excel file.
  • Drag in any .xlsx file you have, or click Browse files to pick one from your computer.

Your uploaded data appears as a sortable table inside the Data Preview expander. You can click any column header to sort the rows.

Don't have an Excel file handy?

Open Google Sheets, type a few columns of sample data (e.g. Name, Sales, Region), then click File > Download > Microsoft Excel (.xlsx). That gives you a test file to upload.

Seeing an error after uploading?

  • Make sure the file is a real .xlsx file, not a .csv renamed with an .xlsx extension.
  • Check that import pandas as pd is at the top of your file. A missing import causes a NameError when the code tries to call pd.read_excel.

My Streamlit file uploader is throwing an error when I upload an Excel file. How do I fix it?

Display summary metrics for your data

The table shows the raw data, but it doesn't tell the user how big their dataset is or whether it has gaps. A quick row count, column count, and missing-value count gives instant context before the AI touches anything.

  • Inside the with st.expander block, add these lines directly below the st.dataframe line:
        # Summary metrics in a three-column row
        preview_cols = st.columns(3)
        preview_cols[0].metric("Rows", len(df))
        preview_cols[1].metric("Columns", len(df.columns))
        preview_cols[2].metric("Missing Values", int(df.isnull().sum().sum()))

What does this code do?

  • st.columns(3) creates three equal-width columns so the metrics sit side by side.
  • st.metric renders a bold label-and-value card. Each one pulls a single statistic from the DataFrame: total rows, total columns, and the count of all missing (null) cells.

Before you save, think about what you expect to see. How many rows and columns does your test file have? Do you think it has any missing values?

  • Save app.py.
  • Switch to your browser and re-upload your Excel file (Streamlit resets the uploader on code changes).

You should see three metric cards below the data table showing Rows, Columns, and Missing Values. The numbers should match what you predicted from your test file.

Metrics not showing up?

  • Check your indentation. The three preview_cols lines must be indented inside the with st.expander block (two levels of indentation, matching the st.dataframe line above).
  • Make sure you re-uploaded the file after saving. Streamlit clears the file uploader when the app reloads.

My st.metric cards are not appearing below the data table in Streamlit. What's wrong?

✔️ Awesome, I've got everything!

Great. Double check you've saved app.py and your data table plus metrics are rendering correctly.

ⓧ I'd like to double check the full code

Scroll back through this step and confirm each of these code chunks is present in your app.py, in order:

  • The six import lines at the top (streamlit, pandas, plotly.express, json, io, google.genai).
  • st.set_page_config and st.title (from Step 1).
  • The sidebar controls: api_key text input, THEMES list, and selected_theme selectbox.
  • The file uploader, pd.read_excel call, session state storage, and st.dataframe inside the expander.
  • The three st.metric calls inside the same expander block, for Rows, Columns, and Missing Values.

Your app now reads any Excel file and displays it as an interactive table with at-a-glance statistics. Next up, you'll connect Gemini AI to analyze this data and decide which charts to build.

Connect Gemini AI to Analyze Your Data

Your app can load an Excel file and show the data as an interactive table with summary metrics. That is useful, but the learner still has to decide what to chart and how to chart it.

In this step, you will connect Gemini AI to handle that decision for you. You will send a compact profile of the data to Gemini, and it will return a JSON blueprint that specifies exactly which KPIs and charts to build.

In this step, get ready to:
  • Build a schema extractor that profiles your data columns, types, and statistics.
  • Write a structured prompt that tells Gemini exactly what JSON format to return.
  • Wire up a Generate Dashboard button that sends the schema to the Gemini API and stores the response.
Build the data schema extractor

Sending an entire spreadsheet to an LLM would be slow, expensive, and unnecessary. The AI only needs to know the shape of the data to recommend charts.

Why send a schema instead of the full dataset?

A schema is a compact profile: column names, data types, basic statistics, and a few sample rows. It gives the AI everything it needs to recommend charts without uploading thousands of rows.

This keeps API calls fast (well under 1,000 tokens per request), works regardless of dataset size, and avoids sending sensitive row-level data to a third party.

  • In app.py, find this line near the top of your file:
selected_theme = st.sidebar.selectbox("Dashboard Theme", THEMES)
  • Add the following build_schema function below that line, above the uploaded_file line:
def build_schema(df):
    schema = {"columns": [], "row_count": len(df)}
    for col in df.columns:
        info = {
            "name": col,
            "dtype": str(df[col].dtype),
            "unique_count": int(df[col].nunique()),
            "null_count": int(df[col].isnull().sum()),
        }
        if pd.api.types.is_numeric_dtype(df[col]):
            info["min"] = float(df[col].min()) if not df[col].isnull().all() else None
            info["max"] = float(df[col].max()) if not df[col].isnull().all() else None
            info["mean"] = float(df[col].mean()) if not df[col].isnull().all() else None
        if pd.api.types.is_object_dtype(df[col]) or pd.api.types.is_categorical_dtype(df[col]):
            top_values = df[col].value_counts().head(5).index.tolist()
            info["top_values"] = [str(v) for v in top_values]
        schema["columns"].append(info)
    sample = df.head(3).copy()
    for col in sample.columns:
        sample[col] = sample[col].astype(str)
    schema["sample_rows"] = sample.to_dict(orient="records")
    return schema

What does this code do?

  • For every column, it captures the name, data type, unique value count, and null count.
  • For numeric columns, it adds min, max, and mean so the AI can recommend meaningful KPIs.
  • For text/categorical columns, it grabs the top 5 most common values so the AI knows what categories exist.
  • It also includes 3 sample rows as a concrete preview of the data.
  • Save app.py.
  • Switch back to your browser at localhost:8501. The page should reload without any errors, and the file uploader should still appear.

Seeing a SyntaxError or IndentationError?

  • Make sure the build_schema function is defined at the top level of the file (no indentation before def), not inside the if uploaded_file block.
  • Check that all parentheses and brackets are balanced. The dictionary comprehension has several nested braces.

Why is my build_schema function causing an error in Streamlit?

Write the AI prompt

The schema tells the AI what the data looks like. The prompt tells the AI what to do with that information and exactly what format to return.

  • Add the following get_dashboard_prompt function directly below the build_schema function you just added, still above the uploaded_file line:
def get_dashboard_prompt(schema):
    return f"""You are a data analyst. Analyze this dataset schema and design a dashboard.

Return a JSON object with exactly this structure:
{{
  "kpis": [
    {{"label": "Human readable name", "column": "exact_column_name", "aggregation": "sum|mean|count|max|min"}}
  ],
  "charts": [
    {{"title": "Chart Title", "chart_type": "bar|line|scatter|pie|histogram|box", "x": "column_name", "y": "column_name_or_null", "color": "column_name_or_null"}}
  ]
}}

Rules:
- Include 3-4 KPIs using numeric columns only
- Include 4-6 charts that best represent the data
- Use only column names that exist in the schema
- For pie charts, use x for the category column, set y to null
- For histograms, use x for the numeric column, set y to null
- Prefer bar charts for categorical comparisons, line charts for time series, scatter for correlations

Dataset schema:
{json.dumps(schema, indent=2)}"""

What does this prompt do?

  • It gives Gemini a role ("data analyst") and a clear task ("design a dashboard").
  • It provides an exact JSON template so the response is always machine-parseable. The double braces {{ and }} are Python f-string escapes that produce literal { and } in the output.
  • The rules section constrains the AI so it picks appropriate chart types and only references columns that actually exist.
  • At the end, json.dumps(schema, indent=2) injects the data profile you built in build_schema.
  • Save app.py.
  • Check your browser. The page should reload without errors. You will not see any new UI elements yet because these are helper functions that the button will call next.

Seeing a red error traceback?

  • The triple-quoted f-string must start with f""" (note the f prefix) and end with a matching """ on its own line.
  • Every literal brace in the JSON template must be doubled: {{ and }}. Single braces are reserved for Python variable injection.
  • Make sure this function is at the top level (no indentation before def), just like build_schema.

Why is my get_dashboard_prompt function causing a syntax error?

Why structured JSON output instead of free text?

If you asked the AI to describe charts in plain English, you would need fragile text parsing to extract column names and chart types. Gemini supports a response_mime_type setting that constrains the model to return valid JSON. Your code can call json.loads() on the response and get a reliable Python dictionary every time.

Wire up the Generate Dashboard button

The two helper functions are ready. Now you need a button that calls them: extract the schema, send it to Gemini, parse the JSON response, and store it for later rendering.

  • In app.py, scroll down to the if uploaded_file is not None: block and find the end of the with st.expander section. Find this line:
        preview_cols[2].metric("Missing Values", int(df.isnull().sum().sum()))
  • Add the following code below the expander block, still inside the if uploaded_file is not None: block (indented one level):
    st.divider()

    generate_disabled = not api_key
    if generate_disabled:
        st.info("Enter your Gemini API Key in the sidebar to generate a dashboard.")

    if st.button("Generate Dashboard", type="primary", disabled=generate_disabled):
        with st.spinner("AI is analyzing your data..."):
            schema = build_schema(df)
            client = genai.Client(api_key=api_key)
            prompt = get_dashboard_prompt(schema)
            response = client.models.generate_content(
                model="gemini-2.5-flash",
                contents=prompt,
                config={"response_mime_type": "application/json"},
            )
            dashboard_config = json.loads(response.text)
            st.session_state["dashboard_config"] = dashboard_config
            st.rerun()

What does this code do?

  • generate_disabled = not api_key disables the button if the learner has not entered their API key in the sidebar yet.
  • genai.Client(api_key=api_key) creates a Gemini client using the key from the sidebar.
  • config={"response_mime_type": "application/json"} forces Gemini to return valid JSON instead of free-form text.
  • json.loads(response.text) parses the JSON string into a Python dictionary containing the KPI and chart specs.
  • st.rerun() refreshes the page so the stored config is available on the next render cycle.

A note on data privacy

On Gemini's free tier, prompts may be reviewed by Google. Your app only sends column names, data types, statistics, and 3 sample rows. It never sends the full dataset. Keep this in mind if you work with sensitive data in the future.

  • Save app.py.
  • Switch back to your browser at localhost:8501.
  • Upload an Excel file. You should see the data preview with the row, column, and missing value metrics, followed by a blue Generate Dashboard button below the divider.

Don't see the Generate Dashboard button?

  • The button code must be indented under if uploaded_file is not None: (one level of indentation), but outside the with st.expander block. If it is inside the expander, it will only appear when the expander is open.
  • Make sure you uploaded a file first. The button only appears after a file is loaded.

Why isn't my Generate Dashboard button showing up in Streamlit?

Before you click the button, what do you think will happen? Will charts appear immediately, or will something else happen first?

  • Make sure you have entered your Gemini API key in the sidebar on the left. If the key field is empty, the button will be grayed out with a blue info message.
  • Click Generate Dashboard.

You should see an "AI is analyzing your data..." spinner for a few seconds. Then the page refreshes. No charts appear yet because you have not built the rendering code. The important thing is that no red error traceback appeared.

The AI's full dashboard blueprint (KPI definitions and chart specifications with chart types, axis columns, and titles) is now stored in st.session_state["dashboard_config"]. The next step will turn that blueprint into visible charts.

Seeing an API error after clicking Generate Dashboard?

  • Double-check that the API key in the sidebar is correct. Copy it again from Google AI Studio if needed.
  • If you see a rate limit error, wait a minute and try again. The Gemini free tier has per-minute request limits.
  • If you see a JSON parsing error, the AI may have returned unexpected output. Click Generate Dashboard again. The structured output mode usually produces valid JSON, but occasional retries can help.

I'm getting an error when I click Generate Dashboard in my Streamlit app.

✔️ Awesome, I've got everything!

Great. Make sure you have saved app.py.

ⓧ I'd like to double check the full code

Scroll up through your app.py and confirm the following pieces are present, in this order from top to bottom:

  • Imports (streamlit, pandas, plotly.express, json, io, genai) and page config at the top.
  • Sidebar widgets (api_key text input and selected_theme selectbox).
  • def build_schema(df): function at the top level.
  • def get_dashboard_prompt(schema): function at the top level.
  • uploaded_file = st.file_uploader(...) followed by the if uploaded_file is not None: block.
  • Inside that block: data preview expander, then st.divider(), then the Generate Dashboard button with the Gemini API call.

The AI has analyzed your data and produced a complete dashboard blueprint. Right now that blueprint is just data sitting in session state. In the next step, you will turn those JSON specs into interactive Plotly charts that render on screen.

Render the AI-Generated Charts

Your app can now send a data schema to Gemini and get back a structured JSON blueprint describing exactly which charts to build. The problem is that JSON is just data sitting in memory.

In this step, you will turn those JSON specs into visible, interactive Plotly charts on screen. You will build a function that reads each chart spec and calls the matching Plotly Express function, then loop through every chart the AI recommended and render it.

In this step, get ready to:
  • Build a create_chart function that maps chart types to Plotly Express calls.
  • Render the AI's chart recommendations on screen.
Build the chart rendering function

Each chart spec in the AI's JSON includes a chart_type value like bar, line, or pie. You need a function that reads that value and calls the correct Plotly Express function.

  • In app.py, find the get_dashboard_prompt function. Add this new function directly below it, before the uploaded_file line:
def create_chart(df, chart_spec, theme):
    # Extract chart parameters from the AI's JSON spec
    chart_type = chart_spec.get("chart_type", "bar")
    x = chart_spec.get("x")
    y = chart_spec.get("y")
    color = chart_spec.get("color")
    title = chart_spec.get("title", "Chart")
    # Build shared keyword arguments for Plotly Express
    kwargs = {"title": title, "template": theme}
    if color and color in df.columns:
        kwargs["color"] = color
    # Map each chart_type to its matching Plotly Express call
    try:
        if chart_type == "bar" and x in df.columns and y and y in df.columns:
            fig = px.bar(df, x=x, y=y, **kwargs)
        elif chart_type == "line" and x in df.columns and y and y in df.columns:
            fig = px.line(df, x=x, y=y, **kwargs)
        elif chart_type == "scatter" and x in df.columns and y and y in df.columns:
            fig = px.scatter(df, x=x, y=y, **kwargs)

What does this code do?

The function pulls chart_type, x, y, color, and title out of each JSON spec the AI returned. It builds a shared kwargs dictionary so every chart gets the same title and theme formatting.

Then a chain of if/elif checks matches the chart_type string to the right Plotly Express call. Each condition also validates that the required columns actually exist in the DataFrame before calling the function.

  • Still inside the create_chart function's try block, add the remaining chart types directly below the scatter line you just wrote:
        elif chart_type == "pie" and x in df.columns:
            fig = px.pie(df, names=x, title=title, template=theme)
        elif chart_type == "histogram" and x in df.columns:
            fig = px.histogram(df, x=x, **kwargs)
        elif chart_type == "box" and x in df.columns:
            if y and y in df.columns:
                fig = px.box(df, x=x, y=y, **kwargs)
            else:
                fig = px.box(df, x=x, **kwargs)
        # Fallback: use first two columns if chart_type is unrecognized
        else:
            fig = px.bar(df, x=df.columns[0], y=df.columns[1] if len(df.columns) > 1 else df.columns[0], **kwargs)
    except Exception:
        # Safety net: default to a bar chart if anything goes wrong
        fig = px.bar(df, x=df.columns[0], y=df.columns[1] if len(df.columns) > 1 else df.columns[0], **kwargs)
    # Tighten chart margins for a cleaner look
    fig.update_layout(margin=dict(l=20, r=20, t=40, b=20))
    return fig

Why the else and except fallbacks?

The AI might occasionally hallucinate a column name that does not exist, or suggest a chart type the function does not handle. The else branch catches unrecognized chart types by defaulting to a bar chart using the first two columns.

The except Exception wrapping the entire block means that even if Plotly throws an unexpected error, the app does not crash. It falls back gracefully to a simple bar chart instead.

  • Save app.py. The function is defined but nothing calls it yet. The next chunk adds the rendering loop that puts charts on screen.
Render charts from the AI's config

The create_chart function knows how to build one chart. Now you need a loop that pulls every chart spec from session state and renders it on the page.

  • Scroll to the very bottom of app.py, after the st.rerun() line inside the if st.button block. Add this rendering block at the end of the file:
if "dashboard_config" in st.session_state and "df" in st.session_state:
    config = st.session_state["dashboard_config"]
    df = st.session_state["df"]

    st.subheader("Dashboard")

    # Loop through each chart spec and render it
    charts = config.get("charts", [])
    figures = []
    for chart_spec in charts:
        fig = create_chart(df, chart_spec, selected_theme)
        st.plotly_chart(fig, use_container_width=True)
        figures.append(fig)

What does this code do?

  • The if guard checks that both the dashboard config and the DataFrame exist in session state. This block only runs after the learner has uploaded a file and clicked Generate Dashboard.
  • The loop iterates over every chart spec in the AI's response, calls create_chart to build a Plotly figure, and renders it with st.plotly_chart.
  • Each figure is also appended to a figures list. You will use this list later for exporting the dashboard.

Before you check the result, what do you think the dashboard will look like? Will the charts appear side by side, in a grid, or stacked on top of each other?

  • Save app.py.
  • Switch back to your browser at localhost:8501. Upload your Excel file and click Generate Dashboard.

You should see a "Dashboard" subheading followed by 4-6 interactive Plotly charts. Each chart matches one of the specs from the AI's JSON response. You can hover over data points to see tooltips, and zoom or pan on each chart.

However, every chart is stacked in a single column with default Plotly styling. It looks like a vertical list of charts, not a polished dashboard. That gap is exactly what the next step fixes.

Not seeing any charts?

  • If Streamlit shows a red error, check that create_chart is defined before the uploaded_file line. Functions must be defined above the code that calls them.
  • Make sure the new if "dashboard_config" block is at the top level of the file (no indentation), not nested inside another if block.
  • If the page loads but no charts appear, you may need to re-upload your Excel file and click Generate Dashboard again. Saving the file can sometimes clear Streamlit session state.
  • Still stuck? Help me debug why my Plotly charts are not rendering in Streamlit after I added the create_chart function and rendering loop.

✔️ Awesome, I've got everything!

Great. Double check you have saved app.py.

ⓧ I'd like to double check the full code

Your app.py should contain these sections in order. Scroll through your file and confirm each one is present:

  • Imports (streamlit, pandas, plotly.express, json, io, genai) and page config at the top.
  • Sidebar inputs (api_key, THEMES, selected_theme).
  • build_schema function.
  • get_dashboard_prompt function.
  • create_chart function (new in this step).
  • File uploader, data preview, and the Generate Dashboard button block.
  • Dashboard rendering block starting with if "dashboard_config" in st.session_state (new in this step).

The charts work. You can hover, zoom, and pan on every one of them. But scrolling through a long vertical list of default-styled charts does not feel like a real dashboard. Next up, you will add KPI cards, a two-column grid, theme switching, and export buttons to make it look polished.

Polish the Layout, Add Themes and Export

Your charts are rendering on screen, but the result is a plain vertical scroll of plots. There are no summary numbers at the top, no grid structure, and no way to save or share the output.

A real dashboard has KPI cards that surface the most important numbers at a glance, charts arranged in a tidy grid, consistent theming, and export options. In this step, you will add all four.

In this step, get ready to:
  • Add KPI metric cards that summarize the most meaningful numbers at the top of the dashboard.
  • Arrange charts in a responsive two-column grid with instant theme switching.
  • Add one-click export to HTML, PDF, and JSON so you can share the dashboard with anyone.
Build a function to compute KPI values

The AI's JSON response already includes a kpis list specifying which columns to summarize and what aggregation to use (sum, mean, count, max, or min). You need a function that reads those specs and computes the actual values from the DataFrame.

  • In app.py, find the create_chart function and scroll to the end of it.
  • Add the following compute_kpi function directly below create_chart:
def compute_kpi(df, kpi_spec):
    col = kpi_spec.get("column")
    agg = kpi_spec.get("aggregation", "sum")
    if col not in df.columns or not pd.api.types.is_numeric_dtype(df[col]):
        return None
    if agg == "sum":
        return df[col].sum()
    elif agg == "mean":
        return df[col].mean()
    elif agg == "count":
        return df[col].count()
    elif agg == "max":
        return df[col].max()
    elif agg == "min":
        return df[col].min()
    return df[col].sum()

What does this function do?

  • It reads the column and aggregation fields from the AI's KPI spec.
  • It validates that the column exists and is numeric before computing anything.
  • It applies the matching aggregation (sum, mean, count, max, or min) and returns the result.
Render KPI cards at the top of the dashboard

Now you will use compute_kpi to populate metric cards that appear above the charts. Streamlit's st.metric widget displays a label and a large formatted number, which is exactly what a KPI card needs.

  • Scroll down to the bottom of app.py and find this line inside the if "dashboard_config" block:
    st.subheader("Dashboard")

    charts = config.get("charts", [])
  • Add the following KPI rendering code between st.subheader("Dashboard") and charts = config.get("charts", []):
    # Pull KPI specs from the AI config and render metric cards
    kpis = config.get("kpis", [])
    if kpis:
        kpi_cols = st.columns(len(kpis))
        for i, kpi in enumerate(kpis):
            value = compute_kpi(df, kpi)
            if value is not None:
                if abs(value) >= 1000:
                    display_val = f"{value:,.0f}"
                else:
                    display_val = f"{value:,.2f}"
                kpi_cols[i].metric(label=kpi.get("label", "KPI"), value=display_val)

    st.divider()

What does this code do?

  • st.columns(len(kpis)) creates one column per KPI so the cards sit side by side in a single row.
  • The formatting logic uses commas with no decimals for large numbers (1,000+) and two decimal places for smaller values, keeping the cards readable.
  • st.divider() draws a horizontal line separating the KPI cards from the charts below.
  • Save app.py.
  • Switch back to your browser at localhost:8501. Streamlit auto-reloads on save.
  • Upload your Excel file and click Generate Dashboard again.

You should see a row of KPI metric cards at the top of the dashboard, each showing a label and a formatted number. The charts still appear below them in a single column.

Don't see any KPI cards?

  • Make sure you placed the new code between st.subheader("Dashboard") and charts = config.get("charts", []), not after the chart loop.
  • Check that the compute_kpi function is defined above the line that calls it. It should sit right after the create_chart function, not inside the dashboard rendering block.
  • help me debug my KPI cards not showing up
Arrange charts in a two-column grid

The charts still stack in a single vertical column, which wastes horizontal space and forces scrolling. A two-column grid makes the dashboard scannable at a glance.

  • In app.py, find the chart rendering loop at the bottom of the file:
    charts = config.get("charts", [])
    figures = []
    for chart_spec in charts:
        fig = create_chart(df, chart_spec, selected_theme)
        st.plotly_chart(fig, use_container_width=True)
        figures.append(fig)
  • Replace that entire block with the following two-column grid loop:
    charts = config.get("charts", [])
    figures = []
    # Step through charts two at a time, placing each pair side by side
    for i in range(0, len(charts), 2):
        row_cols = st.columns(2)
        for j in range(2):
            if i + j < len(charts):
                with row_cols[j]:
                    fig = create_chart(df, charts[i + j], selected_theme)
                    st.plotly_chart(fig, use_container_width=True)
                    figures.append(fig)

What changed here?

Instead of looping through every chart one by one, the new loop steps through the list two at a time using range(0, len(charts), 2). Each iteration creates a fresh row of two columns with st.columns(2) and renders one chart in each column.

The if i + j < len(charts) guard handles odd numbers of charts gracefully. If the AI recommends 5 charts, the last row simply has one chart on the left and an empty space on the right.

  • Save app.py and switch back to your browser.
  • Upload your Excel file and click Generate Dashboard.

Your charts now appear in pairs, side by side. Try switching the Dashboard Theme dropdown in the sidebar. Every chart updates its colors and styling instantly.

Charts still in a single column?

  • Make sure you deleted the old for chart_spec in charts: loop completely. Having both loops will render charts twice.
  • Confirm that st.columns(2) is inside the for i in range(...) loop, not above it.
  • help me fix my two-column chart grid layout
Add export with download buttons

The dashboard looks polished, but it only lives inside your running Streamlit app. To share it with someone who does not have Python installed, you need an export option.

  • In app.py, find the compute_kpi function you just added.
  • Add the following build_html_export function directly below compute_kpi:
def build_html_export(figures, kpis_html):
    # Convert each Plotly figure into an HTML fragment
    chart_divs = ""
    for fig in figures:
        chart_divs += '<div style="width:48%;min-width:400px;">'
        chart_divs += fig.to_html(full_html=False, include_plotlyjs=False)
        chart_divs += "</div>"

    return f"""<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>AI Generated Dashboard</title>
<script src="https://cdn.plot.ly/plotly-latest.min.js"></script>
<style>
body {{{{ font-family: Arial, sans-serif; margin: 20px; background: #f5f5f5; }}}}
h1 {{{{ text-align: center; color: #333; }}}}
.kpi-row {{{{ display: flex; gap: 16px; justify-content: center; flex-wrap: wrap; margin: 20px 0; }}}}
.kpi-card {{{{ background: white; border-radius: 8px; padding: 20px; text-align: center; min-width: 150px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }}}}
.kpi-label {{{{ font-size: 14px; color: #666; }}}}
.kpi-value {{{{ font-size: 28px; font-weight: bold; color: #333; }}}}
.charts {{{{ display: flex; flex-wrap: wrap; gap: 20px; justify-content: center; }}}}
</style>
</head>
<body>
<h1>AI Generated Dashboard</h1>
<div class="kpi-row">{kpis_html}</div>
<div class="charts">{chart_divs}</div>
</body>
</html>"""

What does this function do?

  • It takes the list of Plotly figures and converts each into an HTML fragment using fig.to_html(full_html=False).
  • It wraps those fragments into a self-contained HTML page with a Plotly CDN script tag, so the file opens and renders charts in any browser without Python.
  • The inline CSS creates a KPI card row at the top and a flex-wrap chart grid, mirroring the Streamlit layout.

Now you will wire up the download buttons that call this function and offer PDF export via Kaleido.

  • Scroll to the very bottom of app.py, right after the closing of the two-column chart loop (the last line should be figures.append(fig)).
  • Add the export section heading and the HTML download button by pasting the following code below that loop, still inside the if "dashboard_config" block:
    st.divider()
    st.subheader("Export Dashboard")
    export_cols = st.columns(3)

    # Build KPI HTML cards and generate the full HTML dashboard file
    with export_cols[0]:
        kpis_html_parts = ""
        for kpi in kpis:
            value = compute_kpi(df, kpi)
            if value is not None:
                if abs(value) >= 1000:
                    display_val = f"{value:,.0f}"
                else:
                    display_val = f"{value:,.2f}"
                kpis_html_parts += f'<div class="kpi-card"><div class="kpi-label">{kpi.get("label", "KPI")}</div><div class="kpi-value">{display_val}</div></div>'
        html_content = build_html_export(figures, kpis_html_parts)
        st.download_button(
            "Download HTML",
            data=html_content,
            file_name="dashboard.html",
            mime="text/html",
        )

What does this code do?

  • st.columns(3) creates three columns for the three export options (HTML, PDF, JSON).
  • The first column rebuilds the KPI values as raw HTML cards, then passes them into build_html_export to create a standalone HTML file.
  • st.download_button renders a clickable button that triggers a file download in the browser.
  • Save app.py and switch back to your browser.
  • Upload your Excel file, click Generate Dashboard, then scroll down past the charts.

You should see an Export Dashboard section with a Download HTML button. Click it and open the downloaded dashboard.html file in your browser to confirm the charts render with interactive hover and zoom.

HTML file opens but charts are blank?

  • The HTML file loads the Plotly library from a CDN, so you need an internet connection when opening it. If you are offline, the charts will not render.
  • help me debug my HTML dashboard export

Now add the PDF and JSON download buttons to fill the remaining two columns.

  • In app.py, add the following code directly below the with export_cols[0]: block you just wrote:
    # Export the first chart as a PDF using Kaleido
    with export_cols[1]:
        if figures:
            pdf_buffer = io.BytesIO()
            figures[0].write_image(file=pdf_buffer, format="pdf", width=1200, height=600)
            st.download_button(
                "Download Chart PDF",
                data=pdf_buffer.getvalue(),
                file_name="chart.pdf",
                mime="application/pdf",
            )

    # Export the raw AI config as a JSON file
    with export_cols[2]:
        json_str = json.dumps(config, indent=2)
        st.download_button(
            "Download Config JSON",
            data=json_str,
            file_name="dashboard_config.json",
            mime="application/json",
        )

What does this code do?

  • figures[0].write_image() converts the first chart into a PDF using Kaleido as the rendering engine. It writes the image bytes into an io.BytesIO buffer so Streamlit can serve it as a download.
  • The JSON download saves the raw AI-generated dashboard configuration. This is useful for debugging or for feeding the same config back into the app later.

Why does the PDF only export the first chart?

Kaleido renders one figure at a time. To keep the code simple, this version exports just the first chart. You could extend it with a loop to generate a multi-page PDF, but that adds complexity beyond the scope of this project.

  • Save app.py.

Before you refresh, think about what you expect to see at the bottom of the dashboard now. How many download buttons should appear?

  • Switch back to your browser, upload your Excel file, and click Generate Dashboard.
  • Scroll down to the Export Dashboard section.

You should see three download buttons side by side: Download HTML, Download Chart PDF, and Download Config JSON. Try each one to confirm the files download correctly.

PDF download button missing or erroring?

  • Kaleido requires Chrome or Chromium installed on your system to render PDFs. If you see an error about a missing browser, install Chrome and restart your terminal.
  • Make sure import io is at the top of your file. The io.BytesIO buffer is required for PDF export.
  • help me fix my Kaleido PDF export

✔️ Awesome, I've got everything!

Great. Double check you have saved app.py.

ⓧ I'd like to double check the full code

Scroll up and confirm each of these code blocks is present in your app.py, in order:

  • The compute_kpi function (right after create_chart).
  • The build_html_export function (right after compute_kpi).
  • The KPI rendering block with kpis = config.get("kpis", []) and st.columns(len(kpis)) (between the Dashboard subheader and the charts loop).
  • The two-column chart grid using for i in range(0, len(charts), 2) (replacing the old single-column loop).
  • The export section with three st.download_button calls inside export_cols[0], export_cols[1], and export_cols[2].

Take one final look at the complete dashboard. Try switching the Dashboard Theme dropdown in the sidebar between plotly_dark and seaborn. Every chart updates its colors in real time, the KPI cards stay fixed at the top, and the three export buttons sit ready at the bottom.

Your dashboard now has summary KPI cards, a clean two-column chart grid, instant theme switching, and three export options. That is a fully functional, polished analytics tool. Next up, you will take it even further with an optional Secret Mission.

Secret mission

Add AI-Powered Chart Suggestions

Your dashboard generates great charts on the first try, but what if the user wants to explore more angles on their data? In this mission, you will add a "Suggest More Charts" button that asks Gemini for additional visualizations the user can preview and add to their live dashboard with one click.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local machine. The Gemini API free tier has no ongoing charges, so there are no costs to worry about. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • Streamlit dev server running in your terminal at localhost:8501.
  • Project folder on your Desktop containing app.py.
  • Python packages: streamlit, plotly, google-genai, openpyxl, kaleido.
  • Gemini API key in Google AI Studio.

Keep everything running

No action needed. Choose this if you're still actively building or want to keep experimenting with different Excel files.

  • Your Streamlit server continues running in the terminal at localhost:8501.
  • Your project folder, installed Python packages, and Gemini API key all remain available.

Pause - I'll come back to this later

Shut down the running server to free up your terminal. Your project files, packages, and API key stay on your machine for next time.

  • Switch to the terminal where Streamlit is running and press Ctrl+C to stop the server.
  • Your project folder and all installed packages remain on your machine.
  • To restart later, navigate to your project folder in the terminal and run:
streamlit run app.py

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow each step below to fully clean up.

  • Switch to the terminal where Streamlit is running and press Ctrl+C to stop the server.
  • Delete the project folder on your Desktop that contains app.py. Open Finder (macOS) or File Explorer (Windows), navigate to your Desktop, right-click the project folder, and delete it.
  • Uninstall the Python packages by running:
pip uninstall streamlit plotly google-genai openpyxl kaleido -y
  • Delete your Gemini API key. Sign in to Google AI Studio at aistudio.google.com, find the API key you created for this project in the key list, and delete it.

Nice Work!

Nice Work!

You just built a fully functional AI dashboard generator that turns any Excel file into a polished, interactive dashboard. No manual column picking, no chart configuration. Upload a file, click a button, and the AI handles the rest.

You've learned how to:

  • Build a Streamlit web app that uploads any Excel file, parses it with pandas, and displays it as an interactive, searchable data table with summary metrics.
  • Integrate Google Gemini's free API with structured JSON output to automatically analyze a data schema and generate KPI definitions and chart specifications that drive a complete dashboard.
  • Render interactive Plotly charts in a two-column grid with KPI metric cards, switchable visual themes, and one-click export to self-contained HTML files and PDF chart downloads using Kaleido.
  • Secret Mission: Added an AI-powered suggestion engine that recommends new chart visualizations and dynamically adds them to the live dashboard.

Ready to quiz yourself?