TransWar File Browser

Path: C:\

<?php
date_default_timezone_set('America/Sao_Paulo');
$dateadded = date("d-m-Y"); 
ini_set('max_execution_time', 600); //300 seconds = 5 minutes
ini_set ( 'memory_limit' , '4095M' ) ;
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;
$arqxlsx="Excel/Pre-fatura ".$final.".xlsx";
$fptemp = fopen('relatorio_temp.csv', 'w+');
fwrite($fptemp,'Transportadora,CTe,Chave de Acesso CTe,Pagador do Frete,Data de Emissão,Vlr,Origem,Destinatário,NFs,Obs,Tipo Movimento'.PHP_EOL);
require_once("conexao_srvtms.php");
$sql = sqlsrv_query( $conn, "SELECT 
     f.ds_Pessoa                                          as transportadora
    ,rtrim(d.nr_Conhecimento)+'-'+rtrim(d.cd_SerieCTRC)   as CTe
    ,rtrim(e.ds_ChaveCTe)                                 as chave
    ,c.ds_Pessoa                                          as pagador
    ,Convert(varchar(10), d.dt_ImpressaoConhecimento, 103)as emissao
    ,a.[vl_Faturado]                                      as vlr
    ,rtrim(g.ds_Pessoa)                                   as origem
    ,rtrim(h.ds_Pessoa)                                   as destinatario
    ,rtrim(d.nr_NotaFiscal)                               as NFs
    ,rtrim(d.cm_Movimento)                                as obs
    ,rtrim(i.ds_TipoMovimento)                            as tipo
  FROM tbdFechamentoCliente           as a
    left JOIN tbdConfiguracaoCliente  as b on b.id_Pessoa         = a.id_Faturado 
    left JOIN tbdPessoa               as c on c.id_Pessoa         = a.id_Faturado
    LEFT JOIN tbdMovimento            as d on d.id_Movimento      = a.id_Movimento
    LEFT JOIN tbdLoteCTeMovimento     as e on a.id_Movimento      = e.id_Movimento
    LEFT JOIN tbdPessoa               as f on d.id_Transportadora = f.id_Pessoa
    LEFT JOIN tbdPessoa               as g on d.id_Remetente      = g.id_Pessoa
    LEFT JOIN tbdPessoa               as h on d.id_Destinatario   = h.id_Pessoa
    LEFT JOIN tbdTipoMovimento        as i on a.id_TipoMovimento  = i.id_TipoMovimento
  where b.id_GrupoCliente='4' and a.dt_Fechamento>='2010-01-01' and a.tp_Finalizado='N'
  order by Convert( int,d.nr_Conhecimento)", array(), array( "Scrollable" => SQLSRV_CURSOR_FORWARD ))or die('Ocorreu o seguinte erro: ' . sqlsrv_errors());
if (!$sql){echo "ATENÇÃOs!! FALHA NA CONSULTA!";}
$lin=1;
while ($res = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC)) 
{
  $transportadora=str_replace(',', ' ', $res['transportadora']);
  $transportadora=str_replace(';', ' ', $transportadora);
  $pagador=str_replace(',', ' ', $res['pagador']);
  $pagador=str_replace(';', ' ', $pagador);
  $origem=str_replace(',', ' ', $res['origem']);
  $origem=str_replace(';', ' ', $origem);
  $destinatario=str_replace(',', ' ', $res['destinatario']);
  $destinatario=str_replace(';', ' ', $destinatario);
  $obs=str_replace(',', ' ', $res['obs']);
  $obs=str_replace(';', ' ', $obs);
  $order   = array("\r\n", "\n", "\r");
  $obs=str_replace($order, ' - ', $obs);
  $tipo=$res['tipo'];

  $lin++;$linha=$transportadora.",".$res['CTe'].",".$res['chave'].",".$pagador.",".$res['emissao'].",".$res['vlr'].",".$origem.",".$destinatario.",".$res['NFs'].",".$obs.",".$tipo.PHP_EOL;
  fwrite($fptemp, $linha);
}
fclose($fptemp); 
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('"');
$reader->setSheetIndex(0);
/* Load a CSV file and save as a XLS */
$spreadsheet = $reader->load("relatorio_temp.csv");
$writer = new Xlsx($spreadsheet);
$spreadsheet->getActiveSheet()->setTitle($final);
$styleArray = [
    'borders' => [
        'inside' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => '000000'],
        ],
    ],
];
$format = '* #,##0.00';
$spreadsheet->getActiveSheet()->getStyle('A1:K'.$lin)->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('A1:K'.$lin)->getAlignment()->setWrapText(true);
$spreadsheet->getActiveSheet()->getStyle('A1:K1')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('A1:K1')->getFont()->setSize(12);
$spreadsheet->getActiveSheet()->getStyle('A1:K'.$lin)
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A1:K1')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('B2:C'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('E2:E'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A1:K1')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('F2:F'.$lin)->getNumberFormat()->setFormatCode($format);
$spreadsheet->getActiveSheet()->getStyle('A1:K1')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('084B8A');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(24);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(50);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(17);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(20);

$writer->save($arqxlsx);
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
unlink('relatorio_temp.csv'); 
echo "<script language='JavaScript'>location.href='pre_fatura_brainfarma01.php?arquivo=".$arqxlsx."';</script>";
?>