'Expoting MySQL data to Excel file
I want to download an Excel file from a MySQL database query.
This is the code I am using;
<?php
include '../database.php';
?>
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- Option 1: Bootstrap Bundle and JQuery -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-ka7Sk0Gln4gmtz2MlQnikT1wXgYsOg+OMhuP+IlRH9sENBO0LRn5q+8nbTov4+1p" crossorigin="anonymous"></script>
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
<title>Capenergy Explotación de datos</title>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<H1>Explotación de datos </H1>
<H2>Inneoterapia DESPI</H2>
<H3>Post market follow up</H3>
</div>
</div>
</div>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Post market follow up - Inneoterapia DESPI</h2>
<table class="table table-bordered">
<thead>
<tr>
<th style="border: 2px solid black">Profesional</th>
<th style="border: 2px solid black">Fecha consulta</th>
<th style="border: 2px solid black">Código consulta</th>
<th style="border: 2px solid black">Sexo</th>
<th style="border: 2px solid black">Edad</th>
<th style="border: 2px solid black">Motivo consulta</th>
<th style="border: 2px solid black">Número de sesiones</th>
</tr>
</thead>
<tbody>
<?php
$myquery = "SELECT
profesional.nombre as nombre_profesional,
profesional.apellidos as apellidos_profesional,
consulta.fecha as fecha_consulta,
consulta.codigo as codigo_consulta,
paciente.sexo as sexo_paciente,
paciente.edad as edad_paciente,
consulta.motivo1 as motivo_consulta1,
consulta.motivo2 as motivo_consulta2,
consulta.motivo3 as motivo_consulta3,
consulta.motivo4 as motivo_consulta4,
consulta.motivo5 as motivo_consulta5,
(SELECT MAX(numero_sesion) FROM tb_sesiones_2022 WHERE tb_sesiones_2022.codigo_consulta = consulta.codigo) as numero_sesiones
FROM tb_consultas consulta
LEFT JOIN users profesional ON consulta.profesional = profesional.id
LEFT JOIN tb_pacientes paciente ON consulta.paciente = paciente.id_paciente
WHERE consulta.clinica = 11";
$my_res = mysqli_query($con,$myquery);
while($my_data = mysqli_fetch_assoc($my_res))
{
?>
<tr>
<td style="border: 2px solid black"><?php echo $my_data['apellidos_profesional'].', '.$my_data['nombre_profesional'];?></td>
<td style="border: 2px solid black"><?php echo $my_data['fecha_consulta'];?></td>
<td style="border: 2px solid black"><?php echo $my_data['codigo_consulta'];?></td>
<td style="border: 2px solid black"><?php echo $my_data['sexo_paciente'];?></td>
<td style="border: 2px solid black"><?php echo $my_data['edad_paciente'];?></td>
<td style="border: 2px solid black"><?php echo $my_data['motivo_consulta1'].", ".$my_data['motivo_consulta2'].", ".$my_data['motivo_consulta3'].", ".$my_data['motivo_consulta4'].", ".$my_data['motivo_consulta5'];?></td>
<td style="border: 2px solid black"><?php echo $my_data['numero_sesiones'];?></td>
</tr>
<?php
}
?>
</tbody>
</table>
<div class="text-center">
<a href="postmarketfollowup_export.php" class="btn btn-primary" target="_blank">Exportar a Excel</a>
</div>
</div>
</div>
</div>
</body>
</html>
Here you have the code for postmarketfollowup_export.php
<?php
include '../database.php';
header('Content-type: application/vnd-ms-excel');
$filename = "postmarketfollowup_despi.xls";
header("Content-Disposition:attachment;filename=\"$filename\"");
?>
<table class="table table-bordered">
<thead>
<tr>
<th style="border: 2px solid black">Profesional</th>
<th style="border: 2px solid black">Fecha consulta</th>
<th style="border: 2px solid black">Código consulta</th>
<th style="border: 2px solid black">Sexo</th>
<th style="border: 2px solid black">Edad</th>
<th style="border: 2px solid black">Motivo consulta</th>
<th style="border: 2px solid black">Número de sesiones</th>
</tr>
</thead>
<tbody>
<?php
$myquery = "SELECT
profesional.nombre as nombre_profesional,
profesional.apellidos as apellidos_profesional,
consulta.fecha as fecha_consulta,
consulta.codigo as codigo_consulta,
paciente.sexo as sexo_paciente,
paciente.edad as edad_paciente,
consulta.motivo1 as motivo_consulta1,
consulta.motivo2 as motivo_consulta2,
consulta.motivo3 as motivo_consulta3,
consulta.motivo4 as motivo_consulta4,
consulta.motivo5 as motivo_consulta5,
(SELECT MAX(numero_sesion) FROM tb_sesiones_2022 WHERE tb_sesiones_2022.codigo_consulta = consulta.codigo) as numero_sesiones
FROM tb_consultas consulta
LEFT JOIN users profesional ON consulta.profesional = profesional.id
LEFT JOIN tb_pacientes paciente ON consulta.paciente = paciente.id_paciente
WHERE consulta.clinica = 11";
$my_res = mysqli_query($con,$myquery);
while($my_data = mysqli_fetch_assoc($my_res))
{
?>
<tr>
<td style="border: 2px solid black"><?php echo $my_data['apellidos_profesional'].', '.$my_data['nombre_profesional'];?></td>
<td style="border: 2px solid black"><?php echo $my_data['fecha_consulta'];?></td>
<td style="border: 2px solid black"><?php echo $my_data['codigo_consulta'];?></td>
<td style="border: 2px solid black"><?php echo $my_data['sexo_paciente'];?></td>
<td style="border: 2px solid black"><?php echo $my_data['edad_paciente'];?></td>
<td style="border: 2px solid black"><?php echo $my_data['motivo_consulta1'].", ".$my_data['motivo_consulta2'].", ".$my_data['motivo_consulta3'].", ".$my_data['motivo_consulta4'].", ".$my_data['motivo_consulta5'];?></td>
<td style="border: 2px solid black"><?php echo $my_data['numero_sesiones'];?></td>
</tr>
<?php
}
?>
</tbody>
</table>
<div class="text-center">
<a href="postmarketfollowup_export.php" class="btn btn-primary" target="_blank">Exportar a Excel</a>
</div>
The web output is fine:
But the Excel file output is not so fine:
I guess there should be something wrong with the html bootstrap tags. I would like to get the Excel file output like the web output.
Solution 1:[1]
try
header("Content-type: application/vnd.ms-excel");
Without a Content-type it is being opened as plain text.
Your line has vnd- not vnd.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |