1
I have a web system that converts a table HTML for EXCEL, however it has a field corresponding to the CNPJ , when I transform this table to EXCEL, it return me with 1,111111+14 instead of 111111111111111.
How can I leave formatted properly while going to EXCEL ?
<?php
$file = 'planilha.xls';
header ("Content-type: application/x-msexcel");
header ("Content-Disposition: attachment; filename=\"{$file}\"" );
header ("Content-Description: PHP Generated Data" );
?>
<?php
session_start();
$serverName = "NOME_BANCO";
$connectionInfo = array( "Database"=>"NOME", "UID"=>"USUARIO", "PWD"=>"SENHA" );
$conn = sqlsrv_connect($serverName, $connectionInfo);
if ($conn === false) {
die(print_r(sqlsrv_errors(), true));
}
$data_inicial = $_POST['data_inicio'];
$data_final = $_POST['data_final'];
$query = ("SELECT * FROM TABELA WHERE EMISSAO BETWEEN '$data_inicial' AND '$data_final' ORDER BY EMISSAO");
$params = array();
$options = array('Scrollable' => SQLSRV_CURSOR_KEYSET);
$stmt = sqlsrv_query($conn, $query, $params, $options);
?>
<!DOCTYPE html>
<html lang="pt-br">
<head>
<meta charset="UTF-8">
<meta name="description" content="">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
</head>
<body>
<table border="1">
<thead>
<tr>
<th>
<center><b>CNPJ</b></center>
</th>
</tr>
</thead>
<?php while ($campo= sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
?>
<tr>
<td>
<center>
<?php echo $campo['CNPJ']; ?>
</center>
</td>
</tr>
<?php
}
?>
</table>
<?php include("rodape.php")?>
Straight from HTML I believe that there is no way. But you can make a VBA code of less than 5 lines that will apply the correct formatting to the column, for example.
– Evilmaax
You are saying in the header that it is an Excel spreadsheet but is returning an HTML. This is a gambit that may even work, but will reach a point that will not have escape, you will have to generate a real Excel file (apparently you have reached that point). Archives
.xlsxare archive.zipwith files.xmland other miscellaneous files. This structure follows a pattern called Officeopenxml and adopted as a standard ECMA 376– fernandosavio
To have control over data formatting, bold, column width, etc. you need to create this file correctly. You will need to find a PHP library that creates this file pattern, read its documentation and use it correctly.
– fernandosavio