Leer fichero de excel

composer require phpoffice/phpspreadsheet 1.29 --ignore-platform-reqs
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Csv;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
require_once 'vendor/autoload.php';
$nomFichero = "pepe.xlsx";
$file_mimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$arr_file = explode('.',$nomFichero);
$extension = end($arr_file);
if ('csv' == $extension) {
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
} else {
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
}
$spreadsheet = $reader->load($nomFichero);
$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
// A partir de aquí ya recorrer el array


Crear Excel con Spreadsheet

composer require phpoffice/phpspreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$sheet->getStyle('A1:D1')->getFill()
->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
->getStartColor()
->setARGB('702082');

$sheet->getStyle('A1:D1')->getFont()->setColor( new \PhpOffice\PhpSpreadsheet\Style\Color( \PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE));
$sheet->getStyle('A1:D1')->getFont()->setSize(14);

$sheet->getColumnDimension('A')->setAutoSize(true);
$sheet->getColumnDimension('B')->setAutoSize(true);
$sheet->getColumnDimension('C')->setAutoSize(true);
$sheet->getColumnDimension('D')->setAutoSize(true);

$sheet->setCellValue('A1', 'xxx');
$sheet->setCellValue('B1', 'xxx');
$sheet->setCellValue('C1', 'xxx');
$sheet->setCellValue('D1', 'xxx');

$contFila = 1;

foreach ($resultExcel as $item) {
$contFila++;
$sheet->setCellValue('A'.$contFila, $item->xxx);
$sheet->setCellValue('B'.$contFila, $item->xxx);
$sheet->setCellValue('C'.$contFila, $item->xxx);
$sheet->setCellValue('D'.$contFila, $item->xxx);
}

$writer = new Xlsx($spreadsheet);
// $writer->save('hello world.xlsx');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="loquesea.xlsx"');
$writer->save('php://output'); // para q se descargue en vez de guardar


Excel: Ejemplos fórmulas

Rellenar con ceros a la izquierda una cantidad:
=TEXTO(A2;"000000000000000000")

Añadir una coma al final
=CONCATENAR(B2;",")
Sumar:
=SUMA(A1:A50) // para restas, multip y disisiones no hay formula ponerlo asi =A1*A2*A3
Promedio:
=PROMEDIO(D2:F2)
Máximo y mínimo
=MAX(D2:F2)
=MIN(D2:F2) Prevenir error:
=SI.ERROR (MAX(A2:A3) / MIN(C3:F9),"Ha habido un error") // por si acaso se produjera una división por cero u otro error Condicional =SI(B2="Madrid","España","Otro país")
=SI(D3>5;"APROBADO";"SUSPENSO")
Más información en: https://www.xataka.com/basics/17-formulas-excel-esenciales-para-empezar-aprender-formulas-excel

Excel: crear un excel directamente en PHP

<?php
header('Pragma: ');
header('Cache-Control: ');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename=recetas.xls');
?>
<table border=1>
...
</table>

PHPExcel: Problema de memoria si tiene muchas filas para leer

Se soluciona poniendo:

ini_set('memory_limit', '4000M');

Excel: Crear SQL en una columna a partir de las otras columnas

="INSERT INTO borrar SET codigo = '"&A2&"', nombre = '"&D2&"', apellido1 = '"&B2&"', apellido2 = '"&C2&"', relacion = 'EMPLEADO', empresa = 1;"


Y tirar para abajo para aplicar a todas las filas (para ello Ctrl+C -> Ctrl+Shift+Fin -> Ctrl+V)

Convertir fichero CSV a Excel

include 'PHPExcel/IOFactory.php';
$objReader = PHPExcel_IOFactory::createReader('CSV');
$objReader->setDelimiter(";");
$objReader->setInputEncoding('UTF-8');
$objPHPExcel = $objReader->load('MyCSVFile.csv');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('MyExcelFile.xls');

PHPExcel: añadir filas nuevas a un fichero XLS ya existente

require_once './librerias/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load("./hist_form.xlsx");
$highestColumm = $objPHPExcel->setActiveSheetIndex(0)->getHighestColumn();
$highestRow = $objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
$highestRow++;
$objPHPExcel->setActiveSheetIndex(0);
$fh_actual = date('d-m-Y H:i:s');
$objPHPExcel->getActiveSheet()->setCellValue('A'.$highestRow, $fh_actual);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$highestRow, $_POST["cif"]);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$highestRow, $_POST["nombre"]);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$highestRow, $_POST["apellidos"]);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$highestRow, $_POST["email"]);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('hist_form.xlsx', __FILE__);

Excel: Hacer automáticas redirecciones de htaccess

En una nueva columna del Excel:
="RedirectMatch 301 "&A1&" "&B1&""

Y luego tirar para abajo para que se aplique la fórmula a todas las demás filas

Leer un fichero de Excel con PHP

require_once './librerias/PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load("./empleados.xlsx");

$objHoja = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);



$i = 0;
foreach($objHoja as $iIndice => $objCelda){
if($iIndice > 1 && !empty($objCelda['A'])){
// print_r($objCelda);
echo $objCelda['A'];
echo "<hr>";
}
}

Crear Excel de forma básica

header('Content-Type: text/html; charset=iso-8859-1');
session_start();
require_once ('librerias/cnn_bd.php');
$db = new db;
$sql = "select * from ...";
$regAux = mysqli_query($db->cnn, $sql);

header('Pragma: ');
header('Cache-Control: ');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename=excel_lsg.xls');
?>
<style type="text/css">
    * {
        font-family: Verdana, Arial, Helvetica, sans-serif;
        font-size: 10px;
    }
    table {border-collapse: collapse;}
    .datos {border: 1px solid #000000}
    .cabecera {
        background-color: #F9D119;
        color: #000000;
        text-align: left;
        font-weight:bold;
    }
</style>

<table cellpadding="3" cellspacing="0" class="datos" border="1">
    <tr>
        <th class="cabecera">xxx</th>
        <th class="cabecera">xxx</th>
        <th class="cabecera">xxx</th>
    </tr>
    <?php
    while ($rowAux = $regAux->fetch_assoc()) {  ?>
        <tr>
            <td class="datos"> <?=$rowPS["..."];?></td>
            <td class="datos"> <?=$rowPS["..."];?> </td>
            <td class="datos"> <?=$rowPS[".."];?> </td>
        </tr>
        <?php
    }
    ?>
</table>

PHPExcel: Añadir imágenes al Excel

$objDrawing = new PHPExcel_Worksheet_Drawing();    //create object for Worksheet drawing
$objDrawing->setName('Customer Signature');        //set name to image
$objDrawing->setDescription('Customer Signature'); //set description to image
$signature = '../../imagenes/proyectos-grandes/'.$db2->Record['img1'];    //Path to signature .jpg file
$objDrawing->setPath($signature);
$objDrawing->setOffsetX(25);                       //setOffsetX works properly
$objDrawing->setOffsetY(10);                       //setOffsetY works properly
$objDrawing->setCoordinates('B'.$i);        //set image to cell
$objDrawing->setWidth(32);                 //set width, height
$objDrawing->setHeight(32);

$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

PHPExcel: Añadir estilos nuevos a las gráficas estadísticas de PHPExcel

https://github.com/PHPOffice/PHPExcel/pull/170/files?diff=split

Documentación:
https://github.com/PHPOffice/PHPExcel/pull/170

Excel: Formula para concatenar varios textos de diferentes columnas

=A1 & " " & B1 & " " & C1

Generar ficheros Excel

https://phpexcel.codeplex.com/

EJ General:

$objPHPExcel = new PHPExcel();
 $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
 $cacheSettings = array(' memoryCacheSize ' => '128M');
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

 $objPHPExcel->setActiveSheetIndex(0);
  $i = 1;
   
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setAutoSize(true);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setAutoSize(true);
...
$objPHPExcel->getActiveSheet()->getStyle("A2")->getFont()->setBold(true);
 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(18);

----------------------
  foreach ($arr as $clave => $valor) {
         $objPHPExcel->getActiveSheet()->setCellValue("A".$i, $clave.":");
         $objPHPExcel->getActiveSheet()->setCellValue("B".$i, $valor);
        $objPHPExcel->setActiveSheetIndex(0);
        cellColor('A'.$i, $arrColores[$contColor]);
        cellColor('B'.$i, $arrColores[$contColor]);
        $objPHPExcel->getActiveSheet()->getStyle('A'.$i)->getFont()->setColor( $phpColor );
        $objPHPExcel->getActiveSheet()->getStyle('B'.$i)->getFont()->setColor( $phpColor );
        $i++;
        $contColor++;
        if ($contColor==5)
            $contColor = 0;
     }
............................................
 $objPHPExcel->getActiveSheet()->setCellValue("A".$i, "Fecha alta");
    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()->setCellValue("B".$i, "Id Comercial");
    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()->setCellValue("C".$i, "Tratamiento");
    $objPHPExcel->setActiveSheetIndex(0);
-----------------------------------------
 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
 if(file_exists("excelPrueba.xlsx")){
        unlink("excelPrueba.xlsx");
 }
 $objWriter->save("excelPrueba.xlsx");
 header("location: excelPrueba.xlsx");