Excel Processing

Overview

Kontrak Backend provides robust Excel file processing capabilities using ExcelJS. The system can import employee data, validate fields, handle multiple contract types, and provide detailed error reporting for invalid data.

Excel Parser Service

The ExcelParserService is the core service for Excel file processing:

src/services/excel-parser.service.ts

export class ExcelParserServices {
  async validateExcel(
    buffer: Buffer,
    fields: Record<string, FieldConfig>,
    options: ImportOptions,
  ): Promise<ValidationResult> {
    // Import data from Excel
    const rowData = await this.importExcelFromBuffer(buffer, fields, options);
    
    // Validate employee data
    const validation = this.validationService.validateEmployeeInbatch(rowData);
    
    if (validation.errors.length > 0) {
      throw new AppError(
        `No se pudo procesar el archivo. Se encontraron ${validation.errors.length} errores.`,
        BAD_REQUEST,
        { validationErrors: cleansErrors }
      );
    }
    
    return {
      employees: validation.validEmployees || [],
      totalRecords: rowData.length
    };
  }
}

Import Process Flow

Import Options

Configure Excel import behavior with ImportOptions:

src/services/excel-parser.service.ts

export interface ImportOptions {
  sheetIndex?: number;        // Sheet index (0, 1, 2...) - default: 0
  sheetName?: string;         // Sheet name (overrides sheetIndex)
  skipEmptyRows?: boolean;    // Skip empty rows - default: true
  skipEmptyCells?: boolean;   // Skip empty cells
  headerRow?: number;         // Header row number - default: 1
}

Basic Usage
Specific Sheet
Multiple Sheets

const options: ImportOptions = {
  headerRow: 1,
  skipEmptyRows: true
};

const result = await excelParser.validateExcel(
  fileBuffer,
  CONTRACT_FIELDS_MAP,
  options
);

const options: ImportOptions = {
  sheetName: 'Empleados 2024',  // Use specific sheet
  headerRow: 1
};

const options: ImportOptions = {
  sheetIndex: 2,  // Use third sheet (0-based index)
  headerRow: 2    // Headers in row 2
};

Excel File Structure

Expected Format

Kontrak Backend expects Excel files with:

Header row containing column names (typically row 1)
Data rows with employee information starting immediately after headers
One employee per row
Contract type column specifying PLANILLA, SUBSIDIO, or PART TIME

Example Excel Layout

Column order doesn’t matter - the system uses flexible header matching with multiple aliases.

Nombre	Apellido Paterno	Apellido Materno	DNI	Email	Cargo	Tipo de Contrato	Salario	Fecha Ingreso	Fecha Fin
Juan	García	López	12345678	[email protected]	Operador	PLANILLA	1500.00	01/01/2024	31/12/2024
Ana	Martínez	Sánchez	87654321	[email protected]	Supervisor	SUBSIDIO	2000.00	01/06/2024	30/09/2024

Header Validation

The system validates Excel headers before processing data:

Header Mapping

src/validators/excel-headers.validator.ts

export function validateAndGetHeaderMapping(
  worksheet: ExcelJS.Worksheet,
  fields: Record<string, FieldConfig>,
  headerRow: number = 1,
): Map<string, string> {
  const result = validateExcelHeaders(worksheet, fields, headerRow);
  
  if (!result.isValid) {
    const headerErrors = result.missingHeaders.map((headerName) => ({
      row: headerRow,
      field: headerName,
      message: 'Encabezado requerido no encontrado en el archivo.'
    }));
    
    throw new AppError(
      'La estructura del archivo es incorrecta. Faltan columnas obligatorias.',
      BAD_REQUEST,
      { validationErrors: headerErrors }
    );
  }
  
  return result.headerMapping;
}

Header Normalization

Headers are normalized for flexible matching:

src/validators/excel-headers.validator.ts

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

Examples:

"Apellido Paterno" → "apellidopaterno"
"APELLIDO PATERNO" → "apellidopaterno"
"apellido_paterno" → "apellidopaterno"

Field Aliases

Each field accepts multiple header names:

src/constants/contract-field.ts

contractType: {
  aliases: [
    'tipo de contrato',
    'Tipo Contrato',
    'tipodecontrato',
    'tipo_de_contrato',
    'tipo contrato',
    'TIPO DE CONTRATO'
  ],
  description: 'Tipo de contrato del empleado'
}

The system automatically matches Excel column headers to internal field names using normalized comparison.

Data Extraction

Cell Value Extraction

The parser handles various Excel cell types:

src/services/excel-parser.service.ts

private extractCellValue(
  cell: ExcelJS.Cell,
): string | number | boolean | Date | null {
  const value = cell.value;
  
  if (value === null || value === undefined) return null;
  
  // Preserve primitive types
  if (typeof value === 'number' || 
      typeof value === 'boolean' || 
      value instanceof Date) {
    return value;
  }
  
  // Handle strings
  if (typeof value === 'string') {
    const trimmed = value.trim();
    return trimmed === '' ? null : trimmed;
  }
  
  // Handle rich text
  if (typeof value === 'object' && 'text' in value) {
    const text = String(value.text).trim();
    return text === '' ? null : text;
  }
  
  // Handle formulas
  if (typeof value === 'object' && 'result' in value) {
    return value.result;
  }
  
  return String(value).trim() || null;
}

Special Field Processing

Field-Specific Transformations

Validation Process

After extraction, employee data goes through comprehensive validation:

Batch Validation

src/services/validation.service.ts

validateEmployeeInbatch(rows: Record<string, unknown>[]): {
  errors: ValidationError[];
  validEmployees?: EmployeeData[];
} {
  const errors: ValidationError[] = [];
  const validEmployees: EmployeeData[] = [];
  const dniSet = new Set<string>();
  
  rows.forEach((rowData, index) => {
    const rowNumber = index + 2;  // Account for header row
    const validation = this.validateEmployee(rowData, rowNumber, dniSet);
    
    if (validation.errors.length > 0) {
      errors.push(...validation.errors);
    } else if (validation.employee) {
      validEmployees.push(validation.employee);
    }
  });
  
  return { errors, validEmployees };
}

Validation Checks

Contract Type Validation

Verify the contract type is valid (PLANILLA, SUBSIDIO, or PART TIME):

const rawContractType = String(rowData.contractType || '')
  .trim()
  .toUpperCase();

if (!CONTRACT_TYPES.includes(rawContractType as ContractType)) {
  return { errors: [] };  // Skip invalid types
}

DNI Uniqueness

Check for duplicate DNIs within the same file:

if (dniSet.has(dni)) {
  errors.push({
    error: new AppError(
      `El DNI ${dni} ya existe en el archivo (duplicado)`,
      BAD_REQUEST
    ),
    row: rowNumber,
    field: 'DNI',
  });
}

Schema Validation

Validate against Zod schema for data types and formats:

const result = employeeSchema.safeParse(rowData);

if (!result.success) {
  const validationErrors = result.error.issues.map((issue) => ({
    row: rowNumber,
    field: issue.path.join('.'),
    error: new AppError(issue.message, BAD_REQUEST),
  }));
  return { errors: validationErrors };
}

Contract-Specific Validation

Verify required fields for each contract type are present (e.g., SUBSIDIO contracts must have replacementFor and reasonForSubstitution).

Error Handling

Error Types

{
  row: 1,
  field: 'apellido paterno',
  message: 'Encabezado requerido no encontrado en el archivo.'
}

Error Response Structure

throw new AppError(
  'No se pudo procesar el archivo. Se encontraron N errores.',
  BAD_REQUEST,
  {
    validationErrors: [
      { row: 2, field: 'dni', message: 'DNI es requerido' },
      { row: 3, field: 'email', message: 'Email inválido' },
      // ... more errors
    ]
  }
);

When validation errors occur, the entire batch is rejected. Fix all errors and re-upload the corrected Excel file.

Common Excel Issues

Troubleshooting Guide

Field Configuration

Fields are configured with aliases and descriptions:

src/constants/contract-field.ts

export interface FieldConfig {
  aliases: string[];     // Accepted column names
  description: string;   // Field purpose
}

export const BASE_FIELDS: Record<string, FieldConfig> = {
  name: {
    aliases: [
      'nombre',
      'name',
      'NOMBRE COMPLETO',
      'Nombre completo'
    ],
    description: 'Nombre del empleado'
  },
  // ... more fields
};

Field Maps

Contract Fields
Addendum Fields

export const CONTRACT_FIELDS_MAP: Record<string, FieldConfig> = {
  ...BASE_FIELDS,
  ...COMMON_CONTRACT_FIELDS,
  ...SUBSIDIO_SPECIFIC_FIELDS,
  ...LOCATION_FIELDS,
};

Used for importing employee contracts from Excel.

export const ADDENDUM_FIELDS_MAP: Record<string, FieldConfig> = {
  ...ADDENDUM_FIELDS,
  ...LOCATION_FIELDS,
};

Used for importing contract addendums (amendments).

Performance Considerations

The Excel parser uses streaming for efficient memory usage, even with large files.

Batch processing: All rows validated in a single pass
Early termination: Stops on critical errors (corrupt file, missing headers)
Memory efficient: Processes rows incrementally
Large file support: Can handle thousands of employee records

Example Usage

import { ExcelParserServices } from './services/excel-parser.service';
import { CONTRACT_FIELDS_MAP } from './constants/contract-field';

const excelParser = new ExcelParserServices();

try {
  const result = await excelParser.validateExcel(
    fileBuffer,
    CONTRACT_FIELDS_MAP,
    {
      headerRow: 1,
      skipEmptyRows: true
    }
  );
  
  console.log(`✅ Successfully imported ${result.totalRecords} employees`);
  console.log(`Valid employees: ${result.employees.length}`);
  
  // Process validated employees
  for (const employee of result.employees) {
    await generateContract(employee);
  }
} catch (error) {
  if (error instanceof AppError) {
    console.error('Validation errors:', error.context?.validationErrors);
  }
}

Employee Data Structure

Complete field reference and validation rules

Contract Types

Required fields for each contract type

API: Upload Excel

Upload and validate Excel files via API

Configuration Guide

Configure field aliases and validation

Get Started

Core Concepts

Guides

Overview

Excel Parser Service

Import Process Flow

Import Options

Excel File Structure

Expected Format

Example Excel Layout

Header Validation

Header Mapping

Header Normalization

Field Aliases

Data Extraction

Cell Value Extraction

Special Field Processing

Validation Process

Batch Validation

Validation Checks

Error Handling

Error Types

Error Response Structure

Common Excel Issues

Field Configuration

Field Maps

Performance Considerations

Example Usage

Employee Data Structure

Contract Types

API: Upload Excel

Configuration Guide

Build docs developers (and LLMs) love

Get Started

Core Concepts

Guides

​Overview

​Excel Parser Service

​Import Process Flow

​Import Options

​Excel File Structure

​Expected Format

​Example Excel Layout

​Header Validation

​Header Mapping

​Header Normalization

​Field Aliases

​Data Extraction

​Cell Value Extraction

​Special Field Processing

​Validation Process

​Batch Validation

​Validation Checks

​Error Handling

​Error Types

​Error Response Structure

​Common Excel Issues

​Field Configuration

​Field Maps

​Performance Considerations

​Example Usage

​Related Topics

Employee Data Structure

Contract Types

API: Upload Excel

Configuration Guide

Build docs developers (and LLMs) love

Overview

Excel Parser Service

Import Process Flow

Import Options

Excel File Structure

Expected Format

Example Excel Layout

Header Validation

Header Mapping

Header Normalization

Field Aliases

Data Extraction

Cell Value Extraction

Special Field Processing

Validation Process

Batch Validation

Validation Checks

Error Handling

Error Types

Error Response Structure

Common Excel Issues

Field Configuration

Field Maps

Performance Considerations

Example Usage

Related Topics