Interactive AI Dashboard Builder

Turn any Excel file into an AI-powered dashboard with drag-and-drop panels.

Introduction

30 Second Summary

Think about the last time you opened a spreadsheet full of raw data. Rows of transactions, dates, and categories that somehow need to become a professional dashboard.

In this project, you will build an AI-powered dashboard builder using Streamlit and Gemini. Upload any Excel file, and the AI automatically generates a professional dashboard with aggregated charts, written insights, drag-and-drop layout, and multi-format export.

What You'll Build

Upload any Excel file, watch the AI analyze your data and build a full dashboard of professional charts with written insights underneath, drag panels into your preferred order, then download the finished dashboard as interactive HTML, a static image, or a multi-page PDF.

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

  • An AI analysis pipeline that reads any Excel file, generates charts using real aggregations and pivot tables, and displays a written insight below each chart.
  • A drag-and-drop dashboard where you reorder panels, remove charts, ask the AI for deeper analysis suggestions, and reset the layout anytime.
  • A multi-format export system that downloads your dashboard as interactive HTML, a static PNG, or a multi-page PDF.
  • An optional Secret Mission to push your skills further.

Are there any prerequisites?

This project is Part 2 of the AI-Powered Excel Dashboard series. You need a working project folder from Part 1 with app.py, a virtual environment with your Part 1 dependencies installed, and a Gemini API key.

Before We Start

Your Part 1 dashboard can turn an Excel file into charts, but it uses simple column-to-chart mapping. Before you start building, take a moment to think about where that approach falls short with real-world data and what an intelligent dashboard would do differently.

Verify Part 1 and Install New Dependencies

Your Part 1 dashboard is the foundation for everything you build in this project. If it doesn't run cleanly, nothing you layer on top will work.

You also need two new packages for the features ahead. streamlit-dnd adds drag-and-drop panel reordering, and Pillow handles PNG and PDF image stitching for multi-format export.

In this step, get ready to:
  • Confirm your Part 1 app runs and renders charts.
  • Pin Streamlit and install streamlit-dnd and Pillow.
  • Smoke test drag-and-drop with a temporary file.
Confirm Part 1 runs

Before adding anything new, let's make sure your Part 1 app still works. You'll re-run it and upload a test Excel file to confirm charts render.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type Cursor and press Enter to open it. If you use VS Code, type Visual Studio Code instead.
  • Click File in the top menu bar, then click Open Folder.
  • Select your Part 1 project folder and click Open.
  • Open the integrated terminal by pressing Ctrl+` (macOS and Windows).

Your virtual environment needs to be active before running the app.

  • Activate your virtual environment by running this command:

macOS

source venv/bin/activate

Windows

venv\Scripts\activate

You should see (venv) appear at the start of your terminal prompt.

Different venv folder name?

If you named your virtual environment something other than venv in Part 1, replace venv with your folder name in the command above.

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

What does this command do?

Streamlit starts a local web server and opens your browser to http://localhost:8501. If the browser doesn't open automatically, copy the URL from the terminal and paste it into your browser.

  • Upload a test Excel file using the file uploader in the app.
  • Confirm that your Part 1 charts render on the page.

You should see Plotly charts appear based on your uploaded data. The theme selector and export should also work as they did in Part 1.

  • Press Ctrl+C in the terminal to stop the Streamlit server.

App won't start?

  • Make sure (venv) appears in your terminal prompt. Without the virtual environment active, Python can't find your installed packages.
  • If you see a port-in-use error, another process is already running on port 8501. Close any other Streamlit tabs or terminal sessions, then try again.
  • help me troubleshoot why my Part 1 Streamlit app won't start
Pin Streamlit and install new packages

The streamlit-dnd library was built and tested against Streamlit 1.58.0. It hooks into internal DOM selectors that can change between Streamlit versions, so pinning avoids compatibility issues.

  • Install the pinned version of Streamlit along with the two new packages by running this command:
pip install streamlit==1.58.0 streamlit-dnd==0.1.1 Pillow

What does this command do?

  • streamlit==1.58.0 pins Streamlit to the exact version streamlit-dnd was tested against. Your Part 1 install may have been a different version, but this ensures drag-and-drop works reliably.
  • streamlit-dnd==0.1.1 adds HTML5 drag-and-drop reordering for Streamlit container children.
  • Pillow is Python's image processing library. You'll use it to stitch chart images into a single PNG and to build multi-page PDFs.
  • Verify streamlit-dnd installed correctly by running:
pip show streamlit-dnd

You should see Version: 0.1.1 in the output.

  • Verify Pillow installed by running:
pip show Pillow

You should see a Version: line followed by a version number.

Install failed?

  • Check that (venv) is showing in your terminal prompt. Running pip install outside the virtual environment installs packages globally and can cause version conflicts.
  • If you see a network error, check your internet connection and try the command again.
  • help me troubleshoot a pip install failure for streamlit-dnd and Pillow
Smoke test streamlit-dnd

Before wiring drag-and-drop into your real app, let's test it in isolation. You'll create a throwaway file, confirm items can be dragged, then delete it.

  • Create a new file called test_dnd.py in your project folder by running:

macOS

touch test_dnd.py

Windows

New-Item test_dnd.py
  • Confirm the file exists by running:
ls

You should see test_dnd.py alongside app.py in the output.

  • Open test_dnd.py in the editor and paste this code:
import streamlit as st
from streamlit_dnd import dnd, apply_move

st.title("Drag-and-Drop Test")

# Initialize a list of draggable items
if "items" not in st.session_state:
    st.session_state["items"] = {"todo": ["Task A", "Task B", "Task C"]}

# Render each item inside a keyed parent container
with st.container(key="todo", border=True):
    for item in st.session_state["items"]["todo"]:
        with st.container(key=f"item_{item}", border=True):
            st.write(item)

# Wire drag-and-drop after rendering
event = dnd("todo")
if event:
    apply_move(event, st.session_state["items"])
    st.rerun()

What does this code do?

  • The items are stored in st.session_state as a dictionary with a "todo" key pointing to a list. This is the data structure streamlit-dnd expects.
  • Each item is rendered inside a keyed st.container. The key gives each item a stable identity so the library can track which item was dragged.
  • dnd("todo") is called AFTER the containers are rendered. It wires HTML5 drag handlers onto the existing DOM elements.
  • When a drop happens, apply_move() updates the Python list in place and st.rerun() refreshes the page to show the new order.
  • Save test_dnd.py.

Before you run this, what do you expect to see in the browser? Think about what three items in a bordered container would look like.

  • Run the test file by running:
streamlit run test_dnd.py

You should see three bordered cards labeled Task A, Task B, and Task C. Drag one card to a different position and release it. The card stays in its new position after the page reruns.

  • Press Ctrl+C in the terminal to stop the server.
  • Delete the test file now that drag-and-drop is confirmed working. Right-click test_dnd.py in the Cursor sidebar and select Delete.

Drag-and-drop not working?

  • If you see ModuleNotFoundError, the install from the previous substep did not succeed. Run pip show streamlit-dnd to confirm the package is installed.
  • If the items appear but won't drag, check that your Streamlit version is 1.58.0 by running pip show streamlit. A different version may have changed the DOM selectors that streamlit-dnd relies on.
  • help me debug why streamlit-dnd drag-and-drop is not working in my test file

Your environment is ready with all the new tools. Next up, you'll replace the simple column-to-chart mapping with an AI-powered analysis pipeline that generates real aggregations and insights.

Build AI-Powered Data Analysis and Smart Panels

Your environment is locked in and your new dependencies are ready. In Part 1, your dashboard mapped columns to charts directly: find a numeric column, find a categorical column, make a bar chart. That breaks down with real-world Excel data that has multiple sheets, missing values, and data that needs aggregations and pivots to tell a useful story.

In this step, you will replace your Part 1 code with an AI-powered analysis pipeline. Gemini reads the data schema, generates an analysis plan with groupings and pivots, and your app executes those operations in pandas to build professional charts with written insights.

In this step, get ready to:
  • Wire sheet selection and automatic data cleaning for messy Excel files.
  • Build an AI pipeline that sends data to Gemini and gets back structured chart plans.
  • Render chart panels with insights and remove buttons.
Add sheet selection and data cleaning

Part 1's column-to-chart approach only works on perfectly clean, single-sheet spreadsheets. Real Excel files have multiple sheets, empty rows, and text columns that are secretly numbers. Your new app needs to handle all of that automatically.

You are going to replace the entire contents of app.py with a new version, built section by section.

  • Switch to your editor. Select all code in app.py and delete it.
  • Paste the new foundation by adding this code:
import streamlit as st
import pandas as pd
import plotly.express as px
import plotly.io as pio
import json
import uuid
import io
from google import genai
from google.genai import types
from streamlit_dnd import dnd, apply_move

# --- Configuration ---
st.set_page_config(page_title="Interactive AI Dashboard Builder", layout="wide")

GEMINI_API_KEY = "your-api-key-here"
client = genai.Client(api_key=GEMINI_API_KEY)
THEMES = {
    "Default": "plotly",
    "Dark": "plotly_dark",
    "Minimal": "plotly_white",
    "Presentation": "presentation",
}

CHART_BUILDERS = {
    "bar": lambda df, x, y, title, tpl: px.bar(df, x=x, y=y, title=title, template=tpl),
    "line": lambda df, x, y, title, tpl: px.line(df, x=x, y=y, title=title, template=tpl),
    "scatter": lambda df, x, y, title, tpl: px.scatter(df, x=x, y=y, title=title, template=tpl),
    "pie": lambda df, x, y, title, tpl: px.pie(df, names=x, values=y, title=title, template=tpl),
    "histogram": lambda df, x, y, title, tpl: px.histogram(df, x=x, title=title, template=tpl),
    "box": lambda df, x, y, title, tpl: px.box(df, x=x, y=y, title=title, template=tpl),
}

What does this code set up?

The imports bring in everything the app needs: Streamlit for the UI, Plotly for charts, pandas for data manipulation, and the google-genai client for Gemini.

  • THEMES maps friendly names to Plotly template strings so the learner can switch chart styles from a dropdown.
  • CHART_BUILDERS maps chart type names (like "bar" or "pie") to Plotly Express builder functions. Gemini will return chart type names, and this dict turns those names into real charts.
  • Replace your-api-key-here with your actual Gemini API key from Part 1.
  • Below the CHART_BUILDERS dict, add this code to initialize session state and create a basic app skeleton:
# Initialize session state keys
def init_session_state():
    if "panels" not in st.session_state:
        st.session_state["panels"] = {"charts": []}
    if "original_order" not in st.session_state:
        st.session_state["original_order"] = []
    if "suggestions" not in st.session_state:
        st.session_state["suggestions"] = []
    if "df" not in st.session_state:
        st.session_state["df"] = None
    if "theme" not in st.session_state:
        st.session_state["theme"] = "plotly"
    if "analyzed" not in st.session_state:
        st.session_state["analyzed"] = False

# --- Main App ---
init_session_state()
st.title("Interactive AI Dashboard Builder")

with st.sidebar:
    st.header("Controls")
    uploaded_file = st.file_uploader("Upload Excel File", type=["xlsx", "xls"])
    if uploaded_file is not None:
        xls = pd.ExcelFile(uploaded_file)
        sheet_names = xls.sheet_names
        if len(sheet_names) > 1:
            selected_sheet = st.selectbox("Select Sheet", sheet_names)
        else:
            selected_sheet = sheet_names[0]

What does this code do?

  • init_session_state() creates session state keys that persist across Streamlit reruns. The panels key uses a dict with a "charts" list because streamlit-dnd expects that exact structure.
  • The sidebar creates a file uploader that accepts .xlsx and .xls files. If the uploaded file has multiple sheets, a dropdown lets the user pick one.
  • Save app.py.
  • Run the app to confirm the foundation loads by running this command in your terminal:
streamlit run app.py

You should see "Interactive AI Dashboard Builder" as the page title. The sidebar shows a file uploader labeled "Upload Excel File". Upload a multi-sheet Excel file and a "Select Sheet" dropdown appears.

Seeing an import error?

  • Make sure your virtual environment is activated. You should see (venv) at the start of your terminal prompt.
  • Confirm streamlit-dnd is installed. Run pip show streamlit-dnd and check it returns version info.

Why is Streamlit not loading my app?

  • Above the # --- Main App --- comment, add these two data preparation functions:
# Drop empty rows/columns, try to parse dates and numbers
def clean_dataframe(df):
    df = df.dropna(how="all", axis=0).dropna(how="all", axis=1)
    for col in df.columns:
        if df[col].dtype == "object":
            try:
                df[col] = pd.to_datetime(df[col])
            except (ValueError, TypeError):
                try:
                    df[col] = pd.to_numeric(df[col], errors="coerce")
                except (ValueError, TypeError):
                    pass
    return df

# Build a summary of column types and samples for the AI prompt
def get_schema_summary(df):
    col_info = []
    for col in df.columns:
        dtype = str(df[col].dtype)
        non_null = df[col].count()
        sample = df[col].dropna().head(3).tolist()
        if df[col].dtype == "object":
            nunique = df[col].nunique()
            col_info.append(f"- {col} (type: {dtype}, non-null: {non_null}, unique values: {nunique}): samples {sample}")
        else:
            col_info.append(f"- {col} (type: {dtype}, non-null: {non_null}): samples {sample}")
    return "\n".join(col_info)

What do these functions do?

  • clean_dataframe() strips fully empty rows and columns, then attempts to convert text columns to dates or numbers. This handles messy spreadsheets where a "price" column was saved as text.
  • get_schema_summary() builds a plain-text description of each column: its data type, how many non-null values it has, sample values, and unique counts for text columns. This summary gets sent to Gemini so it understands the data without seeing every row.
  • Now wire the sidebar to use these functions. Find the with st.sidebar: block in your code and replace the entire block with this expanded version:
with st.sidebar:
    st.header("Controls")
    uploaded_file = st.file_uploader("Upload Excel File", type=["xlsx", "xls"])
    if uploaded_file is not None:
        xls = pd.ExcelFile(uploaded_file)
        sheet_names = xls.sheet_names
        if len(sheet_names) > 1:
            selected_sheet = st.selectbox("Select Sheet", sheet_names)
        else:
            selected_sheet = sheet_names[0]
        # Read and clean the selected sheet
        if st.session_state["df"] is None or not st.session_state["analyzed"]:
            raw_df = pd.read_excel(uploaded_file, sheet_name=selected_sheet)
            df = clean_dataframe(raw_df)
            st.session_state["df"] = df
    theme_name = st.selectbox("Theme", list(THEMES.keys()))
    st.session_state["theme"] = THEMES[theme_name]

What changed in the sidebar?

The sidebar now reads the selected sheet with pd.read_excel(), runs it through clean_dataframe(), and stores the result in session state. The if st.session_state["df"] is None guard prevents re-reading the file on every Streamlit rerun.

A Theme dropdown also appears below the uploader, letting you switch between chart styles like "Dark" and "Minimal".

  • Save app.py. Your Streamlit app hot-reloads.
  • Upload an Excel file. You should now see the sheet selector (if the file has multiple sheets) and a Theme dropdown in the sidebar.

Don't see the Theme dropdown?

  • Make sure the theme_name = st.selectbox("Theme", ...) line is inside with st.sidebar: but outside the if uploaded_file is not None: block. Check the indentation matches the code above exactly.

Why is my sidebar not showing the theme dropdown?

Build the AI analysis pipeline

The data is cleaned and stored. Now you need the intelligence layer. You will build a function that sends the data schema to Gemini and gets back a structured JSON analysis plan. Then you will build the function that executes each plan item as a real pandas operation.

  • Above the # --- Main App --- comment (below get_schema_summary), add the analyze_data function:
def analyze_data(df):
    schema = get_schema_summary(df)
    prompt = f"""You are an expert data analyst. Given this dataset, create a professional dashboard analysis plan.
Dataset: {len(df)} rows, {len(df.columns)} columns
Columns:
{schema}
Generate 3-5 analysis items that would create a professional dashboard. Focus on meaningful aggregations, not raw column plots. Think like a business analyst: pivots, groupings, top-N rankings, trends over time, distribution analysis.
Return a JSON array of objects. Each object must have:
- chart_type: one of "bar", "line", "scatter", "pie", "histogram", "box"
- title: a professional chart title (e.g., "Monthly Revenue by Product Category", not "amount by date")
- aggregation: describe the pandas operation in plain English (e.g., "group by category column, sum the amount column, sort descending, take top 10")
- groupby_column: the column to group by (or null if not applicable)
- value_column: the column to aggregate (or null for histogram)
- agg_function: one of "sum", "mean", "count", "median", "max", "min" (or null for histogram)
- sort_descending: true or false
- top_n: number of top results to show (or null for all)
- insight: a specific, data-aware observation (reference actual values from the sample data where possible, e.g., "Food is the largest category at 42% of total spending")
Only use column names that exist in the schema above. Make insights specific and quantitative when possible."""
    response = client.models.generate_content(
        model="gemini-2.0-flash",
        contents=prompt,
        config=types.GenerateContentConfig(
            response_mime_type="application/json",
            temperature=0.5,
        ),
    )
    return json.loads(response.text)

What does analyze_data do?

This function sends Gemini a summary of your data (column names, types, sample values) and asks it to think like a business analyst. Instead of just pairing columns, Gemini plans real aggregations: "group sales by month and sum", "top 5 categories by revenue".

The key is response_mime_type="application/json". This tells Gemini to return structured JSON instead of freeform text, so you can parse the response directly with json.loads().

  • Below analyze_data, add the execute_analysis function that turns each AI plan item into real pandas operations:
def execute_analysis(analysis_item, df):
    try:
        groupby_col = analysis_item.get("groupby_column")
        value_col = analysis_item.get("value_column")
        agg_func = analysis_item.get("agg_function", "sum")
        sort_desc = analysis_item.get("sort_descending", True)
        top_n = analysis_item.get("top_n")
        if analysis_item["chart_type"] == "histogram":
            return df, analysis_item.get("groupby_column") or value_col, None
        if groupby_col and value_col and agg_func:
            agg_df = df.groupby(groupby_col, as_index=False).agg({value_col: agg_func})
            if sort_desc:
                agg_df = agg_df.sort_values(value_col, ascending=False)
            if top_n:
                agg_df = agg_df.head(top_n)
            return agg_df, groupby_col, value_col
        elif groupby_col:
            counts = df[groupby_col].value_counts().reset_index()
            counts.columns = [groupby_col, "count"]
            if top_n:
                counts = counts.head(top_n)
            return counts, groupby_col, "count"
        else:
            return df, value_col, None
    except Exception:
        return None, None, None

What does execute_analysis do?

This function takes one item from Gemini's analysis plan and runs the actual pandas operation. It handles three patterns: groupby().agg() for aggregations, value_counts() for counting categories, and raw data pass-through for histograms.

The try/except wrapping ensures that if Gemini suggests a column name that does not exist, the function returns None gracefully instead of crashing.

  • Save app.py. Your app hot-reloads. Confirm the app still shows the title and sidebar with no red error messages.

Seeing a red error after saving?

  • Check that both analyze_data and execute_analysis are defined above the # --- Main App --- comment, not inside it.
  • Make sure the triple-quoted prompt in analyze_data has matching opening and closing """.

Why is my app showing an error after adding the AI functions?

  • Now wire the sidebar to trigger AI analysis when a file is uploaded. In the sidebar, find this block:
        # Read and clean the selected sheet
        if st.session_state["df"] is None or not st.session_state["analyzed"]:
            raw_df = pd.read_excel(uploaded_file, sheet_name=selected_sheet)
            df = clean_dataframe(raw_df)
            st.session_state["df"] = df
  • Replace that block with this expanded version that triggers Gemini analysis:
        if st.session_state["df"] is None or not st.session_state["analyzed"]:
            raw_df = pd.read_excel(uploaded_file, sheet_name=selected_sheet)
            df = clean_dataframe(raw_df)
            st.session_state["df"] = df
            with st.spinner("Analyzing your data with Gemini..."):
                try:
                    analysis_plan = analyze_data(df)
                    panels = []
                    for item in analysis_plan:
                        agg_df, x_col, y_col = execute_analysis(item, df)
                        if agg_df is not None:
                            panel = {
                                "id": str(uuid.uuid4())[:8],
                                "chart_type": item["chart_type"],
                                "title": item["title"],
                                "x": x_col,
                                "y": y_col,
                                "insight": item.get("insight", ""),
                                "agg_df": agg_df.to_dict("records") if isinstance(agg_df, pd.DataFrame) else None,
                            }
                            panels.append(panel)
                    st.session_state["panels"]["charts"] = panels
                    st.session_state["original_order"] = [p.copy() for p in panels]
                    st.session_state["analyzed"] = True
                except Exception as e:
                    st.error(f"Analysis error: {e}")
                    st.session_state["analyzed"] = True

What does the analysis wiring do?

  • After cleaning the data, it calls analyze_data(df) to get Gemini's analysis plan. Then it loops through each plan item, runs execute_analysis(), and builds a panel config dict with the aggregated data.
  • Each panel stores its aggregated DataFrame as a list of dicts (agg_df.to_dict("records")) so it serializes cleanly in session state. The original panel order is saved separately for a "Reset Layout" feature you will add later.
  • Below execute_analysis, add these two figure-building functions (still above # --- Main App ---):
def build_figure_from_analysis(analysis_item, agg_df, x_col, y_col, template):
    chart_type = analysis_item["chart_type"]
    title = analysis_item["title"]
    builder = CHART_BUILDERS.get(chart_type)
    if builder is None or agg_df is None:
        return None
    try:
        fig = builder(agg_df, x_col, y_col, title, template)
        fig.update_layout(height=400)
        return fig
    except Exception:
        return None

def build_figure_for_panel(panel, df, template):
    chart_type = panel["chart_type"]
    builder = CHART_BUILDERS.get(chart_type)
    if builder is None:
        return None
    try:
        agg_df = panel.get("agg_df")
        if agg_df is not None and isinstance(agg_df, list):
            agg_df = pd.DataFrame(agg_df)
        elif agg_df is None:
            agg_df = df
        fig = builder(agg_df, panel["x"], panel.get("y"), panel["title"], template)
        fig.update_layout(height=400)
        return fig
    except Exception:
        return None

Why two figure-building functions?

build_figure_from_analysis() takes a raw analysis item and freshly aggregated data. build_figure_for_panel() takes a stored panel config and reconstructs the figure from the saved agg_df data.

Since agg_df is stored as a list of dicts in session state, build_figure_for_panel converts it back to a DataFrame before passing it to the Plotly builder.

  • Save app.py. Upload an Excel file. You should see the spinner "Analyzing your data with Gemini..." appear in the sidebar while Gemini processes your data.

Seeing "Analysis error" in the sidebar?

  • Double-check that your GEMINI_API_KEY at the top of app.py is set to your actual Gemini API key, not the placeholder your-api-key-here.
  • Confirm the gemini-2.0-flash model is available in your region. Try running a quick test in a Python shell with your key.

Why is my Gemini analysis failing?

Render panels with insights and remove buttons

Gemini generated the analysis plan and your app executed the pandas operations. The panel data is sitting in session state. Now you need to render each panel as an interactive chart card with a title, a Plotly chart, an insight caption, and a remove button.

  • At the very bottom of app.py (below the with st.sidebar: block), add the panel rendering code:
# Render each panel as a bordered card
if st.session_state["df"] is not None and st.session_state["panels"]["charts"]:
    template = st.session_state["theme"]
    with st.container(key="charts", border=False):
        for panel in st.session_state["panels"]["charts"]:
            with st.container(key=f"panel_{panel['id']}", border=True):
                col_title, col_remove = st.columns([10, 1])
                with col_title:
                    st.subheader(panel["title"])
                with col_remove:
                    if st.button("x", key=f"remove_{panel['id']}"):
                        st.session_state["panels"]["charts"] = [
                            p for p in st.session_state["panels"]["charts"]
                            if p["id"] != panel["id"]
                        ]
                        st.rerun()
                fig = build_figure_for_panel(panel, st.session_state["df"], template)
                if fig:
                    st.plotly_chart(fig, use_container_width=True)
                else:
                    st.warning(f"Could not render {panel['chart_type']} chart.")
                if panel.get("insight"):
                    st.caption(panel["insight"])
elif st.session_state["df"] is None:
    st.info("Upload an Excel file in the sidebar to get started.")

What does the rendering code do?

  • Each panel renders inside a bordered st.container with a unique key based on its ID. The key is essential because streamlit-dnd uses these keys to identify draggable items in the next step.
  • The st.columns([10, 1]) layout places the chart title on the left and a small "x" remove button on the right. Clicking remove filters that panel out of the list and calls st.rerun() to refresh.
  • st.caption() renders the AI-generated insight below each chart as subtle, italicized text.

Before you test, think about what you expect to see. How many charts do you think Gemini will generate from your Excel file? Will the titles be raw column names or descriptive analysis titles?

  • Save app.py.
  • If your app still shows results from a previous upload, close the browser tab and restart with:
streamlit run app.py
  • Upload a multi-column Excel file.

After the spinner completes, you should see 3 to 5 chart panels. Each panel has a descriptive title like "Monthly Revenue by Product Category" (not just column names). Below each chart is a one-line insight caption. Click the "x" button on any panel to remove it.

Charts not appearing after upload?

  • Make sure the rendering code is at the very bottom of app.py, below and outside the with st.sidebar: block. Check that it is not indented inside the sidebar.
  • If you see "Could not render chart" warnings, Gemini may have suggested a column that does not exist in your file. Try a different Excel file with clearly named columns like "Date", "Amount", "Category".
  • If the page is blank with no info message, confirm the elif st.session_state["df"] is None: line is at the same indentation level as the if above it.

Why are my chart panels not rendering after Gemini analysis?

\u2714\uFE0F Awesome, I've got everything!

Great. Double-check you have saved app.py and that your charts are rendering with insights.

\u24E7 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:

  1. Imports (10 lines starting with import streamlit as st).
  2. Configuration block: st.set_page_config, GEMINI_API_KEY, client, THEMES, CHART_BUILDERS.
  3. init_session_state() function with keys: panels, original_order, suggestions, df, theme, analyzed.
  4. clean_dataframe() function.
  5. get_schema_summary() function.
  6. analyze_data() function with the Gemini prompt and response_mime_type="application/json".
  7. execute_analysis() function with groupby, value_counts, and histogram handling.
  8. build_figure_from_analysis() and build_figure_for_panel() functions.
  9. Main app section: init_session_state() call, st.title(...), with st.sidebar: block with file uploader, sheet selector, analysis trigger, and theme selector.
  10. Panel rendering if/elif block at the bottom with the chart loop and info message.

Your dashboard now turns any Excel file into professional, AI-analyzed charts with written insights. Next up, you will make those panels draggable so users can customize their dashboard layout.

Enable Drag-and-Drop Reordering

Your dashboard now generates professional charts with AI-driven aggregations and insights. But the panels are locked in whatever order Gemini chose.

In this step, you will wire streamlit-dnd to make every panel draggable. You will also add a Reset Layout button so users can snap panels back to their original order.

In this step, get ready to:
  • Wire dnd() to the panel rendering container so panels become draggable.
  • Add a Reset Layout button that restores the original panel order.
  • Test dragging panels to new positions and resetting their order.
Wire dnd() to the panel container

The streamlit-dnd library works by attaching HTML5 drag handlers to Streamlit containers that have already been rendered on the page. This means the dnd() call must come AFTER the rendering loop, not before it.

  • In app.py, scroll to the bottom of the file and find this line:
elif st.session_state["df"] is None:
  • Add the following code directly ABOVE that elif line, still inside the with st.container(key="charts", border=False): block:
    # Enable drag-and-drop on the charts container
    event = dnd("charts")
    if event:
        apply_move(event, st.session_state["panels"])
        st.rerun()

What does this code do?

  • dnd("charts") attaches drag handlers to every child of the container with key="charts". When the user drops a panel in a new position, it returns a DropEvent.
  • apply_move(event, st.session_state["panels"]) reorders the "charts" list inside the panels dict to match the drop position.
  • st.rerun() refreshes the page so the panels render in their new order.

Why is dnd() called after rendering?

streamlit-dnd wires drag handlers onto existing DOM elements using internal Streamlit CSS selectors like st-key-* classes. If you call dnd() before the containers exist on the page, there is nothing to attach to.

  • Save app.py.
  • Return to your terminal and run the app by running:
streamlit run app.py
  • Upload an Excel file and wait for the charts to load.
  • Drag a chart panel to a new position. The panel should snap into its new spot after the page reruns.

Panels not dragging?

  • Make sure your dnd("charts") call is AFTER the for panel loop, not before it. It must be inside the with st.container(key="charts", border=False): block, indented at the same level as the for loop.
  • Check that you have streamlit==1.58.0 installed. The streamlit-dnd library was tested against this version.
  • Confirm each panel container has a unique key= argument. Without keys, drag events cannot identify which panel moved.

Why can't I drag my dashboard panels?

Add a Reset Layout button

Users need a way to undo their reordering. Your app already saves the original panel order during analysis in st.session_state["original_order"]. Now you will add a sidebar button that restores it.

  • In app.py, find this line inside the with st.sidebar: block:
    st.session_state["theme"] = THEMES[theme_name]
  • Add the following code below that line, still inside the with st.sidebar: block:
    # Show Reset Layout button only when data has been analyzed
    if st.session_state["df"] is not None and st.session_state["analyzed"]:
        if st.button("Reset Layout"):
            if st.session_state["original_order"]:
                # Keep only panels that still exist (some may have been removed)
                current_ids = {p["id"] for p in st.session_state["panels"]["charts"]}
                original = [p for p in st.session_state["original_order"] if p["id"] in current_ids]
                # Append any panels added after the original analysis
                new_panels = [p for p in st.session_state["panels"]["charts"] if p["id"] not in {o["id"] for o in original}]
                st.session_state["panels"]["charts"] = original + new_panels
                st.rerun()

What does this code do?

  • The button only appears after data has been analyzed, guarded by the analyzed check.
  • It filters original_order to only include panels that still exist. If the user removed a panel, it stays removed.
  • Any panels added after the initial analysis (which are not in original_order) get appended at the end so nothing is lost.
  • Save app.py.
  • Return to your browser. The app auto-refreshes.
  • Drag a panel to a new position, then click Reset Layout in the sidebar. The panels should snap back to their original order.

Reset Layout button not showing?

  • Make sure you have uploaded an Excel file and the analysis has completed. The button is hidden until st.session_state["analyzed"] is True.
  • Check that the new code is indented inside the with st.sidebar: block. Code outside the sidebar block renders in the main area.

Why isn't my Reset Layout button appearing in the sidebar?

✔️ Awesome, I've got everything!

Make sure you have saved app.py. Both code additions are small, so a quick scroll-through should confirm everything is in place.

ⓧ I'd like to double check the full code

Your app.py is too long to show in a single block. Scroll through your file and confirm these two additions are present in order:

  • Inside with st.sidebar:, after the theme selector line (st.session_state["theme"] = THEMES[theme_name]): the Reset Layout button block, guarded by if st.session_state["df"] is not None and st.session_state["analyzed"]:.
  • Inside with st.container(key="charts", border=False):, after the for panel loop: the event = dnd("charts") block with apply_move and st.rerun().
Test drag, reorder, and reset

Before you run through the full test, think about this: if you drag the first panel to the last position, remove a panel, then click Reset Layout, will the removed panel come back?

  • Upload an Excel file (if you do not already have one loaded) and wait for charts to appear.
  • Drag the first chart panel to the last position. Confirm it stays in its new spot after the page reruns.
  • Remove a panel by clicking its x button.
  • Click Reset Layout in the sidebar.

The remaining panels snap back to their original order. The removed panel does NOT come back. That is the filtering logic at work: the reset only restores order for panels that still exist.

Why doesn't Reset Layout bring back removed panels?

The reset filters original_order against current_ids. If a panel ID is no longer in the active panel list, it is excluded. Reset Layout restores the ordering, not the content.

Your dashboard is now fully interactive. Users can drag panels to tell their own data story. Next up, you will add an AI-powered deep-dive feature that suggests entirely new charts the initial analysis missed.

Add AI-Powered Deep-Dive Suggestions

Your dashboard now has draggable panels that persist their order across interactions. The initial AI analysis is solid, but every dataset has more stories hiding under the surface.

In this step, you will add a "Suggest Charts" button that sends your existing analysis context back to Gemini and asks for deeper analysis angles the initial pass missed. Cross-column correlations, distribution comparisons, second-level groupings. Each suggestion appears as a card the user can add to the dashboard or dismiss.

In this step, get ready to:
  • Build a deep-dive prompt that tells Gemini what analysis you already have so it suggests something new.
  • Wire a "Suggest Charts" button and render suggestion cards with Add and Dismiss actions.
  • Test the full suggestion flow end to end.
Build the deep-dive suggestion function

The initial analyze_data function works blind. It sees the dataset schema and generates a plan from scratch. The deep-dive function is different. It also sends a summary of every chart already on the dashboard so Gemini knows what ground has been covered.

  • In app.py, scroll down to the build_figure_for_panel function. Find the closing return None at the end of that function.
  • Add the following new function below build_figure_for_panel:
def suggest_deep_dive(df, existing_panels):
    schema = get_schema_summary(df)
    # Summarize what analysis already exists on the dashboard
    existing_summary = []
    for p in existing_panels:
        existing_summary.append(f"- {p['title']} ({p['chart_type']}): insight: {p.get('insight', 'N/A')}")
    existing_text = "\n".join(existing_summary) if existing_summary else "None yet"
    # Ask Gemini for NEW analyses the current dashboard misses
    prompt = f"""You are an expert data analyst. Given this dataset and the analysis already performed, suggest 2-3 NEW deeper analyses.\n\nDataset: {len(df)} rows, {len(df.columns)} columns\nColumns:\n{schema}\n\nAnalysis already on dashboard:\n{existing_text}\n\nReturn a JSON array with chart_type, title, aggregation, groupby_column, value_column, agg_function, sort_descending, top_n, description fields. Only use column names from the schema above."""
    response = client.models.generate_content(
        model="gemini-2.0-flash",
        contents=prompt,
        config=types.GenerateContentConfig(
            response_mime_type="application/json",
            temperature=0.7,
        ),
    )
    return json.loads(response.text)

What does this function do?

The function builds a prompt with two key pieces of context. First, the full dataset schema (column names, types, sample values) so Gemini knows what data is available. Second, a summary of every chart already on the dashboard (titles, chart types, insights) so Gemini avoids repeating existing analysis.

The temperature=0.7 setting is slightly higher than the initial analysis (0.5). This encourages more creative, varied suggestions rather than safe, obvious ones.

Why send existing analysis to Gemini?

Without context about what charts already exist, Gemini would suggest the same obvious analyses it generated the first time. By including the existing panel summaries, you give Gemini a "do not repeat" list. The suggestions come back as deeper angles: correlations, distributions, or groupings the initial pass skipped.

  • Save app.py. The function is defined but nothing calls it yet. The next substep wires it to a sidebar button.
Wire the "Suggest Charts" button and suggestion cards

The function exists but the user has no way to trigger it. You need a button in the sidebar that calls suggest_deep_dive, stores the results, and then renders each suggestion as a card with Add and Dismiss options.

  • In app.py, scroll to the sidebar code. Find this line inside the if st.session_state["df"] is not None and st.session_state["analyzed"]: block:
        if st.button("Reset Layout"):
  • Add the following code directly above the if st.button("Reset Layout"): line, so the "Suggest Charts" button appears first:
        # Button to request deeper analysis from Gemini
        if st.button("Suggest Charts"):
            with st.spinner("Asking Gemini for deeper analysis..."):
                try:
                    suggestions = suggest_deep_dive(
                        st.session_state["df"],
                        st.session_state["panels"]["charts"],
                    )
                    st.session_state["suggestions"] = suggestions
                except Exception as e:
                    st.error(f"Gemini error: {e}")

What does this code do?

When the learner clicks "Suggest Charts", the button calls suggest_deep_dive with the current DataFrame and existing panel list. The results are stored in st.session_state["suggestions"]. The try/except block catches any Gemini API errors and shows them in the sidebar instead of crashing the app.

  • Save app.py and return to your running Streamlit app in the browser.
  • Upload an Excel file if you do not already have one loaded.
  • In the sidebar, you should see a new Suggest Charts button above Reset Layout. Click it.
  • A spinner reading "Asking Gemini for deeper analysis..." appears briefly, then disappears. The suggestions are stored in session state but nothing renders yet.

Seeing a Gemini error in the sidebar?

  • Make sure your Gemini API key is still valid. Check that GEMINI_API_KEY at the top of app.py matches the key from Google AI Studio.
  • Confirm you uploaded a file and that the initial analysis completed before clicking "Suggest Charts". The button only appears after analysis finishes.
  • Still stuck? Why is my Suggest Charts button throwing a Gemini error?

Now you need the cards that display each suggestion and let the user act on it.

  • In app.py, find the closing line of the if st.button("Reset Layout"): block (the last st.rerun() inside it).
  • Add the following code below the Reset Layout block to render suggestion cards:
        # Render suggestion cards when suggestions exist
        if st.session_state["suggestions"]:
            st.subheader("Chart Suggestions")
            for i, sug in enumerate(st.session_state["suggestions"]):
                with st.container(border=True):
                    st.markdown(f"**{sug.get('title', 'Untitled')}**")
                    st.caption(sug.get("description", sug.get("insight", "")))
                    col_add, col_dismiss = st.columns(2)
                    with col_add:
                        if st.button("Add", key=f"add_sug_{i}"):
                            # Execute the aggregation and build a new panel
                            agg_df, x_col, y_col = execute_analysis(sug, st.session_state["df"])
                            if agg_df is not None:
                                new_panel = {
                                    "id": str(uuid.uuid4())[:8],
                                    "chart_type": sug["chart_type"],
                                    "title": sug["title"],
                                    "x": x_col,
                                    "y": y_col,
                                    "insight": sug.get("description", sug.get("insight", "")),
                                    "agg_df": agg_df.to_dict("records") if isinstance(agg_df, pd.DataFrame) else None,
                                }
                                st.session_state["panels"]["charts"].append(new_panel)
                            st.session_state["suggestions"].pop(i)
                            st.rerun()
                    with col_dismiss:
                        if st.button("Dismiss", key=f"dismiss_sug_{i}"):
                            st.session_state["suggestions"].pop(i)
                            st.rerun()

What does this code do?

  • Each suggestion renders as a bordered card showing its title (bold) and description (caption).
  • Clicking Add calls execute_analysis (the same function the initial analysis uses) to run the pandas aggregation, builds a new panel dict, and appends it to the panels list.
  • Clicking Dismiss removes the suggestion from session state without adding anything to the dashboard.
  • Both actions call st.rerun() so the UI refreshes immediately.

Why reuse execute_analysis instead of building new logic?

The suggestions share the same JSON shape as the initial analysis items: chart_type, groupby_column, value_column, agg_function. By designing the Gemini prompt to return the same structure, you can reuse execute_analysis without writing new aggregation code.

  • Save app.py.

Seeing a DuplicateWidgetID error?

  • Each button inside the loop needs a unique key. Check that your Add button uses key=f"add_sug_{i}" and your Dismiss button uses key=f"dismiss_sug_{i}". Missing the f"" prefix means every button gets the same static string as its key.
  • Need more help? Why am I getting a DuplicateWidgetID error in my Streamlit suggestion cards?
Test the suggestion flow

Time to see the full deep-dive feature in action. Before you test, think about what you expect to happen.

  • Return to your running Streamlit app in the browser. Upload an Excel file if you do not have one loaded.
  • Wait for the initial AI analysis to complete so you have chart panels on the dashboard.

Before you click "Suggest Charts", predict: will the suggestions duplicate the charts already on the dashboard, or will they be different? Why?

  • Click Suggest Charts in the sidebar.

After the spinner finishes, 2-3 suggestion cards appear in the sidebar under a "Chart Suggestions" subheader. Each card shows a title, a description of what new insight it would reveal, and two buttons: Add and Dismiss. The suggestions are different from your existing charts because the prompt told Gemini what analysis already exists.

  • Click Add on one of the suggestion cards.

A new chart panel appears at the bottom of the dashboard with aggregated data and an insight caption. The suggestion card disappears from the sidebar.

  • Click Dismiss on another suggestion card. Confirm it disappears from the sidebar without adding a chart to the dashboard.

Suggestion cards not appearing?

  • Make sure the suggestion cards block is at the same indentation level as the "Suggest Charts" and "Reset Layout" button blocks (inside the if st.session_state["df"] is not None and st.session_state["analyzed"]: check).
  • Check that the suggestion cards code starts with if st.session_state["suggestions"]: (not if st.session_state["suggestion"]: without the trailing "s").
  • Still stuck? Why are my Streamlit suggestion cards not showing after clicking Suggest Charts?

✔️ Awesome, I've got everything!

Great. Double check that you have saved app.py.

ⓧ I'd like to double check the full code

Your app.py is too long to show in one block. Scroll through your file and confirm these sections appear in order:

  • Imports, config, THEMES, and CHART_BUILDERS at the top.
  • init_session_state, clean_dataframe, get_schema_summary, analyze_data, execute_analysis functions from previous steps.
  • build_figure_from_analysis and build_figure_for_panel functions.
  • The new suggest_deep_dive function (added in this step).
  • Main app code: init_session_state(), sidebar with upload, theme, Suggest Charts button (new), Reset Layout button, and suggestion cards (new).
  • Dashboard rendering section with panel containers, drag-and-drop via dnd("charts").

Your dashboard now discovers insights you might have missed. Upload a file, get the initial analysis, then hit "Suggest Charts" for deeper analysis whenever you want more. Next up, you will add theme switching and multi-format export so the finished dashboard can be downloaded as HTML, PNG, or PDF.

Add Theme Selector and Multi-Format Export

Your dashboard now generates AI-powered charts, supports drag-and-drop reordering, and suggests deeper analyses on demand. The last piece is sharing: right now, the dashboard only lives inside the browser tab.

This step confirms the theme selector already updates every panel, then builds a multi-format export system. You will add three export functions so users can download the finished dashboard as interactive HTML, a static PNG image, or a multi-page PDF.

In this step, get ready to:
  • Confirm the theme selector updates all chart panels dynamically.
  • Build export functions for HTML, PNG, and PDF formats.
  • Wire the export UI into the sidebar with a format selector and download button.
Confirm the theme selector updates all panels

The theme selector is already wired into your app. When you pick a different theme from the sidebar dropdown, Streamlit reruns the entire script, and every chart rebuilds with the new Plotly template stored in st.session_state["theme"].

  • Switch back to your terminal and run the app by running this command:
streamlit run app.py
  • Upload a test Excel file in the sidebar and wait for the AI analysis to generate chart panels.
  • In the sidebar, open the Theme dropdown and select Dark.

Every chart panel should immediately switch to a dark background. The titles, axes, and gridlines all reflect the dark Plotly template.

  • Try Minimal and Presentation as well to see how each template changes the chart styling.

Why does the theme update without extra code?

Streamlit reruns your entire script from top to bottom every time a widget value changes. When you pick a new theme, st.session_state["theme"] updates, and the rendering loop calls build_figure_for_panel with the new template for every panel.

This is one of Streamlit's biggest strengths: you write code as if it runs once, and the framework handles reactivity for you.

Theme not changing?

Make sure the THEMES dictionary at the top of app.py maps each name to a valid Plotly template string like "plotly_dark". Also confirm that your rendering loop reads template = st.session_state["theme"] before building figures.

Why isn't my theme selector changing my Plotly charts?

Build the HTML export function

The theme works. Now you need a way to get the dashboard out of the browser. You will build three export functions, starting with HTML.

  • Stop the running app with Ctrl+C in the terminal.
  • In app.py, scroll to the build_figure_for_panel function. Add the following export_html function directly below it:
def export_html(panels, df, template):
    # Start with HTML boilerplate and basic styling
    html_parts = [
        "<html><head><title>Dashboard Export</title>",
        "<style>body{font-family:Arial,sans-serif;max-width:1200px;margin:0 auto;padding:20px;} .insight{color:#555;font-style:italic;margin-bottom:30px;}</style>",
        "</head><body><h1>Dashboard Report</h1>",
    ]
    # Add each panel's chart and insight as HTML sections
    for panel in panels:
        fig = build_figure_for_panel(panel, df, template)
        if fig:
            html_parts.append(f"<h2>{panel['title']}</h2>")
            html_parts.append(fig.to_html(full_html=False, include_plotlyjs=\"cdn\"))
            if panel.get("insight"):
                html_parts.append(f'<p class=\"insight\">{panel["insight"]}</p>')
    html_parts.append("</body></html>")
    return "\n".join(html_parts)

What does this code do?

  • The function builds a complete HTML page by assembling parts into a list and joining them at the end.
  • fig.to_html(full_html=False, include_plotlyjs="cdn") returns just the chart's div element (not a full page) and loads the Plotly library from a CDN instead of embedding it. This keeps the file small while the charts stay fully interactive.
  • Each panel's insight text is added as an italic paragraph below its chart.

Before you can test HTML export, you need the sidebar UI that calls this function. Next, you will add the export section to the sidebar.

Add the export UI to the sidebar

The export section gives the user a format dropdown and a download button. It calls whichever export function matches their selection.

  • In app.py, scroll down to the sidebar section. Find the end of the suggestion cards block (the last st.button("Dismiss"...) section).
  • Add the following export section below the suggestion cards block, still inside the if st.session_state["df"] is not None and st.session_state["analyzed"]: block:
        # Export section in sidebar
        st.subheader("Export Dashboard")
        if st.session_state["panels"]["charts"]:
            export_format = st.selectbox("Format", ["HTML", "PNG", "PDF"])
            if st.button("Download Dashboard"):
                panels = st.session_state["panels"]["charts"]
                df = st.session_state["df"]
                template = st.session_state["theme"]
                # Generate the export in the selected format
                with st.spinner(f"Generating {export_format}..."):
                    if export_format == "HTML":
                        data = export_html(panels, df, template).encode()
                        mime = "text/html"
                        filename = "dashboard.html"
                    elif export_format == "PNG":
                        data = export_png(panels, df, template)
                        mime = "image/png"
                        filename = "dashboard.png"
                    else:
                        data = export_pdf(panels, df, template)
                        mime = "application/pdf"
                        filename = "dashboard.pdf"
                # Offer the generated file as a download
                st.download_button(
                    f"Save {export_format}",
                    data=data,
                    file_name=filename,
                    mime=mime,
                )

What does this code do?

  • st.selectbox("Format", ["HTML", "PNG", "PDF"]) renders a dropdown in the sidebar so the user picks their preferred format.
  • When the user clicks Download Dashboard, the code branches on the selected format, calls the matching export function, and feeds the result to st.download_button.
  • The PNG and PDF export functions do not exist yet. For now, only HTML export will work. You will add the other two next.
  • Save app.py.
  • Run the app by running this command:
streamlit run app.py
  • Upload an Excel file and wait for the charts to generate.
  • In the sidebar, scroll down to the new Export Dashboard section.
  • Make sure HTML is selected in the Format dropdown.
  • Click Download Dashboard.
  • A Save HTML button appears. Click it to download dashboard.html.
  • Open the downloaded file in your browser. You should see interactive Plotly charts (hover to see tooltips, zoom, pan) with insight text below each chart.

No Export Dashboard section in the sidebar?

The export section only appears when charts exist. Make sure you have uploaded a file and waited for the AI analysis to complete. Also check that the st.subheader("Export Dashboard") line is indented inside the if st.session_state["df"] is not None and st.session_state["analyzed"]: block.

Why isn't my export section showing up in the Streamlit sidebar?

Add PNG and PDF export functions

HTML export gives interactive charts, but sometimes you need a static image or a printable document. The PNG export uses Kaleido to render each chart as an image, then Pillow to stitch them into one tall image.

  • Stop the running app with Ctrl+C.
  • In app.py, find the export_html function you just added. Add the following export_png function directly below it:
def export_png(panels, df, template):
    # Render each panel's chart to PNG bytes
    images = []
    for panel in panels:
        fig = build_figure_for_panel(panel, df, template)
        if fig:
            fig.update_layout(width=1200, height=400)
            img_bytes = fig.to_image(format="png")
            images.append(img_bytes)
    if not images:
        return b""
    if len(images) == 1:
        return images[0]
    # Stitch all chart images vertically into one tall image
    from PIL import Image
    pil_images = [Image.open(io.BytesIO(img)) for img in images]
    total_height = sum(im.height for im in pil_images)
    max_width = max(im.width for im in pil_images)
    combined = Image.new("RGB", (max_width, total_height), "white")
    y_offset = 0
    for im in pil_images:
        combined.paste(im, (0, y_offset))
        y_offset += im.height
    buffer = io.BytesIO()
    combined.save(buffer, format="PNG")
    return buffer.getvalue()

What does this code do?

  • fig.to_image(format="png") uses Kaleido under the hood to render each Plotly figure as PNG bytes.
  • If there are multiple charts, Pillow opens each PNG, calculates the combined height, creates a blank white canvas, and pastes each image below the previous one.
  • The result is a single tall PNG with every chart stacked vertically.
  • Save app.py and run the app to test PNG export:
streamlit run app.py
  • Upload an Excel file and wait for charts to generate.
  • In the sidebar, select PNG from the Format dropdown.
  • Click Download Dashboard, then click Save PNG.
  • Open dashboard.png. You should see a tall static image with all your charts stacked vertically.

PNG export failing?

Kaleido requires Chrome or Chromium to be installed on your system. If you see an error about a missing browser, run kaleido_get_chrome in your terminal to install a compatible version. This was likely already set up from Part 1.

Why is my Kaleido PNG export failing in Streamlit?

PNG works. Now add the PDF export function. The approach is similar: render each chart to PNG, then use Pillow to save them as pages in a multi-page PDF.

  • Stop the running app with Ctrl+C.
  • In app.py, find the export_png function. Add the following export_pdf function directly below it:
def export_pdf(panels, df, template):
    # Render each chart to PNG, then convert to PDF pages
    images = []
    for panel in panels:
        fig = build_figure_for_panel(panel, df, template)
        if fig:
            fig.update_layout(width=1200, height=400)
            img_bytes = fig.to_image(format="png")
            images.append(img_bytes)
    if not images:
        return b""
    # Convert PNG bytes to RGB Pillow images for PDF conversion
    from PIL import Image
    pil_images = [Image.open(io.BytesIO(img)).convert("RGB") for img in images]
    buffer = io.BytesIO()
    if len(pil_images) == 1:
        pil_images[0].save(buffer, format="PDF")
    else:
        # save_all=True with append_images creates multi-page PDF
        pil_images[0].save(buffer, format="PDF", save_all=True, append_images=pil_images[1:])
    return buffer.getvalue()

What does this code do?

  • Like PNG export, each chart is rendered to PNG bytes via Kaleido. The key difference is the final assembly step.
  • .convert("RGB") ensures each image is in RGB mode. PDF format does not support RGBA (transparency), so this conversion prevents errors.
  • save(format="PDF", save_all=True, append_images=pil_images[1:]) tells Pillow to create a multi-page PDF. The first image becomes page 1, and every image in append_images becomes a subsequent page.
  • Save app.py and run the app:
streamlit run app.py
  • Upload an Excel file and wait for charts to generate.
  • Select PDF from the Format dropdown.
  • Click Download Dashboard, then click Save PDF.
  • Open dashboard.pdf. You should see a multi-page PDF with one chart per page.

PDF file is empty or won't open?

Make sure charts were generated before exporting. If the panels list is empty, the function returns empty bytes. Also double-check that .convert("RGB") is present on the Pillow image line. Without it, Pillow may fail silently on RGBA images.

Why is my Pillow multi-page PDF export producing an empty or corrupted file?

✔️ Awesome, I've got everything!

Great. Make sure you have saved app.py.

ⓧ I'd like to double check the full code

The full app.py file is long, so confirm the following sections are present in this order:

  • Imports (streamlit, pandas, plotly, json, uuid, io, genai, streamlit_dnd).
  • Configuration: THEMES dict and CHART_BUILDERS dict.
  • Helper functions: init_session_state, clean_dataframe, get_schema_summary.
  • AI functions: analyze_data, execute_analysis, build_figure_from_analysis, suggest_deep_dive.
  • Panel rendering: build_figure_for_panel.
  • Export functions (NEW): export_html, export_png, export_pdf.
  • Main app: init_session_state(), title, sidebar with upload, sheet selection, analysis, theme, Suggest Charts, Reset Layout, suggestion cards, and the Export Dashboard section (NEW).
  • Dashboard rendering: panel loop with dnd("charts") at the bottom.
Run the final integration test

All three export formats are working individually. Time to confirm everything works together in a single session.

Before you run through this, what do you think will happen if you change the theme, then export as HTML? Will the exported file use the new theme or the default?

  • Make sure the app is running (run streamlit run app.py if you stopped it).
  • Upload an Excel file and wait for AI analysis to generate charts.
  • Drag a panel to reorder the dashboard.
  • Change the theme to Dark.
  • Click Suggest Charts, then click Add on one of the suggestions.
  • Remove a panel by clicking its x button.
  • Export as HTML and open the file. Confirm the charts are interactive and use the Dark theme.
  • Export as PNG and open the file. Confirm a single image with all current charts stacked vertically.

The exported HTML file uses the Dark theme because export_html passes the current template to build_figure_for_panel. Whatever theme the dashboard shows on screen is exactly what gets exported.

Secret mission

AI-Powered Layout Suggestions

Your dashboard panels can be dragged around manually, but what if Gemini could suggest the best ordering for you? Add a Suggest Layout button that asks Gemini for an optimal panel arrangement based on data storytelling principles, then apply the recommended order in one click.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your local machine. Your Gemini API key uses the free tier, so there are no ongoing costs to worry about.

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • Your project folder with app.py (modified from Part 1).
  • Your virtual environment with all installed dependencies (Streamlit, Plotly, pandas, openpyxl, google-genai, Kaleido, Pillow, streamlit-dnd).
  • Your Gemini API key (free tier).

Keep everything running

No action needed. Choose this if you are still actively building or plan to extend your dashboard soon.

  • All resources are local. Your Gemini API key stays on the free tier with generous rate limits.
  • Your virtual environment and code are saved on your machine.

Pause - I'll come back to this later

Shut down running processes to free up memory. Your virtual environment and code stay saved locally.

  • If Streamlit is still running in your terminal, press Ctrl+C to stop it.
  • Close the terminal window.
  • When you are ready to come back, activate your virtual environment and run:
streamlit run app.py

Delete - I don't want to use this again

Remove all project resources and start fresh.

  • If Streamlit is still running, press Ctrl+C to stop it.
  • Deactivate your virtual environment by running:
deactivate
  • Delete your project folder. On macOS/Linux, run:
cd ~/Desktop
rm -rf your-project-folder
  • On Windows, run:
cd ~\Desktop
rmdir /s /q your-project-folder
  • Optionally, delete your Gemini API key. Open the Google Cloud API Credentials page.
  • Find the API key you used for this project and click the Actions icon.
  • Select Delete API key and confirm in the modal.

That's a Wrap!

That's a Wrap!

Nice work. You just transformed a static Excel charting tool into an intelligent dashboard builder powered by Gemini. Upload any messy spreadsheet, and your app analyzes the data, builds professional charts with written insights, lets you drag panels into any order, and exports the finished dashboard in three formats.

You've learned how to:

  • Build an AI-powered data analysis pipeline that sends a dataset's schema and sample values to Gemini, receives structured JSON analysis plans with aggregations and pivots, executes them in pandas, and renders Plotly charts with written insight summaries.
  • Wire drag-and-drop reordering using streamlit-dnd with session state persistence, plus an AI-powered deep-dive suggestion feature that recommends new analysis angles the initial pass missed.
  • Build a multi-format export system that downloads your dashboard as interactive HTML, a stitched PNG image, or a multi-page PDF using Kaleido and Pillow.
  • Completed an optional Secret Mission to push your skills further.

Ready to quiz yourself?