# Technical Specification: Strikethrough Text Detection and Removal in Excel Processing Pipeline

## Overview

This specification outlines the implementation of strikethrough text detection and removal functionality in the Excel document processing pipeline before parsing with Docling. The goal is to identify cells with strikethrough styling and remove them from the extraction process to improve data quality.

## Problem Statement

The current Excel processing pipeline does not handle strikethrough text, which typically indicates deprecated, obsolete, or invalid data. Including strikethrough text in the final extraction can:
- Reduce data quality and accuracy
- Include outdated information in embeddings
- Confuse downstream processing and search results

## Current Pipeline Flow

### Excel Hybrid Pipeline Architecture

The system uses two parallel pipelines for Excel processing:

#### **NEW Pipeline** (Docling-based for large tables):
```
fetch_document
  → parse_excel_large_tables
  → detect_headers
  → chunk_large_table_records
  → extract_domain_terms_excel
  → generate_embeddings_large_tables
```

**Key components:**
- `parse_excel_large_tables` (airflow/dags/tasks/parse_tasks.py:21-123)
- `LargeTableExtractor.detect_tables()` (src/extraction_v2/large_table_extractor.py:95-121)
- `LargeTableExtractor.extract_table()` (src/extraction_v2/large_table_extractor.py:123-194)
- `LargeTableExtractor._process_data_rows()` (src/extraction_v2/large_table_extractor.py:402-481)

#### **DETAILED Pipeline** (openpyxl-based for images/shapes/comments):
```
fetch_document
  → parse_excel_detailed
  → upload_images_to_seaweedfs
  → generate_embeddings_chunks
  → extract_domain_terms_detailed
```

**Key components:**
- `parse_excel_detailed` (airflow/dags/tasks/parse_tasks.py:374-446)
- `ExcelParserService.parse_excel_file()` (src/extraction_v2/excel_parser_service.py)

#### **Merge Phase:**
```
[generate_embeddings_large_tables, extract_domain_terms_detailed]
  → merge_pipeline_results
  → store_vectors_excel
```

## Proposed Solution

### Architecture Decision

Create a **utility function** that both Excel parsing tasks (`parse_excel_large_tables` and `parse_excel_detailed`) can call directly at the beginning of their execution to remove strikethrough cells in-place before processing.

### Implementation Strategy

**Single Utility Function Approach:**
- Create `remove_strikethrough_cells()` function in a shared utility module
- Both parsing tasks call this function immediately after loading the Excel file
- Function modifies the workbook in-memory before any processing begins
- No separate Airflow task needed

**Advantages:**
- Simple, straightforward implementation
- No additional DAG complexity
- Both pipelines use the same cleanup logic
- In-memory modification (no extra file I/O)
- Easy to test independently

**Disadvantages:**
- Function is called twice (once per pipeline) for the same file
- Cannot easily disable for one pipeline without affecting the other

## Detailed Design

### Component: Strikethrough Utility Function

**Location:** `src/extraction_v2/strikethrough_utils.py` (new file)

```python
def remove_strikethrough_cells(input_path: str, output_path: Optional[str] = None) -> dict:
    """
    Remove strikethrough text from Excel file by directly manipulating XML.

    This function works directly with Excel's OOXML format to avoid openpyxl
    serialization issues that can cause file corruption.

    Args:
        input_path: Path to input Excel file (.xlsx)
        output_path: Optional path for output file. If None, overwrites input.

    Returns:
        Statistics dict with:
        - cells_modified: Number of cells with strikethrough text removed
        - sheets_affected: Number of sheets modified
        - runs_removed: Total number of rich text runs removed

    Example:
        from src.extraction_v2.strikethrough_utils import remove_strikethrough_cells

        result = remove_strikethrough_cells('file.xlsx')  # Modifies in-place
        # Or save to new file:
        result = remove_strikethrough_cells('file.xlsx', 'file_cleaned.xlsx')
    """
```

**Implementation Approach:**

The implementation uses **direct XML manipulation** instead of openpyxl to avoid file corruption issues:

1. **Extract Excel file** (Excel .xlsx files are ZIP archives of XML files)
2. **Process sharedStrings.xml** - Remove strikethrough text runs from rich text cells
3. **Process styles.xml** - Identify font styles with strikethrough formatting
4. **Process worksheet XMLs** - Clear cells that use strikethrough styles
5. **Remove phonetic properties** - Clear Japanese furigana that become invalid after text removal
6. **Repackage as Excel file** - Create valid XLSX without corruption

**Key Features:**
- Handles both **rich text strikethrough** (partial text) and **cell-level strikethrough** (entire cell)
- Preserves all other formatting (colors, bold, italic, etc.)
- No Excel repair errors (avoids openpyxl serialization issues)
- In-place modification or save to new file
- Uses lxml for better XML preservation when available

### Integration: Calling from Parse Tasks

Both Excel parsing tasks call the utility function directly at the beginning of their execution, passing the file path. The function modifies the Excel file in-place before any parsing begins.

#### Integration Point 1: `parse_excel_large_tables`

**Location:** `airflow/dags/tasks/parse_tasks.py`

**Modification in `parse_excel_large_tables` function (line 21-123):**

```python
@task.external_python(python=EXTERNAL_PYTHON)
def parse_excel_large_tables(fetch_result: Dict[str, Any]) -> Dict[str, Any]:
    """Parse Excel document for LARGE TABLES using Docling (NEW pipeline)."""
    from src.extraction_v2.document_converter import DocumentToHtmlConverter
    from src.extraction_v2.html_table_extractor import HtmlTableExtractor
    from src.extraction_v2.large_table_extractor import LargeTableExtractor
    from src.extraction_v2.strikethrough_utils import remove_strikethrough_cells  # NEW
    import logging
    import pickle
    import os

    log = logging.getLogger(__name__)

    # ... existing validation code ...

    local_path = fetch_result['local_path']
    document_id = fetch_result['document_id']

    log.info(f"Parsing Excel document {document_id}, path: {local_path}")

    # NEW: Remove strikethrough text before processing
    # This modifies the file in-place by directly manipulating the Excel XML
    strikethrough_result = remove_strikethrough_cells(local_path)
    if strikethrough_result['cells_modified'] > 0:
        log.info(
            f"Removed strikethrough text: {strikethrough_result['runs_removed']} runs "
            f"from {strikethrough_result['cells_modified']} cells across "
            f"{strikethrough_result['sheets_affected']} sheets"
        )

    # Continue with existing parsing logic
    converter = DocumentToHtmlConverter()
    large_extractor = LargeTableExtractor()
    # ... rest of existing code ...
```

#### Integration Point 2: `parse_excel_detailed`

**Location:** `airflow/dags/tasks/parse_tasks.py`

**Modification in `parse_excel_detailed` function (line 384-467):**

```python
@task.external_python(python=EXTERNAL_PYTHON)
def parse_excel_detailed(fetch_result: Dict[str, Any]) -> Dict[str, Any]:
    """Parse Excel with DETAILED extraction (images, shapes, flowcharts, comments)."""
    import sys
    sys.path.insert(0, '/opt/airflow')

    from src.extraction_v2.excel_parser_service import get_excel_parser_service
    from src.extraction_v2.strikethrough_utils import remove_strikethrough_cells  # NEW
    from uuid import UUID
    import logging
    import pickle
    import asyncio

    log = logging.getLogger(__name__)

    # ... existing validation code ...

    local_path = fetch_result['local_path']
    document_id = fetch_result['document_id']

    log.info(f"Parsing Excel with OLD pipeline: {document_id}, path: {local_path}")

    # NEW: Remove strikethrough text before processing
    # This modifies the file in-place by directly manipulating the Excel XML
    strikethrough_result = remove_strikethrough_cells(local_path)
    if strikethrough_result['cells_modified'] > 0:
        log.info(
            f"Removed strikethrough text: {strikethrough_result['runs_removed']} runs "
            f"from {strikethrough_result['cells_modified']} cells across "
            f"{strikethrough_result['sheets_affected']} sheets"
        )

    # Read file content for parser service
    with open(local_path, 'rb') as f:
        file_content = f.read()

    # Continue with existing parsing logic
    parser_service = get_excel_parser_service()
    # ... rest of existing code ...
```

## Configuration

No additional configuration needed. The function is always called by both parsing tasks.

If you want to disable it temporarily for debugging, you can add a flag in `airflow/dags/config.py`:

```python
class Config:
    # ... existing config ...

    # Strikethrough removal (set to False to disable)
    ENABLE_STRIKETHROUGH_REMOVAL: bool = True
```

Then in both parse tasks:
```python
from config import CONFIG

# Only remove strikethrough if enabled
if CONFIG.ENABLE_STRIKETHROUGH_REMOVAL:
    strikethrough_result = remove_strikethrough_cells(workbook)
```

## Testing Strategy

### Unit Tests

**Location:** `tests/unit/extraction_v2/test_strikethrough_utils.py`

```python
import pytest
from openpyxl import Workbook
from openpyxl.styles import Font
from src.extraction_v2.strikethrough_utils import remove_strikethrough_cells


def test_remove_strikethrough_single_cell():
    """Test removal of single cell with strikethrough."""
    wb = Workbook()
    ws = wb.active

    # Create cells with and without strikethrough
    ws['A1'] = 'Normal text'
    ws['A2'] = 'Strikethrough text'
    ws['A2'].font = Font(strike=True)
    ws['A3'] = 'More normal text'

    result = remove_strikethrough_cells(wb)

    assert result['cells_cleared'] == 1
    assert ws['A1'].value == 'Normal text'
    assert ws['A2'].value is None  # Cleared
    assert ws['A3'].value == 'More normal text'


def test_remove_strikethrough_multiple_sheets():
    """Test removal across multiple sheets."""
    wb = Workbook()
    ws1 = wb.active
    ws1.title = 'Sheet1'
    ws2 = wb.create_sheet('Sheet2')

    # Add strikethrough to both sheets
    ws1['A1'] = 'Strike 1'
    ws1['A1'].font = Font(strike=True)
    ws2['B2'] = 'Strike 2'
    ws2['B2'].font = Font(strike=True)

    result = remove_strikethrough_cells(wb)

    assert result['cells_cleared'] == 2
    assert result['sheets_affected'] == 2
    assert ws1['A1'].value is None
    assert ws2['B2'].value is None


def test_remove_strikethrough_merged_cells():
    """Test that entire merged range is cleared when one cell has strikethrough."""
    wb = Workbook()
    ws = wb.active

    # Merge cells A1:B2 and apply strikethrough
    ws.merge_cells('A1:B2')
    ws['A1'] = 'Merged strikethrough'
    ws['A1'].font = Font(strike=True)

    result = remove_strikethrough_cells(wb)

    # All cells in merged range should be cleared
    assert ws['A1'].value is None
    assert ws['A2'].value is None
    assert ws['B1'].value is None
    assert ws['B2'].value is None


def test_remove_strikethrough_no_strikethrough():
    """Test handling of workbook with no strikethrough."""
    wb = Workbook()
    ws = wb.active
    ws['A1'] = 'Normal text'

    result = remove_strikethrough_cells(wb)

    assert result['cells_cleared'] == 0
    assert result['sheets_affected'] == 0
    assert ws['A1'].value == 'Normal text'


def test_remove_strikethrough_preserves_other_formatting():
    """Ensure non-strikethrough formatting is preserved."""
    wb = Workbook()
    ws = wb.active

    ws['A1'] = 'Bold text'
    ws['A1'].font = Font(bold=True)
    ws['A2'] = 'Strike text'
    ws['A2'].font = Font(strike=True)

    result = remove_strikethrough_cells(wb)

    assert ws['A1'].value == 'Bold text'
    assert ws['A1'].font.bold is True  # Bold preserved
    assert ws['A2'].value is None  # Strikethrough cleared
```

### Integration Tests

**Location:** `tests/integration/test_strikethrough_integration.py`

```python
def test_parse_excel_large_tables_removes_strikethrough():
    """Test that parse_excel_large_tables removes strikethrough cells."""
    # Create test Excel file with strikethrough
    # Call parse_excel_large_tables
    # Verify strikethrough cells are not in the output


def test_parse_excel_detailed_removes_strikethrough():
    """Test that parse_excel_detailed removes strikethrough cells."""
    # Create test Excel file with strikethrough
    # Call parse_excel_detailed
    # Verify strikethrough cells are not in the output


def test_both_pipelines_handle_same_file():
    """Verify both pipelines correctly handle the same file with strikethrough."""
    # Run both pipelines on the same file
    # Verify consistent strikethrough removal
```

### Test Data

Create test Excel files in `tests/fixtures/excel/`:
- `strikethrough_single_cell.xlsx` - One cell with strikethrough
- `strikethrough_multiple_sheets.xlsx` - Multiple sheets with strikethrough
- `strikethrough_merged_cells.xlsx` - Merged cells with strikethrough
- `strikethrough_large_table.xlsx` - Large table (>1000 rows) with strikethrough
- `no_strikethrough.xlsx` - Control file with no strikethrough

## Performance Considerations

### Expected Overhead

- **Small files (<100 sheets, <10K cells):** ~100-200ms overhead
- **Medium files (100-500 sheets, 10K-100K cells):** ~500ms-2s overhead
- **Large files (>500 sheets, >100K cells):** ~2-10s overhead

### Optimization Strategies

1. **Read-only mode for detection:**
   ```python
   workbook = load_excel_file(file_path, read_only=True, data_only=False)
   ```

2. **Skip preprocessing if no strikethrough detected:**
   - First pass: Quick scan for strikethrough (read-only)
   - Second pass: Only create cleaned file if strikethrough found

3. **Parallel sheet processing:**
   - For very large files, process sheets in parallel using multiprocessing

4. **Caching:**
   - Cache preprocessing results in XCom for retry scenarios

## Rollout Plan

### Phase 1: Implementation
- [x] Create `src/extraction_v2/strikethrough_utils.py` with `remove_strikethrough_cells()` function
- [ ] Add unit tests in `tests/unit/extraction_v2/test_strikethrough_utils.py`
- [ ] Create test fixtures with strikethrough cells

### Phase 2: Integration
- [x] Modify `parse_excel_large_tables` to call the utility function
- [x] Modify `parse_excel_detailed` to call the utility function
- [ ] Add integration tests
- [ ] Test both pipelines with sample files

### Phase 3: Testing & Validation
- [ ] Run unit tests (should achieve >95% coverage)
- [ ] Run integration tests with real Excel files
- [ ] Performance benchmarking on large files
- [ ] Validate logging output

### Phase 4: Deployment
- [ ] Deploy to staging environment
- [ ] Monitor logs for strikethrough removal statistics
- [ ] Validate no regression in parsing accuracy
- [ ] Deploy to production

### Phase 5: Monitoring (Ongoing)
- [ ] Track how often strikethrough cells are found
- [ ] Monitor processing time impact
- [ ] Gather user feedback on data quality improvements

## Success Metrics

- **Correctness:** 100% of strikethrough cells detected and removed
- **Performance:** <5% increase in total pipeline execution time
- **Memory:** No memory leaks from temporary file creation
- **Reliability:** No failures due to preprocessing errors (graceful fallback to original file)
- **Data Quality:** Reduction in outdated/invalid data in extracted content (measured by user feedback)

## Rollback Plan

If issues arise:

1. **Quick disable:** Add `ENABLE_STRIKETHROUGH_REMOVAL=False` to config
2. **Code revert:** Comment out the function calls in both parse tasks
3. **Full revert:** Revert the commits that added strikethrough removal

The implementation has minimal changes to existing code, making rollback straightforward without data loss or pipeline disruption.

## Future Enhancements

1. **Configurable styling rules:** Support for other text styles (dim, faint, italic) indicating obsolete data
2. **Row-level filtering:** Option to remove entire rows if any cell has strikethrough
3. **Audit log:** Detailed logging of removed content for compliance
4. **ML-based detection:** Use ML to detect deprecated data beyond just styling cues
5. **User configuration:** Allow per-document or per-user preprocessing preferences

## Dependencies

### Python Packages
- `structlog>=23.0.0` (already installed)
- `lxml` (optional, for better XML preservation - falls back to standard library `xml.etree.ElementTree`)

### External Services
- None (all processing is local)

### Infrastructure
- Temporary directory for XML extraction (uses Python's `tempfile.TemporaryDirectory()`)
- No additional storage requirements

## References

- **Current Pipeline:** airflow/dags/document_extraction_dag.py:159-404
- **Parse Tasks:** airflow/dags/tasks/parse_tasks.py:21-446
- **Large Table Extractor:** src/extraction_v2/large_table_extractor.py:60-563
- **Excel Loader:** src/extraction_v2/excel_loader.py:22-160
- **Openpyxl Font API:** https://openpyxl.readthedocs.io/en/stable/api/openpyxl.styles.fonts.html

## Approval

- [ ] Technical Lead Review
- [ ] Architecture Review
- [ ] Security Review (file handling)
- [ ] Performance Review
- [ ] Product Owner Approval

---

**Document Version:** 1.1
**Last Updated:** 2026-01-12
**Author:** Development Team
**Status:** Implementation Complete - Pending Testing