Path: C:\
<?php
date_default_timezone_set('America/Sao_Paulo');
$hoje = date("Ymd"); $dateadded = date("d/m/Y");
$DT=date('d-m-Y_G\hi\m');
ini_set('max_execution_time', 300); //300 seconds = 5 minutes
$grupo='17';$ds_grupo='CRODA';
require_once("conexao_srvtms.php");
if (isset($_POST['di'])) { $inicial=$_POST['di'];} else { $inicial=$dateadded;}
if (isset($_POST['df'])) { $final=$_POST['df'];} else { $final=$dateadded;}
$ano1=substr($inicial, 6);
$mes1=substr($inicial, 3,-5);
$dia1=substr($inicial, 0,-8);
$data1=$ano1."-".$mes1."-".$dia1;
$ano2=substr($final, 6);
$mes2=substr($final, 3,-5);
$dia2=substr($final, 0,-8);
$data2=$ano2."-".$mes2."-".$dia2;
$tsql= "SELECT A.id_PedidoColeta as Coleta_TW
,Convert(varchar(30), B.ds_Pessoa) as Fornecedor
,Convert(varchar(30), C.ds_Pessoa) as Destinatario
,Convert(varchar(27), D.ds_Cidade) as Local
,A.qt_Volume as Volume
,A.kg_Mercadoria as Peso
,Convert(varchar(10), A.dt_Coleta, 103) as Data_Realizada
,Convert(varchar(27), F.ds_Pessoa) as Motorista
,X.nr_NotaFiscalInteiro as nrNF
,X.vl_NotaFiscal as valor
,X.qt_Volume as VolumeNF
,X.kg_Mercadoria as PesoNF
,Convert(varchar(10), X.dt_Emissao, 103) as EmissaoNF
,p.cd_Placa as placa
FROM dbo.tbdPedidoColeta as A
left JOIN dtbtransporte.dbo.tbdPessoa AS B on A.id_Pessoa = B.id_Pessoa
left JOIN dtbtransporte.dbo.tbdPessoa AS C on A.id_Remetente = C.id_Pessoa
left JOIN dtbtransporte.dbo.tbdCidade AS D ON D.id_Cidade = A.id_CidadeColeta
left JOIN dtbtransporte.dbo.tbdManifesto AS G on A.id_Manifesto = G.id_Manifesto
left JOIN dtbtransporte.dbo.tbdVeiculo AS p on G.id_Veiculo = p.id_Veiculo
left JOIN dtbtransporte.dbo.tbdPessoa AS F on G.id_Motorista = F.id_Pessoa
left JOIN dtbtransporte.dbo.tbdConfiguracaoCliente AS E on E.id_Pessoa = C.id_Pessoa
left JOIN dtbtransporte.dbo.tbdItemPedidoColeta AS H on A.id_PedidoColeta = H.id_PedidoColeta
left JOIN dtbtransporte.dbo.tbdMovimentoNotaFiscal AS X on H.id_Movimento = X.id_Movimento
WHERE (Convert(varchar(10), A.dt_Coleta,120) BETWEEN '$data1' and '$data2') AND E.id_GrupoCliente='$grupo' ORDER by A.id_PedidoColeta ASC";
$cabec="DO GRUPO ".$ds_grupo;
$consulta = sqlsrv_query($conn, $tsql, array(), array( "Scrollable" => SQLSRV_CURSOR_KEYSET ));
if (!$consulta){echo "ATENÇÃO!! FALHA NA CONSULTA!";}
$numReg1 = sqlsrv_num_rows($consulta);
if($numReg1 ==0 ) {echo "<script language='JavaScript'>alert('Sem resultados carregados. Faça uma busca ampliando o filtro.');location.href='coletacroda.php';</script>";exit();}
$arqcsv="coleta-CRODA.csv";
$arqxlsx="coleta-CRODA.xlsx";
$file_csv = fopen($arqcsv, 'w+');
$l=1;
while ($row = sqlsrv_fetch_array($consulta, SQLSRV_FETCH_ASSOC))
{ $Coleta_TW=$row['Coleta_TW'];
$nrNF=$row['nrNF'];
$valor=$row['valor'];
$Fornecedor=trim($row['Fornecedor']);
$Destinatario=trim($row['Destinatario']);
$Local=trim($row['Local']);
$Volume=$row['VolumeNF'];
$Peso=$row['PesoNF'];
$placa=$row['placa'];
$Motorista=trim($row['Motorista']);
$Data_Realizada=$row['Data_Realizada'];
$EmissaoNF=$row['EmissaoNF'];
if ($Data_Realizada=="" or $nrNF==""){$pular="S";}else
{$linha[$l] = array($Coleta_TW,$Data_Realizada,$Fornecedor,$Destinatario,$Local,$Motorista,$placa,$nrNF,$EmissaoNF,$valor,$Volume,$Peso);++$l;}
}
$cabecalho="ACOMPANHAMENTO DE COLETAS ".$cabec." de ".$inicial." até ".$final;
fwrite($file_csv,$cabecalho.PHP_EOL);
fwrite($file_csv,'Coleta TW;Data;Fornecedor;Destinatário;Local;Motorista;Placa;Nota Fiscal;Emissão;Valor;Volume;Peso'.PHP_EOL);
for ( $i = 1; $i < $l; ++$i )
{$linha2 = $linha[$i][0]. ";" .$linha[$i][1] . ";" . $linha[$i][2].";".$linha[$i][3].";".$linha[$i][4].";".$linha[$i][5].";".$linha[$i][6].";".$linha[$i][7].";".$linha[$i][8].";".$linha[$i][9].";".$linha[$i][10].";".$linha[$i][11]. PHP_EOL;
fwrite($file_csv, $linha2);
}
fclose($file_csv);
$l=$l+2;
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
/* Set CSV parsing options */
$reader->setDelimiter(';');
$reader->setEnclosure('"');
$locale = 'pt_br';
$validLocale = \PhpOffice\PhpSpreadsheet\Settings::setLocale($locale);
if (!$validLocale) {
echo 'Unable to set locale to ' . $locale . " - reverting to en_us" . PHP_EOL;
}
$reader->setSheetIndex(0);
/* Load a CSV file and save as a XLS */
$spreadsheet = $reader->load($arqcsv);
$writer = new Xlsx($spreadsheet);
$spreadsheet->getActiveSheet()->setTitle('COLETAS');
$spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
$spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
$styleArray = [
'borders' => [
'inside' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
'color' => ['argb' => '000000'],
],
],
];
$format = '* #,##0.00';
$format1 = '* #,###';
$spreadsheet->getActiveSheet()->getStyle('A1:L'.$l)->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('A2:L2')->getFill()
->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
->getStartColor()->setARGB('F4FA58');
$spreadsheet->getActiveSheet()->getStyle('a1:L1')->getFont()->setSize(14);
$spreadsheet->getActiveSheet()->getStyle('a2:L2')->getFont()->setSize(11);
$spreadsheet->getActiveSheet()->getStyle('a1:L2')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('A1:L'.$l)
->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a1:L2')
->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a1:L2')
->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('A1:L2')->getFill()
->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
->getStartColor()->setARGB('5882FA');
$spreadsheet->getActiveSheet()->getStyle('g3:i'.$l)
->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a3:b'.$l)
->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('i3:j'.$l)
->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('j3:j'.$l)->getNumberFormat()->setFormatCode($format);
$spreadsheet->getActiveSheet()->getStyle('l3:l'.$l)->getNumberFormat()->setFormatCode($format);
for ($c=3; $c <= $l; $c++)
{ if ($c % 2 == 0) { $bg="d8d8d8";} else {$bg="ffffff";}
$range='A'.$c.':L'.$c;
$spreadsheet->getActiveSheet()->getStyle($range)->getFill()
->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
->getStartColor()->setARGB($bg);
}
$spreadsheet->getActiveSheet()->mergeCells('A1:L1');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(35);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(35);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(35);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(10);
$writer->save($arqxlsx);
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
unlink($arqcsv);
echo "<script language='JavaScript'>alert('Planilha EXCEL gerada com Sucesso! \\nTecle OK para BAIXAR.');location.href='coletacroda.php?arquivo=".$arqxlsx."';</script>";
?>