Excel generated by PHP query returns with incorrect characters

-1

I made a PHP query to generate an Excel report. Home The values are saved correctly, as shown below:



However the generated Excel returns with incorrect characters, mainly in Mandarin, rsrs, as shown below:



IalreadyinsertedtheconversiondataintoPortuguese-br,butitdidnotwork
<metahttp-equiv="Content-Type" content="text/html" charset="utf-8" lang="pt-br"> .

Here is the code:

<?php

$data = date('d-m-Y');

header("Expires: 0");
header("Cache-control: private");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Description: File Transfer");
header("Content-Type: application/vnd.ms-excel");
header("Content-disposition: attachment; filename=CADOP_{$data}.xls");
?><head>
<meta http-equiv="Content-Type" content="text/html" charset="utf-8" lang="pt-br">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script><scriptsrc="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script src="sorttable.js"></script>

</head>

<script>
$( function() {
$( "#datepicker" ).datepicker();
} );
</script> 

<script>  
      $(document).ready(function(){  
           $.datepicker.setDefaults({  
        dayNamesMin: ['D','S','T','Q','Q','S','S','D'],
        dayNamesShort: ['Dom','Seg','Ter','Qua','Qui','Sex','Sáb','Dom'],
        dayNames: ['Domingo','Segunda','Terça','Quarta','Quinta','Sexta','Sábado'],
        monthNamesShort: ['Jan','Fev','Mar','Abr','Mai','Jun','Jul','Ago','Set','Out','Nov','Dez'],
        monthNames: ['Janeiro','Fevereiro','Março','Abril','Maio','Junho','Julho','Agosto','Setembro','Outubro','Novembro','Dezembro'],
                dateFormat: 'yy-mm-dd'   
           });  
           $(function(){  
                $("#from_date").datepicker();  
                $("#to_date").datepicker();  
           });  
           $('#filter').click(function(){  
                var from_date = $('#from_date').val();  
                var to_date = $('#to_date').val();  
                if(from_date != '' && to_date != '')  
                {  
                     $.ajax({  
                          url:"filter.php",  
                          method:"POST",  
                          data:{from_date:from_date, to_date:to_date},  
                          success:function(data)  
                          {  
                               $('#order_table').html(data);  
                          }  
                     });  
                }  
                else  
                {  
                     alert("Please Select Date");  
                }  
           });  
      });  

function centraliza(w,h){
    x = parseInt((screen.width - w)/2);
    y = parseInt((screen.height - h)/2);
   //alert(x + '\n' + y);
    document.getElementById('a').style.top = x;
    document.getElementById('a').style.left = y;
}
 </script>

  </form>
   <br>


<table id="table" table border="1" class="sortable" align="center"> 
<thead> 
<tr> 
<th>Datas</th>
<th>Módulo</th>
<th>Assunto/Script</th>
<th>Descrição/Alteração</th>
<th>Script/Informação</th>
<th>Responsável</th>
<th>Tipo</th>
<th>Origem</th>


</tr> 
</thead> 
<tbody> 



<?php

    $serverName = "xxxxxxxxxxxxxxxx"; 
    $connectionInfo = array( "Database"=>"xxxxxx", "UID"=>"xxxxxxx", "PWD"=>"xxxxxxxxxx");

    $conn = sqlsrv_connect( $serverName, $connectionInfo);

$sql="SELECT [Datas]
      ,[Modulo]
      ,[AssuntoScript]
      ,[DescricaoAlteracao]
      ,[ScriptInformacao]
      ,[Responsavel]
      ,[Tipo]
      ,[Origem]
  FROM [xxxxxxxxxx].[dbo].[formulario]";


        $stmt = sqlsrv_query( $conn, $sql); //or print_r( sqlsrv_errors() );
    if( $stmt === false) {
    die( print_r( sqlsrv_errors(), true) );
}


while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
?>

<tr>
<td><?php echo $row["Datas"] ?></td>
<td><?php echo $row["Modulo"] ?></td>
<td><?php echo $row["AssuntoScript"] ?></td>
<td><?php echo $row["DescricaoAlteracao"] ?></td>
<td><?php echo $row["ScriptInformacao"] ?></td>
<td><?php echo $row["Responsavel"] ?></td>
<td><?php echo $row["Tipo"] ?></td>
<td><?php echo $row["Origem"] ?></td>


<?php
}
sqlsrv_free_stmt( $stmt);

?>
</tr>
</tbody> 
</table>





<br />
<table width="100%">
<tr>
    <td colspan="4" style="font-size: x-small;">Fonte: MONITORIA - QUALIDADE - xxxx RJ</td>
</tr>
<tr>
    <td colspan="4" style="font-size: x-small;"><?php echo "Relatório Gerado em " .date('d/m/Y'). " às " . date('H:i:s'); ?></td>
</tr>
</table>


Thanks!

    
asked by anonymous 19.11.2018 / 19:31

1 answer

0

I was able to solve the problem by changing the DB connection and declaring the "Characterset"

$dbhost   = "xxxxxxxx";   #Nome do host
$db       = "xxxxx";   #Nome do banco de dados
$user     = "xxxxxxxx"; #Nome do usuário
$password = "xxxxxxx";   #Senha do usuário


$conninfo = array("Database" => $db, "UID" => $user, "PWD" => $password, "CharacterSet" => "UTF-8",);
$conn = sqlsrv_connect($dbhost, $conninfo);
    
19.11.2018 / 22:10