Exporting Scored Trade Areas to XLSX for Stakeholders

This page solves one exact task: writing a scored, ranked GeoDataFrame into a multi-sheet XLSX workbook — a ranked shortlist, a per-criterion score matrix, and a demographics sheet — that finance and asset-management stakeholders can sort, pivot, and drop into a model without ever touching a GIS tool.

Excel is where a site-selection recommendation meets the people who control the budget. The workbook is the format at the end of the automated reporting and GIS export stage that a lease committee will actually open, re-sort, and paste into a discounted-cash-flow model. The task is narrow but has real traps: geometry must be handled deliberately (usually dropped), numeric dtypes must survive the write so downstream formulas work, and conditional formatting has to make the ranking legible at a glance.

Prerequisites

Before running this task you need:

Python packages: geopandas and pandas for the scored frame, and openpyxl as the Excel engine that supports multiple sheets and conditional formatting. Install with pip install geopandas pandas openpyxl.
A scored, ranked shortlist. A GeoDataFrame with one row per candidate site carrying a suitability_score, per-criterion weighted sub-scores, demographic attributes, and a geometry with a CRS. This is the output of building weighted site suitability scores, with demographic fields sourced via weighting demographic variables for target audiences.
The parent context. This page implements the write_xlsx writer described in Automated Reporting & GIS Export; read that first for the assembler and the CRS policy.

Configuration and execution parameters

The writer is parameterized so the workbook is reproducible and its geometry policy explicit. Everything that affects what a stakeholder sees is pinned here.

Parameter	Value for this task	Notes
`engine`	`openpyxl`	Supports multi-sheet writes and conditional formatting rules.
`geometry_policy`	`drop`	Drop geometry by default; alternative is a single WKT column.
`wkt_crs`	`EPSG:4326`	If WKT is kept, encode it in WGS84 for portability.
`area_crs`	`EPSG:5070`	Equal-area CRS for the trade-area size column.
`sheets`	shortlist, scores, demographics	One concern per sheet; no mega-sheet.
`freeze_header`	`A2`	Freeze the header row so long lists stay readable.
`heat_scale`	3-color	Conditional format on `suitability_score` for at-a-glance ranking.

The one decision that matters most is the geometry policy. A GeoDataFrame cannot be written to Excel with its geometry column intact — openpyxl has no cell type for a polygon. Dropping geometry is the default because a finance sheet does not use it; when a downstream tool genuinely needs the shape, encode it as a single Well-Known Text (WKT) column in WGS84 rather than exploding coordinates across cells.

Why three sheets, not one

A single mega-sheet forces a stakeholder to scroll horizontally across forty columns to find the one number they care about, and it makes pivot tables awkward because unrelated concerns share a header row. Splitting the workbook by concern keeps each sheet legible and independently pivotable:

Shortlist is the executive view — rank, site, headline score, and trade-area size. This is the sheet the committee opens first and the one they re-sort.
Scores exposes the per-criterion weighted contributions so an analyst can see why a site ranked where it did, mirroring the transparency of building weighted site suitability scores. When a weight is questioned, this sheet is the evidence.
Demographics carries the population, income, age, and household attributes behind each trade area, so the demographic argument stands on its own.

The site_id column appears on every sheet as the join key, so a stakeholder who builds their own model can VLOOKUP or pivot across all three without ambiguity. The conditional-format heat scale lives only on the Shortlist’s suitability_score column: a three-color red-yellow-green scale turns the ranking into something readable at a glance, where the eye finds the strongest candidates before reading a single number. Applying it to more than the one score column would add noise, not signal.

Annotated implementation

The writer below splits the scored frame into three sheets, converts geometry per the policy, writes numeric columns with dtypes preserved, then reopens the workbook with openpyxl to apply conditional formatting and freeze panes. It never mutates the source frame.

python

from __future__ import annotations

from pathlib import Path

import geopandas as gpd
import pandas as pd
from openpyxl import load_workbook
from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.utils import get_column_letter
from pyproj import CRS

AREA_CRS = CRS.from_epsg(5070)        # equal-area, for the km2 column
WKT_CRS = CRS.from_epsg(4326)         # if geometry is retained, ship WGS84 WKT


def _to_dataframe(sites: gpd.GeoDataFrame, policy: str = "drop") -> pd.DataFrame:
    """Convert a GeoDataFrame to a plain DataFrame under the geometry policy."""
    assert sites.crs is not None, "scored sites have no CRS; refusing to export"
    df = pd.DataFrame(sites.drop(columns=sites.geometry.name))
    if policy == "wkt":
        # Encode geometry as WGS84 WKT text in a single column.
        wkt = sites.geometry.to_crs(WKT_CRS).to_wkt()
        df["geometry_wkt"] = wkt.values
    return df


def write_xlsx(sites: gpd.GeoDataFrame, cfg) -> Path:
    """Write a multi-sheet stakeholder workbook; return the file path."""
    assert sites.crs is not None, "scored sites have no CRS; refusing to export"
    # Trade-area size MUST be measured in an equal-area CRS, not in degrees.
    area_km2 = sites.to_crs(AREA_CRS).geometry.area / 1_000_000.0

    policy = "wkt" if getattr(cfg, "include_geometry_xlsx", False) else "drop"
    flat = _to_dataframe(sites, policy=policy)
    flat = flat.assign(trade_area_km2=area_km2.values)

    shortlist = flat[["rank", "site_id", "site_name", "suitability_score",
                      "trade_area_km2"]].copy()
    scores = flat[["site_id", "site_name", "w_access", "w_demo",
                   "w_comp", "suitability_score"]].copy()
    demographics = flat[["site_id", "site_name", "population",
                         "median_income", "median_age", "households"]].copy()

    out_path = cfg.out_dir / f"site_shortlist_{cfg.run_id}.xlsx"
    out_path.parent.mkdir(parents=True, exist_ok=True)

    with pd.ExcelWriter(out_path, engine="openpyxl") as xw:
        shortlist.to_excel(xw, sheet_name="Shortlist", index=False)
        scores.to_excel(xw, sheet_name="Scores", index=False)
        demographics.to_excel(xw, sheet_name="Demographics", index=False)

    _style_workbook(out_path, n_rows=len(flat))
    return out_path


def _style_workbook(path: Path, n_rows: int) -> None:
    """Freeze headers, size columns, and heat-scale the score column."""
    wb = load_workbook(path)
    for ws in wb.worksheets:
        ws.freeze_panes = "A2"                         # keep header visible
        for col_idx, col_cells in enumerate(ws.columns, start=1):
            width = max(len(str(c.value)) for c in col_cells if c.value is not None)
            ws.column_dimensions[get_column_letter(col_idx)].width = width + 2

    ws = wb["Shortlist"]
    header = {cell.value: cell.column_letter for cell in ws[1]}
    score_col = header["suitability_score"]        # locate column by header name
    rng = f"{score_col}2:{score_col}{n_rows + 1}"
    ws.conditional_formatting.add(rng, ColorScaleRule(
        start_type="min", start_color="F8696B",       # low score = red
        mid_type="percentile", mid_value=50, mid_color="FFEB84",
        end_type="max", end_color="63BE7B"))           # high score = green
    wb.save(path)

Two-phase writing is deliberate: pandas writes the data and sheet structure, then openpyxl reopens the file to apply presentation rules that pandas cannot express. The geometry is either dropped or collapsed into one WKT column before the frame ever reaches to_excel — there is no code path that hands a shapely object to the Excel engine. The trade-area size is computed once, in the equal-area CRS, and joined back by position.

Failure modes and debugging

Symptom	Cause	Fix
`TypeError: cannot serialize geometry` on write	Geometry column reached `to_excel`	Drop geometry or convert to a WKT string column first.
Numbers stored as text; formulas return `#VALUE!`	A column was cast to `object`/`str` upstream	Preserve numeric dtypes; check `df.dtypes` before writing.
Wrong trade-area size	Area computed in EPSG:4326 degrees	Compute area in an equal-area CRS (EPSG:5070).
Conditional formatting on the wrong range	Off-by-one from the header row	Range starts at row 2; end at `n_rows + 1`.
Garbled accented store names	Non-UTF-8 handling	openpyxl writes UTF-8; ensure upstream reads are UTF-8 too.
Scientific notation on ZIP/FIPS codes	Numeric dtype on an identifier column	Keep GEOIDs and ZIPs as strings so leading zeros survive.

The dtype trap is the one that silently reaches the stakeholder. If population or median_income arrives as an object dtype — common when a column carried a sentinel string upstream — Excel stores it as text, and the committee’s =SUM() returns nothing. Assert numeric dtypes before the write.

Verification

Confirm the workbook’s structure and typing before shipping it:

Sheet names: the workbook contains exactly Shortlist, Scores, and Demographics.
Row counts: each sheet has one data row per shortlisted site (plus the header).
Dtype checks: suitability_score, population, and median_income round-trip as numeric, not text.

python

import pandas as pd
from openpyxl import load_workbook

def verify_workbook(path, expected_rows: int) -> None:
    wb = load_workbook(path, read_only=True)
    assert set(wb.sheetnames) == {"Shortlist", "Scores", "Demographics"}, \
        f"unexpected sheets: {wb.sheetnames}"

    sheets = pd.read_excel(path, sheet_name=None, engine="openpyxl")
    for name, df in sheets.items():
        assert len(df) == expected_rows, f"{name}: {len(df)} rows, expected {expected_rows}"

    numeric = sheets["Shortlist"]["suitability_score"]
    assert pd.api.types.is_numeric_dtype(numeric), "suitability_score is not numeric"
    demo = sheets["Demographics"]
    for col in ("population", "median_income"):
        assert pd.api.types.is_numeric_dtype(demo[col]), f"{col} stored as text"

Reading the file back with pd.read_excel and asserting numeric dtypes is the check that matters: it proves the committee’s spreadsheet formulas will work, which a sheet-name check alone never confirms.

Automated Reporting & GIS Export — the assembler and geometry policy this XLSX writer implements.
Ranking and Shortlisting Candidate Sites — produces the ranked order the shortlist sheet reflects.
Weighting Demographic Variables for Target Audiences — the source of the demographic columns.

← Back to Automated Reporting & GIS Export

Exporting Scored Trade Areas to XLSX for Stakeholders

Prerequisites #

Configuration and execution parameters #

Why three sheets, not one #

Annotated implementation #

Failure modes and debugging #

Verification #

Related #