Importar planilhas do Excel com PHP
O tema de hoje é como importar planilhas do Excel com PHP gravando em um banco de dados no MySQL, algum tempo atrás escrevi 2 posts com tema parecido porém com algumas limitações.
Só era possível importar planilhas com a extensão xls, nos dias atuais o excel gera planilhas com a extensão xlsx, nesse caso vou atualizar o conteúdo adaptado para essa extensão.
Outros posts sobre PHP que podem interessar:
Parte 1 – Importando planilhas do Excel (.xls) para o MySQL usando PHP com PDO
Parte 2 -Importando planilhas do Excel (.xls) para o MySQL usando PHP com PDO
10 pegadinhas PHP escondidas na linguagem
O segredo para se trabalhar com importações de arquivos independente da extensão é conhecer a estrutura desse arquivo, ou seja, em que ordem estão dispostas as colunas que serão importadas. Nesse post vamos simular a importação de uma planilha contendo cadastros de clientes com alguns campos básicos, a ideia é ler essas linhas e inserir em uma tabela cliente no MySQL.
Layout e dados da planilha
Observem que as colunas possuem títulos, essa primeira linha terá que ser descartada no momento da importação, outro ponto importante é o código do cliente que possui “0” a esquerda do número então o campo que vai receber esse valor na tabela tem que ser do tipo VARCHAR, campos com data type INT não aceitam zeros a esquerda.
Observação: A extensão xlsx é gerada a partir de planilhas gravadas pela ferramenta Microsoft Excel, nesse exemplo abri uma planilha com a ferramenta LibreOffice Calc pois estou trabalhando com Ubuntu.
Criando o banco de dados e a tabela cliente no MySQL
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE DATABASE blog; USE blog; CREATE TABLE cliente( id int auto_increment primary key, codigo varchar(10), nome varchar(100), cpf varchar(15), email varchar(100), celular varchar(15) ); |
Script PHP para importar planilhas
Um pré-requisito para conseguirmos escrever essa importação é a utilização de uma classe PHP que não é de minha autoria mas o leitor deve baixar o arquivo SimpleXLSX.class.php, essa classe possui todos os métodos necessários para leitura de planilhas do excel com a extensão xlsx.
Vamos apenas escrever outra classe “ImportaPlanilha” mas simples que vai funcionar como um “wrapper” dos métodos para ler os valores das células e utilizando uma conexão PDO vamos criar outro método que vai servir para gravar os valores no banco de dados, desse modo conseguimos importar planilhas com PHP.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 |
<?php ini_set('max_execution_time','-1'); require_once "SimpleXLSX.class.php"; class ImportaPlanilha{ // Atributo recebe a instância da conexão PDO private $conexao = null; // Atributo recebe uma instância da classe SimpleXLSX private $planilha = null; // Atributo recebe a quantidade de linhas da planilha private $linhas = null; // Atributo recebe a quantidade de colunas da planilha private $colunas = null; /* * Método Construtor da classe * @param $path - Caminho e nome da planilha do Excel xlsx * @param $conexao - Instância da conexão PDO */ public function __construct($path=null, $conexao=null){ if(!empty($path) && file_exists($path)): $this->planilha = new SimpleXLSX($path); list($this->colunas, $this->linhas) = $this->planilha->dimension(); else: echo 'Arquivo não encontrado!'; exit(); endif; if(!empty($conexao)): $this->conexao = $conexao; else: echo 'Conexão não informada!'; exit(); endif; } /* * Método que retorna o valor do atributo $linhas * @return Valor inteiro contendo a quantidade de linhas na planilha */ public function getQtdeLinhas(){ return $this->linhas; } /* * Método que retorna o valor do atributo $colunas * @return Valor inteiro contendo a quantidade de colunas na planilha */ public function getQtdeColunas(){ return $this->colunas; } /* * Método que verifica se o registro CPF da planilha já existe na tabela cliente * @param $cpf - CPF do cliente que está sendo lido na planilha * @return Valor Booleano TRUE para duplicado e FALSE caso não */ private function isRegistroDuplicado($cpf=null){ $retorno = false; try{ if(!empty($cpf)): $sql = 'SELECT id FROM cliente WHERE cpf = ?'; $stm = $this->conexao->prepare($sql); $stm->bindValue(1, $cpf); $stm->execute(); $dados = $stm->fetchAll(); if(!empty($dados)): $retorno = true; else: $retorno = false; endif; endif; }catch(Exception $erro){ echo 'Erro: ' . $erro->getMessage(); $retorno = false; } return $retorno; } /* * Método para ler os dados da planilha e inserir no banco de dados * @return Valor Inteiro contendo a quantidade de linhas importadas */ public function insertDados(){ try{ $sql = 'INSERT INTO cliente (codigo, nome, cpf, email, celular)VALUES(?, ?, ?, ?, ?)'; $stm = $this->conexao->prepare($sql); $linha = ; foreach($this->planilha->rows() as $chave => $valor): if ($chave >= 1 && !$this->isRegistroDuplicado(trim($valor[2]))): $codigo = trim($valor[]); $nome = trim($valor[1]); $cpf = trim($valor[2]); $email = trim($valor[3]); $celular = trim($valor[4]); $stm->bindValue(1, $codigo); $stm->bindValue(2, $nome); $stm->bindValue(3, $cpf); $stm->bindValue(4, $email); $stm->bindValue(5, $celular); $retorno = $stm->execute(); if($retorno == true) $linha++; endif; endforeach; return $linha; }catch(Exception $erro){ echo 'Erro: ' . $erro->getMessage(); } } } |
Observações sobre os principais métodos da classe “ImportaPlanilha“:
1 – No método construtor recebo e executo uma validação básica dos 2 parâmetros $path e $conexao, instancio um objeto da classe SimpleXLSX, logo abaixo chamo o método dimension() que pertence a classe SimpleXLSX e capturo o retorno desse método atribuindo os valores de $linha e $coluna, com isso já temos quantidade de linhas e colunas da planilha passada como parâmetro.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
public function __construct($path=null, $conexao=null){ if(!empty($path) && file_exists($path)): $this->planilha = new SimpleXLSX($path); list($this->colunas, $this->linhas) = $this->planilha->dimension(); else: echo 'Arquivo não encontrado!'; exit(); endif; if(!empty($conexao)): $this->conexao = $conexao; else: echo 'Conexão não informada!'; exit(); endif; } |
2 – Esse método tem como objetivo verificar se o CPF passado como parâmetro já existe na tabela, se existir ele retorna TRUE senão é retornado FALSE, com esse método conseguimos impedir que sejam duplicados dados na tabela cliente usando como parâmetro o valor do CPF.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
private function isRegistroDuplicado($cpf=null){ $retorno = false; try{ if(!empty($cpf)): $sql = 'SELECT id FROM cliente WHERE cpf = ?'; $stm = $this->conexao->prepare($sql); $stm->bindValue(1, $cpf); $stm->execute(); $dados = $stm->fetchAll(); if(!empty($dados)): $retorno = true; else: $retorno = false; endif; endif; }catch(Exception $erro){ echo 'Erro: ' . $erro->getMessage(); $retorno = false; } return $retorno; } |
3 – Para finalizar temos o método que realmente importa os dados da planilha para o banco de dados, observem que utilizamos o objeto SimpleXLSX que está no atributo $planilha para percorrer as linhas e as colunas da planilha, a leitura dos valores é feita passando um índice. Antes de inserir verifico se a linha que está sendo lida é a primeira, nesse caso sabemos que essa linha contém apenas os títulos das colunas então não serão inseridos esses valores e também chamamos o método “isRegistroDuplicado()” para verificar se o CPF já foi cadastrado.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
public function insertDados(){ try{ $sql = 'INSERT INTO cliente (codigo, nome, cpf, email, celular)VALUES(?, ?, ?, ?, ?)'; $stm = $this->conexao->prepare($sql); $linha = ; foreach($this->planilha->rows() as $chave => $valor): if ($chave >= 1 && !$this->isRegistroDuplicado(trim($valor[2]))): $codigo = trim($valor[]); $nome = trim($valor[1]); $cpf = trim($valor[2]); $email = trim($valor[3]); $celular = trim($valor[4]); $stm->bindValue(1, $codigo); $stm->bindValue(2, $nome); $stm->bindValue(3, $cpf); $stm->bindValue(4, $email); $stm->bindValue(5, $celular); $retorno = $stm->execute(); if($retorno == true) $linha++; endif; endforeach; return $linha; }catch(Exception $erro){ echo 'Erro: ' . $erro->getMessage(); } } |
Importando dados da Planilha
Abaixo temos um exemplo de como usar a classe “ImportaPlanilha.class.php” para importar planilhas, observem que estou configurando uma conexão PDO para ser passada como parâmetro no momento que instanciarmos a classe, além do caminho e nome da planilha xlsx que nesse exemplo esta no mesmo diretório que os scripts.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
<?php /* Seta configuração para não dar timeout */ ini_set('max_execution_time','-1'); /* Require com a classe de importação construída */ require 'ImportaPlanilha.class.php'; /* Instância conexão PDO com o banco de dados */ $pdo = new PDO('mysql:host=localhost;dbname=blog', 'root', '123456'); /* Instância o objeto importação e passa como parâmetro o caminho da planilha e a conexão PDO */ $obj = new ImportaPlanilha('./clientes.xlsx', $pdo); /* Chama o método que retorna a quantidade de linhas */ echo 'Quantidade de Linhas na Planilha ' , $obj->getQtdeLinhas(), '<br>'; /* Chama o método que retorna a quantidade de colunas */ echo 'Quantidade de Colunas na Planilha ' , $obj->getQtdeColunas(), '<br>'; /* Chama o método que inseri os dados e captura a quantidade linhas importadas */ $linhasImportadas = $obj->insertDados(); /* Imprime a quantidade de linhas importadas */ echo 'Foram importadas ', $linhasImportadas, ' linhas'; |
Se tudo correr bem após executar o script acima no navegador, devemos obter o resultado como exibido na imagem abaixo, onde temos a quantidade de linhas e colunas existentes na planilha, quantidade de registros que foi importada para o banco de dados.
Ao executarmos novamente esse script, a quantidade de registros importada será “0” zero, pois estamos verificando no momento do INSERT se o CPF do cliente já está gravado na tabela, nesse caso nenhum registro será inserido.
Abaixo segue imagem com dados que foram importados da planinha após uma consulta no MySQL Workbench:
Bom pessoal nesse post demonstrei como importar planilhas do Excel com a extensão xlsx, como sempre escrevo nos posts sobre os meus exemplos, eles podem ser infinitamente melhorados com adição de outros métodos conforme a necessidade, a ideia é sempre mostrar que existe como fazer e dar um caminho para leitor.
Já precisei dessa funcionalidade em sistema onde era necessário importar planilhas do Excel extraídas de um sistema ERP para outro sistema gerador de gráficos e funcionou muito bem com grande volumes de dados.
Se você gostou desse post compartilhe nas redes sociais abaixo, até a próxima.
