TransWar File Browser

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
require_once("conexao_srvtms.php");
$tsql= "SELECT Convert(varchar(40), C.ds_Pessoa) as Fornecedor
    ,Convert(varchar(40), C1.ds_Pessoa) as Destinatario
    ,D.ds_Cidade as Cid_Origem
    ,D.UFE_SG as UF_Origem
    ,D1.ds_Cidade as Cid_Destino
    ,D1.UFE_SG as UF_Destino
    ,Convert(varchar(10), A.dt_Coleta, 103) as Data_Coleta
    ,Convert(varchar(10), X.dt_Emissao, 103) as Data_NF
    ,X.nr_NotaFiscalInteiro as nrNF
    ,CAST(X.kg_Mercadoria as decimal(16,3)) as Peso
    ,E1.ds_NaturezaMercadoria as Nat_Merc
  FROM dbo.tbdPedidoColeta as A
    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
    left JOIN dtbtransporte.dbo.tbdMovimento AS X1 on X.id_Movimento = X1.id_Movimento
    left JOIN dtbtransporte.dbo.tbdPessoa AS B on X1.id_ClienteFaturamento = B.id_Pessoa
    left JOIN dtbtransporte.dbo.tbdPessoa AS C on X1.id_Remetente = C.id_Pessoa
    left JOIN dtbtransporte.dbo.tbdPessoa AS C1 on X1.id_Destinatario = C1.id_Pessoa
    left JOIN dtbtransporte.dbo.tbdCidade AS D ON  D.id_Cidade = C.id_Cidade
    left JOIN dtbtransporte.dbo.tbdCidade AS D1 ON  D1.id_Cidade = C1.id_Cidade
    left JOIN dtbtransporte.dbo.tbdConfiguracaoCliente AS E on E.id_Pessoa = B.id_Pessoa
    left JOIN dtbtransporte.dbo.tbdNaturezaMercadoria AS E1 on X1.id_NaturezaMercadoria = E1.id_NaturezaMercadoria
  WHERE (Convert(varchar(10), A.dt_Coleta,120) BETWEEN '2021-01-01' and '2021-12-31') AND E.id_GrupoCliente='21'  
  ORDER by X.nr_NotaFiscalInteiro ASC";
$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.');</script>";exit();}
$arqcsv="DSM-DNIT.csv";
$arqxlsx="DSM-DNIT.xlsx";
$file_csv = fopen($arqcsv, 'w+');
$l=1;
while ($row = sqlsrv_fetch_array($consulta, SQLSRV_FETCH_ASSOC)) 
{	$Fornecedor     =trim($row['Fornecedor']);
    $Destinatario   =trim($row['Destinatario']);
    $Cid_Origem     =trim($row['Cid_Origem']);
    $UF_Origem      =trim($row['UF_Origem']);
    $Cid_Destino    =trim($row['Cid_Destino']);
    $UF_Destino     =trim($row['UF_Destino']);
    $Data_Coleta    =$row['Data_Coleta'];
    $Data_NF        =$row['Data_NF'];
    $nrNF           =$row['nrNF'];
	$Peso           =$row['Peso'];
    $Nat_Merc       =trim($row['Nat_Merc']);
    $linha[$l] = array($Fornecedor,$Destinatario,$Cid_Origem,$UF_Origem,$Cid_Destino,$UF_Destino,$Data_Coleta,$Data_NF,$nrNF,$Peso,$Nat_Merc);
    ++$l;
}
$cabecalho="RELATÓRIO DE COLETAS/ENTREGAS DO GRUPO DSM  de  01/01/2021 até  31/12/2021";
fwrite($file_csv,$cabecalho.PHP_EOL);   
fwrite($file_csv,'Fornecedor;Destinatário;Cidade Origem;UF;Cidade Destino;UF;Data Coleta;Data NF;nº NF;Peso;Natureza Mercadoria'.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].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-ENTREGAS');
$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.000';
$spreadsheet->getActiveSheet()->getStyle('A1:K'.$l)->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('A2:K2')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('F4FA58');
$spreadsheet->getActiveSheet()->getStyle('a1:K1')->getFont()->setSize(14);
$spreadsheet->getActiveSheet()->getStyle('a2:K2')->getFont()->setSize(11);
$spreadsheet->getActiveSheet()->getStyle('a1:K2')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('A1:K'.$l)
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a1:K2')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a1:K2')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('A1:K2')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('5882FA');
$spreadsheet->getActiveSheet()->getStyle('D3:D'.$l)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('F3:I'.$l)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('j3:j'.$l)->getNumberFormat()->setFormatCode($format);
for ($c=3; $c <= $l; $c++) 
  { if ($c % 2 == 0) { $bg="d8d8d8";} else {$bg="ffffff";}
    $range='A'.$c.':K'.$c;
    $spreadsheet->getActiveSheet()->getStyle($range)->getFill()
        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
        ->getStartColor()->setARGB($bg);
  }
$spreadsheet->getActiveSheet()->mergeCells('A1:K1');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(50);
$writer->save($arqxlsx);
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
//unlink($arqcsv); 
?>