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