# Excel Document Ingestion Pipeline (Legacy Version)

## Overview

This document describes the legacy Excel document ingestion pipeline architecture. The entry point is `excel_document_ingestion_service.py`, which orchestrates the entire process of parsing Excel files, extracting content (text, images, shapes, comments), and ingesting them into Milvus vector storage.

## Architecture Diagram

```
┌─────────────────────────────────────────────────────────────────┐
│                 Excel Document Ingestion Service                │
│                  (excel_document_ingestion_service.py)          │
└────────────┬────────────────────────────────────────────────────┘
             │
             ├─ Initialization Phase
             │  ├─ AzureChatService (LLM chat completions)
             │  ├─ KeywordExtractionService (KeyBERT)
             │  ├─ EntityExtractionService (NER)
             │  ├─ MarkdownConverterService (format conversion)
             │  ├─ ExcelParserService (core parsing)
             │  ├─ MilvusVectorClient (vector storage)
             │  └─ S3Client (MinIO image storage)
             │
             ├─ Parsing Phase (ExcelParserService)
             │  ├─ File Format Conversion (.xls → .xlsx via LibreOffice)
             │  ├─ Image Extraction & AI Descriptions
             │  ├─ Shape Extraction with Text Content
             │  ├─ Comment Extraction with Metadata
             │  ├─ Cell Annotation (shapes/comments → cells)
             │  └─ LangChain-based Chunking
             │
             ├─ Processing Phase
             │  ├─ Keyword Extraction (per chunk)
             │  ├─ Entity Extraction (per chunk)
             │  ├─ Image Upload to MinIO (base64 → S3)
             │  └─ Metadata Preparation
             │
             └─ Storage Phase
                ├─ Milvus Vector Insertion (with embeddings)
                └─ Return Ingestion Results
```

## Component Breakdown

### 1. Entry Point: ExcelDocumentIngestionService

**File**: `excel_document_ingestion_service.py`

**Key Responsibilities**:
- Orchestrates the entire ingestion workflow
- Manages service dependencies
- Handles keyword and entity extraction
- Processes chunks for Milvus insertion
- Manages MinIO image storage

**Main Method**: `ingest_excel_file()`

```python
async def ingest_excel_file(
    file_content: bytes,
    file_id: UUID,
    original_filename: str,
    replace_images_with_descriptions: bool = False,
    max_characters: Optional[int] = None,
    extract_keywords_and_entities: bool = True,
    unique_filename: Optional[str] = None,
    user_metadata: Optional[Dict[str, Any]] = None,
) -> dict
```

**Flow**:
1. Calls `ExcelParserService.parse_excel_file()` to get chunks
2. Processes chunks via `_process_chunks_for_milvus()`
3. Inserts records into Milvus
4. Returns ingestion results

---

### 2. Core Parser: ExcelParserService

**File**: `excel_parser_service.py`

**Key Responsibilities**:
- Excel file parsing with multiple format support (.xls, .xlsx, .xlsm)
- Image extraction and AI-generated descriptions
- Shape extraction with connector detection
- Comment extraction with metadata preservation
- Cell annotation with shapes/comments
- LangChain-based intelligent chunking

**Dependencies**:
- `ExcelImageExtractionService`: Extracts embedded images
- `ExcelShapeExtractionService`: Extracts shapes (text boxes, connectors)

**Main Parsing Flow** (from `parse_excel_file()`):

```
1. File Format Handling
   ├─ Create temporary file from file_content
   ├─ Detect file extension (.xls, .xlsx, .xlsm)
   └─ Convert .xls → .xlsx (via LibreOffice or xlrd fallback)

2. Image Processing (if enabled)
   ├─ Extract images with metadata (sheet, position, size)
   ├─ Generate AI descriptions via Azure OpenAI
   ├─ Option A: Replace images in Excel with descriptions
   └─ Option B: Create separate image chunks

3. Shape Extraction
   ├─ Extract shapes (text boxes, connectors) from all sheets
   ├─ Detect connector targets (which cells shapes point to)
   ├─ Create shape DTOs with metadata
   └─ Apply shape annotations to cells (before parsing)

4. Comment Extraction
   ├─ Extract comments from all cells
   ├─ Preserve author, cell reference, comment text
   ├─ Generate unique comment IDs
   └─ Apply comment markers to cells (before parsing)

5. Cell Annotation Application
   ├─ For shapes with connectors: Add <annotation> tags to target cells
   ├─ For shapes without connectors: Check bounding box
   │  ├─ If cells have values: Annotate each cell
   │  └─ If cells empty: Merge cells and insert shape text
   └─ For comments: Add <comment id="..."> tags to cells

6. LangChain Parsing
   ├─ Load Excel with openpyxl (preserves annotations)
   ├─ Convert each sheet to Markdown format
   ├─ Split content using RecursiveCharacterTextSplitter
   │  ├─ chunk_size: max_characters (default from settings)
   │  ├─ chunk_overlap: 300
   │  └─ separators: ["\n\n", "\n", " ", ""]
   └─ Create TextElementDTO objects with metadata

7. DTO Conversion
   ├─ Convert TextElementDTO → ChunkedElementDTO (text chunks)
   ├─ Convert image metadata → ChunkedElementDTO (image chunks)
   ├─ Convert shape metadata → ChunkedElementDTO (shape chunks)
   └─ Convert comment metadata → ChunkedElementDTO (comment chunks)

8. Return Combined Chunks
   └─ List[ChunkedElementDTO] with all element types
```

**Key Methods**:

| Method | Purpose | Location |
|--------|---------|----------|
| `parse_excel_file()` | Main parsing orchestrator | excel_parser_service.py:51 |
| `_convert_xls_to_xlsx()` | Format conversion | excel_parser_service.py:345 |
| `_extract_images_with_descriptions()` | Image extraction + AI descriptions | excel_parser_service.py:648 |
| `_apply_shape_annotations_to_cells()` | Inject shape context into cells | excel_parser_service.py:950 |
| `_extract_comments_from_file()` | Extract all comments | excel_parser_service.py:1125 |
| `_apply_comments_to_cells()` | Inject comment markers into cells | excel_parser_service.py:1198 |
| `_parse_with_langchain()` | Core chunking logic | excel_parser_service.py:460 |
| `_sheet_to_markdown()` | Sheet → Markdown conversion | excel_parser_service.py:531 |

---

### 3. Supporting Services

#### 3.1 ExcelImageExtractionService
**File**: `excel_image_extraction_service.py`

**Responsibilities**:
- Extract embedded images from Excel files
- Generate AI descriptions using Azure OpenAI Vision
- Capture image metadata (format, size, position, sheet)
- Encode images as base64 for storage

**Output**: Dictionary with image metadata and base64 data

#### 3.2 ExcelShapeExtractionService
**File**: `excel_shape_extraction_service.py`

**Responsibilities**:
- Extract shapes (text boxes, connectors, callouts) from Excel
- Detect connector targets (which cells shapes point to)
- Extract text content from shapes
- Generate ChunkedElementDTO objects for shapes

**Key Features**:
- Connector detection: Identifies which cell a connector points to
- Bounding box calculation: Determines which cells a shape overlaps

#### 3.3 KeywordExtractionService
**Responsibilities**:
- Extract keywords using KeyBERT model
- Chunk size limit: 480 characters (under 514 limit)
- Chunk overlap: 50 characters

**Used In**: `_extract_keywords_and_entities_from_text()` at excel_document_ingestion_service.py:96

#### 3.4 EntityExtractionService
**Responsibilities**:
- Extract named entities using NER model
- Supports configurable entity labels (default: SOFTWARE_DOMAIN_ENTITY_LABELS)
- Chunk size limit: 350 characters (under 384 limit)
- Chunk overlap: 50 characters

**Used In**: `_extract_keywords_and_entities_from_text()` at excel_document_ingestion_service.py:109

---

### 4. Data Flow

#### Phase 1: File Upload → Parsing
```
User uploads Excel file
  ↓
ExcelDocumentIngestionService.ingest_excel_file()
  ↓
ExcelParserService.parse_excel_file()
  ↓
File conversion (.xls → .xlsx if needed)
  ↓
Image extraction (optional)
  ↓
Shape extraction
  ↓
Comment extraction
  ↓
Cell annotation (shapes + comments)
  ↓
LangChain parsing (Markdown + chunking)
  ↓
Returns: List[ChunkedElementDTO]
```

#### Phase 2: Chunk Processing → Storage
```
List[ChunkedElementDTO]
  ↓
For each chunk:
  ├─ Extract keywords (KeywordService)
  ├─ Extract entities (EntityService)
  ├─ Combine keywords + entities → keywords_text field
  ├─ If Image chunk: Upload base64 to MinIO
  │  ├─ Save to S3 bucket: excel_images/{file_id}/{image_name}
  │  ├─ Get MinIO URL and S3 key
  │  └─ Replace base64 with image_url + image_s3_key
  └─ Prepare Milvus record:
     {
       file_id: str,
       element_type: str,
       file_type: str,
       text: str,
       keywords_text: str,
       metadata: {
         internal_key: {chunk metadata},
         ...user_metadata
       },
       created_at: ISO timestamp
     }
  ↓
Milvus.insert(milvus_records)
  ↓
Returns: {
  file_id,
  original_filename,
  total_chunks,
  total_records_inserted,
  inserted_ids,
  created_at
}
```

---

## Data Structures

### ChunkedElementDTO
```python
@dataclass
class ChunkedElementDTO:
    file_id: UUID
    element_type: str  # "Text", "Image", "Shape", "Comment"
    file_type: str     # "xlsx", "xls", etc.
    text: str          # Main content (or description for images)
    metadata: Dict[str, Any]
```

### Metadata Structure (Text Chunks)
```python
{
    "sheet_name": str,
    "sheet_number": int,
    "source_filename": str,
    "unique_filename": str
}
```

### Metadata Structure (Image Chunks)
```python
{
    "source_filename": str,
    "sheet_name": str,
    "sheet_number": int,
    "image_number": int,
    "position_key": str,
    "image_format": str,
    "width": int,
    "height": int,
    "position": {
        "from_cell": str,
        "to_cell": str
    },
    "cell_reference": str,
    "image_base64": str,  # Only before MinIO upload
    "image_url": str,     # After MinIO upload
    "image_s3_key": str,  # After MinIO upload
    "mime_type": str,
    "unique_filename": str
}
```

### Metadata Structure (Shape Chunks)
```python
{
    "sheet_name": str,
    "sheet_number": int,
    "shape_type": str,
    "position": {
        "from_cell": str,
        "to_cell": str
    },
    "connected_target": {
        "cell_ref": str,
        "cell_value": str
    },  # Only if connector detected
    "source_filename": str,
    "unique_filename": str
}
```

### Metadata Structure (Comment Chunks)
```python
{
    "comment_id": str,
    "sheet_name": str,
    "cell_address": str,
    "row": int,
    "column": str,
    "column_index": int,
    "cell_value": str,
    "author": str,
    "source_filename": str,
    "unique_filename": str
}
```

### Milvus Record Structure
```python
{
    "file_id": str,
    "element_type": str,
    "file_type": str,
    "text": str,
    "keywords_text": str,  # Combined keywords + entities for BM25
    "metadata": {
        "internal_key": {
            # Chunk-specific metadata (see above)
        },
        # User metadata fields at root level for filtering
    },
    "created_at": str  # ISO format
}
```

---

## Key Features

### 1. Multi-Format Support
- **Native**: `.xlsx`, `.xlsm`
- **Converted**: `.xls` (via LibreOffice → .xlsx or xlrd fallback)
- **Preservation**: Images, shapes, comments, formatting preserved during conversion

### 2. Image Handling
- **Extraction**: Embedded images extracted with position metadata
- **AI Descriptions**: Azure OpenAI Vision generates descriptions
- **Storage Options**:
  - Option A: Replace images with descriptions in Excel before parsing
  - Option B: Store images as separate chunks + upload to MinIO
- **MinIO Integration**: Base64 images uploaded to S3-compatible storage

### 3. Shape Processing
- **Supported Types**: Text boxes, connectors, callouts, other shapes
- **Connector Detection**: Identifies which cells connectors point to
- **Annotation Strategy**:
  - With connector: Annotate target cell
  - Without connector: Annotate overlapping cells or merge empty cells

### 4. Comment Extraction
- **Metadata Preserved**: Author, cell reference, comment text
- **ID Tracking**: Unique IDs for comment-cell association
- **Annotation**: Comments embedded in cell content before parsing

### 5. Intelligent Chunking
- **LangChain Integration**: RecursiveCharacterTextSplitter
- **Configurable**: `max_characters` parameter (default from settings)
- **Overlap**: 300 characters for context preservation
- **Hierarchical Separators**: `["\n\n", "\n", " ", ""]`

### 6. Keyword & Entity Extraction
- **Keywords**: KeyBERT with 480-character chunks
- **Entities**: NER with 350-character chunks
- **BM25 Optimization**: Combined into `keywords_text` field
- **Software Domain**: Default entity labels for technical content

### 7. Metadata Management
- **Two-Level Structure**:
  - Internal metadata: Under configurable key (e.g., `_internal`)
  - User metadata: Root level for easy filtering
- **Context Preservation**: Sheet name, page number, file info

---

## Configuration

### Environment Variables (via Settings)
```python
# From get_settings()
excel_max_characters: int          # Default chunk size
excel_large_file_threshold: int    # Cell count for optimization
excel_supported_extensions: list   # ['.xlsx', '.xls', '.xlsm']
metadata_internal_key: str         # Key for internal metadata
minio_presigned_url_expiration: int  # URL expiration (seconds)
```

### Text Splitters
```python
# Entity extraction splitter
RecursiveCharacterTextSplitter(
    chunk_size=350,      # Under 384 NER limit
    chunk_overlap=50,
    separators=["\n\n", "\n", ". ", ", ", " ", ""]
)

# Keyword extraction splitter
RecursiveCharacterTextSplitter(
    chunk_size=480,      # Under 514 KeyBERT limit
    chunk_overlap=50,
    separators=["\n\n", "\n", ". ", ", ", " ", ""]
)

# Main content splitter (in ExcelParserService)
RecursiveCharacterTextSplitter(
    chunk_size=max_characters,  # Configurable
    chunk_overlap=300,
    separators=["\n\n", "\n", " ", ""]
)
```

---

## Performance Optimizations

### 1. File Size Detection
- Checks total cell count before parsing
- Uses `large_file_threshold` to determine strategy
- Logs warnings for large files

### 2. Read-Only Mode
- Opens workbooks with `read_only=True` when possible
- Reduces memory footprint for large files

### 3. Streaming Processing
- Processes sheets sequentially
- Chunks generated incrementally via LangChain

### 4. Conditional Processing
- `extract_keywords_and_entities` can be disabled for performance
- Image processing is optional
- Shape/comment extraction can be skipped

---

## Error Handling

### Graceful Degradation
- Keyword extraction failure: Logs warning, continues
- Entity extraction failure: Logs warning, continues
- Image upload failure: Keeps base64 in metadata, logs error
- LibreOffice unavailable: Falls back to xlrd for .xls conversion

### Cleanup
- Temporary files always deleted in `finally` block
- Modified files (with annotations) cleaned up
- Workbooks properly closed after processing

---

## Factory Function

```python
def get_excel_document_ingestion_service(
    collection_name: Optional[str] = None,
    gpu_processing: bool = False,
) -> ExcelDocumentIngestionService
```

**Responsibilities**:
- Instantiates all required services
- Configures Milvus client with collection name
- Enables GPU processing for entity extraction if requested
- Returns fully configured service instance

---

## Usage Example

```python
from excel_document_ingestion_service import get_excel_document_ingestion_service

# Get service instance
service = get_excel_document_ingestion_service(
    collection_name="my_collection",
    gpu_processing=False
)

# Ingest Excel file
result = await service.ingest_excel_file(
    file_content=excel_bytes,
    file_id=UUID("..."),
    original_filename="report.xlsx",
    replace_images_with_descriptions=False,  # Keep images as separate chunks
    max_characters=2000,
    extract_keywords_and_entities=True,
    unique_filename="report_v2.xlsx",  # Optional
    user_metadata={"department": "finance", "year": 2024}
)

# Result
{
    "file_id": "...",
    "original_filename": "report.xlsx",
    "total_chunks": 150,
    "total_records_inserted": 150,
    "inserted_ids": [...],
    "created_at": "2024-12-30T16:30:00"
}
```

---

## Limitations & Known Issues

### 1. .xls Conversion
- **LibreOffice Required**: For full .xls support with images/shapes
- **Fallback**: xlrd conversion loses images, shapes, formatting
- **Timeout**: LibreOffice conversion has 60-second timeout

### 2. Image Processing
- **Memory**: Large images stored as base64 before MinIO upload
- **Performance**: AI descriptions add significant latency
- **Vision API**: Requires Azure OpenAI Vision endpoint

### 3. Shape Extraction
- **Limited Types**: Only text-based shapes supported
- **Complex Connectors**: Multi-segment connectors may not detect correctly
- **Overlap Detection**: Bounding box calculation may be inaccurate

### 4. Comment Handling
- **Rich Text**: Complex formatted comments may lose formatting
- **Large Comments**: Very long comments not chunked separately

### 5. Chunking
- **Table Splitting**: Tables may be split across chunks
- **Context Loss**: Related cells may end up in different chunks
- **Formula Values**: Only calculated values extracted (formulas lost)

---

## Integration Points

### Input
- **Source**: File upload (bytes)
- **Metadata**: file_id, original_filename, user_metadata

### Output
- **Milvus**: Vector embeddings + metadata
- **MinIO**: Images stored in `excel_images/{file_id}/` folder
- **Return**: Ingestion result dictionary

### External Dependencies
- **Azure OpenAI**: Image descriptions, entity extraction
- **KeyBERT**: Keyword extraction
- **Milvus**: Vector storage
- **MinIO**: Image storage
- **LibreOffice**: .xls conversion (optional)

---

## Migration Notes

When migrating to a new pipeline, consider:

1. **Chunk Structure**: Ensure new pipeline maintains compatible metadata structure
2. **Image Storage**: MinIO integration must be preserved or migrated
3. **Keyword/Entity Extraction**: BM25 indexing depends on `keywords_text` field
4. **Element Types**: Support for Text, Image, Shape, Comment element types
5. **Metadata Nesting**: Internal vs. user metadata separation
6. **Presigned URLs**: Image retrieval depends on S3 key tracking

---

## File References

| File | Purpose | Location |
|------|---------|----------|
| `excel_document_ingestion_service.py` | Main orchestrator | Root directory |
| `excel_parser_service.py` | Core parsing logic | Root directory |
| `excel_image_extraction_service.py` | Image extraction | Root directory |
| `excel_shape_extraction_service.py` | Shape extraction | Root directory |

---

## Related Documentation

- [Architecture Overview](./architecture.md)
- [Data Flow Report](./data_flow_report.md)
- [Integration Guide](./integration-guide-structured-store.md)

---

**Last Updated**: 2024-12-30
**Pipeline Version**: Legacy (Pre-Docling Integration)