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/CTe KERRY ".$final.".xlsx";
$fptemp = fopen('fatura_temp.csv', 'w+');
fwrite($fptemp,"Dados do Emitente - Transportador,,,,,Dados do Tomador - Pagador,,Dados do Remetente - Origem,,Dados do Destinatário - Destino,,Valores,".PHP_EOL); 
fwrite($fptemp,'Razão Social,CNPJ,UF,Inscrição Estadual,Chave Cte,CNPJ,UF,CNPJ,UF,CNPJ,UF,Valor do Serviço,ICMS'.PHP_EOL);
require_once("conexao_srvtms.php");
$sql = sqlsrv_query( $conn, "SELECT
       rtrim(a.nr_Conhecimento)+'-'+rtrim(a.cd_SerieCTRC) as CTe
      ,l.ds_Pessoa as nomectrc
      ,l.cd_CGCCPF as cnpjctrc
      ,i.cd_Estado as ufctrc
      ,l.cd_IE as iectrc
      ,o.ds_ChaveCTe as chave
      ,b.cd_CGCCPF as cnpjfat
      ,k.cd_Estado as uffat
      ,d.cd_CGCCPF as cnpjori
      ,m.cd_Estado as ufremet
      ,e.cd_CGCCPF as cnpjdes
      ,n.cd_Estado as ufdest
      ,a.vl_Frete as vlfrete
      ,a.vl_ICMS as icms
  FROM dbo.tbdMovimento as a
      LEFT JOIN dbo.tbdPessoa as b on a.id_ClienteFaturamento = b.id_Pessoa
      left JOIN dbo.tbdConfiguracaoCliente AS c on a.id_ClienteFaturamento = c.id_Pessoa
      LEFT JOIN dbo.tbdPessoa as d on a.id_Remetente = d.id_Pessoa
      LEFT JOIN dbo.tbdPessoa as e on a.id_Destinatario = e.id_Pessoa
      LEFT JOIN dbo.tbdPessoa as l on a.id_Transportadora = l.id_Pessoa
      LEFT JOIN dbo.tbdCidade as h on l.id_Cidade = h.id_Cidade
      LEFT JOIN dbo.tbdEstado as i on h.id_Estado = i.id_Estado
      LEFT JOIN dbo.tbdCidade as j on b.id_Cidade = j.id_Cidade
      LEFT JOIN dbo.tbdEstado as k on j.id_Estado = k.id_Estado
      LEFT JOIN dbo.tbdCidade as f on a.id_CidadeOrigem = f.id_Cidade
      LEFT JOIN dbo.tbdEstado as m on f.id_Estado = m.id_Estado
      LEFT JOIN dbo.tbdCidade as g on a.id_CidadeDestinatario = g.id_Cidade
      LEFT JOIN dbo.tbdEstado as n on g.id_Estado = n.id_Estado
      LEFT JOIN dbo.tbdLoteCTeMovimento as o on  a.id_Movimento = o.id_Movimento
    WHERE c.id_GrupoCliente='14' and (Convert(varchar(8), a.dt_ImpressaoConhecimento,112) BETWEEN '$data1' and '$data2') order by a.nr_Conhecimento", array(), array( "Scrollable" => SQLSRV_CURSOR_FORWARD ));
if (!$sql){echo "ATENÇÃOs!! FALHA NA CONSULTA!";}
$lin=2;$manif1="999999";
while ($res = sqlsrv_fetch_array($sql, SQLSRV_FETCH_ASSOC)) 
{
  if ($res['CTe']<>$manif1) 
    { $manif1=$res['CTe'];
      if ($res['ufctrc']==$res['ufdest']){$IE=$res['iectrc'];}else{$IE='Não Possui';}
      $lin++;$linha=trim($res['nomectrc']).",".$res['cnpjctrc'].",".$res['ufctrc'].",".$IE.",".$res['chave'].",".$res['cnpjfat'].",".$res['uffat'].",".$res['cnpjori'].",".$res['ufremet'].",".$res['cnpjdes'].",".$res['ufdest'].",".$res['vlfrete'].",".$res['icms'].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("fatura_temp.csv");
$writer = new Xlsx($spreadsheet);
$spreadsheet->getActiveSheet()->setTitle($final);
$spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(30);
$spreadsheet->getActiveSheet()->mergeCells('A1:E1');
$spreadsheet->getActiveSheet()->mergeCells('F1:G1');
$spreadsheet->getActiveSheet()->mergeCells('H1:I1');
$spreadsheet->getActiveSheet()->mergeCells('J1:K1');
$spreadsheet->getActiveSheet()->mergeCells('L1:M1');
$styleArray = [
    'borders' => [
        'inside' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => '000000'],
        ],
    ],
];
$styleArray2 = [
    'borders' => [
        'vertical' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => 'a4a4a4'],
        ],
    ],
];
$format = '* #,##0.00';
$format2 = '* ###';
$spreadsheet->getActiveSheet()->getStyle('A1:M'.$lin)->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('A1:M'.$lin)->applyFromArray($styleArray2);
$spreadsheet->getActiveSheet()->getStyle('A1:M2')->getAlignment()->setWrapText(true);
$spreadsheet->getActiveSheet()->getStyle('a1:m1')->getFont()->setSize(10);
$spreadsheet->getActiveSheet()->getStyle('a2:M2')->getFont()->setSize(10);
$spreadsheet->getActiveSheet()->getStyle('a3:M'.$lin)->getFont()->setSize(9);
$spreadsheet->getActiveSheet()->getStyle('a1:M2')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('A1:M2')
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A1:M2')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A3:K'.$lin)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a2:M2')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('B3:B'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);
$spreadsheet->getActiveSheet()->getStyle('D3:D'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);
$spreadsheet->getActiveSheet()->getStyle('F3:F'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);
$spreadsheet->getActiveSheet()->getStyle('H3:H'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);
$spreadsheet->getActiveSheet()->getStyle('J3:J'.$lin)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);
$spreadsheet->getActiveSheet()->getStyle('L3:M'.$lin)->getNumberFormat()->setFormatCode($format);
for ($c=3; $c <= $lin; $c++) 
  { if ($c % 2 == 0) { $bg="d8d8d8";} else {$bg="ffffff";}
    $range='A'.$c.':M'.$c;
    $spreadsheet->getActiveSheet()->getStyle($range)->getFill()
        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
        ->getStartColor()->setARGB($bg);
  }
$spreadsheet->getActiveSheet()->getStyle('A2:M2')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('5882FA');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(48);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(14);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(10);

$writer->save($arqxlsx);
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
unlink('fatura_temp.csv'); 
echo "<script language='JavaScript'>alert('Planilha EXCEL gerada com Sucesso! \\nTecle OK para BAIXAR.');location.href='kerry.php?arquivo=".$arqxlsx."';</script>";
?>