---
title: 'Add Cell Color Extraction to Excel Document Processing Pipeline'
slug: 'excel-cell-color-extraction'
created: '2026-01-13'
status: 'ready-for-dev'
stepsCompleted: [1, 2, 3, 4]
tech_stack: ['Python 3.11+', 'xml.etree.ElementTree', 'zipfile', 'openpyxl', 'structlog', 'pytest']
files_to_modify: ['src/extraction_v2/document_converter.py', 'src/extraction_v2/pipeline.py', 'src/extraction_v2/record_builder.py']
files_to_create: ['src/extraction_v2/color_extractor.py', 'tests/extraction_v2/test_color_extractor.py', 'tests/extraction_v2/test_pipeline_colors.py']
code_patterns: ['ooxml_zip_manipulation', 'preprocessing_pipeline', 'temp_file_cleanup', 'record_source_metadata']
test_patterns: ['pytest', 'class_based_tests', 'unittest_mock']
---

# Tech-Spec: Add Cell Color Extraction to Excel Document Processing Pipeline

**Created:** 2026-01-13

## Overview

### Problem Statement

Excel documents use cell colors (background and text) to convey semantic meaning (e.g., red background = redundant data, green = validated). Currently, the extraction pipeline discards all color information during conversion to HTML/records. This prevents downstream systems from applying customer-defined color-based filtering rules, resulting in redundant or incorrectly weighted information in RAG retrieval.

### Solution

Extract cell background and text colors directly from Excel OOXML format during preprocessing phase. Store color metadata in `_source["cell_colors"]` for each record, mapped by header keys. Only extract non-default colors (default = white background #FFFFFF + black text #000000). Colors flow through to Milvus metadata, enabling downstream filtering and weighting based on customer color rules.

### Scope

**In Scope:**
- Create `CellColorExtractor` class to extract colors from OOXML (`xl/styles.xml` + `xl/worksheets/sheet*.xml`)
- Extract only background color and text color (no borders)
- Normalize all color formats (theme, indexed, RGB) to hex RGB format (#RRGGBB)
- Filter out cells with default colors (white background + black text)
- Integrate color extraction into `DocumentToHtmlConverter._preprocess_with_border_detection()`
- Store color map as temporary JSON file during pipeline execution
- Modify `RecordBuilder._build_record()` to add `cell_colors` to `_source`
- Handle both multi-column tables (header-key mapping) and single-column raw text (text-key mapping)
- Clean up temporary color map JSON files after pipeline completion
- Color metadata flows automatically to PostgreSQL and Milvus through existing infrastructure

**Out of Scope:**
- Border color extraction (future enhancement if needed)
- Storing default-colored cells (white bg + black text)
- Modifying Milvus schema (colors stored in existing JSON metadata field)
- Implementing downstream color-based filtering logic (separate feature)
- Real-time color-based retrieval weighting (separate feature)
- UI components for displaying colors (separate feature)
- Chinese or Korean text segmentation (unrelated feature)

## Context for Development

### Codebase Patterns

**1. OOXML Direct Manipulation Pattern (Similar to `strikethrough_utils.py`)**

Location: `src/extraction_v2/strikethrough_utils.py`

The codebase already has precedent for direct OOXML XML manipulation. Key pattern from lines 264-307:

```python
# Excel's formatting chain:
# 1. <fonts> define font properties with IDs (0-indexed)
# 2. <cellXfs> define cell styles referencing font IDs via fontId attribute
# 3. Cells reference style IDs via 's' attribute

# Step 1: Find font IDs with specific property
fonts = root.find('.//ss:fonts', NAMESPACES)
for font_id, font in enumerate(fonts.findall('ss:font', NAMESPACES)):
    property_elem = font.find('ss:strike', NAMESPACES)  # Example: strikethrough
    if property_elem is not None:
        matching_font_ids.add(font_id)

# Step 2: Find cell style IDs that use these fonts
cell_xfs = root.find('.//ss:cellXfs', NAMESPACES)
for style_id, xf in enumerate(cell_xfs.findall('ss:xf', NAMESPACES)):
    font_id = xf.get('fontId')
    if font_id is not None and int(font_id) in matching_font_ids:
        matching_style_ids.add(style_id)

# Step 3: Find cells with these style IDs in worksheet
for cell in root.findall('.//ss:c', NAMESPACES):
    style_id = cell.get('s')
    if style_id is not None and int(style_id) in matching_style_ids:
        # Process cell at this location
```

**Color extraction will follow the same pattern:**
- Extract and parse `xl/styles.xml` to map style IDs → font IDs → colors
- Parse worksheet XMLs to map cells (row, col) → style IDs
- Build comprehensive color map without modifying the Excel file
- No dependency on openpyxl for color extraction (direct XML parsing)

**OOXML Namespaces to use:**
```python
NAMESPACES = {
    'ss': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
    'r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'
}
```

**2. Preprocessing Pipeline Integration Pattern**

Location: `src/extraction_v2/document_converter.py:262-330`

The `_preprocess_with_border_detection()` method orchestrates multiple preprocessing steps:
```python
def _preprocess_with_border_detection(self, file_path: str) -> Optional[str]:
    # Step 1: Extract images and apply descriptions
    # Step 2: Extract and apply shape annotations
    # Step 3: Detect flowcharts
    # Step 4: Detect borders and insert separator rows
    # Step 5: Extract and apply comments
```

Color extraction will be added as a **parallel** step (does not modify file):
- Extract colors BEFORE other preprocessing steps
- Save color map to temporary JSON file
- Return color map path from preprocessing method (thread-safe)
- Clean up temp file in pipeline's `finally` block

**3. Temporary File Management Pattern**

The codebase uses temporary files extensively with cleanup in `finally` blocks:

Example from `document_converter.py:244-260`:
```python
try:
    # ... processing ...
    preprocessed_path = self._preprocess_with_border_detection(file_to_convert)
    # ... more processing ...
finally:
    # Clean up temporary files
    for temp_file in [temp_xlsx_path, preprocessed_path]:
        if temp_file and temp_file != file_path and os.path.exists(temp_file):
            try:
                os.remove(temp_file)
            except Exception as e:
                logger.warning(f"Failed to remove temp file {temp_file}: {e}")
```

Color map cleanup will follow this pattern in `pipeline.py`.

**4. Record Structure with `_source` Metadata**

Location: `src/extraction_v2/record_builder.py:119-177`

The `_build_record()` method builds records from row data:
```python
def _build_record(
    self,
    row_data: list[str],  # Cell values for this row
    flat_headers: list[str],  # Header keys
    document_id: UUID,
    sheet_name: str,
    row_number: int,  # 0-indexed row in original table
    table_index: int
) -> dict[str, Any]:
    # Build content by mapping headers to values
    content = {}
    for col_idx, cell_value in enumerate(row_data):
        if col_idx < len(flat_headers):
            header_key = flat_headers[col_idx]
            if cell_value:
                content[header_key] = cell_value

    # Create record
    record = {
        "content": content,
        "_source": {
            "document_id": str(document_id),
            "sheet": sheet_name,
            "row": row_number,
            "table_id": table_index
        }
    }
    return record
```

**Key insight for color mapping:**
- `row_number` is the 0-indexed row in the HTML table
- `col_idx` from `enumerate(row_data)` gives column position
- Color map key: `f"{row_number},{col_idx}"` → colors
- Map colors to same `header_key` as content for easy filtering

The `_source` dict flows through to Milvus metadata automatically. We'll add `cell_colors` here:
```python
"_source": {
    ...existing fields...,
    "cell_colors": {  # NEW - mapped by same keys as content
        "header_key": {
            "bg_color": "#FF0000",
            "font_color": "#FFFFFF"
        }
    }
}
```

**For raw text records** (single-column tables, line 179-250):
- Use "text" as the single key instead of header keys
- Same structure, simpler mapping

**5. Milvus Metadata Preservation Pattern**

Location: `src/knowledge/vector_store.py:527-531`

The `_prepare_entities()` method builds Milvus metadata from `_source`:
```python
metadata = {
    "sheet_name": record._source["sheet"],
    "row_number": record._source["row"],
    "has_symbols": has_symbols,
}
```

**CRITICAL VALIDATION REQUIRED**: The assumption that `cell_colors` automatically flows through is UNVERIFIED. Must validate:

1. **Proof of Concept Required (Task 0 - BLOCKING)**:
   - Manually add `cell_colors` to a test record's `_source`
   - Index in Milvus and retrieve
   - Verify `cell_colors` appears in `metadata` field
   - Check if any size limits or transformations strip nested dicts
   - **BLOCKING**: Implementation cannot proceed without POC success

2. **Potential Issues to Test**:
   - Milvus JSON field size limits (may truncate large color maps)
   - Python dict → JSON → Milvus conversion handling of nested structures
   - Schema version compatibility (verify current Milvus version supports arbitrary JSON)
   - Filters or transformations in `_prepare_entities()` that might strip unknown keys

3. **Verification Points**:
   - Read `src/knowledge/vector_store.py:527-577` completely
   - Confirm `metadata` dict is passed directly to `MilvusEntity`
   - Confirm `MilvusEntity.metadata` is stored as JSON in Milvus
   - Test retrieval to verify `metadata["cell_colors"]` is accessible

4. **Fallback Plan if POC Fails**:
   - Explicitly add `cell_colors` handling in `_prepare_entities()`
   - Flatten nested dict if Milvus doesn't support nested JSON
   - Use alternative storage (separate Milvus field or PostgreSQL JSONB)

### Files to Reference

| File | Purpose |
| ---- | ------- |
| `src/extraction_v2/strikethrough_utils.py` | Reference for OOXML XML parsing pattern (styles.xml, sheet.xml) |
| `src/extraction_v2/shape_extractor.py` | Reference for ZIP-based Excel manipulation and annotation pattern |
| `src/extraction_v2/document_converter.py` | Integration point for color extraction in preprocessing |
| `src/extraction_v2/pipeline.py` | Temp file cleanup location and color map loading |
| `src/extraction_v2/record_builder.py` | Integration point for adding cell_colors to _source |
| `src/extraction_v2/html_table_extractor.py` | Table structure (rows/cols) for color mapping |
| `src/knowledge/vector_store.py` | Reference for metadata flow to Milvus |

### Critical Implementation Details

**Coordinate Mapping Challenge:**

The color map is extracted from OOXML (sheet-level absolute row/col), but records are built from HTML tables (table-relative row/col). This is a known limitation of the post-HTML-conversion approach.

1. **OOXML Color Map:**
   ```python
   {
     "Sheet1": {
       "0,0": colors,  # Cell A1 (absolute sheet coordinates)
       "5,2": colors,  # Cell C6
       ...
     }
   }
   ```

2. **Record Building Context:**
   - `row_number`: Index within `HtmlTable.rows` (table-relative, 0-indexed)
   - `col_idx`: Column index within row (table-relative, 0-indexed)
   - **No direct mapping to sheet coordinates available**

3. **Chosen Solution:**

   **Option A: Explicit Limitation (RECOMMENDED for MVP)**
   - **Acknowledge**: Coordinate mapping is fundamentally limited without table boundary detection
   - **Document explicitly**: Feature works correctly ONLY for files where:
     - Single table per sheet
     - Table starts at row 0 (no header rows above table)
     - No hidden or filtered rows
     - No gaps between Docling-detected table and sheet origin
   - **Implementation**: Attempt mapping with validation
     ```python
     # In record builder
     color_key = f"{row_number},{col_idx}"
     if sheet_name in color_map and color_key in color_map[sheet_name]:
         cell_colors[header_key] = color_map[sheet_name][color_key]
     # If color_key not found, log warning once per document about coordinate mismatch
     ```
   - **Add validation metrics**:
     - Track % of cells where colors are successfully mapped
     - Log warning if <50% mapping success rate (indicates coordinate mismatch)
     - Add to `_source["color_extraction_metadata"]`: `{"cells_mapped": X, "cells_total": Y, "mapping_rate": Z%}`
   - **User-facing limitation**: Document in API/UI that color extraction works best for simple single-table sheets

   **Option B: Add Table Boundary Detection (Future/Enhanced Version)**
   - Modify `HtmlTableExtractor` to capture table starting coordinates during HTML parsing
   - Store in `HtmlTable.sheet_row_offset` and `HtmlTable.sheet_col_offset`
   - Pass offsets to `RecordBuilder`
   - Adjust color lookup: `color_key = f"{row_number + sheet_row_offset},{col_idx + sheet_col_offset}"`
   - **Requires**: Parsing HTML table position metadata from Docling output (if available)
   - **Complexity**: High - depends on Docling providing position info

   **Decision**: Use Option A for MVP. Add AC to validate mapping rate. If real-world testing shows <70% success rate, escalate to Option B.

### Technical Decisions

**1. Direct OOXML XML Parsing (Not openpyxl)**
- **Reason**: Following established pattern in `strikethrough_utils.py`. Avoids openpyxl serialization issues and provides full control over color format handling.
- **Trade-off**: More complex XML parsing, but proven reliable in existing code.

**2. Store Colors in `_source` (Not New Top-Level Field)**
- **Reason**: `_source` automatically flows to Milvus metadata. No changes needed to ExtractedRecord dataclass or conversion code.
- **Alternative Rejected**: Creating `_formatting` field would require changes to `src/extraction/models.py` and `src/workers/extraction_v2_tasks.py:620`.

**3. Only Extract Non-Default Colors**
- **Reason**: Reduces data size and noise. Default colors (white bg + black text) carry no semantic meaning.
- **Implementation**: Comprehensive default detection covering all Excel representations:

  **Default Background Colors** (all treated as "no fill"):
  - Explicit RGB white: `#FFFFFF`
  - Theme color "Background 1" (lt1): resolves to white in most themes
  - Indexed color 0 or 1: white in standard palette
  - No fill element (`<fill>` missing or `<patternFill patternType="none">`)
  - Pattern fill type "none"

  **Default Font Colors** (all treated as "no color"):
  - Explicit RGB black: `#000000`
  - Theme color "Text 1" (dk1): resolves to black in most themes
  - Indexed color 64: automatic/black in standard palette
  - No color element (`<color>` missing from `<font>`)

  **Filter Logic**:
  ```python
  def is_default_color(bg_color: str | None, font_color: str | None) -> bool:
      default_backgrounds = {"#FFFFFF", None}  # None = no fill
      default_fonts = {"#000000", None}  # None = no color
      return bg_color in default_backgrounds and font_color in default_fonts
  ```

  **Test Coverage Required**:
  - Explicit white + black (AC4 existing)
  - Theme "Background 1" + "Text 1" (AC28 new)
  - Indexed 0/1 + indexed 64 (AC29 new)
  - Null/missing elements (AC30 new)

**4. Normalize to Hex RGB (#RRGGBB)**
- **Reason**: Consistent format for downstream filtering. Excel stores colors in multiple formats (theme with tints, indexed palette, direct RGB).
- **Implementation**: Convert all formats to 6-digit hex with # prefix.

**5. Temporary JSON Sidecar (Not Embedded in HTML)**
- **Reason**: Docling's HTML export doesn't preserve color information. Sidecar file keeps color data separate from document flow.
- **Cleanup**: Removed in `pipeline.py` finally block after records are built.

**6. Map Colors by Header Keys (Same as Content)**
- **Reason**: Makes filtering intuitive - `cell_colors["Status"]` corresponds to `content["Status"]`.
- **Implementation**: Use same `flat_headers` list from RecordBuilder to map colors to content keys.

## Implementation Plan

### Tasks

**Phase 0: Validation (BLOCKING - Must Complete First)**

- [ ] Task 0: Milvus metadata flow proof of concept (BLOCKING)
  - File: `scripts/poc_milvus_colors.py` (NEW)
  - Action: Validate cell_colors flows through to Milvus metadata
  - Implementation:
    - Create test record with cell_colors in _source
    - Call `VectorStore._prepare_entities()` to build MilvusEntity
    - Index in Milvus test collection
    - Retrieve and verify metadata["cell_colors"] exists and is correct
    - Test with nested dict structure and various sizes
    - Check for any truncation or transformation
  - **BLOCKING**: If this fails, STOP and update the design before proceeding to Phase 1
  - Notes: Must succeed before any implementation work begins

**Phase 1: Create Color Extraction Module**

- [ ] Task 1: Create `CellColorExtractor` class with OOXML parsing
  - File: `src/extraction_v2/color_extractor.py` (NEW)
  - Action: Implement class with `extract_colors(file_path: str) -> ColorExtractionResult` method
  - Implementation:
    - **File Size Check**: Before extraction, count sheets and estimate cells. Skip if >20 sheets or >50K cells.
    - **Password Protection Check**: Attempt ZIP open; if fails with BadZipFile, return status="failed", error_message="Password-protected or corrupted file"
    - Extract Excel as ZIP, parse `xl/styles.xml` using `xml.etree.ElementTree`
    - **Sheet Name Mapping**: Parse `xl/workbook.xml` to get sheet names and their `r:id` references, then parse `xl/_rels/workbook.xml.rels` to map `r:id` → worksheet file paths
    - Parse `xl/theme/theme1.xml` to extract 12 theme base colors
    - Build font ID → colors map from `<fonts>` section
      - Handle `<color theme="X" tint="Y">` for theme colors
      - Handle `<color rgb="AARRGGBB">` for RGB colors
      - Handle `<color indexed="X">` for indexed colors
    - Build fill ID → colors map from `<fills>` section
      - Extract `<patternFill><fgColor>` for solid fills
      - Handle same color formats as fonts
    - Build style ID → (font ID, fill ID) map from `<cellXfs>` section
    - Parse each worksheet XML to map cells (row, col) → style ID
    - **Merged Cell Expansion**: Parse `<mergeCells>` from each worksheet, expand top-left cell colors to all constituent coordinates
    - Combine maps to produce final color map: `{sheet_name: {"row,col": {bg_color, font_color}}}`
  - Notes: Use NAMESPACES from `strikethrough_utils.py`, add namespace for theme XML. Coordinate keys use string format `"row,col"` consistently.

- [ ] Task 2: Implement color normalization helpers
  - File: `src/extraction_v2/color_extractor.py`
  - Action: Add methods `_rgb_to_hex()`, `_theme_color_to_hex()`, `_indexed_color_to_hex()`, `_rgb_to_hsl()`, `_hsl_to_rgb()`
  - Implementation:
    - **RGB to Hex** (simple):
      ```python
      def _rgb_to_hex(self, rgb: str) -> str:
          # Input: "RRGGBB" or "AARRGGBB" (ARGB format)
          if len(rgb) == 8:  # ARGB: strip alpha
              rgb = rgb[2:]
          return f"#{rgb.upper()}"
      ```

    - **HSL Conversion Functions** (required for tint calculation):
      ```python
      def _rgb_to_hsl(self, r: float, g: float, b: float) -> tuple[float, float, float]:
          """Convert RGB (0-1 range) to HSL. Returns (h, l, s) with h in 0-360, l/s in 0-1."""
          max_c = max(r, g, b)
          min_c = min(r, g, b)
          l = (max_c + min_c) / 2
          if max_c == min_c:
              h = s = 0.0
          else:
              d = max_c - min_c
              s = d / (2 - max_c - min_c) if l > 0.5 else d / (max_c + min_c)
              if max_c == r:
                  h = (g - b) / d + (6 if g < b else 0)
              elif max_c == g:
                  h = (b - r) / d + 2
              else:
                  h = (r - g) / d + 4
              h /= 6
          return (h * 360, l, s)

      def _hsl_to_rgb(self, h: float, l: float, s: float) -> tuple[float, float, float]:
          """Convert HSL to RGB. h in 0-360, l/s in 0-1. Returns (r, g, b) in 0-1 range."""
          h = h / 360
          if s == 0:
              return (l, l, l)
          def hue_to_rgb(p, q, t):
              if t < 0: t += 1
              if t > 1: t -= 1
              if t < 1/6: return p + (q - p) * 6 * t
              if t < 1/2: return q
              if t < 2/3: return p + (q - p) * (2/3 - t) * 6
              return p
          q = l * (1 + s) if l < 0.5 else l + s - l * s
          p = 2 * l - q
          return (hue_to_rgb(p, q, h + 1/3), hue_to_rgb(p, q, h), hue_to_rgb(p, q, h - 1/3))
      ```

    - **Theme Color to Hex** (complex):
      ```python
      def _theme_color_to_hex(self, theme_index: int, tint: float = 0.0) -> str:
          base_rgb = self._get_theme_color_rgb(theme_index)  # from theme1.xml
          if tint == 0:
              return base_rgb
          r, g, b = int(base_rgb[1:3], 16), int(base_rgb[3:5], 16), int(base_rgb[5:7], 16)
          h, l, s = self._rgb_to_hsl(r / 255, g / 255, b / 255)
          if tint > 0:
              l = l * (1 - tint) + tint
          else:
              l = l * (1 + tint)
          r, g, b = self._hsl_to_rgb(h, l, s)
          return f"#{int(r*255):02X}{int(g*255):02X}{int(b*255):02X}"
      ```

    - **Indexed Color to Hex** (complete 64-color palette):
      ```python
      # Standard Excel color palette (0-63) + automatic (64)
      # Note: Colors 0-7 duplicate 8-15 per Excel spec (legacy compatibility)
      INDEXED_COLORS = {
          0: "#000000", 1: "#FFFFFF", 2: "#FF0000", 3: "#00FF00",
          4: "#0000FF", 5: "#FFFF00", 6: "#FF00FF", 7: "#00FFFF",
          8: "#000000", 9: "#FFFFFF", 10: "#FF0000", 11: "#00FF00",
          12: "#0000FF", 13: "#FFFF00", 14: "#FF00FF", 15: "#00FFFF",
          16: "#800000", 17: "#008000", 18: "#000080", 19: "#808000",
          20: "#800080", 21: "#008080", 22: "#C0C0C0", 23: "#808080",
          24: "#9999FF", 25: "#993366", 26: "#FFFFCC", 27: "#CCFFFF",
          28: "#660066", 29: "#FF8080", 30: "#0066CC", 31: "#CCCCFF",
          32: "#000080", 33: "#FF00FF", 34: "#FFFF00", 35: "#00FFFF",
          36: "#800080", 37: "#800000", 38: "#008080", 39: "#0000FF",
          40: "#00CCFF", 41: "#CCFFFF", 42: "#CCFFCC", 43: "#FFFF99",
          44: "#99CCFF", 45: "#FF99CC", 46: "#CC99FF", 47: "#FFCC99",
          48: "#3366FF", 49: "#33CCCC", 50: "#99CC00", 51: "#FFCC00",
          52: "#FF9900", 53: "#FF6600", 54: "#666699", 55: "#969696",
          56: "#003366", 57: "#339966", 58: "#003300", 59: "#333300",
          60: "#993300", 61: "#993366", 62: "#333399", 63: "#333333",
          64: "#000000"  # automatic = black
      }

      def _indexed_color_to_hex(self, index: int) -> str:
          return self.INDEXED_COLORS.get(index, "#000000")
      ```

  - Notes:
    - Theme color resolution requires parsing `xl/theme/theme1.xml`
    - HSL conversion formulas from W3C CSS Color 3 spec
    - Reference: ECMA-376 Part 1 Section 20.1.6.2 (Theme Colors)

- [ ] Task 3: Implement default color filtering
  - File: `src/extraction_v2/color_extractor.py`
  - Action: Filter out cells where bg_color=="#FFFFFF" AND font_color=="#000000"
  - Implementation:
    - Apply filter after building complete color map
    - Only include cells with non-default colors in final map
  - Notes: Reduces JSON size and downstream processing overhead

**Phase 2: Integrate Color Extraction into Pipeline**

- [ ] Task 4: Add color extraction to preprocessing (WITH THREAD SAFETY)
  - File: `src/extraction_v2/document_converter.py`
  - Action: Add color extraction as Step 0 in `_preprocess_with_border_detection()`
  - Implementation:
    - Import `CellColorExtractor`
    - Create temp file with `tempfile.mkstemp(suffix='_colors.json')`
    - Call `color_extractor.extract_colors(file_path)`
    - Write color map to temp JSON file
    - **THREAD SAFETY FIX**: Return color map path instead of storing in instance variable
      ```python
      color_map_path = None  # Local variable, not self._last_color_map_path
      try:
          color_extractor = CellColorExtractor()
          result = color_extractor.extract_colors(file_path)

          temp_fd, color_map_path = tempfile.mkstemp(suffix='_colors.json')
          os.close(temp_fd)
          with open(color_map_path, 'w') as f:
              json.dump(result.color_map, f)

          # Return path as part of method result
          return preprocessed_path, color_map_path
      except Exception as e:
          if color_map_path and os.path.exists(color_map_path):
              os.remove(color_map_path)
          raise
      ```
    - **Alternative**: Use document_id-based temp file naming for uniqueness
  - Notes: Concurrent document processing requires path to be passed explicitly, not stored in instance state

- [ ] Task 5: Pass color map to record builder (THREAD SAFE)
  - File: `src/extraction_v2/pipeline.py`
  - Action: Load color map and pass to `RecordBuilder.build_records()`
  - Implementation:
    - Modify `convert_to_html()` to return tuple: `(html, color_map_path)`
    - After HTML extraction, check if color_map_path is not None
    - Load color map JSON if path exists
    - Pass `color_map` parameter to `builder.build_records()`
    - Add same for `builder.build_combined_raw_text_records()`
    - Store color_map_path in local variable for cleanup
  - Notes: Color map path is passed explicitly through call chain, not instance state

- [ ] Task 6: Add cell_colors to record structure (MEMORY OPTIMIZED)
  - File: `src/extraction_v2/record_builder.py`
  - Action: Modify `_build_record()` to add `cell_colors` dict to `_source`
  - Implementation:
    - Add optional `color_map` parameter to `_build_record()` signature
    - **Memory Optimization**: Build cell_colors dict with ONLY colors for THIS row (not reference to full map)
      ```python
      cell_colors = {}  # Only colors for this specific row
      for col_idx, cell_value in enumerate(row_data):
          if col_idx < len(flat_headers):
              header_key = flat_headers[col_idx]
              color_key = f"{row_number},{col_idx}"

              if sheet_name in color_map and color_key in color_map[sheet_name]:
                  # Copy color dict (not reference) to avoid shared state
                  cell_colors[header_key] = dict(color_map[sheet_name][color_key])

      # Only add if non-empty
      if cell_colors:
          record["_source"]["cell_colors"] = cell_colors
      ```
    - **Key insight**: Each record gets its own small cell_colors dict (typically 1-10 cells)
    - **Memory scaling**: Linear with colored cells, not N × full_map_size
  - Notes: Expected overhead: ~100-500 bytes per record (vs 500KB+ if referencing full map). To be validated during memory profiling (Task 14).

- [ ] Task 7: Handle raw text records with colors
  - File: `src/extraction_v2/record_builder.py`
  - Action: Add color support to `build_raw_text_records()` and `build_combined_raw_text_records()`
  - Implementation:
    - Add optional `color_map` parameter to both methods
    - Pass to internal record building
    - Use "text" as key instead of header keys for color mapping
  - Notes: Simpler than multi-column tables (single key)

**Phase 3: Cleanup and Error Handling**

- [ ] Task 8: Add temp file cleanup (THREAD SAFE)
  - File: `src/extraction_v2/pipeline.py`
  - Action: Clean up color map JSON in `finally` block
  - Implementation:
    - In `process_document()` method, use local variable `color_map_path`
    - In `finally` block:
      ```python
      if color_map_path and os.path.exists(color_map_path):
          try:
              os.remove(color_map_path)
              logger.debug(f"Cleaned up color map: {color_map_path}")
          except Exception as e:
              logger.warning(f"Failed to cleanup color map: {e}")
      ```
    - Local variable ensures each concurrent call cleans up its own temp file
  - Notes: No shared state = no race conditions

- [ ] Task 9: Add error handling and explicit status signaling
  - File: `src/extraction_v2/color_extractor.py`
  - Action: Add comprehensive error handling with status distinction
  - Implementation:
    - Return structured result instead of just dict:
      ```python
      @dataclass
      class ColorExtractionResult:
          color_map: Dict[str, Dict[str, Dict]]  # The actual colors
          status: str  # "success" | "no_colors" | "partial_failure" | "failed"
          cells_processed: int
          cells_with_colors: int
          error_message: Optional[str] = None
          warnings: List[str] = field(default_factory=list)
      ```
    - Status meanings:
      - `success`: Colors extracted, >0 non-default colors found
      - `no_colors`: Extraction succeeded but file has no non-default colors
      - `partial_failure`: Some colors extracted but errors occurred (log warnings)
      - `failed`: Complete failure (bad ZIP, missing critical files)
    - Wrap ZIP extraction in try/except
    - Handle missing styles.xml → status="failed", error_message="Missing styles.xml"
    - Handle parse errors → status="partial_failure", warnings=[...], continue with partial data
    - Log status and cell counts for debugging
    - Add status to color_extraction_metadata in records
  - Notes: Users/logs can now distinguish between "no colors" vs "extraction broke"

**Phase 4: Testing**

- [ ] Task 10: Create unit tests for color extraction
  - File: `tests/extraction_v2/test_color_extractor.py` (NEW)
  - Action: Create test class with fixtures and test methods
  - Implementation:
    - Create test Excel files with various color formats
    - Test RGB colors, theme colors, indexed colors
    - Test default color filtering
    - Test malformed/missing style files
    - Mock file I/O where appropriate
  - Notes: Follow pytest class-based pattern from `test_domain_term_extractor.py`

- [ ] Task 11: Add integration tests
  - File: `tests/extraction_v2/test_pipeline.py` or new file
  - Action: Test end-to-end color flow
  - Implementation:
    - Process sample Excel with colors
    - Verify colors in final records
    - Verify colors in `_source["cell_colors"]`
    - Verify temp file cleanup
  - Notes: May require actual Excel test fixtures

- [ ] Task 12: Baseline performance profiling
  - File: `scripts/profile_extraction.py` (NEW)
  - Action: Profile current pipeline without color extraction
  - Implementation:
    - Use cProfile to measure preprocessing time
    - Test with small (1 sheet, 500 cells), medium (5 sheets, 5K cells), large (10 sheets, 10K cells) files
    - Document baseline numbers in PR description
  - Notes: Establishes performance baseline for comparison

- [ ] Task 13: Color extraction performance profiling
  - File: `scripts/profile_color_extraction.py` (NEW)
  - Action: Profile color extraction components
  - Implementation:
    - Time ZIP extraction, XML parsing, map building, JSON I/O separately
    - Test with same file sizes as baseline
    - Verify <10% overhead vs baseline
    - Add size check: skip if >20 sheets or >50K cells
  - Notes: Validate performance requirements before merge

- [ ] Task 14: Memory usage profiling
  - File: `scripts/profile_memory.py` (NEW)
  - Action: Measure peak memory usage
  - Implementation:
    - Use memory_profiler or tracemalloc
    - Track color map size vs file size
    - Verify no memory leaks from temp files
  - Notes: Ensure memory scales linearly with file size

- [ ] Task 15: Create test fixtures
  - File: `tests/fixtures/excel_colors/` (NEW directory)
  - Action: Create Excel test files for unit and integration tests
  - Implementation:
    - `simple_rgb_colors.xlsx` - Basic RGB formatted cells
    - `theme_colors.xlsx` - Theme colors with tints
    - `indexed_colors.xlsx` - Standard palette colors (8-63)
    - `mixed_colors.xlsx` - Mix of color formats
    - `default_colors.xlsx` - All white bg + black text
    - `multi_sheet_colors.xlsx` - Multiple sheets with colors
    - `merged_cells_colors.xlsx` - Merged cells with colors
    - `password_protected.xlsx` - Encrypted workbook for error testing
  - Notes: Create using Excel or openpyxl script. Include README describing each fixture.

### Acceptance Criteria

**Color Extraction Functionality:**

- [ ] AC1: Given an Excel file with RGB colors, when colors are extracted, then all RGB colors are converted to #RRGGBB hex format

- [ ] AC2: Given an Excel file with theme colors and tints, when colors are extracted, then theme colors are resolved to RGB hex values

- [ ] AC3: Given an Excel file with indexed palette colors, when colors are extracted, then indexed colors are mapped to RGB hex values

- [ ] AC4: Given an Excel file with default colors (white bg + black text), when colors are extracted, then default-colored cells are excluded from the color map

- [ ] AC5: Given an Excel file with mixed default and non-default colors, when colors are extracted, then only non-default colors appear in the final map

**Pipeline Integration:**

- [ ] AC6: Given an Excel file with colored cells, when the preprocessing phase runs, then a color map JSON file is created in the temp directory

- [ ] AC7: Given a color map exists, when building records, then cell_colors are added to _source for cells with non-default colors

- [ ] AC8: Given a multi-column table with colors, when records are built, then cell_colors keys match content keys (by header name)

- [ ] AC9: Given a single-column raw text table with colors, when records are built, then cell_colors use "text" as the key

- [ ] AC10: Given a table row with no colored cells, when the record is built, then cell_colors key is omitted from _source

**Data Flow:**

- [ ] AC11: Given records with cell_colors in _source, when saved to PostgreSQL, then _source is stored in the record

- [ ] AC12: Given records with cell_colors in _source, when indexed in Milvus, then cell_colors appear in metadata JSON field

- [ ] AC13: Given a Milvus search result with colors, when retrieved, then cell_colors are accessible via metadata.cell_colors

**Cleanup and Error Handling:**

- [ ] AC14: Given color extraction completes successfully, when pipeline finishes, then the temporary color map JSON file is deleted

- [ ] AC15: Given color extraction fails, when preprocessing continues, then the pipeline does not crash and processes the document without colors

- [ ] AC16: Given a malformed Excel file with missing styles.xml, when color extraction runs, then it returns status="failed" with error_message="Missing styles.xml" (not just empty map)

- [ ] AC17: Given the pipeline encounters an error after color extraction, when the finally block executes, then the color map temp file is still cleaned up

**Edge Cases:**

- [ ] AC18: Given an Excel file with merged cells, when colors are extracted, then the merged cell's color is mapped to all constituent cell coordinates

- [ ] AC19: Given an Excel file with multiple sheets, when colors are extracted, then each sheet's colors are keyed separately in the map

- [ ] AC20: Given an Excel file with no formatting, when colors are extracted, then an empty color map is returned (not None or error)

**Performance:**

- [ ] AC21: Given a 1-sheet 500-cell Excel file, when processed with color extraction, then color extraction takes <50ms

- [ ] AC22: Given a 10-sheet 10000-cell Excel file, when processed with color extraction, then color extraction takes <500ms

- [ ] AC23: Given any Excel file, when processed with color extraction, then total preprocessing time increases by <10% compared to baseline

- [ ] AC24: Given an Excel file with >20 sheets or >50K cells, when processed, then color extraction is skipped and a warning is logged

- [ ] AC24a: Given an Excel file with >50K cells (regardless of sheet count), when processed, then color extraction is skipped and logs "Skipping color extraction: file exceeds 50K cells"

- [ ] AC24b: Given a password-protected Excel file, when color extraction runs, then it returns status="failed" with error_message containing "password-protected or corrupted"

**Milvus Integration Validation:**

- [ ] AC25: Given a test record with cell_colors in _source, when indexed in Milvus, then cell_colors appears in the metadata JSON field (POC validation)

- [ ] AC26: Given a record with 50 colored cells in cell_colors, when indexed and retrieved from Milvus, then all 50 cell colors are present with correct values (no truncation)

- [ ] AC27: Given a record with nested cell_colors structure, when retrieved from Milvus, then the nested dict structure is preserved (no flattening)

**Comprehensive Default Color Handling:**

- [ ] AC28: Given cells with theme colors "Background 1" (lt1) and "Text 1" (dk1), when colors are extracted, then they are treated as defaults and excluded from the color map

- [ ] AC29: Given cells with indexed colors 0/1 (bg) and 64 (font), when colors are extracted, then they are treated as defaults and excluded

- [ ] AC30: Given cells with missing fill or color elements in XML, when colors are extracted, then they are treated as defaults (None values) and excluded

**XML Parsing Robustness:**

- [ ] AC31: Given an Excel file with cell referencing non-existent style ID, when colors are extracted, then the cell is skipped with a warning logged (no crash)

- [ ] AC32: Given an Excel file with style referencing non-existent font ID, when colors are extracted, then the style uses fallback color (black) and logs warning

- [ ] AC33: Given an Excel file with gradient or pattern fills (not solid), when colors are extracted, then those cells are skipped (only solid fills supported)

- [ ] AC34: Given an Excel file with missing theme1.xml, when theme colors are encountered, then fallback to default theme colors (lt1=white, dk1=black)

**Error State Distinction:**

- [ ] AC35: Given an Excel file with no colored cells, when extraction completes, then status="no_colors" (not "failed")

- [ ] AC36: Given an Excel file with some parse errors but valid colors, when extraction completes, then status="partial_failure" with warnings list populated

- [ ] AC37: Given extraction results, when added to records, then _source["color_extraction_metadata"] contains status, cells_processed, and cells_with_colors counts

**Memory Efficiency:**

- [ ] AC38: Given a document with 10,000 colored cells producing 5,000 records, when processed, then peak memory overhead is <50MB (not 5GB from naive approach)

## Additional Context

### Dependencies

- **Python Standard Library**: `xml.etree.ElementTree`, `zipfile`, `json`, `tempfile`
- **Existing**: `structlog` (logging)
- **No New Dependencies**: All color extraction uses standard library XML parsing
- **openpyxl**: Listed in tech_stack for test fixture creation only (not used in production code)

> [!NOTE]
> **Word Documents Unaffected**: This feature only applies to Excel (.xlsx) files. Word documents pass through the pipeline unchanged - no color extraction is performed on Word files.

### Testing Strategy

**Unit Tests (`tests/extraction_v2/test_color_extractor.py`):**

1. **Color Format Conversion Tests:**
   - `test_rgb_color_extraction` - RGB colors convert to #RRGGBB
   - `test_theme_color_extraction` - Theme colors with tints resolve correctly
   - `test_indexed_color_extraction` - Indexed palette colors map to RGB
   - `test_argb_color_extraction` - ARGB colors (with alpha) convert correctly

2. **Default Color Filtering Tests:**
   - `test_default_colors_excluded` - White bg + black text excluded
   - `test_mixed_default_nondefault` - Only non-default colors in result
   - `test_all_default_colors` - Returns empty map (not None)

3. **Edge Case Tests:**
   - `test_missing_styles_xml` - Returns empty map with warning
   - `test_missing_worksheet` - Handles missing sheet gracefully
   - `test_malformed_xml` - Catches parse errors, doesn't crash
   - `test_empty_workbook` - Returns empty map for blank file

4. **XML Parsing Robustness Tests (NEW - Critical):**
   - `test_missing_font_element` - Font ID referenced but `<font>` missing from styles
   - `test_missing_color_in_font` - `<font>` exists but `<color>` element missing
   - `test_invalid_style_id_reference` - Cell references style ID that doesn't exist in `<cellXfs>`
   - `test_out_of_range_font_id` - `<xf fontId="999">` but only 50 fonts defined
   - `test_circular_style_reference` - Style A references font B which doesn't resolve
   - `test_gaps_in_style_ids` - `<cellXfs>` has IDs 0, 1, 3, 5 (missing 2, 4)
   - `test_fill_pattern_types` - Non-solid fills: gradient, pattern, "none"
   - `test_theme_color_missing_tint` - `<color theme="4">` without tint attribute (should default to 0.0)
   - `test_invalid_rgb_format` - RGB value with wrong length or non-hex characters
   - `test_missing_theme_file` - `xl/theme/theme1.xml` doesn't exist
   - `test_incomplete_theme_colors` - theme1.xml has only 6 of 12 colors defined

5. **Multi-Sheet Tests:**
   - `test_multiple_sheets` - Each sheet keyed separately
   - `test_sheet_name_special_chars` - Handles special chars in names

6. **Password Protection Tests:**
   - `test_password_protected_file` - Returns status="failed" with appropriate error

**Integration Tests (`tests/extraction_v2/test_pipeline_colors.py` - new file):**

1. **End-to-End Flow Tests:**
   - `test_colors_flow_to_records` - Colors appear in final records
   - `test_colors_match_content_keys` - cell_colors keys match content keys
   - `test_raw_text_colors` - Single-column tables use "text" key

2. **Temp File Management Tests:**
   - `test_color_map_temp_file_created` - JSON created during preprocessing
   - `test_color_map_temp_file_deleted` - JSON cleaned up after completion
   - `test_temp_file_cleanup_on_error` - Cleanup even on pipeline failure

3. **Database/Milvus Flow Tests:**
   - `test_colors_in_postgres_record` - _source stored with cell_colors
   - `test_colors_in_milvus_metadata` - Colors accessible in Milvus search results

**Test Fixtures:**

Create Excel test files in `tests/fixtures/excel_colors/`:
- `simple_rgb_colors.xlsx` - Basic RGB formatted cells
- `theme_colors.xlsx` - Theme colors with tints
- `indexed_colors.xlsx` - Standard palette colors
- `mixed_colors.xlsx` - Mix of color formats
- `default_colors.xlsx` - All white bg + black text
- `multi_sheet_colors.xlsx` - Multiple sheets with colors

**Manual Testing:**

1. Process real customer Excel files with colors
2. Query Milvus and verify colors in results
3. Test coordinate alignment on complex multi-table sheets
4. Performance test on large Excel files (1000+ colored cells)

### Notes

**Known Limitations:**

1. **Coordinate Mapping Approximation**
   - Color map uses absolute sheet coordinates, but records use table-relative coordinates
   - Current MVP assumes tables start near sheet top (common case)
   - Multi-table sheets or tables with gaps may have misaligned colors
   - **Mitigation**: Best-effort approach; acceptable for customer filtering use case
   - **Future**: Store table bounding boxes for precise mapping

2. **Merged Cell Handling**

   **Excel's Merged Cell Format Behavior:**
   - Excel stores merged cell formatting on the top-left cell only
   - Merged ranges are defined in `<mergeCells>` section: `<mergeCell ref="A1:C1"/>`
   - Only the top-left cell (A1) has a style reference; B1 and C1 have no style
   - Color extraction will naturally get color from A1 only

   **Implementation Strategy:**
   - **Task 1a**: Parse `<mergeCells>` from each worksheet XML
   - **Task 1b**: Build merged range map: `{"top_row,left_col": ["row,col", ...]}`
   - **Task 1c**: When extracting colors, expand merged cell colors to all constituent coordinates:
     ```python
     # After building initial color map from cells with style IDs:
     for top_left_key, merged_keys in merge_map.items():
         if top_left_key in color_map:
             top_left_color = color_map[top_left_key]
             for key in merged_keys:
                 color_map[key] = top_left_color  # Copy color to all merged cells
     ```
   - **Location**: In `CellColorExtractor.extract_colors()` after initial color map building
   - **Test Coverage**: AC18 (existing) validates this expansion

   **Alternative (If Complexity Too High for MVP):**
   - Skip merged cell expansion
   - Document that only top-left cell of merged ranges has colors
   - Acceptable degradation: Some cells in merged ranges won't have color metadata
   - Users unlikely to notice (merged cells typically span columns, not used for semantic coloring)

3. **Conditional Formatting**
   - Conditional formatting colors are not extracted (applied dynamically by Excel)
   - Only static cell formatting is captured
   - **Rationale**: Out of scope; conditional colors require Excel engine execution

**Performance Considerations:**

**Performance Requirements (MUST BE VALIDATED):**
- Small files (1 sheet, <1000 cells): <50ms color extraction overhead
- Medium files (5 sheets, <5000 cells): <200ms color extraction overhead
- Large files (10+ sheets, >10000 cells): <500ms color extraction overhead
- **Target**: <10% increase in total preprocessing time across all file sizes
- **Hard limit**: Color extraction must not exceed 1 second for any file

**Performance Testing Plan:**
1. **Baseline Profiling** (Task 12):
   - Profile current pipeline without color extraction
   - Measure preprocessing time for small/medium/large test files
   - Document baseline numbers

2. **Color Extraction Profiling** (Task 13):
   - Profile each component:
     - ZIP extraction time
     - XML parsing time (styles.xml, all sheets)
     - Color map building time
     - JSON serialization time
     - Color lookup time in record builder
   - Use `cProfile` or `py-spy` for detailed breakdown
   - Test with real customer files in size categories above

3. **Memory Profiling** (Task 14):
   - Measure peak memory usage during color extraction
   - Track color map size for different file sizes
   - Validate temp file I/O doesn't cause memory spikes

4. **Acceptance Criteria for Performance** (NEW):
   - AC21: Given a 1-sheet 500-cell Excel file, when processed, then color extraction takes <50ms
   - AC22: Given a 10-sheet 10000-cell Excel file, when processed, then color extraction takes <500ms
   - AC23: Given any Excel file, when processed, then total preprocessing time increases by <10%

**Worst-Case Analysis:**
- 20 sheets × 5000 cells/sheet = 100K cells to process
- If 10% colored = 10K color entries in map
- JSON size: ~10K × 100 bytes = 1MB (acceptable)
- XML parsing: 20 sheet files + styles.xml = ~21 parses (potentially slow)
- **Risk**: Large multi-sheet files may exceed performance budget
- **Mitigation**: Add file size check; skip color extraction if >20 sheets or >50K total cells

**Implementation Notes:**

- Color extraction is **read-only** - does not modify the Excel file
- Colors are stored as simple hex strings, easily queryable in Milvus JSON metadata
- Default color filtering reduces storage by ~70% (most cells are unformatted)
- Graceful degradation: Pipeline continues even if color extraction fails

**Memory Usage Analysis:**

**Scenario: 10K colored cells, 5K records**
- Color map JSON size: ~10K cells × 100 bytes = 1MB
- **WRONG approach** (reference full map in each record): 5K × 1MB = 5GB memory
- **CORRECT approach** (copy only row colors):
  - Average 2 colored cells per record
  - 5K records × 2 cells × 100 bytes = 1MB total
  - **~5000x memory savings**

**Implementation ensures**:
- Each record gets independent dict with only its colors
- No references to shared color map state
- Color map loaded once, looked up per-row, then discarded
- Memory scales with colored_cells_count, not records_count × map_size

**Validation** (AC38):
- Process 10K-cell file with 5K records
- Measure peak memory usage
- Verify <50MB overhead (not 5GB)

## Implementation Update: DETAILED Pipeline Integration

**Date:** 2026-01-14
**Status:** Completed

### Problem Discovered During Testing

During end-to-end testing, color data was not appearing in Milvus despite successful local testing. Investigation revealed:

1. **Two Parallel Excel Pipelines**: The Airflow DAG runs TWO pipelines in parallel for Excel files:
   - **NEW Pipeline** (`parse_excel_large_tables`): Uses extraction_v2 (with color extraction implemented)
   - **DETAILED Pipeline** (`parse_excel_detailed`): Uses excel_parser_service for images/shapes/flowcharts

2. **Pipeline Selection Logic**:
   - NEW pipeline processes ONLY large tables (>1000 rows detected via border detection)
   - DETAILED pipeline processes ALL Excel files
   - Both pipelines' results are merged before storing to Milvus

3. **Root Cause**: Color extraction was only implemented in NEW pipeline. For normal-sized tables (most files), the NEW pipeline generates 0 records, and only DETAILED pipeline records reach Milvus WITHOUT colors.

### Solution: Add Color Extraction to DETAILED Pipeline

**Modified File:** `src/extraction_v2/excel_parser_service.py`

**Changes:**

1. **Import CellColorExtractor** (line 46):
   ```python
   from src.extraction_v2.color_extractor import CellColorExtractor
   ```

2. **Initialize color extractor** (line 118):
   ```python
   self.color_extractor = CellColorExtractor()
   ```

3. **Extract colors in parse_excel_file()** (after file conversion, before image extraction):
   ```python
   # STEP 0: Color extraction (non-modifying, creates color map)
   logger.info("Extracting cell colors...")
   try:
       color_result = self.color_extractor.extract_colors(file_to_parse)
       if color_result.status in ["success", "partial_failure"]:
           color_map = color_result.color_map
           logger.info(f"Color extraction success: {color_result.cells_with_colors} colored cells found")
       else:
           color_map = {}
           logger.warning(f"Color extraction failed: {color_result.error_message}")
   except Exception as e:
       color_map = {}
       logger.error(f"Color extraction error: {str(e)}")
   ```

4. **Pass color_map to _convert_docling_chunks_to_dtos()** (line 316):
   ```python
   chunked_dtos = self._convert_docling_chunks_to_dtos(
       chunks, docling_document, file_id, original_filename, sheet_names, color_map
   )
   ```

5. **Attach colors to chunk metadata** (lines 437-444):
   ```python
   # Add cell colors for this sheet if available
   if color_map:
       sheet_name = metadata.get('sheet_name')
       if sheet_name and sheet_name in color_map:
           # Store colors for this sheet
           # Note: Docling chunks are text ranges, not cell-level, so we store
           # the full sheet color map for downstream filtering/analysis
           metadata['cell_colors'] = color_map[sheet_name]
   ```

### Data Flow Through DETAILED Pipeline

1. **excel_parser_service.py**: Extract colors → add to `ChunkedElementDTO.metadata['cell_colors']`
2. **processing_tasks.py**: Extract metadata from chunk → `'metadata': chunk.metadata`
3. **LegacyToMilvusAdapter**: Store in `_legacy_metadata.full_metadata`
4. **vector_store.py**: Copy from `full_metadata` to Milvus metadata (already implemented, lines 548-554)

### Key Differences from NEW Pipeline

| Aspect | NEW Pipeline | DETAILED Pipeline |
|--------|-------------|-------------------|
| **Chunk Granularity** | Cell-level (via RecordBuilder) | Sheet-level text chunks (via Docling) |
| **Color Storage** | Per-cell by header key in `_source["cell_colors"]` | Full sheet color map in `metadata["cell_colors"]` |
| **Use Case** | Large tables with structured headers | Images, shapes, flowcharts, normal-sized tables |
| **Color Mapping** | Precise cell-to-header mapping | Sheet-wide color map for downstream filtering |

### Testing Results

- Test file: `CustomerDocument/IFф╗ХцзШцЫ╕(BB-CASTAR)/х╖еф║Лч╡РцЮЬцГЕха▒.xlsx`
- Color extraction success: 1253 colored cells found
- Logs confirmed color extraction running in DETAILED pipeline:
  ```
  2026-01-14 03:21:39 [info] Extracting cell colors...
  2026-01-14 03:21:39 [info] Color extraction success: 1253 colored cells found
  ```

### File Permissions Fix

**Issue:** Permission denied error when importing `color_extractor.py` in Docker container.

**Root Cause:** File created with 600 permissions (owner read/write only).

**Fix:** Changed permissions to 644 (readable by all):
```bash
chmod 644 src/extraction_v2/color_extractor.py
chmod 644 tests/extraction_v2/test_color_extractor.py
```

### Git Commits

1. **Initial Implementation** (commit 7782b03):
   - Implemented color extraction in NEW pipeline (extraction_v2)
   - Files modified: document_converter.py, pipeline.py, record_builder.py, vector_store.py
   - Files created: color_extractor.py, test_color_extractor.py

2. **DETAILED Pipeline Integration** (commit b0bf988):
   - Added color extraction to excel_parser_service.py
   - Ensures all Excel files get color data, not just large tables

### Enhancement: Cell Values Mapping for Downstream Tasks

**Date:** 2026-01-14
**Status:** Completed

#### Problem: Coordinate-Based Colors Are Hard to Use

The DETAILED pipeline stores colors with coordinate keys like `"3,1"`, `"3,2"` (0-indexed row,col), but text_content is raw markdown:

```json
{
  "text_content": "| 条件番号 | 条件内容 | ...",
  "metadata": {
    "cell_colors": {
      "3,1": {"bg_color": "#FDE9D9"},
      "3,2": {"bg_color": "#FDE9D9"}
    }
  }
}
```

**Downstream challenge**: To map colors to text, systems must:
1. Parse markdown table structure
2. Count rows and columns
3. Map coordinates to cell values
4. Handle merged cells, line breaks, etc.

This is **brittle and error-prone**.

#### Solution: Add `cell_values` Mapping

Add a parallel `cell_values` dict that maps coordinates to actual cell text:

```json
{
  "metadata": {
    "cell_colors": {
      "3,1": {"bg_color": "#FDE9D9"},
      "3,2": {"bg_color": "#FDE9D9"}
    },
    "cell_values": {
      "3,1": "5",
      "3,2": "①電文ID(入力_11)が2920（工事結果情報流通（番ポ工事））の場合..."
    }
  }
}
```

**Downstream benefit**: Simple lookup - "What text has color #FDE9D9?" → Check `cell_values` for matching coordinates.

#### Implementation Details

**File**: `src/extraction_v2/excel_parser_service.py`

**Changes**:

1. **Add helper method to extract cell values** (after color extraction):
   ```python
   def _extract_cell_values_from_sheet(
       self, file_path: str, sheet_name: str
   ) -> Dict[str, str]:
       """Extract cell values from a specific sheet.

       Args:
           file_path: Path to Excel file (.xlsx)
           sheet_name: Name of the sheet

       Returns:
           Dict mapping "row,col" → cell_value (string)
       """
       with zipfile.ZipFile(file_path, 'r') as zip_ref:
           # Load workbook to get sheet index
           wb = load_workbook(file_path, read_only=True, data_only=True)
           if sheet_name not in wb.sheetnames:
               return {}

           sheet = wb[sheet_name]
           cell_values = {}

           for row_idx, row in enumerate(sheet.iter_rows()):
               for col_idx, cell in enumerate(row):
                   if cell.value is not None:
                       key = f"{row_idx},{col_idx}"
                       # Convert to string, handle various types
                       cell_values[key] = str(cell.value)

           wb.close()
           return cell_values
   ```

2. **Attach cell_values to metadata** (in `_convert_docling_chunks_to_dtos()`):
   ```python
   # Add cell colors for this sheet if available
   if color_map:
       sheet_name = metadata.get('sheet_name')
       if sheet_name and sheet_name in color_map:
           # Store colors for this sheet
           metadata['cell_colors'] = color_map[sheet_name]

           # NEW: Add cell values for easy downstream mapping
           cell_values = self._extract_cell_values_from_sheet(
               file_to_parse, sheet_name
           )
           if cell_values:
               metadata['cell_values'] = cell_values
   ```

3. **Optimization**: Cache cell_values per sheet to avoid re-reading:
   ```python
   # At start of parse_excel_file(), initialize cache
   self._cell_values_cache = {}

   # In helper method, use cache:
   cache_key = f"{file_path}:{sheet_name}"
   if cache_key not in self._cell_values_cache:
       self._cell_values_cache[cache_key] = self._extract_cell_values_from_sheet(...)
   return self._cell_values_cache[cache_key]
   ```

#### Data Structure Comparison

| Pipeline | Color Keys | Text Mapping | Use Case |
|----------|-----------|--------------|----------|
| **NEW** | Header names (`"条件番号"`) | Direct semantic mapping | Structured tables, precise cell-level mapping |
| **DETAILED (before)** | Coordinates (`"3,1"`) | None (must parse markdown) | All files, but hard to use |
| **DETAILED (after)** | Coordinates (`"3,1"`) | `cell_values` dict | All files, easy coordinate→text lookup |

#### Downstream Usage Example

**Before** (hard):
```python
# Must parse markdown, count rows/cols
colors = metadata['cell_colors']  # {"3,1": {"bg_color": "#FDE9D9"}}
# How to know what text is at 3,1? Parse markdown table...
```

**After** (easy):
```python
colors = metadata['cell_colors']      # {"3,1": {"bg_color": "#FDE9D9"}}
values = metadata['cell_values']      # {"3,1": "5", "3,2": "①電文ID..."}

# Simple lookup
for coord, color_info in colors.items():
    text = values.get(coord, "")
    print(f"Text '{text}' has color {color_info['bg_color']}")
```

#### Storage Impact

**Memory**: ~50-100 bytes per cell (coordinate key + text value)
- Example: 1000 cells × 75 bytes = 75 KB per chunk
- Negligible compared to text_content (typically 2-4 KB per chunk)

**Milvus**: JSON metadata field already handles nested dicts efficiently.

#### Validation

- Test with sample Excel file that cell_values are correctly extracted
- Verify cell_values flow through to Milvus metadata
- Confirm downstream can map colors to text using cell_values

**Future Enhancements (Out of Scope):**

- Border color extraction (requires parsing border styles in styles.xml)
- Precise coordinate mapping (requires HTML table boundary detection)
- Conditional formatting support (requires Excel engine or approximation)
- Cell patterns and gradients (complex; low customer demand)
- Font bold/italic/underline (separate formatting feature)