Cómo exportar datos de bases de datos MySQL a Excel usando PHP

Recientemente publiqué un artículo Cómo leer archivos CSV y Excel en PHP usando PhpSpreadsheet y uno de los lectores preguntó sobre la exportación de registros de bases de datos MySQL a un archivo Excel. El usuario puede necesitar sus datos en el archivo Excel o CSV para poder leerlos o compartirlos fácilmente. En este artículo, discutimos cómo se pueden exportar datos de una base de datos a archivos Excel y CSV. Además de esto, también estudiaremos cómo se puede enviar este archivo exportado como un archivo adjunto en el correo electrónico.

Empezando

Para comenzar, primero debe instalar la biblioteca PhpSpreadsheet. Recomiendo usar Composer para la instalación de la biblioteca. Abra la terminal en el directorio raíz de su proyecto y ejecute el comando:

composer require phpoffice/phpspreadsheet

PhpSpreadsheet es la biblioteca que brinda soporte para leer y escribir diferentes tipos de formatos de archivo. A continuación se muestra la captura de pantalla de los formatos de archivo compatibles.

Nuestro objetivo final es exportar los registros de la tabla de la base de datos a un archivo Excel / CSV. Tomo la tabla de ‘productos’ como ejemplo. Creemos una tabla de ‘productos’ en la base de datos ejecutando la siguiente consulta.

CREATE TABLE `products` ( `id` int(11) NOT NULL AUTO_INCREMENT,
 `product_name` varchar(255) NOT NULL,
 `product_sku` varchar(255) NOT NULL,
 `product_price` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

A continuación, agregue algunas entradas ficticias en la tabla para que vea algunos registros en el archivo exportado.

INSERT INTO `products` (`id`, `product_name`, `product_sku`, `product_price`) VALUES
(1, 'Apple', 'app_111', '$1000'),
(2, 'Lenovo', 'len_222', '$999'),
(3, 'Samsung', 'sam_689', '$1200'),
(4, 'Acer', 'ace_620', '$900');

Luego de esto creamos un db.phparchivo donde escribiríamos la conexión a la base de datos.

db.php

<?php
$db_host = 'DATABASE_HOST';
$db_username = 'DATABASE_USERNAME';
$db_password = 'DATABASE_PASSWORD';
$db_name = 'DATABASE_NAME';
 
$db = new mysqli($db_host, $db_username, $db_password, $db_name);
 
if($db->connect_error){
    die("Unable to connect database: ". $db->connect_error);
}

Asegúrese de reemplazar los marcadores de posición con los valores reales en el código anterior. En la siguiente parte del tutorial, estoy asignando una sección separada sobre la exportación de datos a Excel, a CSV y el envío del correo electrónico con un archivo exportado como archivo adjunto.

Exportar datos de base de datos MySQL a Excel usando PHP

Ha instalado la biblioteca y está listo con una tabla de base de datos con entradas ficticias. Ahora puede continuar y escribir el código real que exportará un archivo de Excel con los datos completados.

Cree un index.phparchivo en el directorio raíz. En este archivo, escribiré una consulta SELECT para obtener datos de la base de datos, escribiré estos datos en Excel y, finalmente, haré que se descarguen automáticamente.

index.php

<?php
require_once "vendor/autoload.php";
require_once "db.php";
 
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;
 
$spreadsheet = new Spreadsheet();
$Excel_writer = new Xlsx($spreadsheet);
 
$spreadsheet->setActiveSheetIndex(0);
$activeSheet = $spreadsheet->getActiveSheet();
 
$activeSheet->setCellValue('A1', 'Product Name');
$activeSheet->setCellValue('B1', 'Product SKU');
$activeSheet->setCellValue('C1', 'Product Price');
 
$query = $db->query("SELECT * FROM products ORDER BY id DESC");
 
if($query->num_rows > 0) {
    $i = 2;
    while($row = $query->fetch_assoc()) {
        $activeSheet->setCellValue('A'.$i, $row['product_name']);
        $activeSheet->setCellValue('B'.$i, $row['product_sku']);
        $activeSheet->setCellValue('C'.$i, $row['product_price']);
        $i++;
    }
}
 
$filename = 'products.xlsx';
 
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='. $filename);
header('Cache-Control: max-age=0');
$Excel_writer->save('php://output');

Cuando ejecuta este archivo PHP en un navegador, un archivo de Excel comenzará a descargarse automáticamente y esta hoja de Excel tendrá el siguiente resultado.

Exportar datos de base de datos MySQL a CSV usando PHP

En la sección anterior, exportamos datos a un archivo de Excel. Si alguien está buscando exportar datos en un archivo CSV, debe cambiar algunas líneas en el código anterior.

csv.php

<?php
require_once "vendor/autoload.php";
require_once "db.php";
 
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterCsv;
 
$spreadsheet = new Spreadsheet();
$Excel_writer = new Csv($spreadsheet);
 
$spreadsheet->setActiveSheetIndex(0);
$activeSheet = $spreadsheet->getActiveSheet();
 
$activeSheet->setCellValue('A1', 'Product Name');
$activeSheet->setCellValue('B1', 'Product SKU');
$activeSheet->setCellValue('C1', 'Product Price');
 
$query = $db->query("SELECT * FROM products ORDER BY id DESC");
 
if($query->num_rows > 0) {
    $i = 2;
    while($row = $query->fetch_assoc()) {
        $activeSheet->setCellValue('A'.$i, $row['product_name']);
        $activeSheet->setCellValue('B'.$i, $row['product_sku']);
        $activeSheet->setCellValue('C'.$i, $row['product_price']);
        $i++;
    }
}
 
$filename = 'products.csv';
 
header('Content-Type: application/text-csv');
header('Content-Disposition: attachment;filename='. $filename);
header('Cache-Control: max-age=0');
$Excel_writer->save('php://output');

Envíe un correo electrónico con un archivo adjunto

Hasta ahora en este artículo, hemos visto cómo descargar el archivo en formato CSV y Excel. Es posible que algunos usuarios deseen enviar el archivo exportado como archivo adjunto en el correo electrónico. Veamos cómo lograrlo.

Primero instale la biblioteca PHPMailer usando el comando:

composer require phpmailer/phpmailer

Después de instalar la biblioteca, puede usar cualquier servidor SMTP para enviar un correo electrónico. Tu decides. Puede utilizar el servidor SMTP proporcionado por el alojamiento, AWS SES o el servidor SMTP de Gmail. Si va con el servidor SMTP de Gmail, lea nuestro artículo Enviar correo electrónico utilizando el servidor SMTP de Gmail desde PHP Script, que explica la configuración necesaria para ello.

El código que se escribe previamente descarga el archivo automáticamente. Pero ahora, en lugar de hacerlo descargable, guardaremos el archivo en el directorio y luego lo enviaremos como un archivo adjunto. En resumen, tendremos un código a continuación para guardar el archivo.

...
$filename = 'products.csv';
 
if (!file_exists('files')) {
    mkdir('files', 0755);
}
$Excel_writer->save('files/'.$filename);

Nuestro código final para enviar un archivo adjunto en un correo electrónico será el siguiente:

<?php
require_once "vendor/autoload.php";
require_once "db.php";
 
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterCsv;
 
//Import PHPMailer classes into the global namespace
use PHPMailerPHPMailerPHPMailer;
use PHPMailerPHPMailerException;
 
$spreadsheet = new Spreadsheet();
$Excel_writer = new Csv($spreadsheet);
 
$spreadsheet->setActiveSheetIndex(0);
$activeSheet = $spreadsheet->getActiveSheet();
 
$activeSheet->setCellValue('A1', 'Product Name');
$activeSheet->setCellValue('B1', 'Product SKU');
$activeSheet->setCellValue('C1', 'Product Price');
 
$query = $db->query("SELECT * FROM products ORDER BY id DESC");
 
if($query->num_rows > 0) {
    $i = 2;
    while($row = $query->fetch_assoc()) {
        $activeSheet->setCellValue('A'.$i, $row['product_name']);
        $activeSheet->setCellValue('B'.$i, $row['product_sku']);
        $activeSheet->setCellValue('C'.$i, $row['product_price']);
        $i++;
    }
}
 
$filename = 'products.csv';
 
if (!file_exists('files')) {
    mkdir('files', 0755);
}
$Excel_writer->save('files/'.$filename);
 
// send as an attachment
$mail = new PHPMailer(true);
try {
    $mail->isSMTP();
    $mail->Host = 'SMTP_HOST';
    $mail->SMTPAuth = true;
    $mail->Username = 'SMTP_USERNAME';   //username
    $mail->Password = 'SMTP_PASSWORD';   //password
    $mail->SMTPSecure = 'ssl';
    $mail->Port = 465;
 
    $mail->setFrom('FROM_EMAIL_ADDRESS', 'FROM_NAME');
    $mail->addAddress('RECEPIENT_EMAIL_ADDRESS', 'RECEPIENT_NAME');
  
    $mail->addAttachment('files/'.$filename);
  
    $mail->isHTML(true);
    $mail->Subject = 'Our Exported File';
    $mail->Body    = 'Our Exported File';
  
    $mail->send();
    echo 'Message has been sent';
} catch (Exception $e) {
    echo 'Message could not be sent. Mailer Error: '. $mail->ErrorInfo;
}

Reemplace los marcadores de posición con los valores reales y el correo electrónico se enviará con un archivo adjunto a la dirección de correo electrónico del destinatario.

Espero que comprenda cómo exportar registros de bases de datos MySQL al archivo Excel o CSV y también enviarlo como un archivo adjunto. Comparta sus pensamientos y sugerencias en la sección de comentarios a continuación.

Artículos relacionados

• Cómo transferir archivos a un servidor remoto en PHP
• Enviar correo electrónico usando Mailjet (alternativa al servidor SMTP de Gmail) en PHP
• Cómo convertir HTML a PDF en PHP