Execute query and assign values to excel columns

0

I have a problem and I do not know how to execute a query and pass the values to the respective columns, this I do to generate an excel. I use the library PHPExcel .

Query:

$query = 'SELECT i.description_item,i.quantity,u.name_unit,i.reorder_Level,i.target_Stock,
l.name_location,i.commentt,io.quantity_s
FROM inventory_list AS i
INNER JOIN unit_mesurement AS u ON id_unit = fkUnit
INNER JOIN locatiON AS l on id_location = fkLocation
INNER JOIN inventory_output as io on id_output = fkInventory
WHERE i.quantity <= reorder_Level AND i.status = 1';

Code excel.php :

<?php
require_once 'excel/Classes/PHPExcel.php';
require_once 'apis/connection.php';

$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties() //propiedades a libro
        ->setCreator("Códigos de Programación") //creador del libro
        ->setLastModifiedBy("Códigos de Programación")//tittulo
        ->setTitle("Excel en PHP")//
        ->setSubject("Documento de prueba")//descripcion
        ->setDescription("Documento generado con PHPExcel")
        ->setKeywords("excel phpexcel php")
        ->setCategory("Ejemplos"); //categoria

        $estilo = array(
          'borders' => array(
            'outline' => array(
              'style' => PHPExcel_Style_Border::BORDER_THIN
            )
          )
        );

        //definer pestaña activa
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle('Inventory to Reorder'); //titulo de la hoja

        //incluir una imagen
        $objDrawing = new PHPExcel_Worksheet_Drawing();
        $objDrawing->setPath('assets/img/Esterline.png'); //ruta
        $objDrawing->setHeight(65); //altura
        $objDrawing->setCoordinates('A1');
        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet()); //incluir la imagen

        // AGREGAR TITULO A CELDAS
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A6:O6');
        $objPHPExcel->getActiveSheet()->setCellValue('A6', 'Inventory To Reorder'); //TITULO

        // Agregar en celda A2 valor numerico
        $objPHPExcel->getActiveSheet()->getStyle('A8:D8')->applyFromArray($estilo);
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A8:D8');
        $objPHPExcel->getActiveSheet()->setCellValue('A8', 'Description'); //COLUMNA

        $objPHPExcel->getActiveSheet()->getStyle('E8:F8')->applyFromArray($estilo);
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('E8:F8');
        $objPHPExcel->getActiveSheet()->setCellValue('E8', 'Quantity');//COLUMNA

        $objPHPExcel->getActiveSheet()->getStyle('G8:H8')->applyFromArray($estilo);
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('G8:H8');
        $objPHPExcel->getActiveSheet()->setCellValue('G8', 'Unit');//COLUMNA


        $objPHPExcel->getActiveSheet()->getStyle('I8:J8')->applyFromArray($estilo);
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('I8:J8');
        $objPHPExcel->getActiveSheet()->setCellValue('I8', 'Target Stock');//COLUMNA


        $objPHPExcel->getActiveSheet()->getStyle('K8:L8')->applyFromArray($estilo);
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('K8:L8');
        $objPHPExcel->getActiveSheet()->setCellValue('K8', 'Area');//COLUMNA

        $objPHPExcel->getActiveSheet()->getStyle('M8:N8')->applyFromArray($estilo);
        $objPHPExcel->setActiveSheetIndex(0)->mergeCells('M8:N8');
        $objPHPExcel->getActiveSheet()->setCellValue('M8', 'Comment');//COLUMNA




        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="Excel.xls"');
        header('Cache-Control: max-age=0');

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
?>

Result:

I need to know how to include the data in their respective columns.

  

Description = i.description_item

     

Quantity = i.quantity

     

Unit = u.name_unit

     

Target Stock = i.target_Stock

     

Area = l.name_location Comment = i.commentt

    
asked by Pato 18.12.2018 в 19:17
source

0 answers