# Excel Template Generation Best Practices

**Last Updated:** 2026-02-15

## Overview

This document covers best practices for generating Excel templates programmatically to avoid corruption and "problem with some content" errors when users open files in Microsoft Excel.

## Known Corruption Causes

### 1. Chart displayBlanksAs (PhpSpreadsheet)

**Issue:** Excel expects `dispBlanksAs` attribute values of `gap`, `zero`, or `span` – not `0`. PhpSpreadsheet versions before 1.29 could write `val="0"`, causing Excel to show a repair prompt.

**Fix:** Use PhpSpreadsheet 5.5.0+ (includes fix from 1.29+) and ensure Chart constructor uses `DataSeries::EMPTY_AS_GAP` for the displayBlanksAs parameter.

**Reference:** [PhpSpreadsheet Issue #4411](https://github.com/PHPOffice/PhpSpreadsheet/issues/4411)

### 2. Output Buffer and BOM

**Issue:** Any output (whitespace, BOM, echo, deprecation warnings) before binary Excel content corrupts the file.

**Best practices:**
- Use `ob_start()` at the top of download/serve scripts to capture accidental output from includes
- Clear all buffers with `while (ob_get_level()) { ob_end_clean(); }` before sending headers and file content
- Ensure included PHP files have no BOM and no output before `<?php`
- Suppress deprecation warnings during generation (template-cli.php) to prevent PhpSpreadsheet XMLWriter warnings from affecting output

### 3. Formula and Data Validation

**Issue:** Invalid formula references, broken named ranges, or malformed data validation can cause Excel to repair the file.

**Best practices:**
- Use valid cell references (e.g. `A1:B10`, not out-of-range)
- Ensure `formula1`/`formula2` in data validation are numeric strings where expected
- Test conditional formatting rules for supported operators

### 4. Excel Tables and AutoFilter

**Status:** PhpSpreadsheet 5.5+ includes fix from PR #4556 (eliminates `xml:space="preserve"` from table nodes). **excel_table is supported** in template definitions.

**Best practices when using excel_table:**
- Use contiguous ranges (e.g. `A1:H20`)
- Ensure table name is unique and valid (alphanumeric, no spaces)
- Verify with regeneration and manual Excel/LibreOffice test before deploy

**Reference:** [PhpSpreadsheet Issue #4542](https://github.com/PHPOffice/PhpSpreadsheet/issues/4542) (fixed in PR #4556, included in 5.5+)

## PhpSpreadsheet Version

- **Current:** 5.5.0 (includes displayBlanksAs fix and performance improvements)
- **Minimum:** 5.5.0 (required for PhpSpreadsheet 5.x API compatibility)
- **Recommended:** ^5.5 in composer.json to receive patches

## Validation Workflow

1. **After regeneration:** Run `python3 v2/scripts/dev-helpers/validate-generated-xlsx.py`
2. **Excel MCP inspection (optional, when debugging):** Use Excel MCP to inspect formulas, verify data validation rules, check conditional formatting, and read cell values. Use when Python/PHP validation scripts find issues or for detailed inspection. Example: "Use Excel MCP to inspect v2/systems/excel-template-generator/output/generated-templates/dienstplan-excel-vorlage.xlsx"
3. **Before deploy:** Open key templates (e.g. dienstplan-excel-vorlage.xlsx) in Microsoft Excel and LibreOffice – no repair prompt
4. **Download test:** Fetch via `/v2/api/download-template.php?template_id=dienstplan-excel-vorlage` and open

**When to use Excel MCP vs Python/PHP scripts:**
- **Python/PHP scripts:** Primary validation mechanism - use for automated validation, CI/CD, batch checks
- **Excel MCP:** Inspection/debugging tool - use when scripts find issues, for formula verification, structure checks, or detailed inspection
- **Manual Excel/LibreOffice:** Final verification before deploy - visual inspection for repair prompts

## Troubleshooting: Excel Repair Prompt

When users see "We found a problem with some content" or "Excel was able to open the file by repairing or removing the unreadable content":

### Diagnosis Steps

1. **Inspect XLSX structure:**
   ```bash
   python3 v2/scripts/dev-helpers/inspect-xlsx-structure.py path/to/file.xlsx
   ```
   Checks for `dispBlanksAs val="0"` in charts and XML parse errors.

2. **Validate all generated files:**
   ```bash
   python3 v2/scripts/dev-helpers/validate-generated-xlsx.py
   ```

3. **Regenerate and validate:**
   ```bash
   php v2/systems/excel-template-generator/scripts/regenerate-all-templates-cli.php
   ```
   The script runs validation automatically after generation.

4. **Check PhpSpreadsheet version:** `composer show phpoffice/phpspreadsheet` – must be 5.5.0+

5. **Use Excel MCP** (if available): Inspect the generated file to verify formulas and structure.
   - Read formulas from cells to verify they match template definition
   - Check data validation rules are correctly applied
   - Verify conditional formatting ranges
   - Inspect cell values and structure
   - Compare template structure against expected definition
   - Example: "Use Excel MCP to read formulas from v2/systems/excel-template-generator/output/generated-templates/dienstplan-excel-vorlage.xlsx"

### Common Fixes

| Symptom | Fix |
|---------|-----|
| "Repaired Records: Table from /xl/tables/table1.xml" | Remove `excel_table` from template definition (dienstplan-basic.json, shift-planning-basic.json) |
| Charts in template | Upgrade PhpSpreadsheet to 5.5.0+ |
| Deprecation during generation | Already suppressed in template-cli.php |
| Download corruption | download-template.php uses ob_start and buffer clearing |
| Invalid formulas | Audit template definition; fix formula1/formula2 references |

### References

- [PhpSpreadsheet Issue #4411](https://github.com/PHPOffice/PhpSpreadsheet/issues/4411)
- [excel-template-integrity.mdc](../../../.cursor/rules/excel-template-integrity.mdc)

## Excel MCP Integration

**Excel MCP is for inspection/debugging, not template generation.** Template generation uses PhpSpreadsheet (production code path).

### When to Use Excel MCP

1. **Corruption Diagnosis:** When `validate-generated-xlsx.py` or `inspect-xlsx-structure.py` finds issues, use Excel MCP to inspect the actual file structure
2. **Formula Verification:** Read formulas from cells to verify they match template definition
3. **Structure Validation:** Verify sheet names, named ranges, data validation rules are correctly applied
4. **Quality Checks:** Verify conditional formatting, charts, styling match expected output
5. **Template Comparison:** Compare generated template against expected structure or competitor templates

### Excel MCP Tools

- `read_data_from_excel` - Read cell values, formulas, data validation rules from Excel files
- `write_data_to_excel` - Write data to Excel files (for testing/debugging only)
- `apply_formula` - Apply formulas to cells (for testing/debugging only)

### Example Workflows

**Formula Verification:**
```
Use Excel MCP to read formulas from v2/systems/excel-template-generator/output/generated-templates/dienstplan-excel-vorlage.xlsx
```

**Structure Inspection:**
```
Use Excel MCP to inspect the structure of v2/systems/excel-template-generator/output/generated-templates/schichtplan-excel-vorlage.xlsx and verify data validation rules
```

**Corruption Diagnosis:**
```
After running validate-generated-xlsx.py found issues, use Excel MCP to inspect v2/systems/excel-template-generator/output/generated-templates/compliance-checkliste-vorlage.xlsx
```

**See:** `docs/systems/excel-generator/EXCEL_MCP_INTEGRATION.md` for comprehensive guide and `docs/systems/excel-generator/EXCEL_MCP_EXAMPLES.md` for detailed examples

---

## References

- [REGENERATION_WORKFLOW.md](../../../v2/systems/excel-template-generator/docs/REGENERATION_WORKFLOW.md)
- [EXCEL_MCP_INTEGRATION.md](EXCEL_MCP_INTEGRATION.md) – Excel MCP integration guide
- [EXCEL_MCP_EXAMPLES.md](EXCEL_MCP_EXAMPLES.md) – Excel MCP usage examples
- [EXCEL_BEST_PRACTICES.md](EXCEL_BEST_PRACTICES.md) – Design best practices
- [PhpSpreadsheet PR #4414](https://github.com/PHPOffice/PhpSpreadsheet/pull/4414) – displayBlanksAs fix