Meta: Automated content generation from spreadsheets

[jeremymanning]

Overview

This issue tracks the overall progress of automating site content generation from Excel spreadsheets. The goal is to make it easy for lab members to update publications, people, and software pages without editing HTML directly.

Related Issues

• Auto-generate publications page from Excel spreadsheet #1 - Auto-generate publications page from Excel spreadsheet
• Auto-generate people page from Excel spreadsheet #2 - Auto-generate people page from Excel spreadsheet
• Auto-generate software page from Excel spreadsheet #3 - Auto-generate software page from Excel spreadsheet

Architecture

data/
  publications.xlsx
  people.xlsx
  software.xlsx

scripts/
  build_publications.py
  build_people.py
  build_software.py
  build_all.py          # Master build script

templates/              # HTML templates (optional, for Jinja2)
  publications.html.j2
  people.html.j2
  software.html.j2

Local Testing Plan

Before pushing any changes to the live site, the following tests must pass:

1. Build Verification

• All build scripts run without errors
• Generated HTML is valid (no unclosed tags, proper nesting)
• No Python/build errors or warnings

2. Visual Comparison Testing

# Start local server
python3 -m http.server 8000

# Open in browser and verify each page:
# - http://localhost:8000/publications.html
# - http://localhost:8000/people.html
# - http://localhost:8000/software.html

• Publications page: All publications render correctly

  • Thumbnails display properly
  • Links work (PDF, DOI, Code/Data)
  • Correct grouping by publication type
  • Proper sorting (newest first)
  • Mobile layout works

• People page: All members render correctly

  • Photos display properly
  • Active members appear in main grid
  • Alumni appear in alumni section
  • Lab director has correct special layout
  • Mobile layout works (stacked on small screens)

• Software page: All projects render correctly

  • Icons/logos display properly
  • Links work (GitHub, docs, PyPI)
  • Correct grouping by language/type
  • Mobile layout works

3. Data Integrity Tests

• Verify row count in spreadsheet matches items on page
• Spot-check 3-5 random entries for accuracy
• Verify all required fields are populated
• Check for duplicate entries

4. Edge Case Testing

• Empty optional fields handled gracefully
• Special characters in text (quotes, ampersands) escaped properly
• Very long text truncates or wraps appropriately
• Missing images show placeholder or graceful fallback

5. Cross-Browser Testing

• Chrome (latest)
• Firefox (latest)
• Safari (latest)
• Mobile Safari / Chrome

6. Diff Review

# Before committing, review changes
git diff publications.html
git diff people.html
git diff software.html

• Review HTML diff to ensure only expected changes
• No unintended formatting changes
• No removal of important content

Deployment Checklist

1. All local tests pass
2. Changes committed to feature branch
3. Pull request created for review
4. Visual review of PR preview (if available)
5. Merge to main
6. Verify live site at https://context-lab.com

Future Enhancements

• GitHub Actions workflow to auto-build on spreadsheet changes
• Validation script to check spreadsheet data before build
• Preview environment for testing before merge

Labels

epic, automation, tracking

[jeremymanning]

All of these should run as GitHub actions on any push

[jeremymanning]

Implementation Plan: Automated Content Generation from Spreadsheets

Overview

This plan details the implementation of an automated build system that generates publications.html, people.html, and software.html from Excel spreadsheets. All tests use real data, real file I/O, and real HTTP servers—no mocks or simulations.

Key Design Decisions (per discussion)

1. Embedded HTML supported - Description fields can contain HTML for formatting (links, emphasis, etc.)
2. Alumni grouping preserved - Maintain current categories: Postdocs, Grad Students, Lab Managers, Undergrads
3. Automatic builds - GitHub Actions triggers on spreadsheet OR HTML template changes
4. Templates preserved - Original HTML files serve as editable templates; generated content is injected into specific sections

---

Phase 1: Project Structure & Dependencies

1.1 Directory Structure

contextlab.github.io/
├── data/
│   ├── publications.xlsx      # Source data
│   ├── people.xlsx
│   └── software.xlsx
├── templates/                  # Editable base templates (copied from current HTML)
│   ├── publications_base.html  # Has <!-- CONTENT_START --> markers
│   ├── people_base.html
│   └── software_base.html
├── scripts/
│   ├── build.py               # Master build script
│   ├── build_publications.py
│   ├── build_people.py
│   ├── build_software.py
│   ├── validate_data.py       # Data validation
│   └── utils.py               # Shared utilities
├── tests/
│   ├── test_build_publications.py
│   ├── test_build_people.py
│   ├── test_build_software.py
│   ├── test_html_validation.py
│   ├── test_visual_regression.py
│   └── fixtures/              # Test data files
│       ├── test_publications.xlsx
│       ├── test_people.xlsx
│       └── test_software.xlsx
├── .github/
│   └── workflows/
│       └── build-site.yml     # Auto-build on changes
└── requirements-build.txt     # Build dependencies

1.2 Template Marker System

Templates use HTML comments to mark content injection points:

<!-- In templates/publications_base.html -->
<section id="papers" class="content-section">
    <h2>Papers</h2>
    <div class="publications-grid">
        <!-- PAPERS_CONTENT -->
    </div>
</section>

The build script replaces <!-- PAPERS_CONTENT --> with generated HTML from the spreadsheet.

1.3 Dependencies (requirements-build.txt)

openpyxl>=3.1.0          # Excel file parsing
Jinja2>=3.1.0            # HTML templating for content blocks
html5lib>=1.1            # HTML validation
beautifulsoup4>=4.12.0   # HTML parsing for tests
pytest>=7.4.0            # Test framework
requests>=2.31.0         # HTTP requests for live testing
Pillow>=10.0.0           # Screenshot comparison
selenium>=4.15.0         # Browser automation for visual tests

---

Phase 2: Data Schema Design

2.1 Publications Spreadsheet (data/publications.xlsx)

Column	Type	Required	Description
title	string	Yes	Publication title (can contain HTML)
authors	string	Yes	Author list (can contain HTML for links)
year	integer	Yes	Publication year
type	string	Yes	paper, chapter, dissertation, talk, course, poster
venue	string	Yes	Journal, conference, or platform (can contain HTML)
doi_url	string	No	DOI or primary URL
pdf_url	string	No	Direct PDF link
code_url	string	No	Code/data repository
thumbnail	string	No	Filename in images/publications/

2.2 People Spreadsheet (data/people.xlsx)

Column	Type	Required	Description
name	string	Yes	Full name
role	string	Yes	lab director, postdoc, grad student, undergrad, lab manager
status	string	Yes	active, alum
alumni_category	string	No	For alumni: Postdoctoral Researchers, Graduate Students, Lab Managers, Undergraduate Researchers
photo	string	Yes	Filename in images/people/
bio	string	Yes	Biography text (can contain HTML)
website	string	No	Personal website URL
start_year	integer	Yes	Year joined lab
end_year	integer	No	Year left (for alumni)
current_position	string	No	Current role description (for alumni, can contain HTML)

2.3 Software Spreadsheet (data/software.xlsx)

Column	Type	Required	Description
name	string	Yes	Software name
type	string	Yes	python, javascript, matlab
description	string	Yes	Brief description (can contain HTML)
github_url	string	No	GitHub repository
docs_url	string	No	Documentation link
package_url	string	No	PyPI/npm/MATLAB Central

---

Phase 3: Build Scripts Implementation

3.1 Core Utilities (scripts/utils.py)

"""Shared utilities for build scripts."""
import re
from pathlib import Path
from typing import Any, Dict, List, Optional
import openpyxl

def load_spreadsheet(filepath: Path) -> List[Dict[str, Any]]:
    """Load Excel spreadsheet and return list of row dictionaries."""
    wb = openpyxl.load_workbook(filepath, read_only=True, data_only=True)
    sheet = wb.active
    headers = [cell.value for cell in sheet[1]]
    rows = []
    for row in sheet.iter_rows(min_row=2, values_only=True):
        if any(cell is not None for cell in row):
            row_dict = dict(zip(headers, row))
            # Convert None to empty string for optional fields
            rows.append({k: (v if v is not None else '') for k, v in row_dict.items()})
    return rows

def inject_content(template_path: Path, output_path: Path, replacements: Dict[str, str]):
    """Inject generated content into template at marker locations."""
    content = template_path.read_text()
    
    for marker, html in replacements.items():
        pattern = f'<!-- {marker} -->'
        if pattern not in content:
            raise ValueError(f"Marker '{pattern}' not found in template")
        content = content.replace(pattern, html)
    
    output_path.write_text(content)

def validate_required_fields(row: Dict, required: List[str], row_num: int) -> List[str]:
    """Validate required fields are present and non-empty."""
    errors = []
    for field in required:
        value = row.get(field)
        if value is None or (isinstance(value, str) and value.strip() == ''):
            errors.append(f"Row {row_num}: Missing required field '{field}'")
    return errors

3.2 Publications Builder (scripts/build_publications.py)

"""Build publications.html from spreadsheet data."""
import sys
from pathlib import Path
from typing import Dict, List
from utils import load_spreadsheet, inject_content, validate_required_fields

PUBLICATION_TYPES = {
    'paper': 'PAPERS_CONTENT',
    'chapter': 'CHAPTERS_CONTENT', 
    'dissertation': 'DISSERTATIONS_CONTENT',
    'talk': 'TALKS_CONTENT',
    'course': 'COURSES_CONTENT',
    'poster': 'POSTERS_CONTENT'
}
REQUIRED_FIELDS = ['title', 'authors', 'year', 'type', 'venue']

def generate_publication_card(pub: Dict) -> str:
    """Generate HTML for a single publication card."""
    thumbnail_html = ''
    if pub.get('thumbnail'):
        thumbnail_html = f'<img src="images/publications/{pub["thumbnail"]}" alt="Publication thumbnail">'
    
    title_html = pub['title']
    if pub.get('doi_url'):
        title_html = f'<a href="{pub["doi_url"]}" target="_blank">{pub["title"]}</a>'
    
    links = []
    if pub.get('pdf_url'):
        links.append(f'[<a href="{pub["pdf_url"]}" target="_blank">PDF</a>]')
    if pub.get('code_url'):
        links.append(f'[<a href="{pub["code_url"]}" target="_blank">CODE + DATA</a>]')
    links_html = ' '.join(links)
    
    return f'''<div class="publication-card">
    {thumbnail_html}
    <div>
        <h4>{title_html}</h4>
        <p>{pub["authors"]} ({pub["year"]}). <em>{pub["venue"]}</em>.</p>
        <p class="publication-links">{links_html}</p>
    </div>
</div>'''

def build_publications(data_path: Path, template_path: Path, output_path: Path) -> bool:
    """Build publications.html from spreadsheet."""
    rows = load_spreadsheet(data_path)
    
    # Validate
    errors = []
    for i, row in enumerate(rows, start=2):
        errors.extend(validate_required_fields(row, REQUIRED_FIELDS, i))
    
    if errors:
        for error in errors:
            print(f"ERROR: {error}", file=sys.stderr)
        return False
    
    # Group by type and sort by year (newest first)
    grouped: Dict[str, List[Dict]] = {t: [] for t in PUBLICATION_TYPES}
    for row in rows:
        pub_type = row['type'].lower().strip()
        if pub_type in grouped:
            grouped[pub_type].append(row)
    
    for pub_type in grouped:
        grouped[pub_type].sort(key=lambda x: int(x.get('year', 0)), reverse=True)
    
    # Generate HTML for each section
    replacements = {}
    for pub_type, marker in PUBLICATION_TYPES.items():
        cards = [generate_publication_card(pub) for pub in grouped[pub_type]]
        replacements[marker] = '\n\n'.join(cards) if cards else '<!-- No publications in this category -->'
    
    # Inject into template
    inject_content(template_path, output_path, replacements)
    
    total = sum(len(pubs) for pubs in grouped.values())
    print(f"Generated {output_path} with {total} publications")
    return True

if __name__ == '__main__':
    root = Path(__file__).parent.parent
    success = build_publications(
        data_path=root / 'data' / 'publications.xlsx',
        template_path=root / 'templates' / 'publications_base.html',
        output_path=root / 'publications.html'
    )
    sys.exit(0 if success else 1)

3.3 People Builder (scripts/build_people.py)

Key features:

• Lab director gets special two-column layout
• Active members displayed in grid
• Alumni grouped by category (Postdocs, Grad Students, Lab Managers, Undergrads)
• Sorted by end_year (most recent first) within each alumni category

3.4 Software Builder (scripts/build_software.py)

Key features:

• Grouped by language type (Python, JavaScript, MATLAB)
• Each section uses appropriate icon
• Software descriptions support embedded HTML

3.5 GitHub Actions Workflow (.github/workflows/build-site.yml)

name: Build Site

on:
  push:
    paths:
      - 'data/*.xlsx'
      - 'templates/*.html'
    branches:
      - main

jobs:
  build:
    runs-on: ubuntu-latest
    
    steps:
      - uses: actions/checkout@v4
      
      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.11'
      
      - name: Install dependencies
        run: pip install -r requirements-build.txt
      
      - name: Validate data
        run: python scripts/validate_data.py
      
      - name: Build pages
        run: python scripts/build.py
      
      - name: Run tests
        run: pytest tests/ -v --ignore=tests/test_visual_regression.py
      
      - name: Commit and push if changed
        run: |
          git config --local user.email "action@github.com"
          git config --local user.name "GitHub Action"
          git add publications.html people.html software.html
          git diff --staged --quiet || git commit -m "Auto-build: Update generated pages"
          git push

---

Phase 4: Testing Strategy (NO MOCKS!)

4.1 Unit Tests with Real Files (tests/test_build_publications.py)

"""Tests for publications build script using REAL files and data."""
import pytest
from pathlib import Path
import tempfile
import openpyxl
from scripts.build_publications import build_publications, generate_publication_card
from scripts.utils import load_spreadsheet, inject_content

class TestBuildPublications:
    """Test publication building with real Excel files."""
    
    @pytest.fixture
    def temp_dir(self):
        """Create real temporary directory."""
        with tempfile.TemporaryDirectory() as td:
            yield Path(td)
    
    @pytest.fixture
    def sample_xlsx(self, temp_dir):
        """Create a REAL Excel file with test data."""
        filepath = temp_dir / 'publications.xlsx'
        wb = openpyxl.Workbook()
        ws = wb.active
        
        # Headers
        headers = ['title', 'authors', 'year', 'type', 'venue', 'doi_url', 'pdf_url', 'code_url', 'thumbnail']
        ws.append(headers)
        
        # Real test data - including HTML in fields
        ws.append([
            'Test Paper with <em>emphasis</em>',
            '<a href="http://example.com">Author A</a>, Author B',
            2024, 'paper', 'Nature',
            'https://doi.org/10.1000/test',
            'https://example.com/paper.pdf',
            'https://github.com/test/repo',
            'test-thumb.png'
        ])
        ws.append(['Another Paper', 'Author C', 2023, 'paper', 'Science', '', '', '', ''])
        ws.append(['A Talk', 'Author A', 2024, 'talk', 'Conference X', 'https://youtube.com/watch?v=123', '', '', ''])
        
        wb.save(filepath)
        return filepath
    
    @pytest.fixture
    def template_file(self, temp_dir):
        """Create real template with markers."""
        filepath = temp_dir / 'template.html'
        filepath.write_text('''<!DOCTYPE html>
<html><body>
<section id="papers">
<h2>Papers</h2>
<div class="publications-grid">
<!-- PAPERS_CONTENT -->
</div>
</section>
<section id="talks">
<h2>Talks</h2>
<div class="publications-grid">
<!-- TALKS_CONTENT -->
</div>
</section>
<!-- CHAPTERS_CONTENT -->
<!-- DISSERTATIONS_CONTENT -->
<!-- COURSES_CONTENT -->
<!-- POSTERS_CONTENT -->
</body></html>''')
        return filepath
    
    def test_loads_real_excel_file(self, sample_xlsx):
        """Test that we can load a real Excel file."""
        rows = load_spreadsheet(sample_xlsx)
        assert len(rows) == 3
        assert 'emphasis' in rows[0]['title']  # HTML preserved
        assert rows[0]['year'] == 2024
    
    def test_preserves_html_in_fields(self, sample_xlsx):
        """Test that embedded HTML is preserved."""
        rows = load_spreadsheet(sample_xlsx)
        assert '<em>' in rows[0]['title']
        assert '<a href=' in rows[0]['authors']
    
    def test_builds_real_html_output(self, sample_xlsx, template_file, temp_dir):
        """Test building actual HTML file."""
        output_path = temp_dir / 'publications.html'
        
        result = build_publications(sample_xlsx, template_file, output_path)
        
        assert result is True
        assert output_path.exists()
        
        html_content = output_path.read_text()
        assert 'Test Paper' in html_content
        assert '<em>emphasis</em>' in html_content  # HTML preserved
        assert 'Another Paper' in html_content
    
    def test_groups_by_publication_type(self, sample_xlsx, template_file, temp_dir):
        """Test that publications are grouped correctly."""
        output_path = temp_dir / 'publications.html'
        build_publications(sample_xlsx, template_file, output_path)
        
        html_content = output_path.read_text()
        papers_section = html_content.find('id="papers"')
        talks_section = html_content.find('id="talks"')
        
        # Both papers should be in papers section
        assert 'Test Paper' in html_content[papers_section:talks_section]
        assert 'Another Paper' in html_content[papers_section:talks_section]
        # Talk should be in talks section
        assert 'A Talk' in html_content[talks_section:]
    
    def test_sorts_by_year_descending(self, sample_xlsx, template_file, temp_dir):
        """Test newest publications appear first."""
        output_path = temp_dir / 'publications.html'
        build_publications(sample_xlsx, template_file, output_path)
        
        html_content = output_path.read_text()
        pos_2024 = html_content.find('Test Paper')
        pos_2023 = html_content.find('Another Paper')
        assert pos_2024 < pos_2023
    
    def test_handles_empty_optional_fields(self, temp_dir, template_file):
        """Test graceful handling of empty optional fields."""
        filepath = temp_dir / 'minimal.xlsx'
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.append(['title', 'authors', 'year', 'type', 'venue', 'doi_url', 'pdf_url', 'code_url', 'thumbnail'])
        ws.append(['Minimal Paper', 'Author X', 2024, 'paper', 'Journal Y', '', '', '', ''])
        wb.save(filepath)
        
        output_path = temp_dir / 'output.html'
        result = build_publications(filepath, template_file, output_path)
        
        assert result is True
        html = output_path.read_text()
        assert 'Minimal Paper' in html
        assert 'publication-links"></p>' in html or 'publication-links"> </p>' in html  # Empty links
    
    def test_fails_on_missing_required_fields(self, temp_dir, template_file):
        """Test that missing required fields cause failure."""
        filepath = temp_dir / 'invalid.xlsx'
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.append(['title', 'authors', 'year', 'type', 'venue'])
        ws.append(['Only Title', '', '', '', ''])  # Missing required fields
        wb.save(filepath)
        
        output_path = temp_dir / 'output.html'
        result = build_publications(filepath, template_file, output_path)
        
        assert result is False


class TestContentInjection:
    """Test template marker replacement."""
    
    def test_inject_single_marker(self, tmp_path):
        """Test injecting content at a single marker."""
        template = tmp_path / 'template.html'
        template.write_text('<div><!-- MARKER --></div>')
        
        output = tmp_path / 'output.html'
        inject_content(template, output, {'MARKER': '<p>Injected</p>'})
        
        assert output.read_text() == '<div><p>Injected</p></div>'
    
    def test_inject_multiple_markers(self, tmp_path):
        """Test injecting at multiple markers."""
        template = tmp_path / 'template.html'
        template.write_text('<!-- A -->|<!-- B -->')
        
        output = tmp_path / 'output.html'
        inject_content(template, output, {'A': 'First', 'B': 'Second'})
        
        assert output.read_text() == 'First|Second'
    
    def test_raises_on_missing_marker(self, tmp_path):
        """Test error when marker not found."""
        template = tmp_path / 'template.html'
        template.write_text('<div>No markers</div>')
        
        output = tmp_path / 'output.html'
        with pytest.raises(ValueError, match="not found"):
            inject_content(template, output, {'MISSING': 'content'})

4.2 HTML Validation Tests (tests/test_html_validation.py)

"""Test generated HTML is valid using real HTML parser."""
import pytest
from pathlib import Path
import html5lib
from bs4 import BeautifulSoup
import tempfile
import openpyxl

class TestHTMLValidation:
    """Validate generated HTML with real parsers."""
    
    @pytest.fixture
    def built_publications(self, tmp_path):
        """Build real publications.html for testing."""
        from scripts.build_publications import build_publications
        
        # Create test data
        xlsx = tmp_path / 'pubs.xlsx'
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.append(['title', 'authors', 'year', 'type', 'venue', 'doi_url', 'pdf_url', 'code_url', 'thumbnail'])
        ws.append(['Test Paper', 'Author A', 2024, 'paper', 'Journal', 'https://doi.org/test', '', '', ''])
        ws.append(['Test Talk', 'Author B', 2023, 'talk', 'Conf', '', '', '', ''])
        wb.save(xlsx)
        
        # Create template
        template = tmp_path / 'template.html'
        template.write_text('''<!DOCTYPE html>
<html lang="en">
<head><title>Test</title></head>
<body>
<div class="publications-grid"><!-- PAPERS_CONTENT --></div>
<div class="publications-grid"><!-- TALKS_CONTENT --></div>
<!-- CHAPTERS_CONTENT --><!-- DISSERTATIONS_CONTENT --><!-- COURSES_CONTENT --><!-- POSTERS_CONTENT -->
</body>
</html>''')
        
        output = tmp_path / 'publications.html'
        build_publications(xlsx, template, output)
        return output
    
    def test_html_is_well_formed(self, built_publications):
        """Test HTML parses without errors."""
        html_content = built_publications.read_text()
        doc = html5lib.parse(html_content)
        assert doc is not None
    
    def test_all_divs_are_closed(self, built_publications):
        """Verify div tags are balanced."""
        html_content = built_publications.read_text()
        assert html_content.count('<div') == html_content.count('</div>')
    
    def test_all_links_have_href(self, built_publications):
        """Verify all anchor tags have href attributes."""
        html_content = built_publications.read_text()
        soup = BeautifulSoup(html_content, 'html.parser')
        
        for link in soup.find_all('a'):
            href = link.get('href')
            assert href and href.strip(), f"Link missing href: {link}"
    
    def test_no_empty_elements_where_inappropriate(self, built_publications):
        """Check for inappropriately empty structural elements."""
        html_content = built_publications.read_text()
        soup = BeautifulSoup(html_content, 'html.parser')
        
        # h4 tags should have content
        for h4 in soup.find_all('h4'):
            assert h4.get_text(strip=True), f"Empty h4 found"

4.3 Visual Regression Tests (tests/test_visual_regression.py)

"""Visual regression tests using REAL browser and HTTP server."""
import pytest
import subprocess
import time
import requests
from pathlib import Path
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.common.by import By
from PIL import Image
import io

@pytest.mark.visual
class TestVisualRegression:
    """Test visual output with real browser rendering."""
    
    @pytest.fixture(scope="class")
    def http_server(self):
        """Start a REAL HTTP server for testing."""
        root = Path(__file__).parent.parent
        proc = subprocess.Popen(
            ['python3', '-m', 'http.server', '8765'],
            cwd=root,
            stdout=subprocess.DEVNULL,
            stderr=subprocess.DEVNULL
        )
        
        # Wait for server to start
        for _ in range(10):
            try:
                requests.get('http://localhost:8765/', timeout=1)
                break
            except requests.exceptions.ConnectionError:
                time.sleep(0.5)
        
        yield 'http://localhost:8765'
        
        proc.terminate()
        proc.wait()
    
    @pytest.fixture
    def browser(self):
        """Create real Chrome browser instance."""
        options = Options()
        options.add_argument('--headless')
        options.add_argument('--window-size=1920,1080')
        options.add_argument('--no-sandbox')
        
        driver = webdriver.Chrome(options=options)
        yield driver
        driver.quit()
    
    def test_publications_page_renders(self, http_server, browser):
        """Test publications page renders without errors."""
        browser.get(f'{http_server}/publications.html')
        
        assert 'publications' in browser.title.lower()
        
        # Check no JavaScript errors
        logs = browser.get_log('browser')
        errors = [log for log in logs if log['level'] == 'SEVERE']
        assert len(errors) == 0, f"JS errors: {errors}"
    
    def test_publication_cards_visible(self, http_server, browser):
        """Test publication cards are visible."""
        browser.get(f'{http_server}/publications.html')
        time.sleep(1)  # Wait for render
        
        cards = browser.find_elements(By.CSS_SELECTOR, '.publication-card')
        assert len(cards) > 0, "No publication cards found"
        
        for card in cards[:5]:
            assert card.is_displayed()
    
    def test_people_page_renders(self, http_server, browser):
        """Test people page renders without errors."""
        browser.get(f'{http_server}/people.html')
        assert 'people' in browser.title.lower() or 'who we are' in browser.title.lower()
    
    def test_software_page_renders(self, http_server, browser):
        """Test software page renders without errors."""
        browser.get(f'{http_server}/software.html')
        assert 'software' in browser.title.lower()
    
    def test_responsive_mobile(self, http_server, browser):
        """Test mobile responsive layout."""
        browser.set_window_size(375, 812)  # iPhone dimensions
        browser.get(f'{http_server}/publications.html')
        time.sleep(1)
        
        # Page should still render
        cards = browser.find_elements(By.CSS_SELECTOR, '.publication-card')
        assert len(cards) > 0

4.4 Data Integrity Tests (tests/test_data_integrity.py)

"""Test data integrity with real spreadsheet files."""
import pytest
from pathlib import Path
from scripts.utils import load_spreadsheet
import requests
import re

class TestPublicationsDataIntegrity:
    """Verify publications spreadsheet data integrity."""
    
    @pytest.fixture
    def publications_data(self):
        """Load real publications spreadsheet if it exists."""
        root = Path(__file__).parent.parent
        filepath = root / 'data' / 'publications.xlsx'
        if not filepath.exists():
            pytest.skip("Publications spreadsheet not yet created")
        return load_spreadsheet(filepath)
    
    def test_no_duplicate_titles(self, publications_data):
        """Check for duplicate publication titles."""
        titles = [row['title'] for row in publications_data]
        duplicates = [t for t in set(titles) if titles.count(t) > 1]
        assert len(duplicates) == 0, f"Duplicate titles: {duplicates}"
    
    def test_years_are_reasonable(self, publications_data):
        """Check publication years are valid."""
        for row in publications_data:
            year = row.get('year')
            assert year is not None, f"Missing year for: {row['title']}"
            year_int = int(year)
            assert 1990 <= year_int <= 2030, f"Invalid year {year} for: {row['title']}"
    
    def test_types_are_valid(self, publications_data):
        """Check publication types are recognized."""
        valid_types = {'paper', 'chapter', 'dissertation', 'talk', 'course', 'poster'}
        for row in publications_data:
            pub_type = str(row.get('type', '')).lower().strip()
            assert pub_type in valid_types, f"Invalid type '{pub_type}' for: {row['title']}"
    
    def test_urls_are_valid_format(self, publications_data):
        """Check URLs have valid format."""
        url_pattern = re.compile(r'^https?://[^\s]+$')
        
        for row in publications_data:
            for field in ['doi_url', 'pdf_url', 'code_url']:
                url = row.get(field)
                if url and str(url).strip():
                    assert url_pattern.match(str(url)), f"Invalid URL: {url}"
    
    def test_thumbnails_exist(self, publications_data):
        """Check referenced thumbnail files exist."""
        root = Path(__file__).parent.parent
        img_dir = root / 'images' / 'publications'
        
        for row in publications_data:
            thumb = row.get('thumbnail')
            if thumb and str(thumb).strip():
                thumb_path = img_dir / str(thumb)
                assert thumb_path.exists(), f"Missing thumbnail: {thumb}"
    
    @pytest.mark.slow
    @pytest.mark.network
    def test_doi_urls_resolve(self, publications_data):
        """Test that DOI URLs actually resolve (slow, network-dependent)."""
        checked = 0
        for row in publications_data:
            if checked >= 5:  # Limit to 5 to avoid rate limiting
                break
            url = row.get('doi_url')
            if url and 'doi.org' in str(url):
                try:
                    resp = requests.head(str(url), timeout=10, allow_redirects=True)
                    assert resp.status_code < 400, f"DOI broken: {url} -> {resp.status_code}"
                    checked += 1
                except requests.exceptions.RequestException as e:
                    pytest.fail(f"DOI unreachable: {url} -> {e}")


class TestPeopleDataIntegrity:
    """Verify people spreadsheet data integrity."""
    
    @pytest.fixture
    def people_data(self):
        """Load real people spreadsheet if it exists."""
        root = Path(__file__).parent.parent
        filepath = root / 'data' / 'people.xlsx'
        if not filepath.exists():
            pytest.skip("People spreadsheet not yet created")
        return load_spreadsheet(filepath)
    
    def test_photos_exist(self, people_data):
        """Check all referenced photos exist."""
        root = Path(__file__).parent.parent
        img_dir = root / 'images' / 'people'
        
        for row in people_data:
            photo = row.get('photo')
            if photo and str(photo).strip():
                photo_path = img_dir / str(photo)
                assert photo_path.exists(), f"Missing photo for {row['name']}: {photo}"
    
    def test_status_values_valid(self, people_data):
        """Check status is either 'active' or 'alum'."""
        for row in people_data:
            status = str(row.get('status', '')).lower().strip()
            assert status in {'active', 'alum'}, f"Invalid status '{status}' for {row['name']}"
    
    def test_alumni_have_end_year(self, people_data):
        """Alumni should have end_year specified."""
        for row in people_data:
            if str(row.get('status', '')).lower() == 'alum':
                end_year = row.get('end_year')
                assert end_year, f"Alumni missing end_year: {row['name']}"

4.5 Integration Tests (tests/test_integration.py)

"""End-to-end integration tests with real build pipeline."""
import pytest
import subprocess
from pathlib import Path
from bs4 import BeautifulSoup

class TestFullBuildPipeline:
    """Test complete build pipeline with real files."""
    
    @pytest.fixture
    def backup_and_restore(self):
        """Backup and restore HTML files around test."""
        root = Path(__file__).parent.parent
        backups = {}
        
        for html_file in ['publications.html', 'people.html', 'software.html']:
            filepath = root / html_file
            if filepath.exists():
                backups[html_file] = filepath.read_text()
        
        yield root
        
        # Restore originals
        for html_file, content in backups.items():
            (root / html_file).write_text(content)
    
    @pytest.mark.skipif(
        not (Path(__file__).parent.parent / 'data' / 'publications.xlsx').exists(),
        reason="Spreadsheets not yet created"
    )
    def test_full_build_succeeds(self, backup_and_restore):
        """Test running build.py produces valid output."""
        root = backup_and_restore
        
        result = subprocess.run(
            ['python3', 'scripts/build.py'],
            cwd=root,
            capture_output=True,
            text=True
        )
        
        assert result.returncode == 0, f"Build failed: {result.stderr}"
    
    @pytest.mark.skipif(
        not (Path(__file__).parent.parent / 'data' / 'publications.xlsx').exists(),
        reason="Spreadsheets not yet created"
    )
    def test_output_matches_spreadsheet_count(self, backup_and_restore):
        """Verify generated HTML has correct number of items."""
        root = backup_and_restore
        
        # Run build
        subprocess.run(['python3', 'scripts/build.py'], cwd=root, check=True)
        
        # Count publications in spreadsheet
        from scripts.utils import load_spreadsheet
        pub_rows = load_spreadsheet(root / 'data' / 'publications.xlsx')
        pub_count = len(pub_rows)
        
        # Count cards in generated HTML
        html = (root / 'publications.html').read_text()
        soup = BeautifulSoup(html, 'html.parser')
        card_count = len(soup.find_all('div', class_='publication-card'))
        
        assert card_count == pub_count, f"Mismatch: {card_count} cards vs {pub_count} spreadsheet rows"

---

Phase 5: Pre-Push Validation Script

scripts/pre_push_check.py

#!/usr/bin/env python3
"""Run all checks before pushing to GitHub."""
import subprocess
import sys
from pathlib import Path

def run_checks():
    root = Path(__file__).parent.parent
    checks = [
        ("Data validation", ["python3", "scripts/validate_data.py"]),
        ("Build all pages", ["python3", "scripts/build.py"]),
        ("Unit tests", ["pytest", "tests/", "-v", "-m", "not slow and not network and not visual"]),
        ("HTML validation", ["pytest", "tests/test_html_validation.py", "-v"]),
    ]
    
    failed = []
    for name, cmd in checks:
        print(f"\n{'='*60}")
        print(f"Running: {name}")
        print('='*60)
        
        result = subprocess.run(cmd, cwd=root)
        if result.returncode != 0:
            failed.append(name)
            print(f"❌ {name} FAILED")
        else:
            print(f"✓ {name} passed")
    
    print(f"\n{'='*60}")
    if failed:
        print(f"❌ FAILED CHECKS: {', '.join(failed)}")
        return 1
    else:
        print("✓ ALL CHECKS PASSED")
        return 0

if __name__ == '__main__':
    sys.exit(run_checks())

---

Phase 6: Implementation Timeline

Step 1: Foundation (First)

• Create data/, templates/, tests/ directories
• Create requirements-build.txt
• Implement scripts/utils.py with tests
• Set up pytest configuration

Step 2: Publications Builder

• Extract current publications.html content into template with markers
• Create publications.xlsx from existing HTML data
• Implement build_publications.py
• Write and pass all publication tests

Step 3: People Builder

• Extract current people.html into template
• Create people.xlsx from existing data
• Implement build_people.py with director/alumni handling
• Write and pass all people tests

Step 4: Software Builder

• Extract current software.html into template
• Create software.xlsx from existing data
• Implement build_software.py
• Write and pass all software tests

Step 5: Integration

• Implement master build.py
• Create validate_data.py
• Create pre_push_check.py
• Set up GitHub Actions workflow
• Full end-to-end testing

Step 6: Documentation & Deployment

• Update README with build instructions
• Create spreadsheet editing guide
• Test GitHub Actions workflow
• Deploy and verify

[jeremymanning]

Progress Update: Phase 1 Complete

Commit: 4d3dba0

Completed

• Created directory structure: data/, templates/, tests/, scripts/
• Added requirements-build.txt with all dependencies
• Implemented scripts/utils.py with core utilities:
  • load_spreadsheet(): Load Excel files, preserve HTML, handle empty cells
  • inject_content(): Template marker replacement system
  • validate_required_fields(): Required field validation
  • validate_url_format(): URL format checking
  • check_file_exists(): File existence validation
• Added comprehensive test suite (31 tests, all passing)
• All tests use REAL files - no mocks or simulations

Next Steps

• Extract publications.html into template with content markers
• Create publications.xlsx from existing HTML data
• Implement build_publications.py

[jeremymanning]

Completed. The automated content generation system is fully implemented:

What was built

• Data files: Excel spreadsheets in data/ for publications, people, and software
• Templates: HTML templates with content markers in templates/
• Build scripts: Python scripts to generate HTML from spreadsheets
• Validation: Data validation script to catch errors before build
• Testing: 76 automated tests covering all functionality
• CI/CD: GitHub Actions workflow that auto-rebuilds on push

How to use

1. Edit the Excel files in data/
2. Push to main branch
3. GitHub Actions automatically validates, builds, and commits the updated HTML

See the updated README for full documentation.