# XLSX

> Comprehensive spreadsheet creation, editing, and analysis with support for formulas, formatting, data analysis, and visualization. Provides intelligent header detection and semantic column mapping for robust data import.

- Skill: `majiayu000/xlsx-4` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add majiayu000/xlsx-4`
- Raw SKILL.md: https://api.skillmd.com/api/skills/majiayu000/xlsx-4/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- License: Proprietary. LICENSE.txt has complete terms
- Author: majiayu000 (https://skillmd.com/u/majiayu000)
- Updated: 2026-09-09
- Page: https://skillmd.com/skills/majiayu000/xlsx-4

---


# Intelligence Capabilities (New)

When working with imperfect input files (e.g., user uploads, legacy system exports), apply the following intelligent processing strategies.

## 1. Intelligent Header Detection
**Full Guide**: [header-detection.md](intelligence/header-detection.md)

Never assume the first row is the header. Use the following heuristics:
- **Semantic Analysis**: Look for row content matching known keywords using Levenshtein distance (e.g., "Telefome" ≈ "Telefone").
- **Data Pattern Analysis**: The header is typically the last text-heavy row before data-heavy rows (dates, emails, CPFs).
- **Context**: Headers have high density (few empty cells) and are often below metadata rows (titles, dates).

## 2. Semantic Column Mapping
**Full Guide**: [column-mapping.md](intelligence/column-mapping.md)

Map source columns to target fields using specific scoring:
- **Semantic (40%)**: Normalization + Synonyms (e.g., "Zap" -> "Phone").
- **Pattern (30%)**: Data validation samples (e.g., column has `\d{11}` -> "CPF").
- **Context (30%)**: Position and frequency of occurrence.

## 3. Real-World Examples
**See**: [examples.md](intelligence/examples.md) for handling:
- Metadata headers (Reports with titles)
- Ambiguous columns (Date formats)
- Unknown columns

---

# Requirements for Outputs

## All Excel files

### Zero Formula Errors
- Every Excel model MUST be delivered with ZERO formula errors (#REF!, #DIV/0!, #VALUE!, #N/A, #NAME?)

### Preserve Existing Templates (when updating templates)
- Study and EXACTLY match existing format, style, and conventions when modifying files
- Never impose standardized formatting on files with established patterns
- Existing template conventions ALWAYS override these guidelines

## Financial models

### Color Coding Standards
Unless otherwise stated by the user or existing template

#### Industry-Standard Color Conventions
- **Blue text (RGB: 0,0,255)**: Hardcoded inputs, and numbers users will change for scenarios
- **Black text (RGB: 0,0,0)**: ALL formulas and calculations
- **Green text (RGB: 0,128,0)**: Links pulling from other worksheets within same workbook
- **Red text (RGB: 255,0,0)**: External links to other files
- **Yellow background (RGB: 255,255,0)**: Key assumptions needing attention or cells that need to be updated

### Number Formatting Standards

#### Required Format Rules
- **Years**: Format as text strings (e.g., "2024" not "2,024")
- **Currency**: Use $#,##0 format; ALWAYS specify units in headers ("Revenue ($mm)")
- **Zeros**: Use number formatting to make all zeros "-", including percentages (e.g., "$#,##0;($#,##0);-")
- **Percentages**: Default to 0.0% format (one decimal)
- **Multiples**: Format as 0.0x for valuation multiples (EV/EBITDA, P/E)
- **Negative numbers**: Use parentheses (123) not minus -123

### Formula Construction Rules

#### Assumptions Placement
- Place ALL assumptions (growth rates, margins, multiples, etc.) in separate assumption cells
- Use cell references instead of hardcoded values in formulas
- Example: Use =B5*(1+$B$6) instead of =B5*1.05

#### Formula Error Prevention
- Verify all cell references are correct
- Check for off-by-one errors in ranges
- Ensure consistent formulas across all projection periods
- Test with edge cases (zero values, negative numbers)
- Verify no unintended circular references

#### Documentation Requirements for Hardcodes
- Comment or in cells beside (if end of table). Format: "Source: [System/Document], [Date], [Specific Reference], [URL if applicable]"
- Examples:
  - "Source: Company 10-K, FY2024, Page 45, Revenue Note, [SEC EDGAR URL]"
  - "Source: Company 10-Q, Q2 2025, Exhibit 99.1, [SEC EDGAR URL]"
  - "Source: Bloomberg Terminal, 8/15/2025, AAPL US Equity"
  - "Source: FactSet, 8/20/2025, Consensus Estimates Screen"

# XLSX creation, editing, and analysis

## Overview

A user may ask you to create, edit, or analyze the contents of an .xlsx file. You have different tools and workflows available for different tasks.

## Important Requirements

**LibreOffice Required for Formula Recalculation**: You can assume LibreOffice is installed for recalculating formula values using the `recalc.py` script. The script automatically configures LibreOffice on first run

## Reading and analyzing data

### Data analysis with pandas
For data analysis, visualization, and basic operations, use **pandas** which provides powerful data manipulation capabilities:

```python
import pandas as pd

# Read Excel
df = pd.read_excel('file.xlsx')  # Default: first sheet
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)  # All sheets as dict

# Analyze
df.head()      # Preview data
df.info()      # Column info
df.describe()  # Statistics

# Write Excel
df.to_excel('output.xlsx', index=False)
```

## Excel File Workflows

## CRITICAL: Use Formulas, Not Hardcoded Values

**Always use Excel formulas instead of calculating values in Python and hardcoding them.** This ensures the spreadsheet remains dynamic and updateable.

### ❌ WRONG - Hardcoding Calculated Values
```python
# Bad: Calculating in Python and hardcoding result
total = df['Sales'].sum()
sheet['B10'] = total  # Hardcodes 5000

# Bad: Computing growth rate in Python
growth = (df.iloc[-1]['Revenue'] - df.iloc[0]['Revenue']) / df.iloc[0]['Revenue']
sheet['C5'] = growth  # Hardcodes 0.15

# Bad: Python calculation for average
avg = sum(values) / len(values)
sheet['D20'] = avg  # Hardcodes 42.5
```

### ✅ CORRECT - Using Excel Formulas
```python
# Good: Let Excel calculate the sum
sheet['B10'] = '=SUM(B2:B9)'

# Good: Growth rate as Excel formula
sheet['C5'] = '=(C4-C2)/C2'

# Good: Average using Excel function
sheet['D20'] = '=AVERAGE(D2:D19)'
```

This applies to ALL calculations - totals, percentages, ratios, differences, etc. The spreadsheet should be able to recalculate when source data changes.

## Common Workflow
1. **Choose tool**: pandas for data, openpyxl for formulas/formatting
2. **Create/Load**: Create new workbook or load existing file
3. **Modify**: Add/edit data, formulas, and formatting
4. **Save**: Write to file
5. **Recalculate formulas (MANDATORY IF USING FORMULAS)**: Use the recalc.py script
   ```bash
   python recalc.py output.xlsx
   ```
6. **Verify and fix any errors**:
   - The script returns JSON with error details
   - If `status` is `errors_found`, check `error_summary` for specific error types and locations
   - Fix the identified errors and recalculate again
   - Common errors to fix:
     - `#REF!`: Invalid cell references
     - `#DIV/0!`: Division by zero
     - `#VALUE!`: Wrong data type in formula
     - `#NAME?`: Unrecognized formula name

### Creating new Excel files

```python
# Using openpyxl for formulas and formatting
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = Workbook()
sheet = wb.active

# Add data
sheet['A1'] = 'Hello'
sheet['B1'] = 'World'
sheet.append(['Row', 'of', 'data'])

# Add formula
sheet['B2'] = '=SUM(A1:A10)'

# Formatting
sheet['A1'].font = Font(bold=True, color='FF0000')
sheet['A1'].fill = PatternFill('solid', start_color='FFFF00')
sheet['A1'].alignment = Alignment(horizontal='center')

# Column width
sheet.column_dimensions['A'].width = 20

wb.save('output.xlsx')
```

### Editing existing Excel files

```python
# Using openpyxl to preserve formulas and formatting
from openpyxl import load_workbook

# Load existing file
wb = load_workbook('existing.xlsx')
sheet = wb.active  # or wb['SheetName'] for specific sheet

# Working with multiple sheets
for sheet_name in wb.sheetnames:
    sheet = wb[sheet_name]
    print(f"Sheet: {sheet_name}")

# Modify cells
sheet['A1'] = 'New Value'
sheet.insert_rows(2)  # Insert row at position 2
sheet.delete_cols(3)  # Delete column 3

# Add new sheet
new_sheet = wb.create_sheet('NewSheet')
new_sheet['A1'] = 'Data'

wb.save('modified.xlsx')
```

## Recalculating formulas

Excel files created or modified by openpyxl contain formulas as strings but not calculated values. Use the provided `recalc.py` script to recalculate formulas:

```bash
python recalc.py <excel_file> [timeout_seconds]
```

Example:
```bash
python recalc.py output.xlsx 30
```

The script:
- Automatically sets up LibreOffice macro on first run
- Recalculates all formulas in all sheets
- Scans ALL cells for Excel errors (#REF!, #DIV/0!, etc.)
- Returns JSON with detailed error locations and counts
- Works on both Linux and macOS

## Formula Verification Checklist

Quick checks to ensure formulas work correctly:

### Essential Verification
- [ ] **Test 2-3 sample references**: Verify they pull correct values before building full model
- [ ] **Column mapping**: Confirm Excel columns match (e.g., column 64 = BL, not BK)
- [ ] **Row offset**: Remember Excel rows are 1-indexed (DataFrame row 5 = Excel row 6)

### Common Pitfalls
- [ ] **NaN handling**: Check for null values with `pd.notna()`
- [ ] **Far-right columns**: FY data often in columns 50+
- [ ] **Multiple matches**: Search all occurrences, not just first
- [ ] **Division by zero**: Check denominators before using `/` in formulas (#DIV/0!)
- [ ] **Wrong references**: Verify all cell references point to intended cells (#REF!)
- [ ] **Cross-sheet references**: Use correct format (Sheet1!A1) for linking sheets

### Formula Testing Strategy
- [ ] **Start small**: Test formulas on 2-3 cells before applying broadly
- [ ] **Verify dependencies**: Check all cells referenced in formulas exist
- [ ] **Test edge cases**: Include zero, negative, and very large values

### Interpreting recalc.py Output
The script returns JSON with error details:
```json
{
  "status": "success",           // or "errors_found"
  "total_errors": 0,              // Total error count
  "total_formulas": 42,           // Number of formulas in file
  "error_summary": {              // Only present if errors found
    "#REF!": {
      "count": 2,
      "locations": ["Sheet1!B5", "Sheet1!C10"]
    }
  }
}
```

## Best Practices

### Library Selection
- **pandas**: Best for data analysis, bulk operations, and simple data export
- **openpyxl**: Best for complex formatting, formulas, and Excel-specific features

### Working with openpyxl
- Cell indices are 1-based (row=1, column=1 refers to cell A1)
- Use `data_only=True` to read calculated values: `load_workbook('file.xlsx', data_only=True)`
- **Warning**: If opened with `data_only=True` and saved, formulas are replaced with values and permanently lost
- For large files: Use `read_only=True` for reading or `write_only=True` for writing
- Formulas are preserved but not evaluated - use recalc.py to update values

### Working with pandas
- Specify data types to avoid inference issues: `pd.read_excel('file.xlsx', dtype={'id': str})`
- For large files, read specific columns: `pd.read_excel('file.xlsx', usecols=['A', 'C', 'E'])`
- Handle dates properly: `pd.read_excel('file.xlsx', parse_dates=['date_column'])`

## Code Style Guidelines
**IMPORTANT**: When generating Python code for Excel operations:
- Write minimal, concise Python code without unnecessary comments
- Avoid verbose variable names and redundant operations
- Avoid unnecessary print statements

**For Excel files themselves**:
- Add comments to cells with complex formulas or important assumptions
- Document data sources for hardcoded values
- Include notes for key calculations and model sections

---

# Intelligent Spreadsheet Import and Analysis

## Overview

When importing or analyzing spreadsheets, especially for data import workflows, intelligent detection and mapping capabilities are essential for handling real-world spreadsheets that may have:
- Non-standard layouts (metadata rows, empty rows, merged cells)
- Varied column naming (synonyms, abbreviations, typos)
- Multiple data types requiring pattern recognition
- Contextual relationships between columns

This section provides advanced techniques for intelligent header detection, column mapping, and data pattern analysis.

---

## Intelligent Header Detection

### Overview

Header detection is the process of identifying which row in a spreadsheet contains the column names. Real-world spreadsheets often have:
- Title rows or metadata at the top
- Empty separator rows
- Multi-line headers (merged cells)
- Headers that don't start at row 1

### Detection Strategy: Multi-Factor Analysis

Use a combination of semantic analysis, pattern recognition, and contextual clues to identify headers with high confidence.

#### 1. Semantic Analysis

**String Similarity Algorithms**

Use similarity metrics to compare row cells against known header keywords. Implement Levenshtein distance, Jaro-Winkler similarity, and normalization functions to handle:
- Accent removal (á → a)
- Case insensitivity
- Whitespace normalization
- Synonym matching

**Synonym Recognition**

Maintain a knowledge base of header synonyms and variations in multiple languages (Portuguese, English) and handle:
- Regional variations ("celular" vs "móvel")
- Common typos ("E-mail" vs "Email")
- Abbreviations ("Tel" = "Telefone")
- Context-dependent names ("Cliente" = "Nome" in CRM context)

#### 2. Pattern-Based Data Type Detection

Analyze sample data values (first 5-10 rows) to infer column types:
- **CPF**: 11 digits, format XXX.XXX.XXX-XX
- **Email**: Contains @ and valid domain
- **Phone**: 10-11 digits, Brazilian formats
- **Date**: Multiple formats (DD/MM/YYYY, MM/DD/YYYY, Excel serial)
- **Monetary**: R$, points, commas (Brazilian format)
- **CEP**: 8 digits, format XXXXX-XXX

Use pattern matching with regex and validation functions to detect types with confidence scores.

#### 3. Contextual Analysis

Consider multiple contextual factors:
- **Position**: Headers usually in first 5 rows
- **Density**: Headers have many filled cells
- **Consistency**: Headers are text, data rows vary in type
- **Structure**: Headers have similar length/format

### Multi-Factor Scoring

Calculate header likelihood score using:
- Text ratio (30%): Headers are usually text, not numbers
- Keyword matches (40%): Match against known header keywords
- Fill ratio (20%): Headers usually have many filled cells
- Position bonus (10%): Headers usually in first rows

### Thresholds and Confidence Levels

- **Confidence ≥ 0.8**: High confidence - use automatically
- **Confidence 0.6-0.8**: Medium confidence - show to user for confirmation
- **Confidence < 0.6**: Low confidence - require user selection

### Handling Edge Cases

- **Multiple Header Rows**: Detect merged cells spanning multiple rows
- **Empty Rows**: Skip empty separator rows before data
- **Metadata Rows**: Identify and skip title/metadata rows at top

---

## Intelligent Column Mapping

### Overview

Column mapping matches spreadsheet column names to system fields. Real-world spreadsheets use varied naming that requires intelligent matching.

### Multi-Factor Scoring System

Use weighted scoring to determine best mapping:

- **Semantic Similarity (40%)**: String similarity and synonym matching
- **Data Pattern Match (30%)**: Type inferred from actual values
- **Position Context (15%)**: Expected position of field
- **Frequency/Common Name (15%)**: Most common names get priority

### Expanded Knowledge Base

Maintain comprehensive synonym mappings including:
- Portuguese and English variations
- Regional differences
- Common typos and abbreviations
- Context-dependent synonyms

**Example Knowledge Base Structure:**

```python
# Comprehensive header knowledge base for student/CRM imports
EXPANDED_HEADER_KNOWLEDGE = {
    # Name variations (regional, typos, languages)
    'name': [
        # Portuguese standard
        'nome', 'nome completo', 'aluno', 'cliente', 'pessoa',
        'nome do aluno', 'nome do cliente', 'razao social', 'razão social',
        # English
        'name', 'full name', 'client name', 'student name', 'person', 'fullname',
        # Abbreviations
        'nm', 'nome compl', 'nome compl.',
        # Common typos
        'nme', 'nomee', 'nom', 'nomes',
        # Context variations
        'cadastro', 'registro', 'identificação'
    ],

    # Email variations
    'email': [
        # Portuguese
        'email', 'e-mail', 'correio eletrônico', 'correio eletronico',
        'correio', 'endereço de email', 'endereco de email', 'endereço email',
        # English
        'mail', 'e-mail address', 'email address', 'mail address',
        # Abbreviations
        'e-mail', 'mail addr', 'email addr',
        # Common typos
        'emial', 'emal', 'eail', 'emai',
        # Variations
        'correio eletrônico', 'correio eletr.'
    ],

    # Phone variations
    'phone': [
        # Portuguese
        'telefone', 'celular', 'whatsapp', 'contato', 'telefone celular',
        'telefone fixo', 'fone', 'tel', 'telefone residencial',
        # English
        'phone', 'mobile', 'cell phone', 'telephone', 'contact number',
        'mobile phone', 'cell',
        # Regional
        'móvel', 'movel', 'cel',
        # Abbreviations
        'tel', 'fone', 'cel', 'whats', 'wpp',
        # Common typos
        'telefne', 'telefonee', 'celularr'
    ],

    # CPF variations
    'cpf': [
        'cpf', 'documento', 'documento cpf', 'cpf/cnpj', 'cpf cnpj',
        'identificação', 'identificacao', 'id', 'rg', 'documento de identidade',
        'doc', 'document', 'identifier', 'cpf cnpj', 'documento único',
        'documento unico', 'cpf documento', 'número cpf', 'numero cpf'
    ],

    # Address variations
    'address': [
        'endereço', 'endereco', 'rua', 'logradouro', 'street', 'address',
        'endereço completo', 'endereco completo', 'full address', 'endereço residencial',
        'endereco residencial', 'logradouro completo', 'rua endereço'
    ],

    'addressNumber': [
        'número', 'numero', 'nº', 'n°', 'num', 'número do endereço',
        'numero do endereco', 'address number', 'house number', 'nro'
    ],

    'complement': [
        'complemento', 'complement', 'apto', 'apartamento', 'bloco',
        'bloco apto', 'andar', 'sala', 'complemento endereço'
    ],

    'neighborhood': [
        'bairro', 'neighborhood', 'distrito', 'bairro residencial',
        'bairro endereço', 'zona', 'região', 'regiao'
    ],

    'city': [
        'cidade', 'municipio', 'município', 'city', 'municipality',
        'munic', 'cid', 'município de residência', 'cidade residência'
    ],

    'state': [
        'estado', 'uf', 'state', 'province', 'est', 'uf estado',
        'estado uf', 'unidade federativa', 'estado brasileiro'
    ],

    'zipCode': [
        'cep', 'código postal', 'codigo postal', 'zip', 'postal code',
        'cep código', 'postal', 'cep código postal', 'zip code',
        'código postal cep'
    ],

    'country': [
        'país', 'pais', 'country', 'nacionalidade', 'país de origem',
        'pais de origem', 'country of origin'
    ],

    # Professional fields
    'profession': [
        'profissão', 'profissao', 'graduação', 'graduacao', 'formação',
        'formacao', 'profession', 'graduation', 'formation', 'curso',
        'área de atuação', 'area de atuacao', 'especialidade', 'especialização'
    ],

    'hasClinic': [
        'tem clínica', 'tem clinica', 'possui clínica', 'possui clinica',
        'tem estabelecimento', 'has clinic', 'own clinic', 'clinic owner',
        'clínica própria', 'clinica propria'
    ],

    'clinicName': [
        'nome clínica', 'nome clinica', 'clínica nome', 'clinica nome',
        'nome estabelecimento', 'nome do estabelecimento', 'clinic name',
        'estabelecimento', 'nome da clínica'
    ],

    'clinicCity': [
        'cidade clínica', 'cidade clinica', 'cidade estabelecimento',
        'município clínica', 'municipio clinica', 'clinic city',
        'cidade da clínica'
    ],

    # Status and dates
    'status': [
        'status', 'situação', 'situacao', 'estado', 'condição', 'condicao',
        'status aluno', 'situação aluno', 'status do aluno'
    ],

    'birthDate': [
        'data nasc', 'data de nascimento', 'nascimento', 'data nascimento',
        'data nasc.', 'birth date', 'date of birth', 'dob', 'data nascimento aluno',
        'nasc', 'data nascimento aluno'
    ],

    'saleDate': [
        'data da venda', 'data venda', 'data de venda', 'venda data',
        'data contrato', 'data do contrato', 'sale date', 'contract date',
        'data assinatura', 'data de assinatura'
    ],

    'startDate': [
        'data início', 'data inicio', 'data_inicio', 'start date',
        'inicio', 'data de início', 'data de inicio', 'data início curso',
        'início curso', 'inicio curso'
    ],

    # Sales and marketing
    'salesperson': [
        'vendedor', 'vendedora', 'vendedor responsável', 'vendedor responsavel',
        'salesperson', 'seller', 'vendedor da venda', 'responsável venda',
        'responsavel venda'
    ],

    'leadSource': [
        'origem lead', 'origem', 'fonte', 'lead source', 'source',
        'origem do lead', 'fonte do lead', 'como conheceu', 'origem cliente',
        'canal', 'canal de origem', 'fonte de origem'
    ],

    'cohort': [
        'turma', 'cohort', 'turma curso', 'turma do curso', 'grupo',
        'turma aluno', 'classe', 'batch', 'lote'
    ],

    # Financial fields
    'totalValue': [
        'valor total', 'valortotal', 'valor_total', 'total', 'amount',
        'valor', 'preço', 'preco', 'price', 'total value', 'total amount',
        'valor do curso', 'valor curso', 'valor contrato', 'valor total curso',
        'montante', 'valor pago', 'valor investido'
    ],

    'installments': [
        'parcelas', 'número de parcelas', 'numero de parcelas',
        'qtd parcelas', 'quantidade de parcelas', 'installments',
        'parc', 'qtd parc', 'número parcelas', 'numero parcelas',
        'total parcelas', 'qtd de parcelas', 'quantidade parcelas'
    ],

    'installmentValue': [
        'valor parcela', 'valor_parcela', 'valorparcela', 'installment value',
        'valor da parcela', 'valor de parcela', 'parcela valor',
        'valor mensal', 'mensalidade', 'valor por parcela'
    ],

    'paymentStatus': [
        'status pagamento', 'status_pagamento', 'payment status',
        'pagamento', 'status de pagamento', 'situação pagamento',
        'situacao pagamento', 'payment', 'status pag', 'situação pag',
        'condição pagamento', 'condicao pagamento'
    ],

    'paidInstallments': [
        'parcelas pagas', 'parcelas_pagas', 'paid installments',
        'parcelas quitadas', 'parcelas pagas total', 'qtd parcelas pagas',
        'quantidade parcelas pagas', 'parcelas já pagas', 'parcelas ja pagas'
    ],

    # Professional ID
    'professionalId': [
        'registro', 'registro profissional', 'coren', 'cro', 'crm', 'crf',
        'número registro', 'numero registro', 'registro número',
        'professional id', 'professional registration', 'número conselho',
        'numero conselho', 'conselho profissional', 'registro conselho'
    ],

    # Contract status
    'contractStatus': [
        'status contrato', 'status do contrato', 'situação contrato',
        'situacao contrato', 'contract status', 'status do contrato',
        'condição contrato', 'condicao contrato', 'estado contrato'
    ]
}

def find_best_match(column_header: str, knowledge_base: Dict[str, List[str]]) -> List[Tuple[str, float]]:
    """
    Find best matching schema fields for a column header.
    Returns list of (field_name, confidence_score) tuples sorted by score.
    """
    normalized_header = normalize_header(column_header)
    matches = []

    for field_name, synonyms in knowledge_base.items():
        best_score = 0.0

        for synonym in synonyms:
            normalized_synonym = normalize_header(synonym)

            # Exact match
            if normalized_header == normalized_synonym:
                best_score = 1.0
                break
            # Contains match
            elif normalized_synonym in normalized_header or normalized_header in normalized_synonym:
                best_score = max(best_score, 0.9)
            else:
                # Similarity match
                similarity = jaro_winkler_similarity(normalized_header, normalized_synonym)
                if similarity > 0.7:
                    best_score = max(best_score, similarity * 0.85)

        if best_score > 0.5:  # Only include reasonable matches
            matches.append((field_name, best_score))

    # Sort by score descending
    matches.sort(key=lambda x: x[1], reverse=True)
    return matches
```

### Intelligent Suggestions

When confidence is low (< 70%), provide multiple mapping options:
- Show top 3-5 suggestions ordered by score
- Include reasoning for each suggestion
- Highlight pattern mismatches (e.g., column named "ID" but contains CPF values)

---

## Data Pattern Analysis

### Overview

Pattern analysis identifies data types and formats by examining actual values, not just column names. This is crucial for:
- Validating mappings
- Detecting errors
- Normalizing data formats
- Providing user feedback

### Pattern Recognition

Define comprehensive patterns for common data types:
- **CPF**: Brazilian tax ID with validation algorithm
- **Email**: Standard email format validation
- **Phone (Brazilian)**: 10-11 digits with formatting
- **Date**: Multiple format support (DD/MM/YYYY, Excel serial)
- **Monetary (Brazilian)**: R$ format with comma decimal separator
- **CEP**: Brazilian postal code (8 digits)
- **UF**: Brazilian state abbreviations (2 letters)

### Validation and Normalization

For each pattern type:
- **Regex matching**: Initial pattern detection
- **Validator function**: Domain-specific validation (e.g., CPF check digits)
- **Normalizer function**: Convert to standard format
- **Formatter function**: Display in user-friendly format

### Pattern Detection Process

1. Sample first 10-20 rows of data
2. Test each value against pattern regexes
3. Run validator functions for matched patterns
4. Calculate match ratio and confidence
5. Return best match with confidence score

---

## Integration with Existing Code

### Integration with xlsx-helper.ts

Enhance `detectHeaderRow()` function in `src/lib/xlsx-helper.ts`:

**Current Implementation:**
```typescript
// Current basic implementation
export function detectHeaderRow(rows: unknown[][], maxRowsToScan = 15): HeaderDetectionResult {
  // Basic keyword matching and scoring
}
```

**Enhanced Implementation:**
```typescript
// Enhanced with multi-factor analysis
export function detectHeaderRowEnhanced(
  rows: unknown[][],
  maxRowsToScan = 15
): HeaderDetectionResult {
  const candidates: Array<{
    rowIndex: number;
    score: number;
    headers: string[];
  }> = [];

  // Factor 1: Text ratio (headers are usually text)
  const calculateTextRatio = (row: unknown[]): number => {
    const textCells = row.filter(
      cell => cell && !String(cell).trim().match(/^\d+([.,]\d+)?$/)
    );
    return textCells.length / Math.max(row.length, 1);
  };

  // Factor 2: Keyword matches
  const countKeywordMatches = (row: unknown[]): number => {
    const normalizedRow = row.map(cell =>
      normalizeHeader(String(cell || ''))
    );
    return normalizedRow.filter(cell =>
      HEADER_KEYWORDS.some(kw => cell.includes(kw) || kw.includes(cell))
    ).length;
  };

  // Factor 3: Fill ratio
  const calculateFillRatio = (row: unknown[]): number => {
    const nonEmpty = row.filter(cell => cell && String(cell).trim());
    return nonEmpty.length / Math.max(row.length, 1);
  };

  // Factor 4: Pattern consistency (compare with next row)
  const checkPatternConsistency = (
    row: unknown[],
    nextRow: unknown[],
    index: number
  ): number => {
    if (index + 1 >= rows.length) return 0;

    const rowTypes = row.map(cell => typeof cell);
    const nextTypes = nextRow.map(cell => typeof cell);

    // Headers should have different types than data
    const typeOverlap = rowTypes.filter((t, i) => t === nextTypes[i]).length;
    return typeOverlap < rowTypes.length / 2 ? 5 : 0;
  };

  // Calculate scores for each candidate row
  for (let i = 0; i < Math.min(rows.length, maxRowsToScan); i++) {
    const row = rows[i];
    if (!row || row.length === 0) continue;

    let score = 0;

    // Factor 1: Text ratio (30 points max)
    score += calculateTextRatio(row) * 30;

    // Factor 2: Keyword matches (40 points max)
    score += Math.min(countKeywordMatches(row) * 10, 40);

    // Factor 3: Fill ratio (20 points max)
    score += calculateFillRatio(row) * 20;

    // Factor 4: Position bonus (10 points)
    if (i < 5) score += 10;

    // Factor 5: Pattern consistency (5 points)
    score += checkPatternConsistency(row, rows[i + 1] || [], i);

    const headers = row
      .map(h => String(h ?? '').trim())
      .filter(Boolean);

    if (headers.length >= 2) {
      candidates.push({
        rowIndex: i,
        score,
        headers,
      });
    }
  }

  if (candidates.length === 0) {
    return {
      headerRowIndex: 0,
      confidence: 0,
      headers: rows[0]?.map(h => String(h ?? '').trim()).filter(Boolean) || [],
      candidates: [],
    };
  }

  candidates.sort((a, b) => b.score - a.score);
  const best = candidates[0];

  // Calculate confidence
  let confidence: number;
  if (candidates.length === 1) {
    confidence = Math.min(best.score / 100, 1);
  } else {
    const scoreDiff = best.score - candidates[1].score;
    const relativeConfidence = scoreDiff / Math.max(best.score, 1);
    const absoluteConfidence = best.score / 100;
    confidence = Math.min((relativeConfidence + absoluteConfidence) / 2, 1);
  }

  return {
    headerRowIndex: best.rowIndex,
    confidence: Math.round(confidence * 100) / 100,
    headers: best.headers,
    candidates: candidates.slice(0, 5),
  };
}

// Helper function for header normalization
function normalizeHeader(header: string): string {
  return header
    .toLowerCase()
    .normalize('NFD')
    .replace(/[\u0300-\u036f]/g, '') // Remove accents
    .trim()
    .replace(/\s+/g, ' ');
}
```

### Integration with csv-validator.ts

Enhance `mapCSVHeaders()` function:

**Current Implementation:**
```typescript
// Current static mapping
export function mapCSVHeaders(headers: string[]): Record<string, string> {
  const mapping: Record<string, string> = {};
  for (const header of headers) {
    const normalized = header.trim().toLowerCase();
    if (HEADER_MAP[normalized]) {
      mapping[header] = HEADER_MAP[normalized];
    }
  }
  return mapping;
}
```

**Enhanced Implementation:**
```typescript
// Enhanced with pattern analysis and fuzzy matching
export interface ColumnMappingResult {
  mapping: Record<string, string>;
  confidence: Record<string, number>;
  suggestions: Record<string, Array<{ field: string; score: number; reason: string }>>;
}

export function mapCSVHeadersIntelligent(
  headers: string[],
  sampleRows: Record<string, unknown>[],
  maxSuggestions = 3
): ColumnMappingResult {
  const mapping: Record<string, string> = {};
  const confidence: Record<string, number> = {};
  const suggestions: Record<string, Array<{ field: string; score: number; reason: string }>> = {};

  for (const header of headers) {
    // Get column data
    const columnData = sampleRows
      .map(row => row[header])
      .filter(v => v !== undefined && v !== null)
      .slice(0, 10); // Sample first 10 rows

    // Detect pattern
    const pattern = detectColumnPattern(columnData);

    // Find best matches
    const matches = findBestSchemaMatches(header, pattern, headers.indexOf(header));

    if (matches.length > 0 && matches[0].score >= 0.5) {
      mapping[header] = matches[0].field;
      confidence[header] = matches[0].score;
    }

    // Store suggestions for low confidence matches
    if (matches.length > 0 && matches[0].score < 0.7) {
      suggestions[header] = matches.slice(0, maxSuggestions).map(m => ({
        field: m.field,
        score: m.score,
        reason: m.reason,
      }));
    }
  }

  return { mapping, confidence, suggestions };
}

// Pattern detection function
function detectColumnPattern(values: unknown[]): {
  type: string;
  confidence: number;
  matchRatio: number;
} {
  if (values.length === 0) {
    return { type: 'empty', confidence: 0, matchRatio: 0 };
  }

  // CPF pattern
  const cpfPattern = /^\d{3}[.\-]?\d{3}[.\-]?\d{3}[.\-]?\d{2}$/;
  const cpfMatches = values.filter(v => cpfPattern.test(String(v).replace(/\s/g, '')));
  if (cpfMatches.length / values.length > 0.7) {
    return {
      type: 'cpf',
      confidence: cpfMatches.length / values.length,
      matchRatio: cpfMatches.length / values.length,
    };
  }

  // Email pattern
  const emailPattern = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  const emailMatches = values.filter(v => emailPattern.test(String(v).toLowerCase()));
  if (emailMatches.length / values.length > 0.7) {
    return {
      type: 'email',
      confidence: emailMatches.length / values.length,
      matchRatio: emailMatches.length / values.length,
    };
  }

  // Phone pattern (Brazilian)
  const phonePattern = /^[\d\s\(\)\-\+]{10,15}$/;
  const phoneMatches = values.filter(v => {
    const digits = String(v).replace(/\D/g, '');
    return phonePattern.test(String(v)) && digits.length >= 10 && digits.length <= 11;
  });
  if (phoneMatches.length / values.length > 0.7) {
    return {
      type: 'phone',
      confidence: phoneMatches.length / values.length,
      matchRatio: phoneMatches.length / values.length,
    };
  }

  return { type: 'text', confidence: 0.3, matchRatio: 0 };
}

// Find best schema matches with scoring
function findBestSchemaMatches(
  header: string,
  pattern: { type: string; confidence: number },
  columnIndex: number
): Array<{ field: string; score: number; reason: string }> {
  const normalizedHeader = normalizeHeader(header);
  const matches: Array<{ field: string; score: number; reason: string }> = [];

  // Check against expanded knowledge base
  for (const [field, synonyms] of Object.entries(EXPANDED_HEADER_KNOWLEDGE)) {
    let semanticScore = 0;
    let reason = '';

    // Check synonyms
    for (const synonym of synonyms) {
      const normalizedSynonym = normalizeHeader(synonym);

      if (normalizedHeader === normalizedSynonym) {
        semanticScore = 1.0;
        reason = `Exact match with '${synonym}'`;
        break;
      } else if (normalizedSynonym.includes(normalizedHeader) ||
                 normalizedHeader.includes(normalizedSynonym)) {
        semanticScore = Math.max(semanticScore, 0.9);
        reason = `Contains keyword '${synonym}'`;
      } else {
        // Fuzzy match
        const similarity = jaroWinklerSimilarity(normalizedHeader, normalizedSynonym);
        if (similarity > 0.7) {
          semanticScore = Math.max(semanticScore, similarity * 0.85);
          reason = `Similar to '${synonym}' (${Math.round(similarity * 100)}% similarity)`;
        }
      }
    }

    // Pattern match bonus
    const patternScore = getPatternMatchScore(field, pattern.type);
    const totalScore = semanticScore * 0.4 + patternScore * 0.3 +
                       getPositionScore(field, columnIndex) * 0.15 +
                       getFrequencyScore(field, normalizedHeader) * 0.15;

    if (totalScore >= 0.3) {
      matches.push({
        field,
        score: totalScore,
        reason: reason || `Pattern matches: ${pattern.type}`,
      });
    }
  }

  return matches.sort((a, b) => b.score - a.score);
}

// Jaro-Winkler similarity (simplified)
function jaroWinklerSimilarity(s1: string, s2: string): number {
  // Use a library like 'string-similarity' in production
  // Simplified version here
  if (s1 === s2) return 1.0;
  if (s1.length === 0 || s2.length === 0) return 0.0;

  // Simple character overlap
  const longer = s1.length > s2.length ? s1 : s2;
  const shorter = s1.length > s2.length ? s2 : s1;
  const matches = shorter.split('').filter(c => longer.includes(c)).length;

  return matches / Math.max(s1.length, s2.length);
}
```

### Recommended Enhancements

1. **Add pattern detection**: Create `detectColumnPattern()` function in `xlsx-helper.ts`
2. **Expand knowledge base**: Add all synonyms from `EXPANDED_HEADER_KNOWLEDGE` to `HEADER_MAP` in `csv-validator.ts`
3. **Add validation feedback**: Show pattern detection results in UI components
4. **Highlight mismatches**: Alert when header name doesn't match data pattern
5. **Add confidence indicators**: Display confidence scores in mapping UI
6. **Store user corrections**: Learn from user mappings to improve future suggestions

---

## Practical Examples

### Example 1: Detecting Header in Spreadsheet with Metadata

**Spreadsheet Structure:**
```
Row 0: "Relatório de Alunos - 2024"
Row 1: (empty)
Row 2: "Nome" | "E-mail" | "Telefone" | "CPF"
Row 3: "João Silva" | "joao@email.com" | "11999999999" | "123.456.789-00"
Row 4: "Maria Santos" | "maria@email.com" | "21988888888" | "987.654.321-00"
```

**Detection Process:**

```python
rows = [
    ["Relatório de Alunos - 2024"],
    [],
    ["Nome", "E-mail", "Telefone", "CPF"],
    ["João Silva", "joao@email.com", "11999999999", "123.456.789-00"],
    ["Maria Santos", "maria@email.com", "21988888888", "987.654.321-00"]
]

result = detect_header_row(rows, max_rows_to_scan=15)

# Result:
# {
#   'header_row_index': 2,
#   'confidence': 0.85,
#   'headers': ['Nome', 'E-mail', 'Telefone', 'CPF'],
#   'candidates': [
#     {'row_index': 2, 'score': 85.0, 'headers': ['Nome', 'E-mail', 'Telefone', 'CPF']},
#     {'row_index': 0, 'score': 15.0, 'headers': ['Relatório de Alunos - 2024']}
#   ]
# }
```

**Scoring Breakdown for Row 2:**
- Text ratio: 100% (all cells are text) → 30 points
- Keyword matches: 4 matches (nome, email, telefone, cpf) → 40 points
- Fill ratio: 100% (all cells filled) → 20 points
- Position bonus: Row 2 (< 5) → 10 points
- **Total: 100 points → Confidence: 0.85**

**Mappings with Pattern Analysis:**

```python
# Analyze each column
mappings = map_columns_intelligently(
    headers=['Nome', 'E-mail', 'Telefone', 'CPF'],
    rows=rows,
    header_row_index=2
)

# Results:
# {
#   'Nome': ('name', 0.95),      # Exact keyword match + text pattern
#   'E-mail': ('email', 0.90),   # Synonym match + email pattern
#   'Telefone': ('phone', 0.92), # Keyword match + phone pattern
#   'CPF': ('cpf', 0.88)         # Keyword match + CPF pattern
# }
```

### Example 2: Ambiguous Column Name

**Column:** "Contato"

**Sample Data:**
```
"Contato"
"11999999999"
"21988888888"
"(11) 3333-4444"
```

**Analysis Process:**

```python
column_header = "Contato"
column_data = ["11999999999", "21988888888", "(11) 3333-4444"]
column_index = 2

# Step 1: Semantic analysis
semantic_score = find_synonym_match("Contato", "phone")
# Result: 0.85 (contato ≈ telefone/phone)

# Step 2: Pattern detection
pattern = detect_data_pattern(column_data)
# Result: {'type': 'phone', 'confidence': 0.90, 'format': 'brazilian'}

# Step 3: Position context
position_score = 0.70  # Column 2, expected phone position 2

# Step 4: Calculate total score
total_score = (
    semantic_score * 0.40 +      # 0.85 * 0.40 = 0.34
    pattern['confidence'] * 0.30 + # 0.90 * 0.30 = 0.27
    position_score * 0.15 +       # 0.70 * 0.15 = 0.105
    0.6 * 0.15                    # Frequency score = 0.09
)
# Total: 0.805 → Map to 'phone' with 80.5% confidence
```

**Result:** Map to `phone` (0.82 total score)

**Alternative Scenario - If pattern was emails:**

```python
column_data = ["joao@email.com", "maria@email.com", "pedro@email.com"]
pattern = detect_data_pattern(column_data)
# Result: {'type': 'email', 'confidence': 0.95}

# Semantic score for email: 0.60 (contato can mean contact/email)
total_score = 0.60 * 0.40 + 0.95 * 0.30 + 0.70 * 0.15 + 0.4 * 0.15
# Total: 0.72 → Map to 'email' with 72% confidence
```

### Example 3: Type Detection by Pattern (Header Name Mismatch)

**Column:** "ID"
**Values:**
```
"ID"
"123.456.789-00"
"987.654.321-00"
"111.222.333-44"
```

**Analysis:**

```python
column_header = "ID"
column_data = ["123.456.789-00", "987.654.321-00",

…(truncated)