404

D7net
:/ proc / thread-self / cwd /
Filename : testexls.php
back
<?php
require $_SERVER['DOCUMENT_ROOT']."/rosario/adm/vendor/autoload.php"; 
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
use PHPOffice\phpspreadsheet\Shared\Date;	
include_once("conecta.php");
$sql = "select a.id,
                                       c.nome comp_nome,
                                       a.prod_cod,                                       
                                       e.id ean,
                                       p.nome prod_nome,
                                        case when fm.ol = 'S' then
                                        concat(m.nome,' - OL')
                                       else 
                                       m.nome 
                                       end
                                       marca_nome,
                                       ' ' valor_unitario,
                                       1 fator_embalagem,
                                       0 estoque,
                                       a.data data_cotacao,
                                       date_format(a.data, '%d/%m/%Y') data_cotacao_i,
                                       date_format(a.data_fecha, '%d/%m/%Y') data_fecha_i,
                                       date_format(a.data, '%d_%m_%Y') data_cotacao_m,
                                       date_format(a.data_fecha, '%d_%m_%Y') data_fecha_m,
                                      123 repres_id,
                                      repr.email, 
                                    repr.usuario, 
                                    repr.apelido, 
                                    usu.apelido usu_apelido, 
                                    usu.email usu_email, 
                                    MASK(usu.celular, '(##) #####-####') usu_celular
                             from  ct_cotacao a
                                  inner join ct_repres_forn_marca frm
                                    on frm.marca_id = a.marca_id
                                    and frm.forn_id = a.forn_id
                                    and frm.repres_id = 123
                                  inner join ct_fornecedor_marca fm
                                    on fm.marca_id = a.marca_id
                                    and fm.forn_id = a.forn_id  
                                  inner join ro_comprador c
                                    on c.id = a.comp_id
                                  inner join ro_produto p
                                    on p.id = a.prod_cod
                                  inner join ro_ean e
                                    on e.produto_id = a.prod_cod
                                 inner join ro_marca m
                                    on m.id = a.marca_id
                                inner join adms_usuarios repr
                                on repr.id = 123
                                inner join adms_usuarios usu
                                on usu.id = a.usuario_id
                                 where a.data_fecha = '2023-03-16'
                                     and a.repres_id = 123
                                   order by marca_nome, prod_nome";

$res = mysqli_query($link,$sql);
?>
<?php

$spreadsheet = '';
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$spreadsheet->getActiveSheet()->getStyle('A1:I2')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$sheet->getColumnDimension('A')->setWidth(140,'px');
$sheet->getColumnDimension('B')->setWidth(80,'px');
//$sheet->getColumnDimension('C')->setWidth(70,'px');
$sheet->getColumnDimension('C')->setWidth(70,'px');
$sheet->getColumnDimension('D')->setWidth(100,'px');
$sheet->getColumnDimension('E')->setWidth(140,'px');
$sheet->getColumnDimension('F')->setWidth(400,'px');
$sheet->getColumnDimension('G')->setWidth(100,'px');
$sheet->getColumnDimension('H')->setWidth(120,'px');
$sheet->getColumnDimension('I')->setWidth(70,'px');
//$spreadsheet->getActiveSheet()->mergeCells('A1:J1');

//$sheet->getStyle('A1:J2')->getFont()->setBold(true);

$sheet->setCellValue('A1', 'Data');
$sheet->setCellValue('B1', 'Repres');
//$sheet->setCellValue('C2', '#ID');
$sheet->setCellValue('C1', 'Marca');
$sheet->setCellValue('D1', 'Cod. Interno');
$sheet->setCellValue('E1', 'EAN DO PRODUTO');
$sheet->setCellValue('F1', 'Descrição do Produto');
$sheet->setCellValue('G1', 'Preço Cotado');
$sheet->setCellValue('H1', 'Fator Embalagem');
$sheet->setCellValue('I1', 'Estoque');
$i = 3;

while ($row = $res->fetch_assoc()) {
	
$sheet->setCellValue('A'.$i, $row['data_cotacao_i']);
$sheet->setCellValue('B'.$i, $row['repres_id']);
// $sheet->setCellValue('C'.$i, $id);
$sheet->setCellValue('C'.$i, $row['marca_nome']);
$sheet->setCellValue('D'.$i, $row['prod_cod']);
$sheet->setCellValue('E'.$i, $row['ean']);
$sheet->setCellValue('F'.$i, $row['prod_nome']);
$sheet->setCellValue('G'.$i,'      ');
$sheet->setCellValue('H'.$i, '     ');
$sheet->setCellValue('I'.$i, '      ');

$i = $i + 1;
}

	$arquivo = "MinhaPlanilha.xls"; 
$writer = new Xls($spreadsheet);


$writer->save($arquivo);	
header('Content-Type: application/vnd.ms-excel'); 
	header('Content-Disposition: attachment;filename="'.$arquivo.'"'); 
	header('Cache-Control: max-age=0');
ob_end_clean();
ob_start();
$writer->save('php://output');
die;
	?>