<?php

class UtilExcel
{

    public static function getColumnLetter($index) {
        $letter = '';
        while ($index >= 0) {
            $letter = chr($index % 26 + 65) . $letter;
            $index = floor($index / 26) - 1;
        }
        return $letter;
    }

    public static function setCellValueColumn($sheet, $letter, $cell, $title) {
        $sheet->setCellValue($cell, $title);
        $sheet->getStyle($cell)->getFont()->setBold(true); // negrita
        $sheet->getStyle($cell)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle($cell)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle($cell)->getAlignment()->setWrapText(true);
        $sheet->getColumnDimension($letter)->setAutoSize(true);
    }


function setCellValueSmart($sheet, $cell, $value, $type = null) {
    switch ($type) {
        case 'string':
            $sheet->setCellValueExplicit($cell, $value, PHPExcel_Cell_DataType::TYPE_STRING);
            break;

        case 'float':
            $sheet->setCellValue($cell, floatval($value));
            $sheet->getStyle($cell)->getNumberFormat()->setFormatCode('0');
            break;

        case 'float1':
            $sheet->setCellValue($cell, floatval($value));
            $sheet->getStyle($cell)->getNumberFormat()->setFormatCode('0.0');
            break;

        case 'float2':
            $sheet->setCellValue($cell, floatval($value));
            $sheet->getStyle($cell)->getNumberFormat()->setFormatCode('0.00');
            break;

        case 'date':
            $excelDate = PHPExcel_Shared_Date::PHPToExcel(strtotime($value));
            $sheet->setCellValue($cell, $excelDate);

            $sheet->getStyle($cell)->getNumberFormat()->setFormatCode('yyyy-mm-dd');
            break;

        case 'datetime':
            $excelDate = PHPExcel_Shared_Date::PHPToExcel(strtotime($value));
            $sheet->setCellValue($cell, $excelDate);

            $sheet->getStyle($cell)->getNumberFormat()->setFormatCode('yyyy-mm-dd hh:mm:ss');
            break;

        default:
        /*
            // autodetectar
            if (is_numeric($value)) {
                $sheet->setCellValue($cell, floatval($value));
                $sheet->getStyle($cell)->getNumberFormat()->setFormatCode('0.0');
            } else {
                $sheet->setCellValueExplicit($cell, $value, PHPExcel_Cell_DataType::TYPE_STRING);
            }
                */
            break;
    }

    /*
    // Wrap text si contiene salto de línea
    if (is_string($value) && strpos($value, "\n") !== false) {
        $sheet->getStyle($cell)->getAlignment()->setWrapText(true);
        $sheet->getRowDimension($sheet->getCell($cell)->getRow())->setRowHeight(-1);
    }
        */
}



    // aplicamos un autofilter a todas las columnas de la primera fila
    public static function aplicaAutofilter($sheet, $headers){
        // buscamos la letra de la ultima columna
        $lastLetter = self::getColumnLetter(count($headers) - 1);

        // Aplicar autofiltro dinámico
        $sheet->setAutoFilter("A1:{$lastLetter}1");

    }

    public static function createPHPExcel($headers, $fields, $data, $fileName){
        // Crear objeto PHPExcel
        require_once './plugins/PHPExcel-1.8/Classes/PHPExcel.php';
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        $sheet = $objPHPExcel->getActiveSheet();

        foreach ($headers as $index => $title) {
            $letter = self::getColumnLetter($index);
            $cell = $letter . '1';
            self::setCellValueColumn($sheet, $letter, $cell, $title);
        }

        // Ajustar altura de la fila 1 automáticamente
        $sheet->getRowDimension(1)->setRowHeight(-1);

        $rowIndex = 2;
        $colIndex = 0;
        foreach ($data as $row) {
            $colIndex = 0; // Reiniciar colIndex a 0 al inicio de cada fila            
            foreach ($fields as $index => $field) {

                $cell = self::getColumnLetter($colIndex) . $rowIndex;

                $fieldName = $field['name'];
                $fieldType = $field['type'];

//                echo $cell." ".$fieldName." ".$row[$fieldName]." "."<br>";

                self::setCellValueSmart($sheet, $cell, $row[$fieldName], $fieldType);
                $colIndex++;
            }
//                echo "<br><br><br>";
            $rowIndex++;
        }

        self::aplicaAutofilter($sheet, $headers);


        // Configurar cabeceras HTTP para descargar el Excel
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$fileName);
        header('Cache-Control: max-age=0');

        // Crear escritor y enviar al navegador
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;


    }

} 
?>