TransWar File Browser

Path: C:\

<?php
date_default_timezone_set('America/Fortaleza');
$agora = date("Ymd H:i:s"); 
$colL2=strtotime($agora);
$DT=date('d-m-Y_G\hi\m');
ini_set('max_execution_time',1200); 
ini_set ('memory_limit','-1') ;
$grupo='4';$ds_grupo='BRAINFARMA';
$base1= date("d-m-Y"); 
$dia=date('Ymd', strtotime($base1. ' - 6 month'));
$host = "192.168.0.201";                      
$db = "dtbtransporte";
$user = "bi";
$password = "BI@Consult@";                             
$connectionOptions = array("Database" => $db, "UID" => $user, "PWD" => $password,"CharacterSet" => "UTF-8");
$conn = sqlsrv_connect( $host, $connectionOptions);
if (!$conn){echo 'ATENÇÃO! FALHA NA CONEXÃO COM BANCO DE DADOS!<br>Tente novamente ou contate o administrador.<br>';}
$sqlquery="SELECT    g.ds_Agenda   
      ,Convert(varchar(10), g.dt_Agendamento,103) as c05    
      ,Convert(varchar(10), g.dt_Agendamento,112) as c05a    
      ,Convert(varchar(10), g.dt_Saida,103) as c06   
      ,Convert(varchar(10), g.dt_Saida,112) as c06a   
      ,Convert(varchar(5), g.hr_Saida,114) as c07    
      ,k.ds_Cidade
      ,g.nr_Manifesto   
      ,g.ds_Destino
      ,g.ds_Autoriza
      ,b.ds_ChaveNFe
      ,n.ds_Tipo
      ,o.id_Manifesto
      ,Convert(varchar(10), g.dt_Chegada,103) as c15    
      ,Convert(varchar(10), g.dt_Chegada,112) as c15a    
      ,Convert(varchar(8), g.hr_Chegada,114) as c21    
      ,Convert(varchar(10), o.dt_Inclusao,103) as c16
      ,Convert(varchar(10), o.dt_Inclusao,112) as c16a
      ,p.cd_Placa as c19 
      ,q.cd_Placa as c20 
      ,s.ds_TipoVeiculo
    FROM tbdMovimento AS a
      left JOIN tbdMovimentoNotaFiscal AS b ON  a.id_Movimento = b.id_Movimento
      left JOIN tbdAgendaNFs AS f ON  f.ds_ChaveNFe = b.ds_ChaveNFe
      left JOIN tbdAgenda AS g ON  f.id_Agenda = g.id_Agenda
      left JOIN tbdPessoa AS c on a.id_ClienteFaturamento = c.id_Pessoa
      left JOIN tbdConfiguracaoCliente AS d on d.id_Pessoa = c.id_Pessoa
      left JOIN tbdPessoa AS u on a.id_Remetente = u.id_Pessoa
      left JOIN tbdConfiguracaoCliente AS v on v.id_Pessoa = u.id_Pessoa
      left JOIN tbdPessoa AS j on a.id_Destinatario = j.id_pessoa
      left JOIN tbdConfiguracaoCliente AS w on w.id_Pessoa = j.id_Pessoa
      left JOIN tbdCidade AS k ON  a.id_CidadeDestinatario = k.id_Cidade
      left JOIN tbdManifestoMovimento AS n on (a.id_Movimento = n.id_Movimento and n.ds_Tipo = 'ENTREGA' and (n.id_Ocorrencia is null or n.id_Ocorrencia<>69))
      left JOIN tbdManifesto AS o on n.id_Manifesto = o.id_Manifesto or g.nr_Manifesto = o.id_Manifesto
      left JOIN tbdVeiculo AS p on o.id_Veiculo = p.id_Veiculo
      left JOIN tbdVeiculo AS q on o.id_Carreta = q.id_Veiculo
      left JOIN tbdTipoVeiculo AS s on p.id_TipoVeiculo = s.id_TipoVeiculo
    WHERE  d.id_GrupoCliente='".$grupo."' and Convert(varchar(10), a.dt_Cadastro,112)>='".$dia."' and a.id_TipoMovimento<>'5'  and a.id_TipoMovimento<>'109' and (v.id_GrupoCliente='".$grupo."' or w.id_GrupoCliente='".$grupo."') order by o.id_Manifesto ASC, g.ds_Agenda DESC, b.nr_NotaFiscalInteiro ASC";
$result = sqlsrv_query( $conn, $sqlquery, array(), array("Scrollable"=>"buffered"));
if( $result === false ) 
{ if( ($errors = sqlsrv_errors() ) != null) 
  { 
    foreach( $errors as $error ) 
    { 
      echo "SQLSTATE: ".$error[ 'SQLSTATE']."<br />";
      echo "código: ".$error[ 'code']."<br />";
      echo "mensagem: ".$error[ 'message']."<br />";
      exit('Fim do Comando');
    }
  }
}
$num = sqlsrv_num_rows($result); 
$linha3[1]="Aguardando para descarga".PHP_EOL;
$linha4[1]="Gerou Hora Parada (últimos 30 dias)".PHP_EOL;
$linha3[2]='Destino,Agenda,Transportadora,Manifesto,Placa Cavalo,Placa Carreta,Data Agendamento,Data Apresentação,Data Saída,Total Dias,Dias Considerado'.PHP_EOL;
$linha4[2]='Destino,Agenda,Transportadora,Manifesto,Placa Cavalo,Placa Carreta,Data Agendamento,Data Apresentação,Data Saída,Total Dias,Dias Considerado'.PHP_EOL;
$lin3=2;$lin4=2;$manif1="999999";$chaveant='';$agendant='999999';
while ($resultado = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) 
{ if ($chaveant!=$resultado['ds_ChaveNFe'])
  { $chaveant=$resultado['ds_ChaveNFe'];
    $veic=substr($resultado['ds_TipoVeiculo'],0,15);
    $autoriza=trim($resultado['ds_Autoriza']);
    $dsagenda=trim($resultado['ds_Agenda']);
    if (trim($resultado['ds_Tipo'])=="ENTREGA")
    { $manifesto=$resultado['id_Manifesto'];
      $veiculo=current(str_word_count($veic,2));
      $placa=$resultado['c19'];
      if($veiculo=="CAVALO"){$veiculo="CARRETA";}
      if($veiculo=="CARRETA"){$carreta=$resultado['c20'];}else{$carreta="";}
    }else{$manifesto="";$placa="";$veiculo="";$carreta="";}
    if($resultado['c05a']<>''){$colG=strtotime($resultado['c05a']);}else{$colG=0;}
    if ($resultado['c15a']!==null)
      { $colI=strtotime($resultado['c15a'].' '.$resultado['c21']);}else{$colI=0;}
    if($resultado['c06a']!==null){$colL=strtotime($resultado['c06a'].' '.$resultado['c07']);}else{$colL=0;}
    if($colI!==0){if($colI<=($colG+23400)){$colJ=$colG+23400;}else{$colJ=$colI;}}else{$colJ=0;}
    if($colL!==0){$colK="Finalizado";}else{if($colI<>0){$colK="Aguardando";}else{$colK="Em Rota";}}
    if($colL!==0 and $colI!==0){if($colL-$colJ<=0){$colM=0;}else{$colM=($colL-$colJ)/86400;}}else{$colM=0;}
    if($colM!==0){if($colM-1<=0){$colN=0;}else{$colN=$colM-1;}}else{$colN=0;}
    if($colI!==0){$colI=date('d/m/Y  H:i',$colI);}
    if ($manifesto<>"")
    { if ((trim($resultado['ds_Cidade'])=='Anapolis' or trim($resultado['ds_Cidade'])=='Goiania') and (($manifesto<>$manif1) or ($manifesto==$manif1 and $dsagenda<>$agendant and $dsagenda<>''))) 
      { $manif1=$manifesto;$agendant=$dsagenda;
        if($colK==="Aguardando")
        { if($colI!==0){if($colL2-$colJ<=0){$colM=0;}else{$colM=($colL2-$colJ)/86400;}}else{$colM=0;}
          if($colM!==0){if($colM-1<=0){$colN=0;}else{$colN=$colM-1;}}else{$colN=0;}
          $lin3++;$linha3[$lin3]=trim($resultado['ds_Destino']).",".$dsagenda.",TRANSWAR,".$manifesto.",".$placa.",".$carreta.",".$resultado['c05'].",".date('d/m/Y  H:i',$colJ).",".$resultado['c06']." ".$resultado['c07'].",".$colM.",".$colN.PHP_EOL;
        }
        if($autoriza!="Perdeu Agenda" and $autoriza!='PERDEU AGENDA' and $colN>0)
        { $lin4++;$linha4[$lin4]=trim($resultado['ds_Destino']).",".$dsagenda.",TRANSWAR,".$manifesto.",".$placa.",".$carreta.",".$resultado['c05'].",".date('d/m/Y  H:i',$colJ).",".$resultado['c06']." ".$resultado['c07'].",".$colM.",".$colN.PHP_EOL;
        }
      } 
    }
  }
}
$sqlquery="SELECT  fim.data_agenda as dia
    ,sum(fim.paletes) as EmAberto
    ,sum(fim.emrota) as EmRota
    ,sum(fim.aguarda) as Aguardando
from (SELECT   convert(varchar(10),a.dt_Agendamento,120) as data_agenda
    ,case 
  when rtrim(a.ds_Status) is null then sum(c.paletes)
  end             as paletes
  ,case 
  when rtrim(a.ds_Status)='Em Rota' then sum(c.paletes)
  end             as emrota
  ,case 
  when  rtrim(a.ds_Status)='Aguardando' then sum(c.paletes)
  end             as aguarda
  FROM dbo.tbdAgenda as a
  left join (SELECT b.id_Agenda as agenda
    ,b2.qt_Paletes as paletes
    FROM dbo.tbdAgendaNFs as b
      right JOIN dbo.tbdValidacao  as b2 ON rtrim(b.ds_ChaveNFe) = rtrim(b2.ds_ChaveNFe) and b2.nr_NotaFiscal is not null
    ) as c on c.agenda = a.id_Agenda
  where dt_Agendamento is not null and dt_Saida is null
  group by  ds_Status, dt_Agendamento) as fim
  group by fim.data_agenda";
$result = sqlsrv_query( $conn, $sqlquery, array(), array("Scrollable"=>"buffered"));
if( $result === false ) 
{ if( ($errors = sqlsrv_errors() ) != null) 
  { 
    foreach( $errors as $error ) 
    { 
      echo "SQLSTATE: ".$error[ 'SQLSTATE']."<br />";
      echo "código: ".$error[ 'code']."<br />";
      echo "mensagem: ".$error[ 'message']."<br />";
      exit('Fim do Comando');
    }
  }
}
$num = sqlsrv_num_rows($result); 
$tt2=0;$tt3=0;$tt4=0;
while ($resultado = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))
{
  $data1=$resultado['dia'];
  $ano1=substr($data1, 0, 4);
  $mes1=substr($data1, 5, 2);
  $dia1=substr($data1, 8, 2);
  $di=$dia1.'/'.$mes1.'/'.$ano1;
  $data[$di]=$di;
  $emaberto[$di]=$resultado['EmAberto'];$tt2=$tt2+$emaberto[$di];
  $emrota[$di]=$resultado['EmRota'];$tt3=$tt3+$emrota[$di];
  $aguardando[$di]=$resultado['Aguardando'];$tt4=$tt4+$aguardando[$di];
} 
$sqlquery="SELECT  fim.data_agenda as dia
    ,sum(fim.quantidade) as NrAgenda
from (SELECT   convert(varchar(10),a.dt_Agendamento,120) as data_agenda
    ,count(a.dt_Agendamento) as quantidade
  FROM dbo.tbdAgenda as a
  left join (SELECT   b.id_Agenda as agenda
    ,sum(b2.qt_Paletes) as paletes
    FROM dbo.tbdAgendaNFs as b
      right JOIN dbo.tbdValidacao  as b2 ON rtrim(b.ds_ChaveNFe) = rtrim(b2.ds_ChaveNFe) and b2.nr_NotaFiscal is not null
    group by b.id_Agenda) as c on c.agenda = a.id_Agenda
  where dt_Agendamento is not null and dt_Saida is null
  group by  ds_Status, dt_Agendamento) as fim
  group by fim.data_agenda";
$result = sqlsrv_query( $conn, $sqlquery, array(), array("Scrollable"=>"buffered"));
if( $result === false ) 
{ if( ($errors = sqlsrv_errors() ) != null) 
  { 
    foreach( $errors as $error ) 
    { 
      echo "SQLSTATE: ".$error[ 'SQLSTATE']."<br />";
      echo "código: ".$error[ 'code']."<br />";
      echo "mensagem: ".$error[ 'message']."<br />";
      exit('Fim do Comando');
    }
  }
}
$num = sqlsrv_num_rows($result); 
$tt1=0;
while ($resultado = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))
{
  $data1=$resultado['dia'];
  $ano1=substr($data1, 0, 4);
  $mes1=substr($data1, 5, 2);
  $dia1=substr($data1, 8, 2);
  $di=$dia1.'/'.$mes1.'/'.$ano1;
  $data[$di]=$di;
  $quant[$di]=$resultado['NrAgenda'];$tt1=$tt1+$quant[$di];
} 

$lin5=3;
foreach ($data as $value)
{
  $lin5++;$linha5[$lin5]=$data[$value].",".$quant[$data[$value]].",".$emaberto[$data[$value]].",".$emrota[$data[$value]].",".$aguardando[$data[$value]].PHP_EOL;
}
$lin5++;
$linha5[$lin5]="Totais:,".$tt1.",".$tt2.",".$tt3.",".$tt4.PHP_EOL;
$linha5[1]="Agendas por dia (em aberto)".PHP_EOL;
$linha5[2]='Datas,Agendas,Quantidade de Paletes'.PHP_EOL;
$linha5[3]=',,Em Aberto,Em Rota,Aguardando'.PHP_EOL;
$arqcsv='Excel/temp.csv';
$fptemp = fopen($arqcsv, 'w+');
for ($i=1;$i<=$lin5;$i++) { fwrite($fptemp, $linha5[$i]); }
fwrite($fptemp, ' '.PHP_EOL);
for ($i=1;$i<=$lin3;$i++) { fwrite($fptemp, $linha3[$i]); }
fwrite($fptemp, ' '.PHP_EOL);
for ($i=1;$i<=$lin4;$i++) { fwrite($fptemp, $linha4[$i]); }
fclose($fptemp); 
$fim1=$lin3+$lin5+1;
$lin1a=$lin5+2;
$cab1=$lin1a+1;
$ini1=$lin1a+2;

$fim=$lin5+$lin3+$lin4+2;
$lin1=$lin5+$lin3+3;
$cab=$lin1+1;
$ini=$lin1+2;
$arqxlsx="Excel/Resumo_BRAINFARMA.xlsx";
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($arqcsv);
$writer = new Xlsx($spreadsheet);
$styleArray0 = [
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => 'ffffff'],
        ],
    ],
];
$styleArray = [
    'borders' => [
        'allBorders' => [
            'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
            'color' => ['argb' => '000000'],
        ],
    ],
];
$format = '#,##0.00';
$format1 = '#,###';
$format2 = '###';
$format4 = 'dd/mm/yyyy hh:mm';
$spreadsheet->getActiveSheet()->setTitle('Resumo Brainfarma');
$spreadsheet->getActiveSheet()->getRowDimension('1')->setRowHeight(25);
$spreadsheet->getActiveSheet()->getRowDimension($lin1)->setRowHeight(25);
$spreadsheet->getActiveSheet()->getRowDimension($lin1a)->setRowHeight(25);
$spreadsheet->getActiveSheet()->getStyle('a1:F1')->getFont()->setSize(16);
$spreadsheet->getActiveSheet()->getStyle('a'.$lin1a.':K'.$lin1a)->getFont()->setSize(16);
$spreadsheet->getActiveSheet()->getStyle('a'.$lin1.':K'.$lin1)->getFont()->setSize(16);
$spreadsheet->getActiveSheet()->getStyle('a2:F3')->getFont()->setSize(11);
$spreadsheet->getActiveSheet()->getStyle('a'.$cab1.':K'.$cab1)->getFont()->setSize(11);
$spreadsheet->getActiveSheet()->getStyle('a'.$cab.':K'.$cab)->getFont()->setSize(11);
$spreadsheet->getActiveSheet()->getStyle('a1:F3')->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('A'.$lin5.':F'.$lin5)->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('a'.$lin1a.':K'.$cab1)->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('a'.$lin1.':K'.$cab)->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->mergeCells('A1:F1');
$spreadsheet->getActiveSheet()->mergeCells('A'.$lin1a.':K'.$lin1a);
$spreadsheet->getActiveSheet()->mergeCells('A'.$lin1.':K'.$lin1);
$spreadsheet->getActiveSheet()->getStyle('B3:F'.$lin5)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00);
$spreadsheet->getActiveSheet()->getStyle('J'.$ini1.':K'.$fim1)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00);
$spreadsheet->getActiveSheet()->getStyle('J'.$ini.':K'.$fim)->getNumberFormat()
    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_00);
$spreadsheet->getActiveSheet()->getStyle('B3:F'.$lin5)->getNumberFormat()->setFormatCode($format2);
$spreadsheet->getActiveSheet()->getStyle('G'.$ini1.':I'.$fim1)->getNumberFormat()->setFormatCode($format4);
$spreadsheet->getActiveSheet()->getStyle('G'.$ini.':I'.$fim)->getNumberFormat()->setFormatCode($format4);
$spreadsheet->getActiveSheet()->mergeCells('A2:A3');
$spreadsheet->getActiveSheet()->mergeCells('B2:B3');
$spreadsheet->getActiveSheet()->mergeCells('C2:E2');
$spreadsheet->getActiveSheet()->getStyle('A2:E3')->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('8eb4e3');
$spreadsheet->getActiveSheet()->getStyle('A'.$lin5.':E'.$lin5)->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('8eb4e3');
$spreadsheet->getActiveSheet()->getStyle('A'.$cab1.':K'.$cab1)->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('8eb4e3');
$spreadsheet->getActiveSheet()->getStyle('A'.$cab.':K'.$cab)->getFill()
    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
    ->getStartColor()->setARGB('8eb4e3');
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(14);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(18);
$spreadsheet->getActiveSheet()->getStyle('A2:F2')
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A'.$cab1.':K'.$cab1)
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A'.$cab.':K'.$cab)
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A2:F2')
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A'.$cab1.':K'.$cab1)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A'.$cab.':K'.$cab)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A3:F'.$lin5)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A'.$ini1.':K'.$fim1)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A'.$ini.':K'.$fim)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('A2:F2')->getAlignment()->setWrapText(true);
$spreadsheet->getActiveSheet()->getStyle('A'.$cab1.':K'.$cab1)->getAlignment()->setWrapText(true);
$spreadsheet->getActiveSheet()->getStyle('A'.$cab.':K'.$cab)->getAlignment()->setWrapText(true);
$spreadsheet->getActiveSheet()->insertNewColumnBefore('A', 1);
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$spreadsheet->getActiveSheet()->getStyle('A1:Z500')->applyFromArray($styleArray0);
$spreadsheet->getActiveSheet()->getStyle('B2:F'.$lin5)->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('B'.$cab1.':L'.$fim1)->applyFromArray($styleArray);
$spreadsheet->getActiveSheet()->getStyle('B'.$cab.':L'.$fim)->applyFromArray($styleArray);
$z=intval($lin5/2);$z1=$z+1;
//$spreadsheet->getActiveSheet()->insertNewRowBefore(1, 1);
//$spreadsheet->getActiveSheet()->getStyle('A1:Z1')->applyFromArray($styleArray0);
//$spreadsheet->getActiveSheet()->getRowDimension(1)->setRowHeight(30);
//$spreadsheet->getActiveSheet()->getStyle('A1:L1')->getFont()->setSize(18);
$spreadsheet->getActiveSheet()->getStyle('h'.$z.':i'.$z1)
    ->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle('h'.$z.':i'.$z1)
    ->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getCell('h'.$z)->setValue('RESUMO BRAINFARMA');
$spreadsheet->getActiveSheet()->getStyle('h'.$z)->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getStyle('h'.$z)->getFont()->setSize(18);
$spreadsheet->getActiveSheet()->mergeCells('h'.$z.':i'.$z1);
$writer->save($arqxlsx);
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
//include("resumo02.php");
echo "<script language='JavaScript'>location.href='index.php?arquivo=".$arqxlsx."';</script>";
?>