Como salvar dados do MySQL em formato Excel usando PHP

Introdução

Neste tutorial, você aprenderá a exportar dados do MySQL para Excel usando PHP. Esta habilidade é essencial para desenvolvedores que precisam gerar relatórios ou realizar backups de dados de forma eficiente.

Usaremos a biblioteca PhpSpreadsheet, que simplifica a criação de arquivos Excel.

Dependências

Antes de começar, você precisa instalar a biblioteca PhpSpreadsheet em seu ambiente de desenvolvimento. Use o Composer, o gerenciador de dependências do PHP, para fazer a instalação:

composer require phpoffice/phpspreadsheet

Código Completo

A seguir, o código PHP completo para conectar ao banco de dados MySQL, consultar dados e exportá-los para um arquivo Excel:

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Configurações do banco de dados
$host = 'localhost';
$dbname = 'nome_do_seu_banco';
$username = 'seu_usuario';
$password = 'sua_senha';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
} catch (PDOException $e) {
    exit("Erro ao conectar ao banco de dados: " . $e->getMessage());
}

// Consulta SQL
$sql = "SELECT id, empresa, cnpj, endereco, bairro, cidade, estado, telefone, email FROM clientes";
$stmt = $pdo->query($sql);

// Criando uma nova planilha
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Definindo o cabeçalho da planilha
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Empresa');
$sheet->setCellValue('C1', 'CNPJ');
$sheet->setCellValue('D1', 'Endereço');
$sheet->setCellValue('E1', 'Bairro');
$sheet->setCellValue('F1', 'Cidade');
$sheet->setCellValue('G1', 'Estado');
$sheet->setCellValue('H1', 'Telefone');
$sheet->setCellValue('I1', 'Email');

// Preenchendo a planilha com os dados
$rowCount = 2;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $sheet->setCellValue('A' . $rowCount, $row['id']);
    $sheet->setCellValue('B' . $rowCount, $row['empresa']);
    $sheet->setCellValue('C' . $rowCount, $row['cnpj']);
    $sheet->setCellValue('D' . $rowCount, $row['endereco']);
    $sheet->setCellValue('E' . $rowCount, $row['bairro']);
    $sheet->setCellValue('F' . $rowCount, $row['cidade']);
    $sheet->setCellValue('G' . $rowCount, $row['estado']);
    $sheet->setCellValue('H' . $rowCount, $row['telefone']);
    $sheet->setCellValue('I' . $rowCount, $row['email']);
    $rowCount++;
}

// Definindo cabeçalhos para o download
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="clientes.xlsx"');
header('Cache-Control: max-age=0');

// Escrevendo o arquivo diretamente para o PHP output
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;

?>

Explicando o Código

Para explicar o funcionamento deste código, vamos dividí-lo em partes menores. Isso ajudará a entender melhor o processo.

Configuração do Banco de Dados

No código abaixo definimos as configurações de conexão com o banco de dados MySQL, incluindo host, nome do banco, usuário e senha. A conexão é feita através da biblioteca PDO.

// Configurações do banco de dados
$host = 'localhost';
$dbname = 'nome_do_seu_banco';
$username = 'seu_usuario';
$password = 'sua_senha';

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
} catch (PDOException $e) {
    exit("Erro ao conectar ao banco de dados: " . $e->getMessage());
}

Consulta SQL

Após se conectar com o banco de dados, executamos uma consulta SQL para obter os dados da tabela clientes que desejamos exportar.

// Consulta SQL
$sql = "SELECT id, empresa, cnpj, endereco, bairro, cidade, estado, telefone, email FROM clientes";
$stmt = $pdo->query($sql);

Criando e Preenchendo a Planilha Excel

Após obter os dados do MySQL, o próximo passo é criar uma nova planilha Excel e preencher com esses dados. Este procedimento é feito exatamente como se estivéssemos no Excel, sendo A1 a primeira coluna da primeira linha, B2 a segunda coluna da segunda linha e assim por diante.

Usamos a primeira linha como um cabeçalho com os nomes das colunas da tabela. Já os dados do banco de dados são inseridos a partir da segunda linha.

// Criando uma nova planilha
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Definindo o cabeçalho da planilha
$sheet->setCellValue('A1', 'ID');
$sheet->setCellValue('B1', 'Empresa');
$sheet->setCellValue('C1', 'CNPJ');
$sheet->setCellValue('D1', 'Endereço');
$sheet->setCellValue('E1', 'Bairro');
$sheet->setCellValue('F1', 'Cidade');
$sheet->setCellValue('G1', 'Estado');
$sheet->setCellValue('H1', 'Telefone');
$sheet->setCellValue('I1', 'Email');

// Preenchendo a planilha com os dados
$rowCount = 2;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $sheet->setCellValue('A' . $rowCount, $row['id']);
    $sheet->setCellValue('B' . $rowCount, $row['empresa']);
    $sheet->setCellValue('C' . $rowCount, $row['cnpj']);
    $sheet->setCellValue('D' . $rowCount, $row['endereco']);
    $sheet->setCellValue('E' . $rowCount, $row['bairro']);
    $sheet->setCellValue('F' . $rowCount, $row['cidade']);
    $sheet->setCellValue('G' . $rowCount, $row['estado']);
    $sheet->setCellValue('H' . $rowCount, $row['telefone']);
    $sheet->setCellValue('I' . $rowCount, $row['email']);
    $rowCount++;
}

Preparação para Download

Após preencher a planilha com os dados, especificamos alguns cabeçalhos HTTP para informar ao navegador que o arquivo deve ser baixado como um arquivo Excel clientes.xlsx. O arquivo é salvo de forma que a saída seja direcionada para o output do PHP, iniciando o download pelo navegador do usuário:

// Definindo cabeçalhos para o download
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="clientes.xlsx"');
header('Cache-Control: max-age=0');

// Escrevendo o arquivo diretamente para o PHP output
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;

Conclusão

Você agora sabe como exportar dados do MySQL para Excel usando PHP e a biblioteca PhpSpreadsheet.

Explore mais funcionalidades da PhpSpreadsheet para ampliar suas habilidades em PHP. Prática e experimentação te levarão a desenvolver aplicações cada vez mais complexas e úteis.