TransWar File Browser

Path: C:\

<?php
session_start();
ini_set('max_execution_time',259200); 
ini_set ('memory_limit','-1');
date_default_timezone_set('America/Sao_Paulo');
$dateadded = date("d/m/Y  --  G:i"); 
$inicio   = $_SESSION['inicio2'];
$final    = $_SESSION['final2'];
$json     = $_SESSION['json2']; 
$grupo    = $_SESSION['grupo'];
$ds_grupo = $_SESSION['dsgru'];
$empresa  = $_SESSION['empresa'];
if ($grupo<>"nulo"){$arq='grupo_'.$ds_grupo;} else {if($empresa<>"nulo"){$arq='empresa_'.$empresa;} else{$arq='GERAL';}}
$arq = str_replace('|', '-',$arq);
$arqxlsx="upload/base_de_dados_".$arq.".xlsx";
$arqxlsx2="upload/base_de_dados_".$arq."_cliente.xlsx";
$titulo=substr($inicio, 0, 2).'-'.substr($inicio, 3, 2).'-'.substr($inicio, 6, 4).' até '.substr($final, 0, 2).'-'.substr($final, 3, 2).'-'.substr($final, 6, 4); 
$rawData = json_decode($json, true); // Isto irá tornar o JSON em array
$l=count($rawData)+2;
$csv='||||||||||||||||||||||||||||||||||'.PHP_EOL;
$csv.='Data|Número|Transportadora|Motorista|Veículo|Placa|Carreta|Tipo|Volume|Número NFs|Valor NFs|Valor Frete|Documento|Tipo_Movimento|Cliente|Redespacho|Endereço_Redesp|Bairro_Redesp|Cidade/UF|CEP_Redesp|Nome|Endereço|Cidade/UF|Bairro|CEP|ID|Descrição|Dia|Hora|Mobile|Entrega/Coleta|Comentário|Status1|Status2|Status3|Dia3|Dia4|Data|Situação|Centro Custo|Sequência|Cli/Rem|Complemento'.PHP_EOL;
foreach($rawData as $linha){ $b=false;
  foreach($linha as $campo=>$value){
    if ($b) {$csv=$csv.'|';}
      $csv=$csv.$value;$b=true;
  }
  $csv=$csv.PHP_EOL;
}
$arqcsv="upload/base_temp.csv";
$file = fopen($arqcsv,'w');
fwrite($file, $csv);
fclose($file);
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
$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);
$spreadsheet = $reader->load($arqcsv);
$writer = new Xlsx($spreadsheet);
$spreadsheet->getActiveSheet()->setTitle($titulo);
$spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
$spreadsheet->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
$styleArray = [
  'borders' => [
      'allBorders' => [
          'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
          'color' => ['argb' => '000000'],
      ],
  ],
];
$styleArray2 = [
  'borders' => [
      'allBorders' => [
          'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
          'color' => ['argb' => 'FFFFFF'],
      ],
  ],
];
$format = '* #,##0.00';
$spreadsheet->getActiveSheet()->getCell('A1')->setValue('MANIFESTO');
$spreadsheet->getActiveSheet()->getCell('B2')->setValue('Número');
$spreadsheet->getActiveSheet()->mergeCells('A1:H1');
$spreadsheet->getActiveSheet()->getCell('I1')->setValue('MOVIMENTO');
$spreadsheet->getActiveSheet()->mergeCells('I1:O1');
$spreadsheet->getActiveSheet()->getCell('P1')->setValue('REDESPACHO');
$spreadsheet->getActiveSheet()->mergeCells('P1:T1');
$spreadsheet->getActiveSheet()->getCell('U1')->setValue('DESTINATÁRIO');
$spreadsheet->getActiveSheet()->mergeCells('U1:Y1');
$spreadsheet->getActiveSheet()->getCell('Z1')->setValue('OCORRÊNCIA');
$spreadsheet->getActiveSheet()->getCell('AE1')->setValue('PREVISÃO');
$spreadsheet->getActiveSheet()->mergeCells('Z1:AC1');
$spreadsheet->getActiveSheet()->getCell('AF1')->setValue('COMENTÁRIOS');
$spreadsheet->getActiveSheet()->mergeCells('AF1:AF2');

$spreadsheet->getActiveSheet()->getStyle('A1:AR2')->applyFromArray($styleArray2);
$spreadsheet->getActiveSheet()->getStyle('A3:AR'.$l)->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('a1:AR2')->getFont()->setSize(11);
$spreadsheet->getActiveSheet()->getStyle('a1:AR2')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('A1:AR'.$l)
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a1:AR2')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('a1:AR2')
    ->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$spreadsheet->getActiveSheet()->getStyle('A1:AR2')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('5882FA');
$spreadsheet->getActiveSheet()->getStyle('A3:B'.$l)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('E3:I'.$l)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('M3:N'.$l)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('R3:T'.$l)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('W3:Z'.$l)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('AB3:AE'.$l)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('AG3:AR'.$l)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('K3:L'.$l)->getNumberFormat()->setFormatCode($format);
for ($c=3; $c <= $l; $c++) 
  { 
    if ($c % 2 == 0) { $bg="d8d8d8";} else {$bg="ffffff";}
    $range='A'.$c.':AR'.$c;
    $spreadsheet->getActiveSheet()->getStyle($range)->getFill()
        ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
        ->getStartColor()->setARGB($bg);
  }
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(14);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(22);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('S')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('T')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('U')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('V')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('W')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('X')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('Y')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('Z')->setWidth(5);
$spreadsheet->getActiveSheet()->getColumnDimension('AA')->setWidth(40);
$spreadsheet->getActiveSheet()->getColumnDimension('AB')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AC')->setWidth(7);
$spreadsheet->getActiveSheet()->getColumnDimension('AD')->setWidth(7);
$spreadsheet->getActiveSheet()->getColumnDimension('AE')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('AF')->setWidth(50);
$spreadsheet->getActiveSheet()->getColumnDimension('AG')->setWidth(7);
$spreadsheet->getActiveSheet()->getColumnDimension('AH')->setWidth(7);
$spreadsheet->getActiveSheet()->getColumnDimension('AI')->setWidth(7);
$spreadsheet->getActiveSheet()->getColumnDimension('AJ')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('AK')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('AL')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('AM')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('AN')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('AO')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('AP')->setWidth(8);
$spreadsheet->getActiveSheet()->getColumnDimension('AQ')->setWidth(30);
$spreadsheet->getActiveSheet()->insertNewColumnBefore('J', 1);
$spreadsheet->getActiveSheet()->getCell('J2')->setValue('Peso');
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(12);
$spreadsheet->getActiveSheet()->getStyle('J3:J'.$l)->getNumberFormat()->setFormatCode($format);
for ($c=3; $c <= $l; $c++) 
  { $status=$spreadsheet->getActiveSheet()->getCellByColumnAndRow(45,$c)->getValue();
    $spreadsheet->getActiveSheet()->getCell('J'.$c)->setValue($status);
  }
$spreadsheet->getActiveSheet()->removeColumn('AS',1);
$writer->save($arqxlsx);

$spreadsheet->getActiveSheet()->removeColumn('AO',4);
$spreadsheet->getActiveSheet()->removeColumn('AH',5);
$spreadsheet->getActiveSheet()->removeColumn('AE',1);
$spreadsheet->getActiveSheet()->removeColumn('AA',1);
$spreadsheet->getActiveSheet()->getCell('AA1')->setValue('OCORRÊNCIA');
$spreadsheet->getActiveSheet()->getCell('AE1')->setValue('COMENTÁRIOS');
$spreadsheet->getActiveSheet()->getCell('AF1')->setValue('COLETA');
$spreadsheet->getActiveSheet()->mergeCells('AF1:AG1');
$spreadsheet->getActiveSheet()->removeColumn('B', 6);
$writer->save($arqxlsx2);

$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
unlink($arqcsv);
?>
<!DOCTYPE html>
<html lang="pt-br">
  <head>
    <meta http-equiv='Refresh'>
    <meta charset='utf-8'/>
    <meta content='width=device-width, initial-scale=1, maximum-scale=1' name='viewport'>
    <title>Gerar EXCEL</title>
    <link rel="icon" type="image/png" href="images/favicon.ico">
    <link href="css/estilo.css" rel="stylesheet" type="text/css" />
    <link href="css/style.css" rel="stylesheet" type="text/css" />
    <link href='css/redmond/jquery-ui-1.10.1.custom.css' rel='stylesheet' type='text/css'>
    <link href="css/bootstrap.min.css" rel="stylesheet">
    <link href="css/bootstrap-theme.min.css" rel="stylesheet">
    <link href="css/theme.css" rel="stylesheet">
    <link href="css/signin.css" rel="stylesheet">
    <script src='js/jquery-3.3.1.min.js'></script>
    <script src='js/jquery-ui.js' type='text/javascript'></script>
    <script src="js/jquery.mask.js"></script>
    <script src="js/ui.datepicker-pt-BR.js"></script>
  </head>
<body role="document"> 
  <div class="container">   
    <div class="row">
      <td> 
        <table width="100%"> 
          <tr> 
            <td width="30%" align="center">
              <img src="images/logo03.png" width="300" height="88"/>
            </td> 
            <td width="40%" align="center">
              <div class="page-header">
                <h2><span class="label label-primary">&nbsp;&nbsp;TORRE DE CONTROLE&nbsp;&nbsp;</span></h2>
              </div>
            </td>
            <td width="30%" align="center">
              <img src="<?php echo 'images/'.$logo; ?>" width="300" height="88"/>
            </td> 
          </tr> 
          <tr height="30" background="images/top-menubg.gif"> 
            <td>&nbsp;&nbsp;<?php echo $dateadded; ?></td>
            <td align='center'><strong>Gerar Base de Dados para EXCEL</strong></td>
            <td align='right'>&nbsp;&nbsp;</td> 
          </tr> 
        </table>
      </td>
      <div role='main' class='col-md-3'>
        <td valign='top' align='left' class='leftbg'>
          <?php include_once ('menulateral.php');?>
        </td> 
      </div>
      <aside role="main" class="col-md-9">
        <div class="row">
          <div class='alert alert-info' role='alert' align='center'>
            <strong>Tabela EXCEL gerada com sucesso!</strong>
          </div>
          <form method='POST' action=''>
            <div class='col-sm-12'align='center'>
              <button type='submit' class='btn btn-warning' onclick='javascript:window.location.href="main.php";'>
                <a href='<?php echo $arqxlsx2; ?>' download>Baixar Arquivo EXCEL</a>
              </button>
            </div>
          </form>
        </div>
      </aside>
    </div>
  </div>
</body>
<p class="copyright" align="center">&copy; Copyright 2020 TransWar - Todos os direitos reservados. Versão 1.0</p> 
<br />
<script src="js/jquery.min.js"></script>
<script src="js/bootstrap.min.js"></script>
<script src="js/jquery-1.9.1.js" type="text/javascript"></script>
<script src="js/jquery-ui.js" type="text/javascript"></script>
</html>